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

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