http://www.apache.org/licenses/LICENSE-2.0
• | ij | |
• | the import and export utilities | |
• | the database class loading utilities | |
• | sysinfo | |
• | dblook |
• | 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. | |
• | ij commands and errors reference 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 dblook is Derby's Data Definition Language
(DDL) Generation Utility, which is 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 to a file. The generated DDL can then be used to recreate
all or parts of a database, view a subset of a database's objects (for example,
those which pertain to specific tables or schemas), or document a database's
schema. |
• | 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. | |
• | Database class loading utilities These utilities allow you
to store application logic 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, also called a schema dump
tool. It is a simple utility for the dumping the DDL of a user-specified database
to either a console or to 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. |
• | To use ij, you must have derbytools.jar in your
classpath. If you are using the embedded driver, you must also include derby.jar. | |
• | To use the import and export utilities and the database class loading
utilities, you must have derby.jar in your classpath. | |
• | To use sysinfo, either derby.jar or derbytools.jar must
be in your classpath. | |
• | To use Derby tools
from a client with the Derby Network
Server, you must have derbyclient.jar and derbytools.jar in
your classpath. See the Java DB Server and Administration Guide for
more information. |
• | 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
The command line items are: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 command 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 command). 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.
| by using the -D command on the command line | |
2.
| by specifying a properties file using the -p propertyfile option
on the command line |
java org.apache.derby.tools.ij -p ij.properties
java -Dij.maximumDisplayWidth=1000 org.apache.derby.tools.ij
java -Dij.protocol=jdbc:derby: -Dij.database=sample org.apache.derby.tools.ij
To see a list of connection names and the URL's 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 org.apache.derby.tools.ij
ij version 10.2 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.2 ij> connect 'jdbc:derby:sample'; ij>
D:>java org.apache.derby.tools.ij ij version 10.2 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.2 ANOTHERCONN* - jdbc:derby:anotherDB;create=true SAMPLE1 - jdbc:derby:sample * = current connection ij(ANOTHERCONN)>
ij.dataSource=dataSourcename 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. # java -Dij.dataSource=org.apache.derby.jdbc.EmbeddedDataSource -Dij.dataSource.databaseName=sample -Dij.dataSource.createDatabase=create org.apache.derby.tools.ij ij version 10.2 CONNECTION0* * = current connection ij>
• | jdbc:derby: |
ij> Connect 'jdbc:derby:anyDB;create=true;unicode=falj'; ERROR XJ05B: JDBC attribute 'unicode' has an invalid value 'falj', valid values are '{true|false}'. ij>
java org.apache.derby.tools.ij ij version 10.2 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.2 ij> connect 'mydb;uSer=naomi'; ij>
derby.ui.codeset=derbyval
java -Dderby.ui.territory=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.2 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
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 ] | 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 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
• | Use export 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 import 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. |
1.
| To import data from a file to a table, use the SYSCS_UTIL.SYSCS_IMPORT_TABLE
procedure. The procedure definition is:
No Result is returned from the procedure. | |
2.
| To import data from a file to a subset of columns in a table, use the
SYSCS_UTIL.SYSCS_IMPORT_DATA procedure. To import data to subset of columns
in a table, you specify insertColumns on the table
into which data will be imported and/or specify columnIndexes to import data fields from a file to columns in a table. The procedure
definition is:
No result is returned from the procedure. |
1.
| To export all the data from a table to a file, use the SYSCS_UTIL.SYSCS_EXPORT_TABLE
procedure. The procedure definition is:
No Result is returned from the procedure. | |
2.
| To export the result of a SELECT statement to a file, use the SYSCS_UTIL.SYSCS_EXPORT_QUERY
procedure. The procedure definition is:
No result is returned from the procedure. |
CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY ('select * from staff where dept=20', 'c:\output\awards.del',null,null,null);
CREATE TABLE tab1 (c1 CHAR(30), c2 INT GENERATED ALWAYS AS IDENTITY, c3 REAL, c4 CHAR(1))
• | Suppose you want to import data into tab1 from a file myfile.del that
does not have identity column information and myfile.del contains
three fields with the following data: To import data from myfile.del into the tab1 table, explicitly list the column names for tab1 without the identity column c2 and execute the SYSCS_UTIL.SYSCS_IMPORT_DATA procedure as follows:
| |
• | Suppose you want import data into tab1 from a file empfile.del that
also has identity column information and the file contains three fields with
the following data: To import data from empfile.del into the tab1 table, explicitly specify an insert column list without the identity column c2 and specify the column indexes without identity column data and execute the SYSCS_UTIL.SYSCS_IMPORT_DATA procedure as follows:
|
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();
• | 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. |
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",,
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 |
• | ||
• | 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:
|
$ java org.apache.derby.tools.sysinfo ------------------ Java Information ------------------ Java Version: 1.4.2_07 Java Vendor: Sun Microsystems Inc. Java home: c:\p4main\jdk142\jre Java classpath: c:\Derby_10\lib\derby.jar;c:\Derby_10\lib\derbytools.jar; c:\Derby_10\lib\derbyLocale_cs.jar;c:\Derby_10\lib\derbyLocale_de_DE.jar; c:\Derby_10\lib\derbyLocale_es.jar;c:\Derby_10\lib\derbyLocale_fr.jar; c:\Derby_10\lib\derbyLocale_hu.jar;c:\Derby_10\lib\derbyLocale_it.jar; c:\Derby_10\lib\derbyLocale_ja_JP.jar;c:\Derby_10\lib\derbyLocale_ko_KR.jar; c:\Derby_10\lib\derbyLocale_pl.jar;c:\Derby_10\lib\derbyLocale_pt_BR.jar; c:\Derby_10\lib\derbyLocale_ru.jar;c:\Derby_10\lib\derbyLocale_zh_CN.jar; c:\Derby_10\lib\derbyLocale_zh_TW.jar OS name: Windows 2000 OS architecture: x86 OS version: 5.0 Java user name: user1 Java user home: C:\Documents and Settings\myhome Java user dir: C:\Derby_10\test java.specification.name: Java Platform API Specification java.specification.version: 1.4 --------- Derby Information -------- JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 [c:\Derby_10\lib\derby.jar] 10.2.2.0 - (483632) [c:\Derby_10\lib\derbytools.jar] 10.2.2.0 - (483632) ------------------------------------------------------ ----------------- Locale Information ----------------- Current Locale : [English/United States [en_US]] Found support for locale: [cs] version: 10.2.2.0 - (483632) Found support for locale: [de_DE] version: 10.2.2.0 - (483632) Found support for locale: [es] version: 10.2.2.0 - (483632) Found support for locale: [fr] version: 10.2.2.0 - (483632) Found support for locale: [hu] version: 10.2.2.0 - (483632) Found support for locale: [it] version: 10.2.2.0 - (483632) Found support for locale: [ja_JP] version: 10.2.2.0 - (483632) Found support for locale: [ko_KR] version: 10.2.2.0 - (483632) Found support for locale: [pl] version: 10.2.2.0 - (483632) Found support for locale: [pt_BR] version: 10.2.2.0 - (483632) Found support for locale: [ru] version: 10.2.2.0 - (483632) Found support for locale: [zh_CN] version: 10.2.2.0 - (483632) Found support for locale: [zh_TW]
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.)
java org.apache.derby.tools.dblook -d <databaseURL> [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 |
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