Re: [sqlite] Prepare statement in separate function

[Igor Tandetnik]

enjoythe...@hushmail.com wrote:
> I was allocating memory because I wanted to prepare the statement
> in a separate function.

Just prepare the statement, and return sqlite3_stmt* by value. You are not 
allocating memory when returning, say, an int, right? sqlite3_stmt* is 
comparable in size, and can be treated similarly.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare statement in separate function

[enjoythesun]

hello martin,

I was allocating memory because I wanted to prepare the statement 
in a separate function. After all I have changed the whole 
implementation design to something less awkward :)

greetings,
john

On Tue, 11 Oct 2011 11:46:01 +0200 Martin Engelschalk 
 wrote:
>Hello John,
>
>why do you malloc() your DB- and Statement handle?
>
>I  declare a
>
>sqlite3* pDB;
>sqlite3_stmt* pStmnt;
>
>then open the database with
>
>int nRet = sqlite3_open("MyDatabaseName", &pDB);
>
>and prepare a statement using
>
>nRet = sqlite3_prepare_v2(pDB, "insert .", -1, &pStmnt, 
>&szTail);
>
>no need to malloc (or free) anything, and passing pDB or pStmnt to 
>
>functions is easy.
>
>Martin
>
>
>Am 11.10.2011 11:27, schrieb enjoythe...@hushmail.com:
>> hello list,
>>
>> I have a question regarding prepared statements. I'm fairly new 
>to
>> sqlite3 and it's already pretty late, so if I'm overlooking
>> something obvious please forgive me :) I'm on Windows 7 x64 and
>> using sqlite-3070800 (amalgamation).
>>
>> I'm trying to prepare an insert statement, that I will reuse 
>many
>> times using bound parameters. In a short test application, if I
>> prepare the statement within the main function everything is 
>fine:
>>
>> CODE
>> ...
>> int rc=0;
>> pDb = (sqlite3*) malloc (sizeof(sqlite3*));
>> stmt = (sqlite3_stmt*) malloc (sizeof(sqlite3_stmt*));
>> ...
>>
>> if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1,&stmt, NULL)) !=
>> SQLITE_OK){
>>  /* ... error ... */
>> }
>>
>> sqlite3_step(stmt);
>> ...
>> \CODE
>>
>> However, if I try throw this code in a separate function like 
>this,
>>
>> CODE
>> int prepareStatement(sqlite3_stmt* stmt, sqlite3* pDb){
>>
>>  int rc=0;
>>
>>  if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1,&stmt, NULL)) 
>!=
>> SQLITE_OK){
>>  /* ... error ... */
>>  }
>>  return 0;
>> }
>> \CODE
>>
>> it fails as soon as I call sqlite3_step(stmt) afterwards. The
>> debugger stops at:
>>
>> sqlite3_mutex_enter(db->mutex);
>>
>> At first I thought I was doing smthg wrong during memory
>> allocation, but everything seems to be fine there. I'm sure I'm
>> overlooking an obvious mistake. Maybe somebody can give me a 
>hint!
>>
>> thanks,
>> John
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare statement in separate function

[Martin Engelschalk]

Hello John,

why do you malloc() your DB- and Statement handle?

I  declare a

sqlite3* pDB;
sqlite3_stmt* pStmnt;

then open the database with

int nRet = sqlite3_open("MyDatabaseName", &pDB);

and prepare a statement using

nRet = sqlite3_prepare_v2(pDB, "insert .", -1, &pStmnt, &szTail);

no need to malloc (or free) anything, and passing pDB or pStmnt to 
functions is easy.


Martin


Am 11.10.2011 11:27, schrieb enjoythe...@hushmail.com:

hello list,

I have a question regarding prepared statements. I'm fairly new to
sqlite3 and it's already pretty late, so if I'm overlooking
something obvious please forgive me :) I'm on Windows 7 x64 and
using sqlite-3070800 (amalgamation).

I'm trying to prepare an insert statement, that I will reuse many
times using bound parameters. In a short test application, if I
prepare the statement within the main function everything is fine:

CODE
...
int rc=0;
pDb = (sqlite3*) malloc (sizeof(sqlite3*));
stmt = (sqlite3_stmt*) malloc (sizeof(sqlite3_stmt*));
...

if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1,&stmt, NULL)) !=
SQLITE_OK){
/* ... error ... */
}

sqlite3_step(stmt);
...
\CODE

However, if I try throw this code in a separate function like this,

CODE
int prepareStatement(sqlite3_stmt* stmt, sqlite3* pDb){

int rc=0;

if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1,&stmt, NULL)) !=
SQLITE_OK){
/* ... error ... */
}
return 0;
}
\CODE

it fails as soon as I call sqlite3_step(stmt) afterwards. The
debugger stops at:

sqlite3_mutex_enter(db->mutex);

At first I thought I was doing smthg wrong during memory
allocation, but everything seems to be fine there. I'm sure I'm
overlooking an obvious mistake. Maybe somebody can give me a hint!

thanks,
John

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare statement in separate function

[enjoythesun]

hello again,

ok, right after posting to the list, I have found the mistake. 
Obviously it needs to be:

sqlite3_stmt** stmt = (sqlite3_stmt**) malloc 
(sizeof(sqlite3_stmt*));

thanks list! :)


On Tue, 11 Oct 2011 11:27:41 +0200 enjoythe...@hushmail.com wrote:
>hello list,
>
>I have a question regarding prepared statements. I'm fairly new to 
>
>sqlite3 and it's already pretty late, so if I'm overlooking 
>something obvious please forgive me :) I'm on Windows 7 x64 and 
>using sqlite-3070800 (amalgamation).
>
>I'm trying to prepare an insert statement, that I will reuse many 
>times using bound parameters. In a short test application, if I 
>prepare the statement within the main function everything is fine:
>
>CODE
>...
>int rc=0;
>pDb = (sqlite3*) malloc (sizeof(sqlite3*));
>stmt = (sqlite3_stmt*) malloc (sizeof(sqlite3_stmt*));
>...
>
>if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1, &stmt, NULL)) != 
>SQLITE_OK){
>   /* ... error ... */
>}
>
>sqlite3_step(stmt);
>...
>\CODE
>
>However, if I try throw this code in a separate function like 
>this,
>
>CODE
>int prepareStatement(sqlite3_stmt* stmt, sqlite3* pDb){
>
>   int rc=0;
>
>   if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1, &stmt, NULL)) != 
>
>SQLITE_OK){
>   /* ... error ... */
>   }
>   return 0;
>}
>\CODE
>
>it fails as soon as I call sqlite3_step(stmt) afterwards. The 
>debugger stops at:
>
>sqlite3_mutex_enter(db->mutex);
>
>At first I thought I was doing smthg wrong during memory 
>allocation, but everything seems to be fine there. I'm sure I'm 
>overlooking an obvious mistake. Maybe somebody can give me a hint!
>
>thanks,
>John
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Prepare statement in separate function

[enjoythesun]

hello list,

I have a question regarding prepared statements. I'm fairly new to 
sqlite3 and it's already pretty late, so if I'm overlooking 
something obvious please forgive me :) I'm on Windows 7 x64 and 
using sqlite-3070800 (amalgamation).

I'm trying to prepare an insert statement, that I will reuse many 
times using bound parameters. In a short test application, if I 
prepare the statement within the main function everything is fine:

CODE
...
int rc=0;
pDb = (sqlite3*) malloc (sizeof(sqlite3*));
stmt = (sqlite3_stmt*) malloc (sizeof(sqlite3_stmt*));
...

if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1, &stmt, NULL)) != 
SQLITE_OK){
/* ... error ... */
}

sqlite3_step(stmt);
...
\CODE

However, if I try throw this code in a separate function like this,

CODE
int prepareStatement(sqlite3_stmt* stmt, sqlite3* pDb){

int rc=0;

if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1, &stmt, NULL)) != 
SQLITE_OK){
/* ... error ... */
}
return 0;
}
\CODE

it fails as soon as I call sqlite3_step(stmt) afterwards. The 
debugger stops at:

sqlite3_mutex_enter(db->mutex);

At first I thought I was doing smthg wrong during memory 
allocation, but everything seems to be fine there. I'm sure I'm 
overlooking an obvious mistake. Maybe somebody can give me a hint!

thanks,
John

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare Statement

[Igor Tandetnik]

"Mahalakshmi.m"
<[EMAIL PROTECTED]> wrote
in message
news:[EMAIL PROTECTED]
> Can I bind the unsigned short value [ie., like 0x0065 for English and
> 0x3045
> for Japanese] to its corresponding string value.is it possible.
>
> Unsigned short temp;
> For eg,
> If temp = 0x0065 then its corresponding english string 'a' should
> come while
> binding.It works out by using sprintf();But If temp = 0x30E4 then its
> corresponding Japanese string should come.For this sprintf() is not
> working.

... but wsprintf should.
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Prepare Statement

[Mahalakshmi.m]

If my Table is as follows:
create table Music ( id integer not null primary key,
classificationCode integer, input text) <<

Table:
id  classificationCode  input
--  --  -
1   1   aaa
2   0   1345
3   1   asdf

At this point, 

sqlite3_prepare(gpst_SqliteInstance, "SELECT id,
classificationCode, input FROM MUSIC WHERE input
>= ? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0);

Can I bind the unsigned short value [ie., like 0x0065 for English and 0x3045
for Japanese] to its corresponding string value.is it possible.

Unsigned short temp;
For eg,
If temp = 0x0065 then its corresponding english string 'a' should come while
binding.It works out by using sprintf();But If temp = 0x30E4 then its
corresponding Japanese string should come.For this sprintf() is not working.

Can anyone please help to solve this.

Regards,
Mahalakshmi





-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Stephen Oberholtzer
Sent: Friday, February 29, 2008 2:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Prepare Statement

On Thu, Feb 28, 2008 at 9:22 AM, Mahalakshmi.m
<[EMAIL PROTECTED]> wrote:
>
>
>  Hi,
>  My table looks like:
>  IdName
>  1 1aaa
>  2 01345
>  3 1asdf
>
>  I want to bind unsigned short as text. i.e, If the Unsighed short is 
> 0x0061  I want to bind it as 'a'.
>
>  My Prepare statement is as follows:
>
>  Unsigned char u8_ClassificationCode=1;
>
>  Unsigned short u16_Input=0x0061;
>
>  if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC 
> WHERE  Name >= '%d%c'  LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= 
> SQLITE_OK)
>
>  {
>
> return SQLITE_DB_ERROR;
>
>  }
> 
> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
>
> sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
>  *)u16_Input,-1,SQLITE_STATIC);
>
>  }
>

Since nobody else mentioned it: there's something seriously wrong with your
database design.

But first: Your usage of sqlite3_bind_text16 is incorrect.   The
fourth argument, -1, means "My string is NUL-terminated. Use strlen() to
figure out how long my string is and use that.".

However, for that to always work correctly, u16_input needs to be an array
with a NUL terminator:

>> unsigned short u16_input[] = { 'a', '\0' }; <<

Anyway, back to what I was saying: your database design needs rethinking.
1NF (http://en.wikipedia.org/wiki/First_normal_form)
states that a column should only have one value.   However, you seem
to be combining *two* values (Classification Code and Input) into one column
(Name).  Therefore, you should be doing this:

>> create table Music ( id integer not null primary key,
classificationCode integer, input text) <<

Table:
id  classificationCode  input
--  --  -
1   1   aaa
2   0   1345
3   1   asdf

At this point, you would do this:

>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id,
classificationCode, input FROM MUSIC WHERE classificationCode = ? AND input
>= ? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0); << Note that, if you you want
the original form, you can do
>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id, classificationCode
|| input as Name FROM MUSIC WHERE classificationCode = ? AND input >=
? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0); << This will convert
classificationCode to a string and join it against the 'input' column to
return your original Name.

>> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
sqlite3_bind_text(pst_SearchPrepareStmt, 2, "a", -1, SQLITE_STATIC); <<

This also means you can index the string portion of your Name column
separately, and quickly search for something with a specific name without
knowing its classification.

--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare Statement

[Stephen Oberholtzer]

On Thu, Feb 28, 2008 at 9:22 AM, Mahalakshmi.m
<[EMAIL PROTECTED]> wrote:
>
>
>  Hi,
>  My table looks like:
>  IdName
>  1 1aaa
>  2 01345
>  3 1asdf
>
>  I want to bind unsigned short as text. i.e, If the Unsighed short is 0x0061
>  I want to bind it as 'a'.
>
>  My Prepare statement is as follows:
>
>  Unsigned char u8_ClassificationCode=1;
>
>  Unsigned short u16_Input=0x0061;
>
>  if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC WHERE
>  Name >= '%d%c'  LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK)
>
>  {
>
> return SQLITE_DB_ERROR;
>
>  }
> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
>
> sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
>  *)u16_Input,-1,SQLITE_STATIC);
>
>  }
>

Since nobody else mentioned it: there's something seriously wrong with
your database design.

But first: Your usage of sqlite3_bind_text16 is incorrect.   The
fourth argument, -1, means "My string is NUL-terminated. Use strlen()
to figure out how long my string is and use that.".

However, for that to always work correctly, u16_input needs to be an
array with a NUL terminator:

>> unsigned short u16_input[] = { 'a', '\0' }; <<

Anyway, back to what I was saying: your database design needs
rethinking.  1NF (http://en.wikipedia.org/wiki/First_normal_form)
states that a column should only have one value.   However, you seem
to be combining *two* values (Classification Code and Input) into one
column (Name).  Therefore, you should be doing this:

>> create table Music ( id integer not null primary key,
classificationCode integer, input text) <<

Table:
id  classificationCode  input
--  --  -
1   1   aaa
2   0   1345
3   1   asdf

At this point, you would do this:

>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id,
classificationCode, input FROM MUSIC WHERE classificationCode = ? AND
input >= ? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0); <<
Note that, if you you want the original form, you can do
>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id, classificationCode
|| input as Name FROM MUSIC WHERE classificationCode = ? AND input >=
? LIMIT 1;", -1,&pst_SearchPrepareStmt, 0); <<
This will convert classificationCode to a string and join it against
the 'input' column to return your original Name.

>> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
sqlite3_bind_text(pst_SearchPrepareStmt, 2, "a", -1, SQLITE_STATIC);
<<

This also means you can index the string portion of your Name column
separately, and quickly search for something with a specific name
without knowing its classification.

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare Statement

[Igor Tandetnik]

Simon Davies
<[EMAIL PROTECTED]> wrote:
> You need a placeholder in the SQL in order to bind a value.
>
> "SELECT id, Name FROM MUSIC WHERE Name >= '?'  LIMIT 1;",

'?' is a string literal consisting of a single question mark character - 
_not_ a parameter placeholder. You want ? without quotes.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare Statement

[Steven Fisher]

On 28-Feb-2008, at 6:22 AM, Mahalakshmi.m wrote:

> if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC  
> WHERE
> Name >= '%d%c'  LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK)

That's not what a bind point looks like. Take a look here:
http://www.sqlite.org/c3ref/bind_blob.html

I'm not sure you can bind two values in a string this way. Maybe take  
a look at using one of the sqlite3_printf functions to build the value  
right into your query string.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare Statement

[John Stanton]

You misunderstand binding.  You use it like this -

   sql - "SELECT name FROM customers WHERE cust_id = ?";
   this_cust_id - "CUST1275";

   sqlite3_prepare_v2(...);

   sqlite3)bind_text(..., 1, this_cust_id, ...);

You bind a value to the data represented by the ?.  Then you reuse the 
compiled SQL by successively binding data values to it.

If you are not re-using the compiled SQL you do not use bind.

Mahalakshmi.m wrote:
>  
> 
> Hi,
> 
>  
> 
> My table looks like:
> 
>  
> 
> IdName
> 
> 1 1aaa
> 
> 2 01345
> 
> 3 1asdf
> 
>  
> 
>  
> 
> I want the statement to be like:
> 
> "SELECT id, Name FROM MUSIC WHERE Name >= '1a'  LIMIT 1;"
> 
> But using prepare I could not able to get the desired statements.
> 
> I want to bind unsigned short as text. i.e, If the Unsighed short is 0x0061
> I want to bind it as 'a'.
> 
>  
> 
> My Prepare statement is as follows:
> 
>  
> 
> Unsigned char u8_ClassificationCode=1;
> 
> Unsigned short u16_Input=0x0061;
> 
> if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC WHERE
> Name >= '%d%c'  LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK) 
> 
> {
> 
> return SQLITE_DB_ERROR;
> 
> }
> 
> else
> 
> {
> 
> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
> 
> sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
> *)u16_Input,-1,SQLITE_STATIC);
> 
> }
> 
>  
> 
> For the above the return status of sqlite3_prepare is success  but not
> properly binded.
> 
>  
> 
> Please help me to solve this.
> 
>  
> 
> Thanks & Regards,
> 
> Mahalakshmi.M
> 
>  
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare Statement

[Simon Davies]

You need a placeholder in the SQL in order to bind a value.

The following is untested but shows the sort of approach...

Unsigned char u8_ClassificationCode=1;
Unsigned short u16_Input=0x0061;
if ( sqlite3_prepare(
gpst_SqliteInstance,
"SELECT id, Name FROM MUSIC WHERE Name >= '?'  LIMIT 1;",
-1,
&pst_SearchPrepareStmt,
0) != SQLITE_OK )
{
return SQLITE_DB_ERROR;
}
else
{
char sqlParam[ 1000 ];
sprintf( sqlParam, "%1d%c", u8_ClassificationCode, u16_Input );
sqlite3_bind_text16( pst_SearchPrepareStmt, 2, sqlParam, -1,
SQLITE_STATIC );
}

Rgds,
Simon

2008/2/28 Mahalakshmi.m <[EMAIL PROTECTED]>:
>
>
> Hi,
>
>
>
> My table looks like:
>
>
>
> IdName
>
> 1 1aaa
>
> 2 01345
>
> 3 1asdf
>
>
>
>
>
> I want the statement to be like:
>
> "SELECT id, Name FROM MUSIC WHERE Name >= '1a'  LIMIT 1;"
>
> But using prepare I could not able to get the desired statements.
>
> I want to bind unsigned short as text. i.e, If the Unsighed short is 0x0061
> I want to bind it as 'a'.
>
>
>
> My Prepare statement is as follows:
>
>
>
> Unsigned char u8_ClassificationCode=1;
>
> Unsigned short u16_Input=0x0061;
>
> if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC WHERE
> Name >= '%d%c'  LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK)
>
> {
>
>return SQLITE_DB_ERROR;
>
> }
>
> else
>
> {
>
>sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
>
>sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
> *)u16_Input,-1,SQLITE_STATIC);
>
> }
>
>
>
> For the above the return status of sqlite3_prepare is success  but not
> properly binded.
>
>
>
> Please help me to solve this.
>
>
>
> Thanks & Regards,
>
> Mahalakshmi.M
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare Statement

