>>obey TEST103(set_up);
>>-- compile functions
>>log;
------------------------------------------------------------------------------
-- Compiling Java source files: Utils.java TEST103_procs.java
-- Executing: $javac -d $REGRRUNDIR $REGRTSTDIR/Utils.java $REGRTSTDIR/TEST103_procs.java
-- $javac returned 0
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- Archiving Java class files:
--    TEST103_procs.class
--    Utils.class
-- Archive will be written to: TEST103_procs.jar
-- Executing: $jar cMf TEST103_procs.jar TEST103_procs.class Utils.class
-- $jar returned 0
------------------------------------------------------------------------------
>>
>>obey TEST103(create_db);
>>-- create a schema with objects
>>create schema udr103sch;

--- SQL operation complete.
>>set schema udr103sch;

--- SQL operation complete.
>>drop table if exists subscriber_names;

--- SQL operation complete.
>>create table subscriber_names
+>(
+>  name_id largeint generated by default as identity,
+>  company_name varchar(50) not null,
+>  company_suffix varchar (50) not null,
+>  database_name varchar (50) not null,
+>  primary key (name_id)
+>);

--- SQL operation complete.
>>
>>insert into subscriber_names (company_name, company_suffix, database_name) values ('acme', 'acme.com', 'sql_user1');

--- 1 row(s) inserted.
>>insert into subscriber_names (company_name, company_suffix, database_name) values ('esgyn', 'esgyn.com', 'sql_user2');

--- 1 row(s) inserted.
>>insert into subscriber_names (company_name, company_suffix, database_name) values ('bethany', 'gmail.com', 'sql_user3');

--- 1 row(s) inserted.
>>insert into subscriber_names (company_name, company_suffix, database_name) values ('"texas.instruments"', 'ti.com', 'sql_user4');

--- 1 row(s) inserted.
>>insert into subscriber_names (company_name, company_suffix, database_name) values ('"united.airlines"', 'ua.com', 'sql_user5');

--- 1 row(s) inserted.
>>insert into subscriber_names (company_name, company_suffix, database_name) values ('webroot', 'webroot.com', 'sql_user6');

--- 1 row(s) inserted.
>>insert into subscriber_names (company_name, company_suffix, database_name) values ('"mission.help"', 'comcast.net', 'sql_user7');

--- 1 row(s) inserted.
>>insert into subscriber_names (company_name, company_suffix, database_name) values ('radium', 'rd.gov', 'sql_user8');

--- 1 row(s) inserted.
>>insert into subscriber_names (company_name, company_suffix, database_name) values ('alcoa', 'alcoa.com', 'sql_user9');

--- 1 row(s) inserted.
>>insert into subscriber_names (company_name, company_suffix, database_name) values ('hbase', 'apache.com', 'sql_user10');

--- 1 row(s) inserted.
>>
>>CREATE TABLE subscriber_addresses (
+>  id largeint generated by default as identity,
+>  street varchar(255) NOT NULL,
+>  city varchar(255) NOT NULL,
+>  state char(2) NOT NULL,
+>  country varchar(255) NOT NULL,
+>  code varchar(255) NOT NULL,
+>  phone varchar(20) NOT NULL,
+>  PRIMARY KEY (id)
+>);

