Preparing queries

These are the eight methods and two objects that form the SQLite interface. These are the basic list of functions that each user/reader must be aware of when using SQLite in code. These statements don't change, nor does their functionality. These are the key statements to ensure that users are aware of the name, format, and where these functions are used:

• sqlite3: Database connection object, made by sqlite3_open(), killed by sqlite3_close()
• sqlite3_stmt: Preparation statement object, made by sqlite3_prepare(), killed by sqlite3_finalize()
• sqlite3_open(): Opens the database (new or existing) and uses constructor sqlite3
• sqlite3_prepare(): Compiles some SQL text into byte code to perform updating or querying tasks and is the constructor of sqlite3_stmt
• sqlite3_bind(): Application data is stored into the parameters of the original SQL
• sqlite3_step(): The further advancement of sqlite3_stmt onto the next row or completion
• sqlite3_column(): The current row result outlining column values for sqlite3_stmt
• sqlite3_finalize(): sqlite3_stmt destructor
• sqlite3_close(): sqlite3 destructor
• sqlite3_exec(): A wrapper function that works for one or many SQL statements using sqlite3_prepare(), sqlite3_step(), sqlite3_finalize(), and sqlite3_column()

The sqlite3_prepare_v2() function is the one used to prepare and execute SQL statements. The prepare function is the method that SQLite uses as a part of the following three-stage process:

1. First is the preparation stage, next the execution stage, and then the finalization stage. On the preparation side (first step), the components, as explained in Chapter 1, Introduction to SQL and SQLite, outline the parser, tokenizer, and code maker to investigate the SQL and make a statement using the sqlite3_prepare_v2() function working with the compiler. Then, a handle is created with byte code from the sqlite3_stmt function that collates and uses relevant resources for the statement to execute.
2. Secondly, VDBE within SQLite will take the byte code and execute it using the C API. The SQLite3_step() will work with (VDBE) to go through the byte code looking at locking resources as required. Different statements will work differently in VDBE, but for the SELECT statements as an example, using sqlite3_step() as part of a result set, SQLITE_ROW() will be set and the process will go through the whole dataset until SQLITE_DONE is reached. Other statements in the set including UPDATE, INSERT, and DELETE will be directly executed within VDBE.
3. The third step is the final one where the resources to VDBE are closed; the sqlite3_finalize()function does this. Once the sqlite3_finalize() function is executed and resources are free, the program comes to an end via the VDBE and the statement handle is closed as well.