QtSql & SCOPE_IDENTITY in SQL Server

[naroin]

hi,

i use a IDENTITY field in a SQL Server table, and i want to get the autogenerated value of the row i've just added.

my table is as follow :

create table MyTable
(IdTable INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name varchar(128) NOT NULL,
Value INTEGER NOT NULL);

my code is as follow :


db.transaction();

QSqlQuery query(db);

if (!query.exec("insert into MyTable (Name, Value) values ('A name', 1234);"))
{
QMessageBox::critical(this, "Db error", "DB error in INSERT statement : " + query.lastError().text());
db.rollback();
return;
}
if (!query.exec("SELECT SCOPE_IDENTITY()"))
{
QMessageBox::critical(this, "Db error", "DB error in SCOPE_IDENTITY statement : " + query.lastError().text());
db.rollback();
return;
}
if (query.next())
{
qDebug()<<"SCOPE_IDENTITY="<<query.value(0);
}
db.commit();


It seems that i get a result for "SELECT SCOPE_IDENTITY()", but it is always 0.
qDebug()<<query.value(0); always prints : "SCOPE_IDENTITY= QVariant(double, 0)"
while the real value generated is NOT 0.

Have anyone got the same problem?

thanks

[naroin]

any idea?

it works very well with OTL


otl_connect db2;
otl_connect::otl_initialize(); // initialize ODBC environment
long ovalue;
db2.rlogon("UID=user;PWD=password;DSN=db"); // connect to ODBC


otl_stream i(1, // buffer size needs to be set to 1
"SELECT IDENT_CURRENT('MyTable')",
// SELECT statement
db2 // connect object
);

i>>ovalue;

qDebug()<<ovalue;

db2.logoff();

IDENT_CURRENT(x) does NOT work with QtSql.

[unit]

Does it work without QT?

May be SELECT SCOPE_IDENTITY() is needed to use with INSERT STATMENT?


query.exec("insert into MyTable (Name, Value) values ('A name', 1234); SELECT SCOPE_IDENTITY()"))

Can you use QVariant QSqlQuery::lastInsertId () instead?

[naroin]

yes it works without Qt (i tried with OTL, as i said, and it works wery well)

SCOPE_IDENTITY or IDENT_CURRENT do not need to be executed just after a INSERT, but i tried your code, and that still does not work.

lastInsertId() does not work either.
db.driver()->hasFeature(QSqlDriver::LastInsertId) returns false...