Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
"Dave Page"
Date:
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@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
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
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
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.

Re: problems with sqlsetpos -->having odbc intercept alter command

From
"Hollysugar Webmaster"
Date:
Dave,
 
For the moment we have given up on syncing the SyWare product with PostgreSql. We are now shifting our focus to using Microsoft's active sync as a method of syncing to the PostgreSql database. Either of these methods will allow us to communicate and sync with the GIS application we have running on the PDA.
 
With the active sync connection we are also running into a problem. Active sync is sending the command " Alter table "MSysCeCmobile2" add "ConflictID" integer IDENTITY" which then results in a parse error. I have written the equivalent alter table/column commands setting the column to a serial but I do not know how to intercept the original alter command with my group of commands?
 
I tried to create a function which I was going to invoke with a rule but setting the default value for the sequence caused an error in the function 'ERROR: Attribute "conflict_id_id" not found'. conflict_id_id is the name of the sequence I created. This is the line of code I used:
 
 ' ALTER TABLE "MSysCeCmobile2" ALTER "ConflictID" SET DEFAULT nextval("conflict_id_id");'
 
When I used single quotes within the nextval function I would get a parse error.
 
Even if I were to get this function to work correctly it does not appear that PostgreSql has support for a rule on ALTER. Which leads me back to intercepting the ALTER command with the ODBC driver and replacing it with my own ALTER commands.
 
I'm sorry to be such a bother, but hopefully once we get this worked out, it will be useful to other people who would like to sync a mobile application with PostgreSql.
 
Julie