| <html> |
| <head> |
| <META http-equiv="Content-Type" content="text/html; charset=UTF-8"> |
| <title>HyperSQL Utilities Guide</title> |
| <link href="../docbook.css" rel="stylesheet" type="text/css"> |
| <meta content="DocBook XSL-NS Stylesheets V1.74.0" name="generator"> |
| <meta name="keywords" content="HyperSQL, Hsqldb, Hypersonic, Database, JDBC, Java"> |
| </head> |
| <body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"> |
| <div class="book" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h1 class="title"> |
| <a name="N10002"></a>HyperSQL Utilities Guide</h1> |
| </div> |
| <table xmlns:xi="http://www.w3.org/2001/XInclude" class="titlead" cellspacing="0"> |
| <tr> |
| <td> |
| <div> |
| <div class="authorgroup"> |
| <div class="editor"> |
| <h4 class="editedby">Edited by</h4> |
| <h3 class="editor"> |
| <span class="orgname">The HSQLB Development Group</span> |
| </h3> |
| </div> |
| <div class="editor"> |
| <h4 class="editedby">Edited by</h4> |
| <h3 class="editor"> |
| <span class="firstname">Blaine</span> <span class="surname">Simpson</span> |
| </h3> |
| <div class="affiliation"> |
| <span class="orgname">The HSQL Development Group<br> |
| </span> |
| </div> |
| </div> |
| <div class="editor"> |
| <h4 class="editedby">Edited by</h4> |
| <h3 class="editor"> |
| <span class="firstname">Fred</span> <span class="surname">Toussi</span> |
| </h3> |
| <div class="affiliation"> |
| <span class="orgname">The HSQL Development Group<br> |
| </span> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div> |
| <p class="releaseinfo">$Revision: 3539 $</p> |
| </div> |
| <div> |
| <div class="legalnotice"> |
| <a name="N10037"></a> |
| <p>Copyright 2002-2009 The HSQL Development Group. Permission is |
| granted to distribute this document without any alteration under the |
| terms of the HSQLDB license.</p> |
| </div> |
| </div> |
| <div> |
| <p class="pubdate">$Date: 2010-03-17 11:47:21 -0400 (Wed, 17 Mar 2010) $</p> |
| </div> |
| </td><td class="sponsorad"> |
| <div xml:base="../doc-src/branding-frag.xhtml" class="branding"> |
| <img src="../images/hypersql_logo.png"></div> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <hr> |
| </div> |
| <div class="toc"> |
| <p> |
| <b>Table of Contents</b> |
| </p> |
| <dl> |
| <dt> |
| <span class="preface"><a href="#book-pref">Preface</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#altformats-sect">Available formats for this document</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="chapter"><a href="#sqltool-chapt">1. SqlTool</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#sqltool_book_purpose-sect">Purpose, Coverage, Changes in Behavior</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N100FB">Platforms and SqlTool versions covered</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N1011D">Recent Functional Changes</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10188">New Features</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_baremin-sect">The Bare Minimum</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N102AE">Embedding</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N102C3">Non-displayable Types</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N102E3">Desktop shortcuts</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10344">Loading sample data</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#N10360">Satisfying SqlTool's CLASSPATH Requirements</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#sqltool_olderaccess-sect"> |
| Accessing older HSQLDB Databases with SqlTool</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N103BC">App-specific Classes, Embedding, and non-HyperSQL Databases</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10403">Distributing SqlTool with your Apps</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10429">SqlTool Client PCs</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_auth-sect">RC File Authentication Setup</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_dsswitch-sect">Switching Data Sources</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_ilauth-sect">Using Inline RC Authentication</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_logging-sect">Logging</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_int-sect">Interactive Usage</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N10578">SqlTool Command-Line Editing</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N1059C">Command Types</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#N105B5">Command Types</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N105FB">Special Commands</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N106CC">Edit Buffer / History Commands</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_interactive_pl_commands-sect">PL Commands</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N107C2">? Variable</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_binary_files-sect"> |
| Storing and retrieving binary files</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N1080C">Command History</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10816">Shell scripting and command-line piping</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N1081F">Emulating Non-Interactive mode</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_nonint-sect">Non-Interactive</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#sqltool_sqlswitch-sect">Giving SQL on the Command Line</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10871">SQL Files</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_scripting-sect">Piping and shell scripting</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N108E1">Optimally Compatible SQL Files</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N108F4">Comments</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10901">Special Commands and Edit Buffer Commands in SQL Files</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10965">Automation</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10970">Getting Interactive Functionality with SQL Files</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_charencoding-sect"> |
| Character Encoding</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_report-sect">Generating Text or HTML Reports</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_pl-sect">SqlTool Procedural Language</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N10A08">Variables</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_macro-sect">Macros</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10A67">PL Sample</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10A78">Logical Expressions</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10AEB">Flow Control</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10B0E">Example</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_chunk-sect">Chunking</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N10B2C">Why?</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10B3D">How?</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_raw-sect">Raw Mode</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_embedded-langs-sect">SQL/PSM, SQL/JRT, and PL/SQL</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_dsv-sect">Delimiter-Separated-Value Imports and Exports</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N10BE4">Simple DSV exports and imports using default settings</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10C1F">Specifying queries and options</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_unittest-sect">Unit Testing SqlTool</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="chapter"><a href="#test-utility-chapt">2. Hsqldb Test Utility</a></span> |
| </dt> |
| <dt> |
| <span class="chapter"><a href="#dbm-chapt">3. Database Manager</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#dbm_intro-sect">Brief Introduction</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#dbm_tree-sect">Auto tree-update</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#dbm-autoconn-sect">Automatic Connection</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#dbm_rcfile-sect">RC File</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#dbm_wold-sect">Using the current DatabaseManagers with an older HSQLDB |
| distribution.</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#dbm_applet-sect">DatabaseManagerSwing as an Applet</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="chapter"><a href="#transfer-tool-chapt">4. Transfer Tool</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#trantool_intro-sect">Brief Introduction</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="appendix"><a href="#N10E8E">A. HyperSQL File Links</a></span> |
| </dt> |
| </dl> |
| </div> |
| <div class="list-of-tables"> |
| <p> |
| <b>List of Tables</b> |
| </p> |
| <dl> |
| <dt>1. <a href="#altformats-tbl">Available formats of this document</a> |
| </dt> |
| </dl> |
| </div> |
| <div class="list-of-examples"> |
| <p> |
| <b>List of Examples</b> |
| </p> |
| <dl> |
| <dt>1.1. <a href="#N10459">Sample RC File</a> |
| </dt> |
| <dt>1.2. <a href="#N107FC">Inserting binary data into database from a file</a> |
| </dt> |
| <dt>1.3. <a href="#N10801">Downloading binary data from database to a file</a> |
| </dt> |
| <dt>1.4. <a href="#N108CC">Piping input into SqlTool</a> |
| </dt> |
| <dt>1.5. <a href="#N10A6C">Simple SQL file using PL</a> |
| </dt> |
| <dt>1.6. <a href="#N10B1D">SQL File showing use of most PL features</a> |
| </dt> |
| <dt>1.7. <a href="#N10B5F">Interactive Raw Mode example</a> |
| </dt> |
| <dt>1.8. <a href="#N10B96">PL/SQL Example</a> |
| </dt> |
| <dt>1.9. <a href="#N10BA2">SQL/JRT Example</a> |
| </dt> |
| <dt>1.10. <a href="#N10BAC">SQL/PSM Example</a> |
| </dt> |
| <dt>1.11. <a href="#N10BF4">DSV Export Example</a> |
| </dt> |
| <dt>1.12. <a href="#N10C13">DSV Import Example</a> |
| </dt> |
| <dt>1.13. <a href="#N10C72">DSV Export of an Arbitrary SELECT Statement</a> |
| </dt> |
| <dt>1.14. <a href="#N10C8B">Sample DSV headerswitch settings</a> |
| </dt> |
| <dt>1.15. <a href="#N10C91">DSV targettable setting</a> |
| </dt> |
| </dl> |
| </div> |
| <div class="preface" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title"> |
| <a name="book-pref"></a>Preface</h2> |
| </div> |
| </div> |
| </div> |
| <div class="toc"> |
| <p> |
| <b>Table of Contents</b> |
| </p> |
| <dl> |
| <dt> |
| <span class="section"><a href="#altformats-sect">Available formats for this document</a></span> |
| </dt> |
| </dl> |
| </div> |
| <p>If you notice any mistakes in this document, please email the author |
| listed at the beginning of the chapter. If you have problems with the |
| procedures themselves, please use the HSQLDB support facilities which are |
| listed at <a class="link" href="http://hsqldb.org/web/hsqlSupport.html" target="_top">http://hsqldb.org/web/hsqlSupport.html</a>.</p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="altformats-sect"></a>Available formats for this document</h2> |
| </div> |
| </div> |
| </div> |
| <p>This document is available in several formats.</p> |
| <p> |
| You may be reading this document right now at http://hsqldb.org/doc/2.0, or in |
| a distribution somewhere else. |
| I hereby call the document distribution from which you are reading |
| this, your <span class="emphasis"><em>current distro</em></span>. |
| </p> |
| <p> |
| http://hsqldb.org/doc/2.0 hosts the latest production versions of all available formats. |
| If you want a different format of the same <span class="emphasis"><em>version</em></span> |
| of the document you are reading now, then you should try your |
| current distro. |
| If you want the latest production version, you should try http://hsqldb.org/doc/2.0. |
| </p> |
| <p> |
| Sometimes, distributions other than http://hsqldb.org/doc/2.0 do not host all |
| available formats. |
| So, if you can't access the format that you want in your current |
| distro, you have no choice but to use the newest production version at |
| http://hsqldb.org/doc/2.0. |
| </p> |
| <p> |
| |
| <div class="table"> |
| <a name="altformats-tbl"></a> |
| <p class="title"> |
| <b>Table 1. Available formats of this document</b> |
| </p> |
| <div class="table-contents"> |
| <table summary="Available formats of this document" cellspacing="0" style="border-collapse: collapse;border-top: 0.5pt solid ; border-bottom: 0.5pt solid ; border-left: 0.5pt solid ; border-right: 0.5pt solid ; "> |
| <colgroup> |
| <col align="left"> |
| <col align="left"> |
| <col align="left"> |
| </colgroup> |
| <thead> |
| <tr> |
| <th style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; " align="left">format</th><th style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; " align="left">your distro</th><th style="border-bottom: 0.5pt solid ; " align="left">at http://hsqldb.org/doc/2.0</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; " align="left"> |
| Chunked HTML |
| </td><td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; " align="left"> |
| <a class="link" href="index.html" target="_top">index.html</a> |
| </td><td style="border-bottom: 0.5pt solid ; " align="left"> |
| <a class="link" href="http://hsqldb.org/doc/2.0/util-guide/" target="_top">http://hsqldb.org/doc/2.0/util-guide/</a> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; " align="left"> |
| All-in-one HTML |
| </td><td style="border-right: 0.5pt solid ; border-bottom: 0.5pt solid ; " align="left"> |
| <a class="link" href="util-guide.html" target="_top">util-guide.html</a> |
| </td><td style="border-bottom: 0.5pt solid ; " align="left"> |
| <a class="link" href="http://hsqldb.org/doc/2.0/util-guide/util-guide.html" target="_top">http://hsqldb.org/doc/2.0/util-guide/util-guide.html</a> |
| </td> |
| </tr> |
| <tr> |
| <td style="border-right: 0.5pt solid ; " align="left"> |
| PDF |
| </td><td style="border-right: 0.5pt solid ; " align="left"> |
| <a class="link" href="util-guide.pdf" target="_top">util-guide.pdf</a> |
| </td><td style="" align="left"> |
| <a class="link" href="http://hsqldb.org/doc/2.0/util-guide/util-guide.pdf" target="_top">http://hsqldb.org/doc/2.0/util-guide/util-guide.pdf</a> |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| </div> |
| <br class="table-break"> |
| If you are reading this document now with a standalone PDF reader, the |
| <span class="guilabel">your distro</span> links may not work. |
| </p> |
| </div> |
| </div> |
| <div class="chapter" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title"> |
| <a name="sqltool-chapt"></a>Chapter 1. SqlTool</h2> |
| </div> |
| <div> |
| <h3 class="subtitle"> |
| <i>SqlTool Manual</i> |
| </h3> |
| </div> |
| <div> |
| <div class="authorgroup"> |
| <div class="author"> |
| <h3 class="author"> |
| <span class="firstname">Blaine</span> <span class="surname">Simpson</span> |
| </h3> |
| <div class="affiliation"> |
| <span class="orgname">HSQL Development Group<br> |
| </span> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div> |
| <p class="releaseinfo">$Revision: 3607 $</p> |
| </div> |
| <div> |
| <p class="pubdate">$Date: 2010-06-01 07:18:26 -0400 (Tue, 01 Jun 2010) $</p> |
| </div> |
| </div> |
| </div> |
| <div class="toc"> |
| <p> |
| <b>Table of Contents</b> |
| </p> |
| <dl> |
| <dt> |
| <span class="section"><a href="#sqltool_book_purpose-sect">Purpose, Coverage, Changes in Behavior</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N100FB">Platforms and SqlTool versions covered</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N1011D">Recent Functional Changes</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10188">New Features</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_baremin-sect">The Bare Minimum</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N102AE">Embedding</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N102C3">Non-displayable Types</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N102E3">Desktop shortcuts</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10344">Loading sample data</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#N10360">Satisfying SqlTool's CLASSPATH Requirements</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#sqltool_olderaccess-sect"> |
| Accessing older HSQLDB Databases with SqlTool</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N103BC">App-specific Classes, Embedding, and non-HyperSQL Databases</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10403">Distributing SqlTool with your Apps</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10429">SqlTool Client PCs</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_auth-sect">RC File Authentication Setup</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_dsswitch-sect">Switching Data Sources</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_ilauth-sect">Using Inline RC Authentication</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_logging-sect">Logging</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_int-sect">Interactive Usage</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N10578">SqlTool Command-Line Editing</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N1059C">Command Types</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#N105B5">Command Types</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N105FB">Special Commands</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N106CC">Edit Buffer / History Commands</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_interactive_pl_commands-sect">PL Commands</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N107C2">? Variable</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_binary_files-sect"> |
| Storing and retrieving binary files</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N1080C">Command History</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10816">Shell scripting and command-line piping</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N1081F">Emulating Non-Interactive mode</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_nonint-sect">Non-Interactive</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#sqltool_sqlswitch-sect">Giving SQL on the Command Line</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10871">SQL Files</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_scripting-sect">Piping and shell scripting</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N108E1">Optimally Compatible SQL Files</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N108F4">Comments</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10901">Special Commands and Edit Buffer Commands in SQL Files</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10965">Automation</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10970">Getting Interactive Functionality with SQL Files</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_charencoding-sect"> |
| Character Encoding</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_report-sect">Generating Text or HTML Reports</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_pl-sect">SqlTool Procedural Language</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N10A08">Variables</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_macro-sect">Macros</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10A67">PL Sample</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10A78">Logical Expressions</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10AEB">Flow Control</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10B0E">Example</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_chunk-sect">Chunking</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N10B2C">Why?</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10B3D">How?</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_raw-sect">Raw Mode</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_embedded-langs-sect">SQL/PSM, SQL/JRT, and PL/SQL</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#sqltool_dsv-sect">Delimiter-Separated-Value Imports and Exports</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="#N10BE4">Simple DSV exports and imports using default settings</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#N10C1F">Specifying queries and options</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="#sqltool_unittest-sect">Unit Testing SqlTool</a></span> |
| </dt> |
| </dl> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_book_purpose-sect"></a>Purpose, Coverage, Changes in Behavior</h2> |
| </div> |
| </div> |
| </div> |
| <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Note"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Note]" src="../images/db/note.png"></td><th align="left">Note</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| Due to many important improvements to SqlTool, both in terms of |
| stability and features, all users of SqlTool are advised to use the |
| latest version of SqlTool, even if your database instances run with an |
| older HSQLDB version. |
| How to do this is documented in the |
| <a class="link" href="#sqltool_olderaccess-sect" title="Accessing older HSQLDB Databases with SqlTool"> |
| Accessing older HSQLDB Databases with SqlTool</a> |
| section below. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| This document explains how to use SqlTool, the main purpose of |
| which is to read your SQL text file or stdin, and execute the SQL |
| commands therein against a JDBC database. |
| There are also a great number of features to facilitate both |
| interactive use and automation. |
| The following paragraphs explain in a general way why SqlTool is |
| better than any existing tool for text-mode interactive SQL work, |
| and for automated SQL tasks. |
| Two important benefits which SqlTool shares with other pure Java |
| JDBC tools is that users can use a consistent interface and |
| syntax to interact with a huge variety of databases-- any |
| database which supports JDBC; plus the tool itself runs on any |
| Java platform. |
| Instead of using <code class="filename">isql</code> for Sybase, |
| <code class="filename">psql</code> for Postgresql, |
| <code class="filename">Sql*plus</code> for Oracle, etc., you can |
| use SqlTool for all of them. |
| As far as I know, SqlTool is the only production-ready, pure |
| Java, command-line, generic JDBC client. |
| Several databases come with a command-line client with limited |
| JDBC abilities (usually designed for use with just their specific |
| database). |
| </p> |
| <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Important"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Important]" src="../images/db/important.png"></td><th align="left">Important</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| The SqlTool commands and settings are intuitive once you are |
| famililar with the usage idioms. |
| This Guide does not attempt to list every SqlTool command and |
| option available. |
| When you want to know what SqlTool commands or options are available |
| for a specific purpose, you need to list the commands of the |
| appropriate type with the relevant "?" command. |
| For example, as explained below, to see all Special commands, you |
| would run <code class="literal">\?</code>; and to see all DSV export options, |
| you run \x?. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| SqlTool is purposefully not a Gui tool like Toad or DatabaseManager. |
| There are many use cases where a Gui SQL tool would be better. |
| Where automation is involved in any way, you really need a text |
| client to at least test things properly and usually to prototype |
| and try things out. |
| A command-line tool is really better for executing SQL scripts, |
| any form of automation, direct-to-file fetching, and remote client |
| usage. |
| To clarify this last, if you have to do your SQL client work on a |
| work server on the other side of a VPN connection, you will quickly |
| appreciate the speed difference between text data transmission |
| and graphical data transmission, even if using VNC or Remote Console. |
| Another case would be where you are doing some repetitive or |
| very structured work where variables or language features would |
| be useful. |
| Gui proponents may disagree with me, but scripting (of any sort) |
| is more efficient than repetitive copy & pasting with a Gui |
| editor. |
| SqlTool starts up very quickly, and it takes up a tiny fraction of |
| the RAM required to run a comparably complex Gui like Toad. |
| </p> |
| <p> |
| SqlTool is superior for interactive use because over many years it |
| has evolved lots of features proven to be efficient for day-to-day |
| use. |
| Four concise help commands (\?, :?, *?, and /?) list all available |
| commands of the corresponding type. |
| SqlTool doesn't support up-arrow or other OOB escapes (due to basic |
| Java I/O limitations), but it more than makes up for this limitation |
| with macros, user variables, command-line history and recall, and |
| command-line editing with extended Perl/Java regular expressions. |
| The \d commands deliver JDBC metadata information as consistently as |
| possible (in several cases, database-specific work-arounds are used |
| to obtain the underlying data even though the database doesn't |
| provide metadata according to the JDBC specs). |
| Unlike server-side language features, the same feature set works |
| for any database server. |
| Database access details may be supplied on the command line, but |
| day-to-day users will want to centralize JDBC connection details |
| into a single, protected RC file. |
| You can put connection details (username, password, URL, and other |
| optional settings) for scores of target databases into your RC file, |
| then connect to any of them whenever you want by just giving |
| SqlTool the ID ("urlid") for that database. |
| When you Execute SqlTool interactively, it behaves by default |
| exactly as you would want it to. |
| If errors occur, you are given specific error messages and you |
| can decide whether to roll back your session. |
| You can easily change this behavior to auto-commit, |
| exit-upon-error, etc., for the current session or for all |
| interactive invocations. |
| You can import or export delimiter-separated-value files. |
| If you need to run a specific statement repeatedly, perhaps changing |
| the WHERE clause each time, it is very simple to define a macro. |
| </p> |
| <p> |
| When you Execute SqlTool with a SQL script, it also behaves by |
| default exactly as you would want it to. |
| If any error is encountered, the connection will be rolled back, |
| then SqlTool will exit with an error exit value. |
| If you wish, you can detect and handle error (or other) conditions |
| yourself. |
| For scripts expected to produce errors (like many scripts provided |
| by database vendors), you can have SqlTool continue-upon-error. |
| For SQL script-writers, you will have access to portable scripting |
| features which you've had to live without until now. |
| You can use variables set on the command line or in your script. |
| You can handle specific errors based on the output of SQL commands |
| or of your variables. |
| You can chain SQL scripts, invoke external programs, dump data |
| to files, use prepared statements, |
| Finally, you have a procedural language with <code class="literal">if</code>, |
| <code class="literal">foreach</code>, <code class="literal">while</code>, |
| <code class="literal">continue</code>, and <code class="literal">break</code> statements. |
| </p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N100FB"></a>Platforms and SqlTool versions covered</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| SqlTool runs on any Java 1.5 or later platform. |
| I know that SqlTool works well with Sun and OpenJDK JVMs. |
| I haven't run other vendors' JVMs in years (IBM, JRockit, etc.). |
| As my use with OpenJDK proves that I don't depend on Sun-specific |
| classes, I expect it to work well with other (1.5-compatible) Java |
| implementations. |
| </p> |
| <p> |
| SqlTool no longer writes any files without being explicitly |
| instructed to. |
| Therefore, it should work fine on read-only systems, and you'll |
| never have orphaned temp files left around. |
| </p> |
| <p> |
| The command-line examples in this chapter work as given on all |
| platforms (if you substitute in a normalized path in place of |
| <code class="literal">$HSQLDB_HOME</code>), except where noted otherwise. |
| When doing any significant command-line work on Windows |
| (especially shell scripting), you're better off to completely |
| avoid paths with spaces or funny characters. |
| If you can't avoid it, use double-quotes and expect problems. |
| As with any Java program, file or directory paths on the command |
| line after "java" can use forward slashes instead of back slashes |
| (this goes for System properties and the |
| <code class="varname">CLASSPATH</code> variable too). |
| I use forward slashes because they can be used consistently, and |
| I don't have to contort my fingers to type them :). |
| </p> |
| <p> |
| If you are using SqlTool from a HyperSQL distribution of version |
| 1.8.x or earlier, you should use the documentation with that |
| distribution, because this manual documents many new features, |
| several significant changes to interactive-only commands, and |
| a few changes effecting backwards-compatibility (see next |
| section about that). |
| This document is now updated for the current versions of SqlTool |
| and SqlFile at the time I am writing this (versions |
| 3406 and 3604 correspondingly-- SqlFile is the |
| class which actually processes the SQL content for SqlTool). |
| Therefore, if you are using a version of SqlTool or SqlFile that |
| is more than a couple revisions greater, you should find a newer |
| version of this document. |
| (The imprecision is due to content-independent revision increments |
| at build time, and the likelihood of one or two |
| behavior-independent bug fixes after public releases). |
| The startup banner will report both versions when you run SqlTool |
| interactively. |
| (Dotted version numbers of SqlTool and SqlFile definitely indicate |
| ancient versions). |
| </p> |
| <p> |
| This guide covers SqlTool as bundled with HSQLDB after 1.8.x. |
| <sup>[<a href="#ftn.samplelocFn" name="samplelocFn" class="footnote">1</a>]</sup> |
| |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1011D"></a>Recent Functional Changes</h3> |
| </div> |
| </div> |
| </div> |
| <p>This section lists changes to SqlTool since the last |
| major release of HSQLDB which may effect the portability |
| of SQL scripts. |
| For this revision of this document, this list consists of |
| script-impacting changes made to SqlTool |
| <span class="emphasis"><em>after</em></span> the final 1.8.0.0 HSQLDB release. |
| I'm specifically not listing changes to interactive(:)-only |
| commands ( with one legacy exception which is listed below),i |
| since these commands can't be used in SQL scripts; |
| and I'm specifically not listing backwards-compatible feature |
| additions and enhancements. |
| The reason for limiting the change list to only portability- |
| impacting changes is that a list of all enhancements since |
| just 1.8.1.1 would be pages long. |
| </p> |
| <div class="itemizedlist"> |
| <ul type="disc"> |
| <li> |
| SqlTool is now bundled in the supplied file |
| <code class="literal">sqltool.jar</code> instead of |
| <code class="filename">hsqldb.jar</code>. |
| Therefore, the usage idiom |
| <code class="literal">java -jar .../hsqldb.jar</code> has changed to |
| <code class="literal">java -jar .../sqltool.jar</code>. |
| (depending on where you get your HyperSQL resources from, |
| these files may be named with a version label, like |
| <code class="filename">sqltool-1.2.3.4.jar</code>). |
| </li> |
| <li> |
| The package of SqlTool and support classes has been changed |
| from <code class="classname">org.hsqldb.util</code> to |
| <code class="classname">org.hsqldb.cmdline</code>. |
| There is no change to <code class="literal">java -jar...</code> command-lines, |
| but you will need to change the package name in SqlTool |
| command lines of the form <code class="literal">java... org.hsqldb...</code>. |
| This later usage is necessary if you need to modify the |
| classpath for any reason, such as for embedding, to use a |
| logging config file, or to use a non-HSQLDB JDBC driver. |
| </li> |
| <li> |
| SqlTool now consistently outputs \r\n line breaks when |
| on \r\n-linebreak platforms, like Windows. |
| This includes output written to stdout, \w files, |
| and \o files. |
| </li> |
| <li> |
| Time type values are always output with the date as |
| well as the time. |
| This was required in order to produce consistent output |
| for the wildly varying formats provided by different |
| database vendors. |
| </li> |
| <li> |
| DSV input now takes JDBC Timestamp format with date and |
| optionally time of day. |
| </li> |
| <li> |
| The command ":;" is now strictly an interactive command. |
| If you want to repeat a command in an SQL scripts, just |
| repeat the exact text of the command. |
| Non-interactive use now has no dependency on command history. |
| </li> |
| <li> |
| The command ":w" has replace the command \w. |
| Unlike writing "output" to a file with \w, :w is used to |
| write SQL "commands", and this is an interactive feature. |
| </li> |
| <li> |
| Shell scripts using raw mode (e.g. PL/SQL scripts) must |
| terminate the raw code with a line containing ".;", which |
| will also send the code to the database for execution. |
| (The old "." command has been changed to ":." to make it |
| very clear that that command is now an interactive command). |
| </li> |
| <li> |
| You must use raw mode to <span class="emphasis"><em>chunk</em></span> SQL |
| statements to your DB server. |
| I.e., with previous versions of SqlTool, SQL statements |
| were only sent to the DB server when a semi-colon is read |
| at the end of a line. |
| SqlTool now has a much more powerful parser, and individual |
| SQL statements are sent to the DB server as soon as they |
| are terminated with a semi-colon, regardless of line |
| delimiters. |
| Therefore, to send multiple SQL statements to the DB server |
| in one transmittal, you must use raw mode. |
| </li> |
| <li> |
| The --sql argument will never automatically append a |
| semicolon to the text you provide. |
| If you want to execute a command ending with a semi-- |
| then type a semi. |
| </li> |
| <li> |
| Default encoding used is now the platform's default encoding |
| instead of <code class="literal">US-ASCII</code>. |
| </li> |
| <li> |
| To minimize side-effects (especialy for instance-based |
| programmatic usage), the only System properties used are |
| those predefined by the JVM (incl. |
| <code class="literal">javax.net.ssl.*</code>. |
| Properties of the form <code class="literal">sqlfile.*</code> and |
| <code class="literal">sqltool.*</code> are specifically no longer |
| supported. |
| (Less invasive configuration systems are provided to serve |
| the same purposes). |
| </li> |
| <li> |
| SqlTool no longer displays the usage banner if none of |
| inline-RC, urlid, SQL files are supplied, because that now |
| starts up SqlTool with no JDBC Connection. |
| To see the usage banner, use the <code class="literal">--help</code> |
| command-line switch. |
| </li> |
| <li> |
| Requires Java 1.5 in order to build or run. |
| </li> |
| <li> |
| Update and row counts are not displayed in non-interactive |
| mode. |
| The count values are readily available in a format more |
| suitable for scripting uses through PL variables (like with |
| <code class="literal">*{?}</code> or <code class="literal">* VARNAME _</code>). |
| </li> |
| </ul> |
| </div> |
| <p> |
| Although it doesn't effect scripts, I will mention a |
| significant recent change to interactive commands which could |
| confuse existing power users. |
| Special and PL commands are now stored to the edit buffer |
| and to command history, so they can be recalled and edited |
| just like SQL commands. Now, all commands other than |
| edit/history : commands are stored to the buffer and history. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10188"></a>New Features</h3> |
| </div> |
| </div> |
| </div> |
| <div class="itemizedlist"> |
| <ul type="disc"> |
| <li> |
| DSV column and row delimiters are now specified |
| separately for input and output. |
| This allows the input delimiters to be regular expressions. |
| (Which in turn, allows for the next item). |
| </li> |
| <li> |
| The new default DSM import row delimiter works for |
| standard UNIX, Windows, Mac lines. |
| This makes DSV files portable across these platforms. |
| (If using a change control system like Subversion, CVS, |
| or whatever, you can now change control your .dsv files |
| as native ASCII files). |
| </li> |
| <li> |
| Both <code class="literal">/* This kind */</code> and |
| <code class="literal">-- This kind</code> of comments are now |
| handled generally and intuitively, in SQL statements |
| and in SqlTool-specific commands. |
| (There were previously several limitations to where they |
| could be used). |
| </li> |
| <li> |
| At the cost of adding another |
| <span class="emphasis"><em>command type</em></span>, command aliases were |
| replaced by / <span class="emphasis"><em>macros</em></span>. |
| Usage (i.e., execution) is basically the same, but the new |
| macros are much easier to define and list; and macros can |
| be used for both PL and Special commands now (not just |
| for SQL statements). |
| </li> |
| <li> |
| Reports Transaction Isolation level and JDBC Connection |
| Read/Write or Read-Only state connection or request |
| (with \j). |
| </li> |
| <li> |
| New \t command to report database exection duration times. |
| </li> |
| <li> |
| New \v command to set or report the Connection's |
| Transaction Isolation LeVel. |
| </li> |
| <li> |
| |
| <code class="literal">\d object filter</code> commands now use the |
| filter as a regular expression, where possible, and |
| filter may have optional prefix / to mean to match the |
| filter against all output (not just the object name). |
| </li> |
| <li> |
| |
| <code class="literal">\dX filter</code> commands now use the |
| filter as a regular expression, where possible. |
| </li> |
| <li> |
| New <code class="varname">*DSV_TRIM_ALL</code> to automatically |
| handle import of |
| data which is both positional and character-delimited. |
| </li> |
| <li> |
| New \l command to log user-specified messages with |
| <code class="classname">java.util.logging</code> or Log4j |
| logging facility. |
| Nothing at all is written directly to stderr. |
| </li> |
| <li> |
| All warnings and messages now use logging facility. |
| This allows for declarative fine control of what gets |
| logged and where the messages go to, as well as allowing |
| for embedded apps to manage SqlTool apps in an integrated |
| fashion with other app log entries. |
| </li> |
| <li> |
| New *DSV_RECORDS_PER_COMMIT setting to support |
| user-specified tuning of large DSV imports. |
| </li> |
| <li> |
| (Optional) DSV log report can be customized with style |
| sheets. |
| </li> |
| <li> |
| You can interactively (or in SQL scripts) switch JDBC data |
| sources (with \j). |
| SqlTool can be started and used without any data source, |
| though you'll obviously need to connect to a data source |
| before issuing SQL commands. |
| </li> |
| <li> |
| Array types are now supported, including in DSV imports and |
| exports, with the exception that DSV imports do not support |
| element values containing commas. |
| </li> |
| </ul> |
| </div> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_baremin-sect"></a>The Bare Minimum</h2> |
| </div> |
| <div> |
| <h3 class="subtitle">The Bare Minimum You Need to Know to Run SqlTool</h3> |
| </div> |
| </div> |
| </div> |
| <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Warning"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Warning]" src="../images/db/warning.png"></td><th align="left">Warning</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| If you are using an Oracle database server, it will commit your |
| current transaction if you cleanly disconnect, regardless of |
| whether you have set auto-commit or not. |
| This will occur if you exit SqlTool (or any other client) in |
| the normal way (as opposed to killing the process or using |
| Ctrl-C, etc.). |
| This is mentioned in this section only for brevity, so I don't |
| need to mention it in the main text in the many places where |
| auto-commit is discussed. |
| This behavior has nothing to do with SqlTool. |
| It is a quirk of Oracle. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| If you want to use SqlTool, then you either have an SQL text file, |
| or you want to interactively type in SQL commands. |
| If neither case applies to you, then you are probably |
| looking at the wrong program. |
| </p> |
| <div class="procedure"> |
| <a name="N101E0"></a> |
| <p class="title"> |
| <b>Procedure 1.1. To run SqlTool...</b> |
| </p> |
| <ol type="1"> |
| <li> |
| <p> |
| Copy the file |
| <code class="filename"><a class="filename" href="#sqltool.rc-link"> |
| sample/sqltool.rc</a></code> |
| <sup>[<a href="#ftn.samplelocFn" class="footnoteref">1</a>]</sup> |
| of your HyperSQL distribution to your home directory and |
| secure access to it if your computer is accessible |
| to anybody else (most likely from the network). |
| This file will work as-is for a Memory Only database |
| instance; or if your target is a HyperSQL Server |
| running on your local computer with default settings |
| and the password for the "SA" account is blank |
| (the SA password is blank when new HyperSQL database |
| instances are created). |
| Edit the file if you need to change the target Server URL, |
| username, password, character set, JDBC driver, or TLS |
| trust store as documented in the |
| <a class="link" href="#sqltool_auth-sect" title="RC File Authentication Setup">RC File Authentication Setup</a> |
| section. |
| You could, alternatively, use the |
| <code class="literal">--inlineRc</code> command-line switch or the |
| \j special command to connect up to a data source, as |
| documented below. |
| </p> |
| </li> |
| <li> |
| <p> |
| Find out where your <code class="filename">sqltool.jar</code> file |
| resides. |
| It typically resides at |
| <code class="varname">HSQLDB_HOME</code><code class="filename">/lib/sqltool.jar</code> |
| where <code class="varname">HSQLDB_HOME</code> is the |
| "hsqldb" directory inside the root level of your HyperSQL |
| software installation. |
| (For example, if you extract |
| <code class="filename">hsqldb-9.1.0.zip</code> into |
| <code class="filename">c:\temp</code>, |
| your <code class="varname">HSQLDB_HOME</code> would be |
| <code class="filename">c:/temp/hsqldb-9.1.0/hsqldb</code>. |
| Your file may also have a version label in the file name, |
| like <code class="filename">sqltool-1.2.3.4.jar</code>. |
| The forward slashes work just fine on Windows). |
| For this reason, I'm going to use |
| "$HSQLDB_HOME/lib/sqltool.jar" as the path to |
| <code class="filename">sqltool.jar</code> for my examples, but |
| understand that you need to use the actual path to your |
| own <code class="filename">sqltool.jar</code> file. |
| (Unix users may set a real env. variable if they wish, |
| in which case the examples may be used verbatim; |
| Window users may do the same, but will need to dereference |
| the variables like <code class="literal">%THIS%</code> instead of |
| like <code class="literal">$THIS</code>). |
| </p> |
| <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Warning"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Warning]" src="../images/db/warning.png"></td><th align="left">Warning</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| My examples assume there are no spaces or funky characters |
| in your file paths. |
| This avoids bugs with the Windows cmd shell and makes for |
| simpler syntax all-around. |
| If you insist on using directories with spaces or shell |
| metacharacters (including standard Windows home directories |
| like <code class="filename">C:\Documents and Settings\blaine</code>), |
| you will need to double-quote arguments containing these |
| paths. |
| (On UNIX you can alternatively use single-quotes to avoid |
| variable dereferencing at the same time). |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| </li> |
| <li> |
| <p> |
| If you are just starting with SqlTool, you are best off |
| running your SqlTool command from a shell |
| <span class="emphasis"><em>command-line</em></span> (as opposed to by |
| using icons or the Windows' |
| <span class="guimenuitem">Start/Run...</span> or |
| <span class="guimenuitem">Start/Start Search</span>). |
| This way, you will be sure to see error messages if you |
| type the command wrong or if SqlTool can't start up for |
| some reason. |
| On recent versions of Windows, you can get a shell by |
| running <code class="literal">cmd</code> from |
| <span class="guimenuitem">Start/Run...</span> or |
| <span class="guimenuitem">Start/Start Search</span>). |
| On UNIX or Linux, any real or virtual terminal will work. |
| </p> |
| <p> |
| On your shell command line, run |
| <div class="informalexample"> |
| <pre class="screen"> java -jar $HSQLDB_HOME/lib/sqltool.jar --help</pre> |
| </div> |
| to see what command-line arguments are available. |
| Note that you don't need to worry about setting the |
| <code class="varname">CLASSPATH</code> |
| when you use the <code class="literal">-jar</code> switch |
| to <code class="filename">java</code>. |
| </p> |
| <p> |
| To run SqlTool without a JDBC connection, run |
| <div class="informalexample"> |
| <pre class="screen"> java -jar $HSQLDB_HOME/lib/sqltool.jar</pre> |
| </div> |
| You won't be able to execute any SQL, but you can play with |
| the SqlTool interface (including using PL features). |
| </p> |
| <p> |
| To execute SQL, you'll need the classes for the target |
| database's JDBC driver (and database engine classes for |
| <em class="glossterm">in-process</em> databases). |
| As this section is titled <span class="emphasis"><em>The Bare Minimum</em></span>, |
| I'll just say that if you are running SqlTool from a HyperSQL |
| product installation, you are all set to connect to any kind of |
| HyperSQL database. |
| This is because SqlTool will look for the file |
| <code class="filename">hsqldb.jar</code> in the same directory as |
| <code class="filename">sqltool.jar</code>, and that file contains all of |
| the needed classes. |
| (SqlTool supports all JDBC databases and does not require a |
| HyperSQL installation, but these cases would take us beyond |
| <span class="emphasis"><em>the bare minimum</em></span>). |
| So, with <code class="filename">hsqldb.jar</code> in place, you can run |
| commands like |
| <div class="informalexample"> |
| <pre class="screen"> java -jar $HSQLDB_HOME/lib/sqltool.jar mem</pre> |
| </div> |
| for interactive use, or |
| <div class="informalexample"> |
| <pre class="screen"> java -jar $HSQLDB_HOME/lib/sqltool.jar --sql="SQL statement;" mem</pre> |
| </div> |
| or |
| <div class="informalexample"> |
| <pre class="screen"> java -jar $HSQLDB_HOME/lib/sqltool.jar mem filepath1.sql...</pre> |
| </div> |
| where <code class="literal">mem</code> is an |
| <span class="emphasis"><em>urlid</em></span>, |
| and the following arguments are paths to text SQL files. |
| For the filepaths, you can use whatever wildcards your |
| operating system shell supports. |
| </p> |
| <p> |
| The <span class="emphasis"><em>urlid</em></span> <code class="literal">mem |
| </code>in these commands is a key |
| into your RC file, as explained in the |
| <a class="link" href="#sqltool_auth-sect" title="RC File Authentication Setup">RC File Authentication Setup</a> section. |
| Since this is a <em class="glossterm">mem:</em> type catalog, |
| you can use SqlTool |
| with this urlid immediately with no database setup |
| whatsoever (however, you can't persist any changes that |
| you make to this database). |
| The sample sqltool.rc file also defines the urlid |
| "localhost-sa" for a local HyperSQL Listener. |
| At the end of this section, I explain how you can load |
| some sample data to play with, if you want to. |
| </p> |
| </li> |
| </ol> |
| </div> |
| <div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Tip"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Tip]" src="../images/db/tip.png"></td><th align="left">Tip</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| If SqlTool fails to connect to the specified urlid and you don't |
| know why, add the invocation parameter <code class="literal">--debug</code>. |
| This will cause SqlTool to display a stack trace from where the |
| connection attempt fails. |
| (If a connection attempt fails with the interactive \j command, |
| details will always be displayed). |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Important"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Important]" src="../images/db/important.png"></td><th align="left">Important</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| SqlTool does not <span class="emphasis"><em>commit</em></span> SQL changes by default. |
| (You can use the <code class="literal">--autoCommit</code> command-line |
| switch to have it auto-commit). |
| This leaves it to the user's disgression whether to commit or |
| rollback their modifications. |
| If you do want your changes committed, remember to run \= before |
| quitting SqlTool. |
| (Most databases also support the SQL command |
| <code class="literal">commit;</code>), |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| If you put a file named <code class="filename">auto.sql</code> into your |
| home directory, this file will be executed automatically every |
| time that you run SqlTool interactively (unless you invoke with |
| the <code class="literal">--noAutoFile</code> switch). |
| </p> |
| <p> |
| To use a JDBC Driver other than the HyperSQL driver, you can't use |
| the <code class="literal">-jar</code> switch because you need to modify the |
| classpath. |
| You must add the <code class="filename">sqltool.jar</code> file and your JDBC |
| driver classes to your classpath, |
| and you must tell SqlTool what the JDBC driver class name is. |
| The latter can be accomplished by either using the "--driver" |
| switch, or setting "driver" in your config file. |
| The <a class="link" href="#sqltool_auth-sect" title="RC File Authentication Setup">RC File Authentication Setup</a> section. |
| explains the second method. Here's an example of the first method |
| (after you have set the classpath appropriately). |
| <div class="informalexample"> |
| <pre class="screen">java org.hsqldb.cmdline.SqlTool --driver=oracle.jdbc.OracleDriver urlid</pre> |
| </div> |
| </p> |
| <div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Tip"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Tip]" src="../images/db/tip.png"></td><th align="left">Tip</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| If the tables of query output on your screen are all messy |
| because of lines wrapping, the best and easiest solution |
| is usually to resize your terminal emulator window to make it |
| wider. |
| (With some terms you click & drag the frame edges to resize, |
| with others you use a menu system where you can enter the number |
| of columns). |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N102AE"></a>Embedding</h3> |
| </div> |
| <div> |
| <h4 class="subtitle">Using SqlTool to execute SQL files from your own Java |
| code</h4> |
| </div> |
| </div> |
| </div> |
| <p> |
| To repeat what is stated in the JavaDoc for the |
| <code class="classname"><a class="classname" href="#SqlTool.html-link">SqlTool</a></code> |
| class itself: |
| <span class="emphasis"><em> |
| Programmatic users will usually want to use the |
| objectMain(String[]) method if they want arguments and behavior |
| exactly like command-line SqlTool. If you don't need invocation |
| parameter parsing, <code class="filename">auto.sql</code> exection, etc., |
| you will have more control and efficiency by using the SqlFile |
| class directly. The file |
| <code class="filename"><a class="filename" href="#SqlFileEmbedder.java-link"> |
| src/org/hsqldb/sample/SqlFileEmbedder.java</a></code> |
| in the HyperSQL distribution provides an example for this latter |
| strategy. |
| </em></span> |
| |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N102C3"></a>Non-displayable Types</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| There are some SQL types which SqlTool (being a text-based |
| program) can't display properly. |
| This includes the SQL types <code class="literal">BLOB</code>, |
| <code class="literal">JAVA_OBJECT</code>, <code class="literal">STRUCT</code>, |
| and <code class="literal">OTHER</code>. |
| When you run a query that returns any of these, SqlTool will |
| save the very first such value obtained to the binary buffer |
| and will not display any output from this query. |
| You can then save the binary value to a file, as explained in the |
| <a class="link" href="#sqltool_binary_files-sect" title="Storing and retrieving binary files"> |
| Storing and retrieving binary files</a> |
| section. |
| </p> |
| <p> |
| There are other types, such as <code class="literal">BINARY</code>, which |
| JDBC can make displayable (by using ResultSet.getString()), but |
| which you may very well want to retrieve in raw binary format. |
| You can use the \b command to retrieve any-column-type-at-all |
| in raw binary format (so you can later store the value to a |
| binary file). |
| </p> |
| <p> |
| Another restriction which all text-based database clients have |
| is the practical inability for the user to type in binary data |
| such as photos, audio streams, and serialized Java objects. |
| You can use SqlTool to load any binary object into a database |
| by telling SqlTool to get the insert/update datum from a file. |
| This is also explained in the |
| <a class="link" href="#sqltool_binary_files-sect" title="Storing and retrieving binary files"> |
| Storing and retrieving binary files</a> |
| section. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N102E3"></a>Desktop shortcuts</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Desktop shortcuts and quick launch icons are useful, especially |
| if you often run SqlTool with the same set of arguments. |
| It's really easy to set up several of them-- one for each |
| way that you invoke SqlTool (i.e., each one would start |
| SqlTool with all the arguments for one of your typical startup |
| needs). |
| One typical setup is to have one shortcut for each database |
| account which you normally use (use a different |
| <code class="literal">urlid</code> argument in each shortcut's |
| <span class="guilabel">Target</span> specification. |
| </p> |
| <p> |
| Desktop icon setup varies depending on your Desktop manager, |
| of course. |
| I'll explain how to set up a SqlTool startup icon in Windows |
| XP. |
| Linux and Mac users should be able to take it from there, since |
| it's easier with the common Linux and Mac desktops. |
| </p> |
| <div class="procedure"> |
| <a name="N102F0"></a> |
| <p class="title"> |
| <b>Procedure 1.2. Creating a Desktop Shortcut for SqlTool</b> |
| </p> |
| <ol type="1"> |
| <li> |
| <p> |
| Right click in the main Windows background. |
| </p> |
| </li> |
| <li> |
| <p> |
| |
| <span class="guimenuitem">New</span> |
| |
| </p> |
| </li> |
| <li> |
| <p> |
| |
| <span class="guimenuitem">Shortcut</span> |
| |
| </p> |
| </li> |
| <li> |
| <p> |
| |
| <span class="guibutton">Browse</span> |
| |
| </p> |
| </li> |
| <li> |
| <p> |
| Navigate to where your good JRE lives. For recent Sun |
| JRE's, it installs to |
| <code class="filename">C:\Program Files\Java\*\bin</code> |
| by default (the * will be a JDK or JRE identifier and |
| version number). |
| </p> |
| </li> |
| <li> |
| <p> |
| Select <code class="filename">java.exe</code>. |
| </p> |
| </li> |
| <li> |
| <p> |
| |
| <span class="guibutton">OK</span> |
| |
| </p> |
| </li> |
| <li> |
| <p> |
| |
| <span class="guimenuitem">Next</span> |
| |
| </p> |
| </li> |
| <li> |
| <p> |
| Enter any name |
| </p> |
| </li> |
| <li> |
| <p> |
| |
| <span class="guimenuitem">Finish</span> |
| |
| </p> |
| </li> |
| <li> |
| <p> |
| Right click the new icon. |
| </p> |
| </li> |
| <li> |
| <p> |
| |
| <span class="guimenuitem">Properties</span> |
| |
| </p> |
| </li> |
| <li> |
| <p> |
| Edit the <span class="guilabel">Target</span> field. |
| </p> |
| </li> |
| <li> |
| <p> |
| Leave the path to java.exe exactly as it is, including the |
| quotes, but append to what is there. |
| Beginning with a space, enter the command-line that you |
| want run. |
| </p> |
| </li> |
| <li> |
| <p> |
| |
| <span class="guibutton">Change Icon...</span> to a pretty icon. |
| </p> |
| </li> |
| <li> |
| <p> |
| If you want a quick-launch icon instead of (or in addition |
| to) a desktop shortcut icon, click and drag it to your |
| quick launch bar. (You may or may not need to edit the |
| Windows Toolbar properties to let you add new items). |
| </p> |
| </li> |
| </ol> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10344"></a>Loading sample data</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| If you want some sample database objects and data to play |
| with, execute the |
| <code class="filename"><a class="filename" href="#sampledata.sql-link"> |
| sample/sampledata.sql</a></code> SQL file |
| <sup>[<a href="#ftn.samplelocFn" class="footnoteref">1</a>]</sup>. |
| To separate the sample data from your regular data, you can |
| put it into its own schema by running this before you import: |
| <div class="informalexample"> |
| <pre class="programlisting"> CREATE SCHEMA sampledata AUTHORIZATION dba; |
| SET SCHEMA sampledata;</pre> |
| </div> |
| Run it like this from an SqlTool session |
| <pre class="programlisting"> \i HSQLDB_HOME/sample/sampledata.sql</pre> |
| where <span class="bold"><strong>HSQLDB_HOME</strong></span> is the |
| base directory of your HSQLDB software installation |
| <sup>[<a href="#ftn.samplelocFn" class="footnoteref">1</a>]</sup>. |
| </p> |
| <p> |
| For memory-only databases, you'll need to run this every |
| time that you run SqlTool. |
| For other (persistent) databases, the data will reside in |
| your database until you drop the tables. |
| </p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N10360"></a>Satisfying SqlTool's CLASSPATH Requirements</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| As discussed earlier, only the single file |
| <code class="filename">sqltool.jar</code> is required to run SqlTool (the file |
| name may contain a version label like |
| <code class="filename">sqltool-1.2.3.4.jar</code>). |
| But it's useless as an SQL <span class="emphasis"><em>Tool</em></span> unless you can |
| connect to a JDBC data source, and for that you need the target |
| database's JDBC driver in the classpath. |
| For <em class="glossterm">in-process</em> catalogs, you'll also need the |
| database engine classes in the CLASSPATH. |
| The <a class="link" href="#sqltool_baremin-sect" title="The Bare Minimum">The Bare Minimum</a> |
| section explains that the easiest way to use SqlTool with any HyperSQL |
| database is to just use <code class="filename">sqltool.jar</code> in-place where |
| it resides in a HyperSQL installation. |
| This section explains how to satisfy the CLASSPATH requirements for |
| other setups and use cases. |
| </p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="sqltool_olderaccess-sect"></a> |
| Accessing older HSQLDB Databases with SqlTool</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| If you are using SqlTool to access non-HSQLDB database(s), then you |
| should use the latest and greatest-- just |
| grab the newest public release of SqlTool (like from the latest |
| public HyperSQL release) and skip this subsection. |
| </p> |
| <p> |
| You are strongly encouraged to use the latest SqlTool release to |
| access older HSQLDB databases, to enjoy greatly improved SqlTool |
| robustness and features. |
| It is very easy to do this. |
| <div class="procedure"> |
| <ol type="1"> |
| <li> |
| <p> |
| Obtain the latest <code class="filename">sqltool.jar</code> file. |
| One way to obtain the latest <code class="filename">sqltool.jar</code> file |
| is to download the latest HyperSQL distribution and extract that |
| single file |
| </p> |
| </li> |
| <li> |
| <p> |
| Place (or copy) your new <code class="filename">sqltool.jar</code> file |
| right alongside the <code class="filename">hsqldb.jar</code> file for |
| your target database version. |
| If you don't have a local copy of the |
| <code class="filename">hsqldb.jar</code> file for your target database, |
| just copy it from your database server, or download the full |
| distribution for that server version and extract it. |
| </p> |
| </li> |
| <li> |
| <p> |
| |
| <span class="emphasis"><em> |
| (If you have used older versions of SqlTool before, notice |
| that you now invoke SqlTool by specifying the |
| <code class="filename">sqltool.jar</code> file instead of the |
| <code class="filename">hsqldb.jar</code>). |
| </em></span> |
| If your target database is a previous 2.x version of HyperSQL, |
| then you are finished and can use the new SqlTool for your older |
| database. Users upgrading from a pre-2.x version please |
| continue... |
| </p> |
| <p> |
| Run SqlTool like this. |
| <div class="informalexample"> |
| <pre class="screen"> java -jar path/to/sqltool.jar --driver=org.hsqldb.jdbcDriver...</pre> |
| </div> |
| where you specify the pre-2.x JDBC driver name |
| <code class="classname">org.hsqldb.jdbcDriver</code>. |
| Give any other SqlTool parameters as you usually would. |
| </p> |
| <p> |
| Once you have verified that you can access your database using |
| the <code class="literal">--driver</code> paramater as explained above, |
| edit your <code class="filename">sqltool.rc</code> file, and add a |
| new line |
| <div class="informalexample"> |
| <pre class="programlisting"> driver org.hsqldb.jdbcDriver</pre> |
| </div> |
| after each urlid that is for a pre-2.x database. |
| Once you do this, you can invoke SqlTool as usual (i.e. you |
| no longer need the <code class="literal">--driver</code> |
| argument for your invocations). |
| </p> |
| </li> |
| </ol> |
| </div> |
| |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N103BC"></a>App-specific Classes, Embedding, and non-HyperSQL Databases</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| For these situations, you need to add your custom, third-party, or |
| SQL driver classes to your Java CLASSPATH. |
| Java doesn't support adding arbitrary elements to the classpath when |
| you use the <code class="literal">-jar</code>, so you must set a classpath |
| containing <code class="filename">sqltool.jar</code> plus whatever else you |
| need, then invoke SqlTool without the <code class="literal">-jar</code> switch, |
| as briefly described at the end of the |
| <a class="link" href="#sqltool_baremin-sect" title="The Bare Minimum">The Bare Minimum</a> |
| section. |
| For embedded apps, invoke your own main class instead of SqlTool, and |
| you can invoke <code class="classname">SqlTool</code> or |
| <code class="classname">SqlFile</code> from your code base. |
| </p> |
| <p> |
| To customize the classpath, |
| you need to set up your classpath by using your |
| operating system or shell variable <code class="varname">CLASSPATH</code> or |
| by using the <code class="filename">java</code> switch <code class="literal">-cp</code> |
| (or the equivalent <code class="literal">-classpath</code>). |
| I'm not going to take up space here to explain how to set up a |
| Java CLASSPATH. That is a platform-dependent task that is |
| documented well in tons of Java introductions and tutorials. |
| What I'm responsible for telling you is <span class="emphasis"><em>what</em></span> |
| you need to add to your classpath. |
| For the non-embedded case where you have set up your CLASSPATH |
| environmental varialbe, you would invoke SqlTool like this. |
| <div class="informalexample"> |
| <pre class="screen"> java org.hsqldb.cmdline.SqlTool ...</pre> |
| </div> |
| If you are using the <code class="literal">-cp</code> switch instead of a |
| <code class="varname">CLASSPATH</code> variable, stick it after |
| <code class="literal">java</code>. |
| After "<code class="literal">SqlTool</code>", give any SqlTool parameters |
| exactly as you would put after |
| <code class="literal">java -jar .../sqltool.jar</code> if you didn't need to |
| customize the CLASSPATH. |
| You can specify a JDBC driver class to use either with the |
| <code class="literal">--driver</code> switch to SqlTool, or in your |
| RC file stanza (the last method is usually more convenient). |
| </p> |
| <p> |
| Note that without the <code class="literal">-jar</code> switch, SqlTool will |
| still automatically pull in HyperSQL JDBC driver or engine classes |
| from HyperSQL jar files in the same directory. |
| It's often a good practice to minimize your runtime classpath. |
| To prevent the possibility of pulling in classes from other HyperSQL |
| jar files, just copy <code class="filename">sqltool.jar</code> to some other |
| directory (which does not contain other HyperSQL jar files) and put |
| the path to that one in your classpath. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10403"></a>Distributing SqlTool with your Apps</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| You can distribute SqlTool along with your application, for |
| standalone or embedded invocation. |
| For embedded use, you will need to customize the classpath as |
| discussed in the previous item. |
| Either way, you should minimize your application footprint by |
| distributing only those HyperSQL jar files needed by your app. |
| You will obviously need <code class="filename">sqltool.jar</code> if you will |
| use the <code class="classname">SqlTool</code> or |
| <code class="classname">SqlFile</code> class in any way. |
| If your app will only connect to external HyperSQL listeners, then |
| build and include <code class="filename">hsqljdbc.jar</code>. |
| If your app will also <span class="emphasis"><em>run</em></span> a HyperSQL Listener, |
| you'll need to include <code class="filename">hsqldb.jar</code>. |
| If your app will connect directly to a |
| <em class="glossterm">in-process</em> catalog, then include |
| <code class="filename">hsqldbmain.jar</code>. |
| Note that you never need to include more than one of |
| <code class="filename">hsqldb.jar</code>, <code class="filename">hsqldbmain.jar</code>, |
| <code class="filename">hsqljdbc.jar</code>, since the former jars include |
| everything in the following jars. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10429"></a>SqlTool Client PCs</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| If you just want to be able to run SqlTool (interactively or |
| non-interactively) on a PC, and have no need for documentation, then |
| it's usually easiest to just copy |
| <code class="filename">sqltool.jar</code> and <code class="filename">hsqldb.jar</code> |
| to the PCs (plus JDBC driver jars for any other target databases). |
| If you want to minize what you distribute, then build and |
| distribute <code class="filename">hsqljdbc.jar</code> or |
| <code class="filename">hsqldbmain.jar</code> instead of |
| <code class="filename">hsqldb.jar</code>, according to the criteria listed in |
| the previous sub-section. |
| </p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_auth-sect"></a>RC File Authentication Setup</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| RC file authentication setup is accomplished by creating a text |
| RC configuration file. |
| In this section, when I say <span class="emphasis"><em>configuration</em></span> |
| or <span class="emphasis"><em>config</em></span> file, I mean an RC configuration |
| file. |
| RC files can be used by any JDBC client program that uses the |
| org.hsqldb.util.RCData class-- this includes |
| SqlTool, DatabaseManager, DatabaseManagerSwing. |
| </p> |
| <p> |
| You can use it for your own JDBC client programs too. |
| There is example code showing how to do this at |
| <code class="filename"><a class="filename" href="#SqlFileEmbedder.java-link"> |
| src/org/hsqldb/sample/SqlFileEmbedder.java</a></code>. |
| </p> |
| <p> |
| The sample RC file shown here resides at |
| <code class="filename"><a class="filename" href="#sqltool.rc-link">sample/sqltool.rc</a></code> |
| in your HSQLDB distribution |
| <sup>[<a href="#ftn.samplelocFn" class="footnoteref">1</a>]</sup>. |
| </p> |
| <div class="example"> |
| <a name="N10459"></a> |
| <p class="title"> |
| <b>Example 1.1. Sample RC File</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="programlisting"># $Id: sqltool.rc 3353 2009-12-15 19:52:13Z unsaved $ |
| |
| # This is a sample RC configuration file used by SqlTool, DatabaseManager, |
| # and any other program that uses the org.hsqldb.lib.RCData class. |
| # See the documentation for SqlTool for various ways to use this file. |
| |
| # If you have the least concerns about security, then secure access to |
| # your RC file. |
| |
| # You can run SqlTool right now by copying this file to your home directory |
| # and running |
| # java -jar /path/to/sqltool.jar mem |
| # This will access the first urlid definition below in order to use a |
| # personal Memory-Only database. |
| # "url" values may, of course, contain JDBC connection properties, delimited |
| # with semicolons. |
| # As of revision 3347 of SqlFile, you can also connect to datasources defined |
| # here from within an SqlTool session/file with the command "\j urlid". |
| |
| # You can use Java system property values in this file like this: ${user.home} |
| |
| # The only feature added recently is the optional "transiso" setting, |
| # which may be set to an all-caps transaction isolation level as listed |
| # in the Java API Spec for java.sql.Connection. |
| # Windows users are advised to use forward slashes instead of reverse slashes, |
| # and to avoid paths containing spaces or other funny characters. (This |
| # recommendation applies to any Java app, not just SqlTool). |
| |
| # A personal Memory-Only (non-persistent) database. |
| urlid mem |
| url jdbc:hsqldb:mem:memdbid |
| username SA |
| password |
| |
| # A personal, local, persistent database. |
| urlid personal |
| url jdbc:hsqldb:file:${user.home}/db/personal;shutdown=true |
| username SA |
| password |
| transiso TRANSACTION_READ_COMMITTED |
| # When connecting directly to a file database like this, you should |
| # use the shutdown connection property like this to shut down the DB |
| # properly when you exit the JVM. |
| |
| # This is for a hsqldb Server running with default settings on your local |
| # computer (and for which you have not changed the password for "SA"). |
| urlid localhost-sa |
| url jdbc:hsqldb:hsql://localhost |
| username SA |
| password |
| |
| |
| |
| # Template for a urlid for an Oracle database. |
| # You will need to put the oracle.jdbc.OracleDriver class into your |
| # classpath. |
| # In the great majority of cases, you want to use the file classes12.zip |
| # (which you can get from the directory $ORACLE_HOME/jdbc/lib of any |
| # Oracle installation compatible with your server). |
| # Since you need to add to the classpath, you can't invoke SqlTool with |
| # the jar switch, like "java -jar .../hsqldb.jar..." or |
| # "java -jar .../hsqlsqltool.jar...". |
| # Put both the HSQLDB jar and classes12.zip in your classpath (and export!) |
| # and run something like "java org.hsqldb.util.SqlTool...". |
| |
| #urlid cardiff2 |
| #url jdbc:oracle:thin:@aegir.admc.com:1522:TRAFFIC_SID |
| #username blaine |
| #password secretpassword |
| #driver oracle.jdbc.OracleDriver |
| |
| |
| |
| # Template for a TLS-encrypted HSQLDB Server. |
| # Remember that the hostname in hsqls (and https) JDBC URLs must match the |
| # CN of the server certificate (the port and instance alias that follows |
| # are not part of the certificate at all). |
| # You only need to set "truststore" if the server cert is not approved by |
| # your system default truststore (which a commercial certificate probably |
| # would be). |
| |
| #urlid tls |
| #url jdbc:hsqldb:hsqls://db.admc.com:9001/lm2 |
| #username BLAINE |
| #password asecret |
| #truststore ${user.home}/ca/db/db-trust.store |
| |
| |
| # Template for a Postgresql database |
| #urlid blainedb |
| #url jdbc:postgresql://idun.africawork.org/blainedb |
| #username blaine |
| #password losung1 |
| #driver org.postgresql.Driver |
| |
| # Template for a MySQL database. MySQL has poor JDBC support. |
| #urlid mysql-testdb |
| #url jdbc:mysql://hostname:3306/dbname |
| #username root |
| #password hiddenpwd |
| #driver com.mysql.jdbc.Driver |
| |
| # Note that "databases" in SQL Server and Sybase are traditionally used for |
| # the same purpose as "schemas" with more SQL-compliant databases. |
| |
| # Template for a Microsoft SQL Server database |
| #urlid msprojsvr |
| #url jdbc:microsoft:sqlserver://hostname;DatabaseName=DbName;SelectMethod=Cursor |
| # The SelectMethod setting is required to do more than one thing on a JDBC |
| # session (I guess Microsoft thought nobody would really use Java for |
| # anything other than a "hello world" program). |
| # This is for Microsoft's SQL Server 2000 driver (requires mssqlserver.jar |
| # and msutil.jar). |
| #driver com.microsoft.jdbc.sqlserver.SQLServerDriver |
| #username myuser |
| #password hiddenpwd |
| |
| # Template for a Sybase database |
| #urlid sybase |
| #url jdbc:sybase:Tds:hostname:4100/dbname |
| #username blaine |
| #password hiddenpwd |
| # This is for the jConnect driver (requires jconn3.jar). |
| #driver com.sybase.jdbc3.jdbc.SybDriver |
| |
| # Template for Embedded Derby / Java DB. |
| #urlid derby1 |
| #url jdbc:derby:path/to/derby/directory;create=true |
| #username ${user.name} |
| #password any_noauthbydefault |
| #driver org.apache.derby.jdbc.EmbeddedDriver |
| # The embedded Derby driver requires derby.jar. |
| # There'a also the org.apache.derby.jdbc.ClientDriver driver with URL |
| # like jdbc:derby://<server>[:<port>]/databaseName, which requires |
| # derbyclient.jar. |
| # You can use \= to commit, since the Derby team decided (why???) |
| # not to implement the SQL standard statement "commit"!! |
| # Note that SqlTool can not shut down an embedded Derby database properly, |
| # since that requires an additional SQL connection just for that purpose. |
| # However, I've never lost data by not shutting it down properly. |
| # Other than not supporting this quirk of Derby, SqlTool is miles ahead of ij. |
| </pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| <p> |
| As noted in the comment (and as used in a couple examples), you |
| can use Java system properties like this: <code class="code">${user.home}</code>. |
| Windows users, please read the suggestion directed to you in the |
| file. |
| </p> |
| <p> |
| You can put this file anywhere you want to, and specify the |
| location to SqlTool/DatabaseManager/DatabaseManagerSwing by |
| using the <code class="literal">--rcfile</code> argument. |
| If there is no reason to not use the default location (and there |
| are situations where you would not want to), then use the default |
| location and you won't have to give <code class="literal">--rcfile</code> |
| arguments to SqlTool/DatabaseManager/DatabaseManagerSwing. |
| The default location is <code class="filename">sqltool.rc</code> or |
| <code class="filename">dbmanager.rc</code> in your home directory |
| (corresponding to the program using it). |
| If you have any doubt about where your home directory is, just |
| run SqlTool with a phony urlid and it will tell you where it |
| expects the configuration file to be. |
| <div class="informalexample"> |
| <pre class="screen"> java -jar $HSQLDB_HOME/lib/sqltool.jar x</pre> |
| </div> |
| </p> |
| <p> |
| The config file consists of stanza(s) like this: |
| <div class="informalexample"> |
| <pre class="screen"> urlid web |
| url jdbc:hsqldb:hsql://localhost |
| username web |
| password webspassword</pre> |
| </div> |
| </p> |
| <p> |
| These four settings are required for every urlid. |
| (There are optional settings also, which are described a couple |
| paragraphs down). |
| The URL may contain JDBC connection properties. |
| You can have as many blank lines and comments like |
| <div class="informalexample"> |
| <pre class="screen"> # This comment</pre> |
| </div> |
| |
| </p> |
| <p> |
| in the file as you like. |
| The whole point is that the <span class="emphasis"><em>urlid</em></span> that you |
| give in your SqlTool/DatabaseManager command must match a |
| <span class="emphasis"><em>urlid </em></span> in your configuration file. |
| </p> |
| <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Important"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Important]" src="../images/db/important.png"></td><th align="left">Important</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| Use whatever facilities are at your disposal to protect your |
| configuration file. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| It should be readable, both locally and remotely, only to users |
| who run programs that need it. |
| On UNIX, this is easily accomplished by using <code class="literal">chmod/chown |
| </code> commands and making sure that it is protected from |
| anonymous remote access (like via NFS, FTP or Samba). |
| </p> |
| <p> |
| You can also put the following optional settings into a urlid |
| stanza. The setting will, of course, only apply to that urlid. |
| </p> |
| <div class="variablelist"> |
| <table border="0"> |
| <col valign="top" align="left"> |
| <tbody> |
| <tr> |
| <td> |
| <p> |
| <span class="term">charset</span> |
| </p> |
| </td><td> |
| This is used by the SqlTool program, but not by the |
| DatabaseManager programs. |
| See the <a class="link" href="#sqltool_charencoding-sect" title="Character Encoding"> |
| Character Encoding</a> section of the |
| <a class="link" href="#sqltool_nonint-sect" title="Non-Interactive">Non-Interactive</a> |
| section. |
| This is used for input and output files, not for stdin or |
| stdout, which are controlled by environmental variables and |
| Java system properties. |
| If you set no encoding for an urlid, input and outfiles will |
| use the same encoding as for stdin/stdout. |
| (As of right now, the charset setting here is not honored by |
| the \j command, but only when SqlTool loads an urlid specified |
| on the command-line). |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">driver</span> |
| </p> |
| </td><td> |
| Sets the JDBC driver class name. |
| You can, alternatively, set this for one |
| SqlTool/DatabaseManager invocation by using the command |
| line switch <span class="emphasis"><em>--driver</em></span>. |
| Defaults to <span class="emphasis"><em>org.hsqldb.jdbc.JDBCDriver</em></span>. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">truststore</span> |
| </p> |
| </td><td> |
| TLS trust keystore store file path as documented in the |
| TLS section of the Listeners chapter of the |
| <a class="link" href="http://hsqldb.org/doc/2.0/guide/index.html" target="_top"> |
| HyperSQL User Guide</a> |
| You usually only need to set this if the server is using a |
| non-publicly-certified certificate (like a self-signed |
| self-ca'd cert). |
| Relative paths will be resolved relative to the |
| <code class="varname">${user.dir}</code> |
| system property at JRE invocation time. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">transiso</span> |
| </p> |
| </td><td> |
| Specify the Transaction Isolation Level with an all-caps |
| string, exactly as listed in he Field Summary of the Java |
| API Spec for the class |
| <code class="classname">java.sql.Connection</code>. |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| <p> |
| Property and SqlTool command-line switches override settings made |
| in the configuration file. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_dsswitch-sect"></a>Switching Data Sources</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| The \j command lets you switch JDBC Data Sources in your SQL files |
| (or interactively). |
| "\?" shows the syntax to make a connection by either RCData urlid |
| or by name + password + JDBC Url. |
| The urlid variant uses RC file of |
| <code class="filename">$HOME/sqltool.rc</code>. |
| We will add a way to specify an RC file if there is any demand for |
| that. |
| </p> |
| <p> |
| You can start SqlTool without any JDBC Connection by specifying no |
| Inline RC and urlid of "-" (just a hyphen). |
| If you don't need to specify any SQL file paths, you can skip the |
| hypen, as in this example. |
| <div class="informalexample"> |
| <pre class="screen">java -jar $HSQLDB_HOME/lib/sqltool.jar --setVar=v1=one</pre> |
| </div> |
| (The "-" is required when specifying one or more SQL files, in order |
| to distinguish urlid-spec from file-spec). |
| Consequently, if you invoke SqlTool with no parameters at all, you |
| will get a SqlTool session with no JDBC Connection. |
| You will obviously need to use \j before doing any database work. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_ilauth-sect"></a>Using Inline RC Authentication</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| Inline RC authentication setup is accomplished by using the |
| <code class="literal">--inlineRc</code> command-line switch on SqlTool. |
| The <code class="literal">--inlineRc</code> command-line switch takes |
| a comma-separated list of key/value elements. |
| The <code class="literal">url</code> and <code class="literal">user</code> elements |
| are required. The rest are optional. |
| The <code class="literal">--inlineRc</code> switch is the only case where |
| you can give SQL file paths without a preceding urlid indicator |
| (an urlid or -). |
| The program knows not to look for an urlid if you give an inline. |
| </p> |
| <div class="variablelist"> |
| <table border="0"> |
| <col valign="top" align="left"> |
| <tbody> |
| <tr> |
| <td> |
| <p> |
| <span class="term"><code class="varname">url</code></span> |
| </p> |
| </td><td> |
| The JDBC URL of the database you wish to connect to. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term"><code class="varname">user</code></span> |
| </p> |
| </td><td> |
| The username to connect to the database as. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term"><code class="varname">charset</code></span> |
| </p> |
| </td><td> |
| Sets the character encoding. Overrides the platform default, or |
| what you have set by env variables or Java system properties. |
| (Does not effect stdin or stdout). |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term"><code class="varname">truststore</code></span> |
| </p> |
| </td><td> |
| The TLS trust keystore file path as documented in the TLS chapter. |
| Relative paths will be resolved relative to the current directory. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term"><code class="varname">transiso</code></span> |
| </p> |
| </td><td> |
| <code class="classname">java.sql.Connection</code> transaction |
| isolation level to connect with, as specified in the Java |
| API spec. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term"><code class="varname">password</code></span> |
| </p> |
| </td><td> |
| <p> |
| You may only use this element to set empty password, like |
| <div class="informalexample"> |
| <pre class="screen"> password=</pre> |
| </div>For any other password value, omit the |
| <code class="literal">password</code> element and you will be prompted |
| for the value. |
| </p> |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| <p> |
| (Use the <code class="literal">--driver</code> switch instead of |
| <code class="literal">--inlineRc</code> to specify a JDBC driver class). |
| Here is an example of invoking SqlTool to connect to a standalone database. |
| <div class="informalexample"> |
| <pre class="screen">java -jar $HSQLDB_HOME/lib/sqltool.jar --inlineRc=url=jdbc:hsqldb:file:/home/dan/dandb,user=dan</pre> |
| </div> |
| |
| </p> |
| <p> |
| For security reasons, you cannot specify a non-empty password as |
| an argument. You |
| will be prompted for a password as part of the login process. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_logging-sect"></a>Logging</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| Both the \l command and all warnings and error messages now use |
| a logging facility. |
| The logging facility hands off to Log4j if Log4j is found in the |
| classpath, and otherwise will hand off to |
| <code class="classname">java.util.logging</code>. |
| The default behavior of <code class="classname">java.util.logging</code> |
| should work fine for most users. |
| If you are using log4j and are redirecting with pipes, you may |
| want to configure a Console Appender with target of |
| <code class="literal">"System.err"</code> so that error output will go to |
| the error stream (all console output for |
| <code class="classname">java.util.logging</code> goes to stderr by default). |
| See the API specs for Log4j and for J2SE for how to configure |
| either product. |
| If you are embedding SqlTool in a product to process SQL files, |
| I suggest that you use log4j. |
| <code class="classname">java.util.logging</code> is neither scalable nor |
| well-designed. |
| </p> |
| <p> |
| Run the command <code class="literal">\l?</code> to see how to use the |
| logging command <code class="literal">\l</code> in your SQL files (or |
| interactively), including what logging levels you may specify. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_int-sect"></a>Interactive Usage</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| Do read the |
| <a class="link" href="#sqltool_baremin-sect" title="The Bare Minimum">The Bare Minimum</a> |
| section before you read this section. |
| </p> |
| <p> |
| You run SqlTool interactively by specifying no SQL filepaths on |
| the SqlTool command line. Like this. |
| <div class="informalexample"> |
| <pre class="screen"> java -jar $HSQLDB_HOME/lib/sqltool.jar urlid</pre> |
| </div> |
| </p> |
| <div class="procedure"> |
| <a name="N1055A"></a> |
| <p class="title"> |
| <b>Procedure 1.3. What happens when SqlTool is run interactively |
| (using all default settings) |
| </b> |
| </p> |
| <ol type="1"> |
| <li> |
| <p> |
| SqlTool starts up and connects to the specified database, |
| using your SqlTool configuration file |
| (as explained in the |
| <a class="link" href="#sqltool_auth-sect" title="RC File Authentication Setup">RC File Authentication Setup</a> section). |
| </p> |
| </li> |
| <li> |
| <p> |
| SQL file <code class="filename">auto.sql</code> in your home directory |
| is executed (if there is one), |
| </p> |
| </li> |
| <li> |
| <p> |
| SqlTool displays a |
| banner showing the SqlTool and SqlFile version numbers and |
| describes the different command types that you can give, as |
| well as commands to list all of the specific commands available |
| to you. |
| </p> |
| </li> |
| </ol> |
| </div> |
| <p> |
| You exit your session by using the "\q" special command or ending |
| input (like with Ctrl-D or Ctrl-Z). |
| </p> |
| <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Important"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Important]" src="../images/db/important.png"></td><th align="left">Important</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| Any command may be preceded by space characters. |
| Special Commands, Edit Buffer Commands, PL Commands, Macros always |
| consist of just one line. |
| </p> |
| <p> |
| These rules do not apply at all to |
| <a class="link" href="#sqltool_raw-sect" title="Raw Mode">Raw Mode</a>. |
| Raw mode is for use by advanced users when they want to completely |
| bypass SqlTool processing in order to enter a chunk of text for |
| direct transmission to the database engine. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10578"></a>SqlTool Command-Line Editing</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| If you are really comfortable with grep, perl, or vim, you will |
| instantly be an expert with SqlTool command-line editing. |
| Due to limitations of Java I/O, we can't use up-arrow recall, |
| which many people are used to from DosKey and Bash shell. |
| If you don't know how to use regular expressions, and don't want |
| to learn how to use them, then just forget command-recall. |
| <div class="itemizedlist"> |
| <p class="title"> |
| <b>Basic command entry (i.e., without regexps)</b> |
| </p> |
| <ul type="disc"> |
| <li> |
| Just type in your command, and use the backspace-key to |
| fix mistakes on the same line. |
| </li> |
| <li> |
| If you goof up a multi-line command, just hit the ENTER |
| key twice to start over. (The command will be moved to |
| the buffer where it will do no harm). |
| </li> |
| <li> |
| Use the ":h" command to view your command history. |
| You can use your terminal emulator scroll bar and copy |
| and paste facility to repeat commands. |
| </li> |
| <li> |
| As long as you don't need to change text that is already |
| in a command, you can easily repeat commands from the |
| history like ":14;" to re-run command number 14 from |
| history. |
| </li> |
| <li> |
| Expanding just a bit from the previous item, you can |
| add on to a previous command by running a command like |
| ":14a" (where the "a" means <span class="emphasis"><em>append</em></span>). |
| </li> |
| <li> |
| See the <a class="link" href="#sqltool_macro-sect" title="Macros">Macros</a> |
| section about how to set and use macros. |
| </li> |
| </ul> |
| </div> |
| |
| </p> |
| <p> |
| If you use regular expressions to search through your command |
| history, or to modify commands, be aware that the command type |
| of commands in history are fixed. |
| You can search and modify the text after a \ or * prefix (if any), |
| but you can't search on or change a prefix (or add or remove one). |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1059C"></a>Command Types</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| When you are typing into SqlTool, you are always typing part of |
| the <span class="emphasis"><em>immediate command</em></span>. |
| If the immediate command is an SQL statement, it is executed as |
| soon as SqlTool reads in the trailing (unquoted) semi-colon. |
| Commands of the other command types are executed as soon as you |
| hit ENTER. |
| The interactive : commands can perform actions with or on the |
| edit buffer. |
| The <span class="emphasis"><em>edit buffer</em></span> usually contains a copy of |
| the last command executed, and you can always view it with the :b |
| command. |
| If you never use any : commands, you can entirely ignore the |
| edit buffer. |
| If you want to repeat commands or edit previous commands, you |
| will need to work with the edit buffer. |
| The immediate command contains whatever (and exactly what) |
| you type. |
| The command history and edit buffer may contain any type of |
| command other than comments and : commands |
| (i.e., : commands and comments are just not copied to the history |
| or to the edit buffer). |
| </p> |
| <p> |
| Hopefully an example will clarify the difference between the |
| immediate command and the edit buffer. |
| If you type in the edit buffer Substitution command |
| "<code class="literal">:s/tbl/table/</code>", the :s command that you typed |
| is the immediate command (and it will never be stored to the |
| edit buffer or history, since it is a : command), but the purpose |
| of the substitution command is to modify the contents of the |
| edit buffer (perform a substitution on it)-- the goal being that |
| after your substitutions you would execute the buffer with the |
| "<code class="literal">:;</code>" command. |
| The ":a" command is special in that when you hit ENTER to execute |
| it, it copies the contents of the edit buffer to a new immediate |
| command and leaves you in a state where you are |
| <span class="emphasis"><em>appending</em></span> to that |
| <span class="emphasis"><em>immediate</em></span> command (nearly) exactly as if |
| you had just typed it in. |
| </p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N105B5"></a>Command Types</h2> |
| </div> |
| </div> |
| </div> |
| <div class="variablelist"> |
| <p class="title"> |
| <b>Command types</b> |
| </p> |
| <table border="0"> |
| <col valign="top" align="left"> |
| <tbody> |
| <tr> |
| <td> |
| <p> |
| <span class="term">SQL Statement</span> |
| </p> |
| </td><td> |
| <p> |
| Any command that you enter which does not begin with "\", ":", |
| "* " or "/" is an SQL Statement. |
| The command is not terminated when you hit ENTER, like most |
| OS shells. |
| You terminate SQL Statements with either ";" or with a blank |
| line. |
| In the former case, the SQL Statement will be executed against |
| the SQL database and the command will go into the edit |
| buffer and SQL command history for editing or viewing later on. |
| In the former case, |
| <span class="emphasis"><em>execute against the SQL database</em></span> means |
| to transmit the SQL text to the database engine for execution. |
| In the latter case (you end an SQL Statement with a blank |
| line), the command will go to the edit buffer and SQL history, |
| but will not be executed (but you can execute it later from the |
| edit buffer). |
| </p> |
| <p> |
| (Blank lines are only interpreted this way when SqlTool is |
| run interactively. |
| In SQL files, blank lines inside of SQL statements remain |
| part of the SQL statement). |
| </p> |
| <p> |
| As a result of these termination rules, whenever you are |
| entering text that is not a Special Command, Edit Buffer / |
| History Command, or PL Command, you are always |
| <span class="emphasis"><em>appending</em></span> lines to an SQL Statement |
| or comment. |
| (In the case of the first line, you will be appending to an |
| empty SQL statement. I.e. you will be starting a new SQL |
| Statement or comment). |
| </p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">Special Command</span> |
| </p> |
| </td><td> |
| Run the command "\?" to list the Special Commands. |
| All of the Special Commands begin with "\". |
| I'll describe some of the most |
| useful Special Commands below. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">Edit Buffer / History Command</span> |
| </p> |
| </td><td> |
| Run the command ":?" to list the Edit-Buffer/History Commands. |
| All of these commands begin with ":". |
| These commands use commands from the command history, or |
| operate upon the edit "buffer", so that |
| you can edit and/or (re-)execute previously entered commands. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">PL Command</span> |
| </p> |
| </td><td> |
| <p> |
| Procedural Langage commands. |
| Run the command "*?" to list the PL Commands. |
| All of the PL Commands begin with "*". |
| PL commands are for setting and using scripting variables |
| and conditional and flow control statements like |
| <code class="literal">* if</code> and <code class="literal">* while</code>. |
| A few PL features (such as macros and updating and |
| selecing data directly from/to files) can be a real |
| convenience for nearly all users, so these features will be |
| discussed briefly in this section. |
| More detailed explanation of PL variables and the other |
| PL features, with examples, are covered in the |
| <a class="link" href="#sqltool_pl-sect" title="SqlTool Procedural Language">SqlTool Procedural Language</a> section. |
| </p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">Macro Command</span> |
| </p> |
| </td><td> |
| Macro definition and usage commands. |
| Run the command "/?" to show the define, list, or use macros. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">Raw Mode</span> |
| </p> |
| </td><td> |
| The descriptions of command-types above do not apply to |
| <a class="link" href="#sqltool_raw-sect" title="Raw Mode">Raw Mode</a>. |
| In raw mode, SqlTool |
| doesn't interpret what you type at all. It all just |
| goes into the edit buffer which you can send to the database |
| engine. |
| Beginners can safely ignore raw mode. |
| You will never encounter it unless you run the "\." |
| special command, or define a stored procedure or function. |
| See the |
| <a class="link" href="#sqltool_raw-sect" title="Raw Mode">Raw Mode</a> section |
| for the details. |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N105FB"></a>Special Commands</h2> |
| </div> |
| </div> |
| </div> |
| <div class="variablelist"> |
| <p class="title"> |
| <b>Essential Special Commands</b> |
| </p> |
| <table border="0"> |
| <col valign="top" align="left"> |
| <tbody> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\?</span> |
| </p> |
| </td><td> |
| help |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\q</span> |
| </p> |
| </td><td> |
| quit |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\j...</span> |
| </p> |
| </td><td> |
| View JDBC Data Source details or connect up to a JDBC Data |
| Source (replacing the current connection, if any). |
| Run \? to see the syntax for the different usages. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\i path/to/script.sql</span> |
| </p> |
| </td><td> |
| execute the specified SQL script, then continue again |
| interactively. |
| Since SqlTool is a Java program, you can safely use forward |
| slashes in your file paths, regardless of your operating |
| system. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\=</span> |
| </p> |
| </td><td> |
| commit the current SQL transaction. |
| Most users are used to typing the SQL statement |
| <code class="literal">commit;</code>, but this command is crucial for |
| those databases which don't support the statement. |
| It's obviously unnecessary if you have auto-commit mode on. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\x?</span> |
| </p> |
| </td><td> |
| List a summary of DSV eXporting, and all available DSV options. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\m?</span> |
| </p> |
| </td><td> |
| List a summary of DSV iMporting, and all available DSV options. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\d?</span> |
| </p> |
| </td><td> |
| List a summary of the \d commands below. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\dt [filter_substring]</span> |
| </p> |
| </td><td></td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\dv [filter_substring]</span> |
| </p> |
| </td><td></td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\ds [filter_substring]</span> |
| </p> |
| </td><td></td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\di [table_name]</span> |
| </p> |
| </td><td></td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\dS [filter_substring]</span> |
| </p> |
| </td><td></td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\da [filter_substring]</span> |
| </p> |
| </td><td></td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\dn [filter_substring]</span> |
| </p> |
| </td><td></td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\du [filter_substring]</span> |
| </p> |
| </td><td></td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\dr [filter_substring]</span> |
| </p> |
| </td><td></td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\d* [filter_substring]</span> |
| </p> |
| </td><td> |
| <p> |
| Lists available objects of the given type. |
| <div class="itemizedlist"> |
| <ul type="disc"> |
| <li>t: non-system Tables</li> |
| <li>v: Views</li> |
| <li>s: Sequences</li> |
| <li>i: Indexes</li> |
| <li>S: System tables</li> |
| <li>a: Aliases</li> |
| <li>n: schema Names</li> |
| <li>u: database Users</li> |
| <li>r: Roles</li> |
| <li>*: all table-like objects</li> |
| </ul> |
| </div> |
| If your database supports schemas, then the schema name will |
| also be listed. |
| </p> |
| <p> |
| If you supply an optional <span class="emphasis"><em>filter substring</em></span>, |
| then only items which match the specified substring. |
| will be listed. |
| In most cases, the specified filter will be treated as a |
| regular expression matched against the candidate object names. |
| In order to take advantage of extreme server-side performance |
| benefits, however, in some cases the substring is passed to |
| the database server and the filter will processed by the server. |
| </p> |
| <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Important"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Important]" src="../images/db/important.png"></td><th align="left">Important</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| The regexp test is case-sensitive! |
| Even though in SQL queries and for the "\d objectname" |
| command object names are usually case-insensitive, for |
| the \dX commands, you must capitalize the filter |
| substring exactly as it will appear in the special |
| command output. |
| This is an inconvenience, since the database engine |
| will change names in SQL to default case unless you |
| double-quote the name, but that is server-side |
| functionality which cannot (portably) be reproduced by |
| SqlTool. |
| You can use spaces and other special characters in |
| the string. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Tip"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Tip]" src="../images/db/tip.png"></td><th align="left">Tip</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| Filter substrings ending with "." are special. |
| If a substring ends with ".", then this means to narrow |
| the search by the exact, case-sensitive schema name |
| given. |
| For example, if I run "\d* BLAINE.", this will list all |
| table-like database objects in the "BLAINE" schema. |
| The capitalization of the schema must be exactly the same |
| as how the schema name is listed by the "\dn" command. |
| You can use spaces and other special characters in |
| the string. |
| (I.e., enter the name exactly how you would enter it |
| inside of double-quotes in an SQL command). |
| This is an inconvenience, since the database engine |
| will change names in SQL to default case unless you |
| double-quote the name, but that is server-side |
| functionality which cannot (portably) be reproduced by |
| SqlTool. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Tip"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Tip]" src="../images/db/tip.png"></td><th align="left">Tip</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| The filter string "." (just a plain dot) means the current |
| session schema, for databases which support the concept |
| according to the SQL standard (HyperSQL database does). |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Important"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Important]" src="../images/db/important.png"></td><th align="left">Important</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| Indexes may not be searched for by |
| <span class="emphasis"><em>substring</em></span>, only by |
| exact target table name. |
| So if <code class="literal">I1</code> is an index on table |
| <code class="literal">T1</code>, then you list this index by running |
| "\di T1". |
| In addition, many database vendors will report on indexes |
| only if a target table is identified. |
| Therefore, "\di" with no argument will fail if your database |
| vendor does not support it. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\d objectname [[/]regexp]</span> |
| </p> |
| </td><td> |
| <p> |
| Lists names of columns in the specified table or view. |
| <code class="literal">objectname</code> may be a base table name or |
| a schema.object name. |
| </p> |
| <p> |
| If you supply a filter string, then only columns with a name |
| matching the given regular expression will be listd. |
| (If no special characters are used, this just means that |
| names containing the specified substring will match). |
| You'll find this filter is a great convenience compared to |
| other database utilities, where you have to list all columns |
| of large tables when you are only interested in one of them. |
| </p> |
| <p> |
| To narrow the displayed information based on all column |
| outputs, instead of just the column names, just prefix the |
| expression with /. |
| For example, to list all INTERGER columns, you could run |
| <code class="literal">\d mytable /INTEGER</code>. |
| </p> |
| <div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Tip"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Tip]" src="../images/db/tip.png"></td><th align="left">Tip</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| When working with real data (as opposed to learning or playing), |
| I often find it useful to run two SqlTool sessions in two |
| side-by-side terminal emulator windows. |
| I do all of my real work in one window, and use the other |
| mostly for \d commands. |
| This way I can refer to the data dictionary while writing SQL |
| commands, without having to scroll. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| <p> |
| This list here includes only the <span class="emphasis"><em>essential</em></span> |
| Special Commands, but n.b. that there are other useful Special |
| Commands which you can list by running <code class="literal">\?</code>. |
| (You can, for example, execute SQL from external SQL files, and |
| save your interactive SQL commands to files). |
| Some specifics of these other commands are specified immediately |
| below, and the |
| <a class="link" href="#sqltool_report-sect" title="Generating Text or HTML Reports">Generating Text or HTML Reports</a> |
| section explains how to use the "\o" and "\H" special commands to |
| generate reports. |
| </p> |
| <p> |
| Be aware that the <code class="literal">\!</code> Special Command does |
| not work for external programs that read from standard input. |
| You can invoke non-interactive and graphical interactive programs, |
| but not command-line interactive programs. |
| </p> |
| <p> |
| SqlTool executes <code class="literal">\!</code> programs directly, it does |
| not run an operating system shell (this is to avoid OS-specific |
| code in SqlTool). |
| Because of this, you can give as many command-line arguments |
| as you wish, but you can't use shell wildcards or redirection. |
| </p> |
| <p> |
| The \w command can be used to store any command in your SQL |
| history to a file. |
| Just restore the command to the edit buffer |
| with a command like "\-4" before you give the \w command. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N106CC"></a>Edit Buffer / History Commands</h2> |
| </div> |
| </div> |
| </div> |
| <div class="variablelist"> |
| <p class="title"> |
| <b>Edit Buffer / History Commands</b> |
| </p> |
| <table border="0"> |
| <col valign="top" align="left"> |
| <tbody> |
| <tr> |
| <td> |
| <p> |
| <span class="term">:?</span> |
| </p> |
| </td><td> |
| help |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">:b</span> |
| </p> |
| </td><td> |
| List the current contents of the edit buffer. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">:h</span> |
| </p> |
| </td><td> |
| Shows the Command History. |
| For each command which has been executed (up to the max |
| history length), the SQL command history will show the |
| command; its command number (#); and also how many commands |
| <span class="emphasis"><em>back</em></span> it is (as a negative number). |
| : commands are never added to the history list. |
| You can then use either form of the command identifier to |
| recall a command to the edit buffer (the command described |
| next) or as the target of any of the following : commands. |
| This last is accomplished in a manner very similar to the |
| vi editor. |
| You specify the target command number between the colon |
| and the command. |
| As an example, if you gave the command |
| <code class="literal">:s/X/Y/</code>, that would perform the |
| substitution on the contents of the edit buffer; but if you |
| gave the command <code class="literal">:-3 s/X/Y/</code>, that would |
| perform the substitution on the command 3 back in the |
| command history (and copy the output to the edit buffer). |
| Also, just like vi, you can identify the command to recall |
| by using a regular expression inside of slashes, like |
| <code class="literal">:/blue/ s/X/Y/</code> to operate on the last |
| command you ran which contains "blue". |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">:13 OR :-2 OR :/blue/</span> |
| </p> |
| </td><td> |
| <p> |
| Recalls a command from Command history to the edit buffer. |
| Enter ":" followed by the positive command number from |
| Command history, like ":13"... or ":" followed by a negative |
| number like ":-2" for two commands back in the Command |
| history... or ":" followed by a regular expression inside |
| slashes, like ":/blue/" to recall the last command which |
| contains "blue". |
| The specified command will be written to the edit buffer |
| so that you can execute it or edit it using the commands below. |
| </p> |
| <p> |
| As described under the :h command immediately above, |
| you can follow the command number here with |
| any of the commands below to perform the given operation |
| on the specified command from history instead of on the |
| edit buffer contents. |
| So, for example, ":4;" would load command 4 from history |
| then execute it (see the ":;" command below). |
| </p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">:;</span> |
| </p> |
| </td><td> |
| Executes the SQL, Special or PL statement in the edit buffer |
| (by default). |
| This is an extremely useful command. |
| It's easy to remember because it consists of ":", meaning |
| <span class="emphasis"><em>Edit Buffer Command</em></span>, plus a |
| line-terminating ";", (which generally means to execute an |
| SQL statement, though in this case it will also execute a |
| special or PL command). |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">:a</span> |
| </p> |
| </td><td> |
| <p> |
| Enter append mode with the contents of the edit buffer (by |
| default) as the current command. |
| When you hit ENTER, things will be nearly exactly the same |
| as if you |
| physically re-typed the command that is in the edit buffer. |
| Whatever lines you type next will be appended to the immediate |
| command. |
| As always, you then have the choice of hitting ENTER to |
| execute a Special or PL command, entering a blank line to |
| store back to the edit buffer, or end a SQL statement with |
| semi-colon and ENTER to execute it. |
| </p> |
| <p> |
| You can, optionally, put a string after the :a, in which |
| case things will be exactly as just described except the |
| additional text will also be appended to the new immediate |
| command. |
| If you put a string after the :a which ends with ;, then |
| the resultant new immediate command will just be executed |
| right away, as if you typed in and entered the entire thing. |
| </p> |
| <p> |
| If your edit buffer contains |
| <code class="literal">SELECT x FROM mytab</code> and you run |
| <code class="literal">a:le</code>, the resultant command will be |
| <code class="literal">SELECT x FROM mytable</code>. |
| If your edit buffer contains |
| <code class="literal">SELECT x FROM mytab</code> and you run |
| <code class="literal">a: ORDER BY y</code>, the resultant command will be |
| <code class="literal">SELECT x FROM mytab ORDER BY y</code>. |
| Notice that in the latter case the append text begins with a |
| space character. |
| </p> |
| <p> |
| You may notice that you can't use the left-arrow or backspace |
| key to back up over the original text. |
| This is due to Java and portability constraints. |
| If you want to edit existing text, then you shouldn't use the |
| Append command. |
| </p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">:s/from regex/to string/switches</span> |
| </p> |
| </td><td> |
| <p> |
| The Substitution Command is the primary method for SqlTool |
| command editing-- it operates upon the current edit buffer |
| by default. |
| The "to string" and the "switches" are both optional (though |
| the final "/" is not). |
| To start with, I'll discuss the use and behavior if you don't |
| supply any substitution mode switches. |
| </p> |
| <p> |
| Don't use "/" if it occurs in either "from string" or "to |
| string". |
| You can use any character that you want in place of "/", but |
| it must not occur in the <span class="emphasis"><em>from</em></span> or |
| <span class="emphasis"><em>to</em></span> strings. |
| Example |
| <div class="informalexample"> |
| <pre class="screen"> :s@from string@to string@</pre> |
| </div> |
| </p> |
| <p> |
| The <span class="emphasis"><em>to string </em></span> is substituted for the first |
| occurrence of the (case-specific) |
| <span class="emphasis"><em>from string</em></span>. |
| The replacement will consider the entire SQL statement, even |
| if it is a multi-line statement. |
| </p> |
| <p> |
| In the example above, the from regex was a plain string, but |
| it is interpreted as a regular expression so you can do |
| all kinds of powerful substitutions. |
| See the <code class="literal">perlre</code> man page, or the |
| <a class="link" href="http://http://java.sun.com/javase/6/docs/api/java/util/regex/Pattern.html" target="_top">java.util.regex.Pattern</a> |
| API Spec for everything you need to know about extended |
| regular expressions. |
| </p> |
| <p> |
| Don't end a <span class="emphasis"><em>to</em></span> string with ";" in attempt |
| to make a command execute. |
| There is a substitution mode switch to use for that purpose. |
| </p> |
| <p> |
| You can use any combination of the substitution mode switches. |
| <div class="itemizedlist"> |
| <ul type="disc"> |
| <li> |
| <p> |
| Use "i" to make the searches for |
| <span class="emphasis"><em>from regex</em></span> case insensitive. |
| </p> |
| </li> |
| <li> |
| <p> |
| Use "g" to substitute Globally, i.e., to subsitute |
| <span class="emphasis"><em>all</em></span> occurrences of the |
| <span class="emphasis"><em>from regex</em></span> instead of only the |
| first occurrence found. |
| </p> |
| </li> |
| <li> |
| <p> |
| Use ";" to execute the command immediately after the |
| substitution is performed. |
| </p> |
| </li> |
| <li> |
| <p> |
| Use "m" for ^ and $ to match each line-break in a |
| multi-line edit buffer, instead of just at the very |
| beginning and every end of the entire buffer. |
| </p> |
| </li> |
| </ul> |
| </div> |
| |
| </p> |
| <p> |
| If you specify a command number (from the command history), |
| you end up with a feature very reminiscent of vi, but even |
| more powerful, since the Perl/Java regular expression are |
| a superset of the vi regular expressions. |
| As an example, |
| <div class="informalexample"> |
| <pre class="screen"> :24 s/pin/needle/g;</pre> |
| </div> would start with command number 24 from |
| command history, substitute "needle" for all occurrences of |
| "pin", then execute the result of that substitution |
| (and this final statement will of course be copied to the |
| edit buffer and to command history). |
| </p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">:w /path/to/file.sql</span> |
| </p> |
| </td><td> |
| This appends the contents of the current buffer (by default) |
| to the specified file. |
| Since what is being written are Special, PL, or SQL commands, |
| you are effectively creating an SQL script. |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| <p> |
| I find the ":/regex/" and ":/regex/;" constructs particularly |
| handy for every-day usage. |
| <div class="informalexample"> |
| <pre class="screen"> :/\\d/;</pre> |
| </div>re-executes the last \d command that you gave |
| (The extra "\" is needed to escape the special meaning of "\" |
| in regular expressions). |
| It's great to be able to recall and execute the last "insert" |
| command, for example, without needing to check the history or |
| keep track of how many commands back it was. To re-execute |
| the last insert command, just run ":/insert/;". |
| If you want to be safe about it, do it in two steps to verify |
| that you didn't accidentally recall some other command which |
| happened to contain the string "insert", like |
| <div class="informalexample"> |
| <pre class="screen"> :/insert/ |
| :;</pre> |
| </div>(Executing the last only if you are |
| satisfied when SqlTool reports what command it restored). |
| Often, of course, you will want to change the command before |
| re-executing, and that's when you combine the :s and :a commands. |
| </p> |
| <p> |
| We'll finish up with a couple fine points about Edit/Buffer |
| commands. |
| You generally can't use PL variables in Edit/Buffer commands, to |
| eliminate possible ambiguities and complexities when modifying |
| commands. |
| The :w command is an exception to this rule, since it can be |
| useful to use variables to determine the output file, and this |
| command does not do any "editing". |
| </p> |
| <p> |
| The :? help explains how you can change the default regular |
| expression matching behavior (case sensitivity, etc.), but |
| you can always use syntax like "(?i)" inside of your regular |
| expression, as described in the Java API spec for class |
| <code class="classname"><a class="classname" href="http://http://java.sun.com/javase/6/docs/api/java/util/regex/Pattern.html" target="_top"> |
| java.util.regex.Pattern</a></code>. |
| History-command-matching with the /regex/ construct is |
| purposefully liberal, matching any portion of the command, |
| case sensitive, etc., but you can still use the method just |
| described to modify this behavior. In this case, you could |
| use "(?-i)" at the beginning of your regular expression to |
| be case-sensitive. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_interactive_pl_commands-sect"></a>PL Commands</h2> |
| </div> |
| </div> |
| </div> |
| <div class="variablelist"> |
| <p class="title"> |
| <b>Essential PL Command</b> |
| </p> |
| <table border="0"> |
| <col valign="top" align="left"> |
| <tbody> |
| <tr> |
| <td> |
| <p> |
| <span class="term">* VARNAME = value</span> |
| </p> |
| </td><td> |
| <p> |
| Set the value of a variable. |
| If the variable doesn't exist yet, it will be created. |
| The most common use for this is so that you can later use |
| it in SQL statements, print statements, and PL conditionals, |
| by using the <code class="literal">*{VARNAME}</code> or |
| <code class="literal">*{:VARNAME}</code> construct. |
| The only difference between <code class="literal">*{literal}</code> and |
| <code class="literal">*{:VARNAME}</code> is that the former produces an |
| error if VARNAME is not set, whereas the latter will expand |
| to a zero-length string if VARNAME is not set. |
| </p> |
| <p> |
| If you put variable definitions into the SQL file |
| <code class="filename">auto.sql</code> in your home directory, those |
| variables will always be available for interactive use. |
| </p> |
| <p> |
| PL variables can be expanded within all commands other than |
| : edit/history commands. |
| </p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">* load VARNAME /file/path.txt</span> |
| </p> |
| </td><td> |
| Sets VARNAME to the content of the specified ASCII file. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">* prepare VARNAME</span> |
| </p> |
| </td><td> |
| Indicate that next command should be a SQL INSERT or UPDATE |
| command containing one question mark. |
| The value of VARNAME will be substuted for the ? variable. |
| This does work for CLOB columns. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">* VARNAME _</span> |
| </p> |
| </td><td> |
| When next SQL command is run, instead of displaying the rows, |
| just store the very first column value to variable VARNAME. |
| This works for CLOB columns too. |
| It also works with Oracle XML type columns if you use |
| column labels and the <code class="literal">getclobval</code> function. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">* VARNAME ~</span> |
| </p> |
| </td><td> |
| <p> |
| Exactly the same as |
| <pre class="programlisting"> * VARNAME ~</pre> |
| except that the fetched results will be displayed in addition |
| to setting the variable. |
| </p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">* dump VARNAME /file/path.txt</span> |
| </p> |
| </td><td> |
| Store the value of VARNAME to the specified ASCII file. |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N107C2"></a>? Variable</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| You don't set the ? variable. |
| It is just like the Bourne shell variable $? in that it is always |
| automatically set to the first value of a result set (or the |
| return value of other SQL commands). |
| It works just like the <code class="literal">* VARNAME ~</code> |
| command described above, but it all happens automatically. |
| You can, of course, dereference ? like any PL variable, but it |
| does not list with the <code class="literal">list</code> and |
| <code class="literal">listvalues</code> commands. |
| You can see the value whenever you want by running |
| <div class="informalexample"> |
| <pre class="programlisting"> \p *{?}</pre> |
| </div> |
| |
| </p> |
| <p> |
| Note that PL commands are used to upload and download column |
| values to/from local ASCII files, but the corresponding actions |
| for binary files use the special \b commands. |
| This is because PL variables are used for ASCII values and |
| you can store any number of column values in PL variables. |
| This is not true for binary column values. |
| The \b commands work with a single binary byte buffer. |
| </p> |
| <p> |
| See the <a class="link" href="#sqltool_pl-sect" title="SqlTool Procedural Language">SqlTool Procedural Language</a> section |
| below for information on using variables in other ways, and |
| information on the other PL commands and features. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="sqltool_binary_files-sect"></a> |
| Storing and retrieving binary files</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| You can upload binary files such as photographs, audio files, |
| or serialized Java objects into database columns. |
| SqlTool keeps one binary buffer which you can load from files |
| with the \bl command, or from a database query by doing a |
| one-row query for any non-displayable type (including |
| <code class="literal">BLOB</code>, <code class="literal">OBJECT</code>, and |
| <code class="literal">OTHER</code>). |
| In the latter case, the data returned for the first |
| non-displayable column of the first result row will be stored |
| into the binary buffer. |
| </p> |
| <p> |
| Once you have data in the binary buffer, you can upload it |
| to a database column (including <code class="literal">BLOB</code>, |
| <code class="literal">OBJECT</code>, and <code class="literal">OTHER</code> type |
| columns), or save it to a file. |
| The former is accomplished by the special command \bp followed |
| by a <span class="emphasis"><em>prepared</em></span> SQL query containing one |
| question mark place-holder to indicate where the data gets |
| inserted. |
| The latter is accomplished with the \bd command. |
| </p> |
| <p> |
| You can also store the output from normal, displayable column |
| into the binary buffer by using the special command \b. |
| The very first column value from the first result row of the |
| next SQL command will be stored to the binary byte buffer. |
| </p> |
| <div class="example"> |
| <a name="N107FC"></a> |
| <p class="title"> |
| <b>Example 1.2. Inserting binary data into database from a file</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="screen"> \bl /tmp/favoritesong.mp3 |
| \bp |
| INSERT INTO musictbl (id, stream) VALUES(3112, ?);</pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| <div class="example"> |
| <a name="N10801"></a> |
| <p class="title"> |
| <b>Example 1.3. Downloading binary data from database to a file</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="screen"> SELECT stream FROM musictbl WHERE id = 3112; |
| \bd /tmp/favoritesong.mp3</pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| <p> |
| You can also store and retrieve text column values to/from |
| ASCII files, as documented in the |
| <a class="link" href="#sqltool_interactive_pl_commands-sect" title="PL Commands">Essential PL Command</a> |
| section. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1080C"></a>Command History</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| The SQL history shown by the \h command, and used by other commands, |
| is truncated to 100 entries, since its utility comes from being |
| able to quickly view the history list. |
| You can change the history length by setting the system property |
| <code class="literal">sqltool.historyLength</code> to the desire integer |
| value (using any of the System Property mechanisms provided by |
| Java). |
| If there is any demand, I'll make the setting of this value more |
| convenient. |
| </p> |
| <p> |
| The SQL history list contains all executed commands other than |
| Edit Buffer commands and comments, even if the command has a |
| syntax error or fails upon execution. |
| The reason for including bad commands is so that you can |
| recall and fix them if you wish to. |
| The same applies to the edit buffer. |
| If you copy a command to the edit buffer by entering blank |
| line, or if you edit the edit buffer, that edit buffer value |
| will never make it into the command history until and if |
| you execute it. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10816"></a>Shell scripting and command-line piping</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| You normally use non-interactive mode for input piping. |
| You specify "-" as the SQL file name. |
| See the <a class="link" href="#sqltool_scripting-sect" title="Piping and shell scripting">Piping and shell scripting</a> |
| subsection of the Non-Interactive chapter. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1081F"></a>Emulating Non-Interactive mode</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| You can run SqlTool <span class="emphasis"><em>interactively</em></span>, but |
| have SqlTool behave exactly as if it were processing an SQL |
| file (i.e., no command-line prompts, error-handling |
| that defaults to fail-upon-error, etc.). |
| Just specify "-" as the SQL file name in the command line. |
| This is a good way to test what SqlTool will do when it |
| encounters any specific command in an SQL file. |
| See the <a class="link" href="#sqltool_scripting-sect" title="Piping and shell scripting">Piping and shell scripting</a> |
| subsection of the Non-Interactive chapter for an example. |
| </p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_nonint-sect"></a>Non-Interactive</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| Read the <a class="link" href="#sqltool_int-sect" title="Interactive Usage">Interactive Usage</a> |
| section if you have not already, |
| because much of what is in this section builds upon that. |
| You can skip all discussion about Command History and the |
| edit buffer if you will not use those interactive features. |
| </p> |
| <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Important"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Important]" src="../images/db/important.png"></td><th align="left">Important</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| If you're doing data updates, remember to issue a commit command |
| or use the <code class="literal">--autoCommit</code> switch. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| As you'll see, SqlTool has many features that are very |
| convenient for scripting. But what really makes it superior for |
| automation tasks (as compared to SQL tools from other vendors) |
| is the ability to reliably detect errors and to control JDBC |
| transactions. |
| SqlTool is designed so that you can reliably determine if errors |
| occurred within SQL scripts themselves, and from the invoking |
| environment (for example, from a perl, Bash, or Python script, |
| or a simple cron tab invocation). |
| </p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="sqltool_sqlswitch-sect"></a>Giving SQL on the Command Line</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| If you just have a couple Commands to run, you can run them |
| directly from the comand-line or from a shell script without an |
| SQL file, like this. |
| <div class="informalexample"> |
| <pre class="screen">java -jar $HSQLDB_HOME/lib/sqltool.jar --sql="SQL statement;" urlid</pre> |
| </div> |
| |
| <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Note"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Note]" src="../images/db/note.png"></td><th align="left">Note</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p>The <code class="literal">--sql</code> switch automatically implies |
| <code class="literal">--noinput</code>, so if you want to execute the |
| specified SQL before <span class="emphasis"><em>and in addition to</em></span> an |
| interactive session (or stdin piping), then you must also give |
| the <span class="emphasis"><em>--stdinput</em></span> switch. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| |
| </p> |
| <p> |
| Since SqlTool transmits SQL statements to the database engine |
| only when a line is terminated with ";", if you want feedback |
| from multiple SQL statements in an --sql expression, you will |
| need to use functionality of your OS shell to include |
| linebreaks after the semicolons in the expression. |
| With any Bourne-compatible shell, you can include linebreaks in |
| the SQL statements like this. |
| <div class="informalexample"> |
| <pre class="screen"> java -jar $HSQLDB_HOME/lib/sqltool.jar --sql=' |
| SQL statement number one; |
| SQL statement |
| number two; |
| SQL statement three; |
| ' urlid</pre> |
| </div> |
| |
| </p> |
| <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Note"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Note]" src="../images/db/note.png"></td><th align="left">Note</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| The multi-line examples in this section will only work as-is |
| with a Bourne-compatible shell. |
| With some ugliness they can be converted to C shell. |
| For Windows, you are better off to stick with SQL files for |
| multi-line input. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| If you don't need feedback, just separate the SQL commands |
| with semicolons and the entire expression will be |
| <a class="link" href="#sqltool_chunk-sect" title="Chunking">chunked</a>. |
| </p> |
| <p> |
| The <span class="emphasis"><em>--sql</em></span> switch is very useful for |
| setting shell variables to the output of SQL Statements, like |
| this. |
| <div class="informalexample"> |
| <pre class="programlisting"> # A shell script |
| USERCOUNT=`java -jar $HSQLDB_HOME/lib/sqltool.jar --sql=' |
| select count(*) from usertbl; |
| ' urlid` || { |
| # Handle the SqlTool error |
| } |
| echo "There are $USERCOUNT users registered in the database." |
| [ "$USECOUNT" -gt 3 ] && { # If there are more than 3 users registered |
| # Some conditional shell scripting</pre> |
| </div> |
| |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10871"></a>SQL Files</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Just give paths to sql text file(s) on the command line after |
| the <span class="emphasis"><em>urlid</em></span>. |
| </p> |
| <p> |
| Often, you will want to redirect output to a file, like |
| <div class="informalexample"> |
| <pre class="screen"> java -jar $HSQLDB_HOME/lib/sqltool.jar urlid file.sql... > /tmp/file.log 2>&1</pre> |
| </div> |
| </p> |
| <p> |
| You can also execute SQL files from an interactive session with |
| the "\i"' Special Command, |
| but be aware that the default behavior in an interactive |
| session is to continue upon errors. |
| If the SQL file was written without any concern for error |
| handling, then the file will continue to execute after errors |
| occur. |
| You could run <code class="literal">\c false</code> before |
| <code class="literal">\i filename</code>, but then your SqlTool session |
| will exit if an error is encountered in the SQL file. |
| If you have an SQL file without error handling, and you want |
| to abort that file when an error occurs, but not exit |
| SqlTool, the easiest way to accomplish this is usually to add |
| <code class="literal">\c false</code> to the top of the script. |
| </p> |
| <p> |
| If you specify multiple SQL files on the command-line, the |
| default behavior is to exit SqlTool immediately if any of |
| the SQL files encounters an error. |
| </p> |
| <p> |
| |
| <span class="bold"><strong> |
| SQL files themselves have ultimate control over error handling. |
| </strong></span> |
| Regardless of what command-line options are set, or what |
| commands you give interactively, if a SQL file gives error |
| handling statements, they will take precedence. |
| </p> |
| <p> |
| You can also use \i in SQL files. |
| This results in nested SQL files. |
| </p> |
| <p> |
| You can use the following SQL file, |
| <code class="filename"><a class="filename" href="#sample.sql-link"> |
| sample/sample.sql</a></code>, |
| from your HyperSQL distribution |
| <sup>[<a href="#ftn.samplelocFn" class="footnoteref">1</a>]</sup>. |
| It contains SQL as well as Special Commands making good |
| use of most of the Special Commands documented below. |
| </p> |
| <pre class="programlisting">/* |
| $Id: sample.sql 3637 2010-06-07 00:59:13Z unsaved $ |
| Exemplifies use of SqlTool. |
| PCTASK Table creation |
| */ |
| |
| /* Ignore error for these two statements */ |
| \c true |
| DROP TABLE pctasklist; |
| DROP TABLE pctask; |
| \c false |
| |
| \p Creating table pctask |
| CREATE TABLE pctask ( |
| id integer identity, |
| name varchar(40), |
| description varchar(256), |
| url varchar(80), |
| UNIQUE (name) |
| ); |
| |
| \p Creating table pctasklist |
| CREATE TABLE pctasklist ( |
| id integer identity, |
| host varchar(20) not null, |
| tasksequence int not null, |
| pctask integer, |
| assigndate timestamp default current_timestamp, |
| completedate timestamp, |
| show boolean default true, |
| FOREIGN KEY (pctask) REFERENCES pctask, |
| UNIQUE (host, tasksequence) |
| ); |
| |
| \p Granting privileges |
| GRANT select ON pctask TO public; |
| GRANT all ON pctask TO tomcat; |
| GRANT select ON pctasklist TO public; |
| GRANT all ON pctasklist TO tomcat; |
| |
| \p Inserting test records |
| INSERT INTO pctask (name, description, url) VALUES ( |
| 'task one', 'Description for task 1', 'http://cnn.com'); |
| INSERT INTO pctasklist (host, tasksequence, pctask) VALUES ( |
| 'admc-masq', 101, (SELECT id FROM pctask WHERE name = 'task one')); |
| |
| commit; |
| </pre> |
| <p> |
| You can execute this SQL file with a Memory Only database with |
| a command like |
| <div class="informalexample"> |
| <pre class="programlisting"> java -jar $HSQLDB_HOME/lib/sqltool.jar --sql=' |
| create user tomcat password "x"; |
| ' mem path/to/hsqldb/sample/sample.sql</pre> |
| </div> |
| This shows how you can mix SQL on the command line, and SQL inside |
| an SQL file. |
| </p> |
| <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Note"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Note]" src="../images/db/note.png"></td><th align="left">Note</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| The example above uses Bourne shell syntax. |
| C shell syntax would be similar. |
| You would need to use an SQL file to accomplish this on Windows. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| (The <code class="literal">--sql="create...;"</code> arguments create an |
| account which the script uses). |
| You should see error messages between the |
| <code class="literal">Continue-on-error...true</code> and |
| <code class="literal">Continue-on-error...false</code>. The script |
| purposefully runs commands that might fail there. |
| The reason the script does this is to perform |
| database-independent conditional table removals. |
| (The SQL clause <code class="literal">IF EXISTS</code> is more graceful |
| and succinct, so you may want to use that if you don't need to |
| support databases which don't support |
| <code class="literal">IF EXISTS</code>). |
| If an error occurs when continue-on-error is false, the |
| script would abort immedately. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="sqltool_scripting-sect"></a>Piping and shell scripting</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| You can of course, redirect output |
| <span class="emphasis"><em>from</em></span> SqlTool to a file |
| or another program. |
| <div class="informalexample"> |
| <pre class="screen">java -jar $HSQLDB_HOME/lib/sqltool.jar urlid file.sql > file.txt 2>&1 |
| |
| java -jar $HSQLDB_HOME/lib/sqltool.jar urlid file.sql 2>&1 | someprogram...</pre> |
| </div> |
| </p> |
| <p> |
| You can type commands in to SqlTool while being in |
| non-interactive mode by supplying "-" as the file name. |
| This is a good way to test how SqlTool will behave when |
| processing your SQL files. |
| <div class="informalexample"> |
| <pre class="screen"> java -jar $HSQLDB_HOME/lib/sqltool.jar urlid -</pre> |
| </div> |
| </p> |
| <p> |
| This is how you have SqlTool read its input from another |
| program: |
| <div class="example"> |
| <a name="N108CC"></a> |
| <p class="title"> |
| <b>Example 1.4. Piping input into SqlTool</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="screen"> echo "Some SQL commands with '$VARIABLES';" | |
| java -jar $HSQLDB_HOME/lib/sqltool.jar urlid -</pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| For a shell not as graceful as the Bourne-compatible shells, you |
| would need to type this all on the same line (or use a |
| line-continuation trick). |
| </p> |
| <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Warning"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Warning]" src="../images/db/warning.png"></td><th align="left">Warning</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| Beware of null stdin to SqlTool (or SqlFile). |
| At least with Java 6 on UNIX, <code class="classname">System.in</code> |
| returns megabytes of garbage for reads if stdin is closed. |
| I consider this an obvious bug. |
| Therefore, unlike any other program you would invoke from scripts, |
| check stdin before running any Java program that will read from |
| it. |
| I consider this a big ugly bug in Java. |
| This is not just theoretical, because many remote execution |
| environments will have stdin closed off. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| Make sure that you also read the |
| <a class="link" href="#sqltool_sqlswitch-sect" title="Giving SQL on the Command Line">Giving SQL on the Command Line</a> |
| section. |
| The <code class="literal">--sql</code> switch is a great facility to use |
| with shell scripts. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N108E1"></a>Optimally Compatible SQL Files</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| If you want your SQL scripts optimally compatible among other |
| SQL tools, then don't use any Special or PL Commands. |
| SqlTool has default behavior which I think is far superior to |
| the other SQL tools, but you will have to disable these |
| defaults in order to have optimally compatible behavior. |
| </p> |
| <p> |
| These switches provide compatibilty at the cost of poor |
| control and error detection. |
| <div class="itemizedlist"> |
| <ul type="disc"> |
| <li> |
| <p> |
| --continueOnErr=true |
| </p> |
| <p> |
| The output will still contain error messages about |
| everything that SqlTool doesn't like |
| (malformatted commands, SQL command failures, |
| empty SQL commands), but SqlTool will continue to |
| run. |
| Errors will not cause rollbacks (but that won't |
| matter because of the following setting). |
| </p> |
| </li> |
| <li>--autoCommit</li> |
| </ul> |
| </div> |
| |
| </p> |
| <p> |
| You don't have to worry about accidental expansion of |
| PL variables, since SqlTool will never expand PL variables |
| if you don't set any variables on the command line, or give |
| any "* " PL commands. |
| (And you could not have "* " commands in a compatible SQL |
| file). |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N108F4"></a>Comments</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Comments of the form <code class="literal">/*...*/</code> or |
| <code class="literal">--</code> behave as a SQL programmer would |
| expect, in all contexts other than in interactive |
| edit/history commands. |
| </p> |
| <p> |
| If a comment occurs outside of an SQL statement, SqlTool |
| will not send the comment to the database (to improve |
| performance). |
| Raw mode can be used to send just comments to the database. |
| In order to proactively catch accidents, SqlTool will complain |
| if you attempt to send an empty SQL statement (i.e., just |
| whitespace) to the database, even in raw mode. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10901"></a>Special Commands and Edit Buffer Commands in SQL Files</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Don't use Edit Buffer / History Commands in your sql files, |
| because they won't work. |
| Edit Buffer / History Commands are for interactive use only. |
| (But, see the |
| <a class="link" href="#sqltool_raw-sect" title="Raw Mode">Raw Mode</a> section |
| for an exception). |
| You can, of course, use any SqlTool command at all |
| interactively. |
| I just wanted to group together the commands most useful to |
| script-writers. |
| </p> |
| <div class="variablelist"> |
| <table border="0"> |
| <col valign="top" align="left"> |
| <tbody> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\q [abort message]</span> |
| </p> |
| </td><td> |
| <p> |
| Be aware that the \q command will cause SqlTool to |
| completely exit. |
| If a script <code class="filename">x.sql</code> has a \q command in |
| it, then it doesn't matter if the script is executed like |
| <pre class="screen"> java -jar .../sqltool.jar urlid a.sql x.sql z.sql</pre> |
| or if you use |
| \i to read it in interactively, or if another SQL file |
| uses \i to nest it. |
| If \q is encountered, SqlTool will quit. |
| See the <a class="link" href="#sqltool_pl-sect" title="SqlTool Procedural Language">SqlTool Procedural Language</a> |
| section for commands to abort an SQL file (or even parts |
| of an SQL file) without causing SqlTool to exit. |
| </p> |
| <p> |
| \q takes an optional argument, which is an abort message. |
| If you give an abort message, the message is displayed to |
| the user and SqlTool will exit with a failure status. |
| If you give no abort message, then SqlTool will exit |
| quietly with successful status. |
| As a result, <pre class="programlisting"> \q</pre> |
| means to make an immediate but graceful exit, whereas |
| <pre class="programlisting"> \q Message</pre> |
| means to abort immediately. |
| </p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\p [text to print]</span> |
| </p> |
| </td><td> |
| Print the given string to stdout. |
| Just give "\p" alone to print a blank line. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\i /path/to/file.sql</span> |
| </p> |
| </td><td> |
| Include another SQL file at this location. |
| You can use this to nest SQL files. |
| For database installation scripts I often have a master |
| SQL file which includes all of the other SQL files in the |
| correct sequence. |
| Be aware that the current continue-upon-error behavior |
| will apply to included files until such point as the SQL |
| file runs its own error handling commands. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\o [file/path.txt]</span> |
| </p> |
| </td><td> |
| <p> |
| Tee output to the specified file (or stop doing so). |
| See the |
| <a class="link" href="#sqltool_report-sect" title="Generating Text or HTML Reports">Generating Text or HTML Reports</a> |
| section. |
| </p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\=</span> |
| </p> |
| </td><td> |
| A database-independent way to commit your SQL session. |
| Useful for database which have no <code class="literal">COMMIT</code> |
| SQL statement. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\a [true|false]</span> |
| </p> |
| </td><td> |
| This turns on and off SQL transaction autocommits. |
| Auto-commit defaults to false, but you can change that |
| behavior by using the <code class="literal">--autoCommit</code> |
| command-line switch. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\c [true|false]</span> |
| </p> |
| </td><td> |
| <p> |
| A "true" setting tells SqlTool to Continue when errors are |
| encountered. |
| The current transaction will not be rolled back upon SQL |
| errors, so if \c is true, then run the |
| <code class="literal">ROLLCACK;</code> command yourself if that's |
| what you want to happen. |
| The default for interactive use is to continue upon error, |
| but the default for non-interactive use is to abort upon |
| error. |
| You can override this behavior by using the |
| <code class="literal">--continueOnErr</code> command-line switch. |
| </p> |
| <p> |
| With database setup scripts, I usually find it convenient |
| to set "true" before dropping tables (so that things will |
| continue if the tables aren't there), then set it back to |
| false so that real errors are caught. |
| <code class="literal">DROP TABLE tablename IF EXISTS;</code> |
| is a more elegant, but less portable, way to accomplish |
| the same thing. |
| </p> |
| <div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Tip"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Tip]" src="../images/db/tip.png"></td><th align="left">Tip</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| It depends on what you want your SQL files to do, of |
| course, but I usually want my SQL files to abort when |
| an error is encountered, without necessarily killing |
| the SqlTool session. |
| If this is the behavior that you want, then |
| put an explicit <code class="literal">\c false</code> |
| at the top of your SQL file and turn on |
| continue-upon-error only for sections where you really |
| want to permit errors, or where you are using PL |
| commands to handle errors manually. |
| This will give the desired behavior whether your |
| script is called by |
| somebody interactively, from the SqlTool command-line, |
| or included in another SQL file (i.e. nested). |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Important"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Important]" src="../images/db/important.png"></td><th align="left">Important</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| The default settings are usually best for people who |
| don't want to put in any explicit \c or error handling |
| code at all. |
| If you run SQL files from the SqlTool command line, |
| then any errors will cause SqlTool to roll back and |
| abort immediately. |
| If you run SqlTool interactively and invoke SQL files |
| with \i commands, the scripts will continue to run |
| upon errors (and will not roll back). |
| This behavior was chosen because there are lots of |
| SQL files out there that produce errors which can be |
| ignored; but we don't want to ignore errors that a |
| user won't see. |
| I reiterate that any and all of this behavior can (and |
| often should) be changed by Special Commands run in |
| your interactive shell or in the SQL files. |
| Only you know whether errors in your SQL files can |
| safely be ignored. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10965"></a>Automation</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| SqlTool is ideal for mission-critical automation because, |
| unlike other SQL tools, SqlTool returns a dependable exit |
| status and gives you control over error handling and SQL |
| transactions. |
| Autocommit is off by default, so you can build a completely |
| dependable solution by intelligently using \c commands |
| (Continue upon Errors) and commit statements, and by |
| verifying exit statuses. |
| </p> |
| <p> |
| Using the SqlTool Procedural Language, you have ultimate |
| control over program flow, and you can use variables for |
| database input and output as well as for many other purposes. |
| See the <a class="link" href="#sqltool_pl-sect" title="SqlTool Procedural Language">SqlTool Procedural Language</a> |
| section. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10970"></a>Getting Interactive Functionality with SQL Files</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Some script developers may run into cases where they want to |
| run with sql files but they alwo want SqlTool's interactive |
| behavior. |
| For example, they may want to do command recall in the sql file, |
| or they may want to log SqlTool's command-line prompts (which |
| are not printed in non-interactive mode). |
| In this case, do not give the sql file(s) as an argument to |
| SqlTool, but pipe them in instead, like |
| <div class="informalexample"> |
| <pre class="screen">java -jar $HSQLDB_HOME/lib/sqltool.jar urlid < filepath1.sql > /tmp/log.html 2</pre> |
| </div> |
| or |
| <div class="informalexample"> |
| <pre class="screen">cat filepath1.sql... | |
| java -jar $HSQLDB_HOME/lib/sqltool.jar urlid > /tmp/log.html 2>&1</pre> |
| </div> |
| For a shell not as graceful as the Bourne-compatible shells, you |
| would need to type this all on the same line (or use a |
| line-continuation trick). |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="sqltool_charencoding-sect"></a> |
| Character Encoding</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| There are several levels of encoding settings. |
| First there are your platform defaults. |
| These can be changed, temporarily or permanently, with system |
| settings or environmental variables. |
| Java system properties may be used to change the encodings for |
| the JVM run. |
| Finally, can specify a different encoding in your RC file, as |
| documented in the |
| <a class="link" href="#sqltool_auth-sect" title="RC File Authentication Setup">RC File Authentication Setup</a> |
| section, though these will not effect stdin or stdout (as |
| explained there). |
| Programmatic users of <code class="classname">SqlFile</code> have |
| complete control over encoding by setting up |
| <code class="classname">Reader</code>s and |
| <code class="classname">PrintWriter</code>s, |
| or by using constructors with an <code class="literal">encoding</code> |
| parameter. |
| Developers should understand that where a |
| <code class="filename">SqlFile</code> constructor takes a |
| <code class="classname">Reader</code> or a |
| <code class="classname">PrintWriter</code> parameter, we will not apply |
| encoding settings to them, leaving that up to you. |
| </p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_report-sect"></a>Generating Text or HTML Reports</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| This section is about making a file containing the output of |
| database queries. |
| You can generate reports by using operating system facilities |
| such as redirection, tee, and cutting and pasting. |
| But it is much easier to use the "\o" and "\H" special commands. |
| </p> |
| <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Note"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Note]" src="../images/db/note.png"></td><th align="left">Note</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| I've neglected the \H feature, because I don't know of anybody |
| using it. |
| Be aware that it writes very old-fashioned HTML, with no attention |
| to encoding, metadata, style sheets, etc. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="procedure"> |
| <a name="N109A7"></a> |
| <p class="title"> |
| <b>Procedure 1.4. Writing query output to an external file</b> |
| </p> |
| <ol type="1"> |
| <li> |
| <p> |
| By default, everthing will be done in plain text. |
| If you want your report to be in HTML format, then give the |
| special command <code class="literal">\H</code>. |
| If you do so, you will probably want to use filenames with an |
| suffix of ".html" or ".htm" instead of ".txt" in the next step. |
| </p> |
| </li> |
| <li> |
| <p> |
| Run the command <code class="literal">\o path/to/reportfile.txt</code>. |
| From this point on, output from your queries will be appended |
| to the specified file. |
| (I.e. another <span class="emphasis"><em>copy</em></span> of the output is |
| generated.) |
| This way you can continue to monitor or use output as usual as |
| the report is generated. |
| </p> |
| </li> |
| <li> |
| <p> |
| When you want SqlTool to stop writing to the file, run |
| <code class="literal">\o</code> (or just quit SqlTool if you have no |
| other work to do). |
| </p> |
| </li> |
| <li> |
| <p> |
| If you turned on HTML mode with <code class="literal">\H</code> before, |
| you can run <code class="literal">\H</code> again to turn it back off, |
| if you wish. |
| </p> |
| </li> |
| </ol> |
| </div> |
| <p> |
| It is not just the output of "SELECT" statements that will make |
| it into the report file, but |
| <div class="itemizedlist"> |
| <p class="title"> |
| <b>Kinds of output that get teed to \o files</b> |
| </p> |
| <ul type="disc"> |
| <li> |
| Output of SELECT statements. |
| </li> |
| <li> |
| Output of all "\d" Special Commands. |
| (I.e., "\dt", "\dv", etc., and "\d OBJECTNAME"). |
| </li> |
| <li> |
| Output of "\p" Special Commands. |
| You will want to use this to add titles, and perhaps |
| spacing, for the output of individual queries. |
| </li> |
| </ul> |
| </div> |
| Other output will go to your screen or stdout, but will not make |
| it into the report file. |
| Be aware that no error messages will go into the report file. |
| If SqlTool is run non-interactively (including if you give any |
| SQL file(s) on the command line), SqlTool will abort with an error |
| status if errors are encountered. |
| The right way to handle errors is to check the SqlTool exit status. |
| (The described error-handling behavior can be modified with |
| SqlTool command-line switches and Special Commands). |
| </p> |
| <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Warning"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Warning]" src="../images/db/warning.png"></td><th align="left">Warning</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| Remember that \o <span class="emphasis"><em>appends</em></span> to the named file. |
| If you want a new file, then use a new file name or remove the |
| pre-existing target file ahead of time. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Tip"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Tip]" src="../images/db/tip.png"></td><th align="left">Tip</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| So that I don't end up with a bunch of junk in my report file, I |
| usually leave \o off while I perfect my SQL. With \o off, |
| I perfect the SQL query until it produces on my screen exactly |
| what I want saved to file. |
| At this point I turn on \o and run ":/select/;" to repeat the |
| last SQL command containing the given string ("select" in this |
| example). |
| If I have several complex queries to run, I turn \o off and |
| repeat until I'm finished. |
| (Every time you turn \o on, it will append to the file, just |
| like we need). |
| </p> |
| <p> |
| Usually it doesn't come to mind that I need a wider screen until |
| a query produces lines that are too long. |
| In this case, stretch your window and repeat the last command with |
| the ":;" Edit Buffer Command. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_pl-sect"></a>SqlTool Procedural Language</h2> |
| </div> |
| <div> |
| <h3 class="subtitle">Aka PL</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Most importantly, run <code class="filename">SqlTool</code> interactively |
| and give the "*?" command to see what PL commands are available to |
| you. |
| I've tried to design the language features to be intuitive. |
| Readers experience with significant shell scripting in any |
| language can probably learn everything they need to know by |
| looking at (and running!) the sample script |
| <code class="filename"><a class="filename" href="#pl.sql-link"> |
| sample/pl.sql</a></code> in your HyperSQL distribution |
| <sup>[<a href="#ftn.samplelocFn" class="footnoteref">1</a>]</sup> and |
| using the <code class="varname">*?</code> command from within an interactive |
| SqlTool session as a reference. (By |
| <span class="emphasis"><em>significant</em></span> shell scripting, I mean to the |
| extent of using variables, for loops, etc.). |
| </p> |
| <p> |
| PL variables will only be expanded after you run a PL command (or |
| set variable(s) from the command-line). |
| We only want to turn on variable expansion if the user wants |
| variable expansion. |
| People who don't use PL don't have to worry about strings getting |
| accidentally expanded. |
| </p> |
| <p> |
| All other PL commands imply the "*" command, so you only need to |
| use the "*" statement if your script uses PL variables |
| and it is possible that no variables may be set before-hand (and |
| no PL commands have been run previously). |
| In this case, without "*", your script would silently use a |
| literal value like "*{x}" instead of trying to expand it. |
| With a preceding "*" command, PL will notice that the variable |
| <code class="literal">x</code> has not been set and will generate an error. |
| (If x had been set here will be no issue because setting a |
| variable automatically turns on PL variable expansion). |
| </p> |
| <p> |
| PL is also used to upload and download column values to/from |
| local ASCII files, analogously to the special \b commands |
| for binary files. |
| This is explained above in the Interactive |
| <a class="link" href="#sqltool_interactive_pl_commands-sect" title="PL Commands">Essential PL Command</a> |
| section above. |
| </p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10A08"></a>Variables</h3> |
| </div> |
| </div> |
| </div> |
| <div class="itemizedlist"> |
| <ul type="disc"> |
| <li> |
| Use the <code class="literal">* list</code> command to list some or |
| all variables; or <code class="literal">* listvalues</code> to also |
| see the values. |
| </li> |
| <li> |
| You can set variables using the |
| <code class="literal">* VARNAME = value</code> command. |
| This document explains elsewhere how you can set a values |
| to the contents of files, and to the return value of SQL |
| statements and fetches. |
| </li> |
| <li> |
| You can also set variables using the |
| <code class="literal">--setvar</code> command-line switch. |
| I give a very brief but useful example of this below. |
| </li> |
| <li> |
| SqlTool does not allow for setting system variables. |
| As explained below, they are expanded in the same way as |
| PL variables. |
| </li> |
| <li> |
| Variables are always expanded in SQL, Special, and PL |
| commands if they are written like |
| <code class="literal">*{VARNAME}</code> |
| (assuming that a PL command has been run previously). |
| Your SQL scripts can give good feedback by echoing the |
| value of variables with the "\p" special command. |
| Use the construct <code class="literal">*{:VARNAME}</code> to |
| expand the variable, but to expand to a zero-length |
| string instead of fail if VARNAME is not set. |
| </li> |
| <li> |
| Java system variables work the same exact way, except you |
| use <code class="literal">$</code> instead of <code class="varname">*</code> |
| to dereference, like so: <code class="literal">${user.name}</code>. |
| </li> |
| <li> |
| Variables are normally written like |
| <code class="varname">*VARNAME</code> in logical expressions to |
| prevent them from being evaluated too early. |
| See below about logical expressions. |
| </li> |
| <li> |
| <p> |
| You can't do math with expression variables, but you |
| can get functionality like the traditional |
| <code class="literal">for (i = 0; i < x; i++)</code> by appending |
| to a variable and testing the string length, like |
| <pre class="programlisting"> * while (*i < ${x}) |
| * i = *{i}.</pre> |
| |
| <code class="literal">i</code> will be a growing line of dots. |
| </p> |
| </li> |
| <li> |
| <p> |
| Variable names must not contain white space, or |
| the characters "}" or "=". |
| </p> |
| </li> |
| </ul> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="sqltool_macro-sect"></a>Macros</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Macros are just shortcut commands that you can run in place of |
| the full commands which they stand for. |
| Macros stand for SQL, Special or PL commands, whereas PL |
| variables can only be used for elements within a command. |
| It is very easy to define, list, and use macros. |
| Run the command "/?" to see how. |
| If you often run a particular query, then for the effort of |
| about 5 extra keystrokes, you can define a macro for it so |
| that you can enter just "/q;" to run it, whether the original |
| query is 1 line or 40 lines. (You can use any name in place |
| of "q", and the target command can be any kind of SQL, |
| special, or PL command). |
| </p> |
| <p> |
| When you run/use a macro, you can append to the macro value. |
| <span class="emphasis"><em>appendage</em></span> in the "/?" listing shows |
| where you can append additional text to the original command. |
| So, if you define |
| <pre class="programlisting"> sql> /= myworkers SELECT name FROM employees</pre> |
| , you could narrow the query variously during different macro |
| invocations, like |
| <pre class="programlisting"> sql> /myworkers WHERE dept = 20; |
| sql> /myworkers WHERE name like 'Karen%';</pre> |
| |
| </p> |
| <p> |
| Just like when recalling a command from history, you use ";" |
| to execute even Special and PL macro commands. |
| <pre class="programlisting"> sql> /= notate Work completed by |
| sql> /notate Blaine;</pre> |
| If you don't type the ;, you will just recall the command |
| to the buffer (from which you can execute or edit it, if |
| you wish to). |
| </p> |
| <p> |
| To make a macro for a mult-line SQL statement, you use the |
| "/= name :" construct. |
| First, get the target command into the command buffer. |
| If you have already run the command, then run ":h" to see the |
| command number and load it to the buffer like ":13". |
| If you haven't run the command yet, then just enter the |
| command, but end it with a blank line (and no semi-colon). |
| You can check the buffer with ":b" to make sure it is what |
| you want. |
| Then just run "/= name :" to define a macro with name "name". |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10A67"></a>PL Sample</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Here is a short SQL file that gives the specified user write |
| permissions on some application tables. |
| </p> |
| <div class="example"> |
| <a name="N10A6C"></a> |
| <p class="title"> |
| <b>Example 1.5. Simple SQL file using PL</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="programlisting"> /* |
| grantwrite.sql |
| |
| Run SqlTool like this: |
| java -jar path/to/sqltool.jar -setvar=USER=debbie grantwrite.sql |
| */ |
| |
| /* Explicitly turn on PL variable expansion, in case no variables have |
| been set yet. (Only the case if user did not set USER). |
| */ |
| * |
| |
| GRANT all ON book TO *{USER}; |
| GRANT all ON category TO *{USER};</pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| <p> |
| Note that this script will work for any (existing) user just |
| by supplying a different user name on the command-line. |
| I.e., no need to modify the tested and proven script. |
| There is no need for a <code class="literal">commit</code> statement |
| in this SQL file since no DML is done. |
| If the script is accidentally run without setting the |
| USER variable, SqlTool will give a very clear notificaton of |
| that. |
| </p> |
| <p> |
| The purpose of the plain "*" command is just |
| so that the *{USER} variables will be expanded. |
| (This would not be necessary if the USER variable, or any |
| other variable, were set, but we don't want to depend upon |
| that). |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10A78"></a>Logical Expressions</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Logical expressions occur only inside of logical expression |
| parentheses in PL statements. |
| For example, <code class="literal">if (*var1 > astring)</code> and |
| <code class="literal">while (*checkvar)</code>. |
| (The parentheses after "foreach" do not enclose a logical |
| expression, they just enclose a list). |
| </p> |
| <p> |
| There is a critical difference between |
| <code class="literal">*{VARNAME}</code> and <code class="varname">*VARNAME</code> |
| inside logical expressions. |
| <code class="literal">*{VARNAME}</code> is expanded one time when the |
| parser first encounters the logical expression. |
| <code class="varname">*VARNAME</code> is re-expanded every time that the |
| expression is evaluated. |
| So, you would never want to code |
| <code class="literal">* while (*{X} < 5)</code> because the statement |
| will always be true or always be false. |
| (I.e. the following block will loop infinitely or will never |
| run). |
| </p> |
| <p> |
| Don't use quotes or whitespace of any kind in |
| <code class="literal">*{VARNAME}</code> variables in expressions. |
| (They would expand and then the expression would most likely |
| no longer be a valid expression as listed in the table below). |
| Quotes and whitespace are fine in <code class="varname">*VARNAME</code> |
| variables, but it is the entire value that will be used in |
| evaluations, regardless of whether quotes match up, etc. |
| I.e. quotes and whitespace are not <span class="emphasis"><em>special</em></span> |
| to the token evaluator. |
| </p> |
| <div class="variablelist"> |
| <p class="title"> |
| <b>Logical Operators</b> |
| </p> |
| <table border="0"> |
| <col valign="top" align="left"> |
| <tbody> |
| <tr> |
| <td> |
| <p> |
| <span class="term">TOKEN</span> |
| </p> |
| </td><td> |
| The token may be a literal, a <code class="literal">*{VARNAME}</code> |
| which is expanded early, or a <code class="varname">*VARNAME</code> |
| which is expanded late. |
| (You usually do not want to use |
| <code class="literal">*{VARNAME}</code> in logical expressions). |
| False if the token is not set, empty, or "0". |
| True otherwise. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">TOKEN1 == TOKEN2</span> |
| </p> |
| </td><td> |
| True if the two tokens are equivalent "strings". |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">TOKEN1 <> TOKEN2</span> |
| </p> |
| </td><td> |
| Ditto. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">TOKEN1 >< TOKEN2</span> |
| </p> |
| </td><td> |
| Ditto. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">TOKEN1 > TOKEN2</span> |
| </p> |
| </td><td> |
| True if the TOKEN1 string is longer than TOKEN2 or is |
| the same length but is greater according to a string sort. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">TOKEN1 < TOKEN2</span> |
| </p> |
| </td><td> |
| Similarly to TOKEN1 > TOKEN2. |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">! LOGICAL_EXPRESSION</span> |
| </p> |
| </td><td> |
| Logical negation of any of the expressions listed above. |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| <p> |
| |
| <code class="varname">*VARNAME</code>s in logical expressions, where the |
| VARNAME variable is not set, evaluate to an empty string. |
| Therefore <code class="literal">(*UNSETVAR = 0)</code> would be false, |
| even though <code class="literal">(*UNSETVAR)</code> by itself is false |
| and <code class="literal">(0)</code> by itself is false. |
| Another way of saying this is that <code class="varname">*VARNAME</code> |
| in a logical |
| expression is equivalent to *{:VARNAME} out of a logical |
| expression. |
| </p> |
| <p> |
| When developing scripts, you definitely use SqlTool |
| interactively to verify that SqlTool evaluates logical |
| expressions as you expect. |
| Just run <code class="literal">* if</code> commands that print something |
| (i.e. \p) if the test expression is true. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10AEB"></a>Flow Control</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Flow control works by conditionally executing blocks of |
| Commands according to conditions specified by logical |
| expressions. |
| </p> |
| <p> |
| The conditionally executed blocks are called |
| <span class="emphasis"><em>PL Blocks</em></span>. |
| These PL Blocks always occur between a PL flow control |
| statement (like <code class="literal">* foreach, *while, * if</code>) |
| and a corresponding <code class="literal">* end</code> PL Command |
| (like <code class="literal">* end foreach</code>). |
| </p> |
| <p> |
| The values of control variables for foreach and while PL |
| blocks will change as expected. |
| </p> |
| <p> |
| There are <code class="literal">* break</code> and |
| <code class="literal">* continue</code>, which work as any shell |
| scripter would expect them to. |
| The <code class="literal">* break</code> command can also be used to |
| quit the current SQL file without triggering any error |
| processing. |
| (I.e. processing will continue with the next line in the |
| <span class="emphasis"><em>including</em></span> SQL file or interactive |
| session, or with the next SQL file if you supplied multiple on |
| the command-line). |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10B0E"></a>Example</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Below is the example SQL file |
| <code class="filename"><a class="filename" href="#pl.sql-link">sample/pl.sql</a></code>, |
| which shows how to use most PL |
| features <sup>[<a href="#ftn.samplelocFn" class="footnoteref">1</a>]</sup>. |
| If you have a question about how to use a particular |
| PL feature, check this file in your distrubition before asking |
| for help. |
| Definitely give it a run, like |
| <pre class="screen">java -jar $HSQLDB_HOME/lib/sqltool.jar mem $HSQLDB_HOME/pl.jar</pre> |
| |
| </p> |
| <div class="example"> |
| <a name="N10B1D"></a> |
| <p class="title"> |
| <b>Example 1.6. SQL File showing use of most PL features</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="programlisting">/* |
| $Id: pl.sql 3353 2009-12-15 19:52:13Z unsaved $ |
| SQL File to illustrate the use of SqlTool PL features. |
| Invoke like |
| java -jar .../hsqldb.jar .../pl.sql mem |
| -- blaine |
| */ |
| |
| * if (! *MYTABLE) |
| \p MYTABLE variable not set! |
| /* You could use \q to Quit SqlTool, but it's often better to just |
| break out of the current SQL file. |
| If people invoke your script from SqlTool interactively (with |
| \i yourscriptname.sql) any \q will kill their SqlTool session. */ |
| \p Use arguments "--setvar=MYTABLE=mytablename" for SqlTool |
| * break |
| * end if |
| |
| /* Turning on Continue-upon-errors so that we can check for errors ourselves.*/ |
| \c true |
| |
| \p |
| \p Loading up a table named '*{MYTABLE}'... |
| |
| /* This sets the PL variable 'retval' to the return status of the following |
| SQL command */ |
| * retval ~ |
| CREATE TABLE *{MYTABLE} ( |
| i int, |
| s varchar(20); |
| ); |
| \p CREATE status is *{retval} |
| \p |
| |
| /* Validate our return status. In logical expressions, unset variables like |
| *unsetvar are equivalent to empty string, which is not equal to 0 |
| (though both do evaluate to false on their own, i.e. (*retval) is false |
| and (0) is false */ |
| * if (*retval != 0) |
| \p Our CREATE TABLE command failed. |
| * break |
| * end if |
| |
| /* Default Continue-on-error behavior is what you usually want */ |
| \c false |
| \p |
| |
| /* Insert data with a foreach loop. |
| These values could be from a read of another table or from variables |
| set on the command line like |
| */ |
| \p Inserting some data int our new table (you should see 3 row update messages) |
| * foreach VALUE (12 22 24 15) |
| * if (*VALUE > 23) |
| \p Skipping *{VALUE} because it is greater than 23 |
| * continue |
| \p YOU WILL NEVER SEE THIS LINE, because we just 'continued'. |
| * end if |
| INSERT INTO *{MYTABLE} VALUES (*{VALUE}, 'String of *{VALUE}'); |
| * end foreach |
| \p |
| |
| * themax ~ |
| /* Can put Special Commands and comments between "* VARNAME ~" and the target |
| SQL statement. */ |
| \p We're saving the max value for later. You'll still see query output here: |
| SELECT MAX(i) FROM *{MYTABLE}; |
| |
| /* This is usually unnecessary because if the SELECT failed, retval would |
| be undefined and the following print statement would make SqlTool exit with |
| a failure status */ |
| * if (! *themax) |
| \p Failed to get the max value. |
| /* It's possible that the query succeeded but themax is "0". |
| You can check for that if you need to. */ |
| * break |
| \p YOU WILL NEVER SEE THIS LINE, because we just 'broke'. |
| * end if |
| |
| \p |
| \p ############################################################## |
| \p The results of our work: |
| SELECT * FROM *{MYTABLE}; |
| \p MAX value is *{themax} |
| |
| \p |
| \p Everything worked. |
| </pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_chunk-sect"></a>Chunking</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| We hereby call the ability to transmit multiple SQL commands to |
| the database in one transmission <span class="emphasis"><em>chunking</em></span>. |
| Normally it's best to send SQL statements to the database |
| one-at-a-time. |
| That way, the database can give you or your program feedback about |
| each statement. |
| But there are situations where it is more important to transmit |
| multiple-statements-at-a-time than to get feedback for each |
| statement individually. |
| </p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10B2C"></a>Why?</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| The first general reason to chunk SQL commands is performance. |
| For standalone databases, the most common performance |
| bottleneck is network latency. |
| Chunking SQL commands can dramatically reduce network traffic. |
| </p> |
| <p> |
| The second reason is that there are a couple SQL commands which |
| require the terminating ";" to be sent to the database engine. |
| For simplicity and efficiency, it's usually better for general |
| JDBC clients like SqlTool to strip off the final delimiter. |
| Raw commands retains everything that the user types. |
| </p> |
| <p> |
| The third general reason to chunk SQL commands is if your |
| database requires you to send multiple commands in one |
| transmission. |
| This is usually the case with the following types of commands: |
| <div class="itemizedlist"> |
| <ul type="disc"> |
| <li> |
| Nested SQL commands, like the nested CREATE SCHEMA |
| variant, and most stored procedure, function, and |
| trigger definitions. |
| </li> |
| <li> |
| Commands containing non-quoted programming language to |
| be interpreted by the database engine. |
| Definitions of stored procedures, function, and triggers |
| often contain code like this. |
| </li> |
| </ul> |
| </div> |
| |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10B3D"></a>How?</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Use raw mode. |
| Go to the |
| <a class="link" href="#sqltool_raw-sect" title="Raw Mode">Raw Mode</a> section |
| to see how. |
| You can enter any text at all, exactly how you want it to |
| be sent to the database engine. |
| Therefore, in addition to chunking SQL commands, you can |
| give commands for non-SQL extensions to the database. |
| For example, you could enter JavaScript code to be used |
| in a stored procedure. |
| </p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_raw-sect"></a>Raw Mode</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| You begin raw mode by issuing the Special Command "\.". |
| You can then enter as much text in any format you want. |
| When you are finished, enter a line consisting of only ".;" |
| to store the input to the edit buffer and send it to the |
| database server for execution. |
| </p> |
| <p> |
| This paragraph applies only to interactive usage. |
| Interactive users may may end the raw input with ":." |
| instead of ".;". |
| This will just save the input to the edit buffer so that you can |
| edit it and send it to the database manually. |
| You can look at the edit buffer with the ":b" Buffer Command. |
| You would normally use the command ":;" to |
| send the buffer to the database after you are satisfied with it. |
| You'll notice that your prompt will be the "raw" prompt |
| between entering "\." and terminating the raw input with ".;" |
| or ":.". |
| </p> |
| <p> |
| Just by running commands beginning with |
| <code class="literal">BEGIN</code>, <code class="literal">DECLARE</code>, |
| <code class="literal">CREATE function</code>, |
| or <code class="literal">CREATE procedure</code>, your SqlTool session will |
| automatically be changed to Raw mode, exactly as if you had entered |
| "\.". |
| That's because these commands are universally used to define |
| stored procedures or functions, and these commands require raw mode |
| (as explained in the previous section). |
| </p> |
| <p> |
| |
| <div class="example"> |
| <a name="N10B5F"></a> |
| <p class="title"> |
| <b>Example 1.7. Interactive Raw Mode example</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="screen"> sql> \. |
| Enter RAW SQL. No \, :, * commands. |
| End with a line containing only ".;" to send to database, |
| or ":." to store to edit buffer for editing or saving. |
| ----------------------------------------------------------- |
| raw> line one; |
| raw> line two; |
| raw> line three; |
| raw> :. |
| Raw SQL chunk moved into buffer. Run ":;" to execute the chunk. |
| sql> :; |
| Executing command from buffer: |
| line one; |
| line two; |
| line three; |
| |
| SQL Error at 'stdin' line 13: |
| "line one; |
| line two; |
| line three;" |
| Unexpected token: LINE in statement [line] |
| sql></pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| The error message "Unexpected token: LINE in statement [line]" |
| comes from the database engine, not SqlTool. |
| All three lines were transmitted to the database engine. |
| </p> |
| <p> |
| Edit Buffer Commands are not available when running SqlTool |
| non-interactively. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_embedded-langs-sect"></a>SQL/PSM, SQL/JRT, and PL/SQL</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| This section covers database-engine-embedded languages, which are |
| often used in the definition of stored procedures, stored functions, |
| and triggers. |
| <code class="literal">SQL/PSM</code>, <code class="literal">SQL/JRT</code>, |
| and <code class="literal">PL/SQ:</code> are well known examples. |
| We prefer <code class="literal">SQL/PSM</code> and <code class="literal">SQL/JRT</code> |
| because unlike the alternatives, they are based on open SQL |
| specifications. |
| </p> |
| <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Note"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Note]" src="../images/db/note.png"></td><th align="left">Note</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| PL/SQL is <span class="bold"><strong>not</strong></span> the same as |
| PL. PL is the procedural language of SqlFile and is |
| independent of your back-end database. |
| PL commands always begin with *. |
| PL/SQL is an Oracle-specific extension processed on the server side. |
| You can not intermix PL and any server-embedded language |
| (except for setting a PL variable to the output of execution), |
| because when you enter server language to SqlTool, that input is |
| not processed by SqlFile. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| Use <a class="link" href="#sqltool_raw-sect" title="Raw Mode">Raw Mode</a> to send |
| server-language code blocks to the database engine. |
| You do not need to enter the "\." command to enter raw mode. |
| Just begin a new SqlTool command line with "DECLARE", |
| "BEGIN", "CREATE FUNCTION", or "CREATE PROCEDURE", |
| and SqlTool will automatically put you into raw mode. |
| See the <a class="link" href="#sqltool_raw-sect" title="Raw Mode">Raw Mode</a> |
| section for details. |
| </p> |
| <p> |
| The following sample SQL file resides at |
| <code class="filename"><a class="filename" href="#plsql.sql-link">sample/plsql.sql</a></code> |
| in your HyperSQL distribution |
| <sup>[<a href="#ftn.samplelocFn" class="footnoteref">1</a>]</sup>. |
| This script will only work with Oracle, only if you have |
| permission to create the table |
| "T1" in the default schema, and if that object does not |
| already exist. |
| <div class="example"> |
| <a name="N10B96"></a> |
| <p class="title"> |
| <b>Example 1.8. PL/SQL Example</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="programlisting">/* |
| * $Id: plsql.sql 826 2009-01-17 05:04:52Z unsaved $ |
| * |
| * This example is copied from the "Simple Programs in PL/SQL" |
| * example by Yu-May Chang, Jeff Ullman, Prof. Jennifer Widom at |
| * the Standord University Database Group's page |
| * http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html . |
| * I have only removed some blank lines (in case somebody wants to |
| * copy this code interactively-- because you can't use blank |
| * lines inside of SQL commands in non-raw mode SqlTool when running |
| * it interactively); and, at the bottom I have replaced the |
| * client-specific, non-standard command "run;" with SqlTool's |
| * corresponding command ".;" and added a plain SQL SELECT command |
| * to show whether the PL/SQL code worked. - Blaine |
| */ |
| |
| CREATE TABLE T1( |
| e INTEGER, |
| f INTEGER |
| ); |
| |
| DELETE FROM T1; |
| |
| INSERT INTO T1 VALUES(1, 3); |
| |
| INSERT INTO T1 VALUES(2, 4); |
| |
| /* Above is plain SQL; below is the PL/SQL program. */ |
| DECLARE |
| |
| a NUMBER; |
| |
| b NUMBER; |
| |
| BEGIN |
| |
| SELECT e,f INTO a,b FROM T1 WHERE e>1; |
| |
| INSERT INTO T1 VALUES(b,a); |
| |
| END; |
| |
| .; |
| /** The statement on the previous line, ".;" is SqlTool specific. |
| * This command says to save the input up to this point to the |
| * edit buffer and send it to the database server for execution. |
| * I added the SELECT statement below to give imm |
| */ |
| |
| /* This should show 3 rows, one containing values 4 and 2 (in this order)...*/ |
| SELECT * FROM t1; |
| </pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| Note that, inside of raw mode, you can use any kind of formatting |
| that your database engine needs or permits: Whatever you enter-- |
| blank lines, comments, |
| everything-- will be transmitted to the database engine. |
| </p> |
| <p> |
| This file resides at |
| <code class="filename"><a class="filename" href="#sqljrt.sql-link"> |
| testrun/sqltool/sqljrt.sql</a></code> |
| |
| <div class="example"> |
| <a name="N10BA2"></a> |
| <p class="title"> |
| <b>Example 1.9. SQL/JRT Example</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="programlisting">/* |
| * $Id: sqljrt.sql 3353 2009-12-15 19:52:13Z unsaved $ |
| * |
| * Tests SQL/JRT |
| */ |
| |
| create function dehex(VARCHAR(80), INTEGER) |
| returns INTEGER |
| no sql |
| language java |
| external name 'CLASSPATH:java.lang.Integer.valueOf' |
| .; |
| |
| CALL dehex('12', 16); |
| *if (*? != 18) |
| \q SQL/JRT function failed |
| *end if |
| </pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| This file resides at |
| <code class="filename"><a class="filename" href="#sqlpsm.sql-link"> |
| testrun/sqltool/sqlpsm.sql</a></code> |
| |
| <div class="example"> |
| <a name="N10BAC"></a> |
| <p class="title"> |
| <b>Example 1.10. SQL/PSM Example</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="programlisting">/* |
| * $Id: sqlpsm.sql 826 2009-01-17 05:04:52Z unsaved $ |
| * |
| * Tests SQL/JRT |
| */ |
| |
| create table customers( |
| id INTEGER default 0, firstname VARCHAR(50), lastname VARCHAR(50), |
| entrytime TIMESTAMP); |
| |
| create procedure new_customer(firstname varchar(50), lastname varchar(50)) |
| modifies sql data |
| insert into customers values ( |
| default, firstname, lastname, current_timestamp) |
| .; |
| |
| SELECT count(*) FROM customers; |
| *if (*? != 0) |
| \q SQL/PSM preparation failed |
| *end if |
| |
| CALL new_customer('blaine', 'simpson'); |
| |
| SELECT count(*) FROM customers; |
| *if (*? != 1) |
| \q SQL/PSM procedure failed |
| *end if |
| </pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_dsv-sect"></a>Delimiter-Separated-Value Imports and Exports</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| SqlTool's DSV functionality encompasses what many users will |
| recognize as CSV export, as well as portable backup or transfer of |
| data. |
| Those familiar with Oracle's SQL*Loader will recognize the extreme |
| usefulness of the feature set. |
| Besides database- and platform-independent data backups, exports can |
| be used to deploy data sets with applications, to transfer data |
| among multiple database instances (even drastically different |
| database instances such as SQL Server and HyperSQL), and to properly |
| change control data sets with a content management system such as a |
| collaboration server or Subversion. |
| To jump way ahead for a moment to whet your appetite, here is a |
| sample <span class="emphasis"><em>import reject report</em></span> which will can be |
| generated automatically for you upon import just by setting the PL |
| variable <code class="varname">*DSV_REJECT_REPORT</code> (to the desired |
| destination HTML file name). |
| <div class="mediaobject" align="center"> |
| <img src="rejreport-sample.png" align="middle"><div class="caption"> |
| <p>A DSV Import reject report</p> |
| </div> |
| </div> |
| If you wish to, you can review the reject report before deciding |
| whether to commit or roll back the inserts. |
| </p> |
| <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Note"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Note]" src="../images/db/note.png"></td><th align="left">Note</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| This feature is independent of HyperSQL Text Tables. |
| (See the Text Tables chapter of the |
| <a class="link" href="http://hsqldb.org/doc/2.0/guide/index.html" target="_top"> |
| HyperSQL User Guide</a> for details about them). |
| a server-side feature of HyperSQL. |
| It makes no difference to SqlTool whether the source or target |
| table of your export/import is a memory, cache, or text table. |
| Indeed, like all features of SqlTool, it works fine with other |
| JDBC databases. |
| It works great, for example to migrate data from a table |
| of one type to a table of another type, or to another schema, |
| or to another database instance, or to another database system. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| This feature is what most business people call "CSV", but |
| these files are more accurately called <span class="emphasis"><em>Delimiter |
| Separated Value files</em></span> because the delimiter is |
| usually not a comma, and, more importantly, we purposefully |
| choose an effective delimiter instead of the CSV method of |
| using a delimiter which works in some cases and then use |
| quotes and back-slashes to escape occurrence of the delimiter |
| in the actual data. |
| Just by choosing a delimiter which never needs escaping, we |
| eliminate the whole mess, and the data in our files always |
| looks just like the corresponding data in the database. |
| To make this CSV / Delimiter-separated-value dintinction clear, |
| I use the suffix ".dsv" for my data files. |
| This leads me to stipulate the abbreviation DSV for the |
| <span class="emphasis"><em>Delimiter Separated Value</em></span> feature of HyperSQL. |
| </p> |
| <p> |
| Use the <code class="literal">\x</code> command to eXport a table to a |
| DSV file, and the <code class="literal">\m</code> command to iMport a |
| DSV file into a pre-existing table. |
| </p> |
| <p> |
| The row and column delimiters may be any String (or even a |
| regular expression for import), not just a single character. |
| And just as the delimiter capability is more general than |
| traditional CSV delimiters, the export function is also more |
| general than just a table data exporter. |
| Besides the trivial generalization that you may specify a |
| view or other virtual table name in place of a table name, |
| you can alternatively export the output of any query which |
| produces normal text output. |
| A benefit to this approach is that it allows you to export only |
| some columns of a table, and to specify a WHERE clause to narrow |
| down the rows to be exported (or perform any other SQL |
| transformation, mapping, join, etc.). |
| One specific use for this would be to exclude columns of |
| binary data (which can be exported by other means, such as |
| a PL loop to store binary values to files with the \bd command). |
| </p> |
| <p> |
| Note that the import command will not create a new table. |
| This is because of the impossibility of guessing appropriate |
| types and constraints based only on column names and a data |
| sampling (which is all that a DSV-importer has access to). |
| Therefore, if you wish to populate a new table, create the |
| table before running the import. |
| The import file does not need to have data for all columns of a |
| table. |
| The only required columns are those required by database |
| constraints (non-null, indexes, keys, etc.) |
| One specific reason to omit columns is if you want values of |
| some columns to be created automatically by column DEFAULT |
| settings, triggers, HyperSQL identity sequences, etc. |
| Another reason would be to skip binary columns. |
| </p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10BE4"></a>Simple DSV exports and imports using default settings</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| Even if you need to change delimiters, table names, or file |
| names from the defaults, I suggest that you run one export |
| and import with default settings as a practice run. |
| A memory-only HyperSQL instance is ideal for test runs like this. |
| </p> |
| <p> |
| This command exports the table <code class="literal">icf.projects</code> |
| to the file <code class="filename">projects.dsv</code> in the current |
| directory (where you invoked SqlTool from). |
| By default, the output file name will be the specified source |
| table name plus the extension <code class="literal">.dsv</code>. |
| <div class="example"> |
| <a name="N10BF4"></a> |
| <p class="title"> |
| <b>Example 1.11. DSV Export Example</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="screen"> SET SCHEMA icf; |
| \x projects</pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| We could also have run <code class="literal">\x icf.projects</code> |
| (which would have created a file named |
| <code class="filename">icf.projects.dsv</code>) |
| instead of changing the session schema. |
| In this example we have chosen to make the export file name |
| independent of the schema to facilitate importing it into |
| a different schema. |
| </p> |
| <p> |
| Take a look at the output file. |
| Notice that the first line consists of column names, not |
| data. |
| This line is present because it will be needed if the file is |
| to used for a DSV import. |
| Notice the following characterstics about the export data. |
| The column delimiter is the pipe character "|". |
| The record delimiter is the default line delimiter character(s) |
| for your operating system. |
| The string used to represent database <code class="literal">NULL</code>s |
| is <code class="literal">[null]</code>. |
| See the next section for how to change these from their default |
| values. |
| </p> |
| <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Warning"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Warning]" src="../images/db/warning.png"></td><th align="left">Warning</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| You can not DSV import Array values where any Array elements |
| contain commas, for example an Array of VARCHARs which contain |
| one or more commas. |
| There is no such limitation on DSV exports, which you can use |
| for purposes other than SqlTool importing, or you could use |
| a script to change the commas to some other character. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p> |
| This command imports the data from the file |
| <code class="filename">projects.dsv</code> in the current |
| directory (where you invoked SqlTool from) into the table |
| <code class="literal">newschema.projects</code>. |
| By default, the output table name will be the input filename |
| after removing optional leading directory and trailing final |
| extension. |
| <div class="example"> |
| <a name="N10C13"></a> |
| <p class="title"> |
| <b>Example 1.12. DSV Import Example</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="screen"> SET SCHEMA newschema; |
| \m projects.dsv</pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| If the DSV file was named with the target schema, you would |
| have skipped the <code class="literal">SET SCHEMA</code> command, like |
| <code class="literal">\m newschema.projects.dsv</code>. |
| In order to allow for more flexibility, the default input |
| input delimiters are not exactly the same as the output |
| delimiters. |
| The input delimiters are regular expressions. |
| The input column delimiter happens to be the regular expression |
| corresponding exatly to "|"; but the input record delimiter |
| matches UNIX, Windows, Mac, and HTTP line breaks. |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10C1F"></a>Specifying queries and options</h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| For a hands on example of a DSM import which generates |
| an import report and uses some other options, change to |
| directory <code class="filename">HSQLDB/sample</code> and play |
| with the working script |
| <code class="filename"><a class="filename" href="#dsv-sample.sql-link"> |
| dsv-sample.sql</a></code> |
| <sup>[<a href="#ftn.samplelocFn" class="footnoteref">1</a>]</sup>. |
| You can execute it like |
| <div class="informalexample"> |
| <pre class="screen"> java -jar ../lib/sqltool.jar mem dsv-sample.sql</pre> |
| </div> |
| (assuming that you are using the supplied |
| <code class="filename">sqltool.rc</code> file or have have urlid |
| <code class="literal">mem</code> set up). |
| </p> |
| <p> |
| The header line in the DSV file is required at this time. |
| (If there is user demand, it can be made optional for |
| exporting, but it will remain required for importing). |
| </p> |
| <p> |
| Your export will fail if the output column or record delimiter, |
| or the null representation value occurs in the data being |
| exported. |
| You change these values by setting the PL variables |
| <code class="varname">*DSV_COL_DELIM</code>, |
| <code class="varname">*DSV_ROW_DELIM</code>, |
| <code class="varname">*DSV_NULL_REP</code>. |
| Notice that the asterisk is part of the variable names, to |
| indicate that these variables are used by SqlTool internally. |
| When specifying delimiters, you can use the escape seqpences |
| \n, \r, \f, \t, \\, and decimal, octal or hex specifications |
| like \20, \020, \0x20. |
| For example, to change the column delimiter to the tab character, |
| you would give the command |
| <div class="informalexample"> |
| <pre class="screen"> * *DSV_COL_DELIM = \t</pre> |
| </div> |
| |
| </p> |
| <p> |
| The input (\m) delimiter values, |
| <code class="varname">*DSV_COL_SPLITTER</code> and |
| <code class="varname">*DSV_ROW_SPLITTER</code>, are set using normal |
| Perl/Java regexp syntax. |
| There are escapes for specifying special characters, and |
| anything else you would need. |
| Input vs. output row and column delimiters are easily |
| distinguished by containing "SPLITTER" for splitting input |
| (\m) files; or "DELIM" for the delimiters that we will write |
| (\x) among the data. |
| </p> |
| <p> |
| For imports, you must always specify the source DSV file path. |
| If you want to <span class="emphasis"><em>export</em></span> to a different file |
| than one in the current directory named according to the source |
| table, set the PL variable <code class="varname">*DSV_TARGET_FILE</code>, |
| like |
| <div class="informalexample"> |
| <pre class="screen"> * *DSV_TARGET_FILE = /tmp/dtbl.dsv</pre> |
| </div> |
| |
| </p> |
| <p> |
| For exports, you must always specify the source table name |
| or query. |
| If you want to <span class="emphasis"><em>import</em></span> to a table other |
| than that derived from |
| the input DSV file name, set the PL variable |
| <code class="varname">*DSV_TARGET_TABLE</code>. |
| The table name may contain a schema name prefix. |
| </p> |
| <p> |
| You don't need to import all of the columns in a data file. |
| To designate the fields to be skipped, iether set the PL |
| PL variable <code class="varname">*DSV_SKIP_COLUMNS</code>, or replace |
| the column names in the header line to "-" (hyphen). |
| The value of <code class="varname">*DSV_SKIP_COLUMNS</code> is |
| case-insensitive, and multiple column names are separated with |
| white space and/or commas. |
| </p> |
| <p> |
| You can specify a query instead of a tablename with the |
| \x command in order to filter or transform data from a table |
| or view, or to export the output of a join, etc. |
| You must set the PL variable <code class="varname">*DSV_TARGET_FILE</code>, |
| as explained above (since there is no table name from which to |
| automatically map a file name). |
| <div class="example"> |
| <a name="N10C72"></a> |
| <p class="title"> |
| <b>Example 1.13. DSV Export of an Arbitrary SELECT Statement</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="screen"> * *DSV_TARGET_FILE = outfile.txt |
| \x SELECT entrydate, 2 * aval "Double aval", modtime FROM bs.dtbl</pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| Note that I specified the column label alias "Double aval" |
| so that the label for that column in the DSV file header will |
| not be blank. |
| </p> |
| <p> |
| By default, imports will abort as soon as a error is |
| encountered during parsing the file or inserting data. |
| If you invoke SqlTool with a SQL script on the command line, |
| the failure will cause SqlTool to roll back and exit. |
| If run interactively, you can decide whether to commit or |
| roll back the rows that inserted before the failure. |
| You can modify this behavior with the \a and \c settings. |
| </p> |
| <p> |
| If you set either a reject dsv file or a reject report file, |
| then failures during imports will be reported but will not |
| cause the import to abort. |
| When run in this way, SqlTool will give you a report at |
| the end about how many records were skipped, rejected, and |
| successfully inserted. |
| The reject dsv file is just a dsv file with exact copies of |
| the dsv records that failed to insert. |
| The reject report file is a HTML report which lists, for |
| every rejected record, why that record was rejected. |
| <code class="literal">\m?</code> will show you that the required PL |
| variables for this functionality are |
| <code class="varname">*DSV_REJECT_FILE</code> |
| and <code class="varname">*DSV_REJECT_REPORT</code>. |
| In both cases, you set the variable value to the path of the |
| file which SqlTool will create. |
| </p> |
| <p> |
| To allow for user-friendly entry of headers, we require |
| that tables for DSV import/exports use standard column names. |
| I.e., no column names that require quoting. |
| The DSV import and export parsers are very smart and |
| user-friendly. |
| The data types of columns are checked so that the parser can |
| make safe assumptions about white space and blank entries in |
| the data. |
| If a column is a JDBC Boolean type, for example, then we |
| know that a field value of " True " obviously means "True", |
| and that a field value of "" obviously means null. |
| Since we require vanilla style column names, we allow |
| white space anywhere in the header column. |
| We allow blank lines anywhere (where "lines" are delimited |
| by *DSV_ROW_DELIM). |
| By default, commented lines are ignored, and the comment |
| character can be changed from its default value. |
| </p> |
| <p> |
| Run the command "\x?" or "\m?" to see the several system PL |
| variables which you can set to adjust reject file behavior, |
| commenting behavior, and other DSV features. |
| </p> |
| <p> |
| You can also define some settings right in the DSV file, |
| and you can even specify multiple header lines in a single |
| DSV file. |
| I use this last feature to import data from one data set |
| into multple tables that are joined. |
| Since I don't have any more time to dedicate to explaining |
| all of these features, I'll give you some examples from |
| working DSV files and let you take it from there. |
| <div class="example"> |
| <a name="N10C8B"></a> |
| <p class="title"> |
| <b>Example 1.14. Sample DSV headerswitch settings</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="programlisting"> # RCS keyword was here. |
| |
| headerswitch{ |
| itemdef:name|-|-|hardness|breakdc|- |
| simpleitemdef:itemdef_name|maxvalue|weight|-|-|maxhp |
| }</pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| I'll just note that the prefixes for the header rows must be of |
| format target-table-name + :. |
| You can use * for target-table-name here, for the obvious purpose. |
| <div class="example"> |
| <a name="N10C91"></a> |
| <p class="title"> |
| <b>Example 1.15. DSV targettable setting</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="programlisting"> targettable=t</pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| This last example is from the SqlTool unit test file |
| <code class="filename">dsv-trimming.dsv</code>. |
| These special commands must be at the top of the file |
| (before any normal data or header lines). |
| </p> |
| <p> |
| There is also the <code class="varname">*DSV_CONST_COLS</code> setting, |
| which you can use to automatically write static, constant |
| values to the specified columns of all inserted rows. |
| </p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="sqltool_unittest-sect"></a>Unit Testing SqlTool</h2> |
| </div> |
| </div> |
| </div> |
| <p> |
| The SqlTool unit tests reside at testrun/sqltool in the |
| HyperSQL source code repository. |
| Just run the <code class="filename">runtests.bash</code> script from |
| that directory to execute all of the tests. |
| As you can see, the test runner, unfortunately, requires a Bash |
| shell at this time. |
| Read the file <code class="filename">README.txt</code> to find out all |
| about it, including everything you'd need to know to test your |
| own scripts or to add more unit test scripts for SqlTool. |
| </p> |
| </div> |
| <div class="footnotes"> |
| <br> |
| <hr align="left" width="100"> |
| <div class="footnote"> |
| <p> |
| <sup>[<a href="#samplelocFn" name="ftn.samplelocFn" class="simpara">1</a>] </sup> |
| To reduce the time I will need to spend maintaining this document, |
| in this chapter I am giving the path to the |
| <code class="filename">sample</code> directory as it is in HyperSQL 2.0.x |
| distributions, namely, <code class="filename">HSQLDB_HOME/sample</code>. |
| Users of HSQLDB before 2.0.x should translate these sample |
| directory paths |
| to use <code class="filename">HSQLDB_HOME/src/org/hsqldb/sample/...</code>. |
| </p> |
| </div> |
| </div> |
| </div> |
| <div class="chapter" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title"> |
| <a name="test-utility-chapt"></a>Chapter 2. Hsqldb Test Utility</h2> |
| </div> |
| </div> |
| </div> |
| <p>The <code class="classname">org.hsqldb.test</code> package contains a number |
| of tests for various functions of the database engine. Among these, the |
| <code class="classname">TestUtil</code> class performs the tests that are based on |
| scripts. To run the tests, you should compile the |
| <code class="filename">hsqldbtest.jar</code> target with Ant and JUnit.</p> |
| <p>The <code class="classname">TestUtil</code> class should be run in the |
| /testrun/hsqldb directory of the distributed files. It then runs the set |
| of TestSelf*.txt files in the directory. To start the application in |
| Windows, change to the directory and type:</p> |
| <pre class="screen"> java org.hsqldb.test.TestUtil</pre> |
| <p>All files in the working directory with names matching TestSelf*.txt |
| are processed in alphabetical order.</p> |
| <p>You can add your own scripts to test different series of SQL |
| queries. The format of the TestSelf*.txt file is simple text, with some |
| indentation and prefixes in the form of Java-style comments. The prefixes |
| indicate what the expected result should be.</p> |
| <p>The class <code class="classname">org.hsqldb.test.TestScriptRunner</code> is |
| a more general program which you can use to test any script files which |
| you specify (with scripts of the same exact format as described below). |
| For example, <pre class="screen">java org.hsqldb.test.TestScriptRunner --urlid=mem script1.tsql script2.sql</pre> |
| You must have the HSQLDB classes, including the util and test classes, in |
| your <code class="varname">CLASSPATH</code>. The urlid must be set up in an RC file |
| as explained in the <a class="link" href="#sqltool_auth-sect" title="RC File Authentication Setup">RC File Authentication Setup</a> section. Use the |
| <code class="literal">rcfile=</code> argument to specify an RC file other than the |
| default of <code class="filename">testscriptrunner.rc</code> in the current |
| directory. To see all invocation possibilities, just run TestScriptRunner |
| with no arguments at all. TestScriptRunner can run tests sequentially (the |
| default) or in simultaneous asynchronous threads.</p> |
| <div class="itemizedlist"> |
| <ul type="disc"> |
| <li> |
| <p>Comment lines must start with -- and are ignored</p> |
| </li> |
| <li> |
| <p>Lines starting with spaces are the continuation of the previous |
| line (for long SQL statements)</p> |
| </li> |
| <li> |
| <p>SQL statements with no prefix are simply executed.</p> |
| </li> |
| <li> |
| <p> |
| <span class="emphasis"><em>The remaining items in this list exemplify use of the |
| available command line-prefixes.</em></span> |
| </p> |
| </li> |
| <li> |
| <div class="informalexample"> |
| <p>The /*s*/ option stands for silent. It is used for |
| executing quries regardless of results. Used for preparation of |
| tests, not for actual tests.</p> |
| <pre class="programlisting">/*s*/ Any SQL statement - errors are ignored</pre> |
| </div> |
| </li> |
| <li> |
| <div class="informalexample"> |
| <p>The /*c<rows>*/ option is for SELECT queries and |
| asserts the number of rows in the result matches the given |
| count.</p> |
| <pre class="programlisting">/*c<rows>*/ SQL statement returning count of <rows></pre> |
| </div> |
| </li> |
| <li> |
| <div class="informalexample"> |
| <p>The /*u*/ option is for queries that return an update |
| count, such as DELETE and UPDATE. It asserts the update count |
| matches.</p> |
| <pre class="programlisting">/*u<count>*/ SQL statement returning an update count equal to <count></pre> |
| </div> |
| </li> |
| <li> |
| <div class="informalexample"> |
| <p>The /*e*/ option asserts that the given query results in an |
| erros. It is mainly used for testing the error detection |
| capabilities of the engine. The SQL State of the expected error can |
| be defined, for example /*e42578*/, to verify the returned error. |
| This option can be used with syntactically valid queries to assert a |
| certain state in the database. For example a CREATE TABLE can be |
| used to assert the table of the same name already exists.</p> |
| <pre class="programlisting">/*e*/ SQL statement that should produce an error when executing</pre> |
| </div> |
| </li> |
| <li> |
| <div class="informalexample"> |
| <p>The /*r....*/ option asserts the SELECT query returns a |
| single row containing the given set of field values.</p> |
| <pre class="programlisting">/*r<string1>,<string2>*/ SQL statement returning a single row ResultSet equal to the specified value</pre> |
| </div> |
| </li> |
| <li> |
| <div class="informalexample"> |
| <p>The extended /*r...*/ option asserts the SELECT query |
| returns the given rows containing the given set of field |
| values.</p> |
| <pre class="programlisting">/*r |
| <string1>,<string2> |
| <string1>,<string2> |
| <string1>,<string2> |
| */ SQL statement returning a multiple row ResultSet equal to the specified values</pre> |
| </div> |
| <p>(note that the result set lines are indented).</p> |
| </li> |
| <li> |
| <p>The /*d*/ directive just displays the supplied |
| text.</p> |
| <div class="informalexample"> |
| <pre class="programlisting">/*d*/ Some message</pre> |
| </div> |
| </li> |
| <li> |
| <p>The /*w MILLIS*/ directive causes the test to Wait for a |
| specified number of millisedonds.</p> |
| <div class="informalexample"> |
| <pre class="programlisting">/*w 1000*/ Optional message</pre> |
| </div> |
| </li> |
| <li> |
| <p>The /*w ENFORCE_SEQUENCE WAITER_NAME*/ directive causes the |
| test to Wait for the specified <span class="emphasis"><em>Waiter</em></span>. A waiter |
| is just name that is used to associate a /*w*/ directive to its |
| corresponding /*p*/ directive. The ENFORCE_SEQUENCE argument must be |
| set to <code class="literal">true</code> or <code class="literal">false</code> to specify |
| whether to fail unless the /*p*/ command runs after the /*w*/ command |
| is waiting.</p> |
| <div class="informalexample"> |
| <pre class="programlisting">/*w true script4*/ Optional message</pre> |
| </div> |
| </li> |
| <li> |
| <p>The /*p ENFORCE_SEQUENC WAITER_NAME*/ directive is the peer |
| directive to /*w*/, which causes a waiting thread to |
| Proceed.</p> |
| <div class="informalexample"> |
| <pre class="programlisting">/*p true script4*/ Optional message</pre> |
| </div> |
| </li> |
| <li> |
| <p>All the options are lowercase letters. During development, an |
| uppercase can be used for a given test to exclude a test from the test |
| run. The utility will just report the test blocks that have been |
| excluded without running them. Once the code has been developed, the |
| option can be turned into lowercase to perform the actual test.</p> |
| </li> |
| </ul> |
| </div> |
| <p>See the TestSelf*.txt files in the /testrun/hsqldb/ directory for |
| actual examples.</p> |
| <p>The String <code class="literal">${timestamp}</code> may be used in script |
| messages (like in /*d*/, /*w*/, /*p*/). It expands to the current time, |
| down to the second. For example, <div class="informalexample"> |
| <pre class="programlisting">/*d*/ It is now ${timestamp}</pre> |
| </div> |
| </p> |
| </div> |
| <div class="chapter" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title"> |
| <a name="dbm-chapt"></a>Chapter 3. Database Manager</h2> |
| </div> |
| <div> |
| <div class="authorgroup"> |
| <div class="author"> |
| <h3 class="author"> |
| <span class="firstname">Fred</span> <span class="surname">Toussi</span> |
| </h3> |
| <div class="affiliation"> |
| <span class="orgname">The HSQL Development Group<br> |
| </span> |
| </div> |
| </div> |
| <div class="author"> |
| <h3 class="author"> |
| <span class="firstname">Blaine</span> <span class="surname">Simpson</span> |
| </h3> |
| <div class="affiliation"> |
| <span class="orgname">The HSQL Development Group<br> |
| </span> |
| </div> |
| </div> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div class="toc"> |
| <p> |
| <b>Table of Contents</b> |
| </p> |
| <dl> |
| <dt> |
| <span class="section"><a href="#dbm_intro-sect">Brief Introduction</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#dbm_tree-sect">Auto tree-update</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#dbm-autoconn-sect">Automatic Connection</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#dbm_rcfile-sect">RC File</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#dbm_wold-sect">Using the current DatabaseManagers with an older HSQLDB |
| distribution.</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="#dbm_applet-sect">DatabaseManagerSwing as an Applet</a></span> |
| </dt> |
| </dl> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="dbm_intro-sect"></a>Brief Introduction</h2> |
| </div> |
| </div> |
| </div> |
| <p>The Database Manager tool is a simple GUI database query tool with |
| a tree display of the tables. Both AWT and SWING versions of the tool |
| are available and work almost identically. The AWT version class name is |
| org.hsqldb.util.DatabaseManager; the SWING version, |
| org.hsqldb.util.DatabaseManagerSwing. The SWING version has more |
| refinements than the AWT version.</p> |
| <p>The AWT version of the database manager can be deployed as an |
| applet in a browser. A demo HTML file with an embedded Database Manager |
| is included in the /demo directory.</p> |
| <p>When the Database Manager is started, a dialogue allows you to |
| enter the JDBC driver, URL, user and password for the new connection. A |
| drop-down box, Type, offers preset values for JDBC driver and URL for |
| most popular database engines, including HSQLDB. Once you have selected |
| an item from this drop-down box, you should edit the URL to specify the |
| details of the database or any additional properties to pass. You should |
| also enter the username and password before clicking on the OK |
| button.</p> |
| <p>The connection dialogue allows you to save the settings for the |
| connection you are about to make. You can then access the connection in |
| future sessions. To save a connection setting, enter a name in the |
| Setting Name box before clicking on the OK button. Next time the |
| connection dialogue is displayed, the drop-down box labeled Recent will |
| include the name for all the saved connection settings. When you select |
| a name, the individual settings are displayed in the appropriate |
| boxes.</p> |
| <p>The small Clr button next to the drop-down box allows you to clear |
| all the saved settings. If you want to modify an existing setting, first |
| select it from the drop-down box then modify any of the text boxes |
| before making the connection. The modified values will be saved.</p> |
| <p>Most SWING menu items have context-sensitive tool tip help text |
| which will appear if you hold the mouse cursor still over the desired |
| menu item. (Assuming that you don't turn Tooltips off under the |
| <span class="guimenu">Help</span> menu.</p> |
| <p>The database object tree in the SWING version allows you to |
| right click on the name of a table or column and choose from common SQL |
| statements for the object, for example SELECT * FROM thistable ... If |
| you click on one of the given choices, the sample statement is copied to |
| the command window, where you can modify and complete it.</p> |
| <p>The DatabaseManagers do work with HSQLDB servers serving |
| TLS-encrypted JDBC data. See the TLS section of the Listeners chapter of |
| the <a class="link" href="distro_baseurl_DEFAULTVAL/guide/index.html" target="_top"> |
| HyperSQL User Guide</a> |
| </p> |
| <div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Tip"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Tip]" src="../images/db/tip.png"></td><th align="left">Tip</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p>If you are using DatabaseManagerSwing with Oracle, you will |
| want to make sure that <span class="guimenuitem">Show row counts</span> and |
| <span class="guimenuitem">Show row counts</span> are both off |
| <span class="emphasis"><em>before connecting to the database</em></span>. You may also |
| want to turn off Auto tree-update, as described in the next |
| section.</p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="dbm_tree-sect"></a>Auto tree-update</h2> |
| </div> |
| </div> |
| </div> |
| <p>By default, the object tree in the left panel is refreshed when |
| you execute DDL which may update those objects. If you are on a slow |
| network or performance-challenged PC, use the <span class="guimenu">view</span> / |
| <span class="guimenuitem">Auto-refresh tree</span> menu item to turn it off. |
| You will then need to use the |
| <span class="guimenu">view</span><span class="guimenuitem">Refresh tree</span> menu item |
| every time that you want to refresh the tree.</p> |
| <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Note"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Note]" src="../images/db/note.png"></td><th align="left">Note</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p>Auto-refresh tree does not automatically show all updates to |
| database objects, it only refreshes when you submit DDL which may |
| update database objects. (This behavior is a compromise between |
| utility and performance).</p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="dbm-autoconn-sect"></a>Automatic Connection</h2> |
| </div> |
| </div> |
| </div> |
| <p>You can use command-line switches to supply connection |
| information. If you use these switch(es), then the connection dialog |
| window will be skipped and a JDBC connection will be established |
| immediately. Assuming that the <code class="filename">hsqldb.jar</code> (or an |
| alternative jar) are in your <code class="varname">CLASSPATH</code>, this command |
| will list the available command-line options. <div class="informalexample"> |
| <pre class="screen"> java org.hsqldb.util.DatabaseManagerSwing --help</pre> |
| </div> |
| </p> |
| <p>It's convenient to skip the connection dialog window if you |
| always work with the same database account.</p> |
| <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Warning"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Warning]" src="../images/db/warning.png"></td><th align="left">Warning</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p>Use of the --password switch is not secure. Everything typed |
| on command-lines is generally available to other users on the |
| computer. The problem is compounded if you use a network connection to |
| obtain your command line. The RC File section explains how you can set |
| up automatic connections without supplying a password on the command |
| line.</p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="dbm_rcfile-sect"></a>RC File</h2> |
| </div> |
| </div> |
| </div> |
| <p>You can skip the connection dialog window securely by putting |
| the connection information into an RC file and then using the |
| <code class="literal">--urlid</code> switch to DatabaseManager or |
| DatabaseManagerSwing. This strategy is great for adding launch menu |
| items and/or launch icons to your desktop. You can set up one icon for |
| each of the database accounts which you regularly use.</p> |
| <p>The default location for the RC file is |
| <code class="filename">dbmanager.rc</code> in your home directory. The <a class="link" href="#sqltool_auth-sect" title="RC File Authentication Setup">RC File Authentication Setup</a> section |
| explains how to put the connection information into this text file. If |
| you also run <a class="link" href="#sqltool-chapt" title="Chapter 1. SqlTool">SqlTool</a>, then you can share the RC file with |
| SqlTool by using a sym-link (if your operating system supports sym |
| links), or by using the <code class="literal">--rcfile</code> switch for either |
| SqlTool or DatabaseManagerSwing.</p> |
| <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Warning"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Warning]" src="../images/db/warning.png"></td><th align="left">Warning</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p>Use your operating system facilities to prevent others from |
| reading your RC file, since it contains passwords.</p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <p>To set up launch items/icons, first experiment on your command |
| line to find exactly what command works. For example, <div class="informalexample"> |
| <pre class="screen">java -cp /path/to/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing --urlid mem</pre> |
| </div> Then, use your window manager to add an item that |
| runs this command.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="dbm_wold-sect"></a>Using the current DatabaseManagers with an older HSQLDB |
| distribution.</h2> |
| </div> |
| </div> |
| </div> |
| <p>This procedure will allow users of a legacy version of HSQLDB |
| to use all of the new features of the DatabaseManagers. You will also |
| get the new version of the SqlTool! This procedure works for distros |
| going back to 1.7.3.3 at least, probably much farther.</p> |
| <p>These instructions assume that you are capable of running an |
| Ant build. See the Building Appendix of the <a class="link" href="distro_baseurl_DEFAULTVAL/guide/index.html" target="_top"> HyperSQL User |
| Guide</a>.</p> |
| <div class="procedure"> |
| <ol type="1"> |
| <li> |
| <p>Download and extract a current HSQLDB distribution. If you |
| don't want to use the source code, documentation, etc., you can use |
| a temporary directory and remove it afterwards.</p> |
| </li> |
| <li> |
| <p>Cd to the build directory under the root directory where |
| you extracted the distribution to.</p> |
| </li> |
| <li> |
| <p>Run <code class="literal">ant hsqldbutil</code>.</p> |
| </li> |
| <li> |
| <p>If you're going to wipe out the build directory, copy |
| <code class="filename">hsqldbutil.jar</code> to a safe location |
| first.</p> |
| </li> |
| <li> |
| <p>For now on, whenver you are going to run DatabaseManager*, |
| make sure that you have this <code class="filename">hsqldbutil.jar</code> as |
| the first item in your <code class="varname">CLASSPATH</code>.</p> |
| </li> |
| </ol> |
| </div> |
| <p>Here's a UNIX example where somebody wants to use the new |
| DatabaseManagerSwing with their older HSQLDB database, as well as with |
| Postgresql and a local application. <div class="informalexample"> |
| <pre class="screen">CLASSPATH=/path/to/hsqldbutil.jar:/home/bob/myapp/classes:/usr/local/lib/pg.jdbc3.jar |
| export CLASSPATH |
| java org.hsqldb.util.DatabaseManagerSwing --urlid urlid</pre> |
| </div> |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="dbm_applet-sect"></a>DatabaseManagerSwing as an Applet</h2> |
| </div> |
| </div> |
| </div> |
| <p>DatabaseManagerSwing is also an applet. You can use it in HTML, |
| JSPs, etc. Be aware that in Applet mode, actions to load or save local |
| files will be disabled, and attempts to access any server other than the |
| HTML-serving-host will fail.</p> |
| <p>Since the Applet can not store or load locally saved preferences, |
| the only way to have persistent preference settings is by using Applet |
| parameters. <div class="variablelist"> |
| <p class="title"> |
| <b>DatabaseManagerSwing Applet Parameters</b> |
| </p> |
| <table border="0"> |
| <col valign="top" align="left"> |
| <tbody> |
| <tr> |
| <td> |
| <p> |
| <span class="term">jdbcUrl</span> |
| </p> |
| </td><td>URL of a data source to auto-connect to. String |
| value.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">jdbcDriver</span> |
| </p> |
| </td><td>URL of a data source to auto-connect to. String value. |
| Defaults to |
| <code class="classname">org.hsqldb.driver.JDBCDriver</code>.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">jdbcUser</span> |
| </p> |
| </td><td>User name for data source to auto-connect to. String |
| value.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">jdbcPassword</span> |
| </p> |
| </td><td>Password for data source to auto-connect to. String |
| value. Defaults to zero-length string.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">schemaFilter</span> |
| </p> |
| </td><td>Display only object from this schema in the object |
| navigator. String value.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">laf</span> |
| </p> |
| </td><td>Look-and-feel. String value.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">loadSampleData</span> |
| </p> |
| </td><td>Auto-load sample data. Boolean value. Defaults to |
| false.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">autoRefresh</span> |
| </p> |
| </td><td>Auto-refresh the object navigator when DDL |
| modifications detected in user SQL commands. Boolean value. |
| Defaults to true.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">showRowCounts</span> |
| </p> |
| </td><td>Show number of rows in each table in the object |
| navigator. Boolean value. Defaults to false.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">showSysTables</span> |
| </p> |
| </td><td>Show system tables in the object navigator. Boolean |
| value. Defaults to false.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">showSchemas</span> |
| </p> |
| </td><td>Show object names like schema.name in object navigator. |
| Boolean value. Defaults to true.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">resultGrid</span> |
| </p> |
| </td><td>Show query results in Gui grid (as opposed to in plain |
| text). Boolean value. Defaults to true.</td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">showToolTips</span> |
| </p> |
| </td><td>Show help hover-text. Boolean value. Defaults to |
| true.</td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| </p> |
| </div> |
| </div> |
| <div class="chapter" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title"> |
| <a name="transfer-tool-chapt"></a>Chapter 4. Transfer Tool</h2> |
| </div> |
| <div> |
| <div class="authorgroup"> |
| <div class="author"> |
| <h3 class="author"> |
| <span class="firstname">Fred</span> <span class="surname">Toussi</span> |
| </h3> |
| <div class="affiliation"> |
| <span class="orgname">The HSQL Development Group<br> |
| </span> |
| </div> |
| </div> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div class="toc"> |
| <p> |
| <b>Table of Contents</b> |
| </p> |
| <dl> |
| <dt> |
| <span class="section"><a href="#trantool_intro-sect">Brief Introduction</a></span> |
| </dt> |
| </dl> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="trantool_intro-sect"></a>Brief Introduction</h2> |
| </div> |
| </div> |
| </div> |
| <p>Transfer Tool is a GUI program for transferring SQL schema and |
| data from one JDBC source to another. Source and destination can be |
| different database engines or different databases on the same |
| server.</p> |
| <p>Transfer Tool works in two different modes. Direct transfer |
| maintains a connection to both source and destination and performs the |
| transfer. Dump and Restore mode is invoked once to transfer the data |
| from the source to a text file (Dump), then again to transfer the data |
| from the text file to the destination (Restore). With Dump and Restore, |
| it is possible to make any changes to database object definitions and |
| data prior to restoring it to the target.</p> |
| <p>Dump and Restore modes can be set via the command line with -d |
| (--dump) or -r (--restore) options. Alternatively the Transfer Tool can |
| be started with any of the three modes from the Database Manager's Tools |
| menu.</p> |
| <p>The connection dialogue allows you to save the settings for the |
| connection you are about to make. You can then access the connection in |
| future sessions. These settings are shared with those from the Database |
| Manager tool. See the appendix on Database Manager for details of the |
| connection dialogue box.</p> |
| <p>From version 1.8.0 Transfer Tool is no longer part of the |
| <code class="filename">hsqldb.jar</code>. You can build the |
| <code class="filename">hsqldbutil.jar</code> using the Ant command of the same |
| name, to build a jar that includes Transfer Tool and the Database |
| Manager.</p> |
| <p>When collecting meta-data, Transfer Tool performs SELECT * FROM |
| <table> queries on all the tables in the source database. This may |
| take a long time with some database engines. When the source database is |
| HSQLDB, this means memory should be available for the result sets |
| returned from the queries. Therefore, the memory allocation of the java |
| process in which Transfer Tool is executed may have to be high.</p> |
| <p>The current version of Transfer is far from ideal, as it has not |
| been actively developed for several years. The program also lacks the |
| ability to create UNIQUE constraints and creates UNIQUE indexes instead. |
| However, some bugs have been fixed in the latest version and the program |
| can be used with most of the supported databases. The best way to use |
| the program is the DUMP and RESTORE modes, which allow you to manually |
| change the SQL statements in the dump file before restoring to a |
| database. A useful idea is to dump and restore the database definition |
| separately from the database data.</p> |
| </div> |
| </div> |
| <div class="appendix" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h1 class="title"> |
| <a name="N10E8E"></a>HyperSQL File Links</h1> |
| </div> |
| <div> |
| <h3 class="subtitle"> |
| <i>HyperSQL Files referred to in this Guide</i> |
| </h3> |
| </div> |
| </div> |
| </div> |
| <p> |
| HyperSQL files referred to in the text may be retrieved from the |
| canonical HyperSQL documentation site, http://hsqldb.org/doc/2.0, or from the |
| same location you are reading this page from. |
| </p> |
| <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> |
| <table border="0" summary="Note"> |
| <tr> |
| <td valign="top" align="center" rowspan="2" width="25"><img alt="[Note]" src="../images/db/note.png"></td><th align="left">Note</th> |
| </tr> |
| <tr> |
| <td valign="top" align="left"> |
| <p> |
| If you are reading this document with a standalone PDF reader, |
| only the http://hsqldb.org/doc/2.0/... links will function. |
| </p> |
| </td> |
| </tr> |
| </table> |
| </div> |
| <div class="itemizedlist"> |
| <p class="title"> |
| <b> |
| Pairs of local + http://hsqldb.org/doc/2.0 links for referenced files. |
| </b> |
| </p> |
| <ul type="disc"> |
| <li> |
| <a name="sqltool.rc-link"></a> |
| <p> |
| Local: |
| <a class="link" href="../verbatim/sample/sqltool.rc" target="_top">../verbatim/sample/sqltool.rc</a> |
| |
| </p> |
| <p> |
| |
| <a class="link" href="http://hsqldb.org/doc/2.0/verbatim/sample/sqltool.rc" target="_top">http://hsqldb.org/doc/2.0/verbatim/sample/sqltool.rc</a> |
| |
| </p> |
| </li> |
| <li> |
| <a name="sampledata.sql-link"></a> |
| <p> |
| Local: |
| <a class="link" href="../verbatim/sample/sampledata.sql" target="_top">../verbatim/sample/sampledata.sql</a> |
| |
| </p> |
| <p> |
| |
| <a class="link" href="http://hsqldb.org/doc/2.0/verbatim/sample/sampledata.sql" target="_top">http://hsqldb.org/doc/2.0/verbatim/sample/sampledata.sql</a> |
| |
| </p> |
| </li> |
| <li> |
| <a name="sample.sql-link"></a> |
| <p> |
| Local: |
| <a class="link" href="../verbatim/sample/sample.sql" target="_top">../verbatim/sample/sample.sql</a> |
| |
| </p> |
| <p> |
| |
| <a class="link" href="http://hsqldb.org/doc/2.0/verbatim/sample/sample.sql" target="_top">http://hsqldb.org/doc/2.0/verbatim/sample/sample.sql</a> |
| |
| </p> |
| </li> |
| <li> |
| <a name="pl.sql-link"></a> |
| <p> |
| Local: |
| <a class="link" href="../verbatim/sample/pl.sql" target="_top">../verbatim/sample/pl.sql</a> |
| |
| </p> |
| <p> |
| |
| <a class="link" href="http://hsqldb.org/doc/2.0/verbatim/sample/pl.sql" target="_top">http://hsqldb.org/doc/2.0/verbatim/sample/pl.sql</a> |
| |
| </p> |
| </li> |
| <li> |
| <a name="plsql.sql-link"></a> |
| <p> |
| Local: |
| <a class="link" href="../verbatim/sample/plsql.sql" target="_top">../verbatim/sample/plsql.sql</a> |
| |
| </p> |
| <p> |
| |
| <a class="link" href="http://hsqldb.org/doc/2.0/verbatim/sample/plsql.sql" target="_top">http://hsqldb.org/doc/2.0/verbatim/sample/plsql.sql</a> |
| |
| </p> |
| </li> |
| <li> |
| <a name="dsv-sample.sql-link"></a> |
| <p> |
| Local: |
| <a class="link" href="../verbatim/sample/dsv-sample.sql" target="_top">../verbatim/sample/dsv-sample.sql</a> |
| |
| </p> |
| <p> |
| |
| <a class="link" href="http://hsqldb.org/doc/2.0/verbatim/sample/dsv-sample.sql" target="_top">http://hsqldb.org/doc/2.0/verbatim/sample/dsv-sample.sql</a> |
| |
| </p> |
| </li> |
| <li> |
| <a name="sqljrt.sql-link"></a> |
| <p> |
| Local: |
| <a class="link" href="../verbatim/testrun/sqltool/sqljrt.sql" target="_top">../verbatim/testrun/sqltool/sqljrt.sql</a> |
| |
| </p> |
| <p> |
| |
| <a class="link" href="http://hsqldb.org/doc/2.0/verbatim/testrun/sqltool/sqljrt.sql" target="_top">http://hsqldb.org/doc/2.0/verbatim/testrun/sqltool/sqljrt.sql</a> |
| |
| </p> |
| </li> |
| <li> |
| <a name="sqlpsm.sql-link"></a> |
| <p> |
| Local: |
| <a class="link" href="../verbatim/testrun/sqltool/sqlpsm.sql" target="_top">../verbatim/testrun/sqltool/sqlpsm.sql</a> |
| |
| </p> |
| <p> |
| |
| <a class="link" href="http://hsqldb.org/doc/2.0/verbatim/testrun/sqltool/sqlpsm.sql" target="_top">http://hsqldb.org/doc/2.0/verbatim/testrun/sqltool/sqlpsm.sql</a> |
| |
| </p> |
| </li> |
| <li> |
| <a name="SqlFileEmbedder.java-link"></a> |
| <p> |
| Local: |
| <a class="link" href="../verbatim/src/org/hsqldb/sample/SqlFileEmbedder.java" target="_top">../verbatim/src/org/hsqldb/sample/SqlFileEmbedder.java</a> |
| |
| </p> |
| <p> |
| |
| <a class="link" href="http://hsqldb.org/doc/2.0/verbatim/src/org/hsqldb/sample/SqlFileEmbedder.java" target="_top">http://hsqldb.org/doc/2.0/verbatim/src/org/hsqldb/sample/SqlFileEmbedder.java</a> |
| |
| </p> |
| </li> |
| <li> |
| <a name="SqlTool.html-link"></a> |
| <p> |
| Local: |
| <a class="link" href="../apidocs/org/hsqldb/cmdline/SqlFile.html" target="_top">../apidocs/org/hsqldb/cmdline/SqlFile.html</a> |
| |
| </p> |
| <p> |
| |
| <a class="link" href="http://hsqldb.org/doc/2.0/apidocs/org/hsqldb/jcmdline/SqlFile.html" target="_top">http://hsqldb.org/doc/2.0/apidocs/org/hsqldb/jcmdline/SqlFile.html</a> |
| |
| </p> |
| </li> |
| </ul> |
| </div> |
| </div> |
| </div> |
| <HR> |
| <P class="svnrev">$Revision: 3539 $</P> |
| </body> |
| </html> |