GNOME Bugzilla – Bug 488860
Add 'LIMIT' and 'OFFSET' keyword to prepared statements
Last modified: 2007-11-06 13:22:04 UTC
I would really need to have the possibility to write prepared statements containing the 'LIMIT' and 'OFFSET' keywords. They can avoid the query to do a full scan of the table and speed a lot the things up. I'm coding a sqlite backend for Anjuta and it would be great if you could provide these addons on libgda 3.2. thanks, regards Massimo
Ok, will do.
I had misread your problem... You can in fact define a GdaQuery including the LIMIT and OFFSET keywords even if they are not parsed by Libgda, this works.
Hi, Sure, defining a GdaQuery is a way to interact with the underlying database, the problem is that with my procedure I should create hundreds or thousands of new equal queries parsed everytime by the db backend, say SQLite. With prepared statements instead the db will reuse the already parsed query, speeding up the process. It's like recompiling a regex everytime or have it compiled once. I need the fastest interaction with db because even a 1ms per query would do the difference. thanks, regards Massimo
*** Bug 488965 has been marked as a duplicate of this bug. ***
It's now implemented in Libgda (and in LibSql). However, even if a query contains some SQL not recognized by Libgda (such as the CASE ... WHEN construct), the providers can treat them as prepared statements which allows to avoid SQL parsing everytime the query is run (for this to work, you need to use a GdaQuery object, not a GdaCommand).
That's great! thanks a lot, regards, Massimo
Thanks!
Hi, (In reply to comment #5) > It's now implemented in Libgda (and in LibSql). However, even if a query > contains some SQL not recognized by Libgda (such as the CASE ... WHEN > construct), the providers can treat them as prepared statements which allows to > avoid SQL parsing everytime the query is run (for this to work, you need to use > a GdaQuery object, not a GdaCommand). > in this case, say I have a nested query, like SELECT symbol_id FROM symbol WHERE symbol.file_defined_id = (SELECT file_defined_id FROM symbol WHERE symbol = [PARAMETER]) how can the '[PARAMTER]' be specified? Using the classic ## /* name:'par' type:gint */ won't work because it's not recognized by gda_query_new_from_sql (). Probably using g_strdup_printf () to build the sql? thanks and regards, Massimo
Created attachment 98656 [details] Test case You can use the "classic" Libgda notation for parameters, but the gda_query_new_from_sql() will return an error (even though it will also create a correct GdaQuery object). See the test case attached. It's better to avoid using the g_strdup_printf() to build SQL as it can easily lead to SQL injection problems.