Sqlite multipule row insert question

**rogerholmes** · 29th December 2009, 20:35

I am trying to insert several rows into sqlite database, i have searched this fourm and the net but still can not get this to work, it just inserts the first row correctly but nothing is inserted after the first row. I have verified the query is updated to the correct values on each loop. can you help me?

Qt Code:

Switch view

templatedb.transaction();
            while(!db->eof()){
 
            theQuery="";
            theQuery ="INSERT INTO reporttemplates (reportname ,itemtag,itemposx,itemposy,scale,angle,zvalue,data,originalrect,currentrect,";
            theQuery =theQuery+"type,fontindex,color,fntsize,isbold,isitalic,isunderlined,pagenum,showonallpages,opacity)";
            theQuery =theQuery+ " VALUES('"+ui->lineEdit->text()+"','"+db->getCell(db->currentRow(),0)+"','"+db->getCell(db->currentRow(),1)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),2)+"','"+db->getCell(db->currentRow(),3)+"','"+db->getCell(db->currentRow(),4)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),5)+"','"+db->getCell(db->currentRow(),6)+"','"+db->getCell(db->currentRow(),7)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),8)+"','"+db->getCell(db->currentRow(),9)+"','"+db->getCell(db->currentRow(),10)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),11)+"','"+db->getCell(db->currentRow(),12)+"','"+db->getCell(db->currentRow(),13)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),14)+"','"+db->getCell(db->currentRow(),15)+"','"+db->getCell(db->currentRow(),16)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),17)+"','"+db->getCell(db->currentRow(),18)+"')" ;
            templatedb.exec(theQuery);
 
 
 
            db->next();
            int row =db->currentRow();
        }
 
templatedb.exec("COMMIT");

templatedb.transaction();
            while(!db->eof()){

            theQuery="";
            theQuery ="INSERT INTO reporttemplates (reportname ,itemtag,itemposx,itemposy,scale,angle,zvalue,data,originalrect,currentrect,";
            theQuery =theQuery+"type,fontindex,color,fntsize,isbold,isitalic,isunderlined,pagenum,showonallpages,opacity)";
            theQuery =theQuery+ " VALUES('"+ui->lineEdit->text()+"','"+db->getCell(db->currentRow(),0)+"','"+db->getCell(db->currentRow(),1)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),2)+"','"+db->getCell(db->currentRow(),3)+"','"+db->getCell(db->currentRow(),4)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),5)+"','"+db->getCell(db->currentRow(),6)+"','"+db->getCell(db->currentRow(),7)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),8)+"','"+db->getCell(db->currentRow(),9)+"','"+db->getCell(db->currentRow(),10)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),11)+"','"+db->getCell(db->currentRow(),12)+"','"+db->getCell(db->currentRow(),13)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),14)+"','"+db->getCell(db->currentRow(),15)+"','"+db->getCell(db->currentRow(),16)+"',";
            theQuery =theQuery+"'"+db->getCell(db->currentRow(),17)+"','"+db->getCell(db->currentRow(),18)+"')" ;
            templatedb.exec(theQuery);



            db->next();
            int row =db->currentRow();
        }

templatedb.exec("COMMIT");

To copy to clipboard, switch view to plain text mode

**ChrisW67** · 29th December 2009, 21:11

What does QSqlDatabase::lastError () tell you about each attempted insert?

With the code I can see you will need to be wary of single quotes appearing in the source data because it will break the SQL. Reportname and data look like the obvious candidates but everything is quoted in your SQL, so anything could be. If you have this happening then perhaps you should consider QSqlQuery::prepare() and QSqlQuery::bindValue() rather than directly accessing the database driver with a constructed string.

You would typically use QSqlDatabase::commit() to commit your transaction.

**rogerholmes** · 29th December 2009, 21:32

ChrisW,

thanks for your answer, I used the QDebug (i should have thought of that

)

it gives error 19 unable to fetch row , constraint failed.

i am not sure if that means it can not get info from sqlite or the "db" database.

**rogerholmes** · 31st December 2009, 07:33

I have changed to bindvalue ,still same problem.

the first record is inserted into the sqlite database (templatedb) correctly but no record is inserted after that. i have found that the query goes inactive after the first successful query.exec().

I have found several post about this behavior but i can find NO solution. Does ANYBODY know what the problem is?

Qt Code:

Switch view

int x=0;
int numrecords= db.recordcount;
 
