Programming
 
MySQL with Other Languages
 
MySQL with Perl
 
Introduction to Perl
 
Perl is a dynamic programming language created by Larry Wall and first released in 1987. Perl borrows features from a variety of other languages including C, shell scripting (sh), AWK, sed and Lisp. For many years, perl has been the best-beloved scripting language in the Unix/Linux universe. Moreover, Perl continues to play an important role as a programming language for CGI scripts, by which dynamic web sites can be realized. Structurally, Perl is based on the brace-delimited block style of AWK and C, and was widely adopted for its strengths in string processing, and lack of the arbitrary limitations of many scripting languages at the time.
 
Perl and MySQL
 
Perl DBI (DataBase Interface) is the most common database interface for the Perl programming language. DBI was specified by Tim Bunce, in collaboration with others, starting in 1994, and is currently maintained across the Internet as a CPAN module in accordance with the Open Source model. DBD (DataBase Driver) serves as an abstraction layer which allows programmers to use nearly platform-independent SQL code in their applications.
 
Establishing a Connection to the Database
 
The connection is established with the DBI method connect. The first parameter to this method is a character string specifying the type of the database and the name of the computer (or localhost). The syntax of the character string can be deduced from the following example. The next two parameters must contain the user name and password.
 
A fourth, optional, parameter can be used to specify numerous attributes. For example, 'RaiseError'=>1 has the effect that the Perl script is broken off with an error message if the connection to the database cannot be established.
  
use DBI;
$datasource = "DBI:mysql:database=mylibrary;host=localhost";
$user = "root";
$passw = "xxx";
$dbh = DBI->connect($datasource, $user, $passw,
    {'RaiseError' => 1});
 
MySQL with C and C++
 
The C application programming interface (API) is the most elementary interface to MySQL. All other APIs, such as those for PHP, Perl, and C++, are based on the C API. Thus a knowledge of the C API will facilitate learning about the other APIs. The API functions constitute a component of the library libmysqlclient.Every C program that wishes to use MySQL functions must therefore have access to this library.
 
Please note that there are several versions of libmysqlclient in use: version 10 for MySQL 3.23.n, version 11 for the alpha and beta versions of MySQL 4.0.n, version 12 for the stable versions of MySQL 4.0.n, and finally, version 14 for MySQL 4.1.n. These libraries are largely compatible. Version 12 differs from version 10 only by some extensions that allow the new features of MySQL 4.0 to be used. This chapter is based on version 12, but because of the narrow range of difference, most of the information is valid for version 10 as well.
 
MySQL provides a client library written in the C programming language that you can use to write client programs that access MySQL databases. This library defines an application-programming interface that includes the following facilities:
 
. Connection management routines that establish and terminate a session with a server
. Routines that construct queries, send them to the server, and process the results
. Status- and error-reporting functions for determining the exact reason for an error when an API call fails
. Routines that help you process options given in option files or on the command line
 
This chapter shows how to use the client library to write your own programs using conventions that are reasonably consistent with those used by the client programs included in the MySQL distribution. I assume you know something about programming in C, but I've tried not to assume you're an expert.
 
The chapter develops a series of client programs in a rough progression from very simple to more complex. The first part of this progression develops the framework for a client skeleton that does nothing but connect to and disconnect from the server. (The reason for this is that although MySQL client programs are written for different purposes, one thing they all have in common is that they must establish a connection to the server.) Development of the framework proceeds in the following stages:
 
. Begin with some bare-bones connection and disconnection code (client1).
. Add error checking (client2).
. Add the ability to get connection parameters at runtime, such as the hostname, username, and password (client3).
 
The resulting client3 program is reasonably generic, so you can use it as the basis for any number of other client programs. After developing it, we'll pause to consider how to handle various kinds of queries. Initially, we'll discuss how to handle specific hard-coded SQL statements and then develop code that can be used to process arbitrary statements. After that, we'll add some query-processing code to client3 to develop another program (client4) that's similar to the mysql client and can be used to issue queries interactively.
 
Establishing the Connection
 
The connection to the MySQL server is established through a Connection object. If errors are to be trapped while the connection is being established, the object must be created with the constructor Connection(use_exceptions). If the connection fails to be established, then a BadQuery exception is triggered.
 
The actual connection is established with the method connect,to which up to four parameters may be passed: the database name, host name, user name, and password. Unspecified parameters are automatically read from my.cnf ([client] section). If no information is to be found at that location, then default values are used, namely, localhost as host name and the current login name as user name:
  
Connection conn(use_exceptions);
try {
    conn.connect("databasename", "hostname", "username", "password");
} // if an  error occurs
catch(BadQuery er) {
    cerr << "error is establishing the connection: " << er.error << endl;
    return 1;
}
 
Instead of using connect, you can create a connection with real_connect. The advantage is that real_connect supports some additional parameters (such as for the port number and timeout period).
 
Once the connection has been established, you can use the methods of the Connection object to create additonal objects (such as the Query object, to be defined shortly), determine various properties of the current connection with server_info, client_info, etc., execute SQL commands with exec, shut down the server with shutdown, etc.
 
To break the connection prematurely, execute the close method. (The connection is broken automatically at the end of the program.)
 
conn.close();
 
Executing Queries
 
To execute a query without results (thus not SELECT), you can simply pass the SQL character string to the method exec of the Connection object. If you are sending an INSERT command, you can also determine the ID number of the new data record with insert_id:
 
conn.exec("INSERT INTO publishers (publName) VALUES ('test')"); int newid = conn.insert_id();
 
Evaluating SELECT Queries
 
If you execute a SELECT command and wish to evaluate its result, then you should use objects of the classes Query, Result, and Row:
 
Query query = conn.query(); // generae Query object query << "SELECT . . . "; // execute SQL command Result result = query.store(); // store results
 
Once a Result object is at hand, you can determine the number of records returned by the SELECT command with one of the (equivalent) methods size or rows:
 
size_type rows = result.size();
 
If you wish to loop through all result records, then the Result object can provide an iterator. The following code lines show its use:
  
// loop over all result records
Row row;
Result::iterator it;
for(it = result.begin(); it != result.end(); it++) {
    row = *it;
    // evaluate row
}
 
You can determine the number of columns with the method size of the Row object. Access to the individual data fields is accomplished with row[0], row[1], etc., or in the more readable, but decidedly less efficient, manner row["column_name"].
 
Worthy of note is the return data type of row[ ... ]: It involves objects of the class RowData, which can be conveniently transformed by cast operations into the basic data types of C++ (for example, int, double, Date). On this point, Connector/C++ offers fundamental advantages over the C API, which always returns only character strings.
 
Thus, if the first column of a SELECT result has the data type DATE or TIMESTAMP, then you can assign the contents of this column to the variable mydate:
 
Date mydate = (Date)row[0];
 
Please note, however, that this transformation functions only if suitable data are available. (Thus you cannot change a Double into a date.)
 
Moreover, you should first test using row[ ... ].is_null( ) whether the field contains NULL. In such a case, the transformation can result in an error. (In some cases, NULL is simply transformed to 0 or 0000-00-00. But even in such cases, a NULL test should be made to distinguish 0 from NULL.)
 
If you no longer require a Result or ResUse object, you should release it with a call to purge. In particular, if you are working with ResUse objects, then the execution of purge is often explicitly required before the next SQL command can be executed.
 
Note :

MySQL can be used with variety of programming languages such as Visual Basic, Visual Basic.NET, C#, ADO.NET, Clipper etc. All the languages can not be covered in one chapter and it is not possible for me to give details of all languages in one chapter.