Thread: 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.