Re: continuing problems with sqlsetpos & syncing postgresql to a mobile database

Hi Julie,

You will need to talk to the Syware people about the reasons why the
table doesn't get created then I think. I'm no expert on ODBC cursors
having never worked with them, but there is certainly no reason why
SQLSetPos would prevent a table being created - not that it even appears
to be attempted.

My only thought is that the error itself causes your application to exit
before the table is created, but then (and I admit I'm guess what the
application is doing), if it hasn't found it's sync table, how would it
know what (if any) tables should be opened and updated, and therefore
why is it even calling SQLSetPos at this point?

The bottom line though I'm afraid is that I don't know enough about the
cursor support, or even it's usage in general to help you further with
this. I've CC'd this back to the ODBC list as hopefully someone there
might know more.

Regards, Dave.

-----Original Message-----
From: Hollysugar Webmaster [mailto:webmaster(at)hollysugar(dot)com]
Sent: 08 April 2003 23:25
To: Dave Page
Subject: Re: [ODBC] continuing problems with sqlsetpos & syncing
postgresql to a mobile database


Dave,

We created a new database with fewer tables so we could more
quickly check whether the changes we are making are having any effect
and to reduce the size of the log files. I double checked and the
SywareSync table does not exist in the database. I checked using both
PgAdmin (with view system objects selected) and with the \d option in
psql) but the table does not show up. I am going to manually create the
table so we can eliminate the possibility that the SywareSync table or
lack of it is causing the problem.

The first error block in the mylog (comm log) is
[632]PGAPI_SetStmtOption: entering...
[632]SetStmtOption(): SQL_CONCURRENCY = 2 [632]-> 3
[632][[SQLGetDiagRec]]
[632]PGAPI_GetDiagRec entering rec=1[632]**** PGAPI_StmtError:
hstmt=19137600 <2000>
[632]SC_get_error: status = 16, msg = #Requested value changed.#
[632] szSqlState = '01S02',len=24,
szError='Requested value changed.'
[632]PGAPI_GetDiagRec exiting 0
[632][[SQLGetDiagRec]]
[632]PGAPI_GetDiagRec entering rec=2[632]**** PGAPI_StmtError:
hstmt=19137600 <2000>
[632]SC_get_error: status = 16, msg = #Requested value changed.#
[632]PGAPI_GetDiagRec exiting 100
[632][[SQLSetStmtAttr]] Handle=19137600 6,1
[632]PGAPI_SetStmtAttr Handle=19137600 6,1

The second error block is the exact same except that hstmt is a
different number and the Handle's are a different number as well.

The third error block relates to the Syware_syncinfo errors
(around line 16xx).

The next block of errors is around line 33xx and seems to relate
to the error message "#cursor updatability changed#". I copied the block
below.

[632]recycle statement: self= 19137600
[632]APD_free_params: ENTER, self=19137732
[632]IPD_free_params: ENTER, self=19137760
[632]IPD_free_params: EXIT
[632]Exec_with_parameters_resolved: copying statement params:
trans_status=1, len=45, stmt='update "mobile" set OID = 0 where OID IS
NULL'
[632] stmt_with_params = 'update "mobile" set OID = 0 where
OID IS NULL'
[632] it's NOT a select statement: stmt=19137600
[632]send_query(): conn=19100592, query='update "mobile" set OID
= 0 where OID IS NULL'
[632]send_query: done sending query
[632]in QR_Constructor
[632]exit QR_Constructor
[632]read 18, global_socket_buffersize=4096
[632]send_query: got id = 'P'
[632]send_query: got id = 'C'
[632]send_query: ok - 'C' - UPDATE 0
[632]send_query: setting cmdbuffer = 'UPDATE 0'
[632]send_query: returning res = 19142672
[632]send_query: got id = 'Z'
[632]PGAPI_ExecDirect: returned 1 from PGAPI_Execute
[632][[SQLGetDiagRec]]
[632]PGAPI_GetDiagRec entering rec=1[632]**** PGAPI_StmtError:
hstmt=19137600 <2000>
[632]SC_get_error: status = 16, msg = #cursor updatability
changed#
[632] szSqlState = '01S02',len=27, szError='cursor
updatability changed'
[632]PGAPI_GetDiagRec exiting 0
[632][[SQLGetDiagRec]]
[632]PGAPI_GetDiagRec entering rec=2[632]**** PGAPI_StmtError:
hstmt=19137600 <2000>
[632]SC_get_error: status = 16, msg = #cursor updatability
changed#
[632]PGAPI_GetDiagRec exiting 100
[632][SQLExecDirect][632]PGAPI_ExecDirect: entering...
[632]**** PGAPI_ExecDirect: hstmt=19137600, statement='update
"mobile" set OID = 0 where OID >= 268435456'
[632]PGAPI_ExecDirect: calling PGAPI_Execute...
[632]PGAPI_Execute: entering...
[632]PGAPI_Execute: clear errors...
[632]PGAPI_Execute: recycling statement (should have been done
by app)...
[632]recycle statement: self= 19137600

This block of code seems to be repeated then the program exits
out.

I will have the syware_syncinfo table added in a few minutes and
I will let you know if that affects the outcome.

Thanks.

Julie

