procedure.out

derby database source code.good for you.

testNegative
create procedure asdf() language java external name 'asdfasdf' parameter style java
EXPECTED SQL Exception: (42Y04) Cannot create a procedure or function with EXTERNAL NAME 'asdfasdf' because it is not a list separated by periods. The expected format is <full java path>.<method name>.
create procedure asdf() language java external name 'asdfasdf.' parameter style java
EXPECTED SQL Exception: (42Y04) Cannot create a procedure or function with EXTERNAL NAME 'asdfasdf.' because it is not a list separated by periods. The expected format is <full java path>.<method name>.
create procedure a23456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789() language java external name 'asdf.asdf' parameter style java
EXPECTED SQL Exception: (42622) The name 'A23456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789' is too long. The maximum length is '128'.
create procedure asdf language java external name java.lang.Thread.currentThread parameter style java
EXPECTED SQL Exception: (42X01) Syntax error: Encountered "language" at line 1, column 23.
CREATE PROCEDURE ASSEMBLY_PARTS (IN ASSEMBLY_NUM INTEGER, OUT NUM_PARTS INTEGER, OUT COST DOUBLE) EXTERNAL NAME 'parts!assembly' DYNAMIC RESULT SETS 1 LANGUAGE C PARAMETER STYLE GENERAL
EXPECTED SQL Exception: (42X01) Syntax error: Encountered "C" at line 1, column 161.
create procedure sys.proc1() language java external name 'java.lang.System.gc' parameter style java
EXPECTED SQL Exception: (42X62) 'CREATE PROCEDURE' is not allowed in the 'SYS' schema.
create procedure noclass() language java external name 'asdf.asdf' parameter style java language java
EXPECTED SQL Exception: (42613) Multiple or conflicting keywords involving the 'LANGUAGE' clause are present.
create procedure noclass() parameter style java language java external name 'asdf.asdf' parameter style java
EXPECTED SQL Exception: (42613) Multiple or conflicting keywords involving the 'PARAMETER STYLE' clause are present.
create procedure noclass() external name 'asdf.xxxx' language java external name 'asdf.asdf' parameter style java
EXPECTED SQL Exception: (42613) Multiple or conflicting keywords involving the 'EXTERNAL NAME' clause are present.
create procedure noclass() parameter style java language java external name 'asdf.asdf' parameter style derby_rs_collection
EXPECTED SQL Exception: (42X01) Syntax error: Encountered "derby_rs_collection" at line 1, column 105.
create procedure missing01()
EXPECTED SQL Exception: (42X01) Syntax error: Encountered "<EOF>" at line 1, column 28.
create procedure missing02() language java
EXPECTED SQL Exception: (42X01) Syntax error: MISSING ELEMENT.
create procedure missing03() language java parameter style java
EXPECTED SQL Exception: (42X01) Syntax error: MISSING ELEMENT.
create procedure missing04() language java external name 'foo.bar'
EXPECTED SQL Exception: (42X01) Syntax error: MISSING ELEMENT.
create procedure missing05() parameter style java
EXPECTED SQL Exception: (42X01) Syntax error: MISSING ELEMENT.
create procedure missing06() parameter style java external name 'foo.bar'
EXPECTED SQL Exception: (42X01) Syntax error: MISSING ELEMENT.
create procedure missing07() external name 'goo.bar'
EXPECTED SQL Exception: (42X01) Syntax error: MISSING ELEMENT.
create procedure missing08() dynamic result sets 1
EXPECTED SQL Exception: (42X01) Syntax error: MISSING ELEMENT.
create procedure NO_BLOB(IN P1 BLOB(3k)) language java parameter style java external name 'no.blob'
EXPECTED SQL Exception: (42962) Long column type column or parameter 'P1' not permitted in declared global temporary tables or procedure definitions. 
create procedure NO_CLOB(IN P1 CLOB(3k)) language java parameter style java external name 'no.clob'
EXPECTED SQL Exception: (42962) Long column type column or parameter 'P1' not permitted in declared global temporary tables or procedure definitions. 
create procedure NO_LVC(IN P1 LONG VARCHAR) language java parameter style java external name 'no.lvc'
EXPECTED SQL Exception: (42962) Long column type column or parameter 'P1' not permitted in declared global temporary tables or procedure definitions. 
create procedure DUP_P1(IN FRED INT, OUT RON CHAR(10), IN FRED INT) language java parameter style java external name 'no.dup1'
EXPECTED SQL Exception: (42734) Name 'FRED' specified in context 'DUP_P1' is not unique.
create procedure D2.DUP_P2(IN "FreD" INT, OUT RON CHAR(10), IN "FreD" INT) language java parameter style java external name 'no.dup2'
EXPECTED SQL Exception: (42734) Name 'FreD' specified in context 'D2.DUP_P2' is not unique.
create procedure D3.DUP_P3(IN "FRED" INT, OUT RON CHAR(10), IN fred INT) language java parameter style java external name 'no.dup3'
EXPECTED SQL Exception: (42734) Name 'FRED' specified in context 'D3.DUP_P3' is not unique.
CALL APP.NSP(?, ?)
EXPECTED SQL Exception: (42Y03) 'APP.NSP' is not recognized as a function or procedure.
call syscs_util.syscs_set_database_property("foo", "bar")
EXPECTED SQL Exception: (42X15) Column name 'foo' appears in a statement without a FROM list.
signature mismatched types
CALL APP.SIGNATURE_BUG_DERBY_258_A(4)
EXPECTED SQL Exception: (22005) An attempt was made to get a data value of type 'java.lang.String' from a data value of type 'INTEGER'.
signature too many parameters
VALUES APP.SIGNATURE_BUG_DERBY_258_B(4)
EXPECTED SQL Exception: (46J02) Java method signature contains incorrect number (2) of parameters, expected 1.
signature too few parameters
CALL APP.SIGNATURE_BUG_DERBY_258_C(4)
EXPECTED SQL Exception: (46J02) Java method signature contains incorrect number (0) of parameters, expected 1.
signature invalid format
CALL APP.SIGNATURE_BUG_DERBY_258_F(4)
EXPECTED SQL Exception: (46J01) Java method signature has invalid format.
signature invalid format
CALL APP.SIGNATURE_BUG_DERBY_258_G(4)
EXPECTED SQL Exception: (46J01) Java method signature has invalid format.
signature invalid format
CALL APP.SIGNATURE_BUG_DERBY_258_H(4)
EXPECTED SQL Exception: (46J01) Java method signature has invalid format.
signature invalid format
CALL APP.SIGNATURE_BUG_DERBY_258_I(4)
EXPECTED SQL Exception: (46J01) Java method signature has invalid format.
testDelayedClassChecking
call noclass()
EXPECTED SQL Exception: (42X51) The class 'asdf' does not exist or is inaccessible. This can happen if the class is not public.
call nomethod()
EXPECTED SQL Exception: (42X50) No method was found that matched the method call java.lang.Integer.asdf(), 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.
call notstatic()
EXPECTED SQL Exception: (42X50) No method was found that matched the method call java.lang.Integer.equals(), 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.
call notvoid()
EXPECTED SQL Exception: (42X50) No method was found that matched the method call void java.lang.Runtime.getRuntime(), 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.
testDuplicates
create procedure PROCDUP() language java external name 'failAPP.fail0' parameter style java
EXPECTED SQL Exception: (X0Y68) PROCEDURE 'PROCDUP' already exists.
create procedure s1.PROCDUP() language java external name 'fails1.fail0' parameter style java
EXPECTED SQL Exception: (X0Y68) PROCEDURE 'PROCDUP' already exists.
create procedure s2.PROCDUP() language java external name 'fails2.fail0' parameter style java
EXPECTED SQL Exception: (X0Y68) PROCEDURE 'PROCDUP' already exists.
DEFINED PROCEDURES FOR PROCDUP
  APP.PROCDUP AS okAPP.ok0() LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA
  S1.PROCDUP AS oks1.ok0() LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA
  S2.PROCDUP AS oks2.ok0() LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA
DATABASE METATDATA PROCEDURES FOR PROCDUP
  APP.PROCDUP AS okAPP.ok0 type procedureNoResult
  S1.PROCDUP AS oks1.ok0 type procedureNoResult
  S2.PROCDUP AS oks2.ok0 type procedureNoResult
------------
create procedure S1.NOTYET() SPECIFIC fred language java external name 'failAPP.fail0' parameter style java
EXPECTED SQL Exception: (0A000) Feature not implemented: SPECIFIC identifier.
ambigiousMethods
call AMBIGIOUS01(?, ?)
EXPECTED SQL Exception: (42X73) Method resolution for signature org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious1(int, java.lang.String, [Ljava.sql.ResultSet;) was ambiguous. (No single maximally specific method.)
call AMBIGIOUS02(?, ?)
EXPECTED SQL Exception: (42X50) No method was found that matched the method call org.apache.derbyTesting.functionTests.util.ProcedureTest.ambigious2(int, int, java.sql.ResultSet[]), 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.
ambigious2(int,Integer) called
UPDATE COUNT 0
ambigious2(Integer,int) called
UPDATE COUNT 0
zeroArgProcedures
zeroArg() called
UPDATE COUNT 0
zeroArg() called
UPDATE COUNT 0
zeroArg() called
UPDATE COUNT 0
EXPECTED SQL Exception: 'ZA' is not recognized as a function or procedure.
zeroArg() called
UPDATE COUNT 0
zeroArg() called
UPDATE COUNT 0
DEFINED PROCEDURES FOR ZA
  APP.ZA AS org.apache.derbyTesting.functionTests.util.ProcedureTest.zeroArg() LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA
DATABASE METATDATA PROCEDURES FOR ZA
  APP.ZA AS org.apache.derbyTesting.functionTests.util.ProcedureTest.zeroArg type procedureNoResult
------------
DEFINED PROCEDURES FOR ZA
DATABASE METATDATA PROCEDURES FOR ZA
------------
sqlProcedures()
DEFINED PROCEDURES FOR IR%
  APP.IR AS org.apache.derbyTesting.functionTests.util.ProcedureTest.insertRow(IN P1 INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA
  APP.IR2 AS org.apache.derbyTesting.functionTests.util.ProcedureTest.insertRow(IN P1 INTEGER,IN P2 CHAR(10)) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DATA
DATABASE METATDATA PROCEDURES FOR IR%
  APP.IR AS org.apache.derbyTesting.functionTests.util.ProcedureTest.insertRow type procedureNoResult
    procedureColumnIn P1 INTEGER
  APP.IR2 AS org.apache.derbyTesting.functionTests.util.ProcedureTest.insertRow type procedureNoResult
    procedureColumnIn P1 INTEGER
    procedureColumnIn P2 CHAR
------------
CALL IR()
EXPECTED SQL Exception: (42Y03) 'IR' is not recognized as a function or procedure.
UPDATE COUNT 0
UPDATE COUNT 0
EXPECTED SQL Exception: The exception 'SQL Exception: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'T1'.' was thrown while evaluating an expression.
UPDATE COUNT 0
UPDATE COUNT 0
UPDATE COUNT 0
UPDATE COUNT 0
UPDATE COUNT 0
I          |B              
---------------------------
1          |int            
2          |int            
3          |int            
7          |int            
4          |4              
5          |ir2            
6          |'012345678     
CALL IR2(2, 'no way')
EXPECTED SQL Exception: (38000) The exception 'SQL Exception: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'T1'.' was thrown while evaluating an expression.
CALL IR2(?, 'no way')
EXPECTED SQL Exception: (07000) At least one parameter to the current statement is uninitialized.
CALL IR2(2, ?)
EXPECTED SQL Exception: (07000) At least one parameter to the current statement is uninitialized.
dynamicResultSets - parameter style JAVA
create procedure DRS(p1 int) parameter style JAVA READS SQL DATA dynamic result sets -1 language java external name 'org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows'
EXPECTED SQL Exception: (42X01) Syntax error: Encountered "-" at line 1, column 86.
DEFINED PROCEDURES FOR DRS
  APP.DRS AS org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows(IN P1 INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA DYNAMIC RESULT SETS 1
DATABASE METATDATA PROCEDURES FOR DRS
  APP.DRS AS org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows type procedureNoResult
    procedureColumnIn P1 INTEGER
------------
CALL DRS()
EXPECTED SQL Exception: (42Y03) 'DRS' is not recognized as a function or procedure.
CALL DRS(?,?)
EXPECTED SQL Exception: (42Y03) 'DRS' is not recognized as a function or procedure.
selectRows - 1 arg - 1 rs
I          |B              
---------------------------
3          |int            
DEFINED PROCEDURES FOR DRS2
  APP.DRS2 AS org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows(IN P1 INTEGER,IN P2 INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA DYNAMIC RESULT SETS 2
DATABASE METATDATA PROCEDURES FOR DRS2
  APP.DRS2 AS org.apache.derbyTesting.functionTests.util.ProcedureTest.selectRows type procedureNoResult
    procedureColumnIn P1 INTEGER
    procedureColumnIn P2 INTEGER
------------
selectRows - 2 arg - 2 rs
I          |B              
---------------------------
2          |int            
I          |B              
---------------------------
6          |'012345678     
7          |int            
selectRows - 2 arg - 2 rs
I          |B              
---------------------------
2          |int            
selectRows - 2 arg - 2 rs
UPDATE COUNT 0
selectRows - 2 arg - 2 rs
I          |B              
---------------------------
2          |int            
I          |B              
---------------------------
selectRows - 2 arg - 2 rs
pass 1 got result set true
EXPECTED : ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
pass 2 got result set true
EXPECTED : ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
pass 3 got result set false
auto commit is true
lock count before execution 0
selectRows - 2 arg - 2 rs
lock count after execution 1
lock count after next on first rs 3
Is there a second result ? true
lock count after first getMoreResults() 2
lock count after next on second rs 7
more results (should be false) false
lock count after second getMoreResults() 0
auto commit is false
lock count before execution 0
selectRows - 2 arg - 2 rs
lock count after execution 1
lock count after next on first rs 3
Is there a second result ? true
lock count after first getMoreResults() 2
lock count after next on second rs 7
more results (should be false) false
lock count after second getMoreResults() 7
auto commit is true
lock count before execution 0
selectRows - 2 arg - 2 rs
lock count after execution 1
lock count after next on first rs 3
executing statement to force auto commit on open CALL statement
lock count after statement execution 0
Expected - ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
Is there a second result ? true
lock count after first getMoreResults() 0
Expected - ResultSet not open. Operation 'next' not permitted. Verify that autocommit is OFF.
lock count after next on second rs 0
more results (should be false) false
lock count after second getMoreResults() 0
selectRows - 2 arg - 2 rs
I          |B              
---------------------------
2          |int