You are viewing a plain text version of this content. The canonical link for it is here.
Posted to general@db.apache.org by rh...@apache.org on 2012/12/19 19:20:28 UTC
svn commit: r843115 [10/44] - in /websites/production/db/content/derby: ./
binaries/ blogs/ blogs/images/ dev/ docs/ images/ integrate/
integrate/plugin_help/ integrate/plugin_help/images/ logo/ manuals/ papers/
papers/DerbyTut/ releases/ skin/ skin/cs...
Added: websites/production/db/content/derby/integrate/SQuirreL_Derby.html
==============================================================================
--- websites/production/db/content/derby/integrate/SQuirreL_Derby.html (added)
+++ websites/production/db/content/derby/integrate/SQuirreL_Derby.html Wed Dec 19 18:20:21 2012
@@ -0,0 +1,1043 @@
+<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+<html>
+<head>
+<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
+<meta content="Apache Forrest" name="Generator">
+<meta name="Forrest-version" content="0.8">
+<meta name="Forrest-skin-name" content="pelt">
+<title>Using SQuirreL SQL Client with Derby</title>
+<link type="text/css" href="../skin/basic.css" rel="stylesheet">
+<link media="screen" type="text/css" href="../skin/screen.css" rel="stylesheet">
+<link media="print" type="text/css" href="../skin/print.css" rel="stylesheet">
+<link type="text/css" href="../skin/profile.css" rel="stylesheet">
+<script src="../skin/getBlank.js" language="javascript" type="text/javascript"></script><script src="../skin/getMenu.js" language="javascript" type="text/javascript"></script><script src="../skin/fontsize.js" language="javascript" type="text/javascript"></script>
+<link rel="shortcut icon" href="../">
+</head>
+<body onload="init()">
+<script type="text/javascript">ndeSetTextSize();</script>
+<div id="top">
+<!--+
+ |breadtrail
+ +-->
+<div class="breadtrail">
+<a href="http://www.apache.org/">apache</a> > <a href="http://db.apache.org/">db</a><script src="../skin/breadcrumbs.js" language="JavaScript" type="text/javascript"></script>
+</div>
+<!--+
+ |header
+ +-->
+<div class="header">
+<!--+
+ |start group logo
+ +-->
+<div class="grouplogo">
+<a href="http://db.apache.org/derby"><img class="logoImage" alt="Apache Derby" src="../images/derby-logo-web.png" title="Derby is a zero-admin Java RDBMS"></a>
+</div>
+<!--+
+ |end group logo
+ +-->
+<!--+
+ |start Project Logo
+ +-->
+<div class="projectlogoA1">
+<a href="http://db.apache.org"><img class="logoImage" alt="Apache DB Project" src="../images/db-logo-white.png" title="Apache DB creates and maintains database solutions."></a>
+</div>
+<!--+
+ |end Project Logo
+ +-->
+<!--+
+ |start Tabs
+ +-->
+<ul id="tabs">
+<li>
+<a class="unselected" href="../index.html">Home</a>
+</li>
+<li>
+<a class="unselected" href="../quick_start.html">Quick Start</a>
+</li>
+<li>
+<a class="unselected" href="../derby_downloads.html">Download</a>
+</li>
+<li>
+<a class="unselected" href="../derby_comm.html">Community</a>
+</li>
+<li>
+<a class="unselected" href="../manuals/index.html">Documentation</a>
+</li>
+<li class="current">
+<a class="selected" href="../blogs/index.html">Resources</a>
+</li>
+</ul>
+<!--+
+ |end Tabs
+ +-->
+</div>
+</div>
+<div id="main">
+<div id="publishedStrip">
+<!--+
+ |start Subtabs
+ +-->
+<div id="level2tabs"></div>
+<!--+
+ |end Endtabs
+ +-->
+<script type="text/javascript"><!--
+document.write("Last Published: " + document.lastModified);
+// --></script>
+</div>
+<!--+
+ |breadtrail
+ +-->
+<div class="breadtrail">
+
+
+ </div>
+<!--+
+ |start Menu, mainarea
+ +-->
+<!--+
+ |start Menu
+ +-->
+<div id="menu">
+<div onclick="SwitchMenu('menu_1.1', '../skin/')" id="menu_1.1Title" class="menutitle">Blogs and Articles About Derby</div>
+<div id="menu_1.1" class="menuitemgroup">
+<div class="menuitem">
+<a href="../blogs/index.html">Overview</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#blogs">Blogs</a>
+</div>
+<div onclick="SwitchMenu('menu_1.1.3', '../skin/')" id="menu_1.1.3Title" class="menutitle">Articles</div>
+<div id="menu_1.1.3" class="menuitemgroup">
+<div onclick="SwitchMenu('menu_1.1.3.1', '../skin/')" id="menu_1.1.3.1Title" class="menutitle">Tutorials, Tips and Tuning</div>
+<div id="menu_1.1.3.1" class="menuitemgroup">
+<div class="menuitem">
+<a href="../blogs/index.html#getstarted">Getting Started</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#features">Features, Hints and Tips</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#security">Security</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#performance">Performance and Tuning</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.1.3.2', '../skin/')" id="menu_1.1.3.2Title" class="menutitle">Tools and Migration</div>
+<div id="menu_1.1.3.2" class="menuitemgroup">
+<div class="menuitem">
+<a href="../blogs/index.html#tools">Tools</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#migration">Migration</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.1.3.3', '../skin/')" id="menu_1.1.3.3Title" class="menutitle">Applications</div>
+<div id="menu_1.1.3.3" class="menuitemgroup">
+<div class="menuitem">
+<a href="../blogs/index.html#client">Client</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#middletier">Middle Tier</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#persistence">Persistence</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#scalability">Scalability and Failover</a>
+</div>
+</div>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.2', '../skin/')" id="menu_1.2Title" class="menutitle">Integration With Other Products</div>
+<div id="menu_1.2" class="menuitemgroup">
+<div class="menuitem">
+<a href="../integrate/index.html">Overview</a>
+</div>
+<div class="menuitem">
+<a href="../integrate/index.html#uses">What works with Derby?</a>
+</div>
+<div class="menuitem">
+<a href="../integrate/index.html#products">Product Writeups</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.3', '../skin/')" id="menu_1.3Title" class="menutitle">Eclipse Plug-ins</div>
+<div id="menu_1.3" class="menuitemgroup">
+<div class="menuitem">
+<a href="../integrate/derby_plugin_info.html">Info</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.4', '../skin/')" id="menu_1.4Title" class="menutitle">Papers and Presentations</div>
+<div id="menu_1.4" class="menuitemgroup">
+<div class="menuitem">
+<a href="../papers/index.html">Overview</a>
+</div>
+<div onclick="SwitchMenu('menu_1.4.2', '../skin/')" id="menu_1.4.2Title" class="menutitle">Derby Engine</div>
+<div id="menu_1.4.2" class="menuitemgroup">
+<div onclick="SwitchMenu('menu_1.4.2.1', '../skin/')" id="menu_1.4.2.1Title" class="menutitle">Javadoc</div>
+<div id="menu_1.4.2.1" class="menuitemgroup">
+<div class="menuitem">
+<a href="http://db.apache.org/derby/javadoc/engine">Engine</a>
+</div>
+<div class="menuitem">
+<a href="http://db.apache.org/derby/javadoc/language">Language</a>
+</div>
+<div class="menuitem">
+<a href="http://db.apache.org/derby/javadoc/tools">Tools</a>
+</div>
+<div class="menuitem">
+<a href="http://db.apache.org/derby/javadoc/publishedapi">API</a>
+</div>
+</div>
+<div class="menuitem">
+<a href="../papers/derby_arch.html">Architecture</a>
+</div>
+<div class="menuitem">
+<a href="../papers/btree_package.html">BTree</a>
+</div>
+<div class="menuitem">
+<a href="../papers/pageformats.html">Disk Page Format</a>
+</div>
+<div class="menuitem">
+<a href="../papers/derby_htw.html">How Things Work</a>
+</div>
+<div class="menuitem">
+<a href="../papers/Intersect-design.html">Intersect & Except</a>
+</div>
+<div class="menuitem">
+<a href="../papers/JDBCImplementation.html">JDBC</a>
+</div>
+<div class="menuitem">
+<a href="../papers/logformats.html">Log Format</a>
+</div>
+<div class="menuitem">
+<a href="../papers/recovery.html">Logging & Recovery</a>
+</div>
+<div class="menuitem">
+<a href="../papers/optimizer.html">Optimizer</a>
+</div>
+<div class="menuitem">
+<a href="http://db.apache.org/derby/javadoc/engine/org/apache/derby/iapi/types/package-summary.html#package_description">Type System</a>
+</div>
+<div class="menuitem">
+<a href="../papers/versionupgrade.html">Versioning</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.4.3', '../skin/')" id="menu_1.4.3Title" class="menutitle">Derby Network Client</div>
+<div id="menu_1.4.3" class="menuitemgroup">
+<div class="menuitem">
+<a href="../papers/DerbyClientSpec.html">Functional Spec</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.4.4', '../skin/')" id="menu_1.4.4Title" class="menutitle">Derby Tutorial</div>
+<div id="menu_1.4.4" class="menuitemgroup">
+<div class="menuitem">
+<a href="../papers/DerbyTut/index.html">Overview</a>
+</div>
+<div class="menuitem">
+<a href="../papers/DerbyTut/install_software.html">Step 1: Install Software</a>
+</div>
+<div class="menuitem">
+<a href="../papers/DerbyTut/ij_intro.html">Step 2: ij Basics</a>
+</div>
+<div class="menuitem">
+<a href="../papers/DerbyTut/embedded_intro.html">Step 3: Embedded Derby</a>
+</div>
+<div class="menuitem">
+<a href="../papers/DerbyTut/ns_intro.html">Step 4: Derby Network Server</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.4.5', '../skin/')" id="menu_1.4.5Title" class="menutitle">Presentations</div>
+<div id="menu_1.4.5" class="menuitemgroup">
+<div class="menuitem">
+<a href="../papers/ApacheCon.html">ApacheCon</a>
+</div>
+<div class="menuitem">
+<a href="../papers/MiscPresentations.html#Victorian+Java+User+Group">Victorian JUG 2008</a>
+</div>
+<div class="menuitem">
+<a href="../papers/MiscPresentations.html#OSCON+2005">OSCON 2005</a>
+</div>
+<div class="menuitem">
+<a href="../papers/MiscPresentations.html#Colorado+Software+Summit+2004">Colorado 2004</a>
+</div>
+</div>
+</div>
+<!--+
+ |start Search
+ +-->
+<div class="searchbox">
+<hr>
+<form action="http://www.google.com/search" method="get">
+<input value="db.apache.org" name="sitesearch" type="hidden"><input onFocus="getBlank (this, 'Search the site with google');" size="18" name="q" id="query" type="text" value="Search the site with google">
+ <input name="Search" value="Search" type="submit">
+</form>
+</div>
+<!--+
+ |end search
+ +-->
+<div id="credit"></div>
+<div id="roundbottom">
+<img style="display: none" class="corner" height="15" width="15" alt="" src="../skin/images/rc-b-l-15-1body-2menu-3menu.png"></div>
+<!--+
+ |alternative credits
+ +-->
+<div id="credit2"></div>
+</div>
+<!--+
+ |end Menu
+ +-->
+<!--+
+ |start content
+ +-->
+<div id="content">
+<div class="trail">Font size:
+ <input value="Reset" class="resetfont" title="Reset text" onclick="ndeSetTextSize('reset'); return false;" type="button">
+ <input value="-a" class="smallerfont" title="Shrink text" onclick="ndeSetTextSize('decr'); return false;" type="button">
+ <input value="+a" class="biggerfont" title="Enlarge text" onclick="ndeSetTextSize('incr'); return false;" type="button">
+</div>
+<h1>Using SQuirreL SQL Client with Derby</h1>
+<div id="minitoc-area">
+<ul class="minitoc">
+<li>
+<a href="#Overview">Overview</a>
+</li>
+<li>
+<a href="#Prerequisites">Prerequisites</a>
+</li>
+<li>
+<a href="#Configuring+SQuirreL+for+the+Derby+Embedded+JDBC+Driver">Configuring SQuirreL for the Derby Embedded JDBC Driver </a>
+</li>
+<li>
+<a href="#Creating+an+Alias+to+a+Derby+database+using+the+Embedded+Driver">Creating an Alias to a Derby database using the Embedded Driver</a>
+</li>
+<li>
+<a href="#Configuring+the+Derby+Client+Driver+in+the+Drivers+window">Configuring the Derby Client Driver in the Drivers window</a>
+</li>
+<li>
+<a href="#Creating+an+Alias+for+an+existing+Derby+database+using+the+Client+Driver">Creating an Alias for an existing Derby database using the Client Driver</a>
+</li>
+<li>
+<a href="#Creating+a+session+to+issue+SQL+and+browse+the+database">Creating a session to issue SQL and browse the database</a>
+</li>
+<li>
+<a href="#Modifying+data+objects+via+the+Objects+tab">Modifying data objects via the Objects tab</a>
+</li>
+<li>
+<a href="#SQuirreL+and+Derby+Resources">SQuirreL and Derby Resources</a>
+</li>
+</ul>
+</div>
+
+
+<a name="N1000D"></a><a name="Overview"></a>
+<h2 class="boxed">Overview</h2>
+<div class="section">
+<p>
+SQuirreL SQL Client is a useful tool for browsing and manipulating SQL databases via JDBC. This paper discusses setting up SQuirreL to access Derby databases via the Derby Embedded Driver and Client Drivers with the 10.1 version of Derby.
+
+</p>
+<p>
+Some of the features of SQuirreL that are discussed in relation to
+working with Derby databases are:
+</p>
+<ul>
+
+<li>
+Configuring the SQuirreL Drivers window to use the Derby JDBC drivers
+</li>
+
+<li>
+Creating aliases to new or existing Derby databases
+</li>
+
+<li>
+Executing, editing and running SQL scripts and commands
+</li>
+
+<li>
+Browsing the objects in a database using the session window
+</li>
+
+<li>
+Extracting and loading data to and from a Derby table via a local file
+</li>
+
+<li>
+Generating DDL
+</li>
+
+<li>
+Dropping tables
+</li>
+
+</ul>
+</div>
+
+
+<a name="N10032"></a><a name="Prerequisites"></a>
+<h2 class="boxed">Prerequisites</h2>
+<div class="section">
+<p>
+Download and install the following software to use SQuirreL SQL Client with Derby:
+</p>
+<table class="ForrestTable" cellspacing="1" cellpadding="4">
+
+<tr>
+
+<th colspan="1" rowspan="1">Software</th>
+<th colspan="1" rowspan="1">Minimum Release Level Required</th>
+<th colspan="1" rowspan="1">Release Used in this Example</th>
+<th colspan="1" rowspan="1">Download Location</th>
+
+</tr>
+
+<tr>
+
+<td colspan="1" rowspan="1">Java JRE or JDK or SDK</td>
+<td colspan="1" rowspan="1">1.4.x</td>
+<td colspan="1" rowspan="1">1.5.x</td>
+<td colspan="1" rowspan="1">Any J2SE-compliant JVM, Java's reference implementation is at <a class="external" href="http://java.sun.com/javase/">http://java.sun.com/javase/</a></td>
+
+</tr>
+
+<tr>
+
+<td colspan="1" rowspan="1">SQuirreL SQL Client</td>
+<td colspan="1" rowspan="1">Version 2.0</td>
+<td colspan="1" rowspan="1">Version 2.2 Final</td>
+<td colspan="1" rowspan="1"><a class="external" href="http://www.squirrelsql.org/#installation">SQuirreL SQL Client, Version 2.2 Final</a></td>
+
+</tr>
+
+<tr>
+
+<td colspan="1" rowspan="1">Apache Derby</td>
+<td colspan="1" rowspan="1">Version 10.1.x</td>
+<td colspan="1" rowspan="1">Version 10.1.3</td>
+<td colspan="1" rowspan="1"><a href="http://db.apache.org/derby/derby_downloads.html#Latest+Official+Release">Apache Derby, Version 10.1</a></td>
+
+</tr>
+
+</table>
+<p>
+Additionally, if you would like to follow some of the examples for
+creating tables and loading data, download the file, <a href="../binaries/squirrel_derby.zip">squirrel_derby.zip</a>.
+</p>
+<p>
+After downloading SQuirreL, follow the instructions on the installation page,
+<a class="external" href="http://www.squirrelsql.org/#installation">squirrel.org/#installation</a>,
+on how to install SQuirreL using the executable jar file. On my system,
+this command worked to launch the install wizard:
+</p>
+<p class="code-block">
+java -jar squirrel-sql-2.2final-install.jar
+</p>
+<p>
+The wizard lets you choose the location where you wish to install SQuirreL,
+as well as which plugins you would like to install. I selected the Standard
+plugins because this paper will make use of the SQL Scripts standard plugin.
+Note that some of the Optional Plugins are considered Beta quality. After the
+installation is complete go to the directory where you installed SQuirreL and
+launch (or double-click on Windows) the squirrel-sql.bat file, or the squirrel-sql.sh file on Linux.
+</p>
+</div>
+
+
+<a name="N100B6"></a><a name="Configuring+SQuirreL+for+the+Derby+Embedded+JDBC+Driver"></a>
+<h2 class="boxed">Configuring SQuirreL for the Derby Embedded JDBC Driver </h2>
+<div class="section">
+<p>
+When you first launch SQuirreL it will create a directory called <span class="codefrag">.squirrel-sql</span> in your <span class="codefrag">C:\Documents and Settings\<username></span>
+directory on Windows, or <span class="codefrag">$HOME</span> on Linux. This is where configuration settings and history are stored. The first screen that appears in the
+desktop will show
+two windows - the Drivers and the Aliases windows. The Drivers window will
+show the Apache Derby Client and the Apache Derby Embedded drivers in the
+list. If the jar files for either of these drivers, <span class="codefrag">derbyclient.jar</span>
+ or <span class="codefrag">derby.jar</span>, are in your system CLASSPATH then they will have a blue check next to them.
+If not, a red 'X' will appear in front of them as shown below.
+</p>
+<p>
+
+<strong>Figure 1. The SQuirreL SQL Client desktop prior to configuration</strong>
+
+</p>
+<p>
+
+<img alt="SQuirreL desktop" height="467" src="../images/sq_first_screen.gif" width="671">
+</p>
+<p>
+Although you need to install Derby prior to going further, I'll assume you
+don't have the jar files in your CLASSPATH and we'll set up SQuirreL correctly
+so you don't need to.
+</p>
+<p>
+
+<strong>Configuring the Derby Embedded Driver in the Drivers window</strong>
+
+</p>
+<p>
+Select the <strong>Apache Derby Embedded</strong> option from the Drivers window and
+click the pencil icon, which allows you to Modify the Selected Driver.
+We need to point to the location of the <span class="codefrag">derby.jar</span> file on
+the file system, which contains the Derby Embedded Driver. Click the <strong>Extra Class Path</strong> tab, and then the <strong>Add</strong> button. Browse to this directory and
+select <span class="codefrag">derby.jar</span>.
+On my machine I browsed to <span class="codefrag">C:\derby_home\derby_10.1.3\lib\derby.jar</span>.
+</p>
+<p>Now, click the <strong>List Drivers</strong> button and the <strong>Class Name</strong> pulldown list will
+be automatically populated. Select the Class Name of <strong>org.apache.derby.jdbc.EmbeddedDriver</strong> from the list and click <strong>OK</strong>.
+</p>
+<p>
+Don't modify the Example URL in the Drivers window since this is just a listing
+of available drivers, and does not represent a connection to a specific
+database. That is what the Aliases window is for.
+Now you should be returned to the main desktop of SQuirreL and if the driver was
+added successfully, you will see this message in the status window, with green highlighting;
+</p>
+<p class="code-block">Driver class org.apache.derby.jdbc.EmbeddedDriver
+successfully registered for driver definition: Apache Derby Embedded</p>
+<p>
+Also, there should be a blue check mark next to the Apache Derby Embedded
+listing in the Drivers window now.
+</p>
+</div>
+
+
+<a name="N10115"></a><a name="Creating+an+Alias+to+a+Derby+database+using+the+Embedded+Driver"></a>
+<h2 class="boxed">Creating an Alias to a Derby database using the Embedded Driver</h2>
+<div class="section">
+<p>
+Now go to the Aliases window and click the blue '+' mark. Name the Alias
+something descriptive. I'm going to create a database using SQuirreL called
+<span class="codefrag">FirstDB</span> so I'll name the Alias <strong>Derby_Embedded_FirstDB</strong>. For the driver,
+pull down the list to select <strong>Apache Derby Embedded</strong>.
+For the URL, I want to create the <span class="codefrag">FirstDB</span> database so the URL
+will look like this:
+<span class="codefrag">jdbc:derby:FirstDB;create=true</span>.
+</p>
+<p>
+A User Name and Password are not required to make a connection using the
+Embedded Derby driver, so I will not enter any values for this. Also, at
+this time I do not want to automatically logon or connect at startup, so I'll
+leave those boxes unchecked.
+</p>
+<p>
+<strong>Figure 2. Creating an alias for the Embedded Driver</strong>
+</p>
+<p>
+<img alt="Creating an alias" height="605" src="../images/sq_alias_1.gif" width="719"></p>
+<p>
+Test the connection by clicking the <strong>Test</strong> button. The <strong>Connect to: Derby_Embedded_FirstDB</strong> window
+will appear and prompt again for a username and password, but they are not
+required. Just click the <strong>Connect</strong> button to test. If all goes well the
+<span class="codefrag">FirstDB</span> database will be created in the directory where you launched SQuirreL from, and a popup box will tell you the Connection was successful.
+Say <strong>OK</strong> to dismiss this window, and from the next window click the <strong>OK</strong>
+button too. Close the next window instead of connecting, so we can set up
+the Derby Client driver first, before browsing the database.
+</p>
+</div>
+
+
+<a name="N10151"></a><a name="Configuring+the+Derby+Client+Driver+in+the+Drivers+window"></a>
+<h2 class="boxed">Configuring the Derby Client Driver in the Drivers window</h2>
+<div class="section">
+<p>
+The Derby Client driver is used with the Derby network server, so we'll
+discuss the network server prior to configuring the client driver.
+</p>
+<p>
+
+<strong>Starting the Network Server and creating a database using ij</strong>
+
+</p>
+<p>
+The Derby Network server should be used in environments where multiple JVMs
+are connecting to a single Derby database. In the context of using
+SQuirreL to browse a Derby database this configuration is required if other
+users will be connecting to the same database via other clients, for instance,
+<strong>ij</strong> or some other application.
+</p>
+<p>
+Let's start the network server now, and create the database
+using <strong>ij</strong> prior to creating the Alias for this database.
+</p>
+<p>
+Start the Derby Network server on your localhost. If you don't know how to
+start the network server refer to the <a href="http://db.apache.org/derby/papers/DerbyTut/ns_intro.html">Derby tutorial</a>, which also shows you how to
+set up your environment to do so.
+</p>
+<p>
+This is the command I used to start the network server on my host with
+<span class="codefrag">derbynet.jar</span> in my CLASSPATH:
+</p>
+<p class="code-block">
+java org.apache.derby.drda.NetworkServerControl start
+</p>
+<p>
+In another command window, with <span class="codefrag">derbyclient.jar</span> and
+<span class="codefrag">derbytools.jar</span> in my classpath, I issued this command to
+launch <span class="codefrag">ij</span>, and then created a database called <span class="codefrag">ClientDB</span> under my <span class="codefrag">C:/derby_home/databases/</span> directory. Notice the difference
+in the URL when connecting to the network server versus when making an
+embedded Derby connection.
+</p>
+<p class="code-block">
+java org.apache.derby.tools.ij
+ij version 10.1
+ij> connect 'jdbc:derby://localhost:1527/C:/derby_home/databases/ClientDB;create=true';
+</p>
+<p>
+Leave the network server and <span class="codefrag">ij</span> windows active. Next we'll configure SQuirreL
+for use with the Client driver and then create an alias to the <span class="codefrag">ClientDB</span> database.
+</p>
+<p>
+
+<strong>The Derby Client Driver configuration in the Drivers window</strong>
+
+</p>
+<p>
+Select the <strong>Apache Derby Client</strong> driver from the <strong>Drivers</strong> window
+in SQuirreL, and then click the pencil icon again. Click the <strong>Extra Class Path</strong>
+tab and then the <strong>Add</strong> button to browse to the <span class="codefrag">derbyclient.jar</span>
+file. The client driver only contains one Class of driver, so the <strong>Class Name</strong>
+category should be populated with the correct value, <span class="codefrag">org.apache.derby.jdbc.ClientDriver</span>. Click <strong>OK</strong> and the blue check mark will appear
+next to the <strong>Apache Derby Client</strong> value in the list now.
+</p>
+<p>In the next section we'll configure an Alias to use the Derby Client Driver
+to connect to our database, <span class="codefrag">ClientDB</span>.
+</p>
+</div>
+
+
+<a name="N101C7"></a><a name="Creating+an+Alias+for+an+existing+Derby+database+using+the+Client+Driver"></a>
+<h2 class="boxed">Creating an Alias for an existing Derby database using the Client Driver</h2>
+<div class="section">
+<p>
+Select the blue cross from the <strong>Aliases</strong> window and name the Alias something descriptive - I've chosen <strong>Derby_Client_ClientDB</strong>. Make sure the
+Driver is <strong>Apache Derby Client</strong> and for the URL, we will point to our
+database <span class="codefrag">ClientDB</span> we created earlier. For my environment the
+correct value for the URL is:
+</p>
+<p class="code-block">
+jdbc:derby://localhost:1527/C:/derby_home/databases/ClientDB;
+</p>
+<p>
+Here is what the values for my environment look like, just before clicking
+the <strong>Test</strong> button. A user name and password is required in the SQuirreL
+environment although it is not required for <span class="codefrag">ij</span>. Any value for
+the user name and password will do, but realize that the user name sets the
+default schema for the connection. <strong>Note:</strong> If you would like
+to require a specific username and password to connect to Derby, refer to
+this <a href="http://db.apache.org/derby/docs/10.1/devguide/cdevcsecure36127.html">section</a> of the Derby Developer's Guide, on configuring user authentication.
+</p>
+<p>
+<strong>Figure 3. Creating an Alias to an existing database using the Client Driver</strong>
+</p>
+<p>
+<img alt="Creating an alias" height="673" src="../images/sq_alias_client.gif" width="719"></p>
+<p>
+Once the test connection is successful, at the main screen for the <strong>Derby_Client_ClientDB</strong> window, select the <strong>OK</strong> button to close the window.
+</p>
+<p>
+Remember how we left our <span class="codefrag">ij</span> session active? When
+we clicked the test button, and later on when we connect to and browse the
+<span class="codefrag">ClientDB</span> database, we are taking advantage of the Derby Network
+Servers' ability to allow multiple client applications, running in different
+JVMs to connect to the same database.
+</p>
+</div>
+
+
+<a name="N1020E"></a><a name="Creating+a+session+to+issue+SQL+and+browse+the+database"></a>
+<h2 class="boxed">Creating a session to issue SQL and browse the database</h2>
+<div class="section">
+<p>
+Double click the <strong>Derby_Client_ClientDB</strong> entry in the <strong>Aliases</strong>
+window which brings up another window. Click the <strong>Connect</strong> button to
+connect to the database and bring up the session window.
+The session window is shown below with the Alias name listed and the username
+I connected with, in this case, as user <strong>slc</strong>.
+</p>
+<p>
+<strong>Figure 4. Creating a session for the Derby_Client_ClientDB alias</strong>
+</p>
+<p>
+<img alt="Creating a session" height="673" src="../images/sq_derby_client_session.gif" width="719"></p>
+<p>
+
+<strong>Executing, editing and running SQL scripts using the SQL tab</strong>
+
+</p>
+<p>
+Before we explore the <strong>Objects</strong> tab, it will be more interesting
+if we create some tables and insert data into our database.
+Unzip the zip file, <span class="codefrag">squirrel_derby.zip</span> to
+a convenient location and locate the file <span class="codefrag">tables_inserts.sql</span>.
+
+Select the <strong>SQL</strong> tab of the session window and then use the
+folder icon in the session window to <strong>Open a file</strong>. This allows
+you to browse to the <span class="codefrag">tables_inserts.sql</span> file and pastes
+the output into the SQL editor area so we can run it.
+The <span class="codefrag">CREATE TABLE</span> statements included in this
+file are shown below (the inserts are omitted.)
+</p>
+<p class="code-block">
+CREATE TABLE CITIES
+(
+CITY_ID INTEGER NOT NULL CONSTRAINT cities_pk PRIMARY KEY,
+CITY_NAME VARCHAR(24) NOT NULL,
+COUNTRY VARCHAR(26) NOT NULL,
+AIRPORT CHAR(3),
+LANGUAGE VARCHAR(16),
+COUNTRY_ISO_CODE CHAR(2)
+);
+
+CREATE TABLE FLIGHTS
+(
+FLIGHT_ID CHAR(6) NOT NULL,
+SEGMENT_NUMBER INTEGER NOT NULL,
+ORIG_AIRPORT CHAR(3),
+DEPART_TIME TIME,
+DEST_AIRPORT CHAR(3),
+ARRIVE_TIME TIME,
+MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT
+CHECK (meal IN ('B', 'L', 'D', 'S')),
+FLYING_TIME DOUBLE PRECISION,
+MILES INTEGER,
+AIRCRAFT VARCHAR(6),
+DEPART_DATE DATE,
+CONSTRAINT FLIGHTS_PK Primary Key (FLIGHT_ID)
+);
+
+CREATE INDEX DESTINDEX ON FLIGHTS (DEST_AIRPORT) ;
+
+CREATE INDEX ORIGINDEX ON FLIGHTS (ORIG_AIRPORT) ;
+
+CREATE TABLE USERS
+(
+FIRSTNAME VARCHAR(40) NOT NULL,
+LASTNAME VARCHAR (40) NOT NULL,
+USERNAME VARCHAR(20) NOT NULL CONSTRAINT username_pk PRIMARY KEY,
+PASSWORD VARCHAR(20) NOT NULL,
+PASSWORD_VERIFY VARCHAR(20),
+EMAIL VARCHAR(30) NOT NULL
+);
+
+CREATE TABLE USER_CREDIT_CARD
+(
+ID INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT user_cc_pk PRIMARY KEY,
+USERNAME varchar(20) NOT NULL,
+LASTNAME varchar(40),
+CREDIT_CARD_TYPE varchar(15) NOT NULL,
+CREDIT_CARD_NUMBER varchar(20) NOT NULL,
+CREDIT_CARD_DISPLAY varchar(25) NOT NULL
+);
+
+ALTER TABLE USER_CREDIT_CARD ADD CONSTRAINT USERNAME_FK
+Foreign Key (username) REFERENCES USERS (username);
+
+CREATE TABLE FLIGHTHISTORY
+(
+ID INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT user_fh_pk PRIMARY KEY,
+USERNAME VARCHAR(20) NOT NULL,
+FLIGHT_ID CHAR(6) NOT NULL,
+ORIG_AIRPORT CHAR(3) NOT NULL,
+DEST_AIRPORT CHAR(3) NOT NULL,
+MILES INTEGER,
+AIRCRAFT VARCHAR(6),
+DEPARTURE_DATE VARCHAR(25),
+CREDIT_CARD_TYPE varchar(15) NOT NULL,
+CREDIT_CARD_DISPLAY varchar(25) NOT NULL
+);
+
+ALTER TABLE FLIGHTHISTORY ADD CONSTRAINT USERNAME_FH_FK
+Foreign Key (username) REFERENCES USERS (username);
+</p>
+<p>
+All of the SQL should be selected (highlighted in grey), if not, select it by
+issuing a <strong>Ctrl + A</strong> and then click the icon of the running man
+(or alternately enter a <strong>Ctrl + Enter</strong>). The image below shows
+what the session window looks like after I successfully executed all of the SQL in the file.
+</p>
+<p>
+<strong>Figure 5. Running SQL commands using the SQL tab</strong>
+</p>
+<p>
+<img alt="Running SQL commands" height="580" src="../images/sq_create_tables.gif" width="750"></p>
+<p>
+We just saw how to import an SQL file to run it, but other options available
+when running SQL commands or SQL scripts from the SQL tab are:
+</p>
+<ul>
+
+<li>
+New File - the icon with the blank page and the cross at the top.
+</li>
+
+<li>
+Appending a file to the editor contents - the icon with the file and the folder.
+</li>
+
+<li>
+Save editor contents to a file - the disk icon.
+</li>
+
+<li>
+Save editor contents to a new file - the disk icon with the ellipsis.
+</li>
+
+<li>
+Print the editor contents - the printer icon.
+</li>
+
+<li>
+Save the file - the 'X' icon.
+</li>
+
+</ul>
+<p>
+Additionally, ad hoc queries and commands can be entered in the SQL
+editor window.
+</p>
+<p>
+
+<strong>Browsing the database with the Objects tab</strong>
+
+</p>
+<p>
+Now that we have data in our <span class="codefrag">ClientDB</span> database, select
+the <strong>Objects</strong> tab of the session window. The first thing you
+need to do to see the tables you just created is to refresh the tree
+structure. Click the <strong>Refresh Database Object Tree</strong>
+ icon (the little yellow arrows) or push <strong>F5</strong>.
+</p>
+<p>
+Select the schema folder which represents the user you connected as, in
+my case this is the <strong>SLC</strong> folder. When connecting to a
+database in Derby, the default schema is the username you connected with.
+Expand this folder and then expand the <strong>TABLE</strong>
+folder. The five tables you just created should appear. Select the
+<span class="codefrag">CITIES</span> table and notice how the tabs on the right change.
+SQuirreL provides a lot of metadata about each database object. Look
+at some of the info provided by the different tabs and then click the
+<strong>Content</strong> tab.
+</p>
+<p>
+Some of the really nice things to note about the content tab are:
+</p>
+<ul>
+
+<li>
+The ability to sort the table (ascending or descending) by column name.
+Just click the column name and an up or down arrow will appear and
+sort it.
+</li>
+
+<li>
+Right-click one row or select all rows to see the variety of options
+available if you want to copy the data - copy as html, copy with headers,
+show row number, etc.
+</li>
+
+<li>
+Right-click one row or all rows to <strong>Make Editable</strong>. This allows
+you to modify the data, insert or delete rows.
+</li>
+
+</ul>
+<p>
+The image below shows what the first row in the <span class="codefrag">CITIES</span> table looks
+like after I have right-clicked it and then single-clicked in this column.
+The column that I am about to edit for this row is the <span class="codefrag">CITY_NAME</span>
+column as shown by the yellow highlighting.
+By double-clicking the value in this column, several other options are available
+for updating or saving the value of this row's column, including importing and
+exporting values to a file or executing an external command. Refer to the
+SQuirreL help system for a detailed explanation on these options.
+</p>
+<p>
+<strong>Figure 6. Making a row editable by right-clicking in a row</strong>
+</p>
+<p>
+<img alt="Making a row editable" height="580" src="../images/sq_make_editable.gif" width="750"></p>
+<p>
+To save an edit issue a <strong>Ctrl + S</strong> prior to exiting the session,
+otherwise your changes will be lost. Also, exiting a session reverts the
+ability to edit the data to the setting which exists in the
+<strong>New Session Properties</strong> setting. The place to set this property
+for new sessions is under the main SQuirreL desktop menu item, <strong>File >> New Session Properties >> General </strong>
+tab. Once there, look in the <strong>Output</strong> area, and the <strong>Table Contents</strong> setting. To make the output for the table default to Not Editable select <strong>Table</strong>. To enable editing by default, select
+<strong>Editable Table</strong>.
+</p>
+<p>
+To set the default to enable editing in the existing session, click the farthest
+left icon on the menu bar and set the property there.
+</p>
+</div>
+
+
+<a name="N102E5"></a><a name="Modifying+data+objects+via+the+Objects+tab"></a>
+<h2 class="boxed">Modifying data objects via the Objects tab</h2>
+<div class="section">
+<p>
+A plethora of options is available when right-clicking one of the
+tables in the <strong>Objects</strong> tab. The available options can
+be grouped into these major categories:
+</p>
+<ul>
+
+<li>
+Modifying the schema object - dropping the table or deleting records.
+</li>
+
+<li>
+Changing the view of the data - refreshing the object tree or specifying a where clause to limit the number of rows retrieved in the content tab.
+</li>
+
+<li>
+Copying the object name - either the fully qualified name or just the short name.
+</li>
+
+<li>
+Creating scripts - create table, create select, drop table, create data or create template data scripts.
+</li>
+
+</ul>
+<p>
+<strong>Figure 7. Options available for table objects</strong>
+</p>
+<p>
+<img alt="table editing options" height="614" src="../images/sq_object_options.gif" width="751"></p>
+<p>
+
+<strong>Extracting and loading data to a table via a local file</strong>
+
+</p>
+<p>
+Let's "unload" the data from the <span class="codefrag">CITIES</span> into an SQL file
+so we can demonstrate how to delete the rows, drop the table,
+recreate the table and then load the rows back. In the current
+version of SQuirreL, V2.2 Final Release, the only way to "unload" the
+data is to create insert statements from the existing data. In the
+2.3 RC1 version of SQuirreL, there will be a way to export the data
+into a CSV file.
+</p>
+<p>
+First select the <strong>SQL</strong> tab from the session window and
+click the <strong>New File</strong> icon. This will clear the current
+SQL window and allow us to create a new file for the insert statements
+we are about to create. Now go back to the <strong>Objects</strong>
+tab. Right-click the <span class="codefrag">CITIES</span> table and select <strong>Create Data Script</strong>. This will automatically put you back in the <strong>SQL</strong>
+tab. Now click the <strong>Save Editor contents to a file</strong>,
+or <strong>Ctrl + S</strong>. Save the file with an SQL extension.
+I've called it <span class="codefrag">cities_inserts.sql</span>.
+</p>
+<p>
+<strong>Deleting the rows from a table</strong>
+</p>
+<p>
+Now go back to the <strong>Objects</strong> tab. Right-click the
+<span class="codefrag">CITIES</span> table again and select <strong>Delete Rows</strong>.
+All of the rows in this table will be removed as well as any rows in
+other tables that are dependent on rows in the <span class="codefrag">CITIES</span>
+table.
+</p>
+<p>
+<strong>Generating a script to create a table</strong>
+</p>
+<p>
+Go back to the <strong>SQL</strong> tab and close the file with the
+insert statements, then go back to the <strong>Objects</strong> tab
+and select <strong>Create Table Script</strong> when right-clicking
+the <span class="codefrag">CITIES</span> table. The <strong>SQL</strong> tab becomes
+active. Save this script to a new file by issuing a <strong>Ctrl + S</strong>
+or clicking the icon. I've called the file <span class="codefrag">create_cities_table.sql</span>.
+Click the <strong>X</strong> icon to close the file.
+</p>
+<p>
+<strong>Dropping a table</strong>
+</p>
+<p>
+Back in the <strong>Objects</strong> table select the <span class="codefrag">CITIES</span>
+table and drop it by right-clicking it and selecting <strong>Drop</strong>.
+</p>
+<p>
+<strong>Running the scripts to recreate and "load" the data</strong>
+</p>
+<p>
+Select the <strong>SQL</strong> tab and select the <strong>Open a File</strong> icon, the folder icon. Browse to the file you created that contains
+the create table statement. Then execute the SQL by clicking on the
+running man icon. The table should be recreated. Close this file
+and open the SQL file that contained the insert statements and run this
+file.
+</p>
+<p>
+Go back to the <strong>Objects</strong> tab and refresh the database
+object tree if necessary to see the <span class="codefrag">CITIES</span> table.
+Once the <span class="codefrag">CITIES</span> table is selected, click the <strong>Content</strong>
+tab to see all of the data you just loaded again!
+</p>
+</div>
+
+
+<a name="N10393"></a><a name="SQuirreL+and+Derby+Resources"></a>
+<h2 class="boxed">SQuirreL and Derby Resources</h2>
+<div class="section">
+<p>
+The <a href="http://db.apache.org/derby/">Derby</a> web site and
+<a class="external" href="http://wiki.apache.org/db-derby/">wiki</a> are the best sources
+of information about Derby.
+</p>
+<p>
+The intent of this document was to discuss using SQuirreL in relation to Derby
+databases and only provides a view which is a subset of the functionality
+available via SQuirreL.
+The documentation that comes with SQuirreL is very complete and provides
+much more information than provided here. The help system, accessible
+via the main menu item <strong>Help > Help</strong>, or as a static html page,
+<span class="codefrag">quick_start.html</span>, is part of the installation under
+the <span class="codefrag">doc</span> directory where you installed SQuirreL.
+</p>
+<p>
+Susan Cline wrote this paper and would appreciate any feedback in
+the form of suggestions, corrections or questions about it by
+posting to the <a class="external" href="mailto:derby-user@db.apache.org">derby-user</a>
+mailing list.
+</p>
+</div>
+
+
+
+<p>
+<em>Last updated: July 14, 2006</em>
+</p>
+
+
+</div>
+<!--+
+ |end content
+ +-->
+<div class="clearboth"> </div>
+</div>
+<div id="footer">
+<!--+
+ |start bottomstrip
+ +-->
+<div class="lastmodified">
+<script type="text/javascript"><!--
+document.write("Last Published: " + document.lastModified);
+// --></script>
+</div>
+<div class="copyright">
+ Copyright ©
+ 2004-2012 Apache Software Foundation</div>
+<div id="feedback">
+ Send feedback about the website to:
+ <a id="feedbackto" href="mailto:derby-user@db.apache.org?subject=Feedback%C2%A0integrate/SQuirreL_Derby.html">derby-user@db.apache.org</a>
+</div>
+<!--+
+ |end bottomstrip
+ +-->
+</div>
+</body>
+</html>
Added: websites/production/db/content/derby/integrate/db_ddlutils.html
==============================================================================
--- websites/production/db/content/derby/integrate/db_ddlutils.html (added)
+++ websites/production/db/content/derby/integrate/db_ddlutils.html Wed Dec 19 18:20:21 2012
@@ -0,0 +1,893 @@
+<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+<html>
+<head>
+<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
+<meta content="Apache Forrest" name="Generator">
+<meta name="Forrest-version" content="0.8">
+<meta name="Forrest-skin-name" content="pelt">
+<title>Using DdlUtils to Migrate Databases to Derby</title>
+<link type="text/css" href="../skin/basic.css" rel="stylesheet">
+<link media="screen" type="text/css" href="../skin/screen.css" rel="stylesheet">
+<link media="print" type="text/css" href="../skin/print.css" rel="stylesheet">
+<link type="text/css" href="../skin/profile.css" rel="stylesheet">
+<script src="../skin/getBlank.js" language="javascript" type="text/javascript"></script><script src="../skin/getMenu.js" language="javascript" type="text/javascript"></script><script src="../skin/fontsize.js" language="javascript" type="text/javascript"></script>
+<link rel="shortcut icon" href="../">
+</head>
+<body onload="init()">
+<script type="text/javascript">ndeSetTextSize();</script>
+<div id="top">
+<!--+
+ |breadtrail
+ +-->
+<div class="breadtrail">
+<a href="http://www.apache.org/">apache</a> > <a href="http://db.apache.org/">db</a><script src="../skin/breadcrumbs.js" language="JavaScript" type="text/javascript"></script>
+</div>
+<!--+
+ |header
+ +-->
+<div class="header">
+<!--+
+ |start group logo
+ +-->
+<div class="grouplogo">
+<a href="http://db.apache.org/derby"><img class="logoImage" alt="Apache Derby" src="../images/derby-logo-web.png" title="Derby is a zero-admin Java RDBMS"></a>
+</div>
+<!--+
+ |end group logo
+ +-->
+<!--+
+ |start Project Logo
+ +-->
+<div class="projectlogoA1">
+<a href="http://db.apache.org"><img class="logoImage" alt="Apache DB Project" src="../images/db-logo-white.png" title="Apache DB creates and maintains database solutions."></a>
+</div>
+<!--+
+ |end Project Logo
+ +-->
+<!--+
+ |start Tabs
+ +-->
+<ul id="tabs">
+<li>
+<a class="unselected" href="../index.html">Home</a>
+</li>
+<li>
+<a class="unselected" href="../quick_start.html">Quick Start</a>
+</li>
+<li>
+<a class="unselected" href="../derby_downloads.html">Download</a>
+</li>
+<li>
+<a class="unselected" href="../derby_comm.html">Community</a>
+</li>
+<li>
+<a class="unselected" href="../manuals/index.html">Documentation</a>
+</li>
+<li class="current">
+<a class="selected" href="../blogs/index.html">Resources</a>
+</li>
+</ul>
+<!--+
+ |end Tabs
+ +-->
+</div>
+</div>
+<div id="main">
+<div id="publishedStrip">
+<!--+
+ |start Subtabs
+ +-->
+<div id="level2tabs"></div>
+<!--+
+ |end Endtabs
+ +-->
+<script type="text/javascript"><!--
+document.write("Last Published: " + document.lastModified);
+// --></script>
+</div>
+<!--+
+ |breadtrail
+ +-->
+<div class="breadtrail">
+
+
+ </div>
+<!--+
+ |start Menu, mainarea
+ +-->
+<!--+
+ |start Menu
+ +-->
+<div id="menu">
+<div onclick="SwitchMenu('menu_1.1', '../skin/')" id="menu_1.1Title" class="menutitle">Blogs and Articles About Derby</div>
+<div id="menu_1.1" class="menuitemgroup">
+<div class="menuitem">
+<a href="../blogs/index.html">Overview</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#blogs">Blogs</a>
+</div>
+<div onclick="SwitchMenu('menu_1.1.3', '../skin/')" id="menu_1.1.3Title" class="menutitle">Articles</div>
+<div id="menu_1.1.3" class="menuitemgroup">
+<div onclick="SwitchMenu('menu_1.1.3.1', '../skin/')" id="menu_1.1.3.1Title" class="menutitle">Tutorials, Tips and Tuning</div>
+<div id="menu_1.1.3.1" class="menuitemgroup">
+<div class="menuitem">
+<a href="../blogs/index.html#getstarted">Getting Started</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#features">Features, Hints and Tips</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#security">Security</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#performance">Performance and Tuning</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.1.3.2', '../skin/')" id="menu_1.1.3.2Title" class="menutitle">Tools and Migration</div>
+<div id="menu_1.1.3.2" class="menuitemgroup">
+<div class="menuitem">
+<a href="../blogs/index.html#tools">Tools</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#migration">Migration</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.1.3.3', '../skin/')" id="menu_1.1.3.3Title" class="menutitle">Applications</div>
+<div id="menu_1.1.3.3" class="menuitemgroup">
+<div class="menuitem">
+<a href="../blogs/index.html#client">Client</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#middletier">Middle Tier</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#persistence">Persistence</a>
+</div>
+<div class="menuitem">
+<a href="../blogs/index.html#scalability">Scalability and Failover</a>
+</div>
+</div>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.2', '../skin/')" id="menu_1.2Title" class="menutitle">Integration With Other Products</div>
+<div id="menu_1.2" class="menuitemgroup">
+<div class="menuitem">
+<a href="../integrate/index.html">Overview</a>
+</div>
+<div class="menuitem">
+<a href="../integrate/index.html#uses">What works with Derby?</a>
+</div>
+<div class="menuitem">
+<a href="../integrate/index.html#products">Product Writeups</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.3', '../skin/')" id="menu_1.3Title" class="menutitle">Eclipse Plug-ins</div>
+<div id="menu_1.3" class="menuitemgroup">
+<div class="menuitem">
+<a href="../integrate/derby_plugin_info.html">Info</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.4', '../skin/')" id="menu_1.4Title" class="menutitle">Papers and Presentations</div>
+<div id="menu_1.4" class="menuitemgroup">
+<div class="menuitem">
+<a href="../papers/index.html">Overview</a>
+</div>
+<div onclick="SwitchMenu('menu_1.4.2', '../skin/')" id="menu_1.4.2Title" class="menutitle">Derby Engine</div>
+<div id="menu_1.4.2" class="menuitemgroup">
+<div onclick="SwitchMenu('menu_1.4.2.1', '../skin/')" id="menu_1.4.2.1Title" class="menutitle">Javadoc</div>
+<div id="menu_1.4.2.1" class="menuitemgroup">
+<div class="menuitem">
+<a href="http://db.apache.org/derby/javadoc/engine">Engine</a>
+</div>
+<div class="menuitem">
+<a href="http://db.apache.org/derby/javadoc/language">Language</a>
+</div>
+<div class="menuitem">
+<a href="http://db.apache.org/derby/javadoc/tools">Tools</a>
+</div>
+<div class="menuitem">
+<a href="http://db.apache.org/derby/javadoc/publishedapi">API</a>
+</div>
+</div>
+<div class="menuitem">
+<a href="../papers/derby_arch.html">Architecture</a>
+</div>
+<div class="menuitem">
+<a href="../papers/btree_package.html">BTree</a>
+</div>
+<div class="menuitem">
+<a href="../papers/pageformats.html">Disk Page Format</a>
+</div>
+<div class="menuitem">
+<a href="../papers/derby_htw.html">How Things Work</a>
+</div>
+<div class="menuitem">
+<a href="../papers/Intersect-design.html">Intersect & Except</a>
+</div>
+<div class="menuitem">
+<a href="../papers/JDBCImplementation.html">JDBC</a>
+</div>
+<div class="menuitem">
+<a href="../papers/logformats.html">Log Format</a>
+</div>
+<div class="menuitem">
+<a href="../papers/recovery.html">Logging & Recovery</a>
+</div>
+<div class="menuitem">
+<a href="../papers/optimizer.html">Optimizer</a>
+</div>
+<div class="menuitem">
+<a href="http://db.apache.org/derby/javadoc/engine/org/apache/derby/iapi/types/package-summary.html#package_description">Type System</a>
+</div>
+<div class="menuitem">
+<a href="../papers/versionupgrade.html">Versioning</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.4.3', '../skin/')" id="menu_1.4.3Title" class="menutitle">Derby Network Client</div>
+<div id="menu_1.4.3" class="menuitemgroup">
+<div class="menuitem">
+<a href="../papers/DerbyClientSpec.html">Functional Spec</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.4.4', '../skin/')" id="menu_1.4.4Title" class="menutitle">Derby Tutorial</div>
+<div id="menu_1.4.4" class="menuitemgroup">
+<div class="menuitem">
+<a href="../papers/DerbyTut/index.html">Overview</a>
+</div>
+<div class="menuitem">
+<a href="../papers/DerbyTut/install_software.html">Step 1: Install Software</a>
+</div>
+<div class="menuitem">
+<a href="../papers/DerbyTut/ij_intro.html">Step 2: ij Basics</a>
+</div>
+<div class="menuitem">
+<a href="../papers/DerbyTut/embedded_intro.html">Step 3: Embedded Derby</a>
+</div>
+<div class="menuitem">
+<a href="../papers/DerbyTut/ns_intro.html">Step 4: Derby Network Server</a>
+</div>
+</div>
+<div onclick="SwitchMenu('menu_1.4.5', '../skin/')" id="menu_1.4.5Title" class="menutitle">Presentations</div>
+<div id="menu_1.4.5" class="menuitemgroup">
+<div class="menuitem">
+<a href="../papers/ApacheCon.html">ApacheCon</a>
+</div>
+<div class="menuitem">
+<a href="../papers/MiscPresentations.html#Victorian+Java+User+Group">Victorian JUG 2008</a>
+</div>
+<div class="menuitem">
+<a href="../papers/MiscPresentations.html#OSCON+2005">OSCON 2005</a>
+</div>
+<div class="menuitem">
+<a href="../papers/MiscPresentations.html#Colorado+Software+Summit+2004">Colorado 2004</a>
+</div>
+</div>
+</div>
+<!--+
+ |start Search
+ +-->
+<div class="searchbox">
+<hr>
+<form action="http://www.google.com/search" method="get">
+<input value="db.apache.org" name="sitesearch" type="hidden"><input onFocus="getBlank (this, 'Search the site with google');" size="18" name="q" id="query" type="text" value="Search the site with google">
+ <input name="Search" value="Search" type="submit">
+</form>
+</div>
+<!--+
+ |end search
+ +-->
+<div id="credit"></div>
+<div id="roundbottom">
+<img style="display: none" class="corner" height="15" width="15" alt="" src="../skin/images/rc-b-l-15-1body-2menu-3menu.png"></div>
+<!--+
+ |alternative credits
+ +-->
+<div id="credit2"></div>
+</div>
+<!--+
+ |end Menu
+ +-->
+<!--+
+ |start content
+ +-->
+<div id="content">
+<div class="trail">Font size:
+ <input value="Reset" class="resetfont" title="Reset text" onclick="ndeSetTextSize('reset'); return false;" type="button">
+ <input value="-a" class="smallerfont" title="Shrink text" onclick="ndeSetTextSize('decr'); return false;" type="button">
+ <input value="+a" class="biggerfont" title="Enlarge text" onclick="ndeSetTextSize('incr'); return false;" type="button">
+</div>
+<h1>Using DdlUtils to Migrate Databases to Derby</h1>
+<div id="minitoc-area">
+<ul class="minitoc">
+<li>
+<a href="#Overview">Overview</a>
+<ul class="minitoc">
+<li>
+<a href="#Target+Audience">Target Audience</a>
+</li>
+<li>
+<a href="#Prerequisites">Prerequisites</a>
+</li>
+</ul>
+</li>
+<li>
+<a href="#Step+1%3A+Setup+a+working+directory">Step 1: Setup a working directory</a>
+</li>
+<li>
+<a href="#Step+2%3A+Build+and+Install+DdlUtils">Step 2: Build and Install DdlUtils</a>
+</li>
+<li>
+<a href="#Step+3%3A+Create+a+sample+database%3A+Books1">Step 3: Create a sample database: Books1 </a>
+</li>
+<li>
+<a href="#Step+4%3A+Books1+--+Export+Schema+and+Data">Step 4: Books1 -- Export Schema and Data </a>
+<ul class="minitoc">
+<li>
+<a href="#Create+an+Ant+build.xml+file+with+an+export+target">Create an Ant build.xml file with an export target</a>
+</li>
+<li>
+<a href="#Execute+the+Ant+task">Execute the Ant task</a>
+</li>
+</ul>
+</li>
+<li>
+<a href="#Step+5%3A+Books2+--+Create+Database+and+Import+Schema+and+Data">Step 5: Books2 -- Create Database and Import Schema and Data</a>
+<ul class="minitoc">
+<li>
+<a href="#Copy+and+%28maybe%29+modify+the+db-schema.xml+file">Copy and (maybe) modify the db-schema.xml file </a>
+</li>
+<li>
+<a href="#Add+an+import+target+to+the+build.xml">Add an import target to the build.xml</a>
+</li>
+<li>
+<a href="#Execute+the+Ant+task-N10228">Execute the Ant task</a>
+</li>
+</ul>
+</li>
+<li>
+<a href="#Step+6%3A+Verify+Migration">Step 6: Verify Migration</a>
+</li>
+<li>
+<a href="#Caveats">Caveats</a>
+</li>
+<li>
+<a href="#Questions%3F">Questions?</a>
+</li>
+</ul>
+</div>
+
+
+<a name="N1000D"></a><a name="Overview"></a>
+<h2 class="boxed">Overview</h2>
+<div class="section">
+<p>
+
+<a href="http://db.apache.org/ddlutils">DdlUtils</a>, an Apache DB subproject,
+provides utilities for creating and working with Database Definition (DDL) files
+and for exporting and importing data in XML format
+<em>via</em> Ant tasks or programmatically in Java.
+</p>
+<p>
+Among other uses, DdlUtils makes it easier to migrate
+from one database vendor to another.
+This paper provides a simple example for using DdlUtils to migrate a database
+to Derby using the
+<a href="http://db.apache.org/ddlutils/ant/index.all.html">DdlUtils Ant tasks</a>.
+</p>
+<p>
+This example features Derby as both the source and target database
+because Derby is easily available and is what the author knows, but it can
+be used with any of the databases that
+<a href="http://db.apache.org/ddlutils/database-support.html">DdlUtils
+supports</a>.
+Usually there's an even easier way to migrate a Derby database from
+one machine to another: just copy the Derby database files.
+If the source and target machines use official Derby releases
+you don't need to export/import the schema and data, not even for
+machines with different architectures,
+because the files that make up a Derby database are platform-independent.
+However, if you want to upgrade a Derby database to or from an alpha
+version of Derby to an official release,
+you must export the schema and data because Derby doesn't support
+<a class="external" href="http://wiki.apache.org/db-derby/UpgradingTen">upgrading</a>
+an alpha database.
+DdlUtils makes this migration easier.
+</p>
+<a name="N1002F"></a><a name="Target+Audience"></a>
+<h3 class="boxed">Target Audience</h3>
+<p>DdlUtils doesn't have an official release yet,
+ so this example is intended for developers -- and for users who have a
+ developer spirit.
+ This project walks
+ you through checking the DdlUtils code out of the Apache Subversion
+ repository and building it using the Ant build tool.
+ You don't need to be a java developer, in fact you don't need
+ to know any Java at all,
+ but you should be comfortable installing software, setting environment
+ variables, and troubleshooting
+ any problems that might occur.
+ </p>
+<a name="N10039"></a><a name="Prerequisites"></a>
+<h3 class="boxed">Prerequisites</h3>
+<p>This project assumes a basic knowledge of Derby.
+ If you're new, start with the on-line
+ <a href="http://db.apache.org/derby/papers/DerbyTut/index.html">Apache
+ Derby Tutorial</a>.
+ Familiarity with XML is helpful, but not necessary.
+ You also need the software listed below:
+ </p>
+<ul>
+
+<li>Install the most current official release of
+ <a href="http://db.apache.org/derby/derby_downloads.html">Apache Derby</a>. </li>
+
+<li>Install <a class="external" href="http://ant.apache.org/">Apache Ant</a> version
+ 1.6.
+ Set the <span class="codefrag">ANT_HOME</span> environment variable and
+ add the Ant <span class="codefrag">bin</span> directory to your <span class="codefrag">PATH</span>.
+ </li>
+
+<li> Install <a class="external" href="http://subversion.tigris.org/">Subversion</a>
+ and add the Subversion <span class="codefrag">bin</span> directory to your
+ <span class="codefrag">PATH</span>.
+ </li>
+
+</ul>
+</div>
+
+
+<a name="N1006F"></a><a name="Step+1%3A+Setup+a+working+directory"></a>
+<h2 class="boxed">Step 1: Setup a working directory</h2>
+<div class="section">
+<p>
+ Somewhere on your computer create a working directory, then <span class="codefrag">cd</span>
+ into it.
+ This is where you'll check out and build DdlUtils,
+ create the Derby databases,
+ <em>etc</em>.
+ Also create a <span class="codefrag">lib</span> subdirectory; this is where you'll install
+ the DdlUtils software.
+ </p>
+<p class="code-block">mkdir DdlUtils-test
+cd DdlUtils-test
+mkdir lib</p>
+</div>
+
+
+<a name="N10086"></a><a name="Step+2%3A+Build+and+Install+DdlUtils"></a>
+<h2 class="boxed">Step 2: Build and Install DdlUtils</h2>
+<div class="section">
+<p>
+ Check out the DdlUtils repository as shown below:
+ </p>
+<p class="code-block">svn co http://svn.apache.org/repos/asf/db/ddlutils/trunk ddlutils</p>
+<p>After this command completes, you should have a local working copy of the
+ DdlUtils code in a subdirectory called <span class="codefrag">ddlutils</span>.
+ Change directory into that subdirectory and build the DdlUtils jar file
+ as shown below:
+ </p>
+<p class="code-block">cd ddlutils
+ant jar</p>
+<p>You might see some deprecation warnings in the build output, but don't
+ worry about those as long as at the end you see a
+ <span class="codefrag">BUILD SUCCESSFUL</span> message.
+ The resulting jar will be output to a newly created
+ <span class="codefrag">dist</span> subdirectory:
+ </p>
+<p class="code-block">
+<strong>UNIX</strong>
+$ ls dist
+DdlUtils-1.0-dev.jar
+
+<strong>Windows</strong>
+C:\jta\DdlUtils-test\ddlutils>dir dist
+ Volume in drive C has no label.
+ Volume Serial Number is 8017-F8A8
+
+ Directory of C:\jta\DdlUtils-test\ddlutils\dist
+
+03/10/2006 12:38 PM <DIR> .
+03/10/2006 12:38 PM <DIR> ..
+03/10/2006 12:38 PM 252,846 DdlUtils-1.0-dev.jar
+ 1 File(s) 252,846 bytes
+ 2 Dir(s) 66,830,884,864 bytes free</p>
+<p>Copy files from the <span class="codefrag">dist</span> and <span class="codefrag">lib</span> directories
+ into the <span class="codefrag">lib</span> directory you created in
+ Step 1, then <span class="codefrag">cd</span> back up to your working directory:</p>
+<p class="code-block">
+<strong>UNIX</strong>
+cp dist/*.jar ../lib
+cp lib/*.jar ../lib
+cd ..
+
+<strong>Windows</strong>
+copy dist\*.jar ..\lib
+copy lib\*.jar ..\lib
+cd .. </p>
+</div>
+
+
+
+<a name="N100C8"></a><a name="Step+3%3A+Create+a+sample+database%3A+Books1"></a>
+<h2 class="boxed">Step 3: Create a sample database: Books1 </h2>
+<div class="section">
+<p>
+This section creates the database that the
+<a href="http://db.apache.org/torque/releases/torque-3.2/releases/torque-3.2/tutorial/index.html">Apache Torque Tutorial</a>
+uses,
+but you'll use Derby's <span class="codefrag">ij</span> utility to create it instead.
+</p>
+<p>
+Include <span class="codefrag">derby.jar</span> and
+<span class="codefrag">derbytools.jar</span> in your <span class="codefrag">CLASSPATH</span>
+(<a href="http://db.apache.org/derby/papers/DerbyTut/install_software.html#derby_configure">the on-line Derby tutorial shows how to do this</a>), then create the
+database:
+</p>
+<p class="code-block">java org.apache.derby.tools.ij
+connect 'jdbc:derby:Books1;create=true'; </p>
+<p>
+Cut and paste the statements below to create the tables:
+</p>
+<p class="code-block">CREATE TABLE publisher
+(
+ publisher_id INTEGER NOT NULL,
+ name VARCHAR(128) NOT NULL,
+ PRIMARY KEY(publisher_id));
+
+CREATE TABLE author
+(
+ author_id INTEGER NOT NULL,
+ first_name VARCHAR(128) NOT NULL,
+ last_name VARCHAR(128) NOT NULL,
+ PRIMARY KEY(author_id));
+
+CREATE TABLE book
+(
+ book_id INTEGER NOT NULL,
+ title VARCHAR(255) NOT NULL,
+ isbn VARCHAR(24) NOT NULL,
+ publisher_id INTEGER NOT NULL,
+ author_id INTEGER NOT NULL,
+ PRIMARY KEY(book_id),
+ FOREIGN KEY (publisher_id) REFERENCES publisher (publisher_id)
+ ,
+ FOREIGN KEY (author_id) REFERENCES author (author_id)
+ );</p>
+<p>
+Cut and paste the statements below to insert some data:
+</p>
+<p class="code-block">insert into publisher
+ values (1, 'Addison Wesley Professional');
+insert into author
+ values (11, 'Joshua', 'Bloch');
+insert into author
+ values (12, 'W.', 'Stevens');
+insert into book
+ values (101, 'Effective Java Programming Language Guide', '0-201-31005-8', 1, 11);
+insert into book
+ values (102, 'TCP/IP Illustrated, Volume 1', '0-201-63346-9', 1, 12);
+</p>
+<p>
+Finally, exit the database:
+</p>
+<p class="code-block">exit;</p>
+</div>
+
+
+<a name="N10102"></a><a name="Step+4%3A+Books1+--+Export+Schema+and+Data"></a>
+<h2 class="boxed">Step 4: Books1 -- Export Schema and Data </h2>
+<div class="section">
+<p>
+This step uses the DdlUtils <span class="codefrag">DatabaseToDdlTask</span>
+to dump the <span class="codefrag">Books1</span> database to disk.
+You'll use the
+<a href="http://db.apache.org/ddlutils/ant/index.all.html">DdlUtils Ant tasks</a>
+to execute the export.
+</p>
+<a name="N10115"></a><a name="Create+an+Ant+build.xml+file+with+an+export+target"></a>
+<h3 class="boxed">Create an Ant build.xml file with an export target</h3>
+<p>
+Complete source for the Ant build file is below.
+Cut and paste it into a file called <span class="codefrag">build.xml</span>:
+</p>
+<p class="code-block"><?xml version="1.0"?>
+<project name="MigrateToDerby" basedir=".">
+
+ <path id="classpath">
+ <fileset dir="./lib">
+ <include name="**/*.jar"/>
+ </fileset>
+ </path>
+
+ <target name="export-source-db" description="Dumps db structure and data">
+ <taskdef name="databaseToDdl"
+ classname="org.apache.ddlutils.task.DatabaseToDdlTask">
+ <classpath refid="classpath"/>
+ </taskdef>
+
+ <databaseToDdl modelName="MigrateTest">
+ <database url="jdbc:derby:Books1"
+ driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
+ username=""
+ password=""/>
+
+ <writeSchemaToFile outputFile="db-schema.xml"/>
+ <writeDataToFile outputFile="data.xml"/>
+ </databaseToDdl>
+
+ </target>
+
+</project> </p>
+<p>
+Here are a few notes about the <span class="codefrag">build.xml</span> source:
+</p>
+<ul>
+
+<li>In Step 2 you copied all the jars from <span class="codefrag">ddlutils/lib</span> into the
+ <span class="codefrag">lib</span> directory of the working directory.
+ Rather than add them to the <span class="codefrag">CLASSPATH</span> variable,
+ the Ant <span class="codefrag">path</span> element
+ adds all jars in that subdirectory to the runtime environment.
+ The <span class="codefrag">path</span> element is available to any target in this file;
+ right now, there's just one target
+ (<span class="codefrag">export-source-db</span>), but Step 5 will add another.
+ </li>
+
+<li>The <span class="codefrag">export-source-db</span> target exports the schema and data
+ from a database.
+ <ul>
+
+<li>
+<span class="codefrag">modelName</span> specifies a name for the model and will
+ be assigned to the <span class="codefrag">name</span> attribute in the generated
+ schema file.
+ </li>
+
+<li>
+<span class="codefrag">database url</span> specifies all the connection parameters
+ for the database, including the <span class="codefrag">Books1</span> database.
+ This example uses the Derby Embedded JDBC driver (<span class="codefrag">derby.jar</span>),
+ but it could just
+ as easily use the Derby Network Client JDBC driver
+ (<span class="codefrag">derbyclient.jar</span>).</li>
+
+<li>
+<span class="codefrag">writeSchemaToFile</span> exports the schema to a file
+ called <span class="codefrag">db-schema.xml</span>.</li>
+
+<li>
+<span class="codefrag">writeDataToFile</span> exports the data to a file
+ called <span class="codefrag">data.xml</span>.</li>
+
+</ul>
+
+</li>
+
+</ul>
+<a name="N10173"></a><a name="Execute+the+Ant+task"></a>
+<h3 class="boxed">Execute the Ant task</h3>
+<p>
+Now execute the task with <span class="codefrag">ant</span>:
+</p>
+<p class="code-block">ant export-source-db</p>
+<p>
+A <span class="codefrag">BUILD SUCCESSFUL</span> message indicates that that the export
+succeeded and there should be two new files in your current working directory:
+</p>
+<ul>
+
+<li>
+<span class="codefrag">db-schema.xml</span> contains the database schema in XML format.</li>
+
+<li>
+<span class="codefrag">data.xml</span> contains the data in XML format.</li>
+
+</ul>
+</div>
+
+
+<a name="N10198"></a><a name="Step+5%3A+Books2+--+Create+Database+and+Import+Schema+and+Data"></a>
+<h2 class="boxed">Step 5: Books2 -- Create Database and Import Schema and Data</h2>
+<div class="section">
+<p>
+This step uses the DdlUtils <span class="codefrag">DdlToDatabaseTask</span>
+to import the schema and data into a new <span class="codefrag">Books2</span> database.
+As with the export task, you'll use the
+<a href="http://db.apache.org/ddlutils/ant/index.all.html">DdlUtils Ant tasks</a>
+to execute the import.
+</p>
+<a name="N101AB"></a><a name="Copy+and+%28maybe%29+modify+the+db-schema.xml+file"></a>
+<h3 class="boxed">Copy and (maybe) modify the db-schema.xml file </h3>
+<p>
+The export step created a file called <span class="codefrag">db-schema.xml</span>. Copy that
+to a new file called <span class="codefrag">project-schema.xml</span>:
+</p>
+<p class="code-block">
+<strong>UNIX</strong>
+cp db-schema.xml project-schema.xml
+
+<strong>Windows</strong>
+copy db-schema.xml project-schema.xml</p>
+<p>
+Since Derby is both the source and target databases in this example, no
+changes to the generated schema file are required.
+However, changes might be required if the source database is not Derby,
+for example, to change the mapping of a data type:
+</p>
+<ul>
+
+<li> Derby does not support the <span class="codefrag">BOOLEAN</span> type
+ (<a class="external" href="http://issues.apache.org/jira/browse/DERBY-499">yet</a>).
+ Use a SQL <a class="external" href="http://mail-archives.apache.org/mod_mbox/db-derby-user/200412.mbox/%3c41C052AB.4050804@debrunners.com%3e"><span class="codefrag">SMALLINT</span></a>
+ instead.
+ </li>
+
+<li> A Derby user
+ <a class="external" href="http://mail-archives.apache.org/mod_mbox/db-derby-user/200603.mbox/%3c007e01c64635$4ae17b60$dd00a8c0@itsdev.infotechsoft.com%3e">posted</a>
+ that he edited the generated schema file to change some
+ <span class="codefrag">LONG VARCHAR</span>s in the source database
+ to a <span class="codefrag">VARCHAR(32672)</span> to retain search ability
+ and others to <span class="codefrag">CLOB</span> to retain long text > 32,672
+ characters.
+</li>
+
+</ul>
+<a name="N101E9"></a><a name="Add+an+import+target+to+the+build.xml"></a>
+<h3 class="boxed">Add an import target to the build.xml</h3>
+<p>
+Copy the source for the <span class="codefrag">import-target-db</span> target below
+and add it to your <span class="codefrag">build.xml</span> file:
+</p>
+<p class="code-block"> <target name="import-target-db" description="Creates db and loads data">
+ <taskdef name="ddlToDatabase"
+ classname="org.apache.ddlutils.task.DdlToDatabaseTask">
+ <classpath refid="classpath"/>
+ </taskdef>
+
+ <ddlToDatabase schemaFile="project-schema.xml">
+
+ <database url="jdbc:derby:Books2"
+ driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
+ username=""
+ password=""/>
+
+ <createDatabase failonerror="false"/>
+ <writeSchemaToDatabase/>
+ <writeDataToDatabase datafile="data.xml"/>
+
+ </ddlToDatabase>
+ </target> </p>
+<p>
+Here are a few notes about the source:
+</p>
+<ul>
+
+<li> There is just one file for the schema, so it can be specified as an
+ argument to the <span class="codefrag">ddlToDatabase</span> task using the
+ <span class="codefrag">schemaFile</span> argument.
+ You can also use an embedded <span class="codefrag">fileset</span> for multiple files --
+ the
+ <a href="http://db.apache.org/ddlutils/ant/index.all.html">DdlUtils
+ site</a> shows an example using that syntax.
+ </li>
+
+<li> The <span class="codefrag">createDatabase</span> subtask creates the Derby database.
+ </li>
+
+<li> The <span class="codefrag">writeSchemaToDatabase</span> subtask creates the schema
+ from the file specified by <span class="codefrag">schemaFile</span>.
+ </li>
+
+<li> The <span class="codefrag">writeDataToDatabase</span> subtask loads the data from the
+ specified data file.
+ </li>
+
+</ul>
+<a name="N10228"></a><a name="Execute+the+Ant+task-N10228"></a>
+<h3 class="boxed">Execute the Ant task</h3>
+<p>
+Execute the task with <span class="codefrag">ant</span>:
+</p>
+<p class="code-block">ant import-target-db</p>
+<p>Successful execution should look something like this:</p>
+<p class="code-block">ant import-target-db
+Buildfile: build.xml
+
+import-target-db:
+[ddlToDatabase] Read schema file /home/jta/DerbyDb/DdlUtils/project-schema.xml
+[ddlToDatabase] Created database
+[ddlToDatabase] Mar 10, 2006 4:03:05 PM org.apache.ddlutils.platform.SqlBuilder alterDatabase
+[ddlToDatabase] INFO: Creating table AUTHOR
+[ddlToDatabase] Mar 10, 2006 4:03:05 PM org.apache.ddlutils.platform.SqlBuilder alterDatabase
+[ddlToDatabase] INFO: Creating table BOOK
+[ddlToDatabase] Mar 10, 2006 4:03:05 PM org.apache.ddlutils.platform.SqlBuilder alterDatabase
+[ddlToDatabase] INFO: Creating table PUBLISHER
+[ddlToDatabase] Mar 10, 2006 4:03:06 PM org.apache.ddlutils.platform.PlatformImplBase evaluateBatch
+[ddlToDatabase] INFO: Executed 5 SQL command(s) with 0 error(s)
+[ddlToDatabase] Written schema to database
+[ddlToDatabase] Read data file /home/jta/DerbyDb/DdlUtils/data.xml
+
+BUILD SUCCESSFUL
+Total time: 4 seconds </p>
+</div>
+
+
+<a name="N10241"></a><a name="Step+6%3A+Verify+Migration"></a>
+<h2 class="boxed">Step 6: Verify Migration</h2>
+<div class="section">
+<p>
+You should now have two databases.
+</p>
+<p>
+One way to verify the schema in the target Derby database is to examine
+it with <a href="http://db.apache.org/derby/docs/dev/tools/ctoolsusingdblook.html">dblook</a>:
+</p>
+<p class="code-block">java org.apache.derby.tools.dblook -d jdbc:derby:Books2 -o Books2_schema.sql</p>
+<p>
+To verify the data was imported correctly, query the data in both databases:
+</p>
+<p class="code-block">ij> select * from publisher;
+ij> select * from author;
+ij> select * from book;
+</p>
+</div>
+
+
+<a name="N1025D"></a><a name="Caveats"></a>
+<h2 class="boxed">Caveats</h2>
+<div class="section">
+<p>
+The DdlUtils model uses JDBC datatypes exclusively,
+which can be an issue for older drivers.
+Be sure to review
+DdlUtils database support on the
+<a href="http://db.apache.org/ddlutils/database-support.html">DdlUtils site</a>.
+</p>
+</div>
+
+
+<a name="N1026B"></a><a name="Questions%3F"></a>
+<h2 class="boxed">Questions?</h2>
+<div class="section">
+<p>Please post questions about this example to
+ <a href="../derby_mail.html">derby-user <at> db.apache.org</a>.
+ Please post questions about DdlUtils to
+ <a href="http://db.apache.org/ddlutils/mail-lists.html">ddlutils-user <at> db.apache.org</a>.
+</p>
+</div>
+
+
+<p>
+<em>Last Updated: March 16, 2006</em>
+</p>
+
+
+</div>
+<!--+
+ |end content
+ +-->
+<div class="clearboth"> </div>
+</div>
+<div id="footer">
+<!--+
+ |start bottomstrip
+ +-->
+<div class="lastmodified">
+<script type="text/javascript"><!--
+document.write("Last Published: " + document.lastModified);
+// --></script>
+</div>
+<div class="copyright">
+ Copyright ©
+ 2004-2012 Apache Software Foundation</div>
+<div id="feedback">
+ Send feedback about the website to:
+ <a id="feedbackto" href="mailto:derby-user@db.apache.org?subject=Feedback%C2%A0integrate/db_ddlutils.html">derby-user@db.apache.org</a>
+</div>
+<!--+
+ |end bottomstrip
+ +-->
+</div>
+</body>
+</html>