SQLite with Qt4

[MIH1406]

Hi,

I have tried to work with SQlite I have created a connection, a database and a table. Everything seems ready but when I tried to add some records using QSqlDatabase::exec() I faild because I need to provide the primary key for each column I have to add.

If this is my table:

Query = "CREATE TABLE `persons` ("
             "`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,"
             "`name` VARCHAR( 255 ) NOT NULL)";

Query = "CREATE TABLE `persons` ("
             "`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,"
             "`name` VARCHAR( 255 ) NOT NULL)";

To copy to clipboard, switch view to plain text mode 

And this is my Query for records to be added from a LineEdit object:

QString submitQuery = "INSERT INTO `persons` (`id`, `name`) VALUES (NULL, '" + personName + "')";

QString submitQuery = "INSERT INTO `persons` (`id`, `name`) VALUES (NULL, '" + personName + "')";

To copy to clipboard, switch view to plain text mode 

Is there any way to make the `id field filled automatically just like with PHP. It should be increased by one every time I added a record.

Thank you,
Mohammad AlHobayyeb

[Grimlock]

Try using the prepare and bindValue methods of QSqlQuery.

QSqlQuery qry;
qry.prepare("SELECT Minia, Format FROM Dane where Id=:id");
qry.bindValue(":id",id);

QSqlQuery qry;
qry.prepare("SELECT Minia, Format FROM Dane where Id=:id");
qry.bindValue(":id",id);

To copy to clipboard, switch view to plain text mode 

sorry wrong answer.
I can't think of anything that would preform the auto incrementation You would like to achieve. I always end up writing it by my self selecting the maximal value and incrementing it manually..

[MIH1406]

No problem how to get the maximal value in a table?

Thanks

[Grimlock]

Select max(id) form TableName

Select max(id) form TableName

To copy to clipboard, switch view to plain text mode 

Should do the trick.

Or You may try.

QSqlQuery qry;
qry.prepare("SELECT Id FROM TableName Order by Id");
if(!qry.exec())
{
        qFatal("Failed to count records");
        return;
 }
 while(qry.next())
 {
        int index = qry.value(0).toInt();
        if(temp < index)
            break;
        else
            ++temp;
 }

QSqlQuery qry;
qry.prepare("SELECT Id FROM TableName Order by Id");
if(!qry.exec())
{
        qFatal("Failed to count records");
        return;
 }
 while(qry.next())
 {
        int index = qry.value(0).toInt();
        if(temp < index)
            break;
        else
            ++temp;
 }

To copy to clipboard, switch view to plain text mode 

This will get you the first free id.If by any chance You would get some free ids in between due to dropping a row.

[lyuts]

The doc on SQLite says that the keyword is AUTOINCREMENT, not AUTO_INCREMENT. This should take care of your primary key when you insert row without specifying the id.

[MIH1406]

That is a very good point. But where is the SQLite docs?

Thank you
Mohammad

[Lykurg]

But where is the SQLite docs?

www.sqlite.org?