Thread: ODBC Driver Options

ODBC Driver Options

From
"Nicola Tiling"
Date:
Hi

I have installed psqlodbc-07_03_0200 for Windows (under XP). I use it for M$ Access as a frontend of a pg database on a
FreeBSD
Server.

Can anyone tell me where I can find any documents about the options of the driver? I have searched under
http://gborg.postgresql.org/project/psqlodbc/projdisplay.php and http://techdocs.postgresql.org. The document
"ftp://ftp.postgresql.org/pub/odbc/psqlodbc_confighelp.html" doesn't exist anymore.

I would be lucky to know what

- Use Declare/Fetch
- Parse Statements
- Cancel as FreeStmt(Exp)
- Row Versioning
- Disallow Premature
- Server side prepare

mean and what are the effects of them?

Excuse my bad english
Regards
Nicola



Re: ODBC Driver Options

From
"Dave Page"
Date:
Hi,

It's not as up to date as it should be, but most options are documented
at http://gborg.postgresql.org/project/psqlodbc/genpage.php?doc-config

Regards, Dave.

> -----Original Message-----
> From: Nicola Tiling [mailto:nti@w4w.net]
> Sent: 17 May 2004 11:12
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] ODBC Driver Options
>
> Hi
>
> I have installed psqlodbc-07_03_0200 for Windows (under XP).
> I use it for M$ Access as a frontend of a pg database on a
> FreeBSD Server.
>
> Can anyone tell me where I can find any documents about the
> options of the driver? I have searched under
> http://gborg.postgresql.org/project/psqlodbc/projdisplay.php
> and http://techdocs.postgresql.org. The document
> "ftp://ftp.postgresql.org/pub/odbc/psqlodbc_confighelp.html"
> doesn't exist anymore.
>
> I would be lucky to know what
>
> - Use Declare/Fetch
> - Parse Statements
> - Cancel as FreeStmt(Exp)
> - Row Versioning
> - Disallow Premature
> - Server side prepare
>
> mean and what are the effects of them?
>
> Excuse my bad english
> Regards
> Nicola
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index
> scan if your
>       joining column's datatypes do not match
>

Re: ODBC Driver Options

From
Jeff Eckermann
Date:
--- Dave Page <dpage@vale-housing.co.uk> wrote:
> Hi,
>
> It's not as up to date as it should be, but most
> options are documented
> at
>
http://gborg.postgresql.org/project/psqlodbc/genpage.php?doc-config
>
> Regards, Dave.
>
> > -----Original Message-----
> > From: Nicola Tiling [mailto:nti@w4w.net]
> > Sent: 17 May 2004 11:12
> > To: pgsql-odbc@postgresql.org
> > Subject: [ODBC] ODBC Driver Options
> >
> > Hi
> >
> > I have installed psqlodbc-07_03_0200 for Windows
> (under XP).
> > I use it for M$ Access as a frontend of a pg
> database on a
> > FreeBSD Server.
> >
> > Can anyone tell me where I can find any documents
> about the
> > options of the driver? I have searched under
> >
>
http://gborg.postgresql.org/project/psqlodbc/projdisplay.php
>
> > and http://techdocs.postgresql.org. The document
> >
>
"ftp://ftp.postgresql.org/pub/odbc/psqlodbc_confighelp.html"
>
> > doesn't exist anymore.
> >
> > I would be lucky to know what
> >
> > - Use Declare/Fetch
> > - Parse Statements
> > - Cancel as FreeStmt(Exp)
> > - Row Versioning
> > - Disallow Premature
> > - Server side prepare
> >
> > mean and what are the effects of them?

Since you are using MS Access, you will probably avoid
some problems by setting "row versioning" to true.
Also, some people have reported big performance gains
from setting "use declare/fetch" to true, so you may
want to test that.  I am not aware that any of those
other options will give you any benefit with other
than default values.

> >
> > Excuse my bad english
> > Regards
> > Nicola
> >
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to
> choose an index
> > scan if your
> >       joining column's datatypes do not match
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
>       joining column's datatypes do not match





__________________________________
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/

Re: ODBC Driver Options

From
Andrew Ayers
Date:
Jeff Eckermann wrote:
> --- Dave Page <dpage@vale-housing.co.uk> wrote:
>
>>Hi,
>>
>>It's not as up to date as it should be, but most
>>options are documented
>>at
>>
>
> http://gborg.postgresql.org/project/psqlodbc/genpage.php?doc-config
>
>>Regards, Dave.
>>
>>
>>>-----Original Message-----
>>>From: Nicola Tiling [mailto:nti@w4w.net]
>>>Sent: 17 May 2004 11:12
>>>To: pgsql-odbc@postgresql.org
>>>Subject: [ODBC] ODBC Driver Options
>>>
>>>Hi
>>>
>>>I have installed psqlodbc-07_03_0200 for Windows
>>
>>(under XP).
>>
>>>I use it for M$ Access as a frontend of a pg
>>
>>database on a
>>
>>>FreeBSD Server.
>>>
>>>Can anyone tell me where I can find any documents
>>
>>about the
>>
>>>options of the driver? I have searched under
>>>
>>
> http://gborg.postgresql.org/project/psqlodbc/projdisplay.php
>
>>>and http://techdocs.postgresql.org. The document
>>>
>>
> "ftp://ftp.postgresql.org/pub/odbc/psqlodbc_confighelp.html"
>
>>>doesn't exist anymore.
>>>
>>>I would be lucky to know what
>>>
>>>- Use Declare/Fetch
>>>- Parse Statements
>>>- Cancel as FreeStmt(Exp)
>>>- Row Versioning
>>>- Disallow Premature
>>>- Server side prepare
>>>
>>>mean and what are the effects of them?
>
>
> Since you are using MS Access, you will probably avoid
> some problems by setting "row versioning" to true.
> Also, some people have reported big performance gains
> from setting "use declare/fetch" to true, so you may
> want to test that.  I am not aware that any of those
> other options will give you any benefit with other
> than default values.

