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 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.