[Igor Tandetnik]

Mahalakshmi.m
<[EMAIL PROTECTED]>
wrote:
> I want the statement to be like:
>
> "SELECT id, Name FROM MUSIC WHERE Name >= '1a'  LIMIT 1;"
>
> But using prepare I could not able to get the desired statements.
>
> I want to bind unsigned short as text.

There are no parameters in your statement. What exactly do you plan to 
bind to?

> Unsigned char u8_ClassificationCode=1;
>
> Unsigned short u16_Input=0x0061;
>
> if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC
> WHERE Name >= '%d%c'  LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!=
> SQLITE_OK)

You seem to think sqlite3_prepare works like printf, what with you using 
%d and %c. It doesn't. %d and %c have no special meaning in a SQL 
statement. Your statement has no parameters to bind.

Try something like this:

wchar_t text[10];
wsprintf(text, L"%d%c", u8_ClassificationCode, u16_Input);

sqlite3_prepare(gpst_SqliteInstance,
"SELECT id, Name FROM MUSIC WHERE Name >= ?  LIMIT 1;",
-1, &pst_SearchPrepareStmt, 0);

sqlite3_bind_text16(pst_SearchPrepareStmt, 1, text, -1, 
SQLITE_TRANSIENT);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Prepare Statement

[Mahalakshmi.m]

 

Hi,

 

My table looks like:

 

IdName

1 1aaa

2 01345

3 1asdf

 

 

I want the statement to be like:

"SELECT id, Name FROM MUSIC WHERE Name >= '1a'  LIMIT 1;"

But using prepare I could not able to get the desired statements.

I want to bind unsigned short as text. i.e, If the Unsighed short is 0x0061
I want to bind it as 'a'.

 

My Prepare statement is as follows:

 

Unsigned char u8_ClassificationCode=1;

Unsigned short u16_Input=0x0061;

if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC WHERE
Name >= '%d%c'  LIMIT 1;",-1,&pst_SearchPrepareStmt,0)!= SQLITE_OK) 

{

return SQLITE_DB_ERROR;

}

else

{

sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);

sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
*)u16_Input,-1,SQLITE_STATIC);

}

 

For the above the return status of sqlite3_prepare is success  but not
properly binded.

 

Please help me to solve this.

 

Thanks & Regards,

Mahalakshmi.M

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite Prepare Statement

[RaghavendraK 70574]

Hi,

Do we have tool(similar to Sqlite3) by which Prepare statement can be tested as 
easily as normal sql ?
As most of the time we have use Prepare statement hence we need a RAD tool
to verify it.This will be useful to the Sqlite grp.Share your opinion.

Or if any have an idea how to do it,let me know i will make the changes
and submit to Group owner.

regards
ragha

**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*
Dan Source code is attached. I didn't write this, someone else from the forum 
did their name is not on it, nor coppyrighted.. I thought it was a clean way to 
test threading.

Interestingly if you remove out the shared cache everything runs to completion.



Dan Kennedy <[EMAIL PROTECTED]> wrote: 
Hi Ken,

Probably a bug in the new threading stuff. Can you share
source code for this test or is it part of some large app?

Either way, thanks for the report.

Dan.

