Contents


MySQL for Linux on POWER, Part 2

Developing applications

Content series:

This content is part # of # in the series: MySQL for Linux on POWER, Part 2

Stay tuned for additional content in this series.

This content is part of the series:MySQL for Linux on POWER, Part 2

Stay tuned for additional content in this series.

Part 1 introduced MySQL and related tools on Linux on POWER and covered some of the main concepts, such as creating a database, populating it with data, and verifying referential integrity.

This part introduces the basics of developing applications on Linux on POWER to communicate with MySQL in major programming languages, such as PHP, Java, C/C++, Perl, and Python.

Examples in this part are based on the database created in Part 1. To better understand the examples presented in this document, please review the database structure created in Part 1.

All of the examples were compiled and run on Linux on POWER. But note that the instructions are also applicable to Linux in general.

Developing for MySQL in PHP

This section describes how to use PHP and MySQL in the most common scenario: using the Apache Web server.

In the most common configuration, you will need the following components installed on your system:

  • PHP core files including PHP binary
  • PHP module for your version of Apache
  • Apache web server
  • PHP extension module with MySQL access functions

Each of these components usually comes as rpm packages on any Linux distribution standard install media, including Linux on POWER and PPC.

Let’s say you want to create a very simple web-based application that allows you to insert data into and extract data from the EMPLOYEE table of our CONTRACTING database.

The HTML entry form is presented in Figure 1.

Figure 1. HTML data entry form
HTML data entry form
HTML data entry form

The action of this HTML data entry form is to call PHP script insert.php, as shown below:

...
<FORM METHOD="POST" ACTION="insert.php">
...

The major components of this form are input text boxes "Personal_FirstName" and "Personal_LastName": three text boxes for entering employee’s SSNs and drop-down, single-selection list with job titles and corresponding assigned job codes, as seen in the code excerpt below:

. . .
<SELECT NAME="Job_Title">
<option value="200" selected>Application Programmer</option>
<option value="201">Database Administrator</option>
<option value="202">Technical Support</option>
<option value="203">Database Designer</option>
</SELECT>
. . .

Note: To maintain focus on the major concepts, this example does not present the rigorous user data input validation and error processing that you would normally see in a production quality application.

When a user clicks on the Submit Form button, the insert.php script, shown below, runs on the server-side:

Listing1. PHP code example
<?
//MySQL server username, password, and database name
$username="username";
$password="password";
$database="CONTRACTING";

//Extracting information from the form
$first_name=$_POST['Personal_FirstName'];
$last_name=$_POST['Personal_LastName'];
$ssn=$_POST['Personal_SSN1'].$_POST['Personal_SSN2'].$_POST['Personal_SSN3'];
$job_id=$_POST['Job_Title'];

echo '<b>', 'Data extracted from the form: ', '</b>', <br>';
echo 'First Name: ', "$first_name", '<br>';
echo 'Last Name: ', "$last_name", '<br>';
echo 'Social Security Number: ', "$ssn", '<br>';
echo 'Job ID: ', "$job_id", '<p>';

