Java DB Tools and Utilities Guide
Version 10.5
Derby Document build:
May 28, 2009, 5:22:24 PM (EDT)
Apache License Version 2.0, January 2004 http://www.apache.org/licenses/ TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION 1. Definitions. "License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document. "Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License. "Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity. "You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License. "Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files. "Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types. "Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below). "Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof. "Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution." "Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work. 2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form. 3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed. 4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions: (a) You must give any other recipients of the Work or Derivative Works a copy of this License; and (b) You must cause any modified files to carry prominent notices stating that You changed the files; and (c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and (d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding those notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License. You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License. 5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions. 6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file. 7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License. 8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages. 9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability. END OF TERMS AND CONDITIONS APPENDIX: How to apply the Apache License to your work. To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "[]" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. We also recommend that a file or class name and description of purpose be included on the same "printed page" as the copyright notice for easier identification within third-party archives. Copyright [yyyy] [name of copyright owner] Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
• | ij | |
• | the import and export utilities | |
• | the jar file utilities | |
• | sysinfo | |
• | dblook | |
• | SignatureChecker |
• | developers, who might use the tools when developing applications | |
• | system administrators, who might use the tools to run backup scripts or
to import large amounts of data | |
• | end-users, who might use one of the tools to run ad-hoc queries against
a database |
• | What are the Derby tools and utilities? Overview
of the tools and utilities, and Derby and
JDBC basics for new or infrequent users. | |
• | Using ij How to get
started with ij, a JDBC and SQL scripting tool. | |
• | ij properties reference Reference
for ij properties. | |
• | ij commands and errors reference Reference
for ij commands and errors. | |
• | Using the bulk import and export procedures Reference
and how-to instructions for using bulk import and export. | |
• | Storing jar files in a database Syntax
for executing the built-in procedures for storing jar files in the database. | |
• | sysinfo Reference
information on the utility that provides information about your Derby environment. | |
• | dblook Reference
information for a utility that dumps the DDL of a user-specified database to
either a console or a file. | |
• | SignatureChecker Reference
information for a tool that identifies any SQL functions
and procedures in a database that do not follow the SQL Standard argument
matching rules. |
• | ij ij is Derby's
interactive JDBC scripting tool. It is a simple utility for running scripts
against a Derby database.
You can also use it interactively to run ad hoc queries. ij provides
several commands for ease in accessing a variety of JDBC features. ij can
be used in an embedded or a client/server environment. | |
• | The import and export utilities These server-side utilities
allow you to import data directly from files into tables and to export data
from tables into files. Server-side utilities can be in a client/server environment
but require that all files referenced be on the Server machine. | |
• | The jar file utilities These utilities allow you
to store jar files in a database. | |
• | sysinfo sysinfo provides information about your version of Derby and your environment. | |
• | dblook dblook is Derby's
Data Definition Language (DDL) Generation Utility, more informally called a schema dump
tool. It is a simple utility that dumps the DDL of a user-specified database
to either a console or a file. The generated DDL can then be used for such
things as recreating all or parts of a database, viewing a subset of a database's
objects (for example, those which pertain to specific tables and schemas),
or documenting a database's schema. | |
• | SignatureChecker The SignatureChecker tool identifies any SQL functions
and procedures in a database that do not follow the SQL Standard argument
matching rules. |
• | org.apache.derby.jdbc.EmbeddedDriver For embedded environments,
when Derby runs in the
same JVM as the application. This is commonly referred to as the embedded
drvier. | |
• | org.apache.derby.jdbc.ClientDriver For client/server environments
that use the Derby Network
Server. This is commonly referred to as the Network Client driver. |
• | jdbc:derby:databaseName;URLAttributes |
• | jdbc:derby://host:port/databaseName;URLAttributes |
• | ||
• | ||
• | host The name of the machine where the server is running.
It can be the name of the machine or the address. | |
• |
• | ||
• | Database territory This is the territory associated
with your database when it is created. By default, this is the same as the java system locale. The database territory determines
the language of database errors. | |
• |
ij> VALUES CURRENT_DATE; 1 ---------- 2001-09-06 1 row selected ij> localizeddisplay on; ij> VALUES CURRENT_DATE; 1 ------------------ September 6, 2001 1 row selected
ij [-p propertyFile] [inputFile]
(UNIX) java [options] -jar $DERBY_HOME/lib/derbyrun.jar ij [-p propertyFile] [inputFile]
(Windows) java [options] -jar %DERBY_HOME%\lib\derbyrun.jar ij [-p propertyFile] [inputFile]
java [options] org.apache.derby.tools.ij [-p propertyFile] [inputFile]
• | java Start the JVM. | |
• | options The options that the JVM uses. You can
use the -D option to set ij properties (see Starting ij using properties)
or system properties, such as Derby properties. | |
• | propertyFile A file you can use to set ij properties
(instead of the -D option). The property file should be in the format created
by the java.tools.Properties.save methods, which is the same format as the
derby.properties file. | |
• | inputFile A file from which to read commands.
The ij tool exits at the end of the file or an exit command. Using an input
file causes ij to print out the commands as it runs them. If you reroute standard
input, ij does not print out the commands. If you do not supply an input file,
ij reads from the standard input. |
1.
| To create a database with the ij tool, type the
following command:
This command creates a database called testdb in
the current directory, populates the system tables, and connects to the database.
You can then run any SQL statements from the ij command line. |
1.
| by using the -D option on the command line | |
2.
| by specifying a properties file using the -p propertyfile option
on the command line |
java -jar derbyrun.jar -p ij.properties
java -Dij.maximumDisplayWidth=1000 -jar derbyrun.jar
java -Dij.protocol=jdbc:derby: -Dij.database=sample derbyrun.jar
To see a list of connection names and the URLs used to connect to them, use the following command. (If there is a connection that is currently active, it will show up with an * after its name.)java -Dij.connection.sample=jdbc:derby:sample -Dij.connection.History=jdbc:derby:History -Dderby.system.home=c:\derby\demo\databases -jar c:\derby\lib\derbyrun.jar
ij version 10.5 ij(HISTORY)> show connections; HISTORY* - jdbc:derby:History SAMPLE - jdbc:derby:sample * = current connection ij(HISTORY)>
1.
| Start the JVM | |
2.
| Load the appropriate driver. | |
3.
| Create a connection by providing a valid database connection URL. |
If the URL entered contains Network Client information the Connect command loads the Network Client driver:D:>java org.apache.derby.tools.ij ij version 10.5 ij> connect 'jdbc:derby:sample'; ij>
D:>java org.apache.derby.tools.ij ij version 10.5 ij> connect 'jdbc:derby://localhost:1527/sample'; ij>
• | Supplying full connection information on the command line Specifying
one of the following properties along with a valid connection URL on the ij
command line starts ij with the connection already active. This is often used
when running a SQL script so the database name or path can be specified at
runtime.
| ||||||||||
• | Defining a Protocol and using a "short form" URL A default URL protocol
and subprotocol can be specified by setting the property ij.protocol or
using the ij Protocol command.
This allows a connection to be made by specifying only the database name.
This "short form" of the database connection URL defaults the protocol (For
more information, see About Protocols). This
example uses the ij Protocol command
and a "short form" connection URL:
| ||||||||||
• | Specifying an alternate Driver If you are using the drivers supplied
by Derby, you can specify
the driver names listed in JDBC drivers overview.
However, the Derby drivers
are implicitly loaded when a supported protocol is used so specifying them
is probably redundant. Specifying a driver is required when ij is used with
other JDBC drivers to connect to non-Derby databases.
To use drivers supplied by other vendors explicitly specify the driver one
of three ways
|
Action | System Property | ij Property | ij Command |
loading the driver implicitly | ' | ij.connection.connectionName (plus
full URL) ij.database (plus
full URL) ij.protocolij.protocol.protocolName (plus
protocol clause in Connect command) | |
loading the driver explicitly | jdbc.drivers | ||
specifying the database connection URL | ' |
• | Name an input file as a command-line argument. For
example:
| |
• | Redirect standard input to come from a file. For
example:
| |
• | Use the Run command
from the ij command line. For example:
|
• | By redirecting output to a file:
| |
• | By setting the ij.outfile property:
|
ij.connection.connectionName=databaseConnectionURL
D:> java -Dij.connection.sample1=jdbc:derby:sample -Dij.connection.anotherConn=jdbc:derby:anotherDB;create=true org.apache.derby.tools.ij ij version 10.5 ANOTHERCONN* - jdbc:derby:anotherDB;create=true SAMPLE1 - jdbc:derby:sample * = current connection ij(ANOTHERCONN)>
If you do not specify ij.dataSource.databaseName and get an error indicating no database was found, you can still connect to a database by using ij's connect command. You should not specify the protocol (for example jdbc:derby:) in the connect command when using ij.dataSource.ij.dataSource=datasource class name ij.dataSource.databaseName=databasename [ij.dataSource.createDatabase=create]
# # If your application runs on JDK 1.6 or higher, then you should # specify the JDBC4 variant of this DataSource: # org.apache.derby.jdbc.EmbeddedDataSource40. # If your application runs with a jvm supporting JSR169, you cannot use # org.apache.derby.jdbc.EmbeddedDataSource, instead, use: # org.apache.derby.jdbc.EmbeddedSimpleDataSource. # java -Dij.dataSource=org.apache.derby.jdbc.EmbeddedDataSource -Dij.dataSource.databaseName=sample -Dij.dataSource.createDatabase=create org.apache.derby.tools.ij ij version 10.5 CONNECTION0* * = current connection ij>
# # Start ij using EmbeddedSimpleDataSource # java -Dij.dataSource=org.apache.derby.jdbc.EmbeddedSimpleDataSource org.apache.derby.tools.ij ERROR XJ004: Database '' not found. ij version 10.5 ij> connect 'smalldb;create=true'; ij>
java -Dij.exceptionTrace=true org.apache.derby.tools.ij ij version 10.5 ij> connect 'jdbc:derby:wombat'; ERROR XJ004: Database 'wombat' not found. SQL Exception: Database 'wombat' not found. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:44) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:93) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:172) at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(EmbedConnection.java:1955) at org.apache.derby.impl.jdbc.EmbedConnection.(EmbedConnection.java:254) at org.apache.derby.impl.jdbc.EmbedConnection30.(EmbedConnection30.java:72) at org.apache.derby.jdbc.Driver30.getNewEmbedConnection(Driver30.java:73) at org.apache.derby.jdbc.InternalDriver.connect(InternalDriver.java:200) at java.sql.DriverManager.getConnection(DriverManager.java:512) at java.sql.DriverManager.getConnection(DriverManager.java:140) at org.apache.derby.impl.tools.ij.ij.dynamicConnection(ij.java:873) at org.apache.derby.impl.tools.ij.ij.ConnectStatement(ij.java:723) at org.apache.derby.impl.tools.ij.ij.ijStatement(ij.java:553) at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:289) at org.apache.derby.impl.tools.ij.Main.go(Main.java:207) at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:173) at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55) at org.apache.derby.tools.ij.main(ij.java:60) ij ij>
• | jdbc:derby: |
java -Dij.dataSource=org.apache.derby.jdbc.EmbeddedDataSource -Dij.dataSource.databaseName=sample -Dij.showNoConnectionsAtStart=true org.apache.derby.tools.ij ij version 10.5 ij> disconnect; ij> exit; java -Dij.dataSource=org.apache.derby.jdbc.EmbeddedDataSource -Dij.dataSource.databaseName=sample -Dij.showNoConnectionsAtStart=false org.apache.derby.tools.ij ij version 10.5 CONNECTION0* * = current connection ij> disconnect; ij> exit;
java -Dij.showNoCountForSelect=true org.apache.derby.tools.ij ij version 10.5 CONNECTION0* * = current connection ij> create table t1 (c1 int); 0 rows inserted/updated/deleted ij> insert into t1 values 1, 2, 3; 3 rows inserted/updated/deleted ij> select * from t1; C1 ----------- 1 2 3 ij> disconnect; ij> exit; java -Dij.showNoCountForSelect=false org.apache.derby.tools.ij ij version 10.5 CONNECTION0* * = current connection ij> select * from t1; C1 ----------- 1 2 3 3 rows selected ij>
ij> Connect 'jdbc:derby:anyDB;shutdown=rue'; ERROR XJ05B: JDBC attribute 'shutdown' has an invalid value 'rue', valid values are '{true|false}'. ij>
java org.apache.derby.tools.ij ij version 10.5 ij> connect 'mydb;uSer=naomi'; URL Attribute [uSer=naomi] Case of the Derby attribute is incorrect.
java -Dij.URLCheck=false org.apache.derby.tools.ij ij version 10.5 ij> connect 'mydb;uSer=naomi'; ij>
derby.ui.codeset=derbyval
java -Dderby.ui.locale=ja_JP -Dderby.ui.codeset=Cp939 -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
Character Encoding | Explanation |
8859_1 | ISO Latin-1 |
8859_2 | ISO Latin-2 |
8859_7 | ISO Latin/Greek |
Cp1257 | Windows Baltic |
Cp1258 | Windows Vietnamese |
Cp437 | PC Original |
EUCJIS | Japanese EUC |
GB2312 | GB2312-80 Simplified Chinese |
JIS | JIS |
KSC5601 | KSC5601 Korean |
MacCroatian | Macintosh Croatian |
MacCyrillic | Macintosh Cyrillic |
SJIS | PC and Windows Japanese |
UTF8 | Standard UTF-8 |
C:\> REM This example is from a DOS prompt: C:\> java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij ij version 10.5 ij> connect 'menuDB;create=true'; ij> CREATE TABLE menu(course CHAR(10), item CHAR(20), price INTEGER); 0 rows inserted/updated/deleted ij> disconnect; ij> exit; C:\>
ij> INSERT INTO menu VALUES ('appetizer','baby greens',7), ('entree','lamb chops ',6),('dessert','creme brulee',14); 3 rows inserted/updated/deleted ij> SELECT * FROM menu; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 dessert |creme brulee |6 appetizer |baby greens |7 3 rows selected ij>
ij> autocommit off; ij> DROP TABLE menu; 0 rows inserted/updated/deleted ij> CREATE TABLE menu (course CHAR(10), item CHAR(20), price INT); 0 rows inserted/updated/deleted ij> INSERT INTO menu VALUES ('entree', 'lamb chop', 14), ('dessert', 'creme brulee', 6), ('appetizer', 'baby greens', 7); 3 rows inserted/updated/deleted ij> commit; ij> autocommit on; ij>
ij> get cursor menuCursor as 'SELECT * FROM menu'; ij> next menuCursor; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 ij> next menuCursor; COURSE |ITEM |PRICE ----------------------------------------------- dessert |creme brulee |6 ij> close menuCursor; ij>
CONNECT ConnectionURLString [ PROTOCOL Identifier ] [ AS Identifier ] [ USER String PASSWORD String ]
ij> connect 'jdbc:derby:menuDB;create=true'; ij> -- we create a new table in menuDB: CREATE TABLE menu(course CHAR(10), item CHAR(20), price INTEGER); ij> protocol 'jdbc:derby:'; ij> connect 'sample' as sample1; ij(SAMPLE1)> connect 'newDB;create=true' as newDB; ij(NEWDB)> show connections; CONNECTION0 - jdbc:derby:menuDB NEWDB* - jdbc:derby:anotherDB SAMPLE1 - jdbc:derby:newDB ij> ij> connect 'jdbc:derby:sample' user 'sa' password 'cloud3x9'; ij>
ij> describe airlines; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ AIRLINE |CHAR |NULL|NULL|2 |NULL |4 |NO AIRLINE_FULL |VARCHAR |NULL|NULL|24 |NULL |48 |YES BASIC_RATE |DOUBLE |NULL|2 |52 |NULL |NULL |YES DISTANCE_DISCOUNT |DOUBLE |NULL|2 |52 |NULL |NULL |YES BUSINESS_LEVEL_FACT&|DOUBLE |NULL|2 |52 |NULL |NULL |YES FIRSTCLASS_LEVEL_FA&|DOUBLE |NULL|2 |52 |NULL |NULL |YES ECONOMY_SEATS |INTEGER |0 |10 |10 |NULL |NULL |YES BUSINESS_SEATS |INTEGER |0 |10 |10 |NULL |NULL |YES FIRSTCLASS_SEATS |INTEGER |0 |10 |10 |NULL |NULL |YES
-- describe a table in another schema: describe user2.flights; -- describe a table whose name is in mixed-case: describe 'EmployeeTable'; -- describe a table in a different schema, with a case-sensitive name: describe 'MyUser.Orders'; -- describe all the columns from all the tables and views in APP schema: describe 'APP.*'; -- describe all the columns in the current schema: describe '*';
ij> connect 'jdbc:derby:menuDB;create=true'; ij> -- we create a new table in menuDB: CREATE TABLE menu(course CHAR(10), ITEM char(20), PRICE integer); 0 rows inserted/updated/deleted ij> disconnect; ij> protocol 'jdbc:derby:'; ij> connect 'sample' as sample1; ij> connect 'newDB;create=true' as newDB; SAMPLE1 - jdbc:derby:sample NEWDB* - jdbc:derby:newDB;create=true * = current connection ij(NEWDB)> set connection sample1; ij> disconnect sample1; ij> disconnect all; ij>
• | ||
• | To execute a named command identified by PreparedStatementIdentifier.
This must be previously prepared with the ijPrepare command. | |
• | To execute either flavor of command when that command contains dynamic
parameters, specify the values in the Using portion of the command. In this
style, the SQLString or previously prepared PreparedStatementIdentifier is
executed using the values supplied as String or Identifier.
The Identifier in the USING clause must have a result set as its result.
Each row of the result set is applied to the input parameters of the command
to be executed, so the number of columns in the Using's result set must match
the number of input parameters in the Execute's
statement. The results of each execution of the Execute statement
are displayed as they are made. If the Using's result set contains no rows,
the Execute's
statement is not executed. When auto-commit mode is on, the Using's result
set is closed upon the first execution of the Execute statement.
To ensure multiple-row execution of the Execute command,
use the Autocommit command
to turn auto-commit off. |
GET [WITH {HOLD|NOHOLD}] CURSOR Identifier AS String
ij> -- autocommit needs to be off so that the positioned update ij> -- can see the cursor it operates against. ij> autocommit off; ij> get cursor menuCursor as 'SELECT * FROM menu FOR UPDATE OF price'; ij> next menuCursor; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 ij> next menuCursor; COURSE |ITEM |PRICE ----------------------------------------------- dessert |creme brulee |6 ij> UPDATE menu SET price=price+1 WHERE CURRENT OF menuCursor; 1 row inserted/updated/deleted ij> next menuCursor; COURSE |ITEM |PRICE ----------------------------------------------- appetizer |baby greens salad |7 ij> close menuCursor; ij> commit; ij>
GET SCROLL INSENSITIVE [WITH {HOLD|NOHOLD}] CURSOR Identifier AS String
ij> autocommit off; ij> get scroll insensitive cursor scrollCursor as 'SELECT * FROM menu'; ij> absolute 5 scrollCursor; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 ij> after last scrollcursor; No current row ij> before first scrollcursor; No current row ij> first scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 ij> last scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- dessert |creme brulee |6 ij> previous scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 ij> relative 1 scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- dessert |creme brulee |6 ij>>previous scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- dessert |creme brulee |6 ij> next scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- dessert |creme brulee |6
ij> get scroll insensitive cursor scrollCursor as 'SELECT * FROM menu FOR UPDATE OF price'; ij> absolute 5 scrollCursor; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 ij> last scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- dessert |creme brulee |6
ij> get scroll insensitive cursor scrollCursor as 'SELECT * FROM menu FOR UPDATE OF price'; ij> last scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- dessert |creme brulee |6 ij> previous scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14
ij> connect 'jdbc:derby:menuDB'; ij> readonly on; ij> SELECT * FROM menu; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 dessert |creme brulee |6 appetizer |baby greens |7 entree |lamb chop |14 entree |lamb chop |14 dessert |creme brulee |6 6 rows selected ij> UPDATE menu set price = 3; ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
ij> -- autocommit needs to be off so that the positioned update ij> -- can see the cursor it operates against. ij> autocommit off; ij> get scroll insensitive cursor scrollCursor as 'SELECT * FROM menu FOR UPDATE OF price'; ij> last scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- dessert |creme brulee |6 ij> previous scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 ij> relative 1 scrollcursor; COURSE |ITEM |PRICE ----------------------------------------------- dessert |creme brulee |6
ij> prepare seeMenu as 'SELECT * FROM menu'; ij> execute seeMenu; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 dessert |creme brulee |6 2 rows selected ij> remove seeMenu; ij> execute seeMenu; IJ ERROR: Unable to establish prepared statement SEEMENU ij>
ij> autocommit off; ij> INSERT INTO menu VALUES ('dessert', 'rhubarb pie', 4); 1 row inserted/updated/deleted ij> SELECT * from menu; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 dessert |creme brulee |7 appetizer |baby greens |7 dessert |rhubarb pie |4 4 rows selected ij> rollback; ij> SELECT * FROM menu; COURSE |ITEM |PRICE ----------------------------------------------- entree |lamb chop |14 dessert |creme brulee |7 appetizer |baby greens |7 3 rows selected ij>
ij> run 'setupMenuConn.ij'; ij> -- this is setupMenuConn.ij -- ij displays its contents as it processes file ij> connect 'jdbc:derby:menuDB'; ij> autocommit off; ij> -- this is the end of setupMenuConn.ij -- there is now a connection to menuDB and no autocommit. -- input will now resume from the previous source. ; ij>
SHOW { CONNECTIONS | INDEXES [ IN schemaName | FROM table-Name ] | PROCEDURES [ IN schemaName ] | ROLES | ENABLED_ROLES | SETTABLE_ROLES | SCHEMAS | SYNONYMS [ IN schemaName ] | TABLES [ IN schemaName ] | VIEWS [ IN schemaName ] | }
ij> connect 'sample' as sample1; ij> connect 'newDB;create=true' as newDB; ij(NEWDB)> show connections; SAMPLE1 - jdbc:derby:sample NEWDB* - jdbc:derby:newDB;create=true * = current connection ij(NEWDB)>
ij> show indexes in app; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- AIRLINES |AIRLINE |false |3 |A |NULL |NULL COUNTRIES |COUNTRY_ISO_CODE |false |3 |A |NULL |NULL COUNTRIES |COUNTRY |false |3 |A |NULL |NULL CITIES |CITY_ID |false |3 |A |NULL |NULL FLIGHTS |FLIGHT_ID |false |3 |A |NULL |NULL FLIGHTS |SEGMENT_NUMBER |false |3 |A |NULL |NULL FLIGHTAVAILABILITY |FLIGHT_ID |false |3 |A |NULL |NULL FLIGHTAVAILABILITY |SEGMENT_NUMBER |false |3 |A |NULL |NULL FLIGHTAVAILABILITY |FLIGHT_DATE |false |3 |A |NULL |NULL MAPS |MAP_ID |false |3 |A |NULL |NULL MAPS |MAP_NAME |false |3 |A |NULL |NULL FLIGHTS |DEST_AIRPORT |true |3 |A |NULL |NULL FLIGHTS |ORIG_AIRPORT |true |3 |A |NULL |NULL CITIES |COUNTRY_ISO_CODE |true |3 |A |NULL |NULL FLIGHTAVAILABILITY |FLIGHT_ID |true |3 |A |NULL |NULL FLIGHTAVAILABILITY |SEGMENT_NUMBER |true |3 |A |NULL |NULL 16 rows selected ij> show indexes from flights; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- FLIGHTS |FLIGHT_ID |false |3 |A |NULL |NULL FLIGHTS |SEGMENT_NUMBER |false |3 |A |NULL |NULL FLIGHTS |DEST_AIRPORT |true |3 |A |NULL |NULL FLIGHTS |ORIG_AIRPORT |true |3 |A |NULL |NULL 4 rows selected
ij> show procedures in syscs_util; PROCEDURE_SCHEM |PROCEDURE_NAME |REMARKS ------------------------------------------------------------------------ SYSCS_UTIL |SYSCS_BACKUP_DATABASE |org.apache.derby.ca& SYSCS_UTIL |SYSCS_BACKUP_DATABASE_AND_ENA&|org.apache.derby.ca& SYSCS_UTIL |SYSCS_BACKUP_DATABASE_AND_ENA&|org.apache.derby.ca& SYSCS_UTIL |SYSCS_BACKUP_DATABASE_NOWAIT |org.apache.derby.ca& SYSCS_UTIL |SYSCS_BULK_INSERT |org.apache.derby.ca& SYSCS_UTIL |SYSCS_CHECKPOINT_DATABASE |org.apache.derby.ca& SYSCS_UTIL |SYSCS_COMPRESS_TABLE |org.apache.derby.ca& SYSCS_UTIL |SYSCS_DISABLE_LOG_ARCHIVE_MODE|org.apache.derby.ca& SYSCS_UTIL |SYSCS_EXPORT_QUERY |org.apache.derby.ca& SYSCS_UTIL |SYSCS_EXPORT_TABLE |org.apache.derby.ca& SYSCS_UTIL |SYSCS_FREEZE_DATABASE |org.apache.derby.ca& SYSCS_UTIL |SYSCS_IMPORT_DATA |org.apache.derby.ca& SYSCS_UTIL |SYSCS_IMPORT_TABLE |org.apache.derby.ca& SYSCS_UTIL |SYSCS_INPLACE_COMPRESS_TABLE |org.apache.derby.ca& SYSCS_UTIL |SYSCS_SET_DATABASE_PROPERTY |org.apache.derby.ca& SYSCS_UTIL |SYSCS_SET_RUNTIMESTATISTICS |org.apache.derby.ca& SYSCS_UTIL |SYSCS_SET_STATISTICS_TIMING |org.apache.derby.ca& SYSCS_UTIL |SYSCS_UNFREEZE_DATABASE |org.apache.derby.ca& 18 rows selected
ij> show roles; ROLEID ------------------------------ ANYUSER CASUALUSER POWERUSER 3 rows selected ij> show enabled_roles; ROLEID ------------------------------ ANYUSER CASUALUSER 2 rows selected ij> show settable_roles; ROLEID ------------------------------ CASUALUSER POWERUSER 2 rows selected
ij> show schemas; TABLE_SCHEM ------------------------------ APP NULLID SQLJ SYS SYSCAT SYSCS_DIAG SYSCS_UTIL SYSFUN SYSIBM SYSPROC SYSSTAT 11 rows selected
ij> show synonyms; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ APP |MYAIRLINES |
ij> show tables; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ APP |AIRLINES | APP |CITIES | APP |COUNTRIES | APP |FLIGHTAVAILABILITY | APP |FLIGHTS | APP |FLIGHTS_HISTORY | APP |MAPS | 7 rows selected
ij> show views; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ APP |TOTALSEATS | 1 row selected
/* start the file with a /* nested comment */ and see what happens */ connect 'jdbc:derby:newdb;create=true'; values 'hi!'; create table t (x int); /* use a multi-line comment */ /* insert into t values 1, 2, 3; insert into t values 4, 5, 6; */ /* end the file with a comment */ values 'This is a test'; /* This is also a test */
ij> -- this is a comment; -- the semicolons in the comment are not taken as the end -- of the command; for that, we put it outside the --: ; ij>
ij> run 'comment.sql'; ij> /* start the file with a /* nested comment */ and see what happens */ connect 'jdbc:derby:newdb;create=true'; ij> values 'hi!'; 1 --- hi! 1 row selected ij> create table t (x int); 0 rows inserted/updated/deleted ij> /* use a multi-line comment */ /* insert into t values 1, 2, 3; insert into t values 4, 5, 6; */ /* end the file with a comment */ values 'This is a test'; 1 -------------- This is a test 1 row selected ij> /* This is also a test */ ; ij>
• | Use the export system procedures to write data from a database to one
or more files that are stored outside of the database. You can use a procedure
to export data from a table into a file or export data from a SELECT statement
result into a file. | |
• | Use the import system procedures to import data from a file into a table.
If the target table already contains data, you can replace or append to the
existing data. |
• | The CLOB data does not have to be scanned for the delimiters inside the
data | |
• | The BLOB data does not need to be converted into a hexadecimal format |
• | Offset is the position in the external file in bytes | |
• | length is the size of the LOB column data in bytes |
• | Rows are separated by a new line | |
• | Fields are separated by a comma (,) | |
• | Character-based fields are delimited with double quotes (") |
• | Delimiters are mutually exclusive | |
• | A delimiter cannot be a line-feed character, a carriage return, or a blank
space | |
• | The default decimal point (.) cannot be a character delimiter | |
• | Delimiters cannot be hex decimal characters (0-9, a-f, A-F). |
The preceding character string gets imported into the database as:"what a ""great"" day!"
What a "great" day!
The preceding character string gets exported to a file as:"The boot has a 3" heel."
"The boot has a 3""heel."
1,abc,22,def 22,,,"a is a zero-length string, b is null" 13,"hello",454,"world" 4,b and c are both null,,
1,"abc",22,"def" 22,,,"a is a zero-length string, b is null" 13,"hello",454,"world" 4,"b and c are both null",,
1.
| Choose the correct procedure for the type of import that you want
to perform. For examples of these procedures, see Examples of bulk import and export.
|
CREATE TABLE tab1 (c1 CHAR(30), c2 INT GENERATED ALWAYS AS IDENTITY, c3 REAL, c4 CHAR(1))
• | Suppose that you want to import data into tab1 from a
file myfile.del that does not have identity column information.
The myfile.del file contains three fields with the following
data: To import the data, you must explicitly list the column names in the tab1 table except for the identity column c2 when you call the procedure. For example:
| |
• | Suppose that you want import data into tab1 from a file empfile.del that
also has identity column information. The file contains three fields with
the following data: To import the data, you must explicitly specify an insert column list without the identity column c2 and specify the column indexes without identity column data when you call the procedure. For example:
|
1.
| Choose the correct procedure for the type of export that you want
to perform. For examples of these procedures, see Examples of bulk import and export.
|
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'STAFF','myfile.del',null,null,null,0);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'STAFF','c:\output\myfile.del',';','%',null,0);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE( null,'STAFF','c:\data\staff.del',',','"','UTF-8',0);
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE (null, 'STAFF', 'NAME,DEPT,SALARY,PICTURE', '2,3,4,6', 'c:\data\staff.del', ',','"','UTF-8', 0);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (null,'STAFF','myfile.del',null,null,null);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (null,'STAFF','c:\output\myfile.del',';','%',null);
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE(null,'STAFF' 'c:\data\staff.del',',','"','UTF-8', 'c:\data\pictures.dat');
CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY ('SELECT * FROM STAFF WHERE dept=20', 'c:\output\awards.del',null,null,null);
CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE( 'SELECT * FROM STAFF WHERE dept=20', 'c:\data\staff.del', ',' ,'"', 'UTF-8','c:\data\pictures.dat');
PreparedStatement ps=conn.prepareStatement( "CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (?,?,?,?,?,?)"); ps.setString(1,null); ps.setString(2,"STAFF"); ps.setString(3,"staff.dat"); ps.setString(4,"%"); ps.setString(5,null); ps.setString(6,null); ps.execute();
7,95,,Happy Birthday
Character Encoding | Explanation |
8859_1 | ISO Latin-1 |
8859_2 | ISO Latin-2 |
8859_7 | ISO Latin/Greek |
Cp1257 | Windows Baltic |
Cp1258 | Windows Vietnamese |
Cp437 | PC Original |
EUCJIS | Japanese EUC |
GB2312 | GB2312-80 Simplified Chinese |
JIS | JIS |
KSC5601 | KSC5601 Korean |
MacCroatian | Macintosh Croatian |
MacCyrillic | Macintosh Cyrillic |
SJIS | PC and Windows Japanese |
UTF-8 | Standard UTF-8 |
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (NULL,'STAFF','staff.dat',NULL,NULL,'UTF-8')
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (NULL,'STAFF','staff.dat',NULL,NULL,'UTF-8',0)
• | ||
• | jarFilePath The path and physical name of the
jar file to add or use as a replacement. For example: d:/todays_build/tours.jar | |
• | qualifiedJarName The Derby name
of the jar file, qualified by the schema name. Two examples:
|
• | If you are relatively new to the Java programming language, follow the
instructions in "Setting up your environment" in
Getting Started with Java DB to set the
DERBY_HOME
and JAVA_HOME environment variables and to add
DERBY_HOME/bin
to your path. Then use the following command:
| |
• | If you are a regular Java user but are new to Derby, set the
DERBY_HOME
environment variable, then use a java command to invoke the
derbyrun.jar file:
| |
• | If you are familiar with both the Java programming language and Derby,
you have already set
DERBY_HOME.
Set your classpath to include the
Derby
jar files. Then use a java command to invoke the
sysinfo class directly.
|
java -jar C:\db-derby-10.5.1.1-bin\lib\derbyrun.jar sysinfo ------------------ Java Information ------------------ Java Version: 1.6.0_13 Java Vendor: Sun Microsystems Inc. Java home: C:\jdk1.6.0_13\jre Java classpath: C:\db-derby-10.5.1.1-bin\lib\derbyrun.jar OS name: Windows XP OS architecture: x86 OS version: 5.1 Java user name: user1 Java user home: C:\Documents and Settings\user1 Java user dir: C:\DERBYDBS java.specification.name: Java Platform API Specification java.specification.version: 1.6 --------- Derby Information -------- JRE - JDBC: Java SE 6 - JDBC 4.0 [C:\db-derby-10.5.1.1-bin\lib\derby.jar] 10.5.1.1 - (764942) [C:\db-derby-10.5.1.1-bin\lib\derbytools.jar] 10.5.1.1 - (764942) [C:\db-derby-10.5.1.1-bin\lib\derbynet.jar] 10.5.1.1 - (764942) [C:\db-derby-10.5.1.1-bin\lib\derbyclient.jar] 10.5.1.1 - (764942) ------------------------------------------------------ ----------------- Locale Information ----------------- Current Locale : [English/United States [en_US]] Found support for locale: [cs] version: 10.5.1.1 - (764942) Found support for locale: [de_DE] version: 10.5.1.1 - (764942) Found support for locale: [es] version: 10.5.1.1 - (764942) Found support for locale: [fr] version: 10.5.1.1 - (764942) Found support for locale: [hu] version: 10.5.1.1 - (764942) Found support for locale: [it] version: 10.5.1.1 - (764942) Found support for locale: [ja_JP] version: 10.5.1.1 - (764942) Found support for locale: [ko_KR] version: 10.5.1.1 - (764942) Found support for locale: [pl] version: 10.5.1.1 - (764942) Found support for locale: [pt_BR] version: 10.5.1.1 - (764942) Found support for locale: [ru] version: 10.5.1.1 - (764942) Found support for locale: [zh_CN] version: 10.5.1.1 - (764942) Found support for locale: [zh_TW] version: 10.5.1.1 - (764942) ------------------------------------------------------
java org.apache.derby.tools.sysinfo -cp [ [ embedded ][ server ][ client] [ tools ] [ anyClass.class ] ]
• | embedded | |
• | server | |
• | client | |
• | tools | |
• | classname.class |
$ java org.apache.derby.tools.sysinfo -cp embedded SimpleApp.class FOUND IN CLASS PATH: Derby embedded engine library (derby.jar) NOT FOUND IN CLASS PATH: user-specified class (SimpleApp) (SimpleApp not found.)
• | If you are relatively new to the Java programming language, follow the
instructions in "Setting up your environment" in
Getting Started with Java DB to set the
DERBY_HOME
and JAVA_HOME environment variables and to add
DERBY_HOME/bin
to your path. Then use the following command:
| |
• | If you are a regular Java user but are new to Derby, set the
DERBY_HOME
environment variable, then use a java command to invoke the
derbyrun.jar file (all on one line):
| |
• | If you are familiar with both the Java programming language and Derby,
you have already set
DERBY_HOME.
Set your classpath to include the
Derby
jar files. Then use a java command to invoke the
dblook class directly.
|
dblook -d connectionURL [options]
'jdbc:derby://localhost:1527/"C:\private\tmp\myDB";user=someusr;password=somepwd'
• | Checks | |
• | Functions | |
• | Indexes | |
• | Jar files | |
• | Keys (primary, foreign, and unique) | |
• | Schemas | |
• | Stored procedures | |
• | Triggers | |
• | Tables | |
• | Views | |
• | Roles |
java org.apache.derby.tools.dblook -d jdbc:derby:sample
java org.apache.derby.tools.dblook -d jdbc:derby:sample -verbose
java org.apache.derby.tools.dblook -d jdbc:derby:sample -o myDB_DDL.sql
java org.apache.derby.tools.dblook -d 'jdbc:derby:c:\private\stuff\sample' -o 'C:\temp\newDB.sql'
java org.apache.derby.tools.dblook -d jdbc:derby:sample -z samp
java org.apache.derby.tools.dblook -d 'jdbc:derby://localhost:1527/"C:\temp\sample"; user=someusername;password=somepassword' -z samp
java org.apache.derby.tools.dblook -d jdbc:derby:sample -z samp -t "My Table"
java org.apache.derby.tools.dblook -d jdbc:derby:sample -t "My Table" staff
java org.apache.derby.tools.dblook -d jdbc:derby:sample -o myDB_DDL.sql -td '' -append
java org.apache.derby.tools.SignatureChecker connection-url-to-database
java -jar derbyrun.jar SignatureChecker "jdbc:derby:myDB"
java org.apache.derby.tools.SignatureChecker database-name
Found a matching method for: "APP"."DOINSERT"( ) Found a matching method for: "APP"."DOINSERTANDCOMMIT"( ) Found a matching method for: "APP"."APPENDFOOANDBAR"( VARCHAR ) Unresolvable routine: "APP"."IDONTEXIST"( VARCHAR , INTEGER ). Detailed reason: No method was found that matched the method call z.iDontExist(java.lang.String, int), tried all combinations of object and primitive types and any possible type conversion for any parameters the method call may have. The method might exist but it is not public and/or static, or the parameter types are not method invocation convertible. Found a matching method for: "APP"."RUNDDL"( VARCHAR ) Unresolvable routine: "APP"."TABFUNCDOESNTEXIST"( VARCHAR , BIGINT ). Detailed reason: No method was found that matched the method call org.apache.derbyTesting.functionTests.tests.lang.TableFunctionTest. appendFooAndBar(java.lang.String, long), tried all combinations of object and primitive types and any possible type conversion for any parameters the method call may have. The method might exist but it is not public and/or static, or the parameter types are not method invocation convertible.
• | Method: Change the signature of your Java method to match the
signature suggested by the SignatureChecker tool. | |
• | Routine: Drop and recreate your function or procedure so that its
arguments and return type match your Java method according to the SQL Standard
rules described in "Argument matching" in the
Java DB Reference Manual. |