Обсуждение: ODBC connection string, MS Access

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
>
>

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
>
>

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
>
>