ODBC connection string, MS Access

This is a multi-part message in MIME format.

------=_NextPart_000_0003_01C606DE.1B4D1260
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

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=3D1
' 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 =3D _cancel_ generic =
optimizer...
' KSQO B5
' USEDECLAREFETCH B6
' TEXTASLONGVARCHAR B7
' UNKNOWNSASLONGVARCHAR B8
' BOOLSASCHAR B9
' PARSE C0
' CANCELASFREESTMT C1
' EXTRASYSTABLEPREFIXES C2

'Connection string
CONNECTIONSTRING:
strConnInfo =3D "ODBC;Driver=3D" & Driver & ";Server=3D" & SERVER & =
";Port=3D" & PORT & ";Database=3D" & DATABASE & ";"
strConnUserPass =3D "Uid=3D" & USERNAME & ";Pwd=3D" & PASSWORD & ";"
strConnParms =3D "A0=3D0;A1=3D6.4;A2=3D0;A3=3D0;A4=3D1;A5=3D0;A6=3D" & =
A6 & ";A7=3D100;A8=3D" & SOCKET & ";A9=3D1;" & _
"B0=3D254;B1=3D8190;B2=3D0;B3=3D0;B4=3D1;B5=3D1;B6=3D0;B7=3D 1;B8=3D0;B9=3D=
0;" & _
"C0=3D0;C1=3D0;C2=3Ddd_;"

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


------=_NextPart_000_0003_01C606DE.1B4D1260
Content-Type: text/html;
charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-2">

Re: ODBC connection string, MS Access

Thu, Dec 22, 2005 at 09:57:23AM +0100, Zlatko Mati=E6 napsal(a):
> Hello.
>=20
> Could you, please, help me to optimize my connection string (MS Access =
2003, PostgreSQL 8.1.1. and psqlodbc-08_01_0102)?
>=20
> ' 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 i=
t.

> ' CANCELASFREESTMT C1
> ' EXTRASYSTABLEPREFIXES C2
>=20
> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: ODBC connection string, MS Access

This is a multi-part message in MIME format.
--------------030703000605050009080308
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: quoted-printable

Have you tried not specifying anything other than Driver, Server, Databas=
e, UID, PWD?

Most items have a default like PORT defaults to 5432.



Zlatko Mati=E6 wrote:
> Hello.
>=20
> Could you, please, help me to optimize my connection string (MS Access =
2003, PostgreSQL 8.1.1. and psqlodbc-08_01_0102)?
>=20
> ' PG_ODBC_PARAMETER ACCESS_PARAMETER
> ' *********************************************
> ' READONLY A0
> ' PROTOCOL A1
> ' FAKEOIDINDEX A2 'A2 must be 0 unless A3=3D1
> ' 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 =3D _cancel_ generic =
optimizer...
> ' KSQO B5
> ' USEDECLAREFETCH B6
> ' TEXTASLONGVARCHAR B7
> ' UNKNOWNSASLONGVARCHAR B8
> ' BOOLSASCHAR B9
> ' PARSE C0
> ' CANCELASFREESTMT C1
> ' EXTRASYSTABLEPREFIXES C2
>=20
> 'Connection string
> CONNECTIONSTRING:
> strConnInfo =3D "ODBC;Driver=3D" & Driver & ";Server=3D" & SERVER & ";=
Port=3D" & PORT & ";Database=3D" & DATABASE & ";"
> strConnUserPass =3D "Uid=3D" & USERNAME & ";Pwd=3D" & PASSWORD & ";"
> strConnParms =3D "A0=3D0;A1=3D6.4;A2=3D0;A3=3D0;A4=3D1;A5=3D0;A6=3D" &=
A6 & ";A7=3D100;A8=3D" & SOCKET & ";A9=3D1;" & _
> "B0=3D254;B1=3D8190;B2=3D0;B3=3D0;B4=3D1;B5=3D1;B6=3D0;B7=3D 1;B8=3D0;B9=
=3D0;" & _
> "C0=3D0;C1=3D0;C2=3Ddd_;"
>=20
> There are some options I don't understand, for example "Parse statement=
s" and "Fetch/Declare" and don't know how would it affect performances...
> Also, there is a problem with the limited length of connection string t=
hat 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 unneccess=
ary parameters, if possible. Which parameters could be safely removed?
>=20
> Thanks in advance,
>=20
> Zlatko
>=20
>=20

