Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 3

Running scripts and inserting data

Learn how to create a self-documenting SQL command file, which you can execute as often as necessary, by placing SQL commands in a text file along with useful comments. After introducing the basic principles of a SQL script file, this article presents three ways to execute a script file. Then you'll review the fundamentals of inserting data into a table using the SQL INSERT statement, including seeing examples that insert ten new rows in different ways. Finally, the article presents a script that automates this INSERT operation and displays the newly inserted data for validation.

Share:

Robert Brunner, NCSA Research Scientist, Assistant Professor of Astronomy, University of Illinois, Urbana-Champaign

Robert J. BrunnerRobert J. Brunner is a research scientist at the National Center for Supercomputing Applications and an assistant professor of astronomy at the University of Illinois, Urbana-Champaign. He has published several books and a number of articles and tutorials on a range of topics.



12 May 2006

Also available in Russian Japanese

SQL scripts

In the previous article in this series, "Database development with Apache Derby, Part 2: Schemas" (developerWorks, April 2006), you executed SQL commands directly at the prompt of the ij tool. By using the ij tool, you can easily experiment with different SQL or Derby commands. Often, however, you'll need to execute multiple, complex commands. To simplify debugging a set of complex SQL commands, it's generally easier to write them in a text file and then execute the commands in the text file all at once. This operation is known as running a SQL script, and you can do so easily by using Apache Derby. By placing SQL commands in a script file, you gain the additional benefit of being able to execute the commands as many times as necessary.

Script files don't have to be difficult to work with, as shown by the first Derby SQL script example in Listing 1.

Listing 1. A first Derby SQL script
-- Ignore the database not created warning if present
connect 'jdbc:derby:test;create=true' ;

-- First delete the tables if they exist. 
-- Ignore the table does not exist error if present
DROP TABLE bigdog.products ;
DROP TABLE bigdog.vendors ;

-- CREATE the products table for bigdog's Surf Shop
CREATE TABLE bigdog.products (
itemNumber INT NOT NULL,
price DECIMAL(5, 2),
stockDate DATE,
description VARCHAR(128)
) ;

-- CREATE the products table for bigdog's Surf Shop
CREATE TABLE bigdog.vendors (
itemNumber INT NOT NULL,
vendornumber INT NOT NULL,
vendorName CHAR(64)
) ;

exit ;

The script presented in Listing 1 re-creates the bigdog schema and the two tables (products and vendors) that were demonstrated in the previous article in this series. If any of these concepts is unclear, you should go back and reread that article before tackling this one. Because this article introduces the basics of inserting data into a table, you first need to create tables that are ready to accept new data.

Edit Derby SQL scripts

A script file is just a text file that contains a combination of SQL commands and Apache Derby commands that can be run directly from the Apache Derby ij tool. A script file simplifies the development and maintenance of Apache Derby databases and provides a self-documenting technique for building databases. You should store these files as ASCII text files, not as RTF files (or any other format), to prevent text-encoding errors. Some text applications (like Wordpad on a Microsoft® Windows® system) may try to automatically save your file as a rich text file. Be careful to avoid doing so, or you may have problems when you try to execute your script file.

Rather than entering the table-creation SQL commands directly into the Derby ij tool, you can put them into a text file and have the ij tool run the commands in the file directly. This article includes a .zip file (see the Download section to access the file) that contains two script files; one of them, derby.create.sql, is shown in Listing 1. As the article steps through the lines in this script file, you can either follow along with the code shown in Listing 1 or open the script file in your favorite text editor.

The script file includes several lines that start with two dashes (--). These lines are SQL comments; you should use them to provide a basic description of the purpose of each major component within the script file. The first actual command in the script is the Derby connect command, which tells the ij tool to connect to the test database, first creating it if necessary. The next commands are SQL DROP statements that delete the products and vendors tables from the bigdog schema. If the tables don't exist (which is the case if the database was just created), an error message is displayed; but as the preceding SQL comments indicate, you can safely ignore those messages.

You first drop the tables, if they exist, so that you can cleanly create new tables with the exact column definitions you need. The next two SQL statements do just that, creating the products and vendors tables in the bigdog schema. The script ends with an exit command that terminates the connection to the database and allows the ij tool to exit gracefully. The next step is to learn how to execute a Derby script file.

Run scripts in ij

A script file is useful only if you can use it to execute the commands listed in the file. The simplest technique to execute the commands in a SQL script file is to run the script file from within the ij tool. Before you can do this, however, you need to create a test directory and expand the derby4.zip file that you can download with this article (see the Download section). This process is illustrated in Listing 2.

Listing 2. Running a SQL script within ij
rb$ mkdir test
rb$ cp derby4.zip test/
rb$ cd test/
rb$ unzip derby4.zip 
Archive:  derby4.zip
  inflating: derby.create.sql       
  inflating: derby.insert.sql             
rb$ ls
derby.create.sql      derby.insert.sql      derby4.zip
rb$ java org.apache.derby.tools.ij             
ij version 10.1
ij> run 'derby.create.sql' ;
ij> -- Ignore the database not created warning if present
connect 'jdbc:derby:test;create=true' ;
ij> -- First delete the tables if they exist. 
-- Ignore the table does not exist error if present
DROP TABLE bigdog.products ;
ERROR 42Y07: Schema 'BIGDOG' does not exist
ij> DROP TABLE bigdog.vendors ;
ERROR 42Y07: Schema 'BIGDOG' does not exist
ij> -- CREATE the products table for Bigdog's Surf Shop
CREATE TABLE bigdog.products (
itemNumber INT NOT NULL,
price DECIMAL(5, 2),
stockDate DATE,
description VARCHAR(128)
) ;
0 rows inserted/updated/deleted
ij> -- CREATE the products table for Bigdog's Surf Shop
CREATE TABLE bigdog.vendors (
itemNumber INT NOT NULL,
vendornumber INT NOT NULL,
vendorName CHAR(64)
) ;
0 rows inserted/updated/deleted
ij> exit ;

Although Listing 2 may seem long just to execute a Derby script, the process is simple. This example assumes that you have a terminal open (or a Windows Command process window -- but note that some of the steps in this example are UNIX® specific) and that you've changed to the directory where you saved the derby4.zip file that contains the two script files provided with this article. To minimize any chance of errors, you first create a new directory, copy the derby4.zip file into this new directory, and change into this new directory. Then expand the example .zip file. As the directory listing shows, you now have three files in your new directory: derby4.zip, derby.create.sql, and derby.insert.sql. For now, you'll only work with the create script file; the insert script file will be used at the end of this article.

What if something goes wrong?

Sometimes, no matter how hard you try, things don't work out quite right. If you can't safely execute the derby.create.sql script, there are a number of possibilities to check:

  • Be sure the Derby ij tool started up properly. If not, you may have a CLASSPATH tool problem.
  • Be sure you have free disk space in which to create a new database.
  • Be sure you have proper permissions (to read the script file and to create the new database) in the directory where you try to execute the script file.
  • Be sure your script file is a simple ASCII text file.

If all else fails, check the latest Derby documentation, which is available for free on the Apache Derby Web site (see the Resources section for the link), or ask your question on the Derby mailing list.

The rest of Listing 2 demonstrates how to execute the derby.create.sql script file from within the Derby ij tool. First you start the ij tool. If you have problems doing so, you may want to review the Derby installation verification steps presented in the first article in this series. Once you have the ij> prompt, you're ready to run the appropriate script file. You do so by using the run command, which takes a single argument: the name of the script file enclosed in single quotes.

As this script executes, you may see a combination of new ij> prompts, commands, and warning or error messages. This display may seem awkward, but the run command executes your script file as if you were typing the commands directly into the ij tool. At the end, the exit command is processed, and the script completes, shutting down the ij tool. If the output of your first script execution matches what is shown in Listing 2, congratulations are in order. You now have a new test database with two new tables ready to hold data.

Although the technique of executing a script from within the ij tool can be effective, sometimes it's easier to automate the process further. The next section discusses how you can execute a Derby script file directly from a command line.

Run scripts from a command line

This section examines two techniques for running a script from the command line. The first technique is shown in Listing 3.

Listing 3. Running a SQL script from the command line
rb$ java org.apache.derby.tools.ij derby.create.sql
ij version 10.1
ij> -- Ignore the database not created warning if present
connect 'jdbc:derby:test;create=true' ;
WARNING 01J01: Database 'test' not created, connection made to existing database instead.
ij> -- First delete the tables if they exist. 
-- Ignore the table does not exist error if present
DROP TABLE bigdog.products ;
0 rows inserted/updated/deleted
ij> DROP TABLE bigdog.vendors ;
0 rows inserted/updated/deleted
ij> -- CREATE the products table for Bigdog's Surf Shop
CREATE TABLE bigdog.products (
itemNumber INT NOT NULL,
price DECIMAL(5, 2),
stockDate DATE,
description VARCHAR(128)
) ;
0 rows inserted/updated/deleted
ij> -- CREATE the products table for Bigdog's Surf Shop
CREATE TABLE bigdog.vendors (
itemNumber INT NOT NULL,
vendornumber INT NOT NULL,
vendorName CHAR(64)
) ;
0 rows inserted/updated/deleted
ij> exit ;

