Обсуждение: INSERT INTO ... RETURNING id not behaving as expected with SQLNumResultCols

I'm using Postgres 8.4.1 and psqlodbc 08.04.0200. I'm trying to execute a statement INSERT INTO table/values RETURNING
idto get the serial id. Problem is, SQLNumResultCols shows that there are zero columns. The statement I used works fine
inpgadmin. This seems really basic and I am somewhat new to databases and odbc, but does anyone have a solution? 

Thanks,
Ryan

Ryan,

What language are you writing your app? What is the code you're using to
capture the returning id?

Also, are you aware that you can fire a new SELECT statement after
you're done inserting?  In scenarios where you wouldn't have much
concurrency that can be a valid approach.

And don't forget that the OID column is there for you to inspect. Ever
tried a SELECT OID FROM MyTable ORDER BY OID DESC LIMIT 1 ?

Gustavo

On 07/10/2010 01:04, Ryan Pfeiffer wrote:
> I'm using Postgres 8.4.1 and psqlodbc 08.04.0200. I'm trying to execute a statement INSERT INTO table/values
RETURNINGid to get the serial id. Problem is, SQLNumResultCols shows that there are zero columns. The statement I used
worksfine in pgadmin. This seems really basic and I am somewhat new to databases and odbc, but does anyone have a
solution?
>
> Thanks,
> Ryan
>
>
>
>

I'm writing it in visual c++. I've been using DataLayer to interact with ODBC, here:
http://www.codeproject.com/KB/database/DataLayer.aspx

The code in question is:

