Basic Text Bind Question

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Basic Text Bind Question

Kiel Wadner
Could someone point out what I missing or not understanding on this? I'm
trying to do a simple text bind to search for people with the last name
"Fullman". However my return code (rc) from sqlite3_step() is the same as
SQLITE_DONE. I'm assuming this means it doesn't find anything.

Also, how do I pull the character string of the sqlite statement to be
executed from 'sqlite3_stmt' ?

Thanks for the hand.

-- code snipet --

sqlite3_open( "test.db", &database );

size = sprintf( buf, "CREATE TABLE People( fname varchar(25), fname
varchar(25) );" );
size = sprintf( buf, "INSERT INTO People VALUES( 'John', 'Fullman' );" );
size = sprintf( buf, "INSERT INTO People VALUES( 'Sally', 'Fullman' );" );
size = sprintf( buf, "INSERT INTO People VALUES( 'Mike', 'Smith' );" );

size = sprintf( buf, "SELECT * FROM People WHERE lname = '?'" );
rc = sqlite3_prepare( database, buf, -1, &statement, 0 );

sqlite3_bind_text(statement, 1, "Fullman", 7, SQLITE_STATIC );

rc = sqlite3_step(statement);

std::cout << rc << " " << SQLITE_DONE; // rc == SQLITE_DONE at this point

--- end code ---

--
Kiel W.
[hidden email]
----------------------------------
>> time is swift <<
Reply | Threaded
Open this post in threaded view
|

Re: Basic Text Bind Question

D. Richard Hipp
On Tue, 2005-06-07 at 16:53 -0700, Kiel W. wrote:

> size = sprintf( buf, "CREATE TABLE People( fname varchar(25), fname
> varchar(25) );" );
> rc = sqlite3_prepare( database, buf, -1, &statement, 0 );
>

sqlite3_prepare only processes the first statement in your list of
SQL statements.  It then returns a pointer to the beginning of the
second statement so that you can process them all in a loop.  But
your code appears to omit this loop and thus is processing only
the first one.  It never reaches the SELECT.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Basic Text Bind Question

Kiel Wadner
In reply to this post by Kiel Wadner
Added note...

 rc = sqlite3_prepare( database, buf, -1, &statement, 0 );
>

I also tried this with

rc = sqlite3_prepare( database, buf, strlen(buf), &statement, 0);

--
Kiel W.
[hidden email]
----------------------------------
>> time is swift <<
Reply | Threaded
Open this post in threaded view
|

Re: Basic Text Bind Question

Ulrik Sandborg-Petersen
In reply to this post by Kiel Wadner
Hi,

Kiel W. wrote:

>Could someone point out what I missing or not understanding on this? I'm
>trying to do a simple text bind to search for people with the last name
>"Fullman". However my return code (rc) from sqlite3_step() is the same as
>SQLITE_DONE. I'm assuming this means it doesn't find anything.
>
>Also, how do I pull the character string of the sqlite statement to be
>executed from 'sqlite3_stmt' ?
>
>Thanks for the hand.
>
>-- code snipet --
>
>sqlite3_open( "test.db", &database );
>
>size = sprintf( buf, "CREATE TABLE People( fname varchar(25), fname
>varchar(25) );" );
>  
>
I think it stops right here, because you've got fname twice.  That
induces an error.

Also, I'd execute each statement by itself.

HTH

Ulrik Petersen

--
Ulrik Petersen, PhD student, MA, B.Sc.
University of Aalborg, Denmark


Reply | Threaded
Open this post in threaded view
|

Re: Basic Text Bind Question

Ted Unangst
In reply to this post by Kiel Wadner
Kiel W. wrote:

>
> size = sprintf( buf, "CREATE TABLE People( fname varchar(25), fname
> varchar(25) );" );
> size = sprintf( buf, "INSERT INTO People VALUES( 'John', 'Fullman' );" );
> size = sprintf( buf, "INSERT INTO People VALUES( 'Sally', 'Fullman' );" );
> size = sprintf( buf, "INSERT INTO People VALUES( 'Mike', 'Smith' );" );
>
> size = sprintf( buf, "SELECT * FROM People WHERE lname = '?'" );

unless you deleted the code between the sprintf lines, this isn't doing
what you think it is.



--
Ted Unangst             www.coverity.com             Coverity, Inc.
Reply | Threaded
Open this post in threaded view
|

Re: Basic Text Bind Question

Dan Kennedy
In reply to this post by Kiel Wadner

> size = sprintf( buf, "SELECT * FROM People WHERE lname = '?'" );
                                                           ^^^
 