--- SQL operation complete.
>>
>>INSERT INTO subscriber_addresses (street, city, state, country, code, phone) VALUES
+>('3412 Pleasant Bear Nook', 'Smoke Tree', 'AR', '71629-6396', 'US', '(870) 347-7290'),
+>('1682 Misty Zephyr Manor', 'Owyhee', 'ID', '83976-9599', 'US', '(208) 325-5585'),
+>('2782 Honey Canyon', 'Spikenard', 'WV', '25849-6450', 'US', '(681) 986-5817'),
+>('2413 Burning Prairie Bay', 'Electric City', 'AR', '72311-5330', 'US', '(501) 940-7620'),
+>('2921 Wishing Impasse', 'Frisken Wye', 'UT', '84921-6955', 'US', '(801) 037-7153'),
+>('9058 Foggy Timber Alley', 'Burnt Water', 'CT', '06092-5085', 'US', '(203) 787-4917'),
+>('918 Rocky Terrace', 'Zinzer', 'KY', '40101-3844', 'US', '(859) 721-1763'),
+>('1287 Little Passage', 'Sleeping Buffalo', 'UT', '84177-8231', 'US', '(435) 576-9295'),
+>('5434 Silver Barn Harbour', 'Alcatraz', 'WV', '24893-2726', 'US', '(681) 428-9145'),
+>('2122 Colonial Swale', 'Dugway', 'MN', '56314-6558', 'US', '(612) 814-7362'),
+>('8996 Dusty Embers Expressway', 'Frog Eye', 'IN', '47893-1450', 'US', '(219) 696-1689'),
+>('9714 Crystal Rise Downs', 'Three Brothers', 'CA', '91390-3498', 'US', '(831) 451-2536'),
+>('6504 Broad Cloud Byway', 'Agricola', 'SD', '57815-0738', 'US', '(605) 429-0448'),
+>('7277 Cotton Point', 'Muddy Ford', 'LA', '71379-1156', 'US', '(504) 497-5662'),
+>('8009 Iron Willow Via', 'Clappers', 'AR', '72567-9326', 'US', '(479) 988-8059'),
+>('2882 Indian Robin Orchard', 'Chulahoma', 'NC', '28425-9027', 'US', '(704) 036-8613'),
+>('3106 Umber Green', 'Improve', 'CA', '93982-4948', 'US', '(714) 466-4395'),
+>('9228 Bright Hickory Crossing', 'Morocco', 'VT', '05215-0676', 'US', '(802) 624-7015'),
+>('6725 Shady Oak Estates', 'Devils Lake', 'WA', '99182-8374', 'US', '(206) 202-1976'),
+>('3169 Tawny Grove Square', 'Bushnell', 'AZ', '85671-8633', 'US', '(928) 430-8819');

--- 20 row(s) inserted.
>>
>>
>>create table subscribers
+>(
+>   subscriber_id largeint generated by default as identity,
+>   subscriber_user varchar (128) default null,
+>   subscriber_name varchar(150) not null,
+>   subscriber_address varchar (500) not null,
+>   subscriber_state char(2) not null,
+>   subscriber_phone char(25) not null,
+>   subscriber_email char (100) not null,
+>   subscription_package int not null,
+>   zones_available char(10) not null,
+>   devices_available int not null,
+>   primary key (subscriber_address)
+>);

--- SQL operation complete.
>>
>>insert into subscribers
+>  (subscriber_user, subscriber_name, subscriber_address,
+>   subscriber_state, subscriber_phone, subscriber_email,
+>   subscription_package, zones_available, devices_available)
+>select
+>  database_name,
+>  company_name,
+>  street || ' ' || city,
+>  state,
+>  phone,
+>  company_name || '@' || company_suffix,
+>  1, 'ABCDEFGHI-', 3
+>from subscriber_names n, subscriber_addresses a
+>where a.id = n.name_id;

--- 10 row(s) inserted.
>>
>>insert into subscribers
+>  (subscriber_user, subscriber_name, subscriber_address,
+>   subscriber_state, subscriber_phone, subscriber_email,
+>   subscription_package, zones_available, devices_available) values
+> ('DB__ROOT', 'GREAT_EXPECTATIONS', 'an address', 'CA', '(408) 123-1234',
+>  'great_expectations@ge.com', 6, 'ABCDEFGHI-', 3);

--- 1 row(s) inserted.
>>
>>
>>obey TEST103(tests);
>>set schema udr103sch;

--- SQL operation complete.
>>-- create some libraries
>>obey TEST103(create_libraries);
>>-- create a library, make it so name falls alphabetically between
>>-- functions canAccessViews and generatePhoneNumber
>>set schema udr103sch;

--- SQL operation complete.
>>create library functionsForTest103
+>   file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$;

