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

-----Original Message-----
From: Hollysugar Webmaster [mailto:webmaster@hollysugar.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

To: Hollysugar Webmaster

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

To: SweetBeet.com Webmaster

Cc: pgsql-odbc@postgresql.org ; Hiroshi Inoue

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@agterra.com]
Sent: 08 April 2003 20:46
To: Dave Page
Cc: pgsql-odbc@postgresql.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

To: Julie May

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/

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.