//Connecting to the MySQL server
echo '<b>', 'MySQL server connection status: ', '</b>';
$conn=mysql_connect('127.0.0.1',$username,$password);
if (!$conn) {
   die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully', '<p>';

//Changing into the database
echo '<b>', 'Selecting database ', "$database", ' status: ', '</b>';
@mysql_select_db($database) or die( "Unable to select database");
echo 'Successful', '<p>';

//Inserting data into the database
$query = "INSERT INTO EMPLOYEE VALUES ('$ssn','$last_name','$first_name','$job_id')";
echo '<b>', 'SQL query: ', '</b>', "$query", '<p>';
echo '<b>', 'Inserting data status: ', '</b>';
$result=mysql_query($query);
if (!$result) {
   die(mysql_error());
}
echo 'Successful', '<p>';

//Displaying inserted data
$query="SELECT * FROM EMPLOYEE";
$result=mysql_query($query);
if (!$result) {
   die(mysql_error());
}
$num=mysql_numrows($result);
mysql_close($conn);
echo "<b><center>Displaying the EMPLOYEE table contents:</center></b><br><br>";

$i=0;
while ($i < $num) {

$first_name=mysql_result($result,$i,"EMP_FNAME");
$last_name=mysql_result($result,$i,"EMP_LNAME");
$ssn=mysql_result($result,$i,"EMP_SSN");
$job_id=mysql_result($result,$i,"JOB_CODE");

echo "<b>$first_name $last_name</b><br>SSN: $ssn<br>Job ID: $job_id<p>";

$i++;
}
?>

The output of the PHP script is shown in Figure 2.

Figure 2. HTML page produced by the PHP script
HTML page produced by the PHP script
HTML page produced by the PHP script

Notice that the last entry for employee Adam Adams made it into the EMPLOYEE table.

This example illustrates how easy it is to create a database-driven Web application using PHP, Apache, and MySQL, which are bundled by default with all major Linux distributions, including Linux on POWER and PPC architectures.

Developing for MySQL in Java

The topic of Java™-based multi-tier applications communicating with relational databases, like MySQL, is very broad. This section shows a very simple example of a local Java application connecting to MySQL.

For your Java program to communicate with a particular database you need a Java Database Connectivity (JDBC) driver for that database. As with most popular Relational Database Management Systems (RDBMS), MySQL has its JDBC drivers, currently:

  • MySQL Connector/J from MySQL AB
  • The Resin JDBC driver

MySQL Connector/J is an implementation of Sun's JDBC 3.0 API for the MySQL RDBMS and is an official JDBC driver for MySQL. It is 100 percent pure Java, and will, therefore, run on any operating system that provides an appropriate JVM environment, including Linux on POWER distributions. Therefore, this example uses the Connector/J driver. It is a type IV JDBC driver and is known to work with such Web application servers as IBM WebSphere, BEA WebLogic, Apache Tomcat, JBoss, and many others on Linux on POWER.

Aside from the JDBC driver for MySQL you will, of course, need the JDK itself. At the time of this writing, the latest version IBM provides for Linux on POWER and PPC architectures is JDK 1.4.2 in both 32-bit and 64-bit flavors. IBM Developer Kits for Java technology are available online. (See Related topics.)

This example shows the basic code for connecting to a MySQL database and performing queries.

The following is the complete Java code for this example:

Listing 2. Java code example
import java.io.*;
import java.util.*;
import java.sql.*;

public class Java_MySQL
{
   public static void display_rs(ResultSet rs) throws SQLException
   {
      try{
         ResultSetMetaData rsmd = rs.getMetaData();
         for (int i = 1; i <= rsmd.getColumnCount(); ++i)
            System.out.print("\t\t\t" + (rsmd.getColumnName(i)).toUpperCase() );
            System.out.println();
         while ( rs.next() )
         {
            for (int j = 1; j <= rsmd.getColumnCount(); ++j)
            {
               Object obj = rs.getObject(j);
               System.out.print("\t\t\t" + obj.toString());
            }
            System.out.println();
         }
      }
      catch (SQLException E) {
            System.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
            E.printStackTrace();
            System.exit(1);
      }
   }
   public static void main(String args[])
   {
      Statement statement = null;
      Connection connection = null;
      ResultSet resultset;
      String query, prompt, input;
      int choice = -1;
      try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
      
      catch (Exception E) {
         System.err.println("Unable to load driver.");
         E.printStackTrace();
         System.exit(1);
      }
      try {
         String url="jdbc:mysql://localhost/CONTRACTING";
         String username="username";
         String password="password";
         connection=DriverManager.getConnection(url, username, password);
      }
      catch (SQLException E) {
         System.out.println("SQLException: " + E.getMessage());
         System.out.println("SQLState:     " + E.getSQLState());
         System.out.println("VendorError:  " + E.getErrorCode());
         E.printStackTrace();
         connection=null;
         System.exit(1);
      }

      prompt = "\n\t\t\t1. Show contents of the table JOB\n" +
               "\t\t\t2. Exit\n\n";

      while(true)
      {
         System.out.println(prompt);
         try {
            BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
            input = in.readLine();
            choice = Integer.parseInt(input);
         }
         catch (Exception e) {
            e.printStackTrace();
            System.exit(1);
         }

         try {
            switch (choice)
            {
               case 1:
                  query="SELECT * FROM JOB;";
                  statement = connection.createStatement();
                  resultset = statement.executeQuery(query);
                  display_rs(resultset);
                  break;
               case 2:
                 System.out.println("Bye!");
                 System.exit(0);
              default:
                 System.err.println("Invalid value entered!");
            }
         }
         catch (SQLException E) {
            System.out.println("SQLException: " + E.getMessage());
            System.out.println("SQLState:     " + E.getSQLState());
            System.out.println("VendorError:  " + E.getErrorCode());
            E.printStackTrace();
            connection=null;
            System.exit(1);
         }
      }
   }
}

Function display_rs() has standard code for displaying the ResultSet object. Connection to the MySQL database is done in the main() function.

The name of the class that implements java.sql.Driver in MySQL Connector/J is com.mysql.jdbc.Driver. The org.gjt.mm.mysql.Driver class name can be used to remain backwards-compatible with older versions.

The most common way to make the Connector/J JDBC driver available to the JVM is to include a path to the mysql-connector-java-[version]-bin.jar file in the CLASSPATH variable.

The string shown below, follows the usual JDBC URL format:

String url="jdbc:mysql://localhost/CONTRACTING";

Having connected successfully to the MySQL server and your database, you can perform a simple query, like this:

1. Show contents of the table JOB
2. Exit

Option “1" will show the results of the query “SELECT * FROM JOB".

JOB_CODE        JOB_NAME                     JOB_HOUR_CHRG
 200             Application Programmer       35.48
 201             Database Administrator       38.50
 202             Technical Support            27.00
 207             Database Designer            49.99

The second option will produce the contents of the JOB table, and the third option will close the program.

This example was complied and run using 64-bit JDK 1.4.2 from IBM for Linux on POWER and PPC architectures.

Developing for MySQL in C and C++

The official C API for connecting to MySQL is included with the MySQL server. To use the C API for MySQL, your code must include the mysql.h header file, making sure your program is linked to the MySQL client library.

There is also a C++ API, called MySQL++, for connecting to MySQL from C++. This API is a C++ wrapper for MySQL’s C API. It is built by the Standard Template Library (STL) principle. So working with it is as easy as working with an STL component. MySQL++ can be obtained from Tangentsoft.net; the Web site also contains a number of examples that can be compiled and used to teach oneself this API. (See Related topics.)

This section provides an example of how to use the official C API for MySQL on Linux on POWER and PPC.

Below is a simple code sample that shows the standard function calls needed to connect to and extract data from your MySQL database:

Listing 3. C code example
#include <stdlib.h>
#include <stdio.h>
#include <mysql.h>

int
main(int argc, char **argv) {

   MYSQL mysql;
   MYSQL_RES *res;
   MYSQL_ROW row;

   unsigned int num_fields;
   unsigned int i;
   char *query="SELECT * FROM JOB";

   /*Initializing MySQL connection*/
   if(mysql_init(&mysql)==NULL) {
      printf("Failed to initate MySQL connection\n");
      exit(1);
   }

   /*Connecting to MySQL server*/
   if (!mysql_real_connect(&mysql,"127.0.0.1","username","password",NULL,0
   ,NULL,0)) {
      printf( "Failed connect to the server: %s\n",
       mysql_error(&mysql));
      exit(1);
   }

   /*Selecting database*/
   if(mysql_select_db(&mysql,"CONTRACTING")!=0)
      printf( "Failed select CONTRACTING: %s\n", mysql_error(&mysql));

   /*Performing SQL query*/
   if(mysql_query(&mysql,query)) {
      printf("MySQL query error: %s\n",mysql_error(&mysql));
      mysql_close(&mysql);
      exit(1);
   }
   res = mysql_store_result(&mysql);

   if (res) {
      num_fields = mysql_num_fields(res);
      while ((row = mysql_fetch_row(res)))
      {
         for(i = 0; i < num_fields; i++) {
            printf("%s\t",  row[i] ? row[i] : "NULL");
         }
         printf("\n");
      }
      mysql_free_result(res);
   }
   else {
      if(mysql_field_count(&mysql) > 0)
      {
         printf( "Error getting records: %s\n", mysql_error(&mysql));
      }
      else {
         printf( "Failed to find any records and caused an error: 
         %s\n", mysql_error(&mysql));
      }
   }
   mysql_close(&mysql);
}

As you can see, the standard construct of the program is the same as in the earlier examples: initialize the connection, connect to the server, select the database and perform your query, display the results, and, finally, close the connection to the database.

To compile using IBM XL C/C++ compiler for Linux on POWER, issue the following command:

xlc -o C_MySQL C_MySQL.c -qarch=auto –O -I/usr/local/mysql/include 
-L/usr/local/mysql/lib -lmysqlclient -lz

The same compiler flags, minus the –qarch=auto option, are used to compile the same code with the GNU C/C++ compiler, as shown below:

g++ -o C_MySQL C_MySQL.c –O -I/usr/local/mysql/include 
-L/usr/local/mysql/lib -lmysqlclient -lz

The –qarch=auto option instructs the IBM XL C/C++ compiler to determine the host POWER or PPC architecture automatically and produce highly optimized code for it.

IBM XL C/C++ compiler can produce highly optimized machine code for POWER and PPC architectures, usually giving significant performance improvements over the binaries compiled with C/C++ compilers from the GCC. To learn more about the IBM XL C/C++ compiler for Linux on POWER, please refer to the XL C/C++ Advanced Edition for Linux Web page. (See Related topics.)

When you run the compiled binary, the query “SELECT * FROM JOB" executes, and the results are printed back to standard out.

200     Application Programmer   35.48
201     Database Administrator   38.50
202     Technical Support        27.00
207     Database Designer        49.99

As you can see, MySQL’s C API is pretty straightforward and easy to use.

Developing for MySQL in Python

On Linux distributions for POWER and PPC architectures, you can also develop your applications to communicate with MySQL in scripting languages, like Python, Perl, and Ruby. For these languages there are contributed MySQL APIs that are included in the major Linux on POWER and PPC distributions, along with runtime environments.

This section covers developing a simple exemplary application for MySQL in Python.

To develop Python applications for MySQL on Linux, you need to have at least two components installed:

  • Python interpreter run time environment
  • MySQLdb Python-MySQL database server interface for Python

Both of the above components are included in such Linux for POWER distributions as Red Hat Enterprise Linux (RHEL) and SUSE LINUX Enterprise Server (SLES).

You can also access Python.org and the Python-MySQL project home page for the latest Python environments. (See Related topics.)

The following example shows a simple Python application that connects to the CONTRACTING database we created in Part1 and performs a simple query:

Listing 4. Python code example
#! /usr/bin/python

# import MySQL module
import MySQLdb

# connect to the database
db = MySQLdb.connect(host="127.0.0.1", user="username", 
passwd="password", db="CONTRACTING")

# get a cursor
cursor = db.cursor()

# SQL statement
cursor.execute("SELECT * FROM JOB")

# get the result set
result = cursor.fetchall()

# iterate through the result set
for record in result:
        print record[0] , "\t", record[1], "\t", record[2]

As you can see, the structure of this simple program is straightforward and follows the general structure of the previous examples.

Assuming that the file name is python2mysql.py, you can execute the program by issuing the command:

python python2mysql.py

Alternatively, you can change permissions on the python2mysql.py file to make it executable, and then execute it. In this case the “magic" line, similar to the one below is mandatory:

#! /usr/bin/python

To change permissions to make file executable before you can run it, issue:

chmod 755 python2mysql.py

Now we can run the file as a shell script:

nik@bluebill:~> ./python2mysql.py
200     Application Programmer  35.48
201     Database Administrator  38.5
202     Technical Support       27.0
207     Database Designer       49.99

For more information on Python, please refer to the Python documentation project. (See Related topics.)

Developing for MySQL in Perl

To develop applications for MySQL in Perl, you need to have at least the following components:

  • The Perl interpreter run time environment
  • Perl Database Interface(DBI)
  • Perl database interface to the MySQL database

All of these components are included with standard distributions of such major Linux on POWER distributions as RHEL and SLES.

The latest Perl and Perl MySQL DBI releases and documentation can be found online. (See Related topics.)

This section presents a simple example that performs the same function as the previous Python example.

The following example connects to the CONTRACTING database, performs the query, and displays the result:

Listing 5. Perl code example
#!/usr/bin/perl

use DBI;

# server host information
$host="127.0.0.1";
$port="3306";

#database and user credentials information
$db="CONTRACTING";
$userid="mysql";
$passwd="";
$connectionInfo="DBI:mysql:database=$db;$host:$port";

# connect to the database
$dbconn = DBI->connect($connectionInfo,$userid,$passwd);

# prepare and execute query
$query = "SELECT * FROM JOB ORDER BY JOB_CODE";
$st = $dbconn->prepare($query);
$st->execute();

# assign columns
$st->bind_columns(undef, \$JOB_CODE, \$JOB_NAME, \$JOB_HOUR_CHRG );

# output the result
while($st->fetch()) {
   print "$JOB_CODE\t $JOB_NAME\t $JOB_HOUR_CHRG\n";
}

$st->finish();

# disconnect from the database
$dbconn->disconnect;

As with the previous Python example, this Perl code can be run through the interpreter from the command line:

perl perl2mysql.pl

Or it can be made executable:

chmod 755 perl2mysql.pl

And run as a shell script:

nik@bluebill:~> ./perl2mysql.pl
200      Application Programmer  35.48
201      Database Administrator  38.50
202      Technical Support       27.00
207      Database Designer       49.99

As in the latter case, the “magic" line:

#! /usr/bin/perl


pointing to the location of your Perl binary is mandatory.

For more information on the use of Perl with MySQL, please refer to the MySQL Perl Web page. (See Related topics.)

Summary

MySQL is widely accepted as one of the best performing Open Source enterprise-class Linux relational database servers available today. The IBM 64-bit POWER platforms meet all the requirements of today’s clients by delivering superior performance, reliability, and high availability. The combination of MySQL RDBMS, with its robust features and IBM POWER processor-based servers capable of Micro-partitioning, dynamically enabling and disabling SMT, provides one of the strongest Linux database servers platforms available in the market.


Downloadable resources


Related topics

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Linux, Open source
ArticleID=75293
ArticleTitle=MySQL for Linux on POWER, Part 2: Developing applications
publish-date=04072005