MySQL Cookbook (2007)

Appendix B. Executing Programs from the Command Line

As you work through this book, you’ll run many examples using the mysql client program, and there are lots of programs in the recipes distribution for you to try as you read. And of course one purpose of the book is to enable you to write your own MySQL-based programs. Consequently, you’ll often need to execute programs at the command line—that is, at the prompt of your shell or command interpreter. For best use of this book, you should be able to run mysql easily (by entering just its name), and you should be able to execute programs from the recipes distribution or that you write yourself. To accomplish those goals, it’s important that your PATH environment variable be set correctly, and that you know how to make programs executable. This appendix shows how to do those things; if you already know how, you can skip it.

Setting Environment Variables

An environment variable has a value that can be accessed by programs that you run. By setting environment variables, you modify your operating environment. One such variable is PATH, which is used by your command interpreter to determine which directories to search when it looks for programs such as mysql that you tell it to execute. If your PATH is set correctly, you can invoke programs easily no matter what your current directory is. If PATH is not set correctly, your command interpreter will not find them. For example, if the PATH value does not include the directory where mysql is installed, a “command not found” error may occur if you attempt to run mysql by entering its name. You must run it either by specifying its full pathname, or by changing location into the directory where it is installed. Both strategies are unpleasant, and more so with repetition. It’s much better to set your PATH value to name the directories containing the programs you want to use.

Other environment variables are important in other contexts. For example, if you run Perl or Ruby scripts that use module files that you’ve installed, you may need to set the PERL5LIB or RUBYLIB variable to tell Perl or Ruby where to find those modules. For Java, the JAVA_HOME variable should be set to indicate the location of your Java installation, and CLASSPATH should be set to the list of libraries and class files that your Java programs need.

The following discussion describes how to set environment variables. The examples demonstrate how to make it easier to run MySQL programs by modifying your PATH setting to add the directory where MySQL programs are installed. However, the discussion applies to other environment variables as well because the variable-setting syntax is the same.

Here are some general principles to keep in mind regarding environment variables:

§ It’s possible to set an environment variable from the command line, but the variable will retain its value only until you exit the command interpreter. To cause the setting to take effect permanently, set it in the appropriate shell startup file on Unix or by using the Environment Variables interface on Windows (this can be found on the System Control Panel under Advanced). The change takes effect for subsequent invocations of your command interpreter (for example, your next login on Unix, or the next console window that you open on Windows).

§ Some variables (PATH among them) have a value that lists the pathnames for one or more directories. Under Unix, it’s conventional to separate the pathnames using the colon character (:). Under Windows, pathnames can contain colons, so the separator is the semicolon character (;).

§ You can check your current environment variable settings by executing an env or printenv command on Unix, or a set command in a console window on Windows.

Setting the PATH Variable on Unix

Your choice of command interpreter determines the syntax for setting environment variables. In addition, for settings that you put in a startup file, the file to use is interpreter-specific. The following table shows typical startup files for commonly used Unix command interpreters. If you’ve never looked through your command interpreter’s startup files, it’s a good idea to do so to familiarize yourself with their contents.

The examples assume that MySQL programs are installed in the /usr/local/mysql/bin directory and that you want to add that directory to your existing PATH value. That is, the examples assume that there is already a PATH setting in one of your startup files. If you have no PATH setting currently, add the appropriate line or lines to one of the files.

§ For a shell in the Bourne shell family (sh, bash, ksh), look in your startup files for a line that sets and exports the PATH variable:

export PATH=/bin:/usr/bin:/usr/local/bin

Change the setting to add the appropriate directory:

export PATH=/usr/local/mysql/bin:/bin:/usr/bin:/usr/local/bin

The assignment and the export might be on separate lines:

PATH=/bin:/usr/bin:/usr/local/bin

export PATH

Change the setting to this:

PATH=/usr/local/mysql/bin:/bin:/usr/bin:/usr/local/bin

export PATH

§ For a shell in the C shell family (csh, tcsh), look in your startup files for a setenv PATH command:

setenv PATH /bin:/usr/bin:/usr/local/bin

Change the setting to add the appropriate directory:

setenv PATH /usr/local/mysql/bin:/bin:/usr/bin:/usr/local/bin

It’s also possible that your path will be set with a set path command, which uses different syntax:

set path = (/usr/local/mysql/bin /bin /usr/bin /usr/local/bin)

path is not an environment variable, but for C shells, setting path also sets PATH and vice versa.

Adjust the instructions for the pathname actually used on your system.

Setting the PATH Variable on Windows

To set environment variables on Windows, click the Start Menu, right-click My Computer → Properties → Advanced → Environment Variables. You should see a window that enables you to define environment variables or change the values of existing variables. For example, your PATHvariable might have a value like this:

C:\WINDOWS;C:\WINDOWS\COMMAND

To make it easier to run MySQL programs such as mysql, change the value to include the directory where those programs are installed. Adding a directory of C:\Program Files\MySQL\MySQL Server 5.0\bin results in a PATH setting that should look like this:

C:\Program Files\MySQL\MySQL Server 5.0\bin;C:\WINDOWS;C:\WINDOWS\COMMAND

Executing Programs

This section describes how to execute programs from the command line. You can use this information to run programs that you obtain from the recipes distribution or that you write yourself. The first set of instructions applies to scripts written in Perl, Ruby, PHP, or Python. A second set of instructions applies to Java programs.

The example programs can be found in the progdemo directory of the recipes distribution.

Executing Perl, Ruby, PHP, or Python Scripts

The following discussion shows how to execute scripts, using Perl for the examples. However, the principles are similar for Ruby, PHP, and Python scripts.