--------------030703000605050009080308
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------030703000605050009080308
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--------------030703000605050009080308--

Re: ODBC connection string, MS Access

This is a multi-part message in MIME format.
--------------000900080107050102020905
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: quoted-printable

Where does the Access connection string limitation happen?

The meta data for linked tables is stored in system table MySysObjects, w=
here the Connection is a MEMO=20
field type. That's a 65,536 character field.


Zlatko Mati=E6 wrote:

> Hello.
>=20
> Could you, please, help me to optimize my connection string (MS Access =
2003, PostgreSQL 8.1.1. and psqlodbc-08_01_0102)?
>=20
> ' PG_ODBC_PARAMETER ACCESS_PARAMETER
> ' *********************************************
> ' READONLY A0
> ' PROTOCOL A1
> ' FAKEOIDINDEX A2 'A2 must be 0 unless A3=3D1
> ' 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 =3D _cancel_ generic =
optimizer...
> ' KSQO B5
> ' USEDECLAREFETCH B6
> ' TEXTASLONGVARCHAR B7
> ' UNKNOWNSASLONGVARCHAR B8
> ' BOOLSASCHAR B9
> ' PARSE C0
> ' CANCELASFREESTMT C1
> ' EXTRASYSTABLEPREFIXES C2
>=20
> 'Connection string
> CONNECTIONSTRING:
> strConnInfo =3D "ODBC;Driver=3D" & Driver & ";Server=3D" & SERVER & ";=
Port=3D" & PORT & ";Database=3D" & DATABASE & ";"
> strConnUserPass =3D "Uid=3D" & USERNAME & ";Pwd=3D" & PASSWORD & ";"
> strConnParms =3D "A0=3D0;A1=3D6.4;A2=3D0;A3=3D0;A4=3D1;A5=3D0;A6=3D" &=
A6 & ";A7=3D100;A8=3D" & SOCKET & ";A9=3D1;" & _
> "B0=3D254;B1=3D8190;B2=3D0;B3=3D0;B4=3D1;B5=3D1;B6=3D0;B7=3D 1;B8=3D0;B9=
=3D0;" & _
> "C0=3D0;C1=3D0;C2=3Ddd_;"
>=20
> There are some options I don't understand, for example "Parse statement=
s" and "Fetch/Declare" and don't know how would it affect performances...
> Also, there is a problem with the limited length of connection string t=
hat 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 unneccess=
ary parameters, if possible. Which parameters could be safely removed?
>=20
> Thanks in advance,
>=20
> Zlatko
>=20
>=20

--------------000900080107050102020905
Content-Type: text/x-vcard; charset=utf-8;
name="greg.campbell.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="greg.campbell.vcf"

begin:vcard
fn:Greg Campbell
n:Campbell;Greg
org:Michelin North America - US5 Lexington;ENG-ASE
email;internet:greg.campbell@us.michelin.com
title:ASE Systems Engineer
tel;work:803-951-5561/x75561
x-mozilla-html:FALSE
version:2.1
end:vcard


--------------000900080107050102020905
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--------------000900080107050102020905--

Re: ODBC connection string, MS Access

I think I read somewhere it can be only 255 characters long...but I'm not=
=20
sure...

----- Original Message -----=20
From: "Campbell, Greg"
To: "Zlatko Mati=E6"
Cc:
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,=20
where the Connection is a MEMO
field type. That's a 65,536 character field.


Zlatko Mati=E6 wrote:

> Hello.
>
> Could you, please, help me to optimize my connection string (MS Access=20
> 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=3D1
> ' 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 =3D _cancel_ generic=20
> optimizer...
> ' KSQO B5
> ' USEDECLAREFETCH B6
> ' TEXTASLONGVARCHAR B7
> ' UNKNOWNSASLONGVARCHAR B8
> ' BOOLSASCHAR B9
> ' PARSE C0
> ' CANCELASFREESTMT C1
> ' EXTRASYSTABLEPREFIXES C2
>
> 'Connection string
> CONNECTIONSTRING:
> strConnInfo =3D "ODBC;Driver=3D" & Driver & ";Server=3D" & SERVER & ";Po=
rt=3D" &=20
> PORT & ";Database=3D" & DATABASE & ";"
> strConnUserPass =3D "Uid=3D" & USERNAME & ";Pwd=3D" & PASSWORD & ";"
> strConnParms =3D "A0=3D0;A1=3D6.4;A2=3D0;A3=3D0;A4=3D1;A5=3D0;A6=3D" & A=
6 & ";A7=3D100;A8=3D"=20
> & SOCKET & ";A9=3D1;" & _
> "B0=3D254;B1=3D8190;B2=3D0;B3=3D0;B4=3D1;B5=3D1;B6=3D0;B7=3D 1;B8=3D0;B9=
=3D0;" & _
> "C0=3D0;C1=3D0;C2=3Ddd_;"
>
> There are some options I don't understand, for example "Parse statements"=
=20
> and "Fetch/Declare" and don't know how would it affect performances...
> Also, there is a problem with the limited length of connection string tha=
t=20
> Access can handle, so I have a problem that I can't input some bigger=20
> usernames and passwords. Therefore I would like to remove some=20
> unneccessary parameters, if possible. Which parameters could be safely=20
> removed?
>
> Thanks in advance,
>
> Zlatko
>
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: ODBC connection string, MS Access

Thanks. I removed most options, except some really important (strConnParms =
=
"A0=3D0;A4=3D1;B7=3D1;B9=3D0;")

----- Original Message -----=20
From: "Campbell, Greg"
To: "Zlatko Mati=E6"
Cc:
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,=
=20
UID, PWD?

Most items have a default like PORT defaults to 5432.



Zlatko Mati=E6 wrote:
> Hello.
>
> Could you, please, help me to optimize my connection string (MS Access=20
> 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=3D1
> ' 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 =3D _cancel_ generic=20
> optimizer...
> ' KSQO B5
> ' USEDECLAREFETCH B6
> ' TEXTASLONGVARCHAR B7
> ' UNKNOWNSASLONGVARCHAR B8
> ' BOOLSASCHAR B9
> ' PARSE C0
> ' CANCELASFREESTMT C1
> ' EXTRASYSTABLEPREFIXES C2
>
> 'Connection string
> CONNECTIONSTRING:
> strConnInfo =3D "ODBC;Driver=3D" & Driver & ";Server=3D" & SERVER & ";Po=
rt=3D" &=20
> PORT & ";Database=3D" & DATABASE & ";"
> strConnUserPass =3D "Uid=3D" & USERNAME & ";Pwd=3D" & PASSWORD & ";"
> strConnParms =3D "A0=3D0;A1=3D6.4;A2=3D0;A3=3D0;A4=3D1;A5=3D0;A6=3D" & A=
6 & ";A7=3D100;A8=3D"=20
> & SOCKET & ";A9=3D1;" & _
> "B0=3D254;B1=3D8190;B2=3D0;B3=3D0;B4=3D1;B5=3D1;B6=3D0;B7=3D 1;B8=3D0;B9=
=3D0;" & _
> "C0=3D0;C1=3D0;C2=3Ddd_;"
>
> There are some options I don't understand, for example "Parse statements"=
=20
> and "Fetch/Declare" and don't know how would it affect performances...
> Also, there is a problem with the limited length of connection string tha=
t=20
> Access can handle, so I have a problem that I can't input some bigger=20
> usernames and passwords. Therefore I would like to remove some=20
> unneccessary parameters, if possible. Which parameters could be safely=20
> removed?
>
> Thanks in advance,
>
> Zlatko
>
>



------------------------------------------------------------ ---------------=
-----



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster