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_;"
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 C2There 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:
Show quoted text
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
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:
Show quoted text
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
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:
Show quoted text
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
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