Results 1 to 4 of 4

Thread: QtSql & SCOPE_IDENTITY in SQL Server

  1. #1
    Join Date
    Nov 2010
    Posts
    28
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4
    Platforms
    Windows

    Default QtSql & SCOPE_IDENTITY in SQL Server

    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 :
    Qt Code:
    1. create table MyTable
    2. (IdTable INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
    3. Name varchar(128) NOT NULL,
    4. Value INTEGER NOT NULL);
    To copy to clipboard, switch view to plain text mode 

    my code is as follow :
    Qt Code:
    1. db.transaction();
    2.  
    3. QSqlQuery query(db);
    4.  
    5. if (!query.exec("insert into MyTable (Name, Value) values ('A name', 1234);"))
    6. {
    7. QMessageBox::critical(this, "Db error", "DB error in INSERT statement : " + query.lastError().text());
    8. db.rollback();
    9. return;
    10. }
    11. if (!query.exec("SELECT SCOPE_IDENTITY()"))
    12. {
    13. QMessageBox::critical(this, "Db error", "DB error in SCOPE_IDENTITY statement : " + query.lastError().text());
    14. db.rollback();
    15. return;
    16. }
    17. if (query.next())
    18. {
    19. qDebug()<<"SCOPE_IDENTITY="<<query.value(0);
    20. }
    21. db.commit();
    To copy to clipboard, switch view to plain text mode 

    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

  2. #2
    Join Date
    Nov 2010
    Posts
    28
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: QtSql & SCOPE_IDENTITY in SQL Server

    any idea?

    it works very well with OTL

    Qt Code:
    1. otl_connect db2;
    2. otl_connect::otl_initialize(); // initialize ODBC environment
    3. long ovalue;
    4. db2.rlogon("UID=user;PWD=password;DSN=db"); // connect to ODBC
    5.  
    6.  
    7. otl_stream i(1, // buffer size needs to be set to 1
    8. "SELECT IDENT_CURRENT('MyTable')",
    9. // SELECT statement
    10. db2 // connect object
    11. );
    12.  
    13. i>>ovalue;
    14.  
    15. qDebug()<<ovalue;
    16.  
    17. db2.logoff();
    To copy to clipboard, switch view to plain text mode 

    IDENT_CURRENT(x) does NOT work with QtSql.

  3. #3
    Join Date
    Oct 2010
    Location
    Belarus
    Posts
    71
    Thanks
    1
    Thanked 9 Times in 9 Posts
    Qt products
    Qt4
    Platforms
    Windows Maemo/MeeGo

    Default Re: QtSql & SCOPE_IDENTITY in SQL Server

    Does it work without QT?

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

    Qt Code:
    1. query.exec("insert into MyTable (Name, Value) values ('A name', 1234); SELECT SCOPE_IDENTITY()"))
    To copy to clipboard, switch view to plain text mode 

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

  4. #4
    Join Date
    Nov 2010
    Posts
    28
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: QtSql & SCOPE_IDENTITY in SQL Server

    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...

Similar Threads

  1. QtService & QtSql
    By acedanans in forum Qt Programming
    Replies: 11
    Last Post: 3rd October 2014, 13:30
  2. QtSql , how to list all databases on the Mysql-Server
    By luoihoc in forum Qt Programming
    Replies: 1
    Last Post: 7th July 2009, 22:52
  3. stored proc with QtSql
    By xyzt in forum Qt Programming
    Replies: 1
    Last Post: 17th February 2009, 10:12
  4. about Windows Ado and QtSql
    By lutins in forum General Programming
    Replies: 1
    Last Post: 1st September 2008, 21:04
  5. problem of QtSql
    By cresthong in forum Qt Programming
    Replies: 1
    Last Post: 17th July 2008, 18:48

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.