Call Level Interfaces
Contents |
[edit] Introduction
[edit] Calling upon SQL from the Application
A lot of the content on this site is focussed on the server-side of mysql database application development: The SQL language, the MySQL procedural extension, triggers, views and whatnot. Although the MySQL server is quite literallly the heart of the matter, It’s good to realize that database application development does not end there.
End users of the database system cannot, and indeed, should not communicate in SQL with the database server. At least, they should not do so directly. End users interact with the database via one or several application programs or just: applications.
Application programs should support end users in fullfilling their role in the business process. They do so by making high level functionalities available to end user, functionalities that focus on the business process rather than on data.
Nowadays, end user applications tend to be quite graphically oriented, and end users tend to interact quite dynamically with the application. For example, a pointing device such as a mouse or trackball is used to navigate through menus and treeviews, drag and drop documents, or select values in fill-out forms. Somehow, user interaction needs to result in changes in the data stored in the database.
It’s clear that changes in the database are achieved by executing SQL. On the other hand, SQL itself cannot express how a graphical end user interface should look like, or how the mousedown event should be handled. Of course, SQL can express the information (or rater, the data) to define these things; however, it lacks the constructs to realize them.
From what we just stated, it is evident that end-user applications themselves are not built in SQL. Rather, they are written in a procedural or object-oriented programming language, like Visual Basic, Java or C#.NET. Alternatively, end-user applications can be built using some sort of web-enabling technology: a scripting language like PERL or PHP, or some other sort of Server Pages technology like JSP or ASP.
[edit] SQL Call Level Interface (CLI)
There must be some way for the application front-end to gain SQL access to the database back-end. Likewise, there must be some means for the database to deliver it’s data to the application. In other words, there must be some interface that handles communication between the terminal extremities of the application system.
Whatever architectural paradigm (2-tier, 3-tier, n-tier) is used to structure the interaction between the end-user application and the database, there’s typically a distinct module that is occupied solely with offering SQL access to the database. The SQL Standard (Part 3) refers to this as the SQL Call Level Interface, or CLI.
Essentially, a (SQL) CLI is just a set of routines that can be invoked from a particular programming language, the host language, to send SQL commands directly to the database server and access data received from the server within the application. A CLI is usually implemented as an external library that can be linked into the main program in a manner that is dependant upon the host language.
Traditionally, a CLI is thought of as exclusively procedural interface. Here, we choose to include some of the solutions used to access SQL databases from object-oriented languages also. In our opinion, the important thing is that that the CLI offers SQL access to the database from the host language environment. The object-oriented or procedural character of the host language has of course an impact on the details of employing the CLI in that particular case. However, this does not affect the intrinsic functionality offered by the CLI from a functional point of view. SQL access is neither impaired nor enhanced by the procedural or object-oriented nature of the host language.
The mysql reference manual covers CLIs for several application programming languages. Furthermore, the manual discerns between the Connectors and the API’s, although it is not entirely clear on what criteria this distinction is based. Because the term connector applies quite specifically to MySQL, and the term API is very generally applicable to other fields as well, we prefer the term SQL Call Level Interface. Because that’s just a mouthful, we will refer to it with the abbreviation, CLI, most of the time.
Call level interfaces are available for virtually every relational RDBMS, and usually, several host languages are supported through their respective CLI. Some CLIs are generic by design and do not depend on a particular RDBMS. For example, JDBC or ODBC are designed with no particular RDBMS in mind.
[edit] Patterns of operation
Despite the apparent diversity, the functionalities and modes of operation are remarkably similar across different CLIs. Here, in this introduction, we will focus mainly on these general modes of operation rather than on the particulars for a given host language or RDBMS. In the remainder of this section, we will illustrate how these patterns apply to some of the specific MySQL CLIs.
The CLI mediates the following interactions between the server and the client:
1. The client obtains a connection 2. The client sends a command to the server 3. The server executes a command on behalf of the client 4. The server acknowledges execution of the command, possibly returning data to the client 5. If applicable, the client processes data received from the server 6. The client discards the connection
The events between opening and closing the connection mark a session. Usually, the sequence of sending a command and receiving data from the server is executed repeatedly in the course of the session. During the session, some condition might arise that disturbs this process. This could result in abrupt termination of the session. Usually, the CLI provides some sort of mechanism in order for the host language environment to detect this.
[edit] Sessions and Connections
The application obtains a connection by calling a routine provided by the CLI. The routine call prepares the server to communicate with the particular client. This process allocates some resources on the server-side. That’s why the client should explicitly signal the server to discard the connection when it’s done using it.
[edit] Connection Parameters
Usually the connect routine accepts some distinct pieces of data that modify or configure the properties of the connection. We will refer to the data passed to the connect routine as the connection parameters.
In most cases, data needs to be passed to the routine to identify the database server, and to choose or configure a communication protocol. Exactly what sort of data the routine needs is dependent upon matters such as the RDBMS, the communication protocol or the configuration files that are part of the specific CLI framework. For example, if the application connects directly using some sort of TCP/IP protocol, this data may comprise an ip address and a port.
Normally, the database server will want to authenticate the user that requests the connection. Therefore, the routine usually needs some data that can be used to authenticate the user, the credentials. Again, the exact sort of the information is dependent upon the RDBMS or the specific CLI in use.
Apart from these very common ones, a CLI may support all sorts of connection parameters, offering precise control on things like security, compression, timeouts and autocommit behavior.
The routine accepts it’s connection parameters in a manner that is entirely dependent upon the CLI. Some will accept just one string that contains the data in some separated format, whereas others want to receive each piece of data as a distinct argument.
[edit] Connection Handle
The routine also modifies the environment of the client, providing some sort of handle that the client may use to refer to the connection. The other CLI routines require this handle to communicate with the server.
The mechanism to supply the handle and the nature of the handle itself is dependant upon the CLI, and to some extent, the host language. For example, in a procedurally oriented language, the CLI routine may take the form of function call that returns the handle, and the handle itself maybe as simple as a unique integer. In an object oriented language, the handle will usually take the form of (a reference to) an object.
The important thing to realise is that the handle is there because the host language environment needs to be able to refer to the actual communication channel with the server. It doesn’t matter too much how it is implemented, as long as it does the job. We’ll refer to this handle as the connection-handle.
[edit] Commanding the Server
Server commands can be issued through a CLI by calling some specialized routine. Such a routine almost invariably accepts a connection handle corresponding to an opened connection.
[edit] Command Text
The routine may perform a very specific task, and in such a case, the connection-handle is enough to have the server execute the command. However, in most cases, the routine needs at least an extra text string parameter that contains the command to execute, the command-text.
In it’s simplest form, the command-text is required to be an ordinary SQL statement. However, the CLI may allow an extended SQL syntax for the command-text. In particular, the CLI may have a specialized routine to perform a call to a server side stored procedure, or to retrieve all the records from a single table. Because the CLI can infer the full syntax required by the server in those cases, it may be sufficient to pass only the name of the respective object as command-text.
[edit] Immediate and Prepared Statements
Sending the command to the server may occur in either the immediate or the prepared mode. In the immediate mode, the routine sends the command to the server, which executes it immediately. In the prepared mode, the routine sends the command but the server does not execute it; at least, not right away. Instead, the routine delivers a handle that may be used to refer to the command. This is called the command-handle. When command preparation is supported by the CLI, it must define some other routine that executes previously prepared statements. The execute routine will accept a previously obtained command-handle.
From a technical point of view, a command-handle is not so different from a connection-handle. Functionally, they are very different of course, but in either cases, the handle is just a means for the host language environment to refer to a particular object on the server side. Like it is the case with the connection-handle, there is no specific mechanism to return the handle to the client, and the exact implementation of the handle itself is dependant upon both the CLI and the host language.
The prepared mode is potentially more efficient than the immediate mode. The efficiency gain stems from a number of factors. The command-text needs to be sent over the network only once, and it needs to be parsed only once. Especially in case the command-text defines a query of some sort, the database server has to devise a query plan. Statement preparation offers the chance to reuse the query plan too. The processing steps that the server undertakes upon command preparation are referred to collectively as pre-compilation. Of course, efficiency is gained only when a command needs to be executed more than once.
[edit] Parameter Definition
A CLI that support prepared commands usually also supports parameters. To prepare a command, it is sufficient that the structure of the statement is known to the server. Value expressions that are part of the statement need be known only in the execution phase, offering an opportunity to parameterize value expressions. This is a lot like parameter declarations in functions or stored procedures: the parameter is just a placeholder, or formal parameter, that is assigned an actual value when executed.
The details of parameter definition depend upon both the CLI and the host language. Most CLIs support a mechanism that allows parameters to be defined as part of the command-text. The CLI then supports some special syntax to denote a parameter, and the command-text is like an ordinary SQL statement interspersed with parameter denotations. The exact syntax to denote a parameter is dependant upon the CLI.
The CLI may define an additional mechanism to declare parameters for commands that call a server-side stored procedure. Because the CLI can induce the full syntax required by the server in those cases, parameters can be declared explicitly via a programmatic interface.
Parameters have a direction. There are three possible directions: IN, OUT and INOUT. An IN parameter maybe used to inject values from the host language environment into the command prior to execution. An OUT parameter maybe used to deliver values from the server-side to the host language environment after execution. An INOUT parameters combine both directions, with the obvious result that they can both accept a values as well as deliver one.
[edit] Parameter Binding
Execution of a parameterized command involves binding variables from the host language environment to the formal parameters of the prepared command. The binding process enables transfer of data from the host language environment to the server (via the IN and INOUT parameters) and vice versa (via the OUT and INOUT parameters).
Some CLIs require a separate binding step for each separate execution of the command, whereas other CLIs bind host language variables by reference, allowing for a single binding step. Binding can also be encapsulated completely by the CLI, allowing only access to the parameter values through specialized routines.
Another source of variety concerning parameter binding is the mechanism that is used to identify a particular parameter. Most CLIs allow references to a parameter using it’s ordinal position. The ordinal position of a parameter can be determined by reading the command text from left to right, counting the parameter denotations. Others CLIs use named parameters, allowing parameters to be referenced by name. Finally, some CLIs allow support both methods of identifying a parameter.
[edit] Receiving and processing Results
CLI routines that execute a command on the server generally communicates the server’s response to that command back to the client. The response might be a simple acknowledgement, or it may represent some data retrieved from the database. DDL statements will generally result in simple acknowledgements without any data being returned. For INSERT, UPDATETE and DELETE statements, the number of records that was touched by the statement may be available through the CLI. Data may be returned in response to executing SELECT commands or server-side stored procedure calls.
If the command returns data, it usually becomes available in the form of a resultset. The resultset is made accessible from the host language environment through some sort of resultset-handle. The resultset-handle maybe identical to the command-handle. Data need not always be returned as a resultset. In some cases, the CLI can retrieve the rows retrieved from the server-side directly to the host language environment in the form of an array or other composite type. However, this is an exceptional situation.
[edit] Resultset Traversal
A resultset is like a cursor. Indeed, in many cases, the CLI simply exposes server-side database cursor functionality to the host language environment. However, this is not a necessary implementation. Some CLIs retrieve (a portion of) the dataset from the server at once, and store it in a buffer on the client side. This buffer is then used to emulate cursor functionality.
The resultset maintains some sort of current record pointer. The CLI offers routines that accept the resultset-handle to move the record pointer through the records in the resultset. The CLI also offers a routine that to check if a certain move will position the record pointer at an existant position. Together, these routines can be used to set up a loop that traverses the records in the resultset.
Almost every CLI offers a routine that moves the internal record pointer one position forward relative to the current position of the record pointer, effectively positioning it at the next record. In a lot of cases, this is the only move that can be made with the resultset pointer. It is by far the most useful, and probably the most used one.
In principle, a CLI can support any variation on the parameters of movement of the record pointer: it may move in the reverse direction instead of moving forward only; it may skip records instead of taking steps of one record at a time; it may be positioned using an absolute ordinal position instead of relative to the current position.
[edit] Accessing Resultset data
For a resultset, only the fields of the current record are accessible. Some CLIs use variable binding to expose the fields. Like with parameter binding, variables of the host language environment are mapped by reference to the columns of the resultset. However, most CLIs offer specialized routines to access the fields of the current record.
In many cases, a CLI has specialized routines to access a particular field from the current record. Such a routine accepts at least the resultset-handle and some column reference. In most cases, a column reference must be an ordinal position but sometimes, named references are allowed too.
Invariably a CLIs will have routines to read the value of a particular field. Sometimes, the CLI offers routines to write a value to a particular field of the current record as well. If the CLI offers such functionality, it will usually depend upon the properties of the underlying resultset, and the query that generated it, if the update will succeed. To accommodate strong-typed host languages, the CLI may offer a family of routines rather than a single routine. This is because both the read and the write routine need to be available for a variety of datatypes supported by the host language.
Some CLIs offer a routine to retrieve all values from the current record into an array or other composite type. The CLI may even offer a routine that combines record-pointer movement and value retrieval.
[edit] Datatype mappings
In most cases, the datatype system in the host language differs dramatically from that of the database server. Even if a straightforward mapping exists for a given pair of datatypes (for example, a database INT may match the long type in the host language), differences are likely to exist in internal value representation.
Because data flows to and fro the CLI between the host language environment and the database server, there has to be some kind of scheme that takes care of translating the representations of values between the two type systems. This is taken care of by the CLI.
Usually, the CLI is quite good at implicitly converting values from the one type system to the other. In some cases, quite an amount of additional control can be exerted too. Some CLIs allow an explicit conversion from database datatype to a desired host language datatype, especially in case of a strongly type host language. A CLI may also offer functionality so that the host language may discover the most convenient mapping at runtime.
[edit] Miscellaneous other Tasks
Some of the most frequent tasks that are executed through the CLI have just been discussed. However, there are some other important tasks that we’ve left untouched up until now.
[edit] Error handling
During the course of a session, errors might occur. These errors might simply be due to error conditions raised by the database server in response to an invalid command. Errors can also arise because of defects occurring in the infrastructure that connects the client to the server. For example, the network may go down, or the database server host could crash. The CLI anticipates these possible errors, and offers means to detect or handle these errors, should they occur. The precise mechanism of error handling is dependant upon both the CLI and the host language.
[edit] Transaction Control
A CLI usually has some features to exert transaction control. In most cases, the CLI provides routines to issue COMMIT or ROLLBACK commands. A CLI may also accept certain connection parameters setting the default autocommit mode for the duration of the session.
[edit] Accessing Metadata through the CLI
Quite a few CLIs offer a set of routines to acquire metadata. Especially CLIs that are independant of a particular RDBMS (JDBC, ODBC) tend to have extensive metadata support. This makes quite good sense for building applications that must support for multiple RDBMS-es. In such a case, it’s the job of the CLI to make an abstraction of the different native data-dictionaries present in the supported RDBMS-es. Of course, when the application is restricted to MySQL, it is probably not a bad idea to use the information schema as a source of metadata.
Metadata need not be confined to database metadata alone. In principle, metadata may be gathered for an SQL query too. Some CLIs do in fact offer routines to do that as well. Then, metadata consists out of a description of the columns retrieved by the query, their column types etc.
Metadata support is one of the functionalities that shows the most diversity among all CLIs. Because there is so much diversity, we won’t give this much attention here.
[edit] Call Level Interfaces available for MySQL
There are quite a few libraries that implement a MySQL Call Level Interface. For a complete list, see the MySQL reference manual. In the remainder of this section, we focus on the following CLI Implementations:
MySQL Connector/NET
A MySQL CLI implementation for the Microsoft .NET Framework
MySQL Connector/J
The MySQL JDBC Driver for use in Java applications
[edit] Examples
The remainder of this section will use a common set of examples. The examples are all based upon the pers database presented in the Hello World section of this site. You don’t have to install this database; the sample code discussed in the articles on the specific CLIs will install it for you, illustrating the usage of the respective CLI in the process.
- Establishing a Connection
A connection is established with MySQL. If possible, the autocommit mode is set on through the connection parameters. Else, the autocommit mode is set on in a separate statement. We need this to illustrate transaction control later on. The connection remains open during the sample.
- Using immediate commands
Immediate commands are used to perform some DDL. The pers database is created, and set as the current database. The tables emps and dept are created. The pers database will remain the current database for the rest of the sample
- Using prepared commands
An INSERT INTO dept is performed using an immediate command, demonstrating that these are not limited to DDL. A similar INSERT is performed, this time using a prepared command with parameters. One parameter is initialized to the NULL-value in order to illustrate this type of NULL-handling.
- Querying and Resultset processing
A SELECT * from dept command is prepared without parameters. It is executed and the resultset is traversed in forward direction. The field values of the records are printed using the most common method for the host language. The sample demonstrates the most convenient way to loop through the results. The sample should demonstrate at least how to access a single field. If the specific CLI supports specialized methods for resultset processing, such as buffering or built-in rendering capabilities, these are discussed here too, perhaps not in full.
- Transaction control and error handling
The current autocommit mode and transaction isolation level are reported. Then, a transaction is started. The previously prepared INSERT statement is set up to violate the primary constraint. It is executed and the resulting error condition is handled: a rollback is performed, and the problem with the prepared INSERT statement is fixed. The INSERT is then executed and committed. Then, the autocommit mode is set back on.
- Stored Procedures and Functions
A stored procedure and a function are created using immediate statement syntax, illustrating that this is just ordinary DDL. The procedure is called, and it is demonstrated how to process multiple resultsets returned by the procedure. Also, the value of an OUT parameter is retrieved.
- Closing the connection
The connection is closed; and the sample ends here.