ejbql.sql

derby database source code.good for you.

-- Prepare Statements, should pass and return 1
prepare p2 as 'select {fn abs(?)} from myint';
prepare p2 as 'select 1 from myint where ? <= 4';
execute p2 using 'values {fn abs( 4 )}';
execute p2 using 'values {fn abs( -4 )}';
execute p2 using 'values {fn abs( 4.4 )}';
execute p2 using 'values {fn abs( -4.4 )}';
execute p2 using 'values {fn abs( -4.44444444444444444444444 )}';
autocommit on;

drop table myint;
-- Using Strings in escape function
create table myStr( a varchar(10) );
insert into myStr values ( '123' );
insert into myStr values ( '-123' );
insert into myStr values ( '-12 ' );
insert into myStr values ( ' -2 ' );
insert into myStr values ( '1a3' );
select * from myStr;
select abs(a) from myStr;
select {fn abs(a)} from myStr;
drop table myStr;
-- End of ABSVAL test

-- This test EJBQL function, CONCAT. Resolve 3535

-- Begin of CONCAT test
-- Basic
values{ fn concat( 'hello', ' world' ) };
VALUES{ FN CONCAT( 'HELLO', ' WORLD' ) };
values{ fn concat( '' , '' )};
values{ fn concat( CHAR(''), CHAR('') ) };
values{ fn concat( 45, 67 )};
values{ fn concat( '45', 67 )};
values{ fn concat( 45, '67' )};
values{ fn concat( CHAR('C'), CHAR('#') ) };
values{ fn concat( 'ABCDEFGHIJKLMNOPQRSTUVWXYZ`1234567890-=\    [];,./ \'' |',
                   'abcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+|<>?:"{}     ''''''      ' ) };
create table concat ( a int );
insert into concat values (1);
select * from CONCAT;

create table myconcat( a varchar(10) default null, b varchar(10) default null, c int);
insert into myconcat (c) values( 1 );
insert into myconcat (c) values( 2 );
insert into myconcat (a) values( 'hello' );
insert into myconcat (b) values( 'world' );
insert into myconcat (a,b) values( 'hello', 'world' );
select * from myconcat;
select { fn concat( a, b ) } from myconcat;
drop table concat;
drop table myconcat;
-- End of CONCAT test

-- This test the EJBQL function, LOCATE. Resolve 3535

-- LOCATE( string1, string2[, start] ) --- string1, searching from the beginning
--   of string2 }; if start is specified, the search begins from position start.
--   0 is returned if string2 does not contain string1.  Position1 is the first
--   character in string2.

-- Begin of LOCATE test
-- Basic
-- 2 args
values{ fn locate( 'hello', 'hello' ) };
values{ fn locate( 'hello', 'hellohello' ) };
values{ fn locate( 'hello', 'helloworld' ) };
values{ fn locate( 'hello', 'h?hello' ) };
values{ fn locate( 'hello', 'match me, hello now!' ) };
values{ fn locate( '?', '?' ) };
values{ fn locate( '\', '\\') };
values{ fn locate( '/', '//') };
values{ fn locate( '\\', '\') };
values{ fn locate( '//', '/') };
values{ fn locate( '', 'test' ) };
values{ fn locate( '', ''     ) };
values{ fn locate( 'test', '' ) };

-- 3 args 
values{ fn locate( 'hello', 'hello',-1 ) };
values{ fn locate( 'hello', 'hello',-0 ) };
values{ fn locate( 'hello', 'hello', 0 ) };
values{ fn locate( 'hello', 'hello', 1 ) };
values{ fn locate( 'hello', 'hello', 2 ) };
values{ fn locate( 'hello', 'hello', 5 ) };
values{ fn locate( 'hello', 'hello', 9 ) };

values{ fn locate( 'hello', 'hellohello', 0 ) };
values{ fn locate( 'hello', 'hellohello', 1 ) };
values{ fn locate( 'hello', 'hellohello', 2 ) };
values{ fn locate( 'hello', 'hellohello', 5 ) };
values{ fn locate( 'hello', 'hellohello', 6 ) };
values{ fn locate( 'hello', 'hellohello', 7 ) };

values{ fn locate( 'hello', 'h?hello', 1 ) };
values{ fn locate( 'hello', 'h?hello', 2 ) };
values{ fn locate( 'hello', 'h?hello', 3 ) };
values{ fn locate( 'hello', 'h?hello', 4 ) };

values{ fn locate( 'hello', 'match me, hello now!',  7 ) };
values{ fn locate( 'hello', 'match me, hello now!', 15 ) };

values{ fn locate( '?', '?',-1 ) };
values{ fn locate( '?', '?',-0 ) };
values{ fn locate( '?', '?', 0 ) };
values{ fn locate( '?', '?', 1 ) };
values{ fn locate( '?', '?', 2 ) };

values{ fn locate( '\', '\\',0) };
values{ fn locate( '\', '\\',1) };
values{ fn locate( '\', '\\',2) };
values{ fn locate( '\', '\\',3) };

values{ fn locate( '/', '//',0) };
values{ fn locate( '/', '//',1) };
values{ fn locate( '/', '//',2) };
values{ fn locate( '/', '//',3) };

values{ fn locate( '\\', '\',1) };
values{ fn locate( '//', '/',1) };

values{ fn locate( '', 'test',1) };
values{ fn locate( '', 'test',2) };
values{ fn locate( '', 'test',3) };
values{ fn locate( '', 'test',4) };
values{ fn locate( '', 'test',5) };
values{ fn locate( '', ''    ,1) };
values{ fn locate( 'test', '',1) };
values{ fn locate( 'test', '',2) };
values{ fn locate( 'test', '',3) };
values{ fn locate( 'test', '',4) };

values{ fn locate( 'hello', 1 ) };
values{ fn locate( 1, 'hello' ) };
values{ fn locate( 'hello', 'hello', 'hello' ) };
values{ fn locate( 'hello', 'hello', 1.99999999999 ) };
values{ fn locate( 1, 'hel1lo' ) };
values{ fn locate( 1, 1 ) };
values{ fn locate( 1, 1, '1' ) };
values{ fn locate( '1', 1, 1 ) };
values{ fn locate( '1', '1', '1' ) };


-- End of EJBQL function test for LOCATE.
-- This test the EJBQL function, LOCATE. Resolve 3535

-- LOCATE( string1, string2[, start] ) --- string1, searching from the beginning
--   of string2; if start is specified, the search begins from position start.
--   0 is returned if string2 does not contain string1.  Position1 is the first
--   character in string2.

-- Begin of LOCATE test
-- Basic
create table locate( a varchar(20) );

-- create table myChar( a char(10), b char(20), c int default '1'  );
create table myChar( a char(10), b char(20), c int );
insert into myChar (a, b) values( '1234567890', 'abcde1234567890fghij' );
insert into myChar (a, b) values( 'abcdefghij', 'abcdefghij1234567890' );
insert into myChar (a, b) values( 'abcdefghij', '1234567890abcdefghij' );
insert into myChar (a, b) values( 'abcdefghij', '1234567890!@#$%^&*()' );
insert into myChar values( '1234567890', 'abcde1234567890fghij', 2 );
insert into myChar values( 'abcdefghij', 'abcdefghij1234567890', 1  );
insert into myChar values( 'abcdefghij', '1234567890abcdefghij', 15 );
insert into myChar (c) values( 0 );
insert into myChar (c) values( 1 );
insert into myChar (c) values( 2 );
insert into myChar (a) values( 'hello' );
insert into myChar (b) values( 'hello' );
insert into myChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 );
select a, b, c from myChar;
select locate(a, b) from myChar;
select locate(a, b, c) from myChar;

drop table myChar;
create table myLongVarChar( a long varchar, b long varchar, c int);
insert into myLongVarChar (a, b) values( '1234567890', 'abcde1234567890fghij' );
insert into myLongVarChar (a, b) values( 'abcdefghij', 'abcdefghij1234567890' );
insert into myLongVarChar (a, b) values( 'abcdefghij', '1234567890abcdefghij' );
insert into myLongVarChar (a, b) values( 'abcdefghij', '1234567890!@#$%^&*()' );
insert into myLongVarChar (a, b) values( 'abcde', 'abcde' );
insert into myLongVarChar (a, b) values( 'abcde', 'abcd' );
insert into myLongVarChar (a, b) values( '', 'abcde' );
insert into myLongVarChar (a, b) values( 'abcde', null );
insert into myLongVarChar (a, b) values( null, 'abcde' );
insert into myLongVarChar values( '1234567890', 'abcde1234567890fghij', 2 );
insert into myLongVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 );
insert into myLongVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 );
insert into myLongVarChar values( 'abcde', 'abcde', 1 );
insert into myLongVarChar values( 'abcde', 'abcd', 1 );
insert into myLongVarChar values( '', 'abcde', 2 );
insert into myLongVarChar values( 'abcde', null, 1  );
insert into myLongVarChar values( null, 'abcde', 1 );
insert into myLongVarChar (c) values( 0 );
insert into myLongVarChar (c) values( 1 );
insert into myLongVarChar (c) values( 2 );
insert into myLongVarChar (a) values( 'hello' );
insert into myLongVarChar (b) values( 'hello' );
insert into myLongVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 );
select a, b, c from myLongVarChar;
select locate(a, b) from myLongVarChar;
select locate(a, b, c) from myLongVarChar;
drop table myLongVarChar;

create table myVarChar( a varchar(10), b varchar(20), c int );
insert into myVarChar (a, b) values( '1234567890', 'abcde1234567890fghij' );
insert into myVarChar (a, b) values( 'abcdefghij', 'abcdefghij1234567890' );
insert into myVarChar (a, b) values( 'abcdefghij', '1234567890abcdefghij' );
insert into myVarChar (a, b) values( 'abcdefghij', '1234567890!@#$%^&*()' );
insert into myVarChar (a, b) values( 'abcde', 'abcde' );
insert into myVarChar (a, b) values( 'abcde', 'abcd' );
insert into myVarChar (a, b) values( '', 'abcde' );
insert into myVarChar (a, b) values( 'abcde', null );
insert into myVarChar (a, b) values( null, 'abcde' );
insert into myVarChar values( '1234567890', 'abcde1234567890fghij', 2 );
insert into myVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 );
insert into myVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 );
insert into myVarChar values( 'abcde', 'abcde', 1 );
insert into myVarChar values( 'abcde', 'abcd', 1 );
insert into myVarChar values( '', 'abcde', 2 );
insert into myVarChar values( 'abcde', null, 1  );
insert into myVarChar values( null, 'abcde', 1 );
insert into myVarChar (c) values( 0 );
insert into myVarChar (c) values( 1 );
insert into myVarChar (c) values( 2 );
insert into myVarChar (a) values( 'hello' );
insert into myVarChar (b) values( 'hello' );
insert into myVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 );
select a, b, c from myVarChar;
select locate(a, b) from myVarChar;
select locate(a, b, c) from myVarChar;
drop table myVarChar;

-- Negative cases. To match DB2 behaviour
create table t1 (dt date, tm time, ts timestamp);
insert into t1 values (current_date, current_time, current_timestamp);
select locate (dt, ts) from t1;
select locate (tm, ts) from t1;
select locate (ts, ts) from t1;
drop table t1;

values locate('abc', 'dkabc', 1.4);
values locate('c', 'abcdedf', cast(1 as decimal(2,0)));

-- =========================================================================
-- These test cases for national character types will fail until 
-- until a future work around is implemented
-- =========================================================================
create table mynChar( a nchar(10), b nchar(20), c int );
insert into mynChar values( '1234567890', 'abcde1234567890fghij' );
insert into mynChar values( 'abcdefghij', 'abcdefghij1234567890' );
insert into mynChar values( 'abcdefghij', '1234567890abcdefghij' );
insert into mynChar values( 'abcdefghij', '1234567890!@#$%^&*()' );
insert into mynChar values( '1234567890', 'abcde1234567890fghij', 2 );
insert into mynChar values( 'abcdefghij', 'abcdefghij1234567890', 1  );
insert into mynChar values( 'abcdefghij', '1234567890abcdefghij', 15 );
insert into mynChar (c) values( 0 );
insert into mynChar (c) values( 1 );
insert into mynChar (c) values( 2 );
insert into mynChar (a) values( 'hello' );
insert into mynChar (b) values( 'hello' );
insert into mynChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 );
select a, b, c from mynChar;
select locate(a, b) from mynChar;
select locate(a, b, c) from mynChar;
drop table mynChar;

create table myLongnVarChar( a long nvarchar, b long nvarchar, c int );
insert into myLongnVarChar values( '1234567890', 'abcde1234567890fghij' );
insert into myLongnVarChar values( 'abcdefghij', 'abcdefghij1234567890' );
insert into myLongnVarChar values( 'abcdefghij', '1234567890abcdefghij' );
insert into myLongnVarChar values( 'abcdefghij', '1234567890!@#$%^&*()' );
insert into myLongnVarChar values( 'abcde', 'abcde' );
insert into myLongnVarChar values( 'abcde', 'abcd' );
insert into myLongnVarChar values( '', 'abcde' );
insert into myLongnVarChar values( 'abcde', null );
insert into myLongnVarChar values( null, 'abcde' );
insert into myLongnVarChar values( '1234567890', 'abcde1234567890fghij', 2 );
insert into myLongnVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 );
insert into myLongnVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 );
insert into myLongnVarChar values( 'abcde', 'abcde', 1 );
insert into myLongnVarChar values( 'abcde', 'abcd', 1 );
insert into myLongnVarChar values( '', 'abcde', 2 );
insert into myLongnVarChar values( 'abcde', null, 1  );
insert into myLongnVarChar values( null, 'abcde', 1 );
insert into myLongnVarChar (c) values( 0 );
insert into myLongnVarChar (c) values( 1 );
insert into myLongnVarChar (c) values( 2 );
insert into myLongnVarChar (a) values( 'hello' );
insert into myLongnVarChar (b) values( 'hello' );
insert into myLongnVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 );
select a, b, c from myLongnVarChar;
select locate(a, b) from myLongnVarChar;
select locate(a, b, c) from myLongnVarChar;
drop table myLongnVarChar;

create table mynVarChar( a nvarchar(10), b nvarchar(20), c int );
insert into mynVarChar values( '1234567890', 'abcde1234567890fghij' );
insert into mynVarChar values( 'abcdefghij', 'abcdefghij1234567890' );
insert into mynVarChar values( 'abcdefghij', '1234567890abcdefghij' );
insert into mynVarChar values( 'abcdefghij', '1234567890!@#$%^&*()' );
insert into mynVarChar values( 'abcde', 'abcde' );
insert into mynVarChar values( 'abcde', 'abcd' );
insert into mynVarChar values( '', 'abcde' );
insert into mynVarChar values( 'abcde', null );
insert into mynVarChar values( null, 'abcde' );
insert into mynVarChar values( '1234567890', 'abcde1234567890fghij', 2 );
insert into mynVarChar values( 'abcdefghij', 'abcdefghij1234567890', 1 );
insert into mynVarChar values( 'abcdefghij', '1234567890abcdefghij', 15 );
insert into mynVarChar values( 'abcde', 'abcde', 1 );
insert into mynVarChar values( 'abcde', 'abcd', 1 );
insert into mynVarChar values( '', 'abcde', 2 );
insert into mynVarChar values( 'abcde', null, 1  );
insert into mynVarChar values( null, 'abcde', 1 );
insert into mynVarChar (c) values( 0 );
insert into mynVarChar (c) values( 1 );
insert into mynVarChar (c) values( 2 );
insert into mynVarChar (a) values( 'hello' );
insert into mynVarChar (b) values( 'hello' );
insert into mynVarChar values( 'abcdefghij', '1234567890!@#$%^&*()', 21 );
select a, b, c from mynVarChar;
select locate(a, b) from mynVarChar;
select locate(a, b, c) from mynVarChar;

create table myMixed( a char(10), b long nvarchar, c int );
insert into myMixed values( '1234567890', 'abcde1234567890fghij' );
insert into myMixed values( 'abcdefghij', 'abcdefghij1234567890' );
insert into myMixed values( 'abcdefghij', '1234567890abcdefghij' );
insert into myMixed values( 'abcdefghij', '1234567890!@#$%^&*()' );
insert into myMixed values( '1234567890', 'abcde1234567890fghij', 2 );
insert into myMixed values( 'abcdefghij', 'abcdefghij1234567890', 1 );
insert into myMixed values( 'abcdefghij', '1234567890abcdefghij', 15 );
insert into myMixed (c) values( 0 );
insert into myMixed (c) values( 1 );
insert into myMixed (c) values( 2 );
insert into myMixed (a) values( 'hello' );
insert into myMixed (b) values( 'hello' );
insert into myMixed values( 'abcdefghij', '1234567890!@#$%^&*()', 21 );
select a, b, c from myMixed;
select locate(a, b) from myMixed;
select locate(a, b, c) from myMixed;
drop table myMixed;

create table foo( a int );
insert into foo select locate(a, b) from mynVarChar;
insert into foo values( {fn locate('hello', 'hello')} );
select * from foo;
drop table foo;
drop table mynVarChar;
-- =========================================================================

-- Other types
create table myBigInt( a bigint, b bigint );
insert into myBigInt values( 1234, 1234 );
insert into myBigInt values( 4321, 1234 );
select locate(a, b) from myBigInt;
drop table myBigInt;

create table myBit( a char for bit data, b char for bit data );
insert into myBit values( X'40', X'40' );
insert into myBit values( X'01', X'40' );
select locate(a, b) from myBit;
drop table myBit;

-- bug 5794 - LOCATE built-in function is not db2 udb compatible
create table myDate( a date, b date );
insert into myDate values( date('1970-01-08'), date('1970-01-08') );
insert into myDate values( date('1979-08-30'), date('1978-07-28') );
select locate(a, b) from myDate;
drop table myDate;

create table myDecimal( a decimal, b decimal );
insert into myDecimal values( 2.2, 2.2 );
insert into myDecimal values( 12.23, 3423 );
select locate(a, b) from myDecimal;
drop table myDecimal;

create table myDouble( a double precision, b double precision );
insert into myDouble values( 2.2, 2.2 );
insert into myDouble values( 12.23, 3423 );
select locate(a, b) from myDouble;
drop table myDouble;

create table myInteger(a integer, b integer );
insert into myInteger values( 2, 2 );
insert into myInteger values( 123, 3423 );
select locate(a, b) from myInteger;
drop table myInteger;

create table mylongvarbinary( a long varchar for bit data, b long varchar for bit data );
select locate(a, b) from mylongvarbinary;
drop table mylongvarbinary;

-- bug 5794 - LOCATE built-in function is not db2 udb compatible
create table mytime( a time, b time );
insert into mytime values( time('10:00:00'), time('10:00:00') );
insert into mytime values( time('10:00:00'), time('11:00:00') );
select locate(a, b) from mytime;