MySQL Developing And Programming Blog

Installing MySQL, Java, and Connector/J 64 Figure 4.2 Testing MySQL. Installing Java Once the MySQL database server is installed, it s time to install Java. You can find the Java software development kit (SDK) at http://java.sun.com/ j2se/1.4.1/download.html. When you get to this page, you see downloads for numerous platforms and options for either the Java Runtime Environment (JRE) or SDK. Be sure to grab the SDK so you will be able to write code with Java. For Windows, you will find an EXE file to download. When the file has finished downloading, double-click on it to launch the installation wizard. Just a few clicks through the wizard is all it takes to install Java on Windows. When the Java installation wizard has finished installing Java, add the path to the /bin directory of the installation to the system PATH environment variable. That way, you will have access to the Java tools from a Windows command prompt. For Linux, you will find both an RPM and a self-extracting BIN file. If you download the RPM file, it will initially include a BIN extension, which you need to remove. Install the RPM with the rpm-I command. If you download the BIN self- extracting file, you need to change the file to have execution permissions with the chmod a+x command. Once permissions are set correctly, just execute the file to install Java. Full instructions for installing the Windows, Linux, and other environments can be found at http://java.sun.com/j2se/1.4.1/install.html if you run into problems. Testing the Java Installation Once Java has been installed, you should test the installation. To do this, create a file called hello.java and add the following code: public class hello { public static void main(String[] args) { System.out.println(”Hello World It Works”); } }

Hint: This post is supported by Gama web hosting php mysql provider

Installing MySQL 63 Windows Installation The MySQL distribution for Windows comes as a zip file and will need to be uncompressed before it can be used. Use WinZip or another tool of your choice to perform the decompression. Once you do, follow these instructions to install the server on a NT/2000/XP box: 1. Log on as the administrator user. 2. Stop the current MySQL if you re performing an upgrade. a. Open a command prompt. b. If MySQL is running as a service, type net stop where is the name of the MySQL server service name (normally the value is mysql). c. If MySQL is running as an application, change to the /bin directory of the MySQL installation and type mysqladmin u root shutdown. 3. If you are changing from the basic MySQL server to the Max version, you need to remove the service. 4. Locate the setup.exe file of the new installation from the uncompressed files. 5. After MySQL is installed, copy one of the configuration files in the installation directory to the root directory, c:/. If you are using the Max version of MySQL, configure the appropriate InnoDB or BDB options in the configuration file. 6. If you want to install the server as a service, type the command -mysqld-max-nt- –install (or –install-manual if you don t want Windows to automatically start the service when the machine boots). If you are installing MySQL on Windows 95, 98, or ME, the server cannot be used as a service and thus you will need to start and stop the server manually. Use the mysqladmin.exe application in the /bin directory to start MySQL. All Other Installations It is beyond the scope of this blog to provide installation instructions for every platform that MySQL supports. If you need to install MySQL on another platform, download the appropriate distribution and refer to http://www.mysql.com/documentation/mysql/bychapter/manual_Installing.html #Installing for complete instructions. Testing the MySQL Installation To determine that MySQL has been installed and is executing correctly, browse to the /bin directory of MySQL and execute the file mysql. You should see information like that shown in Figure 4.2.

Hint: If you are looking for very good and affordable webspace to host and run your tomcat hosting application check Sandzak.com tomcat web hosting provider

Installing MySQL, Java, and Connector/J 62 Figure 4.1 The available MySQL downloads. Looking at the Production MySQL servers, you have two possibilities: MySQL and MySQL-Max. The MySQL download is a basic MySQL server without transaction support table types compiled into the binary. The MySQL-Max download includes support for the BDB table type in some platforms and the InnoDB table type in all platforms. Depending on which Production system you decided to download, you have the option of pulling the version for Windows, Linux, Solaris, and a host of other platforms. If you are downloading for Windows, you automatically get both the standard MySQL distribution as well as MySQL-Max. If you are downloading the Linux version, MySQL recommends using the RPM file for a clean installation. Note that you will have to download a number of Linux files. You should download the server and client programs files to have an operational development system. After you click on the correct platform version, the installation instructions change. Linux Installation For the Linux version of MySQL, you have two different files on your system. One has a name like MySQL-3.23/MySQL-3.23.52-1.i386.rpm, and the other has a name like MySQL-3.23/MySQL-client-3.23.52-1.i386.rpm. Since these are RPM files, they should install without much error on most recent Linux installations. The steps are 1. Install the server: type rpm -i MySQL-VERSION.i386.rpm. 2. Install the client tools: type rpm -i MySQL-client-VERSION.i386.rpm. The installation process places all of the code in /var/lib/mysql. In addition, the process makes entries in rc.d/ to automatically start MySQL when the machine boots.
Note: If you are looking for cheap and quality provider to host and run your java application check Astra java hosting services

