Queries are written using the SQL programming language.
One way that the server and client can exchange data is simply to pass literal strings back and forth. The client could send the server the string:
SELECT name FROM employees WHERE id = 12;
and the server could send back the string:
"Smith, Jane"
In practice, however, communication is usually done via a “driver”, such as an ODBC driver or a JDBC driver. “ODBC” stands for “Open DataBase Connectivity” and is an API (Application Programming Interface) designed by Microsoft to make database access more consistent across vendors. If your client program follows the ODBC conventions, then your client program will be able to talk with any database server that follows those same 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” stands for “Java DataBase Connectivity”. It is based heavily on the ODBC standard and is essentially “ODBC for Java programs”.
There are two major 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 the query. This can be seen in SQL statements like:
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 may want to execute the same basic statement with different values. For example, if you want to insert data for 500 employees, you may not want to compose 500 separate statements such as
INSERT INTO employees (id, name) VALUES (12, 'Smith, Jane'); INSERT INTO employees (id, name) VALUES (13, 'Jones, Sally'); ...
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. This way you can easily 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 will be 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 the song “American Pie” 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 multistep process. The following shows the process of inserting data. The process is somewhat similar when you want to retrieve data.
1 Prepare the SQL statement. During the prepare phase, the server analyzes the statement and (among other things) looks to see how many parameters there will be. 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 will be 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 will read the values you have stored in the parameters and will pass those values to the server to use with the statement that it has already prepared.