Results 1 to 5 of 5

Thread: SQLite function to return rowid on INSERT

  1. #1
    Join Date
    Apr 2014
    Posts
    116
    Thanks
    8
    Qt products
    Qt5
    Platforms
    MacOS X

    Default SQLite function to return rowid on INSERT

    Hi,

    I would like to write a function within my QML project that inserts data into a SQLite table and returns the rowid of the inserted line on success.
    This is what i got so far:
    Qt Code:
    1. function saveData(name, mode, adjust) {
    2. var db = getDatabase();
    3. var res = "";
    4. db.transaction(function(tx) {
    5. var rs = tx.executeSql('INSERT INTO profiles ' +
    6. 'VALUES (?,?,?);', [name, mode, adjust]);
    7. if (rs.rowsAffected > 0) {
    8. res = tx.executeSql('SELECT last_insert_rowid();');
    9. } else {
    10. res = "Error";
    11. }
    12. });
    13. console.log(res)
    14. return res;
    15. }
    To copy to clipboard, switch view to plain text mode 
    But I do net get an integer back, I get an object. How is this done right and is there a better way to do it than what I tried?

    Thanks.

  2. #2
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: SQLite function to return rowid on INSERT

    The executeSql() call returns an SQLResultSet object that already contains the insertId if one was generated. No need for a separate query.
    http://www.w3.org/TR/2009/WD-webdata...-query-results

    BTW: Does the profile table have an INTEGER PRIMARY KEY column that can provide the auto-generated id?

  3. #3
    Join Date
    Apr 2014
    Posts
    116
    Thanks
    8
    Qt products
    Qt5
    Platforms
    MacOS X

    Default Re: SQLite function to return rowid on INSERT

    Thanks for the answer, I guessed that there is a better way to do it but I am still struggling to find out how to access this insertId property.

    At the moment I do not have a primary key defined. My understanding was that a SQLite table always has as ID column rowid unless I prevent it. Is it not possible to access rowid with insertId?
    Qt Code:
    1. function initialize() {
    2. var db = getDatabase();
    3.  
    4. db.transaction(
    5. function(tx) {
    6. tx.executeSql('CREATE TABLE IF NOT EXISTS profiles ('+
    7. 'name TEXT UNIQUE,' +
    8. 'mode INTEGER,' +
    9. 'adjust INTEGER)');
    10. });
    11. }
    To copy to clipboard, switch view to plain text mode 

  4. #4
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: SQLite function to return rowid on INSERT

    Yes, you access that internal id by declaring a column of type "integer primary key" , effectively an alias for the internal row id, and then it is returned as the last insert id. Without that alias the internal row id is purely internal and hidden.

    The last insert id should be available as rs.insertId at line 7 in your original code.

  5. #5
    Join Date
    Apr 2014
    Posts
    116
    Thanks
    8
    Qt products
    Qt5
    Platforms
    MacOS X

    Default Re: SQLite function to return rowid on INSERT

    Thanks for the help. I've been playing around a little the rowid is returned with rs.insertId even if I did not declare a primary key.

Similar Threads

  1. Replies: 4
    Last Post: 1st February 2014, 22:13
  2. Replies: 1
    Last Post: 2nd January 2013, 10:48
  3. SQLite doesnt return data
    By HelderC in forum Qt Programming
    Replies: 6
    Last Post: 29th May 2011, 13:56
  4. Replies: 2
    Last Post: 4th August 2010, 14:06
  5. Bulk insert into SQLite
    By munna in forum Qt Programming
    Replies: 6
    Last Post: 19th November 2007, 04:56

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.