redBeard
11th October 2011, 00:09
I'm developing a program that will work with both MySQL and SQLServer databases.
On MySQL, I can successfully use the QtSQL module to insert records into the database - the tables have a an 'AUTO_INCREMENT' key field on them. I can successfully retrieve the 'last Insert ID' value via the 'QSqlQuery::lastInsertId ()' method. Works great.
On SQLServer, that feature does not exist so I updated my program to issue a QSqlQuery of "SELECT SCOPE_IDENTITY()". This seems to execute but when I retrieve the resultant values from the query, I get one value and it is always 0.
In a SQL Server Management Studio query window, I successfully inserted a record into a table (as my program does) and executed the "SELECT SCOPE_IDENTITY()" query immediately afterward and it retrieved the correct value.
I've tried a number of options - using transactions and no transactions. commiting the INSERT transaction before issuing the SELECT transaction, etc.
Basic code:
db = QSqlDatabase::database(dbName, true);
// db.transaction();
QString queryStr = "INSERT INTO ADDRESS (ts, address1) "
" VALUES (:ts, :address1)";
QSqlQuery qQuery (db);
qQuery.prepare(queryStr);
QDateTime ts = QDateTime::currentDateTime();
qQuery.bindValue (":ts", QVariant (ts));
qQuery.bindValue (":address1", QVariant ("test address1"));
if (qQuery.exec()) {
cout << "Success!" << endl;
// db.commit();
QSqlQuery query2 (db);
query2.prepare ("SELECT SCOPE_IDENTITY()");
query2.setForwardOnly(true);
if (query2.exec()) {
while (query2.next()) {
QVariant dbValue = query2.value(0);
cout << "testdb(): DB value: " << dbValue.toString().toStdString() << endl;
}
} else {
// error
}
} else {
// error
}
Ideas?
Added after 1 1:
This appears to be a SQLServer 2008 bug (http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811) and I'm using SQLServer 2008. Grr.....
On MySQL, I can successfully use the QtSQL module to insert records into the database - the tables have a an 'AUTO_INCREMENT' key field on them. I can successfully retrieve the 'last Insert ID' value via the 'QSqlQuery::lastInsertId ()' method. Works great.
On SQLServer, that feature does not exist so I updated my program to issue a QSqlQuery of "SELECT SCOPE_IDENTITY()". This seems to execute but when I retrieve the resultant values from the query, I get one value and it is always 0.
In a SQL Server Management Studio query window, I successfully inserted a record into a table (as my program does) and executed the "SELECT SCOPE_IDENTITY()" query immediately afterward and it retrieved the correct value.
I've tried a number of options - using transactions and no transactions. commiting the INSERT transaction before issuing the SELECT transaction, etc.
Basic code:
db = QSqlDatabase::database(dbName, true);
// db.transaction();
QString queryStr = "INSERT INTO ADDRESS (ts, address1) "
" VALUES (:ts, :address1)";
QSqlQuery qQuery (db);
qQuery.prepare(queryStr);
QDateTime ts = QDateTime::currentDateTime();
qQuery.bindValue (":ts", QVariant (ts));
qQuery.bindValue (":address1", QVariant ("test address1"));
if (qQuery.exec()) {
cout << "Success!" << endl;
// db.commit();
QSqlQuery query2 (db);
query2.prepare ("SELECT SCOPE_IDENTITY()");
query2.setForwardOnly(true);
if (query2.exec()) {
while (query2.next()) {
QVariant dbValue = query2.value(0);
cout << "testdb(): DB value: " << dbValue.toString().toStdString() << endl;
}
} else {
// error
}
} else {
// error
}
Ideas?
Added after 1 1:
This appears to be a SQLServer 2008 bug (http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811) and I'm using SQLServer 2008. Grr.....