SQLITE insert problem

[rdf]

Hy everyone,

I'm really struggling with a strange SQLITE database problem. I'm working under Linux (Slackware 13.37), Qt SDK 4.7.4.

My application opens a database in the "main" function like this:

db = QSqlDatabase::addDatabase ("QSQLITE");
db.setDatabaseName (QCoreApplication::applicationDirPath () + "/mydb");
if (!db.open ())
   qDebug () << "error opening database";
else
   qDebug () << "database opened";

db = QSqlDatabase::addDatabase ("QSQLITE");
db.setDatabaseName (QCoreApplication::applicationDirPath () + "/mydb");
if (!db.open ())
   qDebug () << "error opening database";
else
   qDebug () << "database opened";

To copy to clipboard, switch view to plain text mode 

Up to here, all fine. Every 10 minutes it has to write a new line into a table. Before doing that, it makes a SELECT operation on the same table to check if there is already data identified by the same key (there are reasons for this, which are not important in our context).

The table SQL schema is:

CREATE TABLE consumi
(
   year INTEGER NOT NULL,
   month INTEGER NOT NULL,
   day INTEGER NOT NULL,
   hour INTEGER NOT NULL,
   data1 INTEGER NOT NULL,
   data2 INTEGER NOT NULL,
   PRIMARY KEY (year, month, day, hour)
);

CREATE TABLE consumi
(
   year INTEGER NOT NULL,
   month INTEGER NOT NULL,
   day INTEGER NOT NULL,
   hour INTEGER NOT NULL,
   data1 INTEGER NOT NULL,
   data2 INTEGER NOT NULL,
   PRIMARY KEY (year, month, day, hour)
);

To copy to clipboard, switch view to plain text mode 

And this is the code:

bool stop = false;
 
QString sel = QString ("SELECT year FROM consumi WHERE year=%1 AND month=%2 AND day=%3 AND hour=%4")
    .arg (year)
    .arg (month)
    .arg (day)
    .arg (hour);
 
QSqlQuery q;
 
if (!q.exec (sel))
{
    qDebug () << "error during the SELECT query";
    qDebug () << q.lastError ();
}
else
{
   if (q.next ())
   {
      qDebug () << "data already exists for this key";
      stop = true;
   }
}
 
q.clear ();
 
QSqlQuery q1;
 
if (!stop)
{
    QString s = QString ("INSERT INTO consumi VALUES (\"%1\", \"%2\", \"%3\", \"%4\", \"%5\", \"%6\")")
       .arg (year)
       .arg (month)
       .arg (day)
       .arg (hour)
       .arg (data1)
       .arg (data2);
 
   if (!q1.exec (s))
   {
      qDebug () << "error during the INSERT query";
      qDebug () << q1.lastError ();
   }
 
   if (q1.isActive ())
      qDebug () << "all fine";
}

bool stop = false;

QString sel = QString ("SELECT year FROM consumi WHERE year=%1 AND month=%2 AND day=%3 AND hour=%4")
    .arg (year)
    .arg (month)
    .arg (day)
    .arg (hour);

QSqlQuery q;

if (!q.exec (sel))
{
    qDebug () << "error during the SELECT query";
    qDebug () << q.lastError ();
}
else
{
   if (q.next ())
   {
      qDebug () << "data already exists for this key";
      stop = true;
   }
}

q.clear ();

QSqlQuery q1;

if (!stop)
{
    QString s = QString ("INSERT INTO consumi VALUES (\"%1\", \"%2\", \"%3\", \"%4\", \"%5\", \"%6\")")
       .arg (year)
       .arg (month)
       .arg (day)
       .arg (hour)
       .arg (data1)
       .arg (data2);

   if (!q1.exec (s))
   {
      qDebug () << "error during the INSERT query";
      qDebug () << q1.lastError ();
   }

   if (q1.isActive ())
      qDebug () << "all fine";
}

To copy to clipboard, switch view to plain text mode 

Unfortunately, this is what i always get:

error during the INSERT query
QSqlError(14, "Unable to fetch row", "unable to open database file")

Actually, the database is open as every time the SELECT statement is executed without problems. I added the "q1.clear ()" call after reading about it somewhere, but it did not solve the problem. Things don't go better if I omit the SELECT query: no way at all.

I think I tried all I could think about... I would appreciate some hints.

Thank you!
Roberto

[norobro]

The code that you posted works fine here.

[ChrisW67]

Tips: You use single quotes around strings in SQL, not double quotes (line 31). In your table definition and select query you treat year, month, day, and hour as numbers (line 3) and in your insert you treat them as strings. All your columns are integer types not strings. You should get into the habit of using QSQlQuery::prepare() and bindValue() rather than constructing queries as strings because it removes bunches of security issues and handles quoting properly for you.

[rdf]

Dear norobro and ChrisW67, thank you for your answers. And: solved!!!

@norobro: a difference between your test program and my software is that you create the database file, while I open an existing one. So I slightly modified yours (commenting out the CREATE TABLE query), put it on the same machine of the other and run in the same conditions. And I got it: it sounds stupid, but it was a permission problem. But... I was not helped by the SQLITE driver error messages.

If the user launching the program does not have write permission on the existing database FILE, you get the error "attempt to write a readonly database" while attempting to INSERT. But if you have write permission on the database file, but not on the DIRECTORY which contains it, then you get "unable to open database file".

So: I was not careful about it, but I think the error message could be more pertinent.

@ChrisW67: I totally agree with you, but there's a reason why I do this. Time ago I had problems with another DB project because a lot of the numerous queries worked perfectly on the development machine, but not on the target machine, giving some strange errors (same libraries versions!!!). At the end I found out that the problem was with the QSqlQuery prepare/bindValue mechanism (I found some informations about this in a forum, maybe this one), which seemed to be buggy. So I had to rewrite all the queries using QString and arg, like you've seen here. Since then I just do directly like this.

Actually, inserting as strings (thanks for the hint about the single/double quotes) was a desperate way to try to solve my problem... originally I used no quotes. Anyway, AFAIK, in SQLITE has a particular way to deal with data types (http://www.sqlite.org/datatype3.html), and even using quotes here is ok. But you're perfectly right about good programming techniques.

Thanks!
Roberto