while(x<=numrecords){
    QString theQuery;
    QSqlQuery query(theQuery, templatedb) ;
 
 
                theQuery="";
                theQuery ="INSERT INTO reporttemplates (reportname,itemtag,itemposx,itemposy,scale,angle,zvalue,data,originalrect,currentrect,";
                theQuery =theQuery+"type,fontindex,color,fntsize,isbold,isitalic,isunderlined,pagenum,showonallpages,opacity)";
                theQuery =theQuery+ " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
 
               query.prepare(theQuery);
 
               query.bindValue(0,ui->lineEdit->text());
               query.bindValue(1,db->getCell(row,0));  
               query.bindValue(2,db->getCell(row,1));
               query.bindValue(3,db->getCell(row,2));
               query.bindValue(4,db->getCell(row,3));
               query.bindValue(5,db->getCell(row,4));
               query.bindValue(6,db->getCell(row,5));
               query.bindValue(7,db->getCell(row,6));
               query.bindValue(8,db->getCell(row,7));
               query.bindValue(9,db->getCell(row,8));
               query.bindValue(10,db->getCell(row,9));
               query.bindValue(11,db->getCell(row,10));
               query.bindValue(12,db->getCell(row,11));
               query.bindValue(13,db->getCell(row,12));
               query.bindValue(14,db->getCell(row,13));
               query.bindValue(15,db->getCell(row,14));
               query.bindValue(16,db->getCell(row,15));
               query.bindValue(17,db->getCell(row,16));
               query.bindValue(18,db->getCell(row,17));
               query.bindValue(19,db->getCell(row,18));
 
 
      qDebug() << query.exec();  <- works correctly one time , next returns false.
      query.next(); <-try to move to last row 
 
      qDebug() << query.lastError(); <-2nd time returns QSqlError(19,"unable to fetch row", "constraint failed")
x++
}

int x=0;
int numrecords= db.recordcount;

while(x<=numrecords){
    QString theQuery;
    QSqlQuery query(theQuery, templatedb) ;


                theQuery="";
                theQuery ="INSERT INTO reporttemplates (reportname,itemtag,itemposx,itemposy,scale,angle,zvalue,data,originalrect,currentrect,";
                theQuery =theQuery+"type,fontindex,color,fntsize,isbold,isitalic,isunderlined,pagenum,showonallpages,opacity)";
                theQuery =theQuery+ " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

               query.prepare(theQuery);

               query.bindValue(0,ui->lineEdit->text());
               query.bindValue(1,db->getCell(row,0));  
               query.bindValue(2,db->getCell(row,1));
               query.bindValue(3,db->getCell(row,2));
               query.bindValue(4,db->getCell(row,3));
               query.bindValue(5,db->getCell(row,4));
               query.bindValue(6,db->getCell(row,5));
               query.bindValue(7,db->getCell(row,6));
               query.bindValue(8,db->getCell(row,7));
               query.bindValue(9,db->getCell(row,8));
               query.bindValue(10,db->getCell(row,9));
               query.bindValue(11,db->getCell(row,10));
               query.bindValue(12,db->getCell(row,11));
               query.bindValue(13,db->getCell(row,12));
               query.bindValue(14,db->getCell(row,13));
               query.bindValue(15,db->getCell(row,14));
               query.bindValue(16,db->getCell(row,15));
               query.bindValue(17,db->getCell(row,16));
               query.bindValue(18,db->getCell(row,17));
               query.bindValue(19,db->getCell(row,18));


      qDebug() << query.exec();  <- works correctly one time , next returns false.
      query.next(); <-try to move to last row 

      qDebug() << query.lastError(); <-2nd time returns QSqlError(19,"unable to fetch row", "constraint failed")
x++
}

To copy to clipboard, switch view to plain text mode

**ChrisW67** · 31st December 2009, 08:19

Line 39 should be

Qt Code:

Switch view

db.next()

db.next()

To copy to clipboard, switch view to plain text mode

like your original code shouldn't it?

The message regarding violation of a constraint can only be coming from the insert. Duplicate primary key value would be the first place to look although there can be other constraints defined on the reporttemplates table. It's not clear which (if any) column is a unique identifier for a row. If you are not inserting a value into any primary key column, and it is not an auto-increment type field, then the second row insert will have the same id as the first (e.g. NULL, 0 or empty) and fail.

**rogerholmes** · 31st December 2009, 16:36

THANK YOU CHRIS !!!

The primary key was THE issue.

Thread: Sqlite multipule row insert question

Thread Tools

Search Thread

Display

Sqlite multipule row insert question

Re: Sqlite multipule row insert question

Re: Sqlite multipule row insert question

Re: Sqlite multipule row insert question

Re: Sqlite multipule row insert question

The following user says thank you to ChrisW67 for this useful post:

Re: Sqlite multipule row insert question

Similar Threads

Insert unicode in SQlite

SQLite sometimes doens't INSERT into database

SQLite beginner question...

How to increase the speed of insertion of data into table in SQLITE

Bulk insert into SQLite

Bookmarks

Bookmarks

Posting Permissions