Using MySQL, Perl and jQuery to Auto-Populate a Form Field on a Web Page

If you have ever built a form on a web page, you might have used a drop-down menu to display the choices available for a particular field. With a drop-down menu, you restrict the choices a user may select so that the user doesn’t enter invalid data (among other reasons). If a user misspells an entry, then a subsequent search for that value would not produce a found result.

A friend of mine who runs an online forum sent me an email about a problem he was having. He was trying to modify an existing registration web page using jQuery to auto-populate the state names, and then pass the state abbreviation back to his MySQL database. Believe it or not, he was actually having problems with people knowing their own state abbreviation. He had searched and found an example of what he wanted to do, but he couldn’t get it to work. So, I took the example that he found and figured out what he was doing wrong.

I had first suggested that he just hard-code the list of states and their abbreviations in the HTML code. But, he said that if he figured out how to use jQuery with his state abbreviation problem, he would also use it on other parts of his web page where he had dynamic data. With drop-down menus, it is fairly easy to create a list of static data to use in the HTML form. But, in his case, what happens if the data is dynamic, or if there are too many items to list in a drop-down menu? If you have ever had to include your country in a form (and you live in the US), it is always a pain to have to scroll down to the bottom of the long list to find “United States”.

An easy way to create a dynamic list (or even a long static list) is to use jQuery to access your data from a MySQL database, and to build an auto-populated field in your form. When the user starts typing their entry, jQuery will search the MySQL database for a list of “like” matches, and then return a list of all of the matching results for that field.

For this example, we will be using jQuery, Perl and a MySQL database to auto-populate a “state” field in a form. Once a user has typed three characters in the state field box, a list of matching states will appear in a drop-down menu. It will also return the state abbreviation and state id (code), which can then be saved in the MySQL database after the form has been submitted. In this picture from our example, the user has typed “new”, and returned all states that match that string:

First, let’s start by creating the MySQL database that we will use. Here is the SQL code to create the database, which is named “states”. The table will contain the state name, the state abbreviation, and an ID number.