On Wed, 2007-08-29 at 22:15 -0700, Ken wrote:
> Also erros out here, sporadically.
> int sqlite3OsWrite(sqlite3_file *id, const void *pBuf, int amt, i64 offset){
>   return id->pMethods->xWrite(id, pBuf, amt, offset);
> }
> 
> Program received signal SIGSEGV, Segmentation fault.
> [Switching to Thread 1075841376 (LWP 15747)]
> 0x0040c413 in sqlite3OsWrite (id=0x55aaa0, pBuf=0x401ffc30, amt=24, 
> offset=0) at os.c:38
> (gdb) Quit
> (gdb) 
> 
> Ken  wrote: 4 threads, shared_Cache enabled
> LOOP 100 
> BEGIN
>  LOOP 50 times
>   INSERT
>  end LOOP
>  COMMIT
> 
>   SELECT COUNT(*) ...
> end LOOP
> 
> 
> program received signal SIGSEGV, Segmentation fault.
> [Switching to Thread 1080043872 (LWP 15448)]
> moveToChild (pCur=0x569058, newPgno=) at btree.c:3304
> (gdb) 
> 
> 
> if( rc ) return rc;
>   pNewPage->idxParent = pCur->idx;
>   pOldPage = pCur->pPage;
>   pOldPage->idxShift = 0; < Error Here
>   releasePage(pOldPage);
>   pCur->pPage = pNewPage;
>   pCur->idx = 0;
>   pCur->info.nSize = 0;
> 
> 
> Ken
> 
> 
> 
> 
> Ken  wrote: 4 threads, shared_Cache enabled
> LOOP 100 
> BEGIN
>  LOOP 50 times
>   INSERT
>  end LOOP
>  COMMIT
> 
>   SELECT COUNT(*) ...
> end LOOP
> 
> 
> program received signal SIGSEGV, Segmentation fault.
> [Switching to Thread 1080043872 (LWP 15448)]
> moveToChild (pCur=0x569058, newPgno=) at btree.c:3304
> (gdb) 
> 
> 
> if( rc ) return rc;
>   pNewPage->idxParent = pCur->idx;
>   pOldPage = pCur->pPage;
>   pOldPage->idxShift = 0; < Error Here
>   releasePage(pOldPage);
>   pCur->pPage = pNewPage;
>   pCur->idx = 0;
>   pCur->info.nSize = 0;
> 
> 
> Ken
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] PREPARE statement tutorial?

[Dennis Cote]

Olaf Beckman Lapré wrote:


Hi,

I assume that the sqlite3_prepare() / sqlite3_bind() combination results in 
faster performance than sqlite3_exec() for INSERT and UPDATE statements. But 
where can I find example code that uses prepare/bind? Googling didn't give any 
results.

Greetz,

Olaf
 


Olaf,

You could look at the source code for sqlite3_exec() in legacy.c at . It 
is implemented using sqlite3_prepare() and friends. The 
sqlite3_prepare() API functions are also used to implement many features 
of the sqlite shell. You can search for sqlite3_prepare in the file 
shell.c. The source files can be viewed at 
http://www.sqlite.org/cvstrac/dir?d=sqlite/src.


HTH
Dennis Cote

Re: [sqlite] PREPARE statement tutorial?

[Jay Sprenkle]

> I assume that the sqlite3_prepare() / sqlite3_bind() combination results in 
> faster performance than sqlite3_exec() for INSERT and UPDATE statements. But 
> where can I find example code that uses prepare/bind? Googling didn't give 
> any results.

You didn't say which language you're using...

Here's some c++:

sqlite3*   db;
sqlite3_stmt *pStmt;
intrc;

string str;
string sql;

sql = "SELECT Created, Subject, Body FROM News";
sql += " WHERE Created > ?";
sql += " ORDER BY Created LIMIT 6";

// connect to database
rc = sqlite3_open( DB, &db );
if ( rc )
  throw "Can't open database";

if ( sqlite3_prepare( db, sql.c_str(), sql.size(), &pStmt,
NULL ) != SQLITE_OK )
  throw "Cannot prepare sql";

// bind is 1 based
sqlite3_bind_text( pStmt, 1, LastLogin.c_str(),
LastLogin.size(), SQLITE_STATIC );

[sqlite] PREPARE statement tutorial?

[Olaf Beckman Lapré]

 Hi,

I assume that the sqlite3_prepare() / sqlite3_bind() combination results in 
faster performance than sqlite3_exec() for INSERT and UPDATE statements. But 
where can I find example code that uses prepare/bind? Googling didn't give any 
results.

Greetz,

Olaf