Trolltech Home | Qt-interest Home | Recent Threads | All Threads | Author | Date
All threads index page 2

Qt-interest Archive, October 2007
Problem with Sqlite queries

Message 1 in thread

Hi all,

I have a table info in my sqlite database. I do the following:

  QSqlQuery query;
  QString datetime = QDateTime::currentDateTime().toString();

  // Add to database
  query.prepare("INSERT INTO info (price, currency, date) "
		"VALUES (?, ?, ?)");
  query.bindValue(0, price);
  query.bindValue(1, currency);
  query.bindValue(2, datetime);
  query.exec();

  // Now, we need to add reference to productinfo
  query.prepare("SELECT id FROM info "
		"WHERE price = ? AND currency = ? AND datetime = ?");
  query.bindValue(0, price);
  query.bindValue(1, currency);
  query.bindValue(2, datetime);
  query.exec();

After this exec query.value(0) is invalid. Why is this so?

Is there any special reason for it?

Cheers,

-- 
 [ signature omitted ]
Message 2 in thread

Let me add that the table was created with:
query.exec("CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT,"
	       "price INTEGER, currency TEXT, date TEXT)");

On 06/10/2007, Paulo J. Matos <pocm@xxxxxxxxxxx> wrote:
> Hi all,
>
> I have a table info in my sqlite database. I do the following:
>
>   QSqlQuery query;
>   QString datetime = QDateTime::currentDateTime().toString();
>
>   // Add to database
>   query.prepare("INSERT INTO info (price, currency, date) "
>                 "VALUES (?, ?, ?)");
>   query.bindValue(0, price);
>   query.bindValue(1, currency);
>   query.bindValue(2, datetime);
>   query.exec();
>
>   // Now, we need to add reference to productinfo
>   query.prepare("SELECT id FROM info "
>                 "WHERE price = ? AND currency = ? AND datetime = ?");
>   query.bindValue(0, price);
>   query.bindValue(1, currency);
>   query.bindValue(2, datetime);
>   query.exec();
>
> After this exec query.value(0) is invalid. Why is this so?
>
> Is there any special reason for it?
>
> Cheers,
>
> --
> Paulo Jorge Matos - pocm at soton.ac.uk
> http://www.personal.soton.ac.uk/pocm
> PhD Student @ ECS
> University of Southampton, UK
>


-- 
 [ signature omitted ]
Message 3 in thread

Paulo J. Matos schrieb:
> Let me add that the table was created with:
> query.exec("CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT,"
> 	       "price INTEGER, currency TEXT, date TEXT)");
> 
in my sql tables i left out the 'autoincrement' - maybe that's the 
reason?!?!!

RZ

--
 [ signature omitted ]
Message 4 in thread

On 06/10/2007, RZ <rz@xxxxxxxxxxxx> wrote:
> Paulo J. Matos schrieb:
> > Let me add that the table was created with:
> > query.exec("CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT,"
> >              "price INTEGER, currency TEXT, date TEXT)");
> >
> in my sql tables i left out the 'autoincrement' - maybe that's the
> reason?!?!!
>

Are you sure? The AUTOINCREMENT tells sqlite to increment the id
automatically each time I had a new record. Without it, that wouldn't
happen, would it?

> RZ
>
> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with "unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
>
>
>
>


-- 
 [ signature omitted ]
Message 5 in thread

Paulo J. Matos schrieb:
> On 06/10/2007, RZ <rz@xxxxxxxxxxxx> wrote:
>> Paulo J. Matos schrieb:
>>> Let me add that the table was created with:
>>> query.exec("CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT,"
>>>              "price INTEGER, currency TEXT, date TEXT)");
>>>
>> in my sql tables i left out the 'autoincrement' - maybe that's the
>> reason?!?!!
>>
> 
> Are you sure? The AUTOINCREMENT tells sqlite to increment the id
> automatically each time I had a new record. Without it, that wouldn't
> happen, would it?
> 
Yes, i am - i looked for it before writing
afaik int primary key means 'autoincrement' - at least my sqlite tables 
work that way...

--
 [ signature omitted ]
Message 6 in thread