CHAPTER 4 Installing MySQL, Java, and Connector/J If you ve made it this far, you are ready to begin the process of integrating MySQL, Java, and Connector/J to build applications and sites that provide your users with a bounty of information. In this chapter, we explain how to install MySQL, Java, and Connector/J on your system. We cover both Linux and Windows, and for the most part, we show the basic installation that works on 99 percent of the environments out there. If these instructions don t work, you will need to turn to the product documentation. Installing MySQL You can find the MySQL database system at http://www.mysql.com under the downloads section of the site. Several different downloads are available, as shown in Figure 4.1. On the right-hand side of the MySQL Web page, you can see two major sections: Production and Development. The Production line of products has been thoroughly tested both within and outside MySQL. An organization can comfortably use the Production products in such an environment and be assured of stability and reliability. The Development line of products has been tested within MySQL with MySQL s own baseline tests but the products aren t at the level of production readiness. As you can see, the 4.0.x line of MySQL is currently in the Development stage and isn t recommended yet for production use. 61

Hint: This post is supported by Gama web hosting php services

Working with MySQL SQL 60 information on MySQL SQL, please refer to the extensive documentation available on www.mysql.com. In the next chapter, we take a complete look at the installation of MySQL, Java, and Connector/J to build a development system to be used throughout the remainder of this blog.

Hint: This post is supported by Gama web hosting php services

What s Next 59 two tables. Finally, our query uses a WHERE clause to further limit the results from the query. In most cases, all of the conditions to use when relating the tables should appear in the ON clause, and final criteria should appear in the WHERE clause. Outer Left/Right Join Another common join is called the left join. In a left join, the first table listed in the query returns all of its rows even if a match doesn t occur within the ON clause. The right join does just the opposite and returns all rows in the table listed with the join. NULL One of the most interesting features of SQL is the notion that NULL is not 0 as in most programming languages. The value NULL stands on its own in SQL and for this reason, a special equality statement is needed to check whether a field contains a NULL value. The statement is IS NULL and IS NOT NULL. We can create SQL that will pull rows if a column s value is NULL. For example: mysql> SELECT acc_id, password FROM acc WHERE password IS NULL; +———+———-+ | acc_id | password | +———+———-+ | 1034067 | NULL | +———+———-+ 1 row in set (0.00 sec) mysql> SELECT acc_id, password FROM acc WHERE password = NULL; Empty set (0.00 sec) In the first SQL query, we are telling the database server to return all rows where the password field value is NULL. There is one such row in the table, and it is displayed. The second SQL query does nearly the same thing, but instead it tries to match the password value equal to NULL no results are found. What s Next So where is MySQL going? Well, the current plan is to introduce new functionality under the 4.0 version in separate segments. Expect to see increments like 4.0.1, 4.0.2, and so forth. Several large additions are planned for 4.1, including subselects and stored procedures. This chapter has attempted to provide a brief but comprehensive introduction to MySQL SQL for those who aren t familiar with it. For more comprehensive

Hint: This post is supported by Gama web hosting php mysql provider

Working with MySQL SQL 58 acc_id of 1034055. The database server will analyze the WHERE clause and look for all of the acc_id values in the table acc that also appear in the acc_add table. If you look back in our examples, you can see that there are two 1034055 values in the acc table. One of them is an active row, and one is an inactive row. Both of these rows will be matched against the two rows in acc_add for a result set having four rows. However, we also included a Logical AND in our WHERE clause to return only those rows where acc_id is equal in both tables and the ts field in acc is 0. This logical AND limits the total row output to two rows. What we have accomplished in this example is a basic join. We have joined two tables by requesting that information be pulled from multiple tables and a condition placed on the values from the tables. Technically this is called an equi-join. Using a Join When developers talk about a join, they typically use the term join without any identifiers. Developers are really referring to a cross-join, a full join, or an inner join. The idea is that all of the rows in one table are crossed with all of the rows in another table. Currently, our acc table has nine rows, and the acc_add table has two rows. If we were to execute a query like the one that follows, we would get a result with 18 rows and all of the columns from both tables. Since this would create a massive table, we won t reproduce it here. MySQL allows the use of the inner join identified to let the reader of a SQL statement know there is a join occurring. Consider the SQL we used earlier with the acc and acc_add tables. To properly write this SQL using a join, we would have mysql> SELECT acc.acc_id, name FROM acc inner join acc_add on acc.acc_id = acc_add.acc_id where acc.ts = 0; +———+———-+ | acc_id | name | +———+———-+ | 1034055 | John Doe | | 1034055 | John Doe | +———+———-+ 2 rows in set (0.00 sec) This SQL has several aspects. The first is the use of the inner join clause. The SQL says we want to pull data FROM one table and join that table with another one called acc_add. The INNER JOIN causes a full join to occur with both tables. After the join, there is an ON clause. The ON clause is used exclusively with the join. This clause tells the system what criteria to use when relating the
Hint: If you are looking for good and high quality web space to host and run your java application check Vision java web hosting services

