Hi Jim,
Jim Stewart wrote:
> Is there a good tutorial for libpqxx around? The only one I've found is the
> one it comes with (also linked on pqxx.org). Unfortunately this tutorial
> doesn't cover very much. Without knowing quite what I'm looking for, it's
> hard to locate things in the Doxygen reference.
The tutorial for libpqxx is the test suite, and it covers all of
libpqxx. If the feature is not in there, then libpqxx doesn't support
it. :-)
> I'm wondering about alternative ways to insert data. In the libpqxx tests,
> there are plenty of examples that insert e.g. a 2D array of strings, with an
> assumed field order. They all use pqxx::work::insert().
Hmmm, can't seem to find that function in the sources. Where is it defined?
> What I'd like to do is explicitly state the fields I'm inserting. I'd rather
> not rely on the DB field order being fixed, and I'd like to leverage default
> values for fields where they exist, etc. I'd also like to use prepared
> statements for inserts so that I don't have to worry about quoting and
> stringifying everything myself.
Seems like a solid plan! Prepared statements are a very good choice for
these kinds of things.
> I set up a prepared statement to handle an insert, and it worked fine..except
> result.inserted_oid() returned 0. The row was actually inserted in the DB,
> and result.affected_rows() returned 1.
>
> Here's my prepared statement:
>
> dbHandle->prepare("saveTargetStmt",
> "insert into nac_targets (image_id, execution_time, exposure_time, lines, "
> "compander_select, dac_reset_level_left, dac_reset_level_right, "
> "channel_a_offset_left, channel_a_offset_right, "
> "channel_b_offset_left, channel_b_offset_right) "
> "values ($1, now(), $2, $3, $4, $5, $6, $7, $8, $9, $10)")
> ("integer", pqxx::prepare::treat_direct)("integer",
> pqxx::prepare::treat_direct)
> ("integer", pqxx::prepare::treat_direct)("integer",
> pqxx::prepare::treat_direct)
> ("integer", pqxx::prepare::treat_direct)("integer",
> pqxx::prepare::treat_direct)
> ("integer", pqxx::prepare::treat_direct)("integer",
> pqxx::prepare::treat_direct)
> ("integer", pqxx::prepare::treat_direct)("integer",
> pqxx::prepare::treat_direct);
>
> Not shown in the prepared statement is the "id" field, a serial. That's the
> value I was hoping to get from result.inserted_oid().
>
> Summary of questions:
>
> 1) Is it possible to obtain the ID of a row inserted via a prepared statement?
Try using the syntax "returning", i.e.:
INSERT INTO nac_targets (...) values (...) returning id
This syntax is supported by PostgreSQL 8.2 and up, it was added in one
of the more recent SQL standards. The trick also works for UPDATE
statements AFAIK, and you can even do DELETE...RETURNING to return ids
of the records you deleted.
> 2) If so, is there some reason it's not showing up in inserted_oid()?
My guess is that it's because the work::insert function that you mention
uses the "copy" functionality of PostgreSQL, which is different from an
INSERT statement. But since I can't find work::insert anywhere right
now, I can't verify that.
> 3) If not, what's the right way to specify a hash of field/values for insert?
> Do I have to handle all the type conversions myself?
Hmmm, what do you want with such a hash? Anyway, this is not something
that libpqxx should provide, as it's not related to the database but to
your application's technical requirements. If you want to hash your data
for any reason you should specify the procedure for that yourself,
including how you want the types represented. I would like to hear about
your use case though, I mean, I'm interested to hear what you'd need
this for.
> 4) Any pointers to more complete tutorials on general pqxx usage?
Nope. The tutorial is all there is. :-)
Cheers,
Bart
_______________________________________________
Libpqxx-general mailing list
Libpqxx-general@gborg.postgresql.org
http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