On 06/10/2007, RZ <rz@xxxxxxxxxxxx> wrote:
> Paulo J. Matos schrieb:
> > On 06/10/2007, RZ <rz@xxxxxxxxxxxx> wrote:
> >> Paulo J. Matos schrieb:
> >>> Let me add that the table was created with:
> >>> query.exec("CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT,"
> >>>              "price INTEGER, currency TEXT, date TEXT)");
> >>>
> >> in my sql tables i left out the 'autoincrement' - maybe that's the
> >> reason?!?!!
> >>
> >
> > Are you sure? The AUTOINCREMENT tells sqlite to increment the id
> > automatically each time I had a new record. Without it, that wouldn't
> > happen, would it?
> >
> Yes, i am - i looked for it before writing
> afaik int primary key means 'autoincrement' - at least my sqlite tables
> work that way...
>

It seems you're right in that using autoincrement in sqlite 3 works is
not required for autoincrementing the primary key, however, from the
docs, it doesn't seem that autoincrement is useless:
"An INTEGER PRIMARY KEY column can also include the keyword
AUTOINCREMENT. The AUTOINCREMENT keyword modified the way that B-Tree
keys are automatically generated."

So I guess it might be more efficient to use autoincrement is that's
what you want.

Again, the use or not of autoincrement seems to not be the cause of my
problem. :-(

> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with "unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
>
>
>
>


-- 
 [ signature omitted ]
Message 7 in thread

On Sat, 2007-10-06 at 21:11 +0100, Paulo J. Matos wrote:
> Let me add that the table was created with:
> query.exec("CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT,"
> 	       "price INTEGER, currency TEXT, date TEXT)");
> 

[snip]

> >
> >   // Add to database
> >   query.prepare("INSERT INTO info (price, currency, date) "
> >                 "VALUES (?, ?, ?)");

[snip]

> >   query.prepare("SELECT id FROM info "
> >                 "WHERE price = ? AND currency = ? AND datetime = ?");
> >   

Just to state the obvious, the column is called "date" in the create and
insert statements, but it is called "datetime" in the select.

If this is just a typo in your posting, then use error handling and
query.lastError() to find out what is failing. i.e. do this at least
after the insert, and the exec for the select.

Hope this helps,

Stephen Jackson



--
 [ signature omitted ]
Message 8 in thread

On 07/10/2007, Stephen Jackson <spjackson42@xxxxxxxxx> wrote:
> On Sat, 2007-10-06 at 21:11 +0100, Paulo J. Matos wrote:
> > Let me add that the table was created with:
> > query.exec("CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT,"
> >              "price INTEGER, currency TEXT, date TEXT)");
> >
>
> [snip]
>
> > >
> > >   // Add to database
> > >   query.prepare("INSERT INTO info (price, currency, date) "
> > >                 "VALUES (?, ?, ?)");
>
> [snip]
>
> > >   query.prepare("SELECT id FROM info "
> > >                 "WHERE price = ? AND currency = ? AND datetime = ?");
> > >
>
> Just to state the obvious, the column is called "date" in the create and
> insert statements, but it is called "datetime" in the select.
>
> If this is just a typo in your posting, then use error handling and
> query.lastError() to find out what is failing. i.e. do this at least
> after the insert, and the exec for the select.
>

You just stated the obvious, however, I couldn't see the obvious even
though I read it 100 times.

Thanks,

Paulo Matos

> Hope this helps,
>
> Stephen Jackson
>
>
>
> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with "unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
>
>
>
>


-- 
 [ signature omitted ]
Message 9 in thread

Hello once again,

Even after solving the 'obvious' problem you mentioned things are not
working as they should.

The table created with:
if(!query.exec("CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT,"
		   "price INTEGER, currency TEXT, date TEXT)"))
      emit log("AmazonKuKu", QString("Query resulted in error:
%1\n").arg(query.lastError().text()));

is ok. Nothing is logged as an error.
However, when I try:
  // Add to database
  query.prepare("INSERT INTO info (price, currency, date) "
		"VALUES (?, '?', '?')");
  query.bindValue(0, price);
  query.bindValue(1, currency);
  query.bindValue(2, datetime);
  emit log("AmazonKuKuView", QString("Executing Query:\n\tINSERT INTO
info (price, currency, date) VALUES (%1, '%2',
'%3')\n").arg(price).arg(currency).arg(datetime));

  if(!query.exec())
    emit log("AmazonKuKuView", QString("Query resulted in error:
%1\n").arg(query.lastError().text()));

I get in the log:
*** AmazonKuKuView:
Executing Query:
        INSERT INTO info (price, currency, date) VALUES (774, 'GBP',
'Sun Oct 7 16:13:49 2007')

*** AmazonKuKuView:
Query resulted in error:  Parameter count mismatch

Why does it say parameter count mismatch? Because I didn't mention the
id field? Don't think that's a problem because it's automatically
incremented right?

If I try the sqlbrowser demo and insert this exact sql statements
everything seems to turn out just ok.

Any ideas/help would be great!

Cheers,
-- 
 [ signature omitted ]
Message 10 in thread

Try using query.clear() after each your query.exec()

Cheers,

Pavel Zdenek

2007/10/7, Paulo J. Matos <pocm@xxxxxxxxxxx>:
> Hello once again,
>
> Even after solving the 'obvious' problem you mentioned things are not
> working as they should.
>
> The table created with:
> if(!query.exec("CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT,"
>                    "price INTEGER, currency TEXT, date TEXT)"))
>       emit log("AmazonKuKu", QString("Query resulted in error:
> %1\n").arg(query.lastError().text()));
>
> is ok. Nothing is logged as an error.
> However, when I try:
>   // Add to database
>   query.prepare("INSERT INTO info (price, currency, date) "
>                 "VALUES (?, '?', '?')");
>   query.bindValue(0, price);
>   query.bindValue(1, currency);
>   query.bindValue(2, datetime);
>   emit log("AmazonKuKuView", QString("Executing Query:\n\tINSERT INTO
> info (price, currency, date) VALUES (%1, '%2',
> '%3')\n").arg(price).arg(currency).arg(datetime));
>
>   if(!query.exec())
>     emit log("AmazonKuKuView", QString("Query resulted in error:
> %1\n").arg(query.lastError().text()));
>
> I get in the log:
> *** AmazonKuKuView:
> Executing Query:
>         INSERT INTO info (price, currency, date) VALUES (774, 'GBP',
> 'Sun Oct 7 16:13:49 2007')
>
> *** AmazonKuKuView:
> Query resulted in error:  Parameter count mismatch
>
> Why does it say parameter count mismatch? Because I didn't mention the
> id field? Don't think that's a problem because it's automatically
> incremented right?
>
> If I try the sqlbrowser demo and insert this exact sql statements
> everything seems to turn out just ok.
>
> Any ideas/help would be great!
>
> Cheers,
> --
> Paulo Jorge Matos - pocm at soton.ac.uk
> http://www.personal.soton.ac.uk/pocm
> PhD Student @ ECS
> University of Southampton, UK
>
> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with "unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
>
>

--
 [ signature omitted ]
Message 11 in thread

On 07/10/2007, Pavel Zdenek <pavel.zdenek@xxxxxxxxx> wrote:
> Try using query.clear() after each your query.exec()
>

Even by doing that the problem is not solved. For example, the code in
http://qtnode.net/index.php/pastebin/4831 doesn't work even if I use
query.clear() after each query.exec().
 :-(

> Cheers,
>
> Pavel Zdenek
>
> 2007/10/7, Paulo J. Matos <pocm@xxxxxxxxxxx>:
> > Hello once again,
> >
> > Even after solving the 'obvious' problem you mentioned things are not
> > working as they should.
> >
> > The table created with:
> > if(!query.exec("CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT,"
> >                    "price INTEGER, currency TEXT, date TEXT)"))
> >       emit log("AmazonKuKu", QString("Query resulted in error:
> > %1\n").arg(query.lastError().text()));
> >
> > is ok. Nothing is logged as an error.
> > However, when I try:
> >   // Add to database
> >   query.prepare("INSERT INTO info (price, currency, date) "
> >                 "VALUES (?, '?', '?')");
> >   query.bindValue(0, price);
> >   query.bindValue(1, currency);
> >   query.bindValue(2, datetime);
> >   emit log("AmazonKuKuView", QString("Executing Query:\n\tINSERT INTO
> > info (price, currency, date) VALUES (%1, '%2',
> > '%3')\n").arg(price).arg(currency).arg(datetime));
> >
> >   if(!query.exec())
> >     emit log("AmazonKuKuView", QString("Query resulted in error:
> > %1\n").arg(query.lastError().text()));
> >
> > I get in the log:
> > *** AmazonKuKuView:
> > Executing Query:
> >         INSERT INTO info (price, currency, date) VALUES (774, 'GBP',
> > 'Sun Oct 7 16:13:49 2007')
> >
> > *** AmazonKuKuView:
> > Query resulted in error:  Parameter count mismatch
> >
> > Why does it say parameter count mismatch? Because I didn't mention the
> > id field? Don't think that's a problem because it's automatically
> > incremented right?
> >
> > If I try the sqlbrowser demo and insert this exact sql statements
> > everything seems to turn out just ok.
> >
> > Any ideas/help would be great!
> >
> > Cheers,
> > --
> > Paulo Jorge Matos - pocm at soton.ac.uk
> > http://www.personal.soton.ac.uk/pocm
> > PhD Student @ ECS
> > University of Southampton, UK
> >
> > --
> > To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with "unsubscribe" in the subject or the body.
> > List archive and information: http://lists.trolltech.com/qt-interest/
> >
> >
>
>
>


-- 
 [ signature omitted ]
Message 12 in thread

So it seems that doing another prepare() on the same query does
equally good job as clear(). Nice.

Concerning your http://qtnode.net/index.php/pastebin/4831 : Now i see
the problem where everybody has been confused by your autoincrement
decoy. Let's suppose that you are familiar with the odbc/jdbc
philosophy and just forgot the needed query.next() before asking for
the first result :-)

Cheers,

Pavel Z.

2007/10/7, Paulo J. Matos <pocm@xxxxxxxxxxx>:
> On 07/10/2007, Pavel Zdenek <pavel.zdenek@xxxxxxxxx> wrote:
> > Try using query.clear() after each your query.exec()
> >
>
> Even by doing that the problem is not solved. For example, the code in
> http://qtnode.net/index.php/pastebin/4831 doesn't work even if I use
> query.clear() after each query.exec().
>  :-(
>
> > Cheers,
> >
> > Pavel Zdenek
> >
> > 2007/10/7, Paulo J. Matos <pocm@xxxxxxxxxxx>:
> > > Hello once again,
> > >
> > > Even after solving the 'obvious' problem you mentioned things are not
> > > working as they should.
> > >
> > > The table created with:
> > > if(!query.exec("CREATE TABLE info (id INTEGER PRIMARY KEY AUTOINCREMENT,"
> > >                    "price INTEGER, currency TEXT, date TEXT)"))
> > >       emit log("AmazonKuKu", QString("Query resulted in error:
> > > %1\n").arg(query.lastError().text()));
> > >
> > > is ok. Nothing is logged as an error.
> > > However, when I try:
> > >   // Add to database
> > >   query.prepare("INSERT INTO info (price, currency, date) "
> > >                 "VALUES (?, '?', '?')");
> > >   query.bindValue(0, price);
> > >   query.bindValue(1, currency);
> > >   query.bindValue(2, datetime);
> > >   emit log("AmazonKuKuView", QString("Executing Query:\n\tINSERT INTO
> > > info (price, currency, date) VALUES (%1, '%2',
> > > '%3')\n").arg(price).arg(currency).arg(datetime));
> > >
> > >   if(!query.exec())
> > >     emit log("AmazonKuKuView", QString("Query resulted in error:
> > > %1\n").arg(query.lastError().text()));
> > >
> > > I get in the log:
> > > *** AmazonKuKuView:
> > > Executing Query:
> > >         INSERT INTO info (price, currency, date) VALUES (774, 'GBP',
> > > 'Sun Oct 7 16:13:49 2007')
> > >
> > > *** AmazonKuKuView:
> > > Query resulted in error:  Parameter count mismatch
> > >
> > > Why does it say parameter count mismatch? Because I didn't mention the
> > > id field? Don't think that's a problem because it's automatically
> > > incremented right?
> > >
> > > If I try the sqlbrowser demo and insert this exact sql statements
> > > everything seems to turn out just ok.
> > >
> > > Any ideas/help would be great!
> > >
> > > Cheers,
> > > --
> > > Paulo Jorge Matos - pocm at soton.ac.uk
> > > http://www.personal.soton.ac.uk/pocm
> > > PhD Student @ ECS
> > > University of Southampton, UK
> > >
> > > --
> > > To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with "unsubscribe" in the subject or the body.
> > > List archive and information: http://lists.trolltech.com/qt-interest/
> > >
> > >
> >
> >
> >
>
>
> --
> Paulo Jorge Matos - pocm at soton.ac.uk
> http://www.personal.soton.ac.uk/pocm
> PhD Student @ ECS
> University of Southampton, UK
>

--
 [ signature omitted ]