Thread: ODBC: how to change search_path in DSN?
I would like to create an ODBC DSN for a user to export files rom Acccess to a safe area. Right now, all tables are being dumped to public by default. In order to use the one-click export option for Access, I would like to have the connection for the ODBC DSN file change the default search_path to my_schema (and my_schema only). After seeing a post on this on another resource, I tried putting schema=my_schema in the connection settings text box, but the tables appeared in public anyway. Thanks! Carlo
On Dec 19, 2006, at 12:06 PM, Carlo Stonebanks wrote: > I would like to create an ODBC DSN for a user to export files rom > Acccess to > a safe area. Right now, all tables are being dumped to public by > default. > > In order to use the one-click export option for Access, I would > like to have > the connection for the ODBC DSN file change the default search_path to > my_schema (and my_schema only). > > After seeing a post on this on another resource, I tried putting > schema=my_schema in the connection settings text box, but the tables > appeared in public anyway. I think your only option is to change the default for the user. IT would be handy if there was a way to set search_path via pg_service.conf, though... -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Hi Jim, The solution was to put " set search_path=new_path" in Connection Settings in the ODBC configuration (on Page 2 of the dialog). Carlo -----Original Message----- From: Jim Nasby [mailto:decibel@decibel.org] Sent: January 14, 2007 10:07 PM To: Carlo Stonebanks Cc: pgsql-interfaces@postgresql.org; PGSQL Hackers Subject: Re: [INTERFACES] ODBC: how to change search_path in DSN? On Dec 19, 2006, at 12:06 PM, Carlo Stonebanks wrote: > I would like to create an ODBC DSN for a user to export files rom > Acccess to > a safe area. Right now, all tables are being dumped to public by > default. > > In order to use the one-click export option for Access, I would > like to have > the connection for the ODBC DSN file change the default search_path to > my_schema (and my_schema only). > > After seeing a post on this on another resource, I tried putting > schema=my_schema in the connection settings text box, but the tables > appeared in public anyway. I think your only option is to change the default for the user. IT would be handy if there was a way to set search_path via pg_service.conf, though... -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)