Re: [INTERFACES] postgresODBC and Symantec dbAnywhere/Visual Cafe, broken?

David,

I knew I forgot something in my post, the version info is as follows:

Server:

FreeBSD-2.2.7-Release

PostgreSQL-6.3.2

Client:

Win95-osr2

ODBC-6.30.0248 from www.insightdist.com (the latest according to the
web page)

<bigger>Symantec dbANYWHERE Server 1.1a for Windows 95/NT (Evaluation
Version)</bigger>

<bigger>Symantec </bigger>Visual Cafe for Java, Database Developers
Edition 2.5 (Evaluation Version, hey I'm not paying for any of this stuff
until I know it is going to work ;)

The log samples I included before where from the c:\psqlodbc.log file,
but I have included a full log of the following simple test. I have also
included a log that dbAnywhere generates.

First a quick rundown of what dbAnywhere does. dbAnywhere is a
middle-man data source. You are supposed to configure dbAnywhere to
communicate with all your datasources, then Visual Cafe only has to know
how to communicate with dbAnywhere. It makes working with Visual Cafe
really nice, but dbAnywhere only runs on Windoz, so you have to have a
95/NT server set up in addition to your UN*X PostgreSQL.

Since I am developing on Win95 using Visual Cafe, I can run dbAnywhere on
the same machine and use all the nice features that running dbAnywhere
provides to Visual Cafe, i.e. drag-and-drop master-detail, simple form
generation, etc. Then when my applet is done, I simply switch the
applet's datadriver to use the jdbc:postgresql driver and cut dbAnywhere
out of the picture. I have gotten the jdbc:postgresql part working, now
if I could just get the ODBC part going...

On to the test.

0. Cleared the log files. (can you tell I'm a programmer?)

1. I turned on logging for the postgresODBC driver.

2. I fired up dbAnywhere and enabled its logging.

3. I fired up Visual Cafe.

4. In Visual Cafe I selected view->dbNavigator

5. In the dbNavigator window I clicked on the "plus" in front of
"evaluation" (the name of the dbAnywhere server running on the localhost
Win95 machine.)

Navigation using dbNavigator is just like Windoz Explorer.

All the possible datasources were displayed and PostgreSQL was one of
them.

6. I clicked on the "plus" in front of "PostgreSQL".

I was then prompted for a username and password.

7. I typed in a username and password I have set up on my posgresql
server.

All tables owned by the user I typed in were then displayed. The
database to connect to is set in the ODBC configuration. This particular
database is called "test" and has two tables, contacts and persons.

8. I clicked on the "plus" in front of the "contacts" table name.

This is when I get the error: DBMS message: Null statement result in
SQLFetch.

That's it. I closed all the programs and saved the logs (included
below). This error occurs anytime I try to view a table's fields via
dbAnywhere. Kind of makes it useless.

Any ideas?

Thanks,

Matthew

psqlodbc.log

------------

DSN info:
DSN='PostgreSQL',server='10.0.0.1',port='5432',dbase='test',user='postgres',passwd='postgres1'

readonly='0',protocol='',showoid='1',fakeoidindex='0',showsystable='0'

conn_settings=''

translation_dll='',translation_option=''

conn = 87033980, SQLConnect(DSN='PostgreSQL', UID='postgres',
PWD='postgres1')

Global Options: fetch=100, socket=4096, unknown_sizes=2,
max_varchar_size=254, max_longvarchar_size=4094

disable_optimizer=1, unique_index=0, use_declarefetch=1

text_as_longvarchar=0, unknowns_as_longvarchar=0,
bools_as_char=1

extra_systable_prefixes='dd_;', conn_settings=''

conn=87033980, query=' '

conn=87033980, query='BEGIN'

conn=87033980, query='set DateStyle to 'ISO'; set geqo to 'OFF''

Command response: 'SET VARIABLE'

conn=87033980, query='declare SQL_CUR87048472 cursor for select oid from
pg_type where typname='lo''

conn=87033980, query='fetch 100 in SQL_CUR87048472'

[ fetched 0 rows ]

conn=87033980, query='close SQL_CUR87048472'

conn=87033980, query='END'

conn=87033980, query='BEGIN'