> rc = sqlite3_prepare( database, buf, -1, &statement, 0 );
> sqlite3_bind_text(statement, 1, "Fullman", 7, SQLITE_STATIC );
> rc = sqlite3_step(statement);

As others have pointed out, unless you omitted some code for clarity,
the SELECT is never being executed. But after you get that fixed up,
you also need to leave out the quotes around the question mark. With
the quotes in, this is a literal question mark, not an SQL variable.

Replace the SQL you have with:

"SELECT * FROM People WHERE lname = ?"




               
__________________________________
Discover Yahoo!
Use Yahoo! to plan a weekend, have fun online and more. Check it out!
http://discover.yahoo.com/
Reply | Threaded
Open this post in threaded view
|

Re: Basic Text Bind Question

Kiel Wadner
In reply to this post by Ted Unangst
Ok, thanks for the responses.

--Ulrik
>I think it stops right here, because you've got fname twice. That
>induces an error.

You are correct, I'm not sure when I introduced that error, but thanks for
pointing it out. Its not in my running code so it was either C/P error or
something else dumb.

-- Dr. H & Ted
Ok, I see what you guys are saying. I split my code up so I create and
populate the database with another set of sqlite3_exec() statements. I
confirmed the database is populated with the following results:

$ ./sqlite3 test.db
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> select * from People;
fname|lname
John|Fullman
Mike|Fullman
Mike|Smith

My select code is as follows:

sqlite3_open( "test.db", &database );

size = sprintf( buf, "SELECT * FROM People WHERE lname = '?'" );

iPrep = sqlite3_prepare( database, buf, size, &statement, 0 );

sqlite3_bind_text(statement, 1, "Fullman", 7, SQLITE_STATIC );

rc = sqlite3_step(statement);
std::cout << rc << " " << SQLITE_DONE;

I would expect rc to be SQLITE_ROW, but it is SQLITE_DONE. I undertand with
getting multiple row back I will need to loop the sqlite3_step function
later.


--
Kiel W.
[hidden email]
----------------------------------
>> time is swift <<
Reply | Threaded
Open this post in threaded view
|

Re: Basic Text Bind Question

Kiel Wadner
Thanks Dan!

Works like a charm now.

On 6/7/05, Kiel W. <[hidden email]> wrote:

>
> Ok, thanks for the responses.
>
> --Ulrik
> >I think it stops right here, because you've got fname twice. That
> >induces an error.
>
> You are correct, I'm not sure when I introduced that error, but thanks for
> pointing it out. Its not in my running code so it was either C/P error or
> something else dumb.
>
> -- Dr. H & Ted
> Ok, I see what you guys are saying. I split my code up so I create and
> populate the database with another set of sqlite3_exec() statements. I
> confirmed the database is populated with the following results:
>
> $ ./sqlite3 test.db
> SQLite version 3.2.1
> Enter ".help" for instructions
> sqlite> select * from People;
> fname|lname
> John|Fullman
> Mike|Fullman
> Mike|Smith
>
> My select code is as follows:
>
> sqlite3_open( "test.db", &database );
>
> size = sprintf( buf, "SELECT * FROM People WHERE lname = '?'" );
>
> iPrep = sqlite3_prepare( database, buf, size, &statement, 0 );
>
> sqlite3_bind_text(statement, 1, "Fullman", 7, SQLITE_STATIC );
>
> rc = sqlite3_step(statement);
> std::cout << rc << " " << SQLITE_DONE;
>
> I would expect rc to be SQLITE_ROW, but it is SQLITE_DONE. I undertand
> with getting multiple row back I will need to loop the sqlite3_step function
> later.
>
>
> --
> Kiel W.
> [hidden email]
> ----------------------------------
> >> time is swift <<
>



--
Kiel W.
[hidden email]
----------------------------------
>> time is swift <<
Reply | Threaded
Open this post in threaded view
|

Re: Basic Text Bind Question

Eugene Wee
In reply to this post by D. Richard Hipp
Hi,

D. Richard Hipp wrote:
> sqlite3_prepare only processes the first statement in your list of
> SQL statements.  It then returns a pointer to the beginning of the
> second statement so that you can process them all in a loop.  But
> your code appears to omit this loop and thus is processing only
> the first one.  It never reaches the SELECT.
On the processing of multiple SQL statements - should sqlite3_finalize() be
called on each iteration, or can sqlite3_prepare() be called right away and then
sqlite3_finalize() called only when all the processing is finished?

Thanks,
Eugene