Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
"Dave Page"
Date:
 
-----Original Message-----
From: Hollysugar Webmaster [mailto:webmaster@hollysugar.com]
Sent: 09 April 2003 17:02
To: Dave Page
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] problems with sqlsetpos -->having odbc intercept alter command

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? 
 
If that's what Activesync sends, then I don't see how you can intercept it short of hacking the driver or the PostgreSQL parser. 
 
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. 
 
Try doing it in pgAdmin with SQL logging turned on and see what it does. It's almost certainly single quotes though. 
 
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. 
 
No, there is no support for rules on ALTER that I'm aware of. If I get time tomorrow I'll look at activesync (no promises). We have about 20 pocket PCs in the field running a PostgreSQL based application. The only difference here is that the data is downloaded and prepared in a local Access database which is then transferred to the ppc. 
 
Regards, Dave.

SQLColumns, SQLTables where fails depending on row cache size

From
"Wayne Armstrong"
Date:
Hi,
 an SQLColumns or SQLTables request to the odbc driver will fail if the number
of columns(or tables) returned is a multiple of the row cache size and
use_declare_fetch is true.
The error returned is :-='Unrecognized return value from
copy_and_convert_field.'
This is returned because the driver has read 1 row past the end of the result,
and is trying to convert nulls.
I am using the 7.02.00.05 drivers.
I have read the source some, but can't actually see what needs changing (I am
not really a c programmer :).  I have debug logs for this if anyone's
interested.

Regards,
Wayne

New problem with sqlsetpos -->support for adding rows?

From
"Hollysugar Webmaster"
Date:
Gentlemen,
 
First of all thank you for all of your patience and help on this project. We were able to get sqlsetpos to work for updating the postgresql tables when data was altered on the mobile device and synced using the odbc interface.
 
The problem with updating the postgresql database was related to the use of oid's. The mobile database we are using requires an oid column to be the first user column in a table. When we created the table without oids and added oid as the first column with default value of 0 (as required by the mobile database) the sqlsetpos function could not identify where it was located and exited out of the odbc call.
 
Our issue now is in trying to add new records to the mobile database and when synced have them added to the postgresql database. When we try this operation we get the error code "row value out of range" since sqlsetpos is looking for the 41st row when there are only 40 rows.
 
I found the following information on Microsoft's site regarding sqlsetpos and adding new rows into a table.
 
<microsoft info src="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odglosspr.asp">
 
The SQL_ADD value for the Operation argument has been deprecated for ODBC 3.x. ODBC 3.x drivers will need to support SQL_ADD for backward compatibility. This functionality has been replaced by a call to SQLBulkOperations with an Operation of SQL_ADD. When an ODBC 3.x application works with an ODBC 2.x driver, the Driver Manager maps a call to SQLBulkOperations with an Operation of SQL_ADD to SQLSetPos with an Operation of SQL_ADD.
 
</microsoft info>
 
Any suggestions on what I need to do to make this process work?
 
Julie
 
 

Re: SQLColumns, SQLTables where fails depending on row cache size

From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: Wayne Armstrong
>
> Hi,
>  an SQLColumns or SQLTables request to the odbc driver will fail
> if the number
> of columns(or tables) returned is a multiple of the row cache size and
> use_declare_fetch is true.
> The error returned is :-='Unrecognized return value from
> copy_and_convert_field.'
> This is returned because the driver has read 1 row past the end
> of the result,
> and is trying to convert nulls.
> I am using the 7.02.00.05 drivers.
> I have read the source some, but can't actually see what needs
> changing (I am
> not really a c programmer :).  I have debug logs for this if anyone's
> interested.

Could you send me the debug logs ?

regards,
Hiroshi Inoue