home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 18 REXX
/
18-REXX.zip
/
rxsqlora.zip
/
tester.cmd
< prev
Wrap
OS/2 REXX Batch file
|
1997-06-11
|
38KB
|
822 lines
/*
* This program is the test suite for Rexx/SQL
*
* To use it you must do the following:
* 1. set environment variables REXXSQL_USERNAME, REXXSQL_PASSWORD
* and REXXSQL_SERVER to values of a valid username, password and
* database server respectively.
* Typical values for each tested database follows:
*
* Database REXXSQL_USERNAME REXXSQL_PASSWORD REXXSQL_DATABASE
* ----------------------------------------------------------------
* ORACLE SCOTT TIGER
* SQLAnyWhere dba sql SADEMO
* DB2/2 userid password SAMPLE
* mySQL REXXSQL
* mSQL REXXSQL
* ODBC(Access) REXXSQL
* 2. Run this Rexx/SQL program with a parameter of "setup". This creates
* the two test tables; RX_EMP and RX_DEPT.
* 3. Run this Rexx/SQL program with no parameters. This runs the complete
* test suite. Alternately, you can run each individual test by specifying
* its name as the only parameter. The valid values are specified below
* in the variable "exercise".
*/
Trace o
exercise = 'connections describe fetch command placemarker transaction info errors'
If initialise() Then Exit 1
Parse Arg test .
If test = '' Then
Do i = 1 To Words(exercise)
Interpret Call Word(exercise,i)
End
Else
Do
If Datatype(test,'NUM') Then Interpret Call Word(exercise,test)
Else Interpret Call test
End
Call finalise
Return
/*-----------------------------------------------------------------*/
initialise:
dll.unix='./rexxsql.rxlib'
dll.os2='REXXSQL'
dll.win32='REXXSQL'
dll.windowsnt='REXXSQL'
dll.windows95='REXXSQL'
Parse Source int_os method .
If int_os = 'OS/2' Then int_os = 'OS2'; Else int_os = Translate(int_os)
If method = 'COMMAND' Then
Do
rc = RXFuncAdd('SQLLoadFuncs',dll.int_os,'SQLLoadFuncs')
Call SqlLoadFuncs
dll = 'YES'
End
version = sqlvariable('VERSION')
say version
Parse Var version . . . . . os db .
db = Translate(db)
select
when os = 'UNIX' Then envname = 'SYSTEM'
when os = 'WIN32' Then envname = 'ENVIRONMENT'
when os = 'OS/2' Then envname = 'OS2ENVIRONMENT'
otherwise Say 'Unsupported platform'
end
sqlconnect.1 = Value('REXXSQL_USERNAME',,envname)
sqlconnect.2 = Value('REXXSQL_PASSWORD',,envname)
sqlconnect.3 = Value('REXXSQL_DATABASE',,envname)
sqlconnect.4 = Value('REXXSQL_SERVER' ,,envname)
columnnames_emp = 'empid deptno mgrid empname startdt enddt salary dbname'
columnnames_dept = 'deptno deptname dbname'
stringdatatypes_empid.oracle = 'NUMBER'
stringdatatypes_deptno.oracle = 'NUMBER'
stringdatatypes_empid.msql = 'INT'
stringdatatypes_deptno.msql = 'INT'
stringdatatypes_empid.mysql = 'INT'
stringdatatypes_deptno.mysql = 'INT'
stringdatatypes_empid.sybase_sql_anywhere = 'SMALLINT'
stringdatatypes_deptno.sybase_sql_anywhere = 'SMALLINT'
stringdatatypes_empid.sqlanywhere = 'SMALLINT'
stringdatatypes_deptno.sqlanywhere = 'SMALLINT'
stringdatatypes_empid.db2 = 'SMALLINT'
stringdatatypes_deptno.db2 = 'SMALLINT'
/*
* ODBC datatypes have to be specified AFTER a connection is made to a
* database.
*/
Return 0
/*-----------------------------------------------------------------*/
finalise:
If dll = 'YES' Then Call SqlDropFuncs
Return
/*-----------------------------------------------------------------*/
connect: Procedure Expose sqlca. sqlconnect.
Parse Arg id .
If sqlconnect(id,sqlconnect.1,sqlconnect.2,sqlconnect.3,sqlconnect.4) < 0 Then Abort('connect')
Say 'connect: succeeded for <'id'> <'sqlconnect.1'> <'sqlconnect.2'> <'sqlconnect.3'> <'sqlconnect.4'>'
Return
/*-----------------------------------------------------------------*/
disconnect: Procedure Expose sqlca.
Parse Arg id
If sqldisconnect(id) < 0 Then Abort('disconnect')
Say 'disconnect: succeeded for <'id'>'
Return
/*-----------------------------------------------------------------*/
setup: Procedure Expose sqlca. sqlconnect. db columnnames_emp columnnames_dept
Say Copies('*',20)
Say 'setup: Creating test tables...'
Say Copies('*',20)
Call connect 'c1'
/*
* For ODBC we need to determine the database we are connecting to so we
* can use this to specify the datatypes that it can use
*/
dbcon = Translate(sqlgetinfo('c1','DBMSNAME'))
If db = 'ODBC' Then db = Translate(dbcon)
src = "'"||dbcon||"'"
Call setdatatypes
create1 = 'create table RX_EMP ('
Do i = 1 To Words(columnnames_emp)
create1 = create1 Word(columnnames_emp,i) columntypes_emp.db.i
End
create1 = create1 ')'
Call create_test_table 'RX_EMP' create1
create1 = 'create table RX_DEPT ('
Do i = 1 To Words(columnnames_dept)
create1 = create1 Word(columnnames_dept,i) columntypes_dept.db.i
End
create1 = create1 ')'
Call create_test_table 'RX_DEPT' create1
Do i = 1 To columndata_emp.db.0
c1 = "insert into RX_EMP values(",
Strip(Substr(columndata_emp.db.i,1,5)) ',',
Strip(Substr(columndata_emp.db.i,6,5)) ',',
Strip(Substr(columndata_emp.db.i,11,5)) ',',
Strip(Substr(columndata_emp.db.i,16,32)) ',',
Strip(Substr(columndata_emp.db.i,49,12)) ',',
Strip(Substr(columndata_emp.db.i,61,12)) ',',
Strip(Substr(columndata_emp.db.i,73,13)) ',' ,
src ")"
If sqlcommand('c1',c1) < 0 Then Abort('setup: inserting into RX_EMP table')
If sqlvariable('SUPPORTSDMLROWCOUNT') = 1 Then Say 'setup:' sqlca.rowcount 'row(s) inserted successfully'
Else Say 'setup: setting of SQLCA.ROWCOUNT for DML not supported; insert succeeded'
End
Do i = 1 To columndata_dept.db.0
c2 = "insert into RX_DEPT values(",
Strip(Substr(columndata_dept.db.i,1,5)) ',',
Strip(Substr(columndata_dept.db.i,6,52)) ',',
src ")"
If sqlcommand('c2',c2) < 0 Then Abort('setup: inserting into RX_DEPT table')
If sqlvariable('SUPPORTSDMLROWCOUNT') = 1 Then Say 'setup:' sqlca.rowcount 'row(s) inserted successfully'
Else Say 'setup: setting of SQLCA.ROWCOUNT for DML not supported; insert succeeded'
End
If sqlcommit() < 0 Then Abort('setup: commiting')
Call disconnect 'c1'
Return
/*-----------------------------------------------------------------*/
setdatatypes:
/* table definitions for Oracle */
columntypes_emp.oracle.1 = 'number(5) not null,'
columntypes_emp.oracle.2 = 'number(5) not null,'
columntypes_emp.oracle.3 = 'number(5) not null,'
columntypes_emp.oracle.4 = 'varchar2(30) not null,'
columntypes_emp.oracle.5 = 'date,'
columntypes_emp.oracle.6 = 'date,'
columntypes_emp.oracle.7 = 'number(10,2) not null,'
columntypes_emp.oracle.8 = 'varchar2(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][---][---][-------------------------------][----------][----------][-----------] */
columndata_emp.oracle.1 = " 1 10 0'Joe Bloggs' '26-JAN-96' NULL 556.22"
columndata_emp.oracle.2 = " 2 10 1'Mary Jones' '26-FEB-91' '26-JAN-96' 202.04"
columndata_emp.oracle.3 = " 3 20 1'Steve Brown' '04-MAY-95' NULL 345.00"
columndata_emp.oracle.0 = 3
columntypes_dept.oracle.1 = 'number(5) not null,'
columntypes_dept.oracle.2 = 'varchar2(50) not null,'
columntypes_dept.oracle.3 = 'varchar2(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][------------------------------------------------------------] */
columndata_dept.oracle.1 = " 10'Department 10' "
columndata_dept.oracle.2 = " 20'Department 20' "
columndata_dept.oracle.0 = 2
/* table definitions for mSQL */
columntypes_emp.msql.1 = 'int primary key,'
columntypes_emp.msql.2 = 'int not null,'
columntypes_emp.msql.3 = 'int not null,'
columntypes_emp.msql.4 = 'char(30) not null,'
columntypes_emp.msql.5 = 'char(9),'
columntypes_emp.msql.6 = 'char(9),'
columntypes_emp.msql.7 = 'real not null,'
columntypes_emp.msql.8 = 'char(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][---][---][-------------------------------][----------][----------][-----------] */
columndata_emp.msql.1 = " 1 10 0'Joe Bloggs' '26-JAN-96' NULL 556.22"
columndata_emp.msql.2 = " 2 10 1'Mary Jones' '26-FEB-91' '26-JAN-96' 202.04"
columndata_emp.msql.3 = " 3 20 1'Steve Brown' '04-MAY-95' NULL 345.00"
columndata_emp.msql.0 = 3
columntypes_dept.msql.1 = 'int not null,'
columntypes_dept.msql.2 = 'char(50) not null,'
columntypes_dept.msql.3 = 'char(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][------------------------------------------------------------] */
columndata_dept.msql.1 = " 10'Department 10' "
columndata_dept.msql.2 = " 20'Department 20' "
columndata_dept.msql.0 = 2
/* table definitions for mySQL */
columntypes_emp.mysql.1 = 'int primary key,'
columntypes_emp.mysql.2 = 'int not null,'
columntypes_emp.mysql.3 = 'int not null,'
columntypes_emp.mysql.4 = 'char(30) not null,'
columntypes_emp.mysql.5 = 'date,'
columntypes_emp.mysql.6 = 'date,'
columntypes_emp.mysql.7 = 'decimal(9,2) not null,'
columntypes_emp.mysql.8 = 'char(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][---][---][-------------------------------][----------][----------][-----------] */
columndata_emp.mysql.1 = " 1 10 0'Joe Bloggs' '19960126' NULL 556.22"
columndata_emp.mysql.2 = " 2 10 1'Mary Jones' '19910226' '19960126' 202.04"
columndata_emp.mysql.3 = " 3 20 1'Steve Brown' '19950504' NULL 345.00"
columndata_emp.mysql.0 = 3
columntypes_dept.mysql.1 = 'int not null,'
columntypes_dept.mysql.2 = 'char(50) not null,'
columntypes_dept.mysql.3 = 'char(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][------------------------------------------------------------] */
columndata_dept.mysql.1 = " 10'Department 10' "
columndata_dept.mysql.2 = " 20'Department 20' "
columndata_dept.mysql.0 = 2
/* table definitions for Sybase_SQL_Anywhere */
columntypes_emp.Sybase_SQL_Anywhere.1 = 'smallint not null,'
columntypes_emp.Sybase_SQL_Anywhere.2 = 'smallint not null,'
columntypes_emp.Sybase_SQL_Anywhere.3 = 'smallint not null,'
columntypes_emp.Sybase_SQL_Anywhere.4 = 'varchar(30) not null,'
columntypes_emp.Sybase_SQL_Anywhere.5 = 'date,'
columntypes_emp.Sybase_SQL_Anywhere.6 = 'date,'
columntypes_emp.Sybase_SQL_Anywhere.7 = 'money not null,'
columntypes_emp.Sybase_SQL_Anywhere.8 = 'varchar(30)'
columntypes_emp.sqlanywhere.1 = 'smallint not null,'
columntypes_emp.sqlanywhere.2 = 'smallint not null,'
columntypes_emp.sqlanywhere.3 = 'smallint not null,'
columntypes_emp.sqlanywhere.4 = 'varchar(30) not null,'
columntypes_emp.sqlanywhere.5 = 'date,'
columntypes_emp.sqlanywhere.6 = 'date,'
columntypes_emp.sqlanywhere.7 = 'money not null,'
columntypes_emp.sqlanywhere.8 = 'varchar(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][---][---][-------------------------------][----------][----------][-----------] */
columndata_emp.Sybase_SQL_Anywhere.1 = " 1 10 0'Joe Bloggs' '19960126' NULL 556.22"
columndata_emp.Sybase_SQL_Anywhere.2 = " 2 10 1'Mary Jones' '19910226' '19960126' 202.04"
columndata_emp.Sybase_SQL_Anywhere.3 = " 3 20 1'Steve Brown' '19950504' NULL 345.00"
columndata_emp.Sybase_SQL_Anywhere.0 = 3
columntypes_dept.Sybase_SQL_Anywhere.1 = 'smallint not null,'
columntypes_dept.Sybase_SQL_Anywhere.2 = 'char(50) not null,'
columntypes_dept.Sybase_SQL_Anywhere.3 = 'char(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][------------------------------------------------------------] */
columndata_dept.Sybase_SQL_Anywhere.1 = " 10'Department 10' "
columndata_dept.Sybase_SQL_Anywhere.2 = " 20'Department 20' "
columndata_dept.Sybase_SQL_Anywhere.0 = 2
columndata_emp.sqlanywhere.1 = " 1 10 0'Joe Bloggs' '19960126' NULL 556.22"
columndata_emp.sqlanywhere.2 = " 2 10 1'Mary Jones' '19910226' '19960126' 202.04"
columndata_emp.sqlanywhere.3 = " 3 20 1'Steve Brown' '19950504' NULL 345.00"
columndata_emp.sqlanywhere.0 = 3
columntypes_dept.sqlanywhere.1 = 'smallint not null,'
columntypes_dept.sqlanywhere.2 = 'char(50) not null,'
columntypes_dept.sqlanywhere.3 = 'char(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][------------------------------------------------------------] */
columndata_dept.sqlanywhere.1 = " 10'Department 10' "
columndata_dept.sqlanywhere.2 = " 20'Department 20' "
columndata_dept.sqlanywhere.0 = 2
/* table definitions for DB2 */
columntypes_emp.db2.1 = 'smallint not null,'
columntypes_emp.db2.2 = 'smallint not null,'
columntypes_emp.db2.3 = 'smallint not null,'
columntypes_emp.db2.4 = 'varchar(30) not null,'
columntypes_emp.db2.5 = 'date,'
columntypes_emp.db2.6 = 'date,'
columntypes_emp.db2.7 = 'decimal(8,2) not null,'
columntypes_emp.db2.8 = 'varchar(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][---][---][-------------------------------][----------][----------][-----------] */
columndata_emp.db2.1 = " 1 10 0'Joe Bloggs' '1996-01-26'NULL 556.22"
columndata_emp.db2.2 = " 2 10 1'Mary Jones' '1991-02-26''1996-01-26' 202.04"
columndata_emp.db2.3 = " 3 20 1'Steve Brown' '1995-05-04'NULL 345.00"
columndata_emp.db2.0 = 3
columntypes_dept.db2.1 = 'smallint not null,'
columntypes_dept.db2.2 = 'char(50) not null,'
columntypes_dept.db2.3 = 'varchar(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][------------------------------------------------------------] */
columndata_dept.db2.1 = " 10'Department 10' "
columndata_dept.db2.2 = " 20'Department 20' "
columndata_dept.db2.0 = 2
/* table definitions for ACCESS */
columntypes_emp.access.1 = 'byte not null,'
columntypes_emp.access.2 = 'byte not null,'
columntypes_emp.access.3 = 'byte not null,'
columntypes_emp.access.4 = 'char(30) not null,'
columntypes_emp.access.5 = 'datetime,'
columntypes_emp.access.6 = 'datetime,'
columntypes_emp.access.7 = 'currency not null,'
columntypes_emp.access.8 = 'char(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][---][---][-------------------------------][----------][----------][-----------] */
columndata_emp.access.1 = " 1 10 0'Joe Bloggs' '26/01/1996'NULL 556.22"
columndata_emp.access.2 = " 2 10 1'Mary Jones' '26/02/1991''26/01/1996' 202.04"
columndata_emp.access.3 = " 3 20 1'Steve Brown' '04/05/1995'NULL 345.00"
columndata_emp.access.0 = 3
columntypes_dept.access.1 = 'byte not null,'
columntypes_dept.access.2 = 'char(50) not null,'
columntypes_dept.access.3 = 'char(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][------------------------------------------------------------] */
columndata_dept.access.1 = " 10'Department 10' "
columndata_dept.access.2 = " 20'Department 20' "
columndata_dept.access.0 = 2
/* table definitions for ACCESS */
columntypes_emp.dbase.1 = 'numeric not null,'
columntypes_emp.dbase.2 = 'numeric not null,'
columntypes_emp.dbase.3 = 'numeric not null,'
columntypes_emp.dbase.4 = 'char(30) not null,'
columntypes_emp.dbase.5 = 'date,'
columntypes_emp.dbase.6 = 'date,'
columntypes_emp.dbase.7 = 'numeric not null,'
columntypes_emp.dbase.8 = 'char(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][---][---][-------------------------------][----------][----------][-----------] */
columndata_emp.dbase.1 = " 1 10 0'Joe Bloggs' '26/01/1996'NULL 556.22"
columndata_emp.dbase.2 = " 2 10 1'Mary Jones' '26/02/1991''26/01/1996' 202.04"
columndata_emp.dbase.3 = " 3 20 1'Steve Brown' '04/05/1995'NULL 345.00"
columndata_emp.dbase.0 = 3
columntypes_dept.dbase.1 = 'numeric not null,'
columntypes_dept.dbase.2 = 'char(50) not null,'
columntypes_dept.dbase.3 = 'char(30)'
/* 1 2 3 4 5 6 7 8 9 10 11 12 */
/* 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 */
/* [---][------------------------------------------------------------] */
columndata_dept.dbase.1 = " 10'Department 10' "
columndata_dept.dbase.2 = " 20'Department 20' "
columndata_dept.dbase.0 = 2
Return
/*-----------------------------------------------------------------*/
setbindtypes:
stringdatatypes_empid.access = 'BYTE'
stringdatatypes_deptno.access = 'BYTE'
stringdatatypes_empid.dbase = 'NUMERIC'
stringdatatypes_deptno.dbase = 'NUMERIC'
Return
/*-----------------------------------------------------------------*/
create_test_table: Procedure Expose sqlca.
Parse Arg table_name create_string
rc = sqlcommand('c1','drop table' table_name)
If sqlcommand('c1',create_string) < 0 Then Abort('setup: creating' table_name 'table')
Say 'setup:' table_name 'created successfully'
Return
/*-----------------------------------------------------------------*/
connections: Procedure Expose sqlca. sqlconnect.
Say Copies('*',20)
Say 'Testing multiple connections/disconnections...'
Say Copies('*',20)
Call connect 'c1'
Call connect 'c2'
Call connect 'c3'
Call disconnect 'c1'
Call disconnect 'c2'
Call disconnect 'c3'
Return
/*-----------------------------------------------------------------*/
describe: Procedure Expose sqlca. sqlconnect. db os
Say Copies('*',20)
Say 'Testing statement descriptions...'
Say Copies('*',20)
If db = 'ORACLE' & os = 'OS/2' Then
Do
query1 = 'select * from RX_EMP order by empid'
/* query1 = 'select empid,empname from RX_EMP'*/
/* query1 = 'select * from RX_EMP a, RX_DEPT b where a.deptno = b.deptno'*/
Say Copies('*',30)
Say 'The OS/2 port of Oracle (at least 7.0.xx) does not'
Say 'correctly describe the following statement:'
Say ' select * from RX_EMP'
Say Copies('*',30)
End
Else
query1 = 'select * from RX_EMP'
Call connect 'c1'
If sqlgetinfo('c1','DESCRIBECOLUMNS','desc.') < 0 Then Abort('describe: getting describe columns')
Do i = 1 To desc.0
width.i = Length(desc.i)
End
Say 'describe: Describing <'|| query1 ||'>'
If sqlprepare('p1',query1) < 0 Then Abort('describe: preparing')
If sqldescribe('p1') < 0 Then Abort('describe: describing')
col = desc.1
num_rows = p1.column.col.0
Do i = 1 To num_rows
Do j = 1 To desc.0
col = desc.j
col_val = p1.column.col.i
if Length(col_val) > width.j Then width.j = Length(col_val)
End
End
line = ''
line_len = 0
Do i = 1 To desc.0
line = line Left(desc.i,width.i)
line_len = line_len + 1 + width.i
End
Say line
Say Copies('-',line_len)
Do i = 1 To num_rows
line = ''
Do j = 1 To desc.0
col = desc.j
line = line Left(p1.column.col.i,width.j)
End
Say line
End
If sqldispose('p1') < 0 Then Abort('describe: disposing')
Call disconnect 'c1'
Return
/*-----------------------------------------------------------------*/
fetch: Procedure Expose sqlca. sqlconnect. columnnames_emp columnnames_dept db os
Say Copies('*',20)
Say 'Testing sqlprepare/sqlopen/sqlfetch...'
Say Copies('*',20)
If db = 'ORACLE' & os = 'OS/2' Then
Do
query1 = 'select * from RX_EMP order by empid'
/* query1 = 'select empid,empname from RX_EMP'*/
/* query1 = 'select * from RX_EMP a, RX_DEPT b where a.deptno = b.deptno'*/
Say Copies('*',30)
Say 'The OS/2 port of Oracle (at least 7.0.xx) does not'
Say 'correctly describe the following statement:'
Say ' select * from RX_EMP'
Say Copies('*',30)
End
Else
query1 = 'select * from RX_EMP'
Call connect 'c1'
rc = sqlvariable('NULLSTRINGOUT','<null>')
Say 'fetch: Fetching for <'|| query1 ||'>'
If sqlprepare('p1',query1) < 0 Then Abort('fetch: preparing')
If sqlopen('p1') < 0 Then Abort('fetch: opening')
Do Forever
rc = sqlfetch('p1')
If rc < 0 then Abort('fetch: fetching')
If rc = 0 Then Leave
line = ''
Do j = 1 To Words(columnnames_emp)
col = Translate(Word(columnnames_emp,j))
line = line p1.col
End
Say line
End
If sqlclose('p1') < 0 Then Abort('fetch: closing')
If sqldispose('p1') < 0 Then Abort('fetch: disposing')
Call disconnect 'c1'
Return
/*-----------------------------------------------------------------*/
command: Procedure Expose sqlca. sqlconnect. columnnames_emp columnnames_dept
Say Copies('*',20)
Say 'Testing sqlcommand...'
Say Copies('*',20)
query1 = "select * from RX_EMP"
Call connect 'c1'
rc = sqlvariable('NULLSTRINGOUT','<null>')
Say 'command: <'|| query1 ||'>'
If sqlcommand('p1',query1) < 0 Then Abort('command: executing')
Say 'command:' sqlca.rowcount 'row(s) retrieved successfully'
col = Translate(Word(columnnames_emp,1))
num_rows = p1.col.0
Do i = 1 To num_rows
line = ''
Do j = 1 To Words(columnnames_emp)
col = Translate(Word(columnnames_emp,j))
line = line p1.col.i
End
Say line
End
Call disconnect 'c1'
Return
/*-----------------------------------------------------------------*/
placemarker: Procedure Expose sqlca. sqlconnect. os db columnnames_emp columnnames_dept stringdatatypes_empid. stringdatatypes_deptno.
Say Copies('*',20)
Say 'Testing sqlcommand with placemarkers...'
Say Copies('*',20)
If sqlvariable('SUPPORTSPLACEMARKERS') = 0 Then
Do
Say db 'does not support the use of placemarkers in queries. This test ignored.'
Return
End
query1 = "select * from RX_EMP where empid = ? and deptno = ?"
Call connect 'c1'
dbcon = sqlgetinfo('c1','DBMSNAME')
If db = 'ODBC' Then db = Translate(dbcon)
Call setbindtypes
rc = sqlvariable('NULLSTRINGOUT','<null>')
rc = sqlvariable('STANDARDPLACEMARKERS',1)
Say 'placemarker: (normal): <'|| query1 ||'>'
If sqlcommand('p1',query1,stringdatatypes_empid.db,1,stringdatatypes_deptno.db,10) < 0 Then Abort('placemarker: (normal) executing')
col = Translate(Word(columnnames_emp,1))
num_rows = p1.col.0
Do i = 1 To num_rows
line = ''
Do j = 1 To Words(columnnames_emp)
col = Translate(Word(columnnames_emp,j))
line = line p1.col.i
End
Say line
End
Say 'placemarker: (array): <'|| query1 ||'>'
dt.0 = 2
dt.1 = stringdatatypes_empid.db
dt.2 = stringdatatypes_deptno.db
dv.0 = 2
dv.1 = 1
dv.2 = 10
If sqlcommand('p1',query1,'dt.','dv.') < 0 Then Abort('placemarker: (array) executing')
col = Translate(Word(columnnames_emp,1))
num_rows = p1.col.0
Do i = 1 To num_rows
line = ''
Do j = 1 To Words(columnnames_emp)
col = Translate(Word(columnnames_emp,j))
line = line p1.col.i
End
Say line
End
Say 'placemarker: (file): <'|| query1 ||'>'
ei_file = 'empid.tmp'
dn_file = 'deptno.tmp'
If os = 'UNIX' Then
Do
Address System 'rm' ei_file
Address System 'rm' dn_file
End
Else
Do
Address System 'del' ei_file
Address System 'del' dn_file
End
rc = Charout(ei_file,,1)
rc = Charout(ei_file,'1')
rc = Charout(ei_file)
rc = Charout(dn_file,,1)
rc = Charout(dn_file,'10')
rc = Charout(dn_file)
dt.0 = 2
dt.1 = 'FILE:'||stringdatatypes_empid.db
dt.2 = 'FILE:'||stringdatatypes_deptno.db
dv.0 = 2
dv.1 = ei_file
dv.2 = dn_file
If sqlcommand('p1',query1,'dt.','dv.') < 0 Then Abort('placemarker: (file) executing')
col = Translate(Word(columnnames_emp,1))
num_rows = p1.col.0
Do i = 1 To num_rows
line = ''
Do j = 1 To Words(columnnames_emp)
col = Translate(Word(columnnames_emp,j))
line = line p1.col.i
End
Say line
End
If db = 'ORACLE' Then
Do
rc = sqlvariable('STANDARDPLACEMARKERS',0)
query1 = "select * from RX_EMP where empid = :1 and deptno = :2"
Say 'placemarker: (oracle-normal-number): <'|| query1 ||'>'
If sqlcommand('p1',query1,'#',1,10) < 0 Then Abort('placemarker: (oracle-normal-number) executing')
col = Translate(Word(columnnames_emp,1))
num_rows = p1.col.0
Do i = 1 To num_rows
line = ''
Do j = 1 To Words(columnnames_emp)
col = Translate(Word(columnnames_emp,j))
line = line p1.col.i
End
Say line
End
Say 'placemarker: (oracle-array-number): <'|| query1 ||'>'
dv.0 = 2
dv.1 = 1
dv.2 = 10
If sqlcommand('p1',query1,'.','dv.') < 0 Then Abort('placemarker: (oracle-array-number) executing')
col = Translate(Word(columnnames_emp,1))
num_rows = p1.col.0
Do i = 1 To num_rows
line = ''
Do j = 1 To Words(columnnames_emp)
col = Translate(Word(columnnames_emp,j))
line = line p1.col.i
End
Say line
End
query1 = "select * from RX_EMP where empid = :empid and deptno = :deptno"
Say 'placemarker: (oracle-normal-name): <'|| query1 ||'>'
If sqlcommand('p1',query1,':empid',1,':deptno',10) < 0 Then Abort('placemarker: (oracle-normal-name) executing')
col = Translate(Word(columnnames_emp,1))
num_rows = p1.col.0
Do i = 1 To num_rows
line = ''
Do j = 1 To Words(columnnames_emp)
col = Translate(Word(columnnames_emp,j))
line = line p1.col.i
End
Say line
End
Say 'placemarker: (oracle-array-name): <'|| query1 ||'>'
dn.0 = 2
dn.1 = ':empid'
dn.2 = ':deptno'
dv.0 = 2
dv.1 = 1
dv.2 = 10
If sqlcommand('p1',query1,'.','dn.','dv.') < 0 Then Abort('placemarker: (oracle-array-name) executing')
col = Translate(Word(columnnames_emp,1))
num_rows = p1.col.0
Do i = 1 To num_rows
line = ''
Do j = 1 To Words(columnnames_emp)
col = Translate(Word(columnnames_emp,j))
line = line p1.col.i
End
Say line
End
End
Call disconnect 'c1'
Return
/*-----------------------------------------------------------------*/
transaction: Procedure Expose sqlca. sqlconnect. db columnnames_emp columnnames_dept stringdatatypes_empid. stringdatatypes_deptno.
Say Copies('*',20)
Say 'Testing transactions and sqlexecute...'
Say Copies('*',20)
select1 = "select * from RX_DEPT"
insert1 = "insert into RX_DEPT values (100,'Department 100 - new','dummy')"
insert2 = "insert into RX_DEPT values (200,'Department 200 - new','dummy')"
Call connect 'c1'
rc = sqlvariable('NULLSTRINGOUT','<null>')
Say 'transaction: Contents of RX_DEPT before INSERTs'
If sqlcommand('q1',select1) < 0 Then Abort('transaction: executing')
col = Translate(Word(columnnames_dept,1))
num_rows = q1.col.0
Do i = 1 To num_rows
line = ''
Do j = 1 To Words(columnnames_dept)
col = Translate(Word(columnnames_dept,j))
line = line q1.col.i
End
Say line
End
Say 'transaction: Inserting 2 rows into RX_DEPT'
If sqlcommand('q1',insert1) < 0 Then Abort('transaction: executing')
If sqlvariable('SUPPORTSDMLROWCOUNT') = 1 Then Say 'transaction:' sqlca.rowcount 'row(s) inserted successfully'
Else Say 'transaction: setting of SQLCA.ROWCOUNT for DML not supported; insert succeeded'
If sqlprepare('q2',insert2) < 0 Then Abort('transaction: preparing')
If sqlexecute('q2') < 0 Then Abort('transaction: executing')
If sqlvariable('SUPPORTSDMLROWCOUNT') = 1 Then Say 'transaction:' sqlca.rowcount 'row(s) inserted successfully'
Else Say 'transaction: setting of SQLCA.ROWCOUNT for DML not supported; insert succeeded'
If sqldispose('q2') < 0 Then Abort('transaction: disposing')
Say 'transaction: Contents of RX_DEPT after INSERTs'
If sqlcommand('q1',select1) < 0 Then Abort('transaction: executing')
col = Translate(Word(columnnames_dept,1))
num_rows = q1.col.0
Do i = 1 To num_rows
line = ''
Do j = 1 To Words(columnnames_dept)
col = Translate(Word(columnnames_dept,j))
line = line q1.col.i
End
Say line
End
Say 'transaction: Rolling back transaction'
If sqlgetinfo('c1','SUPPORTSTRANSACTIONS') = 0 Then
Say '***' db 'does not support the use of transactions. Rollback is ignored.'
If sqlrollback() < 0 Then Abort('transaction: rolling back')
Say 'transaction: Contents of RX_DEPT after ROLLBACK'
If sqlcommand('q1',select1) < 0 Then Abort('transaction: executing')
col = Translate(Word(columnnames_dept,1))
num_rows = q1.col.0
Do i = 1 To num_rows
line = ''
Do j = 1 To Words(columnnames_dept)
col = Translate(Word(columnnames_dept,j))
line = line q1.col.i
End
Say line
End
Call disconnect 'c1'
Return
/*-----------------------------------------------------------------*/
errors: Procedure Expose sqlca. sqlconnect. db columnnames_emp columnnames_dept stringdatatypes_empid. stringdatatypes_deptno.
Say Copies('*',20)
Say 'Testing error conditions...'
Say Copies('*',20)
Say 'errors: causing error in sqlconnect()... (may take a while to fail!)'
If sqlconnect('c1','junk','junk','junk','junk') < 0 Then rc = Abort('connect:',1)
Say 'errors: causing errors in sqlcommand()...'
If sqlcommand('q1','select abc from junk') < 0 Then rc = Abort('command:',1)
Call connect 'c1'
If sqlcommand('q1','select abc from junk') < 0 Then rc = Abort('command:',1)
Say 'errors: causing error in sqlexecute()...'
If sqlexecute('q1') < 0 Then rc = Abort('execute:',1)
Say 'errors: causing error in sqlopen()...'
If sqlopen('q1') < 0 Then rc = Abort('open:',1)
Say 'errors: causing error in sqlfetch()...'
If sqlfetch('q1') < 0 Then rc = Abort('fetch:',1)
If sqlprepare('q1','select * from RX_EMP') < 0 Then rc = Abort('prepare:')
If sqlfetch('q1') < 0 Then rc = Abort('fetch:',1)
If sqldispose('q1') < 0 Then rc = Abort('dispose:')
Say 'errors: causing error with placemarkers...'
If sqlvariable('USESPLACEMARKERS') = 0 Then
Say db 'does not support the use of placemarkers in queries. This test ignored.'
Else
Do
rc = sqlvariable('STANDARDPLACEMARKERS',1)
If sqlprepare('q1','select * from RX_EMP where empid = ?') < 0 Then rc = Abort('prepare:')
If sqlopen('q1') < 0 Then rc = Abort('open:',1)
If sqlopen('q1','junk') < 0 Then rc = Abort('open:',1)
If sqlopen('q1','junk',10) < 0 Then rc = Abort('open:',1)
If sqldispose('q1') < 0 Then rc = Abort('dispose:')
End
Call disconnect 'c1'
Return
/*-----------------------------------------------------------------*/
info: Procedure Expose sqlca. sqlconnect. db columnnames_emp columnnames_dept stringdatatypes_empid. stringdatatypes_deptno.
Say Copies('*',20)
Say 'Testing sqlvariable, sqlgetinfo and sqlsetinfo...'
Say Copies('*',20)
valid_info = 'SUPPORTSTRANSACTIONS SUPPORTSSQLGETDATA DBMSNAME',
'DBMSVERSION DESCRIBECOLUMNS DATATYPES'
valid_variable = 'SUPPORTSDMLROWCOUNT SUPPORTSPLACEMARKERS',
'LONGLIMIT AUTOCOMMIT IGNORETRUNCATE NULLSTRINGIN NULLSTRINGOUT',
'STANDARDPLACEMARKERS DEBUG VERSION ROWLIMIT SAVESQL'
Say 'info: Valid values for sqlvariable()...' valid_variable
Do i = 1 to Words(valid_variable)
Say ' Current value for' Word(valid_variable,i) sqlvariable(Word(valid_variable,i))
End
Call connect 'c1'
Say 'info: Valid values for sqlgetinfo()...' valid_info
Do i = 1 to Words(valid_info)
rc = sqlgetinfo('c1',Word(valid_info,i),'desc.')
If rc < 0 Then Say ' ERROR:' sqlca.interrm ':' sqlca.sqlerrm
Else
Do
var = Word(valid_info,i)
Do j = 1 To desc.0
If j = 1 Then Say Left(' Current value for' var,40) desc.j
Else Say Copies(' ',40) desc.j
End
End
End
Call disconnect 'c1'
Return
/*-----------------------------------------------------------------*/
Abort: Procedure Expose sqlca.
Parse Arg message, kontinue
Say 'Error in' message
If sqlca.intcode = -1 Then
Do
Say 'SQLCODE:' sqlca.sqlcode
Say 'SQLERRM:' sqlca.sqlerrm
Say 'SQLTEXT:' sqlca.sqltext
End
Else
Do
Say 'INTCODE:' sqlca.intcode
Say 'INTERRM:' sqlca.interrm
End
If kontinue = 1 Then Return 1
Else Exit 1