--- SQL operation complete.
>>showddl library functionsForTest103;
CREATE LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 FILE '/mnt2/ansharma/ansharma_trafr21/incubator-trafodion/core/sqf/rundir/udr/TEST103_functions.dll'
;

-- GRANT UPDATE, USAGE ON LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
>>
>>create library procsForTest103
+>   file $$QUOTE$$ $$REGRRUNDIR$$/$$JARF$$ $$QUOTE$$;

--- SQL operation complete.
>>
>>-- create some routines;
>>obey TEST103(create_routines);
>>-- functions 
>>set schema udr103sch;

--- SQL operation complete.
>>create function canAccessView
+>  (theZone char(1), listOfZones char(10),
+>   packageNeeded int, packageHas int) returns (results int)
+>  language c parameter style sql external name 'canAccessView'
+>  library functionsForTest103
+>  deterministic no sql final call allow any parallelism state area size 1024 ;

--- SQL operation complete.
>>
>>create function generatePhoneNumber
+>  (seedValue int, areaCode char (4)) returns (results char (14))
+>  language c parameter style sql external name 'genPhoneNumber'
+>  library functionsForTest103
+>  deterministic no sql final call allow any parallelism state area size 1024 ;

--- SQL operation complete.
>>
>>create function generateRandomNumber
+>  (seedValue int, numberDigits int) returns (results char (14))
+>  language c parameter style sql external name 'genRandomNumber'
+>  library functionsForTest103
+>  deterministic no sql final call allow any parallelism state area size 1024 ;

--- SQL operation complete.
>>
>>-- procedures
>>create procedure updateSubscriptions(
+>  IN operation char(20),
+>  IN value char(20),
+>  IN userName char(128),
+>  OUT results varchar(1000))
+>  EXTERNAL NAME 'TEST103_procs.updateSubscriptions'
+>  LIBRARY udr103sch.procsForTest103
+>  LANGUAGE JAVA
+>  PARAMETER STYLE JAVA
+>  READS SQL DATA
+>  NO TRANSACTION REQUIRED
+>  ISOLATE
+>  ;

--- SQL operation complete.
>>
>>
>>
>>
>>
>>
>>-- try to drop libraries - should fail
>>drop library functionsForTest103;

*** ERROR[1366] Request failed.  One or more dependent procedures exist.

--- SQL operation failed with errors.
>>drop library procsForTest103;

*** ERROR[1366] Request failed.  One or more dependent procedures exist.

--- SQL operation failed with errors.
>>
>>-- make sure query invalidation works when dropping routines and libraries
>>showddl library functionsForTest103;
CREATE LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 FILE '/mnt2/ansharma/ansharma_trafr21/incubator-trafodion/core/sqf/rundir/udr/TEST103_functions.dll'
;

-- GRANT UPDATE, USAGE ON LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
>>showddl function generatePhoneNumber;

CREATE FUNCTION TRAFODION.UDR103SCH.GENERATEPHONENUMBER
  (
    IN SEEDVALUE INTEGER SIGNED
  , IN AREACODE CHAR(4) CHARACTER SET ISO88591
  )
  RETURNS
  (
    OUT RESULTS CHAR(14) CHARACTER SET ISO88591
  )
  EXTERNAL NAME 'genPhoneNumber'
  LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  NO FINAL CALL
  STATE AREA SIZE 1024
  ALLOW ANY PARALLELISM
  DETERMINISTIC
  SAFE EXECUTION MODE
  ;

-- GRANT EXECUTE ON FUNCTION TRAFODION.UDR103SCH.GENERATEPHONENUMBER TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
>>showddl procedure updateSubscriptions;

CREATE PROCEDURE TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS
  (
    IN OPERATION CHAR(20) CHARACTER SET ISO88591
  , IN VALUE CHAR(20) CHARACTER SET ISO88591
  , IN USERNAME CHAR(128) CHARACTER SET ISO88591
  , OUT RESULTS VARCHAR(1000) CHARACTER SET ISO88591
  )
  EXTERNAL NAME 'TEST103_procs.updateSubscriptions (java.lang.String,java.lang.String,java.lang.String,java.lang.String[])'
  LIBRARY TRAFODION.UDR103SCH.PROCSFORTEST103
  EXTERNAL SECURITY INVOKER
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  READS SQL DATA
  DYNAMIC RESULT SETS 0
  NO TRANSACTION REQUIRED
  ISOLATE
  ;

-- GRANT EXECUTE ON FUNCTION TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
>>
>>drop library functionsForTest103 cascade;

--- SQL operation complete.
>>showddl function generatePhoneNumber;

*** ERROR[1389] Object GENERATEPHONENUMBER does not exist in Trafodion.

*** ERROR[4082] Object TRAFODION.UDR103SCH.GENERATEPHONENUMBER does not exist or is inaccessible.

--- SQL operation failed with errors.
>>showddl function canAccessView;

*** ERROR[1389] Object CANACCESSVIEW does not exist in Trafodion.

*** ERROR[4082] Object TRAFODION.UDR103SCH.CANACCESSVIEW does not exist or is inaccessible.

--- SQL operation failed with errors.
>>showddl library functionsForTest103;

*** ERROR[1389] Object FUNCTIONSFORTEST103 does not exist in Trafodion.

*** ERROR[4082] Object TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>
>>drop library procsForTest103 cascade;

--- SQL operation complete.
>>showddl procedure updateSubscriptions;

*** ERROR[1389] Object UPDATESUBSCRIPTIONS does not exist in Trafodion.

*** ERROR[4082] Object TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS does not exist or is inaccessible.

--- SQL operation failed with errors.
>>showddl library procsForTest103;

*** ERROR[1389] Object PROCSFORTEST103 does not exist in Trafodion.

*** ERROR[4082] Object TRAFODION.UDR103SCH.PROCSFORTEST103 does not exist or is inaccessible.

--- SQL operation failed with errors.
>>
>>-- make sure drop schema works
>>obey TEST103(create_libraries);
>>-- create a library, make it so name falls alphabetically between
>>-- functions canAccessViews and generatePhoneNumber
>>set schema udr103sch;

--- SQL operation complete.
>>create library functionsForTest103
+>   file $$QUOTE$$ $$REGRRUNDIR$$/$$DLL$$ $$QUOTE$$;

--- SQL operation complete.
>>showddl library functionsForTest103;
CREATE LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 FILE '/mnt2/ansharma/ansharma_trafr21/incubator-trafodion/core/sqf/rundir/udr/TEST103_functions.dll'
;

-- GRANT UPDATE, USAGE ON LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103 TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
>>
>>create library procsForTest103
+>   file $$QUOTE$$ $$REGRRUNDIR$$/$$JARF$$ $$QUOTE$$;

--- SQL operation complete.
>>
>>obey TEST103(create_routines);
>>-- functions 
>>set schema udr103sch;

--- SQL operation complete.
>>create function canAccessView
+>  (theZone char(1), listOfZones char(10),
+>   packageNeeded int, packageHas int) returns (results int)
+>  language c parameter style sql external name 'canAccessView'
+>  library functionsForTest103
+>  deterministic no sql final call allow any parallelism state area size 1024 ;

--- SQL operation complete.
>>
>>create function generatePhoneNumber
+>  (seedValue int, areaCode char (4)) returns (results char (14))
+>  language c parameter style sql external name 'genPhoneNumber'
+>  library functionsForTest103
+>  deterministic no sql final call allow any parallelism state area size 1024 ;

--- SQL operation complete.
>>
>>create function generateRandomNumber
+>  (seedValue int, numberDigits int) returns (results char (14))
+>  language c parameter style sql external name 'genRandomNumber'
+>  library functionsForTest103
+>  deterministic no sql final call allow any parallelism state area size 1024 ;

