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.

-- 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.

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.

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.

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.

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.

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.

Back to top

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.

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.

{
    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.

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.

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.

$ 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$ 

Back to top

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.

Back to top

Download

Information about download methods

Resources

Learn

• Read other articles in this series:
  • " Developing with Apache Derby -- Hitting the trifecta: Introduction to Apache Derby" (developerWorks, February 2006) introduces the Apache Derby database and provides the foundation for many topics in this series.
  • " Developing with Apache Derby -- Hitting the trifecta: Database development with Apache Derby, Part 1" (developerWorks, March 2006) introduces the ij tool and demonstrates how to use it to connect to an Apache Derby database.
  • " Developing with Apache Derby -- Hitting the trifecta: Database development with Apache Derby, Part 2" (developerWorks, April 2006) details the basic Apache Derby data types and demonstrates how to create tables in an Apache Derby database.
  
  
• Access a number of Apache Derby project online manuals, which provide more detailed information on how to use the Apache Derby database.
  
  
• Take this tutorial that details how to download and install Apache Derby.
  
  
• Learn how to properly verify your download.
  
  
• Check out developerWorks articles and tutorials about the IBM® Cloudscape™ database, which is built using the Apache Derby code base.
  
  
• Check out the developerWorks Apache Derby project area for articles, tutorials, and other resources to help you get started with Derby today.
  
  
• Visit the developerWorks Open source zone for extensive how-to information, tools, and project updates to help you develop with open source technologies and use them with IBM's products.
  
  
• Visit the Apache Derby Project Web site.
  
  
• Browse all the Apache articles and free Apache tutorials available in the developerWorks Open source zone.
  
  
• Browse for books on these and other technical topics at the Safari bookstore.
  
  

Get products and technologies

• Download Apache Derby.
  
  
• Innovate your next open source development project with IBM trial software, available for download or on DVD.
  
  

Discuss

• Get involved in the developerWorks community by participating in developerWorks blogs.
  
  

About the author

Robert 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.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• SQL scripts
• Insert data in Apache Derby
• Summary
• Download
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Open source on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers