Mark Wyszomierski wrote:
John, Cory, thank you very much. I got execute plain statements ok by
modifying my earlier posting a bit. I was able to create a table using
the prepare statement.
Previously I was using sqlite3_exec() to execute my statements and I
could pass it a callback function which I could use to fetch data for
SELECT statements. How do we do that now with the prepare() and step()
statements? Just some pseudocode like before would be fine. Here in my
test table I have two dummy records and the while loop correctly
iterates over both of them - but how to get the values in each row?
// Table test looks like:
// something | something_else
// ------------------------------------------
// hello | there
// bye | guy
strSql.Format(_T("SELECT * FROM test"));
sqlite3_stmt *pStmt;
const char *pszTailPointer;
int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
&pStmt, &pszTailPointer);
if (nRetVal != SQLITE_OK) {
TRACE("prepare fails!! [%i] [%s]\n", nRetVal, sqlite3_errmsg(db));
return false;
}
nRetVal = sqlite3_step(pStmt);
while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
Sleep(100);
// Try again.
nRetVal = sqlite3_step(pStmt);
// How do I get the information out of this returned record?
// By the way, why would we want a reset() in here?
// sqlite3_reset(pStmt);
}
switch (nRetVal) {
case SQLITE_DONE:
TRACE("Done ok\n");
break;
case SQLITE_ERROR:
TRACE("ERROR!!!!\n");
break;
case SQLITE_MISUSE:
TRACE("MISUSE!!!!\n");
break;
default:
break;
}
sqlite3_finalize(pStmt);
Thanks,
Mark
On 8/14/06, John Stanton <[EMAIL PROTECTED]> wrote:
Mark Wyszomierski wrote:
> Hi Cory,
>
> Alright I gave it a shot from the docs but I'm not handling the
> prepare statement correctly. I'm trying the ASCI version first. The
> prepare statement returns an error. Here is the code snippet I'm
> trying:
>
>
> strSql.Format("CREATE TABLE test (something TEXT, something_else TEXT,
> primary key(something))");
>
> sqlite3_stmt *pStmt;
> const char *pszTailPointer;
> int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
> &pStmt, &pszTailPointer);
> while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
> Sleep(100);
> // Try again.
> nRetVal = sqlite3_step(pStmt);
> TRACE("ret val was [%i]\n", nRetVal);
> }
> switch (nRetVal) {
> case SQLITE_DONE:
> TRACE("Done ok\n");
> break;
> case SQLITE_ERROR:
> TRACE("ERROR!!!!\n");
> break;
> case SQLITE_MISUSE:
> TRACE("MISUSE!!!!\n");
> break;
> default:
> break;
> }
> sqlite3_finalize(pStmt);
> return true;
>
> Any hints?
>
> Thanks,
> Mark
>
>
> On 8/13/06, Cory Nelson <[EMAIL PROTECTED]> wrote:
>
>> On 8/13/06, Mark Wyszomierski <[EMAIL PROTECTED]> wrote:
>> > Hi,
>> >
>> > I have been using sqlite on windows for a few months, it is great. I
>> > need to switch over to unicode support now though, and I am confused
>> > how to do this with sqlite.
>> >
>> > 1) First, when I compiled the original sqlite project, I have the
>> > character set to MBCS. Should I switch this to Unicode and
recompile?
>>
>> I think SQLite explicitly calls CreateFileW but I could be wrong.
>> Might as well compile as Unicode anyway.
>>
>> > 2) I have been using sqlite3_exec() to execute my sql statements,
but
>> > I see that there is no sqlite3_exec16() equivalent to take a unicode
>> > string. I think I'm supposed to use sqlite3_prepare16() but I
have no
>> > idea what the last two parameters of that function are?
>>
>> Check the docs, they explain how to use prepared statements.
>>
>> > 3) To escape my sql statements I was using sqlite3_mprintf() - is
>> > there a unicode equivalent?
>>
>> With prepared statements you put placeholders like "?" into your sql
>> and bind data to the placeholders - no escaping required.
>>
>> > Thanks for any information,
>> > Mark
>> >
>> >
>>
-----------------------------------------------------------------------------
>>
>> > To unsubscribe, send email to [EMAIL PROTECTED]
>> >
>>
-----------------------------------------------------------------------------
>>
>> >
>> >
>>
>>
>> --
>> Cory Nelson
>> http://www.int64.org
>>
>>
-----------------------------------------------------------------------------
>>
>> To unsubscribe, send email to [EMAIL PROTECTED]
>>
-----------------------------------------------------------------------------
>>
>>
>>
>
>
-----------------------------------------------------------------------------
>
> To unsubscribe, send email to [EMAIL PROTECTED]
>
-----------------------------------------------------------------------------
>
>
A "prepare" just compiles the statment, and does not get busy. You do
it to get ready for your execution loop.
You execute the compiled statement with "step" and then call "reset" to
check for errors and intialize the compiled statement ready for the next
"step". At the end of your processing you "finalize" the statement to
tidy up and let you close the database.
open
prepare
loop
step
reset
repeat
finalize
close
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
Here is a chunk of code which executes a SELECT, polls on busy with
deadlock detection and then places the data according to type.
You can see the use of reset and of the extraction of data from
the row and processing by type.
dl_count = 256; /*For deadlock detection.*/
while (!at_end) {
rc = sqlite3_step(dbcmp); /*Get a row.*/
switch (rc) {
case SQLITE_BUSY: /*Database locked. Poll until free or
deadlocked.*/
dl_count--; /*Deadlock count.*/
/*We set up a slightly efficient polling loop by dropping time
slice
each time we hit a lock to minimize overhead without introducing a
delay.*/
#if IS_WIN32
if (dl_count > 0) sleep(0); i /*Yield time slice.*/
#else
#if IS_LINUX
if (dl_count > 0) sched_yield(); /*Yield time slice.*/
#else
if (dl_count > 0) yield(); /*Relinquish time slice.*/
#endif
#endif
else {
at_end = TRUE;
errormet("Database deadlocked", "");
} /*if/else*/
break;
case SQLITE_DONE: /*Exec complete.*/
at_end = TRUE;
sqlite3_reset(dbcmp);
break;
case SQLITE_ROW: /*A row has been found. Load cols and values.*/
for (a = 0; a < col_cnt; a++) {
/*Allow for different types.*/
data_type = sqlite3_column_type(dbcmp, a);
switch (data_type) {
case SQLITE_INTEGER: /*Translate to a string.*/
if (col_type[a] == dc_decimal) {
{ /*block*/
char out[32];
result = sqlite3_column_int(dbcmp, a);
sprintf(strg, "%d", result);
dec_init(out, col_prec[a], 32); /*Correct precision.*/
dec_move(strg, out); /*Move to correct precision.*/
ins_data_item(ctxp, (char *)col_name[a], out,
strlen(out), mud_decimal);
} /*block*/
} else {
result = sqlite3_column_int(dbcmp, a);
sprintf(strg, "%d", result);
ins_data_item(ctxp, (char *)col_name[a], strg,
strlen(strg), mud_decimal);
} /*if/else*/
break;
case SQLITE_FLOAT: /*Could be declared as a DATE/TIME*/
dub = sqlite3_column_double(dbcmp, a);
switch (col_type[a]) {
case dc_decimal:
/*This will transform the float to a fixed point decimal
with precision and scale as defined in the schema.*/
{ /*block*/
char out[32];
sprintf(strg, "%f", dub);
dec_init(out, col_prec[a], 32); /*Correct precision.*/
dec_move(strg, out); /*Move to correct precision.*/
ins_data_item(ctxp, (char *)col_name[a], out,
strlen(out), mud_decimal);
} /*block*/
break;
case dc_float:
sprintf(strg, "%f", dub);
ins_data_item(ctxp, (char *)col_name[a], strg,
strlen(strg), mud_decimal);
break;
case dc_datetime:
get_date_time(dub, strg);
ins_data_item(ctxp, (char *)col_name[a], strg,
strlen(strg), mud_text);
break;
case dc_date:
get_date(dub, strg); /*Transform julian date to
ISO8601.*/
ins_data_item(ctxp, (char *)col_name[a], strg,
strlen(strg), mud_text);
break;
case dc_time:
get_time(dub, strg); /*Julian date to ISO8601 time.*/
ins_data_item(ctxp, (char *)col_name[a], strg,
strlen(strg), mud_text);
break;
default:
break;
case SQLITE_TEXT:
pt = (char *)sqlite3_column_text(dbcmp, a);
if (col_type[a] == dc_decimal) {
ins_data_item(ctxp, (char *)col_name[a], pt, strlen(pt),
mud_decimal);
} else {
ins_data_item(ctxp, (char *)col_name[a], pt, strlen(pt),
mud_text);
} /*if/else*/
break;
case SQLITE_BLOB: /*We could have chunks of HTML etc.*/
byt_cnt = sqlite3_column_bytes(dbcmp, a);
pt = (char *)sqlite3_column_blob(dbcmp, a);
ins_data_item(ctxp, (char *)col_name[a], pt, byt_cnt,
mud_text);
break;
case SQLITE_NULL:
break;
} /*switch*/
} /*for*/
default: /*An error.*/
at_end = TRUE;
/*Details of the error are returned by sqlite3_reset.*/
{
char strg[256];
int errc = sqlite3_reset(dbcmp);
sprintf(strg, " - error %d", errc);
errormet("Error in SQL execution:", strg);
}
break;
} /*switch*/
} /*while*/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