A related question would be what the "name" of these variables are if
you are passing them in a DSN-less connection. For example, I was having
problems in my VB6 application where if I didn't turn off Keyset Query
Optimization I would get errors. This is easy to do in the ODBC setup,
but in order to do it via a DSN-less connection, you have to pass
"ksqo=;" in the connection string.

In this case, it is fairly easy to guess - but what about others, like
"use declare/fetch"? I would love to try this out in my application, but
without knowing what variable to pass - it would take a little to track
down (likely a before/after snapshot of a log, while using a DSN
connection and turning off/on the flag to see what changes in the log
entries).

A list of these variables and what their settings can be would be doubly
helpful...

Andrew Ayers
Phoenix, Arizona

>>>Excuse my bad english
>>>Regards
>>>Nicola
>>>
>>>
>>>
>>>---------------------------(end of
>>>broadcast)---------------------------
>>>TIP 9: the planner will ignore your desire to
>>
>>choose an index
>>
>>>scan if your
>>>      joining column's datatypes do not match
>>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 9: the planner will ignore your desire to choose
>>an index scan if your
>>      joining column's datatypes do not match
>
>
>
>
>
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! - Internet access at a great low price.
> http://promo.yahoo.com/sbc/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you. 

Re: ODBC Driver Options

From
"Nicola Tiling"
Date:
> Since you are using MS Access, you will probably avoid some
> problems by setting "row versioning" to true.

I'm using Access 2003 and think there are no problems with "row versioning"

> Also, some people have reported big performance gains from
> setting "use declare/fetch" to true,

On the contrary it make my Access fall asleep

Thanks a lot for the links and explanations

Nicola



Re: ODBC Driver Options

From
"Nicola Tiling"
Date:
Have a look here

http://www.flatmtn.com/computer/Linux-PostgreSQL.html

I think "use declare/fetch" is "FETCH"


'   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  'must be a value, blank is A6=;
'   FETCH                       A7  'must be a value, try 100
'   SOCKET                      A8  'default 4096
'   UNKNOWNSIZES                A9  ' range [0-2]
'   MAXVARCHARSIZE              B0  'must be a value, default 254
'   MAXLONGVARCHARSIZE          B1
'   DEBUG                       B2
'   COMMLOG                     B3
'   OPTIMIZER                   B4  ' note that 1 = _cancel_ generic optimizer...
'   KSQO                        B5
'   USEDECLAREFETCH             B6
'   TEXTASLONGVARCHAR           B7
'   UNKNOWNSASLONGVARCHAR       B8
'   BOOLSASCHAR                 B9
'   INVERSEBOOL?                BI  '"true is -1"?, 0=checked?
'   PARSE                       C0
'   CANCELASFREESTMT            C1
'   EXTRASYSTABLEPREFIXES       C2  'must be a value, default dd_




Re: ODBC Driver Options

From
Andrew Ayers
Date:
Nicola Tiling wrote:
> Have a look here
>
> http://www.flatmtn.com/computer/Linux-PostgreSQL.html
>
> I think "use declare/fetch" is "FETCH"

I will give it a shot and play around with it - thank you for the
reference!!!

Andrew Ayers
Phoenix, Arizona

>
>
> '   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  'must be a value, blank is A6=;
> '   FETCH                       A7  'must be a value, try 100
> '   SOCKET                      A8  'default 4096
> '   UNKNOWNSIZES                A9  ' range [0-2]
> '   MAXVARCHARSIZE              B0  'must be a value, default 254
> '   MAXLONGVARCHARSIZE          B1
> '   DEBUG                       B2
> '   COMMLOG                     B3
> '   OPTIMIZER                   B4  ' note that 1 = _cancel_ generic optimizer...
> '   KSQO                        B5
> '   USEDECLAREFETCH             B6
> '   TEXTASLONGVARCHAR           B7
> '   UNKNOWNSASLONGVARCHAR       B8
> '   BOOLSASCHAR                 B9
> '   INVERSEBOOL?                BI  '"true is -1"?, 0=checked?
> '   PARSE                       C0
> '   CANCELASFREESTMT            C1
> '   EXTRASYSTABLEPREFIXES       C2  'must be a value, default dd_
>
>
>
>