ret = SQLPrepare(m_hstmt, (SQLCHAR*)selectStatement, SQL_NTS);
if ( ( ret == SQL_SUCCESS ) || ( ret == SQL_SUCCESS_WITH_INFO ) )
{
   SQLSMALLINT numberColumns;

   // Get number of columns in the result set
   ret = SQLNumResultCols(m_hstmt, &numberColumns);

&numberColumns comes up as 0. Can SQLPrepare not be used the way I want for INSERT INTO ... RETURNING?

And yes, I'm aware I could use another select statement, and concurrency would *probably* not be an issue down the
road,but it bothers me that it is possible I could get the wrong ID. I suppose I can just SELECT id WHERE val1 = x,
val2= y, and so on as there should not be any exact duplicates in my tables that I can think of at this point. Still
earlyon in development though. 

Thanks,
Ryan

--- On Thu, 10/7/10, Gustavo Pinsard <pinsard@rocksolid.com.br> wrote:

> From: Gustavo Pinsard <pinsard@rocksolid.com.br>
> Subject: Re: [ODBC] INSERT INTO ... RETURNING id not behaving as expected with SQLNumResultCols
> To: pgsql-odbc@postgresql.org
> Date: Thursday, October 7, 2010, 6:11 AM
> Ryan,
>
> What language are you writing your app? What is the code
> you're using to
> capture the returning id?
>
> Also, are you aware that you can fire a new SELECT
> statement after
> you're done inserting?  In scenarios where you
> wouldn't have much
> concurrency that can be a valid approach.
>
> And don't forget that the OID column is there for you to
> inspect. Ever
> tried a SELECT OID FROM MyTable ORDER BY OID DESC LIMIT 1
> ?
>
> Gustavo
>
> On 07/10/2010 01:04, Ryan Pfeiffer wrote:
> > I'm using Postgres 8.4.1 and psqlodbc 08.04.0200. I'm
> trying to execute a statement INSERT INTO table/values
> RETURNING id to get the serial id. Problem is,
> SQLNumResultCols shows that there are zero columns. The
> statement I used works fine in pgadmin. This seems really
> basic and I am somewhat new to databases and odbc, but does
> anyone have a solution?
> >
> > Thanks,
> > Ryan
> >
> >
> >
> >
>
>

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

Ok, I believe I have found the problem.

http://msdn.microsoft.com/en-us/library/ms711684(v=VS.85).aspx

"The application can call SQLNumResultCols at any time after the statement is prepared or executed. However, because
somedata sources cannot easily describe the result sets that will be created by prepared statements, performance will
sufferif SQLNumResultCols is called after a statement is prepared but before it is executed." 

I suppose that's what I get for slapping an existing library into my software without completely understanding it. If I
understandwhat I've read correctly, SQLPrepare should only be used if I plan on repeating the same statement over and
overwith different variables. 

Since this is a simple wrapper, it seems that an error may have been made. It calls SQLPrepare each time the wrapper
classesare used, so even if it was repeated many times, SQLPrepare is still being called each time which I think is not
useful.Not sure on that though. At this point I moved up SQLExecute and I got the results I expected. 

--- On Thu, 10/7/10, Ryan Pfeiffer <malice_ryan@yahoo.com> wrote:

> From: Ryan Pfeiffer <malice_ryan@yahoo.com>
> Subject: Re: [ODBC] INSERT INTO ... RETURNING id not behaving as expected with SQLNumResultCols
> To: pgsql-odbc@postgresql.org
> Date: Thursday, October 7, 2010, 12:51 PM
> I'm writing it in visual c++. I've
> been using DataLayer to interact with ODBC, here: http://www.codeproject.com/KB/database/DataLayer.aspx
>
> The code in question is:
>
> ret = SQLPrepare(m_hstmt, (SQLCHAR*)selectStatement,
> SQL_NTS);
> if ( ( ret == SQL_SUCCESS ) || ( ret ==
> SQL_SUCCESS_WITH_INFO ) )
> {
>    SQLSMALLINT numberColumns;
>
>    // Get number of columns in the result
> set
>    ret = SQLNumResultCols(m_hstmt,
> &numberColumns);
>
> &numberColumns comes up as 0. Can SQLPrepare not be
> used the way I want for INSERT INTO ... RETURNING?
>
> And yes, I'm aware I could use another select statement,
> and concurrency would *probably* not be an issue down the
> road, but it bothers me that it is possible I could get the
> wrong ID. I suppose I can just SELECT id WHERE val1 = x,
> val2 = y, and so on as there should not be any exact
> duplicates in my tables that I can think of at this point.
> Still early on in development though.
>
> Thanks,
> Ryan
>
> --- On Thu, 10/7/10, Gustavo Pinsard <pinsard@rocksolid.com.br>
> wrote:
>
> > From: Gustavo Pinsard <pinsard@rocksolid.com.br>
> > Subject: Re: [ODBC] INSERT INTO ... RETURNING id not
> behaving as expected with SQLNumResultCols
> > To: pgsql-odbc@postgresql.org
> > Date: Thursday, October 7, 2010, 6:11 AM
> > Ryan,
> >
> > What language are you writing your app? What is the
> code
> > you're using to
> > capture the returning id?
> >
> > Also, are you aware that you can fire a new SELECT
> > statement after
> > you're done inserting?  In scenarios where you
> > wouldn't have much
> > concurrency that can be a valid approach.
> >
> > And don't forget that the OID column is there for you
> to
> > inspect. Ever
> > tried a SELECT OID FROM MyTable ORDER BY OID DESC
> LIMIT 1
> > ?
> >
> > Gustavo
> >
> > On 07/10/2010 01:04, Ryan Pfeiffer wrote:
> > > I'm using Postgres 8.4.1 and psqlodbc 08.04.0200.
> I'm
> > trying to execute a statement INSERT INTO
> table/values
> > RETURNING id to get the serial id. Problem is,
> > SQLNumResultCols shows that there are zero columns.
> The
> > statement I used works fine in pgadmin. This seems
> really
> > basic and I am somewhat new to databases and odbc, but
> does
> > anyone have a solution?
> > >
> > > Thanks,
> > > Ryan
> > >
> > >
> > >
> > >
> >
> >
>
>
>
>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>