Postgres Pro
Downloads
Home   >   mailing lists

Thread: PQoidValue - isn't it for...?

PQoidValue - isn't it for...?

From
Jim Michaels
Date:
I am struggling to learn libpq.

for some reason, I could not get an INSERT to produce an Oid.  actually, what I am looking for, is to get the ID of the last record inserted or to verify that I inserted a record successfully.  I think you use PQresultStatus() for that.(?)

Isn't PQoidValue() for getting the last INSERT id?  or am I misunderstanding it?


I just figured out how to set the start id of a BIGSERIAL to 1000.



SAMPLE OUTPUT:

firstname:Horatio
middlename:P
lastname:Algers
homephone:345-678-9012
workphone:
cellphone:
pager:
company:
address1:
address2:
city:
mailstop:
stateprovince:
postalcode:
country:
comment:abc,123,456
website:
emailhome:
emailwork:
QUERY:"INSERT INTO s_phonelist.phonelist(firstname,middlename,lastname,homephone,workphone,cellphone,pager,company,address1,address2,city,mailstop,stateprovi
,postalcode,country,_comment,website,emailhome,emailwork)
VALUES('Horatio','P','Algers','345-678-9012','','','','','','','','','','','','abc,123,456','','','')"
ERROR: INSERT operation failed!
done.





sprintf(querystr, "INSERT INTO s_phonelist.phonelist(%s)\n"
         "VALUES(%s)", fnl, vl);
printf("QUERY:\"%s\"\n", querystr);
pgr = PQexec(pgc,querystr);

if (PGRES_COMMAND_OK!=PQresultStatus(pgr)) {
    printf("INSERT result is not OK\n");
} else {
    Oid oid =  PQoidValue(pgr);
    if (0 == oid) {
         printf("ERROR: INSERT operation failed!\n");
    }
}

Jim Michaels <jmichae3@yahoo.com>

Re: PQoidValue - isn't it for...?

From
Magnus Hagander
Date:
On Wed, Aug 12, 2009 at 10:11, Jim Michaels<jmichae3@yahoo.com> wrote:
> I am struggling to learn libpq.
>
> for some reason, I could not get an INSERT to produce an Oid.  actually,

By default, tables are created without Oids.


> what I am looking for, is to get the ID of the last record inserted or to
> verify that I inserted a record successfully.  I think you use
> PQresultStatus() for that.(?)

If you want to get the ID, use something like:
INSERT INTO ... VALUES (...) RETURNING id

Assuming "id" is the name of your serial column. It will then return a
regular resultset that you can access with PQgetvalue().

But, if you just want to know if the insert succeeded or not, the
check of PQresultStatus() is enough. If that one returns
PGRES_COMMAND_OK, it means the INSERT was ok. There is no need for
your application to do any further verification.


> Isn't PQoidValue() for getting the last INSERT id?  or am I misunderstanding
> it?

It will get you the last oid, *if* the table has Oids, which user
tables by default don't have. And in most cases, they shouldn't have -
using a SERIAL or BIGSERIAL column is in most cases much better.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: PQoidValue - isn't it for...?

From
Jasen Betts
Date:
On 2009-08-12, Jim Michaels <jmichae3@yahoo.com> wrote:
> --0-1559521671-1250064688=:25681
> Content-Type: text/plain; charset=us-ascii
>
> I am struggling to learn libpq.
>
> for some reason, I could not get an INSERT to produce an Oid.  actually, what I am looking for, is to get the ID of
thelast record inserted or to verify that I inserted a record successfully.  I think you use PQresultStatus() for
that.(?)
>
> Isn't PQoidValue() for getting the last INSERT id?  or am I misunderstanding it?

since 8.0 (iirc) you must specify "WITH OIDS" when you create the
table or you won't get them, you can instead use "RETURNING
someprimarykey" at the end of the insert query (for apropriate
values of someprimarykey) and get a different sort of handle
on the new row

>     Oid oid =  PQoidValue(pgr);
>     if (0 == oid) {
>          printf("ERROR: INSERT operation failed!\n");

You should be printing the result from PQlastError() here too.