Thread: Re: [INTERFACES] psqlodbc
> I've included the HOWTO I recently wrote for mySQL that will be > distributed with the upcoming 4.4.1 release; hope this helps. That helped a lot Eric. Thanks. So, here is the current status: 1) the psqlodbc driver has the database information section _hardcoded_ as "PostgreSQL". 2) the psqlodbc driver does not successfully read the .odbc.ini file (or at least doesn't read any of the entries I've put into it). That explains why the log files were not being written and why things like the server name and port number were never being set. 3) I guess it shouldn't suprise me, but the psqlodbc driver does not use libpq to interface with Postgres. Byron and company have done a lot of work recreating it. Off to work now. Will try some more this evening... - Tom
OK, I've gotten a ways farther; here is the current status on the ApplixWare->Postgres interface via iODBC using the Insight driver: 1) The psqlodbc driver does not use a hardcoded database name as I had reported earlier; there is some fallback code which does but that is not getting executed. 2) ApplixWare/iodbc is handing the psqlodbc driver a registry "section" name string which has a trailing blank. So, the driver was never finding a match in the .odbc.ini file for that block because it wasn't stripping blanks from the target strings (it does strip blanks correctly from the .ini file entries themselves). I brute-force fixed that by stripping the blanks in the psqlodbc .ini reader (in gpps.c), but we should fix it when the driver gets the string from the app. Any idea where that is? 3) The driver appears to successfully complete a few of the transactions involved in starting up the database connection, including one or two queries. It returns from SQLConnect() with a SQL_SUCCESS return code. 4) After that point Applix reports a "broken pipe". Log info from the "psqlodbc" and "mylog" debugging files is at the end of the mail. It looks like SQLConnect() is returning, but I don't know what should happen next to see where things are bombing out. So, questions: Does anyone have a suggestion on which driver routines get called by Applix/iodbc and in what order? Is there any debugging I can turn on in Applix or in the iodbc driver? Any ideas on where to look next? - Tom golem> cat psqlodbc.log DSN info: DSN='PostgreSQL',server='localhost',port='5432',dbase='test',user='tgl',passwd='' readonly='',protocol='',showoid='',fakeoidindex='',showsystable='' conn_settings='' translation_dll='',translation_option='' conn = 134611560, SQLConnect(DSN='PostgreSQL', UID='tgl', PWD='doh') SQLConnect- calling CC_connect... Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=4094 disable_optimizer=1, unique_index=0, use_declarefetch=1 text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 extra_systable_prefixes='dd_;', conn_settings='' conn=134611560, query=' ' SQLConnect- return SQL_SUCCESS golem> cat mylog.log **** in SQLAllocEnv ** ** exit SQLAllocEnv: phenv = 134611544 ** **** SQLAllocConnect: henv = 134611544, conn = 134611560 EN_add_connection: self = 134611544, conn = 134611560 added at i =0, conn->henv = 134611544, conns[i]->henv = 134611544 gpps- try opening /home/tgl/.odbc.ini gpps- look in file /home/tgl/.odbc.ini section PostgreSQL for field Description <snip lots of debugging statements for reading the .ini file> gpps- found section ODBC gpps- check key InstallDir CC_connect(): DSN = 'PostgreSQL', server = 'localhost', port = '5432', database = 'test', username = 'tgl', password='doh' connecting to the server socket... connection to the server socket succeeded. sizeof startup packet = 292 sent the authentication block. sent the authentication block successfully. gonna do authentication read 5, global_socket_buffersize=4096 auth got 'R' areq = 0 sending an empty query... send_query(): conn=134611560, query=' ' send_query: done sending query read 2, global_socket_buffersize=4096 send_query: got id = 'I' in QR_Constructor exit QR_Constructor QResult: in DESTRUCTOR QResult: free memory in, fcount=0 QResult: free memory out QResult: exit DESTRUCTOR empty query seems to be OK. **** SQLAllocStmt: hdbc = 134611560, stmt = 134626288 CC_add_statement: self=134611560, stmt=134626288 Sending Initial Connection query: 'set DateStyle to 'ISO'; set geqo to 'OFF'' **** SQLExecDirect: hstmt=134626288, statement='set DateStyle to 'ISO'; set geqo to 'OFF'' SQLExecDirect: calling SQLExecute **** enter SQLFreeStmt: hstmt=134626288, fOption=1 SC_Destructor: self=134626288, self->result=0, self->hdbc=134611560 SC_free_params: ENTER, self=134626288 SC_Destructor: EXIT **** SQLAllocStmt: hdbc = 134611560, stmt = 134626288 CC_add_statement: self=134611560, stmt=134626288 **** SQLExecDirect: hstmt=134626288, statement='select oid from pg_type where typname='lo'' SQLExecDirect: calling SQLExecute **** enter SQLFreeStmt: hstmt=134626288, fOption=1 SC_Destructor: self=134626288, self->result=0, self->hdbc=134611560 SC_free_params: ENTER, self=134626288 SC_Destructor: EXIT CC_connect- returning SQL_SUCCESS
Thomas G. Lockhart wrote: > OK, I've gotten a ways farther; here is the current status on the > ApplixWare->Postgres interface via iODBC using the Insight driver: > Tom, In case your not aware, Byron is on vacation. He will be back on the 10th. He will assist you (if you need assistance) in getting driver working with Applix. when he returns.
> > OK, I've gotten a ways farther; here is the current status on the > > ApplixWare->Postgres interface via iODBC using the Insight driver: > In case your not aware, Byron is on vacation. He will be back on the > 10th. He will assist you (if you need assistance) in getting driver > working with Applix. when he returns. Yes, Byron announced his vacation on the list, which was helpful. I'm making progress (at least so far) in areas which aren't visible to Byron since they involve Applix/iodbc/psqlodbc interactions (and are happening on a Unix box rather than a Windows machine), but I am looking forward to his advice when he returns. Been copying him so he gets the hint when he gets back :) Anyway, I'm going to try running the backend in full debug mode which might give some clues. I hadn't considered the fact that the "broken pipe" message might actually be a frontend/backend hangup rather than an internal Applix/iodbc problem. So there may be some Postgres packet debugging in our future :/ btw, we apparently missed the window to be included in the next Applix release, but that shouldn't matter much; we can post the driver stuff on the Postgres web site and the Applix mailing list can announce it. Just hope we don't find any fundamental problems on the Applix side, though we shouldn't since it apparently already works with MySQL. - Tom
> 3) The driver appears to successfully complete a few of the > transactions involved in starting up the database connection, > including one or two queries. It returns from SQLConnect() with a > SQL_SUCCESS return code. > 4) After that point Applix reports a "broken pipe". Log info from the > "psqlodbc" and "mylog" debugging files is at the end of the mail. It > looks like SQLConnect() is returning, but I don't know what should > happen next to see where things are bombing out. It turns out that the backend reports "reaping dead processes" without showing any successful transactions other than (perhaps) a successful plain connection. So, it appears that the two or three queries embedded into SQLConnect()/CC_connect() are not actually succeeding, or even getting to the backend properly. Looks like it would help to have Byron involved, so I will move back to working on docs for a few days. btw, I've only just started looking at the fe/be code, but istm that it would be really nice if libpq-fe could fully support the WIN32 interface or if psqlodbc could use libpq-fe when on a Unix box. Some guy in Russia has a driver which uses libpq-fe and the code looks like it has the same lineage as Byron's code (before Byron started working on it). Where does this code come from? Byron's code gives credit to Christian Czezatke and Dan McGuirk but the "iodbc project" driver doesn't. - Tom
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes: > btw, I've only just started looking at the fe/be code, but istm that it > would be really nice if libpq-fe could fully support the WIN32 interface > or if psqlodbc could use libpq-fe when on a Unix box. I would like to see libpq-fe usable by applications running on Windows. I am not in a position to test it for myself currently, but would gladly coordinate any work needed. The current sources are part of the way there --- they are alleged to compile and work when WIN32 is defined. I am concerned about their dependence on error printout to stderr, but I already posted a plan of attack for that. Are there any other issues that need to be taken care of? regards, tom lane
Thomas G. Lockhart wrote: > It turns out that the backend reports "reaping dead processes" without > showing any successful transactions other than (perhaps) a successful > plain connection. So, it appears that the two or three queries embedded > into SQLConnect()/CC_connect() are not actually succeeding, or even > getting to the backend properly. > I am not sure how fresh your source is, but I do recall Byron making some tweaks in the initial connection queries recently. We'll wait and see. > btw, I've only just started looking at the fe/be code, but istm that it > would be really nice if libpq-fe could fully support the WIN32 interface > or if psqlodbc could use libpq-fe when on a Unix box. Some guy in Russia > has a driver which uses libpq-fe and the code looks like it has the same > lineage as Byron's code (before Byron started working on it). Where does > this code come from? Byron's code gives credit to Christian Czezatke and > Dan McGuirk but the "iodbc project" driver doesn't. > Don't know about the Russian dude, but the we completely rewrote the the front-end section in the driver. We did not (and still do not) like the way libpq consumes the entire query result into front-end memory before returning control to the application. This make large queries slow and sometimes impossible. However, with the patches that Bruce and Vadim are working on for multiple ORs (many thanks) this may become a moot issue.
David Hartwig <daybee@bellatlantic.net> writes: > We did not (and still do not) like the > way libpq consumes the entire query result into front-end memory before > returning control to the application. This make large queries slow and > sometimes impossible. With the new PQsendQuery/PQgetResult API, it would not take very much work to address this objection. The application could set a limit on the number of tuples to be returned per PGresult (maybe about 100 would reasonable), and then use a loop calling PQgetResult to fetch all the tuples of a large query. We'd probably want to add a new boolean flag "end of query/not end of query" to PGresult so that one could use this feature even when sending multiple SELECTs in one sendQuery. Rather than make the limit a persistent setting of a PGconn object, I'd be inclined to add it as a parameter to PQgetResult (I think it's not too late to change the calling sequence for PQgetResult, since it hasn't yet seen the light of day in any formal release). Otherwise PQexec might start behaving unexpectedly in an application that contains a mixture of old-style PQexec calls and new-style PQsendQuery/getResult. If the memory consumption issue is a hot button for anyone who is using (or would use) libpq, I'll see if I can squeeze this in for 6.4. I assume the psqlodbc guys will stick to their own code anyway. regards, tom lane
> > ... istm that it > > would be really nice if libpq-fe could fully support the WIN32 > > interface or if psqlodbc could use libpq-fe when on a Unix box. > I would like to see libpq-fe usable by applications running on > Windows. I am not in a position to test it for myself currently, but > would gladly coordinate any work needed. > The current sources are part of the way there --- they are alleged to > compile and work when WIN32 is defined. I am concerned about their > dependence on error printout to stderr, but I already posted a plan > of attack for that. Are there any other issues that need to be taken > care of? Byron is out of town 'til the 10th of August, and I'm sure he will have an opinion. Shall we bring it up again then? Though if others have an opinion speak up so we get an idea of who might have an interest... - Tom