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

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 some data sources cannot easily describe the result sets that will be created by prepared statements, performance will suffer if 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 understand what I've read correctly, SQLPrepare should only be used if I plan on repeating the same statement over and over with different variables.

Since this is a simple wrapper, it seems that an error may have been made. It calls SQLPrepare each time the wrapper classes are 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(at)yahoo(dot)com> wrote:

> From: Ryan Pfeiffer <malice_ryan(at)yahoo(dot)com>
> Subject: Re: [ODBC] INSERT INTO ... RETURNING id not behaving as expected with SQLNumResultCols
> To: pgsql-odbc(at)postgresql(dot)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(at)rocksolid(dot)com(dot)br>
> wrote:
>
> > From: Gustavo Pinsard <pinsard(at)rocksolid(dot)com(dot)br>
> > Subject: Re: [ODBC] INSERT INTO ... RETURNING id not
> behaving as expected with SQLNumResultCols
> > To: pgsql-odbc(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>