Re: [sqlite] View bindings for a statement

[Cole Tuininga]

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

[sqlite] View bindings for a statement

[Cole Tuininga]

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