The using declaration disposes the con variable at the end of the enclosing scope. The SQLiteCommand is an object, which is used to execute a query on the database. The parameters are the SQL statement and the connection object. There are queries which return only a scalar value. In our case, we want a simple string specifying the version of the database. The ExecuteScalar is used in such situations.
We avoid the overhead of using more complex objects. First we drop the table if it already exists. The cars table is created. We modify the default way the data is displayed in the console.
We use the column mode and turn on the headers. Prepared statements increase security and performance. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements are faster and guard against SQL injection attacks. The name and price are placeholders, which are going to be filled later.
In the following example, we retrieve the data from the database. Its callback function is invoked for each result row coming out of the evaluated SQL statement. The second parameter is the number of columns in the result. The third parameter is an array of strings representing fields in the row. The last parameter is array of strings representing column names. Now we will mention parameterized queries. Parameterized queries, also called prepared statements, increase security and performance.
When we use parameterized queries, we use placeholders instead of directly writing the values into the statements. In the example, a question mark?
The placeholder is replaced with integer value 3. The function's second parameter is the index of the SQL parameter to be set and the third parameter is the value to bind to the parameter. In this section, we are going to insert an image to the SQLite database. Note that some people argue against putting images into databases. Here we only show how to do it. We do not dwell on the technical issues of whether to save images in databases or not.
For this example, we create a new table called Images. In this program, we read an image from the current working directory and write it into the Images table of the SQLite test. We read binary data from the filesystem. We have a JPG image called woman. The fopen function opens the specified file for for reading. We move the file pointer to the end of the file using the fseek function.
We need to determine the size of the image. If an error occurs, the error indicator is set. We check the indicator using the fseek function. In case of an error, the opened file handler is closed.
For binary streams, the ftell function returns the number of bytes from the beginning of the file, e. In case of an error, the function returns -1 and the errno is set. The perror function interprets the value of errno as an error message, and prints it to the standard error output stream.
The fread function reads the data from the file pointer and stores it in the data array. The function returns the number of elements successfully read. In this section, we are going to perform the reverse operation.
We will read an image from the database table. We read image data from the Images table and write it to another file, which we call woman2.
This SQL statement selects data from the Images table. We obtain the binary data from the first row. The binary data is written to the file with the fwrite function.
The ferror function checks if the error indicator associated with the stream is set. There are also routines in this family that return the size of the result if it is a string or BLOB and the number of columns in the result set. Every prepared statement must be destroyed using a call to this routine in order to avoid memory leaks. All prepared statements associated with the connection should be finalized prior to closing the connection.
Sometimes a multi-threaded application will create separate database connections for each thread. Note that a single database connection can access two or more databases using the ATTACH SQL command, so it is not necessary to have a separate database connection for each database file. The foregoing is all one really needs to know in order to use SQLite effectively.
All the rest is optimization and detail. In fact, these wrappers are implemented purely in terms of the core routines. In prior discussion, it was assumed that each SQL statement is prepared once, evaluated, then destroyed.
However, SQLite allows the same prepared statement to be evaluated multiple times. This is accomplished using the following routines:. It is not commonly useful to evaluate the exact same SQL statement more than once.
More often, one wants to evaluate similar statements. Or you might want to evaluate the same query multiple times using a different key in the WHERE clause. To accommodate this, SQLite allows SQL statements to contain parameters which are "bound" to values prior to being evaluated. These values can later be changed and the same prepared statement can be evaluated a second time using the new values. Parameters may not be used for column or table names.
A parameter takes one of the following forms:.
0コメント