dml130.out

derby database source code.good for you.

ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML130  
-- SQL Test Suite, V6.0, Interactive SQL, dml130.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION FLATER
   set schema FLATER;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
FLATER                                                                                                                          
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
   ROLLBACK WORK;
ij> -- date_time print
-- TEST:0678 Data type semantics with NULL / NOT NULL!
   CREATE TABLE CH1 (
  CH1A CHARACTER (10) NOT NULL,
  CH1B CHARACTER NOT NULL,
  CH1C CHAR (10) NOT NULL);
0 rows inserted/updated/deleted
ij> -- PASS:0678 If table is created?
   COMMIT WORK;
ij> CREATE TABLE NUM1 (
  NUM1C1 NUMERIC (3, 2) NOT NULL,
  NUM1C2 NUMERIC (2) NOT NULL,
  NUM1C3 NUMERIC NOT NULL);
0 rows inserted/updated/deleted
ij> -- PASS:0678 If table is created?
   COMMIT WORK;
ij> INSERT INTO CH1 VALUES ('FOO', '', '0123456789');
1 row inserted/updated/deleted
ij> -- PASS:0678 If 1 row is inserted?
   SELECT CH1A, CH1B, CH1C
  FROM CH1;
CH1A      |&|CH1C      
-----------------------
FOO       | |0123456789
ij> -- PASS:0678 If 1 row selected and CH1A = 'FOO       '?
-- PASS:0678 AND CH1B = ' ' and CH1C = '0123456789'?
-- NOTE:0678 One subtest deleted.
   INSERT INTO CH1 VALUES ('FOO',
  'F', 'LITTLETOOLONG');
ERROR 22001: A truncation error was encountered trying to shrink CHAR 'LITTLETOOLONG' to length 10.
ij> -- PASS:0678 If ERROR, string data, right truncation, 0 rows selected?
   DELETE FROM CH1;
1 row inserted/updated/deleted
ij> INSERT INTO CH1 VALUES ('FOO',
  'F', 'BLANKS       ');
1 row inserted/updated/deleted
ij> -- PASS:0678 If 1 row is inserted?
   SELECT CH1A, CH1B, CH1C
  FROM CH1;
CH1A      |&|CH1C      
-----------------------
FOO       |F|BLANKS    
ij> -- PASS:0678 If 1 row selected and CH1A = 'FOO       '?
-- PASS:0678 AND CH1B = 'F' and CH1C = 'BLANKS    '?
   INSERT INTO NUM1 VALUES (9.99, -99, 9);
1 row inserted/updated/deleted
ij> -- PASS:0678 If 1 row is inserted?
   SELECT NUM1C1 * 100, NUM1C2, NUM1C3
  FROM NUM1;
1               |NU&|NUM1C3
---------------------------
999.00          |-99|9     
ij> -- PASS:0678 If 1 row selected and values are 999, -99, 9?
   DELETE FROM NUM1;
1 row inserted/updated/deleted
ij> INSERT INTO NUM1 VALUES (-10, 0, 0);
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(3,2).
ij> -- PASS:0678 If ERROR, string data, numeric value out of range?
-- PASS:0678 AND 0 rows inserted?
   INSERT INTO NUM1 VALUES (0, 100, 0);
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(2,0).
ij> -- PASS:0678 If ERROR, string data, numeric value out of range?
-- PASS:0678 AND 0 rows inserted?
   INSERT INTO NUM1 VALUES (0, 0, 0.1);
1 row inserted/updated/deleted
ij> -- PASS:0678 If 1 row is inserted?
   SELECT NUM1C1 * 100, NUM1C2, NUM1C3
  FROM NUM1;
1               |NU&|NUM1C3
---------------------------
0.00            |0  |0     
ij> -- PASS:0678 If 1 row selected and values are 0, 0, 0?
   ROLLBACK WORK;
ij> --O   DROP TABLE NUM1 CASCADE;
   DROP TABLE NUM1 ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> --O   DROP TABLE CH1 CASCADE;
   DROP TABLE CH1 ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0678 <<< END TEST
