Queries are written by using the SQL programming language.
One way that the server and client can exchange data is simply to pass literal strings back and forth. For example, the client could send the server the following string:
SELECT name FROM employees WHERE id = 12;
and the server could send back the following string:
"Smith, Jane"
In practice, however, communication is usually done by using a driver, such as an ODBC driver or a JDBC driver.
ODBC (Open DataBase Connectivity) and is an API (Application Programming Interface) that is designed by Microsoft to make database access more consistent across vendors. If your client program follows the ODBC conventions, then your client program can access any database server that follows those conventions. Most major database vendors support ODBC to at least some extent. The ODBC standard is generally used by programs written in the C programming language.
JDBC (Java DataBase Connectivity) is based heavily on the ODBC standard and is essentially "ODBC for Java programs".
There are two main ways to pass specific data values (for example, "Smith, Jane" to the server. The first way is to simply embed the values as literals in an SQL query. For example:
INSERT INTO employees (id, name) VALUES (12, 'Smith, Jane');
This works well if you have a single statement that you want to execute. There are times, however, that you might want to execute the same basic statement with different values. For example, if you want to insert data for 500 employees, you might not want to compose 500 separate statements.
Instead, you might prefer to compose a single generic statement and then pass specific values for that statement. For example, you might want to compose the following statement:
INSERT INTO employees (id, name) VALUES (?, ?);
and have the question marks replaced with specific data values. In this way you can execute all 500 INSERT statements inside a loop without composing a unique INSERT statement for each employee. By using parameters, you can specify different values each time a statement executes. A parameter allows you to specify a variable that is used by the client program and the ODBC driver to store values that the client and server exchange. In essence, you pass a parameter for each place in the statement where you have a question mark.
Another situation where you might want to use parameters to exchange data values is when working with data that is difficult to represent as string literals. For example, if you want to insert a digitized copy of a song into your database, and you do not want to compose an SQL statement with a literal that contains a series of hexadecimal numbers to represent that digitized data, then you can store the digitized data in an array and notify the ODBC driver of the location of that array.
To use parameters with SQL statements, you go through a multi-step process. The following steps show the process for inserting and retrieving data:
1 Prepare the SQL statement.
During the prepare phase, the server analyzes the statement and (among other things) checks for required parameters. The number and meaning of the parameters is shown by the question marks that are included in the SQL statement.
2 Tell the ODBC driver which variables are used as parameters.
Telling the ODBC driver which variable is associated with which column or value is called "binding" the parameters.
3 Put values into the parameters (that is, set the values of the variables).
4 Execute the prepared statement.
During the execution phase, the ODBC driver reads the values you have stored in the parameters and passes those values to the server to use with the statement that it has already prepared.
5 Collect the result set.
The result of a query is a set of 0 or more rows. However, the result set is not passed to the client immediately.
6 Fetch the next row of the result set (if there is more than one).
When you do a fetch, you tell the server and the ODBC driver to retrieve one row of results from the result set and then store the values of that row into the parameters that you previously defined for the ODBC driver to share with your application. Normally you will perform a loop, fetching one row at a time and reading the data from the parameters after each fetch.