Re: [sqlite] View bindings for a statement
Cole Tuininga wrote: >The question is, is there an easy way to extract the actual query >(with the bound variable set) from the statement handle? This topic has already been discussed in length some time ago: http://www.mail-archive.com/sqlite-users@sqlite.org/msg28610.html It would be beautiful if some of the interfaces suggested there would one day make it into SQLite! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] View bindings for a statement
Cole Tuininga wrote: > > (tangental question - is it enough to just keep rebinding to a query? > Or do I need to call sqlite3_clear_bindings every time?). There is no need to clear the bindings. If you don't clear the bindings you only need to rebind the values that changed. > The table > in question is pretty simple: key/value for the moment. The select > looks up a value based on the key. The thing is that I'm only making > requests for keys that I know exist, and this doesn't seem to be > giving me any results. That is, sqlite3_step is returning SQLITE_DONE > after the first call each time I run it. > > I'm making sure to call sqlite3_reset on the handle each time. > > The question is, is there an easy way to extract the actual query > (with the bound variable set) from the statement handle? I'm looking > to make sure that I'm actually binding the expected value into the > query. Thanks. > As far as I know, there is no way to get the query with the bound values replaced. However, you can transfer the bindings from one query to another so you could transfer the bindings from your query to a simple select with the same number of parameters. You do this using the obsolete sqlite3_transfer_bindings() API (see http://www.sqlite.org/c3ref/aggregate_count.html) Say you have a select that uses three parameters select a, b, c from t1 where b < ?1 and c > ?2 or d = ?3 You can prepare a select like this select ?1, ?2, ?3 and then transfer the bindings from the first select to the second. When you run the second query it will return the values you bound to the first query before calling sqlite3_transfer_bindings(). HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] View bindings for a statement
On Fri, Apr 25, 2008 at 4:13 PM, P Kishor <[EMAIL PROTECTED]> wrote:
>
> On 4/25/08, Cole Tuininga <[EMAIL PROTECTED]> wrote:
> Here is what I do in the Perl world for this problem --
>
> # Create a string with the bound vars to write to a log
> $sql = "SELECT col FROM table WHERE col = '';
> # Write this $sql to a log file
> $log->info(localtime . ": " . $sql . "\n");
>
> # Now bind and run the query for real
> $sth = $dbh->prepare(qq{SELECT col from table WHERE col = ?});
> $sth->execute('');
I see where you're coming from. This does do what I asked, but I
think I didn't explain what my concern was very well. :)
I did try printing out the key. In fact, I then threw it into a perl
one-liner to pull the data from the same sqlite db and got the row I
was looking for (using DBI). The thing is that my C is a bit rusty
and I'm not entirely certain that I've got the call right. It would
probably be useful if I posted a code snippet. Here are some relevant
snippets from the code.
char **keys; /* This gets populated dynamically as an array of all
the keys that are in the sqlite database */
const char *q = "SELECT var FROM host_map WHERE ind = ?\0";
sqlite3 *dbh;
sqlite3_stmt *sth;
if (sqlite3_open_v2(INPUT_FILE, &dbh, SQLITE_OPEN_READONLY, NULL) !=
SQLITE_OK) {
printf("Did not open the database\n");
return -1;
}
/* Build the query */
if (sqlite3_prepare_v2(dbh, q, -1, &sth, NULL) != SQLITE_OK ) {
printf("Could not compile the query\n");
return -1;
}
index = random() % (long)(lines / 2);
result = sqlite3_bind_text(
sth, 1, (char *) keys[index], strlen((char *)keys[index]) + 1,
SQLITE_TRANSIENT
);
/* Check here to make sure the binding took */
keep_reading = 1;
while (keep_reading) {
switch (sqlite3_step(sth)) {
case SQLITE_ROW:
val = sqlite3_column_text(sth, 0);
printf("%s maps to %s\n", keys[index], val);
break;
case SQLITE_BUSY:
printf("DB was too busy to respond\n");
keep_reading = 0;
break;
case SQLITE_ERROR:
printf("An error has occurred.\n");
return -1;
case SQLITE_MISUSE:
printf("Programming error!\n");
keep_reading = 0;
break;
case SQLITE_DONE:
keep_reading = 0;
break;
default:
printf("Unknown result from sqlite3_step\n");
};
}
--
Cole Tuininga
http://www.tuininga.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] View bindings for a statement
On 4/25/08, Cole Tuininga <[EMAIL PROTECTED]> wrote:
> Hi all -
>
> I'm trying sqlite for the first time and am having a small problem.
> I'm using the C api.
>
> I've got a prepared select query that I keep binding new values to
> (tangental question - is it enough to just keep rebinding to a query?
> Or do I need to call sqlite3_clear_bindings every time?). The table
> in question is pretty simple: key/value for the moment. The select
> looks up a value based on the key. The thing is that I'm only making
> requests for keys that I know exist, and this doesn't seem to be
> giving me any results. That is, sqlite3_step is returning SQLITE_DONE
> after the first call each time I run it.
>
> I'm making sure to call sqlite3_reset on the handle each time.
>
> The question is, is there an easy way to extract the actual query
> (with the bound variable set) from the statement handle? I'm looking
> to make sure that I'm actually binding the expected value into the
> query. Thanks.
>
Here is what I do in the Perl world for this problem --
# Create a string with the bound vars to write to a log
$sql = "SELECT col FROM table WHERE col = '';
# Write this $sql to a log file
$log->info(localtime . ": " . $sql . "\n");
# Now bind and run the query for real
$sth = $dbh->prepare(qq{SELECT col from table WHERE col = ?});
$sth->execute('');
>
> --
> Cole Tuininga
> http://www.tuininga.org/
--
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] View bindings for a statement
Hi all - I'm trying sqlite for the first time and am having a small problem. I'm using the C api. I've got a prepared select query that I keep binding new values to (tangental question - is it enough to just keep rebinding to a query? Or do I need to call sqlite3_clear_bindings every time?). The table in question is pretty simple: key/value for the moment. The select looks up a value based on the key. The thing is that I'm only making requests for keys that I know exist, and this doesn't seem to be giving me any results. That is, sqlite3_step is returning SQLITE_DONE after the first call each time I run it. I'm making sure to call sqlite3_reset on the handle each time. The question is, is there an easy way to extract the actual query (with the bound variable set) from the statement handle? I'm looking to make sure that I'm actually binding the expected value into the query. Thanks. -- Cole Tuininga http://www.tuininga.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