--- SQL operation complete.
>>
>>-- procedures
>>create procedure updateSubscriptions(
+>  IN operation char(20),
+>  IN value char(20),
+>  IN userName char(128),
+>  OUT results varchar(1000))
+>  EXTERNAL NAME 'TEST103_procs.updateSubscriptions'
+>  LIBRARY udr103sch.procsForTest103
+>  LANGUAGE JAVA
+>  PARAMETER STYLE JAVA
+>  READS SQL DATA
+>  NO TRANSACTION REQUIRED
+>  ISOLATE
+>  ;

--- SQL operation complete.
>>
>>
>>
>>
>>
>>get functions in schema udr103sch;

Functions in Schema TRAFODION.UDR103SCH
=======================================

CANACCESSVIEW
GENERATEPHONENUMBER
GENERATERANDOMNUMBER

--- SQL operation complete.
>>get procedures in schema udr103sch;

Procedures in Schema TRAFODION.UDR103SCH
========================================

UPDATESUBSCRIPTIONS

--- SQL operation complete.
>>showddl function generatePhoneNumber;

CREATE FUNCTION TRAFODION.UDR103SCH.GENERATEPHONENUMBER
  (
    IN SEEDVALUE INTEGER SIGNED
  , IN AREACODE CHAR(4) CHARACTER SET ISO88591
  )
  RETURNS
  (
    OUT RESULTS CHAR(14) CHARACTER SET ISO88591
  )
  EXTERNAL NAME 'genPhoneNumber'
  LIBRARY TRAFODION.UDR103SCH.FUNCTIONSFORTEST103
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  NO FINAL CALL
  STATE AREA SIZE 1024
  ALLOW ANY PARALLELISM
  DETERMINISTIC
  SAFE EXECUTION MODE
  ;

-- GRANT EXECUTE ON FUNCTION TRAFODION.UDR103SCH.GENERATEPHONENUMBER TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
>>showddl procedure updateSubscriptions;

CREATE PROCEDURE TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS
  (
    IN OPERATION CHAR(20) CHARACTER SET ISO88591
  , IN VALUE CHAR(20) CHARACTER SET ISO88591
  , IN USERNAME CHAR(128) CHARACTER SET ISO88591
  , OUT RESULTS VARCHAR(1000) CHARACTER SET ISO88591
  )
  EXTERNAL NAME 'TEST103_procs.updateSubscriptions (java.lang.String,java.lang.String,java.lang.String,java.lang.String[])'
  LIBRARY TRAFODION.UDR103SCH.PROCSFORTEST103
  EXTERNAL SECURITY INVOKER
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  READS SQL DATA
  DYNAMIC RESULT SETS 0
  NO TRANSACTION REQUIRED
  ISOLATE
  ;

-- GRANT EXECUTE ON FUNCTION TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.
>>drop schema udr103sch;

*** ERROR[1028] The schema must be empty.  It contains at least one object CANACCESSVIEW.

--- SQL operation failed with errors.
>>drop schema udr103sch cascade;

--- SQL operation complete.
>>showddl function generatePhoneNumber;

*** ERROR[1389] Object GENERATEPHONENUMBER does not exist in Trafodion.

*** ERROR[4082] Object TRAFODION.UDR103SCH.GENERATEPHONENUMBER does not exist or is inaccessible.

--- SQL operation failed with errors.
>>showddl procedure updateSubscriptions;

*** ERROR[1389] Object UPDATESUBSCRIPTIONS does not exist in Trafodion.

*** ERROR[4082] Object TRAFODION.UDR103SCH.UPDATESUBSCRIPTIONS does not exist or is inaccessible.

--- SQL operation failed with errors.
>>
>>log LOG103;
>>obey TEST103(clean_up);
>>set schema udr103sch;

--- SQL operation complete.
>>drop schema udr103sch cascade;

*** ERROR[1003] Schema TRAFODION.UDR103SCH does not exist.

--- SQL operation failed with errors.
>>
>>sh rm -f TEST103_procs.class TEST103_procs.jar;
>>
>>
>>exit;

End of MXCI Session

