Hello, It is common to use auto-generated values for primary keys of the tables that don't have any natural candidate. There are 2 mechanisms for doing this depending on the database:
1. Use a sequence. In this case you need to get the next value from the
sequence and use it in "INSERT INTO" statement explicitly.
2. Use "auto increment field. In this case you don't need to, and at least
in MS SQL case, actually can't (unless you turn off identity insert
option), specify the value for the field when inserting it and it's
automatically generated by the database instead.
Firebird, Oracle, PostgreSQL and several others support (1) while MS SQL
and MySQL (and maybe others too) support (2). This makes writing the code
meant to work with different RDBMS a bit difficult but it may still be
done. In pseudo code:
statement = "INSERT INTO T(F1, F2) VALUES(?, ?)";
id_value = get_next_sequence_value("sequence_name");
if id_value != -1:
statement = "INSERT INTO T(ID, F1, F2) VALUES(?, ?, ?)";
statement.use(id_value)
statement.execute()
if id_value == -1:
id_value = get_last_insert_id("tale_name")
The problem is that currently SOCI doesn't provide the required
get_next_sequence_value() and get_last_insert_id() functions. I could
implement them myself using get_backend() but I think that it would really
make more sense to have them inside SOCI itself as the implementation is
entirely backend-specific. What do you think?
I'd be able to do it for all the existing backends except SQLite (it
should be possible to implement it there as well using the information from
http://www.sqlite.org/autoinc.html but we don't use SQLite so I don't
really need it there). ODBC one is special because the mechanism to use
depends on the driver used, so I'd need to call SQLGetInfo(SQL_DBMS_NAME)
and check it dynamically. If anybody has any ideas about how to do it
better, I'd like to know about it, of course.
Thanks in advance for any comments/ideas,
VZ
pgpsGq4BJRdKV.pgp
Description: PGP signature
------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________ Soci-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/soci-users