Listing 3 demonstrates the first method for executing a script file directly from a command line. In this case, you supply the name of the script file as a command-line argument to the ij tool. The lines in the script file are read as before and processed sequentially. If you first executed the derby.create.sql script directly from within the ij tool and now run the same script file in the same directory at the command line, you should see output similar to that in Listing 3 (everything after the first line in the listing). As shown, you receive a warning message that the test database wasn't created because it already existed. This time, however, you don't get error messages saying the two tables can't be dropped, because you already created them (as shown in Listing 2). After you drop the products and vendors tables, you re-create them, and the script exits.

There is yet another way to run a Derby script: redirect the standard input of the ij tool to read from the script file. On a UNIX-based operating system, this is most easily accomplished by using the STDIN redirection character that is appropriate for your shell, such as the less-than symbol (<) for the Bash shell. This technique, which is demonstrated in Listing 4, has the benefit of reducing the amount of output displayed on the screen.

Listing 4. Running a SQL script from the command line (part 2)
rb$ java org.apache.derby.tools.ij < derby.create.sql
ij version 10.1
ij> WARNING 01J01: Database 'test' not created, connection made to existing database instead.
ij> 0 rows inserted/updated/deleted
ij> 0 rows inserted/updated/deleted
ij> 0 rows inserted/updated/deleted
ij> 0 rows inserted/updated/deleted
ij>
rb$

As you can see in Listing 4, when you execute the script by redirecting the standard input of the ij tool, the only displayed text is messages from the ij tool, including information messages like 0 rows inserted/updated/deleted or warnings and error messages. If you want to eliminate those, you can redirect the ij tool's standard output and standard error. For example, if you're working in the Bash shell, you can do this with

java org.apache.derby.tools.ij < derby.create.sql > derby.create.out 2> derby.create.err

which runs the derby.create.sql script file, saves the output messages to derby.create.out, and saves all ij error messages to derby.create.err.

Now you'll focus on the process of inserting data into a table using Apache Derby. To follow along, you need a Derby database with the products table available. If you haven't already done so, you need to execute the derby.create.sql script file.


Insert data in Apache Derby

One of the most important tasks when you're building a database application is inserting data into the database. It doesn't matter how good the database software is -- if you put bad data in a database, nothing else matters. There are several different ways to insert data into a database, but the rest of this article focuses on inserting data into an Apache Derby database using the SQL INSERT statement.

The SQL INSERT statement

Before you can insert data into an Apache Derby database using the SQL INSERT statement, you must know how to properly use this statement. Listing 5 provides the full syntax for the SQL INSERT statement in Apache Derby.

Listing 5. SQL INSERT syntax
INSERT INTO table-Name
    [ (Simple-column-Name [ , Simple-column-Name]* ) ]
	  Expression

This syntax should seem familiar. As discussed in the previous article in this series, the square brackets ([]) enclose optional parameters. The only component whose purpose isn't immediately clear is Expression; but how complex can that simple phrase be? Of course, appearances can be deceiving; the Expression term can expand to one of four different structures:

  • A single-row VALUES list
  • A multiple-row VALUES list
  • A SELECT expression
  • A UNION expression

Of these, the last two are beyond the scope of this article and will be discussed in more detail in a future article. The first two are similar; the only difference is that the first form inserts one row into a table, whereas the latter form inserts multiple rows into a table.

You can use the optional part of the SQL INSERT statement to specify the column order of the values being inserted into the table. By default, data is inserted into a table's columns in the same order that the columns were listed when the table was created. Sometimes you may want to change this order or perhaps only specify values for columns that have NOT NULL constraints. By explicitly listing the columns in your SQL INSERT statement, you gain more control of the operation and can more easily handle these specific use cases.

The syntax for the SQL VALUES expression is fairly simple, as shown in Listing 6.

Listing 6. SQL VALUES syntax
{
    VALUES ( Value {, Value }* )
        [ , ( Value {, Value }* ) ]* |
    VALUES Value [ , Value ]*
 }

This syntax displays the multiple-row format first, followed by the single-row format (remember that the vertical line character, |, means or and that the asterisk character, *, means one or more). The value term stands for a value that you want to insert into a specific column. To insert data into multiple columns, you must enclose the data for a row in parentheses separated by commas.

The next two sections show examples of the syntax being implemented.

Insert data by using SQL

As shown in Listing 7, to insert data into a table, you first need to start the ij tool and connect to your database. Remember that to insert data into a table, the table must exist. If you haven't already done so, execute the table-creation scripts as discussed earlier in this article.

Listing 7. Inserting single rows
rb$ java org.apache.derby.tools.ij           
ij version 10.1
ij> connect 'jdbc:derby:test' ;
ij> INSERT INTO bigdog.products 
    VALUES(1, 19.95, '2006-03-31', 'Hooded sweatshirt') ;
1 row inserted/updated/deleted
ij> INSERT INTO bigdog.products(itemNumber, price, stockDate, description) 
    VALUES(2, 99.99, '2006-03-29', 'Beach umbrella') ;
