Обсуждение: Setting search_path in ODBC connection string

How to set default search_path in connection string ?

I have tables in non-public schema and need to set this to allow ODBC
application to find tables without using schema prefix.

Andrus.

Please set in odbc.ini.

ConnSettings    = set search_path = Others,public;

Regards,
Hiroshi Saito

----- Original Message -----
From: "Andrus" <kobruleht2@hot.ee>
To: <pgsql-odbc@postgresql.org>
Sent: Wednesday, November 05, 2008 11:24 PM
Subject: [ODBC] Setting search_path in ODBC connection string


> How to set default search_path in connection string ?
>
> I have tables in non-public schema and need to set this to allow ODBC
> application to find tables without using schema prefix.
>
> Andrus.
>
>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc

Hiroshi,

> Please set in odbc.ini.
>
> ConnSettings    = set search_path = Others,public;

Thank you.
I need to change this setting at runtime.
When connection is re-created, VFP creates it using previous connection
string automatically.
It is not possible to force VFP to send SET SEARCH_PATH command
automatically on re-connect.

Using odbc.ini requires parsing odbc.ini file in c:\windows directory.
Different connections require different default seach paths. This settings
seems to be global for all connections.
How to force driver to use different search_path's for different connections
?

Any other idea ?
How to force ODBC driver to issue SET SEARCH_PATH  automatically ? Maybe
there is some connection option which can be used ?

Andrus.

Hi Andrus-san.

Sorry...late reaction.

Ahh, there is a part set up into Dialog in windows. Then, It is automatically
used at the time of connection. I'm not checking by the reason for not using
VFP.  However, It is used when operating it by DSN.

Regards,
Hiroshi Saito

----- Original Message -----
From: "Andrus" <kobruleht2@hot.ee>


> Hiroshi,
>
>> Please set in odbc.ini.
>>
>> ConnSettings    = set search_path = Others,public;
>
> Thank you.
> I need to change this setting at runtime.
> When connection is re-created, VFP creates it using previous connection
> string automatically.
> It is not possible to force VFP to send SET SEARCH_PATH command
> automatically on re-connect.
>
> Using odbc.ini requires parsing odbc.ini file in c:\windows directory.
> Different connections require different default seach paths. This settings
> seems to be global for all connections.
> How to force driver to use different search_path's for different connections
> ?
>
> Any other idea ?
> How to force ODBC driver to issue SET SEARCH_PATH  automatically ? Maybe
> there is some connection option which can be used ?
>
> Andrus.
>
>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc

Hiroshi,

> Ahh, there is a part set up into Dialog in windows. Then, It is
> automatically used at the time of connection. I'm not checking by the
> reason for not using VFP.  However, It is used when operating it by DSN.

Thank you. I want to do this without user interaction.
Using this requires my application to create programmatically new DSN for
every possible search_path combination. This reqires admin privileges in
windows.
Is it best way ?

I also discovered that

ConnSettings=set search_path to company1,public

in connection string seems to solve this issue without requiring DSN usage.

Is it OK to use ConnSettings and not use DSNs ?

Andrus.

Hi.

Yes, can use DSN less.

Sample of VBA) -- not VFP

  szConnect = "DRIVER=" & txtDriver & _
               ";DATABASE=" & txtDB & _
               ";UID=" & txtUID & _
               ";SSLMODE=allow" & _
               ";SERVER=" & txtServer & _
               ";PORT=" & txtPort & _
               ";PWD=" & txtPWD & _
               ";A6=set search_path to company1,public;"

Please see,
http://psqlodbc.projects.postgresql.org/config-opt.html

Regards,
Hiroshi Saito

----- Original Message -----
From: "Andrus" <kobruleht2@hot.ee>


> Hiroshi,
>
>> Ahh, there is a part set up into Dialog in windows. Then, It is
>> automatically used at the time of connection. I'm not checking by the
>> reason for not using VFP.  However, It is used when operating it by DSN.
>
> Thank you. I want to do this without user interaction.
> Using this requires my application to create programmatically new DSN for
> every possible search_path combination. This reqires admin privileges in
> windows.
> Is it best way ?
>
> I also discovered that
>
> ConnSettings=set search_path to company1,public
>
> in connection string seems to solve this issue without requiring DSN usage.
>
> Is it OK to use ConnSettings and not use DSNs ?
>
> Andrus.
>
>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc

Hiroshi,

> Yes, can use DSN less.
>
> Sample of VBA) -- not VFP

Thank you.
I changed my application to use this.

Andrus.