-- *********************************************
--O-- TEST:0679 INFO_SCHEM:  Table data types!
--O
--O   CREATE TABLE LOTSA_TYPES (
--O  FLAAG CHAR DEFAULT 'Y', KODE CHAR (5) NOT NULL,
--O  AMPL REAL DEFAULT 3,
--O  COORD DOUBLE PRECISION, SWAY FLOAT, REVS INTEGER,
--O  PITCH SMALLINT, DEGREE NUMERIC (3, 2), RUDDER DECIMAL (2));
--O-- PASS:0679 If table is created?
--O
--O   COMMIT WORK;
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.TABLES
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND TABLE_TYPE = 'BASE TABLE';
--O-- PASS:0679 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.VIEWS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES';
--O-- PASS:0679 If count = 0?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'FLAAG' AND ORDINAL_POSITION = 1
--O  AND COLUMN_DEF = '''Y''' AND DATA_TYPE = 'CHARACTER'
--O  AND CHAR_MAX_LENGTH = 1 AND CHAR_OCTET_LENGTH
--O  > 0 AND NUM_PREC IS NULL AND NUM_PREC_RADIX
--O  IS NULL AND NUM_SCALE IS NULL AND DATETIME_PREC IS
--O  NULL AND INTERVAL_TYPE IS NULL AND INTERVAL_PREC IS
--O  NULL AND IS_NULLABLE = 'YES';
--O-- PASS:0679 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'KODE' AND ORDINAL_POSITION = 2
--O  AND COLUMN_DEF IS NULL AND DATA_TYPE = 'CHARACTER'
--O  AND CHAR_MAX_LENGTH = 5 AND CHAR_OCTET_LENGTH
--O  > 3 AND NUM_PREC IS NULL AND NUM_PREC_RADIX
--O  IS NULL AND NUM_SCALE IS NULL AND DATETIME_PREC IS
--O  NULL AND INTERVAL_TYPE IS NULL AND INTERVAL_PREC IS
--O  NULL AND IS_NULLABLE = 'NO';
--O-- PASS:0679 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'AMPL' AND ORDINAL_POSITION = 3
--O  AND COLUMN_DEF IS NOT NULL AND DATA_TYPE = 'REAL'
--O  AND CHAR_MAX_LENGTH IS NULL AND CHAR_OCTET_LENGTH
--O  IS NULL AND NUM_PREC >= 8 AND NUM_PREC_RADIX
--O  = 2 AND NUM_SCALE IS NULL AND DATETIME_PREC IS
--O  NULL AND INTERVAL_TYPE IS NULL AND INTERVAL_PREC IS
--O  NULL AND IS_NULLABLE = 'YES';
--O-- PASS:0679 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'COORD' AND ORDINAL_POSITION = 4
--O  AND COLUMN_DEF IS NULL AND DATA_TYPE = 'DOUBLE PRECISION'
--O  AND CHAR_MAX_LENGTH IS NULL AND CHAR_OCTET_LENGTH
--O  IS NULL AND NUM_PREC > 8 AND NUM_PREC_RADIX
--O  = 2 AND NUM_SCALE IS NULL AND DATETIME_PREC IS
--O  NULL AND INTERVAL_TYPE IS NULL AND INTERVAL_PREC IS
--O  NULL AND IS_NULLABLE = 'YES';
--O-- PASS:0679 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'SWAY' AND ORDINAL_POSITION = 5
--O  AND COLUMN_DEF IS NULL AND DATA_TYPE = 'FLOAT'
--O  AND CHAR_MAX_LENGTH IS NULL AND CHAR_OCTET_LENGTH
--O  IS NULL AND NUM_PREC >= 8 AND NUM_PREC_RADIX
--O  = 2 AND NUM_SCALE IS NULL AND DATETIME_PREC IS
--O  NULL AND INTERVAL_TYPE IS NULL AND INTERVAL_PREC IS
--O  NULL AND IS_NULLABLE = 'YES';
--O-- PASS:0679 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'REVS' AND ORDINAL_POSITION = 6
--O  AND COLUMN_DEF IS NULL AND DATA_TYPE = 'INTEGER'
--O  AND CHAR_MAX_LENGTH IS NULL AND CHAR_OCTET_LENGTH
--O  IS NULL AND NUM_PREC > 0 AND NUM_SCALE = 0 AND
--O  (NUM_PREC_RADIX = 2 OR NUM_PREC_RADIX = 10)
--O  AND DATETIME_PREC IS NULL AND INTERVAL_TYPE IS NULL AND
--O  INTERVAL_PREC IS NULL AND IS_NULLABLE = 'YES';
--O-- PASS:0679 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'PITCH' AND ORDINAL_POSITION = 7
--O  AND COLUMN_DEF IS NULL AND DATA_TYPE = 'SMALLINT'
--O  AND CHAR_MAX_LENGTH IS NULL AND CHAR_OCTET_LENGTH
--O  IS NULL AND NUM_PREC > 0 AND NUM_SCALE = 0 AND
--O  (NUM_PREC_RADIX = 2 OR NUM_PREC_RADIX = 10)
--O  AND DATETIME_PREC IS NULL AND INTERVAL_TYPE IS NULL AND
--O  INTERVAL_PREC IS NULL AND IS_NULLABLE = 'YES';
--O-- PASS:0679 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'DEGREE' AND ORDINAL_POSITION = 8
--O  AND COLUMN_DEF IS NULL AND DATA_TYPE = 'NUMERIC'
--O  AND CHAR_MAX_LENGTH IS NULL AND CHAR_OCTET_LENGTH
--O  IS NULL AND NUM_PREC = 3 AND NUM_SCALE = 2
--O  AND NUM_PREC_RADIX = 10
--O  AND DATETIME_PREC IS NULL AND INTERVAL_TYPE IS NULL AND
--O  INTERVAL_PREC IS NULL AND IS_NULLABLE = 'YES';
--O-- PASS:0679 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'RUDDER' AND ORDINAL_POSITION = 9
--O  AND COLUMN_DEF IS NULL AND DATA_TYPE = 'DECIMAL'
--O  AND CHAR_MAX_LENGTH IS NULL AND CHAR_OCTET_LENGTH
--O  IS NULL AND NUM_PREC >= 2 AND NUM_SCALE = 0
--O  AND NUM_PREC_RADIX = 10
--O  AND DATETIME_PREC IS NULL AND INTERVAL_TYPE IS NULL AND
--O  INTERVAL_PREC IS NULL AND IS_NULLABLE = 'YES';
--O-- PASS:0679 If count = 1?
--O
--O   ROLLBACK WORK;
--O
--O   DROP TABLE LOTSA_TYPES CASCADE;
--O
--O   COMMIT WORK;
--O
--O-- END TEST >>> 0679 <<< END TEST
--O
--O-- *********************************************
--O
--O-- TEST:0680 INFO_SCHEM:  View data types!
--O
--O   CREATE TABLE LT1 (
--O  FLAAG CHAR DEFAULT 'Y', KODE CHAR (5) NOT NULL,
--O  AMPL REAL DEFAULT 3,
--O  COORD DOUBLE PRECISION, SWAY FLOAT, REVS INTEGER);
--O-- PASS:0680 If table is created?
--O
--O   COMMIT WORK;
--O
--O   CREATE TABLE LT2 (
--O  PITCH SMALLINT, DEGREE NUMERIC (3, 2), RUDDER DECIMAL (2));
--O-- PASS:0680 If table is created?
--O
--O   COMMIT WORK;
--O
--O   CREATE VIEW LOTSA_TYPES AS
--O  SELECT * FROM LT1, LT2;
--O-- PASS:0680 If view is created?
--O
--O   COMMIT WORK;
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.TABLES
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND TABLE_TYPE = 'VIEW';
--O-- PASS:0680 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.VIEWS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES';
--O-- PASS:0680 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'FLAAG' AND ORDINAL_POSITION = 1
--O  AND COLUMN_DEF = '''Y''' AND DATA_TYPE = 'CHARACTER'
--O  AND CHAR_MAX_LENGTH = 1 AND CHAR_OCTET_LENGTH
--O  > 0 AND NUM_PREC IS NULL AND NUM_PREC_RADIX
--O  IS NULL AND NUM_SCALE IS NULL AND DATETIME_PREC IS
--O  NULL AND INTERVAL_TYPE IS NULL AND INTERVAL_PREC IS
--O  NULL AND IS_NULLABLE = 'YES';
--O-- PASS:0680 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'KODE' AND ORDINAL_POSITION = 2
--O  AND COLUMN_DEF IS NULL AND DATA_TYPE = 'CHARACTER'
--O  AND CHAR_MAX_LENGTH = 5 AND CHAR_OCTET_LENGTH
--O  > 0 AND NUM_PREC IS NULL AND NUM_PREC_RADIX
--O  IS NULL AND NUM_SCALE IS NULL AND DATETIME_PREC IS
--O  NULL AND INTERVAL_TYPE IS NULL AND INTERVAL_PREC IS
--O  NULL AND IS_NULLABLE = 'NO';
--O-- PASS:0680 If count = 1?
--O
--O   SELECT COUNT(*) FROM INFO_SCHEM.COLUMNS
--O  WHERE TABLE_SCHEM = 'FLATER' AND TABLE_NAME = 'LOTSA_TYPES'
--O  AND COLUMN_NAME = 'AMPL' AND ORDINAL_POSITION = 3
--O  AND COLUMN_DEF IS NOT NULL AND DATA_TYPE = 'REAL'
--O  AND CHAR_MAX_LENGTH IS NULL AND CHAR_OCTET_LENGTH
--O  IS NULL AND NUM_PREC >= 8 AND NUM_PREC_RADIX
--O  = 2 AND NUM_SCALE IS NULL AND DATETIME_PREC IS
--O  NULL AND INTERVAL_TYPE IS NULL AND INTERVAL_PREC IS
--O  NULL AND IS_NULLABLE = 'YES';
--O-- PASS:0680 If count = 1?
--O