Thread: ODBC connection string, MS Access
Hello.
Could you, please, help me to optimize my connection string (MS Access 2003, PostgreSQL 8.1.1. and psqlodbc-08_01_0102)?
' PG_ODBC_PARAMETER ACCESS_PARAMETER
' *********************************************
' READONLY A0
' PROTOCOL A1
' FAKEOIDINDEX A2 'A2 must be 0 unless A3=1
' SHOWOIDCOLUMN A3
' ROWVERSIONING A4
' SHOWSYSTEMTABLES A5
' CONNSETTINGS A6
' FETCH A7
' SOCKET A8
' UNKNOWNSIZES A9 ' range [0-2]
' MAXVARCHARSIZE B0
' MAXLONGVARCHARSIZE B1
' DEBUG B2
' COMMLOG B3
' OPTIMIZER B4 ' note that 1 = _cancel_ generic optimizer...
' KSQO B5
' USEDECLAREFETCH B6
' TEXTASLONGVARCHAR B7
' UNKNOWNSASLONGVARCHAR B8
' BOOLSASCHAR B9
' PARSE C0
' CANCELASFREESTMT C1
' EXTRASYSTABLEPREFIXES C2
'Connection string
CONNECTIONSTRING:
strConnInfo = "ODBC;Driver=" & Driver & ";Server=" & SERVER & ";Port=" & PORT & ";Database=" & DATABASE & ";"
strConnUserPass = "Uid=" & USERNAME & ";Pwd=" & PASSWORD & ";"
strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=" & A6 & ";A7=100;A8=" & SOCKET & ";A9=1;" & _
"B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=0;" & _
"C0=0;C1=0;C2=dd_;"
CONNECTIONSTRING:
strConnInfo = "ODBC;Driver=" & Driver & ";Server=" & SERVER & ";Port=" & PORT & ";Database=" & DATABASE & ";"
strConnUserPass = "Uid=" & USERNAME & ";Pwd=" & PASSWORD & ";"
strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=" & A6 & ";A7=100;A8=" & SOCKET & ";A9=1;" & _
"B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=0;" & _
"C0=0;C1=0;C2=dd_;"
There are some options I don't understand, for example "Parse statements" and "Fetch/Declare" and don't know how would it affect performances...
Also, there is a problem with the limited length of connection string that Access can handle, so I have a problem that I can't input some bigger usernames and passwords. Therefore I would like to remove some unneccessary parameters, if possible. Which parameters could be safely removed?
Thanks in advance,
Zlatko
Thu, Dec 22, 2005 at 09:57:23AM +0100, Zlatko Matić napsal(a): > Hello. > > Could you, please, help me to optimize my connection string (MS Access 2003, PostgreSQL 8.1.1. and psqlodbc-08_01_0102)? > > ' PROTOCOL A1 A1 is obsolete. It isn't used anyway in driver. > ' SOCKET A8 I think this is obsolete too but I'm not sure. I don't see this option yet. > ' USEDECLAREFETCH B6 When this is enabled and Server side prepare is disabled driver use this: It doesn't use query as driver get it. It declare cursor with query. So no whole result is fetched in SQLExecute or SQLDirect. It needs fetch command (with number of row specified) to get it from backend. Please refer to SQL command CREATE CURSOR in PgSQL documentation. It could be performance lost in some cases but it could be speed up in other cases. > ' PARSE C0 If this is enabled it supports parsing SQL statement in SQLColAttribute. If I know it enable support for query like it the result column could be null and so on. But this could be performance lost if you don't need it. > ' CANCELASFREESTMT C1 > ' EXTRASYSTABLEPREFIXES C2 > > There are some options I don't understand, for example > "Parse statements" and "Fetch/Declare" and don't know how would > it affect performances... List them. I don't want describe them all. > bigger usernames and passwords. Therefore I would like to remove > some unneccessary parameters, if possible. > Which parameters could be safely removed? All for which you have used default values. Enable mylog output and try. There is line with connection string which is really used in the mylog output. Regards, Luf
Have you tried not specifying anything other than Driver, Server, Database, UID, PWD? Most items have a default like PORT defaults to 5432. Zlatko Matić wrote: > Hello. > > Could you, please, help me to optimize my connection string (MS Access 2003, PostgreSQL 8.1.1. and psqlodbc-08_01_0102)? > > ' PG_ODBC_PARAMETER ACCESS_PARAMETER > ' ********************************************* > ' READONLY A0 > ' PROTOCOL A1 > ' FAKEOIDINDEX A2 'A2 must be 0 unless A3=1 > ' SHOWOIDCOLUMN A3 > ' ROWVERSIONING A4 > ' SHOWSYSTEMTABLES A5 > ' CONNSETTINGS A6 > ' FETCH A7 > ' SOCKET A8 > ' UNKNOWNSIZES A9 ' range [0-2] > ' MAXVARCHARSIZE B0 > ' MAXLONGVARCHARSIZE B1 > ' DEBUG B2 > ' COMMLOG B3 > ' OPTIMIZER B4 ' note that 1 = _cancel_ generic optimizer... > ' KSQO B5 > ' USEDECLAREFETCH B6 > ' TEXTASLONGVARCHAR B7 > ' UNKNOWNSASLONGVARCHAR B8 > ' BOOLSASCHAR B9 > ' PARSE C0 > ' CANCELASFREESTMT C1 > ' EXTRASYSTABLEPREFIXES C2 > > 'Connection string > CONNECTIONSTRING: > strConnInfo = "ODBC;Driver=" & Driver & ";Server=" & SERVER & ";Port=" & PORT & ";Database=" & DATABASE & ";" > strConnUserPass = "Uid=" & USERNAME & ";Pwd=" & PASSWORD & ";" > strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=" & A6 & ";A7=100;A8=" & SOCKET & ";A9=1;" & _ > "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=0;" & _ > "C0=0;C1=0;C2=dd_;" > > There are some options I don't understand, for example "Parse statements" and "Fetch/Declare" and don't know how wouldit affect performances... > Also, there is a problem with the limited length of connection string that Access can handle, so I have a problem thatI can't input some bigger usernames and passwords. Therefore I would like to remove some unneccessary parameters, ifpossible. Which parameters could be safely removed? > > Thanks in advance, > > Zlatko > >
Attachment
Where does the Access connection string limitation happen? The meta data for linked tables is stored in system table MySysObjects, where the Connection is a MEMO field type. That's a 65,536 character field. Zlatko Matić wrote: > Hello. > > Could you, please, help me to optimize my connection string (MS Access 2003, PostgreSQL 8.1.1. and psqlodbc-08_01_0102)? > > ' PG_ODBC_PARAMETER ACCESS_PARAMETER > ' ********************************************* > ' READONLY A0 > ' PROTOCOL A1 > ' FAKEOIDINDEX A2 'A2 must be 0 unless A3=1 > ' SHOWOIDCOLUMN A3 > ' ROWVERSIONING A4 > ' SHOWSYSTEMTABLES A5 > ' CONNSETTINGS A6 > ' FETCH A7 > ' SOCKET A8 > ' UNKNOWNSIZES A9 ' range [0-2] > ' MAXVARCHARSIZE B0 > ' MAXLONGVARCHARSIZE B1 > ' DEBUG B2 > ' COMMLOG B3 > ' OPTIMIZER B4 ' note that 1 = _cancel_ generic optimizer... > ' KSQO B5 > ' USEDECLAREFETCH B6 > ' TEXTASLONGVARCHAR B7 > ' UNKNOWNSASLONGVARCHAR B8 > ' BOOLSASCHAR B9 > ' PARSE C0 > ' CANCELASFREESTMT C1 > ' EXTRASYSTABLEPREFIXES C2 > > 'Connection string > CONNECTIONSTRING: > strConnInfo = "ODBC;Driver=" & Driver & ";Server=" & SERVER & ";Port=" & PORT & ";Database=" & DATABASE & ";" > strConnUserPass = "Uid=" & USERNAME & ";Pwd=" & PASSWORD & ";" > strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=" & A6 & ";A7=100;A8=" & SOCKET & ";A9=1;" & _ > "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=0;" & _ > "C0=0;C1=0;C2=dd_;" > > There are some options I don't understand, for example "Parse statements" and "Fetch/Declare" and don't know how wouldit affect performances... > Also, there is a problem with the limited length of connection string that Access can handle, so I have a problem thatI can't input some bigger usernames and passwords. Therefore I would like to remove some unneccessary parameters, ifpossible. Which parameters could be safely removed? > > Thanks in advance, > > Zlatko > >
Attachment
Thanks. I removed most options, except some really important (strConnParms = "A0=0;A4=1;B7=1;B9=0;") ----- Original Message ----- From: "Campbell, Greg" <greg.campbell@us.michelin.com> To: "Zlatko Matić" <zlatko.matic1@sb.t-com.hr> Cc: <pgsql-odbc@postgresql.org> Sent: Saturday, December 24, 2005 1:17 AM Subject: Re: [ODBC] ODBC connection string, MS Access Have you tried not specifying anything other than Driver, Server, Database, UID, PWD? Most items have a default like PORT defaults to 5432. Zlatko Matić wrote: > Hello. > > Could you, please, help me to optimize my connection string (MS Access > 2003, PostgreSQL 8.1.1. and psqlodbc-08_01_0102)? > > ' PG_ODBC_PARAMETER ACCESS_PARAMETER > ' ********************************************* > ' READONLY A0 > ' PROTOCOL A1 > ' FAKEOIDINDEX A2 'A2 must be 0 unless A3=1 > ' SHOWOIDCOLUMN A3 > ' ROWVERSIONING A4 > ' SHOWSYSTEMTABLES A5 > ' CONNSETTINGS A6 > ' FETCH A7 > ' SOCKET A8 > ' UNKNOWNSIZES A9 ' range [0-2] > ' MAXVARCHARSIZE B0 > ' MAXLONGVARCHARSIZE B1 > ' DEBUG B2 > ' COMMLOG B3 > ' OPTIMIZER B4 ' note that 1 = _cancel_ generic > optimizer... > ' KSQO B5 > ' USEDECLAREFETCH B6 > ' TEXTASLONGVARCHAR B7 > ' UNKNOWNSASLONGVARCHAR B8 > ' BOOLSASCHAR B9 > ' PARSE C0 > ' CANCELASFREESTMT C1 > ' EXTRASYSTABLEPREFIXES C2 > > 'Connection string > CONNECTIONSTRING: > strConnInfo = "ODBC;Driver=" & Driver & ";Server=" & SERVER & ";Port=" & > PORT & ";Database=" & DATABASE & ";" > strConnUserPass = "Uid=" & USERNAME & ";Pwd=" & PASSWORD & ";" > strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=" & A6 & ";A7=100;A8=" > & SOCKET & ";A9=1;" & _ > "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=0;" & _ > "C0=0;C1=0;C2=dd_;" > > There are some options I don't understand, for example "Parse statements" > and "Fetch/Declare" and don't know how would it affect performances... > Also, there is a problem with the limited length of connection string that > Access can handle, so I have a problem that I can't input some bigger > usernames and passwords. Therefore I would like to remove some > unneccessary parameters, if possible. Which parameters could be safely > removed? > > Thanks in advance, > > Zlatko > > -------------------------------------------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
I think I read somewhere it can be only 255 characters long...but I'm not sure... ----- Original Message ----- From: "Campbell, Greg" <greg.campbell@us.michelin.com> To: "Zlatko Matić" <zlatko.matic1@sb.t-com.hr> Cc: <pgsql-odbc@postgresql.org> Sent: Saturday, December 24, 2005 1:22 AM Subject: Re: [ODBC] ODBC connection string, MS Access Where does the Access connection string limitation happen? The meta data for linked tables is stored in system table MySysObjects, where the Connection is a MEMO field type. That's a 65,536 character field. Zlatko Matić wrote: > Hello. > > Could you, please, help me to optimize my connection string (MS Access > 2003, PostgreSQL 8.1.1. and psqlodbc-08_01_0102)? > > ' PG_ODBC_PARAMETER ACCESS_PARAMETER > ' ********************************************* > ' READONLY A0 > ' PROTOCOL A1 > ' FAKEOIDINDEX A2 'A2 must be 0 unless A3=1 > ' SHOWOIDCOLUMN A3 > ' ROWVERSIONING A4 > ' SHOWSYSTEMTABLES A5 > ' CONNSETTINGS A6 > ' FETCH A7 > ' SOCKET A8 > ' UNKNOWNSIZES A9 ' range [0-2] > ' MAXVARCHARSIZE B0 > ' MAXLONGVARCHARSIZE B1 > ' DEBUG B2 > ' COMMLOG B3 > ' OPTIMIZER B4 ' note that 1 = _cancel_ generic > optimizer... > ' KSQO B5 > ' USEDECLAREFETCH B6 > ' TEXTASLONGVARCHAR B7 > ' UNKNOWNSASLONGVARCHAR B8 > ' BOOLSASCHAR B9 > ' PARSE C0 > ' CANCELASFREESTMT C1 > ' EXTRASYSTABLEPREFIXES C2 > > 'Connection string > CONNECTIONSTRING: > strConnInfo = "ODBC;Driver=" & Driver & ";Server=" & SERVER & ";Port=" & > PORT & ";Database=" & DATABASE & ";" > strConnUserPass = "Uid=" & USERNAME & ";Pwd=" & PASSWORD & ";" > strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=" & A6 & ";A7=100;A8=" > & SOCKET & ";A9=1;" & _ > "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=0;" & _ > "C0=0;C1=0;C2=dd_;" > > There are some options I don't understand, for example "Parse statements" > and "Fetch/Declare" and don't know how would it affect performances... > Also, there is a problem with the limited length of connection string that > Access can handle, so I have a problem that I can't input some bigger > usernames and passwords. Therefore I would like to remove some > unneccessary parameters, if possible. Which parameters could be safely > removed? > > Thanks in advance, > > Zlatko > >