Skip site navigation (1) Skip section navigation (2)

Re: INSERT INTO ... RETURNING id not behaving as expected with SQLNumResultCols

From: Gustavo Pinsard <pinsard(at)rocksolid(dot)com(dot)br>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Re: INSERT INTO ... RETURNING id not behaving as expected with SQLNumResultCols
Date: 2010-10-07 18:31:13
Message-ID: 4CAE11F1.9060700@rocksolid.com.br (view raw or flat)
Thread:
Lists: pgsql-odbc
Ryan,

You seem to have a buffer problem.  As you know you have to declare 
which columns you want returned from the INSERT statement. Thus, you 
need a compatible buffer to receive each column declared.

As you didn't inform how you're mounting your statement, nor the struct 
to hold the results of it, I can't say for sure.

But here is an example of how I deal with INSERT RETURNING, in my 
language of choice: Clarion.

   PROGRAM

   MAP
   END

szDBConn      CSTRING( 512 )
tblDummy      FILE, DRIVER( 'ODBC' ), NAME( 'dummy' ), OWNER( szDBConn )
RECORD          RECORD
id                ULONG
                 END
               END


   ! Clarion has roots in Cobol, and the following statement
   ! declares the executable part of the program
   CODE

   ! Lets prepare the connection string
   szDBConn = 'Driver={{PostgreSQL ANSI};database=test;uid=test;pwd=test'

   ! Lets open the "file", which is in reality just a buffer pointing
   ! to a table in a server, according to the "OWNER" modifier
   OPEN( tblDummy, 42h )

   ! Here I send the command I want - " & | " instructs to continue
   ! on the next line.  Clarion doesn't use ";" as an end of statement
   ! marker, but the \n\r sequence, and when we need, well, you figured.
   tblDummy{ PROP:SQL } = 'INSERT INTO customers ' & |
                          'VALUES ( 'GUSTAVO' ) ' & |
                          'RETURNING id'

   ! HERE IT IS - read whatever the server returned
   NEXT( tblDummy )

   ! This is a MessageBox() alright.
   MESSAGE( tblDummy.id )

   ! Ends program
   RETURN 0

The above code holds a complete program that will connect to a server 
(given teh connection strings is valid, off course), and issue an INSERT 
command against an existing "customers" table.

Now, how do I SEND the SQL command to de server? Using the transport 
buffer I declared: tblDummy.  This buffer reflects an existing table (or 
view) in the database.  In this case, a table called "dummy".

In Clarion, when I issue a PROP:SQL command attached to a buffer, the 
server responds back the informed buffer.  Then, a simple NEXT() will 
read the contents of the buffer, which is a struct at the end of the 
day, and I'm all set.

This tip of having a dummy table or view declared may help you to 
collect different types of information from the server, not always 
related to data tables.  You can, for that matter, ask the server who is 
logged, for how long, its version etc.

It is just a matter of having a compatible buffer to receive whatever 
the server sends back in response to a command.

HTH

Gustavo

In response to

pgsql-odbc by date

Next:From: Ryan PfeifferDate: 2010-10-07 20:43:40
Subject: Re: INSERT INTO ... RETURNING id not behaving as expected with SQLNumResultCols
Previous:From: Ryan PfeifferDate: 2010-10-07 17:51:59
Subject: Re: INSERT INTO ... RETURNING id not behaving as expected with SQLNumResultCols

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group