1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You 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.
--
-- this test shows the ij commands in use,
-- and what happens when invalid stuff is entered.
-- no driver loaded yet, detected off of the url
-- this one is a bad url:
connect 'cloudscape:wombat';
ERROR 08001: No suitable driver (errorCode = 0)
ij> -- this one will work.
connect 'jdbc:derby:wombat';
ERROR XJ004: Database 'wombat' not found. (errorCode = 40000)
ij> -- no connection yet, this will fail
create table t (i int);
IJ ERROR: Unable to establish connection
ij> -- no table yet, this will fail
select i from t;
IJ ERROR: Unable to establish connection
ij> -- invalid syntax ... incomplete statements
driver;
IJ ERROR: Unable to establish connection
ij> connect;
IJ ERROR: Unable to establish connection
ij> prepare;
IJ ERROR: Unable to establish connection
ij> execute;
IJ ERROR: Unable to establish connection
ij> run;
IJ ERROR: Unable to establish connection
ij> remove;
IJ ERROR: Unable to establish connection
ij> -- should fail because procedure is an illegal statement name
prepare procedure as 'select * from bar';
IJ ERROR: procedure is an illegal name for a statement
ij> -- should fail because text is passed on to derby, which
-- barfs on the unknown statement name. execute procedure is
-- a foundation 2000 concept
execute procedure sqlj.install_jar( 'file:c:/p4c/systest/out/DigIt.jar', 'SourceWUs', 1 );
IJ ERROR: Unable to establish connection
ij> -- moved from errorcode.sql
-- specify an invalid driver
driver 'java.lang.Integer';
IJ ERROR: The class 'java.lang.Integer' does not implement the interface 'java.sql.Driver'.
ij> -- now a valid driver
driver 'org.apache.derby.jdbc.EmbeddedDriver';
ij> -- specify an invalid database
connect 'asdfasdf';
ERROR 08001: No suitable driver (errorCode = 0)
ij> -- now a valid database, but no create
connect 'jdbc:derby:wombat';
ERROR XJ004: Database 'wombat' not found. (errorCode = 40000)
ij> -- and, the help output:
help;
Supported commands include:
PROTOCOL 'JDBC protocol' [ AS ident ];
-- sets a default or named protocol
DRIVER 'class for driver'; -- loads the named class
CONNECT 'url for database' [ PROTOCOL namedProtocol ] [ AS connectionName ];
-- connects to database URL
-- and may assign identifier
SET CONNECTION connectionName; -- switches to the specified connection
SHOW CONNECTIONS; -- lists all connections
AUTOCOMMIT [ ON | OFF ]; -- sets autocommit mode for the connection
DISCONNECT [ CURRENT | connectionName | ALL ];
-- drop current, named, or all connections;
-- the default is CURRENT
SHOW SCHEMAS; -- lists all schemas in the current database
SHOW [ TABLES | VIEWS | PROCEDURES | FUNCTIONS | SYNONYMS ] { IN schema };
-- lists tables, views, procedures, functions or synonyms
SHOW INDEXES { IN schema | FROM table };
-- lists indexes in a schema, or for a table
SHOW ROLES; -- lists all defined roles in the database, sorted
SHOW ENABLED_ROLES; -- lists the enabled roles for the current
-- connection (to see current role use
-- VALUES CURRENT_ROLE), sorted
SHOW SETTABLE_ROLES; -- lists the roles which can be set for the
-- current connection, sorted
DESCRIBE name; -- lists columns in the named table
COMMIT; -- commits the current transaction
ROLLBACK; -- rolls back the current transaction
PREPARE name AS 'SQL-J text'; -- prepares the SQL-J text
EXECUTE { name | 'SQL-J text' } [ USING { name | 'SQL-J text' } ] ;
-- executes the statement with parameter
-- values from the USING result set row
REMOVE name; -- removes the named previously prepared statement
RUN 'filename'; -- run commands from the named file
ELAPSEDTIME [ ON | OFF ]; -- sets elapsed time mode for ij
MAXIMUMDISPLAYWIDTH integerValue;
-- sets the maximum display width for
-- each column to integerValue
ASYNC name 'SQL-J text'; -- run the command in another thread
WAIT FOR name; -- wait for result of ASYNC'd command
HOLDFORCONNECTION; -- sets holdability for a connection to HOLD
-- (i.e. ResultSet.HOLD_CURSORS_OVER_COMMIT)
NOHOLDFORCONNECTION; -- sets holdability for a connection to NO HOLD
-- (i.e. ResultSet.CLOSE_CURSORS_AT_COMMIT)
GET [SCROLL INSENSITIVE] [WITH { HOLD | NOHOLD }] CURSOR name AS 'SQL-J query';
-- gets a cursor (JDBC result set) on the query
-- the default is a forward-only cursor with holdability
NEXT name; -- gets the next row from the named cursor
FIRST name; -- gets the first row from the named scroll cursor
LAST name; -- gets the last row from the named scroll cursor
PREVIOUS name; -- gets the previous row from the named scroll cursor
ABSOLUTE integer name; -- positions the named scroll cursor at the absolute row number
-- (A negative number denotes position from the last row.)
RELATIVE integer name; -- positions the named scroll cursor relative to the current row
-- (integer is number of rows)
AFTER LAST name; -- positions the named scroll cursor after the last row
BEFORE FIRST name; -- positions the named scroll cursor before the first row
GETCURRENTROWNUMBER name; -- returns the row number for the current position of the named scroll cursor
-- (0 is returned when the cursor is not positioned on a row.)
CLOSE name; -- closes the named cursor
LOCALIZEDDISPLAY [ ON | OFF ];
-- controls locale sensitive data representation
EXIT; -- exits ij
HELP; -- shows this message
Any unrecognized commands are treated as potential SQL-J commands and executed directly.
ij>
|
