[ odbc ] setting the search_path
Tanguy
I have several databases. The objects are stored in
the public schema of each database.
I would like to move them to different schemas in one single database.
In the softwares that use these databases, the sql statements
don't mention the schemas.
I found out that it should be possible to set the search_path variable
in order to access the tables, views (etc...) without rewriting all
the sql statements.
I found this link :
http://archives.postgresql.org/pgsql-odbc/2008-11/msg00013.php
I would be perfect for me, but unfortunately it doesn't work.
If I append ";A6=set search_path to my_schema,public;"
to my connection string, I get an error message telling
me the relation doesn't exist (so I assume the search_path
is not properly set).
I tried many ways to do it (replaced A6 with ConnSettings, for instance)
but none seems to work.
Does anyone know how i can achieve this?
Andreas Kretschmer
Do you have different users? If yes, you can set the search_path per
user, using "alter user foo set search_path= ..." once for all.
Other solution, but untested (i don't know much about ODBC, but it works
for connectionis with PHP for instance): set the search_path as a normal
sql-statement, for instance: "set search_path=...; select * from foo"
once per session.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Matthew Woodcraft
> I found this link :
> http://archives.postgresql.org/pgsql-odbc/2008-11/msg00013.php
> I would be perfect for me, but unfortunately it doesn't work.
> If I append ";A6=set search_path to my_schema,public;"
> to my connection string, I get an error message telling
> me the relation doesn't exist (so I assume the search_path
> is not properly set).
> I tried many ways to do it (replaced A6 with ConnSettings, for instance)
> but none seems to work.
> Does anyone know how i can achieve this?
I did something similar some time ago (using 'A6'), and I found I needed
to escape some of the characters in the SQL statement, as follows:
space -> +
underscore -> %5f
comma -> %2c
Then it worked for me.
-M-
Mladen Gogala
You can set path in postgresql.conf. There is a parameter you can
uncomment if you want to set it to something other than default:
mgogala@nyclapwxp2622:~$ grep search_path /etc/postgresql/8.4/main/
postgresql.conf
#search_path = '"$user",public' # schema names
mgogala@nyclapwxp2622:~$
I am running Ubuntu 9.04 with PostgreSQL 8.4.1:
mgogala@nyclapwxp2622:~$ uname -a
Linux nyclapwxp2622 2.6.28-17-generic #58-Ubuntu SMP Tue Dec 1 18:57:07
UTC 2009 i686 GNU/Linux
gogala@nyclapwxp2622:~$ psql
psql (8.4.1)
Type "help" for help.
mgogala=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.1 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Ubuntu 4.3.3-5ubuntu4) 4.3.3, 32-bit
(1 row)
Your question should include your platform and version, too. That will
will make the process of searching the solutions for your problem easier.
Tanguy
>
> Do you have different users? If yes, you can set the search_path per
> user, using "alter user foo set search_path= ..." once for all.
>
Hello,
That's not exactly what i want to do, but if it works it could be an
acceptable workaround.
I will just have to set a different users for each software.
...Oops, i just tried, it doesn't work either.
(I'll check again, i 'm starting to think i'm doing something wrong in
my software)
Thanks for the tip anyway.
> Other solution, but untested (i don't know much about ODBC, but it works
> for connectionis with PHP for instance): set the search_path as a normal
> sql-statement, for instance: "set search_path=...; select * from foo"
> once per session.
>
I had tried this one. It works ok in psql, but not in the software :(
Tanguy
>
> You can set path in postgresql.conf. There is a parameter you can
> uncomment if you want to set it to something other than default:
>
Hello,
That might be a solution, but i thing this is a global setting ?
> Your question should include your platform and version, too. That will
> will make the process of searching the solutions for your problem easier.
>
Yes, you're right. I forgot to mention this.
I run postgresql on a redhat server, kernel version 2.6.18
Postgresql version :
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)
(1 row)
I compiled postgresql from the source code.
Tanguy
> I did something similar some time ago (using 'A6'), and I found I needed
> to escape some of the characters in the SQL statement, as follows:
>
> space -> +
> underscore -> %5f
> comma -> %2c
>
> Then it worked for me.
>
> -M-
Thanks for your answer, i'll try this.
Where did you set this A6 parameter ?
I found you can do it in several places (in odbc.ini, in the connection
string in the software...)
Andreas Kretschmer
> Mladen Gogala a ï¿œcrit :
>
>>
>> You can set path in postgresql.conf. There is a parameter you can
>> uncomment if you want to set it to something other than default:
>>
>
> Hello,
>
> That might be a solution, but i thing this is a global setting ?
Exactly.
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Laurenz Albe
> Mladen Gogala a �crit :
>> You can set path in postgresql.conf. There is a parameter you can uncomment if you want to set it to something other than
>> default:
>
Yes, that is a global setting.
Yours,
Laurenz Albe
Tanguy
> Tanguy <tan...@nowhere.fr> wrote:
>> Mladen Gogala a ï¿œcrit :
>>
>>> You can set path in postgresql.conf. There is a parameter you can
>>> uncomment if you want to set it to something other than default:
>>>
>> Hello,
>>
>> That might be a solution, but i thing this is a global setting ?
>
> Exactly.
>
>
>
> Andreas
Ok, it works.
I created a user for the database, I set
search_path = '"$user",public'
in the postgresql.conf file.
I realized I had not properly granted the access to the user.
Once it was done, it worked fine.
Thanks to everyone who helped me! :)