Thread: ODBC problem
Hi Please help... Im trying to connect to postgresql via ODBC using the iodbc driver manager. As part of that package theres a odbctest program that can be ran, however it appears to be failing. Im kind of stumped as to where to go next as the error isn't too usefull. The error I am getting in the iodbc tracefile is.. SQLDriverConnect( ... ) SQL_NO_DATA_FOUND odbctest just exits with no usefull information.. When I run odbctest I connect to it using the connect string : DSN=Postgres and It seems to be finding my odbc.ini file okay. My odbc.ini file looks like ; ; odbc.ini ; [ODBC Data Sources] Postgres = Postgres [Postgres] Driver = /usr/local/pgsql/lib/libpsqlodbc.so.0 Host = localhost Database = marksdb TraceFile = /tmp/odbc.trace Trace = 1 Im pretty new to this so its almost certainly an education problem. Is there any better documentation on ODBC installation/configuration at all for postgresql Mark
Hi, last week, I had similar problems. First I couldn't get jdbc running correctly with staroffice (I haven't found anybody who could), then I didn't manage to connect anyhow with iodbc and then I had problems with the postgres odbc driver in and unix-odbc. But at the end (after two days), taking the unix-odbc driver included with unix-odbc, I got a stable connection :-) to my pg7.2 server. And even staroffice worked with it together. Heiko odbc.ini: [Emep] Description = Test to Postgres Driver = PostgreSQL Trace = Yes TraceFile = /tmp/sql.log Database = emep Servername = localhost UserName = Password = Port = 5432 Protocol = 7.2 ReadOnly = No RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings = odbcinst.ini: [PostgreSQL] Description = PGSQL 7.2 Driver = /usr/emep/lib/libodbcpsql.so Setup = /usr/emep/lib/libodbcpsqlS.so FileUsage = 1 Debug = 0 mark writes: > > > > Hi > > Please help... Im trying to connect to postgresql via ODBC using the > iodbc driver manager. As part of that package theres a odbctest > program that can be ran, however it appears to be failing. Im kind of > stumped as to where to go next as the error isn't too usefull. > > The error I am getting in the iodbc tracefile is.. > > SQLDriverConnect( ... ) > SQL_NO_DATA_FOUND > > odbctest just exits with no usefull information.. > > When I run odbctest I connect to it using the connect string : > DSN=Postgres and It seems to be finding my odbc.ini file okay. My > odbc.ini file looks like > > ; > ; odbc.ini > ; > [ODBC Data Sources] > Postgres = Postgres > > [Postgres] > Driver = /usr/local/pgsql/lib/libpsqlodbc.so.0 > Host = localhost > Database = marksdb > TraceFile = /tmp/odbc.trace > Trace = 1 > > Im pretty new to this so its almost certainly an education problem. Is > there any better documentation on ODBC installation/configuration at all > for postgresql > > Mark > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Thanks.. Im kind of new to this.. Can you explain to me the role of the odbcinst.ini file and where that needs to be located.. I noticed in your odbc.ini you don't point directly to the driver itself. All the other examples Ive seen do this.. Can you explain how this is working ? Heiko Klein wrote: >Hi, > >last week, I had similar problems. First I couldn't get jdbc running >correctly with staroffice (I haven't found anybody who could), then I >didn't manage to connect anyhow with iodbc and then I had problems with >the postgres odbc driver in and unix-odbc. > >But at the end (after two days), taking the unix-odbc driver included >with unix-odbc, I got a stable connection :-) to my pg7.2 server. And >even staroffice worked with it together. > >Heiko > >odbc.ini: >[Emep] >Description = Test to Postgres >Driver = PostgreSQL >Trace = Yes >TraceFile = /tmp/sql.log >Database = emep >Servername = localhost >UserName = >Password = >Port = 5432 >Protocol = 7.2 >ReadOnly = No >RowVersioning = No >ShowSystemTables = No >ShowOidColumn = No >FakeOidIndex = No >ConnSettings = > >odbcinst.ini: >[PostgreSQL] >Description = PGSQL 7.2 >Driver = /usr/emep/lib/libodbcpsql.so >Setup = /usr/emep/lib/libodbcpsqlS.so >FileUsage = 1 >Debug = 0 > > > >mark writes: > > > > > > > > Hi > > > > Please help... Im trying to connect to postgresql via ODBC using the > > iodbc driver manager. As part of that package theres a odbctest > > program that can be ran, however it appears to be failing. Im kind of > > stumped as to where to go next as the error isn't too usefull. > > > > The error I am getting in the iodbc tracefile is.. > > > > SQLDriverConnect( ... ) > > SQL_NO_DATA_FOUND > > > > odbctest just exits with no usefull information.. > > > > When I run odbctest I connect to it using the connect string : > > DSN=Postgres and It seems to be finding my odbc.ini file okay. My > > odbc.ini file looks like > > > > ; > > ; odbc.ini > > ; > > [ODBC Data Sources] > > Postgres = Postgres > > > > [Postgres] > > Driver = /usr/local/pgsql/lib/libpsqlodbc.so.0 > > Host = localhost > > Database = marksdb > > TraceFile = /tmp/odbc.trace > > Trace = 1 > > > > Im pretty new to this so its almost certainly an education problem. Is > > there any better documentation on ODBC installation/configuration at all > > for postgresql > > > > Mark > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
I can't explain it really. I know, that iodbc examples use allways only one file, and I think, even in unix-odbc you can live with one file (not sure on this). But I liked the idea of having the driver describtion odbcinst.ini separated from the database-setup. The Driver in odbc.ini points to the [PostgreSQL] entry in odbcinst.ini. Both files need to be in the etc directory of your installation or in ~/.odbc.ini ~/.odbcinst.ini (I did it the first way, so I cannot guarantee about the second). Heiko mark writes: > > > Thanks.. Im kind of new to this.. Can you explain to me the role of the > odbcinst.ini file and where that needs to be located.. > > I noticed in your odbc.ini you don't point directly to the driver > itself. All the other examples Ive seen do this.. Can you explain how > this is working ? > > Heiko Klein wrote: > > >odbc.ini: > >[Emep] > >Description = Test to Postgres > >Driver = PostgreSQL > >Trace = Yes > >TraceFile = /tmp/sql.log > >Database = emep > >Servername = localhost > >UserName = > >Password = > >Port = 5432 > >Protocol = 7.2 > >ReadOnly = No > >RowVersioning = No > >ShowSystemTables = No > >ShowOidColumn = No > >FakeOidIndex = No > >ConnSettings = > > > >odbcinst.ini: > >[PostgreSQL] > >Description = PGSQL 7.2 > >Driver = /usr/emep/lib/libodbcpsql.so > >Setup = /usr/emep/lib/libodbcpsqlS.so > >FileUsage = 1 > >Debug = 0 > > > > > > > >mark writes: > > > > > > > > > > > > Hi > > > > > > Please help... Im trying to connect to postgresql via ODBC using the > > > iodbc driver manager. As part of that package theres a odbctest > > > program that can be ran, however it appears to be failing. Im kind of > > > stumped as to where to go next as the error isn't too usefull. > > > > > > The error I am getting in the iodbc tracefile is.. > > > > > > SQLDriverConnect( ... ) > > > SQL_NO_DATA_FOUND > > > > > > odbctest just exits with no usefull information.. > > > > > > When I run odbctest I connect to it using the connect string : > > > DSN=Postgres and It seems to be finding my odbc.ini file okay. My > > > odbc.ini file looks like > > > > > > ; > > > ; odbc.ini > > > ; > > > [ODBC Data Sources] > > > Postgres = Postgres > > > > > > [Postgres] > > > Driver = /usr/local/pgsql/lib/libpsqlodbc.so.0 > > > Host = localhost > > > Database = marksdb > > > TraceFile = /tmp/odbc.trace > > > Trace = 1 > > > > > > Im pretty new to this so its almost certainly an education problem. Is > > > there any better documentation on ODBC installation/configuration at all > > > for postgresql > > > > > > Mark > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > >
Hmmmm, tried setting it up the same as yours and still seem to be having the same problems.. You mentioned that you were seeing a similar thing.. Was it just changing around the odbc.ini and odbcinst.ini file that changed it , or was there anything else you needed to do ? Thanks for the help.. Heiko Klein wrote: >I can't explain it really. I know, that iodbc examples use allways only >one file, and I think, even in unix-odbc you can live with one file (not >sure on this). But I liked the idea of having the driver describtion >odbcinst.ini separated from the database-setup. The Driver in odbc.ini >points to the [PostgreSQL] entry in odbcinst.ini. Both files need to be >in the etc directory of your installation or in ~/.odbc.ini >~/.odbcinst.ini (I did it the first way, so I cannot guarantee about the >second). > >Heiko > >mark writes: > > > > > > Thanks.. Im kind of new to this.. Can you explain to me the role of the > > odbcinst.ini file and where that needs to be located.. > > > > I noticed in your odbc.ini you don't point directly to the driver > > itself. All the other examples Ive seen do this.. Can you explain how > > this is working ? > > > > Heiko Klein wrote: > > > > >odbc.ini: > > >[Emep] > > >Description = Test to Postgres > > >Driver = PostgreSQL > > >Trace = Yes > > >TraceFile = /tmp/sql.log > > >Database = emep > > >Servername = localhost > > >UserName = > > >Password = > > >Port = 5432 > > >Protocol = 7.2 > > >ReadOnly = No > > >RowVersioning = No > > >ShowSystemTables = No > > >ShowOidColumn = No > > >FakeOidIndex = No > > >ConnSettings = > > > > > >odbcinst.ini: > > >[PostgreSQL] > > >Description = PGSQL 7.2 > > >Driver = /usr/emep/lib/libodbcpsql.so > > >Setup = /usr/emep/lib/libodbcpsqlS.so > > >FileUsage = 1 > > >Debug = 0 > > > > > > > > > > > >mark writes: > > > > > > > > > > > > > > > > Hi > > > > > > > > Please help... Im trying to connect to postgresql via ODBC using the > > > > iodbc driver manager. As part of that package theres a odbctest > > > > program that can be ran, however it appears to be failing. Im kind of > > > > stumped as to where to go next as the error isn't too usefull. > > > > > > > > The error I am getting in the iodbc tracefile is.. > > > > > > > > SQLDriverConnect( ... ) > > > > SQL_NO_DATA_FOUND > > > > > > > > odbctest just exits with no usefull information.. > > > > > > > > When I run odbctest I connect to it using the connect string : > > > > DSN=Postgres and It seems to be finding my odbc.ini file okay. My > > > > odbc.ini file looks like > > > > > > > > ; > > > > ; odbc.ini > > > > ; > > > > [ODBC Data Sources] > > > > Postgres = Postgres > > > > > > > > [Postgres] > > > > Driver = /usr/local/pgsql/lib/libpsqlodbc.so.0 > > > > Host = localhost > > > > Database = marksdb > > > > TraceFile = /tmp/odbc.trace > > > > Trace = 1 > > > > > > > > Im pretty new to this so its almost certainly an education problem. Is > > > > there any better documentation on ODBC installation/configuration at all > > > > for postgresql > > > > > > > > Mark > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > > TIP 2: you can get off all lists at once with the unregister command > > > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > > > > > > >
Hi,
I don't really understand what you mean by 'tried setting it up ...',
but I asume you meant, you compiled unix-odbc and used my odbc.ini and
odbcinst.ini with pathes changed to your needs.
First step I didn't mention: You need to set up connections via TCP (the
-i switch on startup or change in $PG_DATA/postgresql.conf the
following:
#tcpip_socket = false
tcpip_socket = true
Second step: Make sure libodbc.so is in you LD_LIBRARY_PATH or
/etc/ld.so.conf (on linux).
Third step: put odbc.ini and odbcinst.ini into the $(prefix)/etc
directory
Fourth step: test with isql: isql DB user
or isql DB user password
Fifth step: set up the application you would like to use.
Heiko
mark writes:
>
>
> Hmmmm, tried setting it up the same as yours and still seem to be having
> the same problems..
>
> You mentioned that you were seeing a similar thing.. Was it just
> changing around the odbc.ini and odbcinst.ini file that changed it , or
> was there anything else you needed to do ?
>
> Thanks for the help..
>