Qt4 QPSQL QSqlQuery::lastInsertId oid problem

[feniksa]

My application use different database drivers, to connect. And i want to use one code for different database (now i am testing Oracel OCI and PSQL). And i have troubles with psql driver:

After insert query, i use QSqlQuery::lastInsertId() method to get id of inserted row. LastInsertId return oid value, not id, that i am waiting. I use additional query (something like sequence::cur_val) or make query like "SELECT id FROM table_name WHERE oid= lastInsertId". But, this is crutches around psql driver lastInsertId. How i can get actually id of row? It is not good, when in code i use

if (psql_driver)  { get_real_id_from_oid(table_name, oid) }

if (psql_driver)  { get_real_id_from_oid(table_name, oid) }

To copy to clipboard, switch view to plain text mode 

.

I found in qt4.6.3 psql driver sources such code for lastInsertId:

QVariant QPSQLResult::lastInsertId() const
{
    if (isActive()) {
        Oid id = PQoidValue(d->result);
        if (id != InvalidOid)
            return QVariant(id);
    }
    return QVariant();
}

QVariant QPSQLResult::lastInsertId() const
{
    if (isActive()) {
        Oid id = PQoidValue(d->result);
        if (id != InvalidOid)
            return QVariant(id);
    }
    return QVariant();
}

To copy to clipboard, switch view to plain text mode 

Maybe it is usefull to make additional query in psql driver and return realyId or make additional method?!?

for testing i use such table:

CREATE TABLE test (
  id oid NOT NULL DEFAULT nextval('test_autoin_sequence'::regclass),
  "name" text
)
WITH (
  OIDS=TRUE
);

CREATE TABLE test (
  id oid NOT NULL DEFAULT nextval('test_autoin_sequence'::regclass),
  "name" text
)
WITH (
  OIDS=TRUE
);

To copy to clipboard, switch view to plain text mode 

sequence create:

CREATE SEQUENCE test_autoin_sequence
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 31
  CACHE 1;

CREATE SEQUENCE test_autoin_sequence
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 31
  CACHE 1;

To copy to clipboard, switch view to plain text mode 

thanks

[asvil]

For pgsql insert query with returning clause:
INSERT INTO tbl1(col1,col2) VALUES (DEFAULT, 'adsf') RETURNING col1; -- or RETURNING * for all columns
After that:
QSqlQuery::next();
And read returning values (for examle, generated by sequence)
May be oracle have same mechanism.

[feniksa]

Thank you for your help, i will try your advice for oracle, postgres and for mysql. If it works (or not) i will write reply