Check understanding of Prepare and Step processing

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Check understanding of Prepare and Step processing

Dave Wellman
Hi,

 

I'm just trying to check my understanding of what happens when running a
query against SQLite and particularly how that might affect interaction
between an application and SQLite.

 

The SQL itself isn't really important for now, but assume it's a select
statement which will return some data.

 

From the "3 minute intro" (http://www.sqlite.org/quickstart.html )I
understand that the basic steps are:

1)      sqlite3_open_v2 - to open my database file for use by SQLite. This
returns a database handle.

2)      sqlite3_prepare_v2 - this routine results in the sql being "compiled
into a byte-code program" (http://www.sqlite.org/c3ref/prepare.html ). This
returns a statement handle.

3)      sqlite3_step - "this function must be called one or more times to
evaluate the statement." (http://www.sqlite.org/c3ref/step.html ) and also
return answer rows
(the above api may be called multiple times to retrieve all data rows)

4)      sqlite3_finalize - to close the statement handle

5)      sqlite3_close_v2 - to close the database  handle

 

Based on the above and some testing (using 3.20.1):

 

Q1) I believe that the "sqlite3_prepare_v2" function parses the sql text to
check it is valid, the named objects exist etc. and builds the 'executable
program'. Does this function run the SQL? I don't think so.

 

Q2) At what point in the interaction between an application and the SQLite
engine is the sql actually executed?  I think this is the first
'sqlite_step' function call.

 

Q3) Are the 'sqlite_prepare' and 'sqlite_step' function calls synchronous?
(By which I mean when called, will they wait to finish their processing
before returning to the application).

 

Q4) If the function call where the SQLite engine runs the sql is
asynchronous then what is the "I'm still running and haven't finished yet"
return code?

(This question may not be a valid question depending on other answers, but I
thought I'd get it out here anyway).

 

Just out of interest:  Is the 'executable program' built by 'prepare' the
'virtual machine instructions' as referred to on the EXPLAIN documentation
page (http://www.sqlite.org/lang_explain.html )?    

 

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:  <http://www.ward-analytics.com> http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Check understanding of Prepare and Step processing

Richard Hipp-3
On 9/16/17, David Wellman <[hidden email]> wrote:
>
> Q1) I believe that the "sqlite3_prepare_v2" function parses the sql text to
> check it is valid, the named objects exist etc. and builds the 'executable
> program'. Does this function run the SQL? I don't think so.
>

sqlite3_prepare_v2() does not run your SQL.  Usually.  Actually, there
are a few PRAGMA statements that do get run immediately.  For example
"PRAGMA foreign_keys=ON".  But for all traditional SQL statements
("INSERT", "DELETE", "UPDATE", "SELECT", "CREATE", "DROP") the SQL is
not run until you call sqlite3_step()

>
> Q2) At what point in the interaction between an application and the SQLite
> engine is the sql actually executed?  I think this is the first
> 'sqlite_step' function call.

It *starts* running on the first sqlite3_step() call.  But it pauses
when the first row of output is generated.  Execution continues on the
second sqlite3_step() call until another row is ready.  And so forth.


>
> Q3) Are the 'sqlite_prepare' and 'sqlite_step' function calls synchronous?
> (By which I mean when called, will they wait to finish their processing
> before returning to the application).

Yes

>
> Just out of interest:  Is the 'executable program' built by 'prepare' the
> 'virtual machine instructions' as referred to on the EXPLAIN documentation
> page (http://www.sqlite.org/lang_explain.html )?
>

Yes
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users