Introducing MySQL SQL 57 address2 varchar(64), address3 varchar(64), city varchar(64), state varchar(64), zip varchar(10), ts timestamp not NULL, act_ts timestamp, primary key(add_id, ts)); Query OK, 0 rows affected (0.00 sec) Let s now add some data to the table for the account ID 1034055. Notice that the acc_add table requires the acc_id of the account whose address is being added to the table. This column value links the acc table with the acc_add table. The full data added to the table can be found on the code download available at www.wiley.com/compbooks/matthews. In this example, we ve added two rows: mysql> insert into acc_add values(30004, 1034055, ‘John Doe’, ‘4565 Some St’, ‘Suite 4′, NULL,’Chicago’, ‘IL’, ‘21734′, 0, now()); Query OK, 1 row affected (0.00 sec) mysql> insert into acc_add values( 30003, 1034055, ‘John Doe’, ‘123 Any St’, NULL, NULL,’Atlanta’, ‘GA’, ‘38394′, 0, now()); Query OK, 1 row affected (0.00 sec) Now we want to get data from both tables at the same time. For example: mysql> SELECT acc.acc_id, name FROM acc, acc_add WHERE acc.acc_id = acc_add.acc_id and acc.ts = 0; +———+———-+ | acc_id | name | +———+———-+ | 1034055 | John Doe | | 1034055 | John Doe | +———+———-+ 2 rows in set (0.00 sec) In this query, we asked for the values of acc_id (from the acc table) and name (from the acc_add table), but only when the acc_id in both of the tables match and the ts field in the acc table is 0. The result is two rows. Let s look a little more closely at what is occurring in this SQL. First, we are asking for data from two tables at the same time, as seen in the FROM acc, acc_add clause. From those two tables, we want two pieces of data: the acc_id and name. Notice how the acc_id has a table name preceding it. This had to be done in order to tell the database server which table we want the acc_id to be pulled from because it can be found in both of them. Now we want to see data from the tables only when the acc_id is identical between the two tables. At this time, the only data in the acc_add table has an

Hint: This post is supported by Gama php5 hosting services

Working with MySQL SQL 56 mysql> begin; Query OK, 0 rows affected (0.00 sec) Now execute your SQL. Once you have finished, use the command COMMIT or ROLLBACK, depending on your circumstances. Functions/Operators In several places throughout this chapter, we have used a MySQL function in a query. A function is code written by MySQL that aids in the query being used. For example, suppose you want to determine the largest account ID in the acc table. To do this, use the max() function: mysql> SELECT max(acc_id) FROM account; +————-+ | max(acc_id) | +————-+ | 1034546 | +————-+ 1 row in set (0.00 sec) In this query, the maximum value in the acc_id column is returned from the database. MySQL includes a large number of functions too many to list in this chapter. Refer to the MySQL documentation for a listing of all available functions and examples. Joins One of the harder concepts to grasp in the world of databases is the join. Let s begin our discussion by throwing another table into our current database. Right now we have a database called acc that has the following fields: acc_id int username varchar password varchar ts timestamp act_ts timestamp This table doesn t hold much information about the actual owner of the account ID. We need to add another table called acc_add that will hold address information for the account owner. Here s the table definition: mysql> create table acc_add ( add_id int not NULL, acc_id int, name varchar(64), address1 varchar(64),
Hint: If you are looking for good and high quality web space to host and run your java application check Vision java web hosting services

Introducing MySQL SQL 55 Note that the DATA DIRECTORY option in the ALTER TABLE as well as in the CREATE TABLE command is available only on MyISAM tables underMySQL 4.0. Deleting Tables If you are absolutely sure that you want to get rid of a table permanently, use the command DROP TABLE
. Here is a simple example of using DROP TABLE: mysql> create table test (id int); Query OK, 0 rows affected (0.05 sec) mysql> insert into test values(1); Query OK, 1 row affected (0.02 sec) mysql> drop table test; Query OK, 0 rows affected (0.03 sec) Notice that the table will be dropped without any reservation by the database server. It is vital that you type in the table name accurately because once a table has been dropped, it is no longer available. Transactions One of the most powerful aspects of MySQL is its ability to use transactions. A transaction is an atomic action that must either succeed or fail. This means that in a transaction consisting of three different queries a SELECT, an INSERT, and an UPDATE if any of these operations fail, the other commands must be rolled back to their original state. The current MySQL system includes two different table types that allow for transactions: InnoDB and BDB. In order for a database table to use transactions, the table must be created using a TYPE clause or the table must be altered with an ALTER TABLE command also using the TYPE clause. Once you create a table to handle transactions, you must inform the MySQL system that you want to use transactions. You can accomplish this by using the autocommit database server variable. By default, this variable is set to a value of 1, meaning that the database server will automatically commit the query once it executes. To start a transaction, the autocommit variable must be set to 0. For example: mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) Now you have the ability to execute SQL statements that will be either committed to the database or rolled back. Start with either the BEGIN or BEGIN WORK statement:

Hint: This post is supported by Gama web hosting php services