-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you. 

Re: ODBC Driver Options

From
Jeff Eckermann
Date:
A neat little trick that will work with any ODBC
driver:

1. Create a file (any name) with a .udl extension.
2. Run the file
3. In the dialog that comes up, choose "use
connections string", and "build"; then follow the
prompts.
4. When done, open the file with a text editor.  You
will see a full connection string, with the names of
all of the driver options.

Thanks to the poster, whose name I cannot recall, who
suggested this.

--- Andrew Ayers <aayers@eldocomp.com> wrote:
> Nicola Tiling wrote:
> > Have a look here
> >
> >
>
http://www.flatmtn.com/computer/Linux-PostgreSQL.html
> >
> > I think "use declare/fetch" is "FETCH"
>
> I will give it a shot and play around with it -
> thank you for the
> reference!!!
>
> Andrew Ayers
> Phoenix, Arizona
>
> >
> >
> > '   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  'must be a
> value, blank is A6=;
> > '   FETCH                       A7  'must be a
> value, try 100
> > '   SOCKET                      A8  'default 4096
> > '   UNKNOWNSIZES                A9  ' range [0-2]
> > '   MAXVARCHARSIZE              B0  'must be a
> value, default 254
> > '   MAXLONGVARCHARSIZE          B1
> > '   DEBUG                       B2
> > '   COMMLOG                     B3
> > '   OPTIMIZER                   B4  ' note that 1
> = _cancel_ generic optimizer...
> > '   KSQO                        B5
> > '   USEDECLAREFETCH             B6
> > '   TEXTASLONGVARCHAR           B7
> > '   UNKNOWNSASLONGVARCHAR       B8
> > '   BOOLSASCHAR                 B9
> > '   INVERSEBOOL?                BI  '"true is
> -1"?, 0=checked?
> > '   PARSE                       C0
> > '   CANCELASFREESTMT            C1
> > '   EXTRASYSTABLEPREFIXES       C2  'must be a
> value, default dd_
> >
> >
> >
> >
>
>
> -- CONFIDENTIALITY NOTICE --
>
> This message is intended for the sole use of the
> individual and entity to whom it is addressed, and
> may contain information that is privileged,
> confidential and exempt from disclosure under
> applicable law. If you are not the intended
> addressee, nor authorized to receive for the
> intended addressee, you are hereby notified that you
> may not use, copy, disclose or distribute to anyone
> the message or any information contained in the
> message. If you have received this message in error,
> please immediately advise the sender by reply email,
> and delete the message. Thank you.





__________________________________
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/

Re: ODBC Driver Options

From
Andrew Ayers
Date:
Jeff Eckermann wrote:
> A neat little trick that will work with any ODBC
> driver:
>
> 1. Create a file (any name) with a .udl extension.
> 2. Run the file
> 3. In the dialog that comes up, choose "use
> connections string", and "build"; then follow the
> prompts.
> 4. When done, open the file with a text editor.  You
> will see a full connection string, with the names of
> all of the driver options.
>
> Thanks to the poster, whose name I cannot recall, who
> suggested this.

Cool - this works great - thanks!

BTW - the "Use declare/fetch" option for the string was:

UseDeclareFetch=1 (or 0 to turn off)

[smacks head against desk - walks away in shame]

Andrew

> --- Andrew Ayers <aayers@eldocomp.com> wrote:
>
>>Nicola Tiling wrote:
>>
>>>Have a look here
>>>
>>>
>>
> http://www.flatmtn.com/computer/Linux-PostgreSQL.html
>
>>>I think "use declare/fetch" is "FETCH"
>>
>>I will give it a shot and play around with it -
>>thank you for the
>>reference!!!
>>
>>Andrew Ayers
>>Phoenix, Arizona
>>
>>
>>>
>>>'   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  'must be a
>>
>>value, blank is A6=;
>>
>>>'   FETCH                       A7  'must be a
>>
>>value, try 100
>>
>>>'   SOCKET                      A8  'default 4096
>>>'   UNKNOWNSIZES                A9  ' range [0-2]
>>>'   MAXVARCHARSIZE              B0  'must be a
>>
>>value, default 254
>>
>>>'   MAXLONGVARCHARSIZE          B1
>>>'   DEBUG                       B2
>>>'   COMMLOG                     B3
>>>'   OPTIMIZER                   B4  ' note that 1
>>
>>= _cancel_ generic optimizer...
>>
>>>'   KSQO                        B5
>>>'   USEDECLAREFETCH             B6
>>>'   TEXTASLONGVARCHAR           B7
>>>'   UNKNOWNSASLONGVARCHAR       B8
>>>'   BOOLSASCHAR                 B9
>>>'   INVERSEBOOL?                BI  '"true is
>>
>>-1"?, 0=checked?
>>
>>>'   PARSE                       C0
>>>'   CANCELASFREESTMT            C1
>>>'   EXTRASYSTABLEPREFIXES       C2  'must be a
>>
>>value, default dd_

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.