Begin with an example script named perldemo.pl that consists of a simple print statement:

print "I am a Perl program.\n";

A script-execution method that works on any platform is to invoke the perl program and tell it the name of the script to run:

%perl perldemo.pl

I am a Perl program.

For a script written in another language, invoke the ruby, php, or python program.

It’s also possible to set up a script so that it is directly executable. The procedure is different for Unix and Windows. Both procedures are described here.

On Unix, to make a script directly executable, include a line at the top of the file that begins with #! and that specifies the pathname of the program that should execute the script. Here is a script named perldemo2.pl with a #! line that names the perl program (if perl has a different location on your system, you’ll need to change the pathname):

#!/usr/bin/perl

print "I am a Perl program.\n";

Next, enable the executable access mode with chmod +x:

%chmod +x perldemo2.pl

At this point, the script can be run the same way as its earlier counterpart (that is, you can use perl perldemo2.pl), but now you should also be able to execute it by entering just its name. However, assuming that the script is located in your current directory, your shell might not find it. The shell searches for programs in the directories named in your PATH environment variable, but for security reasons, the search path for Unix shells often is deliberately set not to include the current directory (.). In that case, include a leading path of ./ to explicitly indicate the script’s location:

%./perldemo2.pl

I am a Perl program.

If you install the script by copying it to a directory that is named in your PATH setting, the leading path is unnecessary when you invoke the script.

On Windows, the procedure for making scripts executable is somewhat different. chmod is not used. Instead, programs are treated as executable based on their filename extensions (such as .exe or .bat). Windows allows you to set up filename associations such that a program can be associated with filenames that end with a particular suffix. This means that you can set up associations so that Perl, Ruby, PHP, or Python are used to execute files with names that end in .pl, .rb, .php, or .py, respectively. (In fact, the installer for a given language might even create the association for you.) Then you can invoke a script with a given extension directly from the command line without naming its language interpreter:

C:\>perldemo.pl

I am a Perl program.

No leading path is needed to invoke a script that is located in the current directory because the Windows command interpreter includes that directory in its search path by default.

Keep the preceding principles in mind when you run scripts from within a directory of the recipes distribution. For example, if you are currently located in the metadata directory of the distribution and you want to execute the get_server_version.rb Ruby script, you can do so with either of these commands on Unix:

%ruby get_server_version.rb

% ./get_server_version.rb

On Windows, you can use either of these commands:

C:\>ruby get_server_version.rb

C:\> get_server_version.rb

Compiling and Executing Java Programs

Java programming requires a software development kit (SDK). If a Java SDK is not already installed on your system, obtain one and install it. For Solaris, Linux, and Windows, Java SDKs are available at java.sun.com. For Mac OS X, current versions come with an SDK; for earlier versions that do not, javac and other support needed for building Java applications is included in the Developer Tools distribution available at connect.apple.com.

After verifying that a Java SDK is installed, set the JAVA_HOME environment variable if it is not already set. Its value should be the pathname of the directory where the SDK is installed. If the SDK installation directory is /usr/local/java/jdk on Unix or C:\jdk on Windows, set JAVA_HOME as follows:

§ For a shell in the Bourne shell family (sh, bash, ksh):

export JAVA_HOME=/usr/local/java/jdk

If you are using the original Bourne shell, sh, you may need to split this into two commands:

JAVA_HOME=/usr/local/java/jdk

export JAVA_HOME

§ For a shell in the C shell family (csh, tcsh):

setenv JAVA_HOME=/usr/local/java/jdk

§ For Windows, get to the Environment Variables window as described earlier in “Setting Environment Variables,” and set JAVA_HOME to this value:

C:\jdk

Adjust the instructions for the pathname actually used on your system.

With a Java SDK in place and JAVA_HOME set, you should be able to compile and run Java programs. Here is a sample program:

public class JavaDemo

{

public static void main (String[] args)

{

System.out.println ("I am a Java program.");

}

}

The class statement indicates the program’s name, which in this case is JavaDemo. The name of the file containing the program should match this name and include a .java extension, so the filename for the program is JavaDemo.java. Compile the program using javac:

%javac JavaDemo.java

If you prefer a different Java compiler, just substitute its name. For example, if you’d rather use Jikes, compile the file like this instead:

%jikes JavaDemo.java

The Java compiler generates compiled byte code to produce a class file named JavaDemo.class. Use the java program to run the class file (specified without the .class extension):

%java JavaDemo

I am a Java program.

To compile and run MySQL-based programs written in Java, you’ll need the MySQL Connector/J JDBC driver (Connecting, Selecting a Database, and Disconnecting). If Java cannot find the driver, you’ll need to set your CLASSPATH environment variable. Its value should include at least your current directory (.) and the pathname of the MySQL Connector/J driver. If the driver has a pathname of /usr/local/lib/java/lib/mysql-connector-java-bin.jar (Unix) or C:\Java\lib\mysql-connector-java-bin.jar (Windows), set CLASSPATH as follows:

§ For a shell in the Bourne shell family (sh, bash, ksh):

export CLASSPATH=.:/usr/local/lib/java/lib/mysql-connector-java-bin.jar

§ For a shell in the C shell family (csh, tcsh):

setenv CLASSPATH .:/usr/local/lib/java/lib/mysql-connector-java-bin.jar

§ For Windows, get to the Environment Variables window as described earlier in “Setting Environment Variables,” and set CLASSPATH to this value:

.;C:\Java\lib\mysql-connector-java-bin.jar

Adjust the instructions for the pathname actually used on your system. You might also need to add other class directories or libraries to your CLASSPATH setting. The specifics depend on how your system is set up.