JavaDB Usage

JavaDB Usage
1. set java_home and derby_home and path environment.
2. run ij or java -jar %DERBY_HOME%\lib\derbyrun.jar ij
3. Create the database and open a connection to the database using the embedded
driver.
CONNECT 'jdbc:derby:firstdb;create=true';
;create=true
The Derby URL attribute that is used to create a database. Derby does not
have an SQL create database command.
4. 
Create a table with two columns using standard SQL.
CREATE TABLE FIRSTTABLE
(ID INT PRIMARY KEY,
NAME VARCHAR(12));
0 rows inserted/updated/deleted

INSERT INTO FIRSTTABLE VALUES
(10,'TEN'),(20,'TWENTY'),(30,'THIRTY');
3 rows inserted/updated/deleted

SELECT * FROM FIRSTTABLE
WHERE ID=20;
ID |NAME
------------------------
20 |TWENTY

5.
Load the SQL script ToursDB_schema.sql.
run 'ToursDB_schema.sql';

ij> show connections;
ij> connect 'jdbc:derby:sample';
ij> connect 'jdbc:derby://localhost:1527/sample';

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>
ij> commit;
ij> elapsedtime on;
ij> VALUES current_date;
1
----------
1998-07-15
ELAPSED TIME = 2134 milliseconds
ij> disconnect;
ij>help;

ij> protocol 'jdbc:derby:';
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)> set connection sample1;
ij(SAMPLE1)> disconnect all;
ij>

6.
Exit the ij tool.
exit;

7. 
C:\JavaDB\DERBYTUTOR>dblook -d jdbc:derby:firstdb -o myDB_DDL.sql
ij> connect 'jdbc:derby:seconddb';
ij> run 'myDB_DDL.sql';
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (null,'FIRSTTABLE','myfile.del',null,null,null);
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE(null,'FIRSTTABLE','myfile.del',null,null,null,1);

A non-zero value for the replace parameter will import in REPLACE mode, while
a zero value will import in INSERT mode. REPLACE mode deletes all existing
data from the table by truncating the table and inserts the imported data.

Import and Export
Example importing all data from a file
The following example shows how to import data into the STAFF table in a sample
database from the myfile.del file. The data will be appended to the existing data in the
table.
CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE
(null,'STAFF','myfile.del',null,null,null,0);

Example exporting all data from a table to a single export file
The following example shows how to export data from the STAFF table in a sample
database to the file myfile.del.
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE
(null,'STAFF','myfile.del',null,null,null);