conn=87033980, query='declare SQL_CUR87114156 cursor for select relname,
usename, relhasrules from pg_class, pg_user where relkind = 'r' and
usename like 'postgres' and relname !~ '^pg_|^dd_' and relname !~
'^xinv[0-9]+' and int4out(usesysid) = int4out(relowner) order by
relname'

conn=87033980, query='fetch 100 in SQL_CUR87114156'

[ fetched 2 rows ]

conn=87033980, query='close SQL_CUR87114156'

conn=87033980, query='END'

conn=87033980, query='BEGIN'

conn=87033980, query='declare SQL_CUR87114156 cursor for select
u.usename, c.relname, a.attname, a.atttypid,t.typname, a.attnum,
a.attlen, a.atttypmod, a.attnotnull from pg_user u, pg_class c,
pg_attribute a, pg_type t where int4out(u.usesysid) = int4out(c.relowner)
and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and
c.relname like 'contacts' order by attnum'

conn=87033980, query='fetch 100 in SQL_CUR87114156'

[ fetched 10 rows ]

conn=87033980, query='close SQL_CUR87114156'

conn=87033980, query='END'

conn=87033980, query='BEGIN'

conn=87033980, query='declare SQL_CUR87193300 cursor for select
u.usename, c.relname, a.attname, a.atttypid,t.typname, a.attnum,
a.attlen, a.atttypmod, a.attnotnull from pg_user u, pg_class c,
pg_attribute a, pg_type t where int4out(u.usesysid) = int4out(c.relowner)
and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and
c.relname like 'contacts' order by attnum'

conn=87033980, query='fetch 100 in SQL_CUR87193300'

[ fetched 10 rows ]

conn=87033980, query='close SQL_CUR87193300'

conn=87033980, query='END'

conn=87033980, query='BEGIN'

conn=87033980, query='declare SQL_CUR87114156 cursor for select
c.relname, i.indkey, i.indisunique, i.indisclustered from pg_index i,
pg_class c, pg_class d where c.oid = i.indexrelid and d.relname =
'contacts' and d.oid = i.indrelid'

conn=87033980, query='fetch 100 in SQL_CUR87114156'

[ fetched 0 rows ]

conn=87033980, query='close SQL_CUR87114156'

conn=87033980, query='END'

conn=87033980, query='BEGIN'

conn=87033980, query='declare SQL_CUR87048472 cursor for select distinct
on attnum a2.attname, a2.attnum from pg_attribute a1, pg_attribute a2,
pg_class c, pg_index i where c.relname = 'contacts_pkey' AND c.oid =
i.indexrelid AND a1.attrelid = c.oid AND a2.attrelid = c.oid AND
(i.indkey[0] = a1.attnum OR i.indkey[1] = a1.attnum OR i.indkey[2] =
a1.attnum OR i.indkey[3] = a1.attnum OR i.indkey[4] = a1.attnum OR
i.indkey[5] = a1.attnum OR i.indkey[6] = a1.attnum OR i.indkey[7] =
a1.attnum) order by a2.attnum'

conn=87033980, query='fetch 100 in SQL_CUR87048472'

[ fetched 0 rows ]

conn=87033980, query='close SQL_CUR87048472'

conn=87033980, query='END'

STATEMENT ERROR: func=SQLFetch, desc='', errnum=3, errmsg='Null statement
result in SQLFetch.'

------------------------------------------------------------

hdbc=87033980, stmt=87193300, result=0

manual_result=1, prepare=0, internal=0

bindings=0, bindings_allocated=0

parameters=0, parameters_allocated=0

statement_type=-2, statement='(null)'

stmt_with_params=''

data_at_exec=-1, current_exec_param=-1, put_data=0

currTuple=-1, current_col=-1, lobj_fd=-1

maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1

cursor_name=''

----------------QResult Info
-------------------------------

CONN ERROR: func=SQLFetch, desc='', errnum=0, errmsg=''

------------------------------------------------------------

henv=88080516, conn=87033980, status=1, num_stmts=16

sock=88080532, stmts=88080572, lobj_type=-999

---------------- Socket Info
-------------------------------

socket=44, reverse=0, errornumber=0, errormsg='(null)'

buffer_in=87040272, buffer_out=87044372

buffer_filled_in=2, buffer_filled_out=0, buffer_read_in=2

conn=87033980, SQLDisconnect

dbAnywhere.log

--------------

902364484 Logging onto the server 'PostgreSQL'

902364484 Opening the database 'PostgreSQL' ...

902364485 DATA SOURCE NAME: PostgreSQL

DBMS NAME: PostgreSQL

DBMS VERSION 06.30.0248 PostgreSQL 6.3

ODBC DRIVER NAME: PSQLODBC.DLL

ODBC DRIVER VERSION: 06.30.0248

SUPPORTED: Yes

902364497 Null statement result in SQLFetch.

902364497 Error: SQL9999

Message: [Symantec][dbANYWHERE Server][Version: 1.1a - Build:
219][SQL]Database engine or client library specific error encountered.

DBMS error code: 1

DBMS message: Null statement result in SQLFetch.

Suggestion: Please see the specific database's vendor supplied
manual(s) for further information concerning this error.

902364497 Error: SQL9999

Message: [Symantec][dbANYWHERE Server][Version: 1.1a - Build:
219][SQL]Database engine or client library specific error encountered.

DBMS error code: 1

DBMS message: Null statement result in SQLFetch.

Suggestion: Please see the specific database's vendor supplied
manual(s) for further information concerning this error.

902364508 DC,127.0.0.1,902364464,44,15

>1. What version of the driver?

>

>2. Could you activate the CommLog in the drive setup and send the
result of a

>minimal test. The log file will be found in C:\psqlodbc.log .

>

>Matthew Hagerty wrote:

>

>> Greetings,

>>

>> I'm going to get all these interfaces working if it kills me! ;)

>>

>> I have dbAnywhere running, I have an ODBC driver for my database

>> (PostgreSQL) installed and working, and dbAnywhere makes a connection
okay.

>> The only problem I have is when I try to select a table using a
wizard (or

>> in the recordDefinition->Table Name property, or in the dbNavigator),
I get

>> the following error:

>>

>> dbAnywhere window:

>>

>> Message: [Symantec][dbANYWHERE Server][Version: 1.1a - Build:

>> 219][SQL]Database engine or client library specific error
encountered.

>> DBMS error code: 1

>> DBMS message: Null statement result in SQLFetch.

>> Suggestion: Please see the specific database's vendor supplied

>> manual(s) for further information concerning this error.

>>

>> >From my ODBC log:

>>

>> STATEMENT ERROR: func=SQLFetch, desc='', errnum=3, errmsg='Null
statement

>> result in SQLFetch.'

>>
------------------------------------------------------------

>> hdbc=75499644, stmt=75514136, result=0

>> manual_result=1, prepare=0, internal=0

>> bindings=0, bindings_allocated=0

>> parameters=0, parameters_allocated=0

>> statement_type=-2, statement='(null)'

>> stmt_with_params=''

>> data_at_exec=-1, current_exec_param=-1, put_data=0

>> currTuple=-1, current_col=-1, lobj_fd=-1

>> maxRows=0, rowset_size=1, keyset_size=0,
cursor_type=0,

>> scroll_concurrency=1

>> cursor_name=''

>> ----------------QResult Info
-------------------------------

>> CONN ERROR: func=SQLFetch, desc='', errnum=0, errmsg=''

>>
------------------------------------------------------------

>> henv=76546180, conn=75499644, status=1, num_stmts=16

>> sock=76546196, stmts=76546236, lobj_type=-999

>> ---------------- Socket Info
-------------------------------

>> socket=40, reverse=0, errornumber=0, errormsg='(null)'

>> buffer_in=75505936, buffer_out=75510036

>> buffer_filled_in=2, buffer_filled_out=0,
buffer_read_in=2

>>

>> Does anyone have any ideas what may be causing this error? I have
tried

>> changing the ODBC setting to just about everything that makes sense
(and

>> them some that don't). The only problems with the driver seems to
be

>> viewing tables.

>>

>> Otherwise, I *am* able to create tables using postgresqlODBC via

>> dbAnywhere! Go figure that I can create a table with dbAnywhere via
ODBC,

>> but I can't view a table!?

>>

>> Any ideas?

>>

>> Thanks,

>> Matthew

>

>

>

>