CREATE TABLE `states` ( `id` int(5) NOT NULL AUTO_INCREMENT, `state` varchar(50) NOT NULL, `abbrev` char(2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Next, you will want to import values into the database. You could do this by importing a .csv file containing this information into MySQL, but to make it quicker for this example, here is the SQL:

insert into states (state, abbrev) values ('Alabama', 'AL'); insert into states (state, abbrev) values ('Alaska', 'AK'); insert into states (state, abbrev) values ('Arizona', 'AZ'); insert into states (state, abbrev) values ('Arkansas', 'AR'); insert into states (state, abbrev) values ('California', 'CA'); insert into states (state, abbrev) values ('Colorado', 'CO'); insert into states (state, abbrev) values ('Connecticut', 'CT'); insert into states (state, abbrev) values ('Delaware', 'DE'); insert into states (state, abbrev) values ('District of Columbia', 'DC'); insert into states (state, abbrev) values ('Florida', 'FL'); insert into states (state, abbrev) values ('Georgia', 'GA'); insert into states (state, abbrev) values ('Hawaii', 'HI'); insert into states (state, abbrev) values ('Idaho', 'ID'); insert into states (state, abbrev) values ('Illinois', 'IL'); insert into states (state, abbrev) values ('Indiana', 'IN'); insert into states (state, abbrev) values ('Iowa', 'IA'); insert into states (state, abbrev) values ('Kansas', 'KS'); insert into states (state, abbrev) values ('Kentucky', 'KY'); insert into states (state, abbrev) values ('Louisiana', 'LA'); insert into states (state, abbrev) values ('Maine', 'ME'); insert into states (state, abbrev) values ('Maryland', 'MD'); insert into states (state, abbrev) values ('Massachusetts', 'MA'); insert into states (state, abbrev) values ('Michigan', 'MI'); insert into states (state, abbrev) values ('Minnesota', 'MN'); insert into states (state, abbrev) values ('Mississippi', 'MS'); insert into states (state, abbrev) values ('Missouri', 'MO'); insert into states (state, abbrev) values ('Montana', 'MT'); insert into states (state, abbrev) values ('Nebraska', 'NE'); insert into states (state, abbrev) values ('Nevada', 'NV'); insert into states (state, abbrev) values ('New Hampshire', 'NH'); insert into states (state, abbrev) values ('New Jersey', 'NJ'); insert into states (state, abbrev) values ('New Mexico', 'NM'); insert into states (state, abbrev) values ('New York', 'NY'); insert into states (state, abbrev) values ('North Carolina', 'NC'); insert into states (state, abbrev) values ('North Dakota', 'ND'); insert into states (state, abbrev) values ('Ohio', 'OH'); insert into states (state, abbrev) values ('Oklahoma', 'OK'); insert into states (state, abbrev) values ('Oregon', 'OR'); insert into states (state, abbrev) values ('Pennsylvania', 'PA'); insert into states (state, abbrev) values ('Rhode Island', 'RI'); insert into states (state, abbrev) values ('South Carolina', 'SC'); insert into states (state, abbrev) values ('South Dakota', 'SD'); insert into states (state, abbrev) values ('Tennessee', 'TN'); insert into states (state, abbrev) values ('Texas', 'TX'); insert into states (state, abbrev) values ('Utah', 'UT'); insert into states (state, abbrev) values ('Vermont', 'VT'); insert into states (state, abbrev) values ('Virginia', 'VA'); insert into states (state, abbrev) values ('Washington', 'WA'); insert into states (state, abbrev) values ('West Virginia', 'WV'); insert into states (state, abbrev) values ('Wisconsin', 'WI'); insert into states (state, abbrev) values ('Wyoming', 'WY'); insert into states (state, abbrev) values ('American Samoa'); insert into states (state, abbrev) values ('Guam', 'GU'); insert into states (state, abbrev) values ('Northern Mariana Islands', 'MP'); insert into states (state, abbrev) values ('Puerto Rico', 'PR');

Now that we have the database, we will need to create the Perl script. Here is the Perl script, and you will need to change the database variables to match your system:

#!/usr/bin/perl -w use CGI; use DBI; use DBD::mysql; use JSON; # print the http header specifically for json print "Content-type: application/json; charset=iso-8859-1\n\n"; # your database variables my $database = "scripting_mysql"; my $host = "192.168.1.2"; my $port = "3306"; my $tablename = "states"; my $user = "root"; my $pass = "root_password"; my $cgi = CGI->new(); my $term = $cgi->param('term'); # mysql connection information $dsn = "dbi:mysql:$database:$host:$port"; # open the database connection $connect = DBI->connect($dsn, $user, $pass) || die print "Can't connect - error..."; # prepare the query $query = $connect->prepare(qq{select id, state AS value, abbrev FROM states where state like ?;}); # execute the query $query->execute('%'.$term.'%'); # obtain the results while ( my $row = $query->fetchrow_hashref ){ push @query_output, $row; } # close the database connection $connect->disconnect(); # print the json output to be returned to the HTML page print JSON::to_json(\@query_output);

For this example, the Perl script should be named states.pl. When executed from the jQuery script in the web page, it will perform a search on the database where the search criteria is in a “like” statement, with a percent sign on both sides of the search term, like this:

# execute the query $query->execute('%'.$term.'%');

You might want to change this query if you have a long data set where entering a few letters would bring up a result that isn’t what the user would probably want. Here is an example of the same form where we are asking the user to enter their state name, and they enter “min” for Minnesota, but it also brings up Wyoming:

If you want to only search for the beginning of the word, remove the first percent sign, like this:

# execute the query $query->execute($term.'%');

This would remove Wyoming from being populated as a choice:

Lastly, here is a simple web page that uses jQuery. We are using the jQuery hosted by Google, and we are using one of their css templates. You may delete their css template and use your own. Also, you will need to change the path of your Perl script on this line:

source: "http://192.168.1.2/cgi-bin/mysql/jquery/state.pl"

And here is the HTML:

<html> <link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jQueryui/1.8.16/themes/base/jQuery-ui.css"> <!-- source = http://code.google.com/apis/libraries/devguide.html#jQuery --> <script src="https://ajax.googleapis.com/ajax/libs/jQuery/1.6.4/jQuery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jQueryui/1.8.16/jQuery-ui.min.js"></script> <!-- Here is the jQuery code --> <script>    $(function(get_states) {     $('#abbrev').val("");     $("#state").autocomplete({     source: "http://192.168.1.2/cgi-bin/mysql/jQuery/state.pl",     minLength: 3,     select: function(event, ui) {      $('#state_id').val(ui.item.id);      $('#abbrev').val(ui.item.abbrev);     }     });    }); </script> <!-- The HTML is simplistic for the example: --> <form method="post"> <table> <tr><td><p class="ui-widget" ><label for="state"/>State: </label></td> <td><input type="text" id="state" name="state" size=30/> </td> <td><input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/></td></tr> </table> <input type="hidden" id="id" name="id" /> <p><input type="submit" name="submit" value="Submit" /></p> </form> <!-- This section will populate the form field with a list of matching states --> <script>   $("#auto_populate_field").submit(function(get_states){    $("#submitted").html("State: " + $("#state").val() + "<br />State Abbreviation: " + $("#abbrev").val() + "<br />State ID: " + $("#state_id").val());    return false;   }); </script>

In this example, once the user starts typing past three characters, a drop-down menu will appear with choices that match the letters being typed. You can decrease/increase the total number of characters that the user will need to type before it queries the database. This is done via this line in the jQuery code:

minLength: 3,

Every time that the person types a character past the initial three characters, the jQuery script will query the MySQL database. This needs to be taken into consideration if you will have a lot of traffic on the pages where you are using jQuery.

The Perl script returns three values in json output – the id, state and abbreviation fields. The values of these fields are place in the text boxes by the autocomplete jQuery function.

In this example, the abbreviation for the state field is returned and placed in the “abbrev” field in the form. The value for the state id is also returned, but since the id field is a hidden field in the form, you won’t see the output/value – but it will be stored in the form.

If you want to see the value of the state id, simply change your web form to look like this:

<form method="post"> <table> <tr><td><p class="ui-widget" style="textalign:right;"><label for="state"/>State: </label></td> <td><input type="text" id="state" name="state" size=30/> </td> <td><input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/></td> <td><input type="text" size=4 id="state_id" name="state_id" /></td> </tr> </table> <p><input type="submit" name="submit" value="Submit" /></p> </form>

You can now see the third field which will contain the state id:

When you enter “min” and then click on Minnesota, you will see the state ID (24) in the third field:

You could also use this to auto-populate city and state fields after someone enters their zip code. Or you could even use this to auto-populate the shipping costs for an online order (I might tackle this one in a future post). Hopefully you will be able to copy and paste this example and have a working jQuery form web page example in just a few minutes.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.

Advertisement

Using Perl, Cookies and MySQL to Record Web Site Visitor’s Data

I have built several web sites and applications in the past, and I have to admit that I am a data junkie. If there is user data or any other data to be captured when someone uses my application or visits a web page, then I like to capture it (I can always delete it later). Of course, you can use a third-party Javascript plug-in for web site analytics (which I also use), but I like to gather information myself.

Most of the pages that I have written use Perl – even for the home page when possible (yes, I am “old school”, and my lack of using modern Perl code is evident). Each time a user clicks to go to another page, I like to capture as much information as possible about their visit.

Perl, PHP and other scripting languages have ways for you to capture the information about your web page visitor. For Perl, this simple script will tell you a lot of information the visitor and your server. In order for this script to work, you have to call it from a web browser.

#!/usr/bin/perl -w use strict; use CGI qw(:standard); use CGI::Carp qw(warningsToBrowser fatalsToBrowser); print header; print start_html("Environment"); foreach my $key (sort(keys(%ENV))) { print "$key = $ENV{$key} \n"; } print end_html;

I took the above script and placed it in my webserver’s cgi-bin directory, made it executable, and opened it in my browser. I added a “?test=1234567” at the end of the script so that you could see the value of the REQUEST_URI variable.

Here is the web output:

DOCUMENT_ROOT = /Library/WebServer/Documents GATEWAY_INTERFACE = CGI/1.1 HTTP_ACCEPT = text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8 HTTP_ACCEPT_CHARSET = ISO-8859-1,utf-8;q=0.7,*;q=0.7 HTTP_ACCEPT_ENCODING = gzip, deflate HTTP_ACCEPT_LANGUAGE = en-us,en;q=0.5 HTTP_CONNECTION = keep-alive HTTP_COOKIE = HTTP_HOST = 192.168.1.2 HTTP_USER_AGENT = Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv:7.0.1) Gecko/20100101 Firefox/7.0.1 PATH = /usr/bin:/bin:/usr/sbin:/sbin QUERY_STRING = test=1234567 REMOTE_ADDR = 192.168.1.5 REMOTE_PORT = 60398 REQUEST_METHOD = GET REQUEST_URI = /cgi-bin/mysql/perl_env.pl?test=1234567 SCRIPT_FILENAME = /Library/WebServer/cgi-bin/mysql/perl_env.pl SCRIPT_NAME = /cgi-bin/mysql/perl_env.pl SERVER_ADDR = 192.168.1.2 SERVER_ADMIN = email_address@scriptingmysql.com SERVER_NAME = 192.168.1.2 SERVER_PORT = 80 SERVER_PROTOCOL = HTTP/1.1 SERVER_SIGNATURE = SERVER_SOFTWARE = Apache/2.2.17 (Unix) mod_ssl/2.2.17 OpenSSL/0.9.7l DAV/2 PHP/5.2.15

This is a lot of information, and you probably don’t need or want most of it. You will want to select information that you can actually or potentially use. For example, if you want to capture their computer info, HTTP referral and remote IP address, in your Perl script. you would create variables in your Perl script like this:

$HTTP_USER_AGENT = $ENV{'HTTP_USER_AGENT'}; $REMOTE_ADDR = $ENV{'REMOTE_ADDR'}; $HTTP_REFERER = $ENV{'HTTP_REFERER'};

You can then insert this information into your MySQL database, along with an “action” of what they are doing on the page. I create an activity table to record what the user is doing on various web pages. If they are visiting your dashboard page, you might set their “activity” to “Dashboard Visit”. I also include a date and time stamp. A sample activity table might look like this:

CREATE TABLE `activity` ( `Customer_Serial_Number` varchar(15) NOT NULL, `Action` varchar(50) NOT NULL, `Date_Time` datetime NOT NULL, `Info` varchar(50) NOT NULL, `Notes` varchar(100) NOT NULL, `HTTP_USER_AGENT` varchar(150) NOT NULL `REMOTE_ADDR` varchar(15) NOT NULL `HTTP_REFERER` varchar(300) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

If the web site requires a user name and password to login, I also use cookies – which is usually how I retrieve the Customer_Serial_Number and/or any other personal information that they have provided to me. Or, if they fill out a “contact us” form, you can save their name and information in a cookie to be retrieved when they visit your web site later. Just be sure to set the cookie expiration date to a date in the future, or to have the cookie never expire.

Here is a quick way to create a cookie in Perl. In this example, we are creating two cookies, Customer Serial Number and Customer Email, both of which we have retrieved from MySQL after they logged into the site:

# ===========    # set cookie for Customer Serial Number    # ===========    $customer_serial_number = CGI::Cookie->new(-name => "CustSN",       -value => $Customer_Serial_Number,       -expires => "+15m",       -domain => ".mydomain.com",       -path => "/"    );    # ===========    # set cookie for Customer Email    # ===========    $Email = CGI::Cookie->new(-name => "CustE",       -value => "$Customer_Email_Address",       -expires => "+1y",       -domain => ".mydomain.com",       -path => "/"    ); # create the cookie for Customer Serial Number and Customer Email $q = new CGI; print $q->header(-cookie => [$customer_serial_number, $Email] );

Here are the definitions for the cookie variables (information from http://perldoc.perl.org/CGI/Cookie.html.

CSN - the name of the cookie. You will use this name to retrieve the cookie later. value - the value of the cookie - the value can be a scalar, an array reference, or a hash reference. expires - how long you want the cookie to be available. Here are some settings:    - Cookie expires 10 seconds from now +10s    - Cookie expires 10 minutes from now +10m    - Cookie expires 10 hours from now +10h    - Cookie expires 10 days from now +10d    - Cookie expires 10 months from now +10M    - Cookie expires 10 years from now +10y    - to make it expire immediately, set it to a negative number, such as: -10M domain - points to a domain name or to a fully qualified host name. If not specified, the cookie will be returned only to the web server that created it. path - points to a partial URL on the current server. The cookie will be returned to all URLs beginning with the specified path. If not specified, it defaults to '/', which returns the cookie to all pages at your site.

There are a couple of other variables that you can use, so refer to the Perl cookie page for more information.

Deciding which information that you want to keep is up to you. Then, you can simply create a MySQL table to store this information. When a user registers on your site, you can store their email address in a cookie that never expires, so the next time they login, you can auto-populate the login form with their email address.

You can also redirect mobile users to a web page that is specific to their screen size. For example, when I connect to this script with my iPhone, I can tell that the connection is from an iPhone via the HTTP_USER_AGENT:

HTTP_USER_AGENT = Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_2_10 like Mac OS X; en-us) AppleWebKit/533.17.9 (KHTML, like Gecko) Version/5.0.2 Mobile/8E600 Safari/6533.18.5

However, my iPhone’s remote IP address is almost useless:

REMOTE_ADDR = 10.1.10.10

So, your Perl script can then detect that this user is on an iPhone, and then it can redirect them with a meta refresh tag to a version of the web page specifically designed for the iPhone screen resolution.

One web site that I designed required a user to login to get past the home screen. Once they logged in, I would grab their serial number from the MySQL database, and then create a cookie with the serial number value. With each page they visited, I would send a few bits of information to my MySQL visitor database.

Here is part of the Perl script that I would use to record customer information: (information on inserting data into MySQL via Perl may be found in an earlier post).

#!/usr/bin/perl -w use CGI qw(:standard); use CGI::Carp qw(warningsToBrowser fatalsToBrowser); # ---------------------------------------------------------------------------------- # grab the environment variables # ---------------------------------------------------------------------------------- $HTTP_USER_AGENT = $ENV{'HTTP_USER_AGENT'}; $REMOTE_ADDR = $ENV{'REMOTE_ADDR'}; # ---------------------------------------------------------------------------------- # fetch the cookie for the Customer Serial Number # ---------------------------------------------------------------------------------- %cookies = CGI::Cookie->fetch; if ($cookies{CustSN}) { $Customer_Serial_Name = $cookies{CustSN}->value; } # ---------------------------------------------------------------------------------- # grab date and time # ---------------------------------------------------------------------------------- my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime time; $year = $year + 1900; $mon = $mon + 1; # add a zero if the value is less than 10 if ($sec < 10) { $sec = "0$sec"; } if ($min < 10) { $min = "0$min"; } if ($hour < 10) { $hour = "0$hour"; } if ($mday < 10) { $mday = "0$mday"; } if ($mon < 10) { $mon = "0$mon"; } if ($year < 10) { $year = "0$year"; } $DateTime = "$year-$mon-$mday $hour:$min:$sec"; # --------------------------------------------------------------------------------- # insert customer activity into database # --------------------------------------------------------------------------------- $Action = "Dashboard Visit"; $dbh = ConnectToMySql($Database); $query = "insert into Activity (Customer_Serial_Number, Action, Date_Time, HTTP_USER_AGENT, REMOTE_ADDR) values (?,?,?,?,?)"; $sth = $dbh->prepare($query); $sth->execute('$Customer_Serial_Number', '$Action', '$DateTime', '$HTTP_USER_AGENT', '$REMOTE_ADDR'); # --------------------------------------------------------------------------------- # ---------------------------------------------------------------------------------

Once you have enough customer data, you can figure out the paths that your customers are taking when visiting your site. Do they login and go straight to the forum page? Or if you have a web store, do they go straight to the clearance page? Or are they logging in and then doing nothing? If a customer never visits a certain page, what can you do to drive them to visit that page? Third-party tools can provide good overall web site analytical information, but nothing beats being able to see one particular customer’s activity on your own.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.

Using Java to Connect to MySQL Enterprise Monitor with Plugin for Connector/J

If you use MySQL for any number of databases, and if you are not using the MySQL Enterprise Monitor (MEM) to manage your databases, then you are missing out on a great DBA tool.

From http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-introduction.html:

“MySQL Enterprise Monitor is a companion product for MySQL Server that monitors your MySQL instances, notifies you of potential issues and problems, and advises you how to fix the issues. MySQL Enterprise Monitor can monitor all kinds of configurations, from a single MySQL instance that is important to your business, all the way up to a huge farm of database server machines powering a busy web site. “

The query analyzer function of the Enterprise Monitor helps you to analyze/tune your queries. It can tell you which queries are being run the most often, which ones are taking the longest to run, it can even help you find queries that are returning a zero result set, and other query “violators”.

But the caveat is that in order to use the query analyzer function, you have to redirect your MySQL traffic through a proxy server (configured during the MEM installation). Using the proxy server is an additional step that might be acceptible for testing on development servers, but it might not be a great idea in a production environment.

However, if you are using Java for your database connections, you can bypass the proxy server and send your query information directly to the MEM Service Manager. This is accomplished with the MySQL Enterprise Plugin for Connector/J.

From http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-qanal-using-cj.html:

“The MySQL Enterprise Plugin for Connector/J lets you use the Query Analyzer to monitor MySQL queries from any application using the Connector/J JDBC driver. As described in Section 3.2, “Query Analyzer User Interface“, the Query Analyzer can help you locate queries that are inefficient or slow. Tuning such queries helps to shorten load times for web pages, and improves overall system responsiveness and scalability.”

As stated, the information about the queries is sent directly to the MySQL Enterprise Service Manager. Once you install the MySQL Enterprise Plugin for Connector/J and tweak your Java database connection string, query analysis becomes available for your Java applications. And, the Enterprise Monitor can show you the location of the “offensive” Java code. Here is an example, with the code location highlighted in blue:

There are a few things that you need to do before you can use this plugin. The prerequisites are:

– MySQL Connector/J version 5.1.12 or later.
– JDK-1.5.0 or later.
– MySQL Enterprise Service Manager version 2.2 or later.
– The MySQL instance that the application uses must be monitored by a MySQL Enterprise monitoring agent. The mysql.inventory table must be accessible to the configured user for the Connector/J connection to get the UUID of the monitored server.
– Apache Commons logging in the CLASSPATH of the application being analyzed. If you are not already using Commons Logging, modify the application’s CLASSPATH as described in the following section to point to the JAR file bundled with the MySQL Enterprise Monitor product.

I don’t write Java code, but I wanted to be able to test this functionality. I found an example script on MySQL’s web site, and so I was able to modify it to send a test query to my installation of MEM.

I did run into one problem – I wasn’t setting my CLASSPATH variable correctly. To solve this problem, I edited my .profile file for the root user, and added the following JAR file locations to my CLASSPATH: (the colors are added for clarity)

export set CLASSPATH=/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/mysql-connector-java-commercial-5.1.17-bin.jar:/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/c-java-mysql-enterprise-plugin-1.0.0.61.jar:/Applications/mysql/enterprise/monitor/apache-tomcat/webapps/ROOT/WEB-INF/lib/required/commons-logging-1.1.1.jar:

I am running MySQL on a Mac Pro Server, so you will need to modify the file paths in your CLASSPATH to match your system.

After you have completed the prerequisites, you are ready to see if you can connect to the Service Manager with a query via Java. Here is a sample script that you can use (you will need to modify it your system parameters):

import java.io.IOException; import java.sql.*; // Notice, do not import com.mysql.jdbc.* // or you will have problems! public class LoadDriver {    public static void main(String[] args) {          try {             // The newInstance() call is a work around for some             // broken Java implementations             Class.forName("com.mysql.jdbc.Driver").newInstance();             Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.1.2/database_name?"+                "user=root&password=root_password"+                "&statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters"+                "&serviceManagerUrl=http://192.168.1.2:18080/"+                "&serviceManagerUser=agent"+                "&serviceManagerPassword=agent_password");             //Get a Statement object                Statement stmt = conn.createStatement();                ResultSet rs = stmt.executeQuery("SELECT sleep(120)");                System.out.println("Closing connection");                       conn.close();                } catch (Exception ex) {                // handle the error                ex.printStackTrace();          }       } }

You will need to compile the script before running it. Here are the results from compiling and then executing the script: (since you are doing a SELECT SLEEP(120), the script will “sleep” for 120 seconds before finishing.)

macserver01:java root# javac LoadDriver.java macserver01:java root# java LoadDriver Fri Oct 14 14:08:57 EDT 2011 DEBUG: Using direct result set size methods. Fri Oct 14 14:08:57 EDT 2011 DEBUG: Using batch-aware prepared sql extractor. Fri Oct 14 14:08:57 EDT 2011 INFO: MySQL Enterprise Plugin for Connector/J version 1.0.0.61 started for MySQL instance 53e71576-026c-4798-961b-0025efc37db2. Fri Oct 14 14:08:57 EDT 2011 DEBUG: GET http://192.168.1.2:18080/v2/rest/instance/mysql/StatementAnalysisSupport/53e71576-026c-4798-961b-0025efc37db2 HTTP/1.1 200 OK Server: Apache-Coyote/1.1 Content-Type: application/json Transfer-Encoding: chunked Date: Fri, 14 Oct 2011 18:08:57 GMT {     "name": "53e71576-026c-4798-961b-0025efc37db2",     "parent": "/instance/mysql/Server/53e71576-026c-4798-961b-0025efc37db2",     "values":     {         "auto_explain_min_exec_time_ms": 1,         "capture_examples": "true",         "capture_explain": "true",         "enabled": "true",         "frequency": null } } Fri Oct 14 14:08:57 EDT 2011 DEBUG: Configuration is: com.mysql.etools.jdbc.StatementAnalysisConfiguration@a14e84[captureExamples=true,captureExplains=true,enabled=true,explainPlanThresholdMicros=1000,usingDefaults=false] Fri Oct 14 14:08:57 EDT 2011 DEBUG: GET http://192.168.1.2:18080/v2/rest/instance/mysql/StatementAnalysisSupport/53e71576-026c-4798-961b-0025efc37db2 HTTP/1.1 200 OK Server: Apache-Coyote/1.1 Content-Type: application/json Transfer-Encoding: chunked Date: Fri, 14 Oct 2011 18:08:57 GMT {     "name": "53e71576-026c-4798-961b-0025efc37db2",     "parent": "/instance/mysql/Server/53e71576-026c-4798-961b-0025efc37db2",     "values": {         "auto_explain_min_exec_time_ms": 1,         "capture_examples": "true",         "capture_explain": "true",         "enabled": "true",         "frequency": null } } Fri Oct 14 14:08:57 EDT 2011 DEBUG: Configuration is: com.mysql.etools.jdbc.StatementAnalysisConfiguration@a14e84[captureExamples=true,captureExplains=true,enabled=true,explainPlanThresholdMicros=1000,usingDefaults=false]

And here are the results as show in the Query Analyzer tab of MySQL Enterprise Monitor:

By clicking on the SELECT sleep(?) link, I can then take a look at the query information:

For more complex queries, this tool really provides a wealth of information to help you tune your queries and increase the performance of your MySQL databases. MySQL Enterprise Monitor has a wealth of great features. For more information, see http://dev.mysql.com/doc/mysql-monitor/2.3/en/index.html.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.

Steve Jobs, you will be missed. Greatly.

I remember my first computer. It was a TI-99/4A. I bought it back in 1982 (I think), and it cost around $300. The entire computer fit inside what looked like a really thick keyboard. It had a slot on the right for cartridges, and I had a cassette tape drive that I used for backing up the BASIC computer programs that I wrote. I thought that it was a great computer at the time, but I really didn’t have anything to compare it to. The games were the best part of the computer (really the only fun part – my BASIC skills were lacking). And even though the games were fairly lame by even 1982 standards, but they were still plenty of fun to play.

I also remember when I saw a Macintosh for the first time. I had followed Apple for some time, but I had never had the opportunity to actually see a Macintosh. I think it was in 1987 or 1988, when I was a student at the University of Georgia. I believe it was a Mac SE, and it had one megabyte of RAM and a twenty-megabyte hard drive. Up to that point, I had only played around with MS-DOS, and that was on a computer at work. I was amazed at the graphical interface. The mouse was definitely cool. I had to have one, but at that time, they were a couple thousand dollars (at least), and I was a “poor college student”. I spent a lot of time at the computer lab, and I waited.

In 1989, I landed my first job out of college, and one of the first things on my “to-buy” list was a Mac. I just didn’t WANT a Mac – I NEEDED a Mac. So, with a loan from the bank (yes, we took out a loan), my brother and I bought two Mac SE’s, and an HP Deskwriter dot-matrix printer that cost $1,195 (we had to share the printer). I remember opening the box and putting the computer on my desk – and I was thrilled and amazed. With my 2400-baud modem, I could connect to the Unix computers at work. Life was grand. The first major improvement that I made was to upgrade the RAM to four megabytes. I think the upgrade was a whopping $400. And then, about a year later, the hard drive died (one of the only problems that I have ever had with a Mac). So, I upgraded to a forty-megabyte drive for $500. I remember thinking that I will wouldn’t need any more additional hard drive space for a really long time. Besides, I had several hundred 3.5 inch floppies. I later upgraded to a faster processor and bought an internal video card that let me use a huge monitor at the same time (I think it was a 19″ monitor – but it was a grayscale monitor). But having two monitors was cool – very cool – even though a color monitor still eluded me.

And then, about every year or two, I would upgrade to a newer model. I had an SE/30, LC, IIsi, Quadra 840 AV (which I used to edit my first videos), a Power Mac G3, G4 and then several iMac’s. I remember staying up all night playing SIM City on the LC and playing on Prodigy. I had other computers in the house – a Windows machine and a couple of Unix boxes. But I was all-Mac. I remember my first web cam, which I set up to record people driving my the house. The motion-detection software would record a few grayscale photos when cars would drive by. I found out when the trash was picked up and when the mail was delivered. Technology was amazing.

I followed all of the Apple news. Even before I bought my first Mac, I remember when Steve was booted from Apple. I winced as I remember watching Scully and Amelio take Apple to the brink of bankruptcy. When I traveled to the San Francisco area in the mid-1990’s on a business trip, I took some time to drive to Cupertino to the Apple Campus. While I would have loved to have been an Apple employee back then, moving to California was pretty much out of the question. But, I wanted to at least state that I had applied for a job there – which I did. I wasn’t hired, but at least I tried, at least I did that. And I remember when Steve Jobs returned. Mac lovers now had hope. The love affair was rekindled – and it had the opportunity to be much, much stronger than before. Steve would fix it. He had to fix it. And he did.

When I started a company a few years ago, the office was filled with Intel iMac’s and a Mac Pro Server (running MySQL of course!). My wife has a MacBook Pro, my kids have Mac Mini’s, and I just recently purchased a MacBook Pro. We even have an iMac in the kitchen. My Mom has an iMac and my Dad has my Mom’s old G4 (when she upgrades, he gets the hand-me-downs). My sister recently “upgraded” from Windows to an iMac. I gave my niece a MacBook when she started graphics arts school. (My brother is the only current hold-out, but he is on the way to converting back to the Mac). I think you get my point – I really love Macintosh computers and I enjoy “converting people” to the cause.

Needless to say, my life has been touched in some way throughout the past 20-plus years by the Macintosh. I have done everything from creating proposals, creating and listening to music, to editing my youngest son’s birth video (yes, it is rated G) – all on a Macintosh. It is even where I store my tens of thousands of digital photos. I have designed web sites, created software products and ran a business on Macintosh computers. My wife produced a video for my 40th birthday party on her Mac. And, I can’t forget my first iPod, my first iPod Touch, and my iPhones – versions 3 and 4 (I am holding out for the iPad 3). Heck, I even still use the old AppleWorks application every once in a while.

Even though the Mac isn’t and has never been the most dominant “PC” in the marketplace – if you ask anyone that uses a Mac if they would ever use anything else, the vast majority of them would answer with a strong and resounding “no” (or even HELL NO). Even when Apple stock was trading at less than five bucks per share, and it seemed like it was going out of business, I remember telling friends that Apple would pull through (heck – it HAD to pull through). I couldn’t imagine having to use a Windows machine.

And so, I owe a lot of my memories over the past twenty years to Apple Computer, and of course, to Steve Jobs. Steve wouldn’t just manufacture a computer, he would design a work of art (albeit a more expensive work of art). And the operating system was, well, extremely easy to use. Having an Apple computer is tantamount to having a love affair – a comment that I usually don’t hear from users of that other operating system. Steve’s vision for what a computer should do for the user and how the user interacts with the computer was pure genius. And being an ex-Unix admin, once the Mac OS switched to a Unix-based OS, I was even more smitten.

When I had heard the Steve Jobs had passed, I was sad. Well, maybe a bit more than sad. I was never really a big fan of Steve Jobs as a person, mainly because I really didn’t know a lot about him personally – I just loved his products, his vision. When he would announce a new product, you could tell that he was really, really proud of what he had achieved. He wasn’t just up on stage hawking widgets, he was introducing something truly historic. Something that would cause other companies to scurry and to try and play catch-up (they rarely succeeded). I can only hope that the team that he has placed at Apple will continue his legacy of producing innovation at the same level of genius that he has cultivated over the years. If not, then I will also be sad for my two kids, as they won’t get to experience the joy of using a Macintosh as I have. Steve Jobs, you will be missed. Greatly.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.