1 row inserted/updated/deleted
ij> INSERT INTO bigdog.products(itemNumber, price, stockDate) 
    VALUES(3, 0.99, '2006-02-28') ;
1 row inserted/updated/deleted
ij> exit ;

This example presents three single-row inserts into the bigdog.products table. The first SQL INSERT statement doesn't provide a list of columns; it inserts an itemNumber, a price, a stockDate, and a description. Notice that the values inserted into both the stockDate and description columns are enclosed in single quote characters. The description column is a variable-length character string, so it expects a string (which you indicate by enclosing the character data within single quotes). The stockDate column, on the other hand, is a date column; it requires you to pass in dates in single quotes to properly parse out the correct day, month, and year information. (For more guidance on the format of data types during a SQL INSERT operation, read the online documentation or see the previous article in this series.)

The second SQL INSERT statement explicitly lists all four columns and inserts new values appropriately. The final SQL INSERT statement lists only three columns and inserts only three values. The description column is left empty, which means it will have a NULL value.

Although single-row SQL INSERT statements can be useful, when you need to insert multiple rows, it's more efficient to do so directly, as shown in Listing 8.

Listing 8. Inserting multiple rows
rb$ java org.apache.derby.tools.ij           
ij version 10.1
ij> connect 'jdbc:derby:test' ;
ij> INSERT INTO bigdog.products(itemNumber, price, stockDate, description)
    VALUES (4, 29.95, '2006-02-10', 'Male bathing suit, blue'),
           (5, 49.95, '2006-02-20', 'Female bathing suit, one piece, aqua'),
           (6, 9.95, '2006-01-15', 'Child sand toy set'),
           (7, 24.95, '2005-12-20', 'White beach towel'),
           (8, 32.95, '2005-12-22', 'Blue-striped beach towel'),
           (9, 12.95, '2006-03-12', 'Flip-flop'),
           (10, 34.95, '2006-01-24', 'Open-toed sandal') ;
7 rows inserted/updated/deleted
ij> exit ;

In this example, you first start the ij tool and connect to your database. The next line inserts seven rows into the database by explicitly listing all four columns and providing new values for each row. As discussed earlier, multiple-row inserts enclose the values for each new row within parentheses, and these values are separated by commas. After this SQL INSERT statement, the ij tool reports that seven new rows were inserted.

Insert data in Apache Derby

A multiple-row insert is preferable to multiple single-row inserts; but an even better technique is to place the SQL INSERT statements in a script file and run the script to insert the data. This approach lets you more easily fix errors or reinsert the data if necessary without recreating the requisite SQL INSERT statements. The .zip file that you can download with this article includes two SQL script files, as discussed earlier. The second script file (derby.insert.sql) inserts the ten rows presented in the previous section into the database and performs a simple query to display the results, thus validating the insert operation. The mechanics of performing a query operation will be discussed in detail in the next article, but you don't have to understand the query to be able to run the insert script.

To execute the script, you can choose any of the three methods presented at the start of this article. In Listing 9, the insert script is executed by redirecting the standard input of the ij tool to read from the script file. The ten rows are inserted, and the results are displayed to the screen.

Listing 9. Verifying the insert operation
$ java org.apache.derby.tools.ij < derby.insert.sql
ij version 10.1
ij> ij> 10 rows inserted/updated/deleted
ij> ITEMNUMBER |PRICE   |STOCKDATE |DESCRIPTION                              
-------------------------------------------------------------------------------
1          |19.95   |2006-03-31|Hooded sweatshirt                             
2          |99.99   |2006-03-29|Beach umbrella                                 
3          |0.99    |2006-02-28|                                               
4          |29.95   |2006-02-10|Male bathing suit, blue                        
5          |49.95   |2006-02-20|Female bathing suit, one piece, aqua           
6          |9.95    |2006-01-15|Child sand toy set                             
7          |24.95   |2005-12-20|White beach towel                              
8          |32.95   |2005-12-22|Blue-striped beach towel                        
9          |12.95   |2006-03-12|Flip-flop                                      
10         |34.95   |2006-01-24|Open-toed sandal                               

10 rows selected
ij> ij> rb$

Summary

This article has discussed two major topics. First, you were introduced to the idea of SQL script files, which you can use to perform multiple SQL (or Derby) commands in an automated fashion. You can execute a SQL script file using the Apache Derby ij tool in three ways: from within the tool, using the run command, or by using either of two techniques directly from the command line. Next, you reviewed the SQL syntax for the INSERT statement and saw examples of how to use this statement to insert data into a Derby database. Future articles will build on this foundation to query, update, and delete data from an Apache Derby database.


Download

DescriptionNameSize
Derby SQL scripts for this articlederby4.zip1KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Open source on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Open source, Information Management
ArticleID=110654
ArticleTitle=Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 3
publish-date=05122006