----- Original Message -----
From: Dave Page <mailto:dpage(at)vale-housing(dot)co(dot)uk>
To: Hollysugar Webmaster
<mailto:webmaster(at)hollysugar(dot)com>
Sent: Tuesday, April 08, 2003 3:17 PM
Subject: RE: [ODBC] continuing problems with sqlsetpos &
syncing postgresql to a mobile database

Julie,

Your app does appear to be calling SQLTables, which is
probably reporting that SYWARE_Syncinfo exists (you can check this with
pgAdmin or \d in psql). The logs show no attempt to create the table,
but they do show the table name being used unquoted in a select query.
Because PostgreSQL folds unquoted identifiers to lowercase, and is case
sensitive, the table in the query is effectively sysware_syncinfo which
doesn't exist.

I don't see how SQLSetPos would prevent this table being
created, as it is used to control a cursor, not execute statements like
CREATE TABLE.

Regards, Dave.

-----Original Message-----
From: Hollysugar Webmaster
[mailto:webmaster(at)hollysugar(dot)com]
Sent: 08 April 2003 21:35
To: Dave Page
Subject: Re: [ODBC] continuing problems with
sqlsetpos & syncing postgresql to a mobile database


Dave,

That is what we thought originally when we
looked at the logs. We were told by the SyWare tech people that if the
SYWARE_SyncInfo table does not exist (which it doesn't) that the program
will create the table (which I have not seen it do) and that the program
continues on. They originally thought the problem had to do with the
sqlsetpos.

When I look at the mylogs I guess (because I
truly don't have a clue) that the problem is either related to the
"SC_pos_reload_needed " statement or in the following block of
statements.

[1764]pcrow=1
[1764][SQLSetPos][1764]PGAPI_SetPos fOption=2
irow=1 lock=0 currt=0
[1764]PGAPI_SetConnectOption: entering fOption =
102 vParam = 0
[1764]PGAPI_SetConnectOption: AUTOCOMMIT:
transact_status=1, vparam=0
[1764]POS UPDATE 0+0 fi=1240160 ti=1242620
[1764]PGAPI_SetConnectOption: entering fOption =
102 vParam = 1
[1764]PGAPI_SetConnectOption: AUTOCOMMIT:
transact_status=0, vparam=1
[1764]rowset=1 processed=1 ret=-1
[1764][[SQLGetDiagRec]]
[1764]PGAPI_GetDiagRec entering rec=1[1764]****
PGAPI_StmtError: hstmt=19137600 <2000>
[1764]SC_Get_error returned nothing.
[1764]PGAPI_GetDiagRec exiting 100
[1764][[SQLGetDiagRec]]
[1764]PGAPI_GetDiagRec entering rec=1[1764]****
PGAPI_StmtError: hstmt=19137600 <512>
[1764]SC_Get_error returned nothing.
[1764]PGAPI_GetDiagRec exiting 100

I'm completely stumped but I am learning a lot
:)

Thanks again for all of your help (both of you
Dave and Hiroshi)

Julie

----- Original Message -----
From: Dave Page
<mailto:dpage(at)vale-housing(dot)co(dot)uk>
To: SweetBeet.com Webmaster
<mailto:webmaster(at)agterra(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org ; Hiroshi
Inoue <mailto:inoue(at)tpf(dot)co(dot)jp>
Sent: Tuesday, April 08, 2003 2:07 PM
Subject: Re: [ODBC] continuing problems
with sqlsetpos & syncing postgresql to a mobile database

Hi Julie,

The obvious error is that your
application is not correctly quoting the tablename in the query:

select * from SYWARE_SyncInfo where
Tablename = 'contract_test'

It should probably be:

select * from "SYWARE_SyncInfo" where
Tablename = 'contract_test'

I'm not sure about the SetPos error
though. Any ideas Hiroshi?

Regards, Dave.

-----Original Message-----
From: SweetBeet.com Webmaster
[mailto:webmaster(at)agterra(dot)com]
Sent: 08 April 2003 20:46
To: Dave Page
Cc: pgsql-odbc(at)postgresql(dot)org; Hiroshi
Inoue
Subject: continuing problems with
sqlsetpos & syncing postgresql to a mobile database


Dave,

We tried both the snapshot of the
psqlodbc driver and the snapshot of the psqlodbc30 driver. We were not
able to get either to work. I looked through the logs and when I
searched for "error" I did find new error messages but I am not sure
what they mean. I have attached all the logs for both drivers. I
truncated the mylogs to reduce their size, let me know if you need to
see the full log

Julie

----- Original Message -----
From: Dave Page
<mailto:dpage(at)vale-housing(dot)co(dot)uk>
To: Julie May <mailto:julie(at)ccorb(dot)com>
Sent: Saturday, April 05, 2003 1:21 PM
Subject: RE: [pgadmin-support]
PgMigration error when migrating Dbase tables

Hi Julie,

Please try the updated DLLs from

http://www.geocities.jp/inocchichichi/psqlodbc/
<http://www.geocities.jp/inocchichichi/psqlodbc/>

I haven't tested it myself but I'm told
that:

You can use SQLSetPos(SQL_UPDATE) if you
compiled the driver with DRIVER_CURSOR_IMPLEMENT #defined(the current
makefile s in cvs already #define it) and turn on the *Updatable
cursors* DSN option.

Hiroshi's snapshots are only a week and
a bit old, so should include the relevant mods. If not, let me know and
I'll compile a fresh one for you.

Regards, Dave.