Обсуждение: ODBC, large objects and tracing
Hi, I am porting an application from Win NT to Linux. It is an application for storing, querying, subsetting and exporting geographic data. On NT the data is stored in MS SQL Server, database access is handled through a dll which handles all requests for data from the application. The dll is written using ODBC 3 as the interface to SQL Server. I installed postgresql 6.53 a few days ago and had a go at getting the dll code working with the postgresql odbc driver. As the driver is written for odbc 2.0, I decided to try the UnixOdbc driver and driver manager. Using their package my code compiled perfectly, with NO modifications. However, I immediatley ran into trouble when inserting into large-objects (Lot's of blobs in this app!). In the server trace there was an error to the effect of "Invalid large object descriptor". That had me stumped until yesterday, when I ran across a patch for the ODBC driver on this mailing list (dated Dec. 8, 99). I applied the patch (by hand) to the UnixODBC version of the postgres driver, and the problem went away. I'm impressed! I thought it was going to be way more difficult than this. If anyone is interested, I would be happy to supply the modified source files for the driver and a script I cobbled together to add the large object support functions, data type and table trigger (for deleting lo's). It all seems to work quite well. I do have a couple of questions. What datatype does SQL_VARBINARY map to in postgresql? I searched the driver header files and came up with "bytea". But when I try to use it by binding my binary data to a parameter in the insert statement and then calling SQLExecute, SQLPutDate, etc., all I get is an SQL_ERROR return value from SQLExecute. Finally, as lame as it sounds, I can't figure out how to make the odbc trace work! I've set TRACE = yes and specified a tracefile located in the postgres home directory. It is always empty. Have I missed something obvious? Thanks, Eliot Cline IT Systems Manager Intercontinental Sourcing and Services, Ltd. Bangkok, Thailand ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
---- Original Message ----- From: "Eliot Cline" <eliotcline@hotmail.com> To: <pgsql-interfaces@postgreSQL.org> Sent: Monday, December 20, 1999 11:47 PM Subject: [INTERFACES] ODBC, large objects and tracing > Hi, > > I am porting an application from Win NT to Linux. It is an application for > storing, querying, subsetting and exporting geographic data. On NT the data > is stored in MS SQL Server, database access is handled through a dll which > handles all requests for data from the application. The dll is written using > ODBC 3 as the interface to SQL Server. > > I installed postgresql 6.53 a few days ago and had a go at getting the dll > code working with the postgresql odbc driver. As the driver is written for > odbc 2.0, I decided to try the UnixOdbc driver and driver manager. Using > their package my code compiled perfectly, with NO modifications. > > However, I immediatley ran into trouble when inserting into large-objects > (Lot's of blobs in this app!). In the server trace there was an error to the > effect of "Invalid large object descriptor". > > That had me stumped until yesterday, when I ran across a patch for the ODBC > driver on this mailing list (dated Dec. 8, 99). I applied the patch (by > hand) to the UnixODBC version of the postgres driver, and the problem went > away. I'm impressed! I thought it was going to be way more difficult than > this. > > If anyone is interested, I would be happy to supply the modified source > files for the driver and a script I cobbled together to add the large object > support functions, data type and table trigger (for deleting lo's). It all > seems to work quite well. > I'd definitely like to have a look at the script that was cobbled together to add all that... I've been having troubles but its with the JDBC driver and large objects, but I think its just that I haven't done all thats necessary on the postgres side to enable it. Cheers, Joe.
On Wed, 22 Dec 1999, Joe Shevland wrote: > > I am porting an application from Win NT to Linux. It is an application for > > storing, querying, subsetting and exporting geographic data. On NT the > data > > is stored in MS SQL Server, database access is handled through a dll which > > handles all requests for data from the application. The dll is written > using > > ODBC 3 as the interface to SQL Server. > > > > I installed postgresql 6.53 a few days ago and had a go at getting the dll > > code working with the postgresql odbc driver. As the driver is written for > > odbc 2.0, I decided to try the UnixOdbc driver and driver manager. Using > > their package my code compiled perfectly, with NO modifications. > > > > However, I immediatley ran into trouble when inserting into large-objects > > (Lot's of blobs in this app!). In the server trace there was an error to > the > > effect of "Invalid large object descriptor". > > > > That had me stumped until yesterday, when I ran across a patch for the > ODBC > > driver on this mailing list (dated Dec. 8, 99). I applied the patch (by > > hand) to the UnixODBC version of the postgres driver, and the problem went > > away. I'm impressed! I thought it was going to be way more difficult than > > this. > > > > If anyone is interested, I would be happy to supply the modified source > > files for the driver and a script I cobbled together to add the large > object > > support functions, data type and table trigger (for deleting lo's). It all > > seems to work quite well. Theres an example trigger to automatically delete lo's when the row containing a reference to them is deleted in the source tree, under contrib/lo. > I'd definitely like to have a look at the script that was cobbled together > to add all that... I've been having troubles but its with the JDBC driver > and large objects, but I think its just that I haven't done all thats > necessary on the postgres side to enable it. Lo's should be useable from the outset, except that you need to setAutoCommit(false) to enable transactions. LO's can't be used outside of a transaction. If you still have problems with JDBC & LO's, let me know. Peter -- Peter T Mount peter@retep.org.uk Main Homepage: http://www.retep.org.uk PostgreSQL JDBC Faq: http://www.retep.org.uk/postgresJava PDF Generator: http://www.retep.org.uk/pdf