#DEFINE XFSCSqrName 'LDTBLS'
#DEFINE XFSCTraceFile 'C:\xfs\TEMP\ldtbls.XPT'
!EXS;MODE=OPEN;FILE=ldtbls.sqr;PATH=C:\demo\prod\sqr\;
!************************************************************************
! *
! $Date:: 1/15/98 $ *
! $Revision:: 1 $ *
! $Workfil:: ldtbls.SQR $ *
! *
! Notes: *
!***********************************************************************
#define ORACLE
#define DOSOS2
#define null
#define TBLVARTYPE VARCHAR2
#define TBLNUMTYPE NUMBER
#define DATE DATE
#define money number (7,2)
begin-setup
end-setup
begin-heading 1
end-heading
begin-footing 1
end-footing
begin-report
if ({XFSCTraceFile}> '')
open {XFSCTraceFile} as 23112
for-writing
record=256:vary
status=#openStatus
if (#openStatus = -1)
Let $DispMsg = 'Unable to open file: ' || {XFSCTraceFile}
display $DispMsg
Let $DispMsg = 'Trace File is not written'
display $DispMsg
Let #XPTNO = 0
Let $DispMsg = 'Sqr Name: ' || {XFSCSqrName}
display $DispMsg
else
Let #XPTNO = 23112
Let $DispMsg = 'Sqr Name: ' || {XFSCSqrName}
write 23112 from $DispMsg
display $DispMsg
Let $DispMsg = 'Write Trace File: ' || {XFSCTraceFile}
write 23112 from $DispMsg
end-if
else
Let $DispMsg = 'Sqr Name: ' || {XFSCSqrName}
display $DispMsg
Let $DispMsg = 'Trace File is not written'
display $DispMsg
Let #XPTNO = 0
end-if
DO XSFP-SQR-TIME(20032,'BEGIN-REPORT')
DO XSFC-SQR-EXECUTION('XFST_TABLE07',#XSFC-Instance,'Begin','ldtbls.sqr')
do Main
DO XSFC-SQR-EXECUTION('XFST_TABLE07',#XSFC-Instance,'End','ldtbls.sqr')
DO XSFP-SQR-TIME(40036,'END-REPORT')
if (#XPTNO = 23112)
close 23112
end-if
end-report
begin-procedure Main
DO XSFP-SQR-TIME(20038,'MAIN')
Let $UserChoice = ''
while ($UserChoice <> 'Q')
do Get-User-Input($UserChoice)
evaluate $UserChoice
when='1'
do Begin-Table-Tran
do Create-All-Tables
do Commit-Table-Tran
break
when='2'
do Begin-Table-Tran
do Load-All-Tables
do Commit-Table-Tran
break
when='3'
do Begin-Table-Tran
do Create-All-Tables
do Commit-Table-Tran
do Begin-Table-Tran
do Load-All-Tables
do Commit-Table-Tran
break
when-other
display 'Invalid User Choice'
break
end-evaluate
end-while
DO XSFP-SQR-TIME(40067,'MAIN')
end-procedure Main
begin-procedure Get-User-Input(:$UserChoice)
DO XSFP-SQR-TIME(20069,'GET-USER-INPUT')
Let $UserInputOK = 'N'
while ($UserInputOK <> 'Y')
input $UserChoice '1=Create Tables; 2.Load Tables; Q=End' type=char maxlen=1
Let $UserChoice = upper($UserChoice)
if (($UserChoice > '0') AND
($UserChoice < '4'))
Let $UserInputOK = 'Y'
else
if ($UserChoice = 'Q')
Let $UserInputOK = 'Y'
else
Let $ErrMsg = '**** ' || $UserChoice || ' is not a valid Entry ****'
show $ErrMsg
end-if
end-if
end-while
DO XSFP-SQR-TIME(40088,'GET-USER-INPUT')
end-procedure Get-User-Input(:$UserChoice)
begin-Procedure Begin-Table-Tran
DO XSFP-SQR-TIME(40092,'BEGIN-TABLE-TRAN')
end-Procedure Begin-Table-Tran
begin-Procedure Commit-Table-Tran
COMMIT
DO XSFP-SQR-TIME(40098,'COMMIT-TABLE-TRAN')
end-Procedure Commit-Table-Tran
!EXS;MODE=OPEN;FILE=crldtbl.sqc;PATH=C:\demo\prod\sqc\;#Include 'crldtbl.sqc' !Create, Load Tables
!************************************************************************
! *
! $Date:: 1/15/98 $ *
! $Revision:: 1 $ *
! $Workfil:: crldtbl.sqc $ *
! *
! Notes: *
!***********************************************************************
begin-procedure Create-All-Tables
DO XSFP-SQR-TIME(20110,'CREATE-ALL-TABLES')
do Create-Table01
do Create-Table02
do Create-Table03
do Create-Table04
do Create-Table05
do Create-Table06
do Create-Table07
do Create-Table08
do Create-Table09
do Create-Table10
DO XSFP-SQR-TIME(40122,'CREATE-ALL-TABLES')
end-procedure Create-All-Tables
begin-procedure Load-All-Tables
DO XSFP-SQR-TIME(20124,'LOAD-ALL-TABLES')
do Load-Table01
do Load-Table02
do Load-Table03
do Load-Table04
do Load-Table05
do Load-Table06
do Load-Table07
do Load-Table08
do Load-Table09
do Load-Table10
DO XSFP-SQR-TIME(40136,'LOAD-ALL-TABLES')
end-procedure Load-All-Tables
!EXS;MODE=OPEN;FILE=crtbl.sqc;PATH=C:\demo\test\sqc\;#Include 'crtbl.sqc' !Create Tables
!************************************************************************
! *
! $Date:: 1/15/98 $ *
! $Revision:: 1 $ *
! $Workfil:: crtbl.sqc $ *
! *
! Notes: *
!***********************************************************************
begin-procedure Create-Table01
DO XSFP-SQR-TIME(20148,'CREATE-TABLE01')
Let $Table = 'appts'
do drop_table($Table)
begin-sql on-error=table_exists($Table)
CREATE TABLE [$Table]
(appt_time {TBLVARTYPE}(5) not null,
appt_date {date} not null,
appointment {TBLVARTYPE}(45) not null)
end-sql
DO XSFP-SQR-TIME(40158,'CREATE-TABLE01')
end-procedure Create-Table01
begin-procedure Create-Table02
DO XSFP-SQR-TIME(20160,'CREATE-TABLE02')
Let $Table = 'cash_receipts'
do drop_table($Table)
begin-sql on-error=table_exists ($Table)
CREATE TABLE [$Table]
(cust_num int not null,
date_received {date} not null,
amount_received {money} not null)
end-sql
DO XSFP-SQR-TIME(40170,'CREATE-TABLE02')
end-procedure Create-Table02
begin-procedure Create-Table03
DO XSFP-SQR-TIME(20172,'CREATE-TABLE03')
Let $Table = 'customers'
do drop_table($Table)
begin-sql on-error=table_exists ($Table)
CREATE TABLE [$Table]
(cust_num int not null,
name {TBLVARTYPE} (30) {null},
addr1 {TBLVARTYPE} (30) {null},
addr2 {TBLVARTYPE} (30) {null},
city {TBLVARTYPE} (16) {null},
state {TBLVARTYPE} (02) {null},
zip {TBLVARTYPE} (10) {null},
phone {TBLVARTYPE} (10) {null},
tot int {null})
end-sql
DO XSFP-SQR-TIME(40188,'CREATE-TABLE03')
end-procedure Create-Table03
begin-procedure Create-Table04
Let $Table = 'dept'
do drop_table($Table)
begin-sql on-error=table_exists ($Table)
CREATE TABLE [$Table]
(deptno int not null,
dname {TBLVARTYPE} (14) not null,
loc {TBLVARTYPE} (20) not null)
end-sql
DO XSFP-SQR-TIME(40203,'CREATE-TABLE04')
end-procedure Create-Table04
begin-procedure Create-Table05
DO XSFP-SQR-TIME(20205,'CREATE-TABLE05')
Let $Table = 'emp'
do drop_table($Table)
begin-sql on-error=table_exists ($Table)
CREATE TABLE [$Table]
(empno int not null,
ename {TBLVARTYPE} (10) not null,
sal {money} not null,
comm {money} {null},
deptno int not null)
end-sql
DO XSFP-SQR-TIME(40219,'CREATE-TABLE05')
end-procedure Create-Table05
begin-procedure Create-Table06
DO XSFP-SQR-TIME(20221,'CREATE-TABLE06')
Let $Table = 'ordlines'
do drop_table($Table)
begin-sql on-error=table_exists ($Table)
CREATE TABLE [$Table]
(order_num int not null,
product_code int not null,
quantity int not null)
end-sql
DO XSFP-SQR-TIME(40231,'CREATE-TABLE06')
end-procedure Create-Table06
begin-procedure Create-Table07
DO XSFP-SQR-TIME(20233,'CREATE-TABLE07')
Let $Table = 'orders'
do drop_table($Table)
begin-sql on-error=table_exists ($Table)
CREATE TABLE [$Table]
(order_date {date} not null,
invoice_date {date} {null},
order_num int not null,
invoice_num int {null},
cust_num int not null,
ship_name {TBLVARTYPE} (30) {null},
ship_addr1 {TBLVARTYPE} (30) {null},
ship_addr2 {TBLVARTYPE} (30) {null},
ship_city {TBLVARTYPE} (16) {null},
ship_state {TBLVARTYPE} (2) {null},
ship_zip {TBLVARTYPE} (10) {null},
terms {TBLVARTYPE} (20) {null},
shipped_via {TBLVARTYPE} (20) {null},
comments {TBLVARTYPE} (60) {null},
empno int {null})
end-sql
DO XSFP-SQR-TIME(40255,'CREATE-TABLE07')
end-procedure Create-Table07
begin-procedure Create-Table08
DO XSFP-SQR-TIME(20257,'CREATE-TABLE08')
Let $Table = 'products'
do drop_table($Table)
begin-sql on-error=table_exists ($Table)
CREATE TABLE [$Table]
(product_code int not null,
description {TBLVARTYPE} (60) not null,
price {money} not null)
end-sql
DO XSFP-SQR-TIME(40266,'CREATE-TABLE08')
end-procedure Create-Table08
begin-procedure Create-Table09
DO XSFP-SQR-TIME(20268,'CREATE-TABLE09')
Let $Table = 'reminders'
do drop_table($Table)
begin-sql on-error=table_exists ($Table)
CREATE TABLE [$Table]
(remind_date {date} not null,
reminder {TBLVARTYPE} (240) not null)
end-sql
DO XSFP-SQR-TIME(40277,'CREATE-TABLE09')
end-procedure Create-Table09
begin-procedure Create-Table10
DO XSFP-SQR-TIME(20279,'CREATE-TABLE10')
Let $Table = 'stocks'
do drop_table($Table)
begin-sql on-error=table_exists ($Table)
CREATE TABLE [$Table]
(stock char (8) not null,
quote_date {date} not null,
price {money} not null)
end-sql
DO XSFP-SQR-TIME(40289,'CREATE-TABLE10')
end-procedure Create-Table10
begin-procedure drop_table ($table)
DO XSFP-SQR-TIME(20291,'DROP_TABLE')
begin-sql on-error=table_not_exists($table)
DROP TABLE [$table]
end-sql
DO XSFP-SQR-TIME(40295,'DROP_TABLE')
end-procedure
begin-procedure table_exists ($MySqlMsg)
DO XSFP-SQR-TIME(20297,'TABLE_EXISTS')
if #_sql-status != 6100
display $MySqlMsg
display 'SQL Error status: '
display #_sql-status
display ' message:'
display $_sql-error
stop
end-if
DO XSFP-SQR-TIME(40306,'TABLE_EXISTS')
end-procedure
begin-procedure table_not_exists($table)
DO XSFP-SQR-TIME(20308,'TABLE_NOT_EXISTS')
display 'Table is not dropped: ' noline
display $table
DO XSFP-SQR-TIME(40311,'TABLE_NOT_EXISTS')
end-procedure
!EXS;MODE=CLOSE;FILE=crtbl.sqc;PATH=C:\demo\test\sqc\;
!EXS;MODE=OPEN;FILE=ldtbl.sqc;PATH=C:\demo\prod\sqc\;#Include 'ldtbl.sqc' !Load Tables
!************************************************************************
! *
! $Date:: 1/15/98 $ *
! $Revision:: 1 $ *
! $Workfil:: ldtbl.sqc $ *
! *
! Notes: *
!***********************************************************************
begin-procedure Load-Table01
DO XSFP-SQR-TIME(20324,'LOAD-TABLE01')
open 'c:\sqr\odb\samplew\appts.dat' as 1 for-reading record=3000
do read_appts
close 1
DO XSFP-SQR-TIME(40328,'LOAD-TABLE01')
end-procedure Load-Table01
begin-procedure Load-Table02
DO XSFP-SQR-TIME(20330,'LOAD-TABLE02')
open 'c:\sqr\odb\samplew\cash.dat' as 1 for-reading record=3000
do read_cash
close 1
DO XSFP-SQR-TIME(40334,'LOAD-TABLE02')
end-procedure Load-Table02
begin-procedure Load-Table03
DO XSFP-SQR-TIME(20336,'LOAD-TABLE03')
open 'c:\sqr\odb\samplew\customer.dat' as 1 for-reading record=3000
do read_customers
close 1
DO XSFP-SQR-TIME(40340,'LOAD-TABLE03')
end-procedure Load-Table03
begin-procedure Load-Table04
DO XSFP-SQR-TIME(20342,'LOAD-TABLE04')
open 'c:\sqr\odb\samplew\dept.dat' as 1 for-reading record=3000
do read_dept
close 1
DO XSFP-SQR-TIME(40346,'LOAD-TABLE04')
end-procedure Load-Table04
begin-procedure Load-Table05
DO XSFP-SQR-TIME(20348,'LOAD-TABLE05')
open 'c:\sqr\odb\samplew\emp.dat' as 1 for-reading record=3000
do read_emp
close 1
DO XSFP-SQR-TIME(40352,'LOAD-TABLE05')
end-procedure Load-Table05
begin-procedure Load-Table06
DO XSFP-SQR-TIME(20354,'LOAD-TABLE06')
open 'c:\sqr\odb\samplew\ordlines.dat' as 1 for-reading record=3000
do read_ordlines
close 1
DO XSFP-SQR-TIME(40358,'LOAD-TABLE06')
end-procedure Load-Table06
begin-procedure Load-Table07
DO XSFP-SQR-TIME(20360,'LOAD-TABLE07')
open 'c:\sqr\odb\samplew\orders.dat' as 1 for-reading record=3000
do read_orders
close 1
DO XSFP-SQR-TIME(40364,'LOAD-TABLE07')
end-procedure Load-Table07
begin-procedure Load-Table08
DO XSFP-SQR-TIME(20366,'LOAD-TABLE08')
open 'c:\sqr\odb\samplew\products.dat' as 1 for-reading record=3000
do read_products
close 1
DO XSFP-SQR-TIME(40370,'LOAD-TABLE08')
end-procedure Load-Table08
begin-procedure Load-Table09
DO XSFP-SQR-TIME(20372,'LOAD-TABLE09')
open 'c:\sqr\odb\samplew\reminder.dat' as 1 for-reading record=3000
do read_reminders
close 1
DO XSFP-SQR-TIME(40376,'LOAD-TABLE09')
end-procedure Load-Table09
begin-procedure Load-Table10
DO XSFP-SQR-TIME(20378,'LOAD-TABLE10')
open 'c:\sqr\odb\samplew\stocks.dat' as 1 for-reading record=3000
do read_stocks
close 1
DO XSFP-SQR-TIME(40382,'LOAD-TABLE10')
end-procedure Load-Table10
begin-procedure read_appts
DO XSFP-SQR-TIME(20385,'READ_APPTS')
show 'Inserting records from file appts.dat into appts ...'
move 0 to #inserts
move 0 to #tot-recs
Let $sepchar = '|'
while 1 = 1
read 1 into $x:3000
if #end-file = 1
break ! End of file.
end-if
unstring $x by $sepchar into -
$appt_date_string -
$appt_time -
$appointment
!
! Use the strtodate function to move the date into a date variable.
! This will ensure the date will be in the correct DB format.
!
do Convert-Date-Oracle($appt_date_string,$OracDate)
Let $appt_date_string = $OracDate
do insert_appts
add 1 to #tot-recs
end-while
show ' Total records inserted: ' #tot-recs edit 999,999
DO XSFP-SQR-TIME(40409,'READ_APPTS')
end-procedure
begin-procedure insert_appts
DO XSFP-SQR-TIME(20411,'INSERT_APPTS')
begin-sql
insert into appts (
appt_time,
appt_date,
appointment)
values (
$appt_time,
$appt_date_string,
$appointment)
end-sql
DO XSFP-SQR-TIME(40422,'INSERT_APPTS')
end-procedure
begin-procedure read_cash
DO XSFP-SQR-TIME(20424,'READ_CASH')
show 'Inserting records from file cash.dat into cash_receipts ...'
move 0 to #inserts
move 0 to #tot-recs
while 1 = 1
read 1 into $x:3000
if #end-file = 1
break ! End of file.
end-if
unstring $x by $sepchar into -
$cust_num -
$date_received_string -
$amount_received
move $cust_num to #cust_num
!
! Use the strtodate function to move the date into a date variable.
! This will ensure the date will be in the correct DB format.
!
do Convert-Date-Oracle($date_received_string,$OracDate)
Let $date_received_string = $OracDate
move $amount_received to #amount_received
do insert_cash
add 1 to #tot-recs
end-while
show ' Total records inserted: ' #tot-recs edit 999,999
DO XSFP-SQR-TIME(40451,'READ_CASH')
end-procedure
begin-procedure insert_cash
begin-sql
insert into cash_receipts (
cust_num,
date_received,
amount_received)
values (
#cust_num,
$date_received_string,
#amount_received)
end-sql
DO XSFP-SQR-TIME(40465,'INSERT_CASH')
end-procedure
begin-procedure read_customers
DO XSFP-SQR-TIME(20467,'READ_CUSTOMERS')
show 'Inserting records from file customer.dat into customers ...'
move 0 to #inserts
move 0 to #tot-recs
while 1 = 1
read 1 into $x:3000
if #end-file = 1
break ! End of file.
end-if
unstring $x by $sepchar into -
$cust_num -
$name -
$addr1 -
$addr2 -
$city -
$state -
$zip -
$phone -
$tot
move $cust_num to #cust_num
move $tot to #tot
do insert_customers
add 1 to #tot-recs
end-while
show ' Total records inserted: ' #tot-recs edit 999,999
DO XSFP-SQR-TIME(40492,'READ_CUSTOMERS')
end-procedure
begin-procedure insert_customers
DO XSFP-SQR-TIME(20494,'INSERT_CUSTOMERS')
begin-sql
insert into customers (
cust_num,
name,
addr1,
addr2,
city,
state,
zip,
phone,
tot)
values (
#cust_num,
$name,
$addr1,
$addr2,
$city,
$state,
$zip,
$phone,
#tot)
end-sql
DO XSFP-SQR-TIME(40517,'INSERT_CUSTOMERS')
end-procedure
begin-procedure read_dept
DO XSFP-SQR-TIME(20519,'READ_DEPT')
show 'Inserting records from file dept.dat into dept ...'
move 0 to #inserts
move 0 to #tot-recs
while 1 = 1
read 1 into $x:3000
if #end-file = 1
break ! End of file.
end-if
unstring $x by $sepchar into -
$deptno -
$dname -
$loc
move $deptno to #deptno
do insert_dept
add 1 to #tot-recs
end-while
show ' Total records inserted: ' #tot-recs edit 999,999
DO XSFP-SQR-TIME(40537,'READ_DEPT')
end-procedure
begin-procedure insert_dept
begin-sql
insert into dept (
deptno,
dname,
loc)
values (
#deptno,
$dname,
$loc)
end-sql
DO XSFP-SQR-TIME(40551,'INSERT_DEPT')
end-procedure
begin-procedure read_emp
DO XSFP-SQR-TIME(20554,'READ_EMP')
show 'Inserting records from file emp.dat into emp ...'
move 0 to #inserts
move 0 to #tot-recs
while 1 = 1
read 1 into $x:3000
if #end-file = 1
break ! End of file.
end-if
unstring $x by $sepchar into -
$empno -
$ename -
$sal -
$comm -
$deptno
move $empno to #empno
move $sal to #sal
move $comm to #comm
move $deptno to #deptno
do insert_emp
add 1 to #tot-recs
end-while
show ' Total records inserted: ' #tot-recs edit 999,999
DO XSFP-SQR-TIME(40577,'READ_EMP')
end-procedure
begin-procedure insert_emp
begin-sql
insert into emp (
empno,
ename,
sal,
comm,
deptno)
values (
#empno,
$ename,
#sal,
#comm,
#deptno)
end-sql
DO XSFP-SQR-TIME(40595,'INSERT_EMP')
end-procedure
begin-procedure read_ordlines
DO XSFP-SQR-TIME(20598,'READ_ORDLINES')
show 'Inserting records from file ordlines.dat into ordlines ...'
move 0 to #inserts
move 0 to #tot-recs
while 1 = 1
read 1 into $x:3000
if #end-file = 1
break ! End of file.
end-if
unstring $x by $sepchar into -
$order_num -
$product_code -
$quantity
move $order_num to #order_num
move $product_code to #product_code
move $quantity to #quantity
do insert_ordlines
add 1 to #tot-recs
end-while
show ' Total records inserted: ' #tot-recs edit 999,999
DO XSFP-SQR-TIME(40618,'READ_ORDLINES')
end-procedure
begin-procedure insert_ordlines
begin-sql
insert into ordlines (
order_num,
product_code,
quantity)
values (
#order_num,
#product_code,
#quantity)
end-sql
DO XSFP-SQR-TIME(40632,'INSERT_ORDLINES')
end-procedure
begin-procedure read_orders
DO XSFP-SQR-TIME(20634,'READ_ORDERS')
show 'Inserting records from file orders.dat into orders ...'
move 0 to #inserts
move 0 to #tot-recs
while 1 = 1
read 1 into $x:3000
if #end-file = 1
break ! End of file.
end-if
unstring $x by $sepchar into -
$order_num -
$order_date_string -
$invoice_num -
$invoice_date_string -
$cust_num -
$ship_name -
$ship_addr1 -
$ship_addr2 -
$ship_city -
$ship_state -
$ship_zip -
$terms -
$shipped_via -
$comments -
$empno
move $order_num to #order_num
move $invoice_num to #invoice_num
move $cust_num to #cust_num
move $empno to #empno
!
! Use the strtodate function to move the date into a date variable.
! This will ensure the date will be in the correct DB format.
!
do Convert-Date-Oracle($order_date_string,$OracDate)
Let $order_date_string = $OracDate
do Convert-Date-Oracle($invoice_date_string,$OracDate)
Let $invoice_date_string = $OracDate
do insert_orders
add 1 to #tot-recs
end-while
show ' Total records inserted: ' #tot-recs edit 999,999
DO XSFP-SQR-TIME(40677,'READ_ORDERS')
end-procedure
begin-procedure insert_orders
begin-sql
insert into orders (
order_num,
order_date,
invoice_num,
invoice_date,
cust_num,
ship_name,
ship_addr1,
ship_addr2,
ship_city,
ship_state,
ship_zip,
terms,
shipped_via,
comments,
empno)
values (
#order_num,
$order_date_string,
#invoice_num,
$invoice_date_string,
#cust_num,
$ship_name,
$ship_addr1,
$ship_addr2,
$ship_city,
$ship_state,
$ship_zip,
$terms,
$shipped_via,
$comments,
#empno)
end-sql
DO XSFP-SQR-TIME(40715,'INSERT_ORDERS')
end-procedure
begin-procedure read_products
DO XSFP-SQR-TIME(20717,'READ_PRODUCTS')
show 'Inserting records from file products.dat into products ...'
move 0 to #inserts
move 0 to #tot-recs
while 1 = 1
read 1 into $x:3000
if #end-file = 1
break ! End of file.
end-if
unstring $x by $sepchar into -
$product_code -
$description -
$price
move $product_code to #product_code
move $price to #price
do insert_products
add 1 to #tot-recs
end-while
show ' Total records inserted: ' #tot-recs edit 999,999
DO XSFP-SQR-TIME(40736,'READ_PRODUCTS')
end-procedure
begin-procedure insert_products
begin-sql
insert into products (
product_code,
description,
price)
values (
#product_code,
$description,
#price)
end-sql
DO XSFP-SQR-TIME(40750,'INSERT_PRODUCTS')
end-procedure
begin-procedure read_reminders
DO XSFP-SQR-TIME(20752,'READ_REMINDERS')
show 'Inserting records from file reminder.dat into reminders ...'
move 0 to #inserts
move 0 to #tot-recs
while 1 = 1
read 1 into $x:3000
if #end-file = 1
break ! End of file.
end-if
unstring $x by $sepchar into -
$remind_date_string -
$reminder
do Convert-Date-Oracle($remind_date_string,$OracDate)
Let $remind_date_string = $OracDate
do insert_reminders
add 1 to #tot-recs
end-while
show ' Total records inserted: ' #tot-recs edit 999,999
DO XSFP-SQR-TIME(40772,'READ_REMINDERS')
end-procedure
begin-procedure insert_reminders
begin-sql
insert into reminders (
remind_date,
reminder
)
values (
$remind_date_string,
$reminder
)
end-sql
DO XSFP-SQR-TIME(40786,'INSERT_REMINDERS')
end-procedure
begin-procedure read_stocks
DO XSFP-SQR-TIME(20788,'READ_STOCKS')
show 'Inserting records from file stocks.dat into stocks ...'
move 0 to #inserts
move 0 to #tot-recs
while 1 = 1
read 1 into $x:3000
if #end-file = 1
break ! End of file.
end-if
unstring $x by $sepchar into -
$stock -
$quote_date_string -
$price
move $price to #price
do Convert-Date-Oracle($quote_date_string,$OracDate)
Let $quote_date_string = $OracDate
do insert_stocks
#ifDEF ORACLE
add 1 to #inserts
if #inserts >= 50
commit
move 0 to #inserts
end-if
#endif
add 1 to #tot-recs
end-while
show ' Total records inserted: ' #tot-recs edit 999,999
DO XSFP-SQR-TIME(40817,'READ_STOCKS')
end-procedure
begin-procedure insert_stocks
DO XSFP-SQR-TIME(20819,'INSERT_STOCKS')
begin-sql
insert into stocks (
stock,
quote_date,
price)
values (
$stock,
$quote_date_string,
#price)
end-sql
DO XSFP-SQR-TIME(40830,'INSERT_STOCKS')
end-procedure
begin-procedure Convert-Date-Oracle($dateYYYYMMDD,:$NewDate)
DO XSFP-SQR-TIME(20832,'CONVERT-DATE-ORACLE')
Let $YY = substr($dateYYYYMMDD,3,2)
Let $MM = substr($dateYYYYMMDD,5,2)
Let $DD = substr($dateYYYYMMDD,7,2)
evaluate $MM
when='01'
Let $Mon = 'JAN'
break
when='02'
Let $Mon = 'FEB'
break
when='03'
Let $Mon = 'MAR'
break
when='04'
Let $Mon = 'APR'
break
when='05'
Let $Mon = 'MAY'
break
when='06'
Let $Mon = 'JUN'
break
when='07'
Let $Mon = 'JUL'
break
when='08'
Let $Mon = 'AUG'
break
when='09'
Let $Mon = 'SEP'
break
when='10'
Let $Mon = 'OCT'
break
when='11'
Let $Mon = 'NOV'
break
when='12'
Let $Mon = 'DEC'
break
when-other
Let $Mon = ''
end-evaluate
Let $NewDate = $DD || '-' || $MON || '-' || $YY
DO XSFP-SQR-TIME(40877,'CONVERT-DATE-ORACLE')
end-procedure Convert-Date-Oracle($dateYYYYMMDD)
!EXS;MODE=CLOSE;FILE=ldtbl.sqc;PATH=C:\demo\prod\sqc\;
!EXS;MODE=CLOSE;FILE=crldtbl.sqc;PATH=C:\demo\prod\sqc\;
!EXS;MODE=OPEN;FILE=XFSINCL.SQC;PATH=C:\demo\prod\sqc\;#Include 'XFSINCL.SQC'
#define XFSORACLE
#define XFSRDBMSNAME 'ORACLE'
!************************************************************************ *
! Confidentiality Information: *
! *
! This module is the confidential and proprietary information of *
! TCS INC (USA), which is supplied as part of SQR Tools. This file *
! would be automatically added with modifications into SQR files by SQR *
! Tools. This file is not to be copied reprodcued, added as include *
! file in any SQR or transmitted in any form, by any means, in whole or *
! in part, nor is it to be used for any purpose other than that for *
! which it is expressly provided without the written permission of *
! Textile Computer Systems (USA). *
! *
!***********************************************************************
#ifndef XFSRDBMSNAME
#define XFSINFORMIX
#define XFSRDBMSNAME 'INFORMIX'
#endif
begin-procedure XSFC-SQR-EXECUTION($WhichTable,:#WhichInstance,$Where,$WhichSqr)
DO XSFP-SQR-TIME(20902,'XSFC-SQR-EXECUTION')
do XSFC-Get-Curr-DateTime($XSFC-Date,$XSFC-Time,$XSFC-DateTime,$XSFC-ShortDateTime)
Let $XFSC-SqrName = upper($WhichSqr)
if ($Where = 'Begin')
do XFSC-GET-INSTANCE($WhichTable,$XFSC-SqrName,#WhichInstance)
Let #WhichInstance = #WhichInstance + 1
do XFSC-Begin-Table-Tran
if (rtrim($_username,' ') = '')
Let $XFSC-UserName = ' '
else
Let $XFSC-UserName = $_username
end-if
Let $XFSCSqlErrorMsg = 'Table: ' || $WhichTable
begin-sql On-Error=XFSC-Insert-Sql-Error($XFSCSqlErrorMsg)
INSERT INTO [$WhichTable]
(XFSF_SQRNAME,
XFSF_COUNT,
XFSF_RDBMS,
XFSF_USERNAME,
XFSF_PLATFORM,
XFSF_STARTDATETIME,
XFSF_ENDDATETIME,
XFSF_TYPE,
XFSF_TIME)
VALUES ($XFSC-SqrName,
#WhichInstance,
$_sqr-database,
$XFSC-UserName,
$_sqr-platform,
$XSFC-ShortDateTime,
$XSFC-ShortDateTime,
'I',
'00:00')
end-sql
do XFSC-Commit-Table-Tran
end-if
if ($Where = 'End')
do XFSC-Begin-Table-Tran
Let $XFSCSqlErrorMsg = 'Table: ' || $WhichTable
begin-sql On-Error=XFSC-Update-Sql-Error($XFSCSqlErrorMsg)
update [$WhichTable]
set XFSF_ENDDATETIME = $XSFC-ShortDateTime,
XFSF_TYPE = 'C'
where XFSF_SQRNAME = $XFSC-SqrName
and XFSF_COUNT = #WhichInstance
end-sql
do XFSC-Commit-Table-Tran
end-if
DO XSFP-SQR-TIME(40952,'XSFC-SQR-EXECUTION')
end-procedure XSFC-SQR-EXECUTION($WhichTable,:#WhichInstance,$Where,$WhichSqr)
begin-procedure XSFC-Get-Curr-DateTime(:$OutDate,:$OutTime,:$OutDateTime,:$ShortOutDateTime)
DO XSFP-SQR-TIME(20954,'XSFC-GET-CURR-DATETIME')
#ifdef XFSALLBASE
DATE-TIME () 'YYYY-MM-DD' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
Let $OutDate = &SysDate
Let $OutTime = &SysTime
#endif
#ifdef XFSDB2
DATE-TIME () 'YYYY-MM-DD' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
Let $OutDate = &SysDate
Let $OutTime = &SysTime
#endif
#ifdef XFSINFORMIX
DATE-TIME () 'YYYY-MM-DD' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
Let $OutDate = &SysDate
Let $OutTime = &SysTime
#endif
#ifdef XFSINFORMIX_NON_ANSI
DATE-TIME () 'YYYY-MM-DD' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
Let $OutDate = &SysDate
Let $OutTime = &SysTime
#endif
#ifdef XFSINGRES
DATE-TIME () 'DD-MON-YYYY' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
Let $SysDate = &SysDate
Let $OutTime = &SysTime
do XSFC-Convert-To-DefaultDate('DD-MON-YYYY', $SysDate, $NewDate)
Let $OutDate = $NewDate
#endif
#ifdef XFSORACLE
DATE-TIME () 'DD-MON-YYYY' &SysDate
DATE-TIME () 'HH:MI PM' &SysTime
Let $SysDate = &SysDate
Let $SysTime = &SysTime
do XSFC-Convert-To-DefaultDate('DD-MON-YYYY', $SysDate, $NewDate)
Let $OutDate = $NewDate
do XSFC-Convert-To-DefaultTime($SysTime, $NewTime)
Let $OutTime = $NewTime
#endif
#ifdef XFSRDB
DATE-TIME () 'DD-MON-YYYY' &SysDate
DATE-TIME () 'HH:MI' &SysTime
Let $SysDate = &SysDate
Let $OutTime = &SysTime || ':00'
do XSFC-Convert-To-DefaultDate('DD-MON-YYYY', $SysDate, $NewDate)
Let $OutDate = $NewDate
#endif
#ifdef XFSSQLBASE
DATE-TIME () 'DD-MON-YYYY' &SysDate
DATE-TIME () 'HH:MI PM' &SysTime
Let $SysDate = &SysDate
Let $SysTime = &SysTime
do XSFC-Convert-To-DefaultDate('DD-MON-YYYY', $SysDate, $NewDate)
Let $OutDate = $NewDate
do XSFC-Convert-To-DefaultTime($SysTime, $NewTime)
Let $OutTime = $NewTime
#endif
#ifdef XFSSYBASE
DATE-TIME () 'DD-MON-YYYY' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
Let $SysDate = &SysDate
Let $OutTime = &SysTime
do XSFC-Convert-To-DefaultDate('DD-MON-YYYY', $SysDate, $NewDate)
Let $OutDate = $NewDate
#endif
Let $OutDateTime = $OutDate || ' ' || $OutTime
Let $ShortOutDateTime = substr($OutDateTime,1,4) ||
substr($OutDateTime,6,2) ||
substr($OutDateTime,9,2) ||
substr($OutDateTime,12,2) ||
substr($OutDateTime,15,2) ||
substr($OutDateTime,18,2)
DO XSFP-SQR-TIME(41031,'XSFC-GET-CURR-DATETIME')
end-procedure XSFC-Get-Curr-DateTime(:$OutDate,:$OutTime,:OutDateTime,:$ShortOutDateTime)
begin-procedure XSFC-Convert-To-DefaultDate($InFormat, $SysDate, :$NewDate)
DO XSFP-SQR-TIME(21033,'XSFC-CONVERT-TO-DEFAULTDATE')
if ($InFormat = 'DD-MON-YYYY')
Let $InMon = upper(substr($SysDate,4,3))
evaluate $InMon
when='JAN'
Let $NewMon = '01'
break
when='FEB'
Let $NewMon = '02'
break
when='MAR'
Let $NewMon = '03'
break
when='APR'
Let $NewMon = '04'
break
when='MAY'
Let $NewMon = '05'
break
when='JUNE'
Let $NewMon = '06'
break
when='JULY'
Let $NewMon = '07'
break
when='AUG'
Let $NewMon = '08'
break
when='SEP'
Let $NewMon = '09'
break
when='OCT'
Let $NewMon = '10'
break
when='NOV'
Let $NewMon = '11'
break
when='DEC'
Let $NewMon = '12'
break
when-other
display 'Wrong date format: ' noline
display $SysDate
STOP
break
end-evaluate
Let $NewDate = substr($SysDate,8,4) || '-' ||
$NewMon || '-' || substr($SysDate,1,2)
else
Let $NewDate = $SysDate
end-if
DO XSFP-SQR-TIME(41084,'XSFC-CONVERT-TO-DEFAULTDATE')
end-procedure XSFC-Convert-To-DefaultDate($InFormat, $SysDate, :$NewDate)
begin-procedure XSFC-Convert-To-DefaultTime($SysTime, :$NewTime)
DO XSFP-SQR-TIME(21086,'XSFC-CONVERT-TO-DEFAULTTIME')
Let $SysAmPm = substr($SysTime,7,2)
Let #SysHour = to_number(substr($SysTime,1,2))
if (($SysAmPm = 'PM') AND (#SysHour < 12))
Let #SysHour = #SysHour + 12
end-if
if (($SysAmPm = 'AM') AND (#SysHour > 11))
Let #SysHour = 0
end-if
Let #SysMinute = to_number(substr($SysTime,4,2))
Let $NewTime = edit(#SysHour,'00') || ':' || edit(#SysMinute,'00') ||
':00'
DO XSFP-SQR-TIME(41100,'XSFC-CONVERT-TO-DEFAULTTIME')
end-procedure XSFC-Convert-To-DefaultTime($SysTime, :$NewTime)
begin-procedure XFSC-GET-INSTANCE($WhichTable,$WhichSqr,:#WhichInstance)
DO XSFP-SQR-TIME(21102,'XFSC-GET-INSTANCE')
Let #WhichInstance = 0
begin-select
MAX(XFSF_COUNT) &XFSFACount
Let #WhichInstance = &XFSFACount
FROM [$WhichTable] XFSTA
WHERE XFSF_SQRNAME = $WhichSqr
end-select
DO XSFP-SQR-TIME(41110,'XFSC-GET-INSTANCE')
end-procedure XFSC-GET-INSTANCE($WhichTable,$WhichSqr,#WhichInstance)
begin-Procedure XFSC-Begin-Table-Tran
#ifdef XFSALLBASE
#endif
#ifdef XFSDB2
#endif
#ifdef XFSINFORMIX
#endif
#ifdef XFSINFORMIX_NON_ANSI
begin-sql
BEGIN WORK
end-sql
#endif
#ifdef XFSINGRES
#endif
#ifdef XFSORACLE
#endif
#ifdef XFSRDB
#endif
#ifdef XFSSQLBASE
#endif
#ifdef XFSSYBASE
begin-sql
BEGIN TRANSACTION
end-sql
#endif
DO XSFP-SQR-TIME(41139,'XFSC-BEGIN-TABLE-TRAN')
end-Procedure XFSC-Begin-Table-Tran
begin-Procedure XFSC-Commit-Table-Tran
#ifdef XFSALLBASE
COMMIT
#endif
#ifdef XFSDB2
COMMIT
#endif
#ifdef XFSINFORMIX
begin-sql
COMMIT WORK
end-sql
#endif
#ifdef XFSINFORMIX_NON_ANSI
begin-sql
COMMIT WORK
end-sql
#endif
#ifdef XFSINGRES
COMMIT
#endif
#ifdef XFSORACLE
COMMIT
#endif
#ifdef XFSRDB
COMMIT
#endif
#ifdef XFSSQLBASE
COMMIT
#endif
#ifdef XFSSYBASE
begin-sql
COMMIT TRANSACTION
end-sql
#endif
DO XSFP-SQR-TIME(41178,'XFSC-COMMIT-TABLE-TRAN')
end-Procedure XFSC-Commit-Table-Tran
begin-procedure XFSC-Update-Sql-Error($XFSCSqlErrorMsg)
display 'Update'
display $XFSCSqlErrorMsg
display 'SQL Error Status: ' noline
display #_sql-status
display 'SQL Error Message: ' noline
display $_sql-error
DO XSFP-SQR-TIME(41189,'XFSC-UPDATE-SQL-ERROR')
end-procedure XFSC-Update-Sql-Error($XFSCSqlErrorMsg)
begin-procedure XFSC-Insert-Sql-Error($XFSCSqlErrorMsg)
DO XSFP-SQR-TIME(21191,'XFSC-INSERT-SQL-ERROR')
display 'Insert'
display $XFSCSqlErrorMsg
display 'SQL Error Status: ' noline
display #_sql-status
display 'SQL Error Message: ' noline
display $_sql-error
DO XSFP-SQR-TIME(41198,'XFSC-INSERT-SQL-ERROR')
end-procedure XSFC-Insert-Sql-Error($XFSCSqlErrorMsg)
!EXS;MODE=CLOSE;FILE=XFSINCL.SQC;PATH=C:\demo\prod\sqc\;
!EXS;MODE=CLOSE;FILE=ldtbls.sqr;PATH=C:\demo\prod\sqr\;
#ifndef XFSRDBMSNAME
#define XFSINFORMIX
#define XFSRDBMSNAME 'INFORMIX'
#endif
begin-procedure XSFP-SQR-TIME(#ProfLineNo,$ProfMsg)
do XSFP-Get-Curr-DateTime($XSFP-Date,$XSFP-Time,$XSFP-DateTime,$XSFP-ShortDateTime)
if (#ProfLineNo > 40000)
Let $DispMsg = 'Leaving from ' || $ProfMsg || ' line number ' || to_char(#ProfLineNo - 40000)
else
if (#ProfLineNo > 20000)
Let $DispMsg = 'Entering into ' || $ProfMsg || ' line number ' || to_char(#ProfLineNo - 20000)
else
Let $DispMsg = 'Finished executing line number ' || to_char(#ProfLineNo)
end-if
end-if
Let $DispMsg = $DispMsg || ' at ' || $XSFP-DateTime
display $DispMsg
if (#_XPTNO = 23112)
if (length($DispMsg) > 255)
Let $Msg = substr($DispMsg,1,253) || '..'
end-if
write 23112 from $DispMsg
end-if
end-procedure XSFP-SQR-TIME(#ProfLineNo,$ProfMsg)
begin-procedure XSFP-Get-Curr-DateTime(:$OutDate,:$OutTime,:$OutDateTime,:$ShortOutDateTime)
#ifdef XFSALLBASE
DATE-TIME () 'YYYY-MM-DD' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
Let $OutDate = &SysDate
Let $OutTime = &SysTime
#endif
#ifdef XFSDB2
DATE-TIME () 'YYYY-MM-DD' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
Let $OutDate = &SysDate
Let $OutTime = &SysTime
#endif
#ifdef XFSINFORMIX
DATE-TIME () 'YYYY-MM-DD' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
Let $OutDate = &SysDate
Let $OutTime = &SysTime
#endif
#ifdef XFSINFORMIX_NON_ANSI
DATE-TIME () 'YYYY-MM-DD' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
Let $OutDate = &SysDate
Let $OutTime = &SysTime
#endif
#ifdef XFSINGRES
DATE-TIME () 'DD-MON-YYYY' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
do XSFP-Convert-To-DefaultDate('DD-MON-YYYY', $SysDate, $NewDate)
Let $OutDate = &NewDate
Let $OutTime = &SysTime
#endif
#ifdef XFSORACLE
DATE-TIME () 'DD-MON-YYYY' &SysDate
DATE-TIME () 'HH:MI PM' &SysTime
Let $SysDate = &SysDate
Let $SysTime = &SysTime
do XSFP-Convert-To-DefaultDate('DD-MON-YYYY', $SysDate, $NewDate)
Let $OutDate = $NewDate
do XSFP-Convert-To-DefaultTime($SysTime, $NewTime)
Let $OutTime = $NewTime
#endif
#ifdef XFSRDB
DATE-TIME () 'DD-MON-YYYY' &SysDate
DATE-TIME () 'HH:MI' &SysTime
Let $SysDate = &SysDate
Let $OutTime = &SysTime || ':00'
do XSFP-Convert-To-DefaultDate('DD-MON-YYYY', $SysDate, $NewDate)
Let $OutDate = $NewDate
#endif
#ifdef XFSSQLBASE
DATE-TIME () 'DD-MON-YYYY' &SysDate
DATE-TIME () 'HH:MI PM' &SysTime
Let $SysDate = &SysDate
Let $SysTime = &SysTime
do XSFP-Convert-To-DefaultDate('DD-MON-YYYY', $SysDate, $NewDate)
Let $OutDate = $NewDate
do XSFP-Convert-To-DefaultTime($SysTime, $NewTime)
Let $OutTime = $NewTime
#endif
#ifdef XFSSYBASE
DATE-TIME () 'DD-MON-YYYY' &SysDate
DATE-TIME () 'HH:MI:SS' &SysTime
Let $SysDate = &SysDate
Let $OutTime = &SysTime
do XSFP-Convert-To-DefaultDate('DD-MON-YYYY', $SysDate, $NewDate)
Let $OutDate = $NewDate
#endif
Let $OutDateTime = $OutDate || ' ' || $OutTime
Let $OutDateTime = $OutDate || ' ' || $OutTime
Let $ShortOutDateTime = substr($OutDateTime,1,4) ||
substr($OutDateTime,6,2) ||
substr($OutDateTime,9,2) ||
substr($OutDateTime,12,2) ||
substr($OutDateTime,15,2) ||
substr($OutDateTime,18,2)
end-procedure XSFP-Get-Curr-DateTime(:$OutDate,:$OutTime,:$OutDateTime,:$ShortOutDateTime)
begin-procedure XSFP-Convert-To-DefaultDate($InFormat, $SysDate, :$NewDate)
if ($InFormat = 'DD-MON-YYYY')
Let $InMon = upper(substr($SysDate,4,3))
evaluate $InMon
when='JAN'
Let $NewMon = '01'
break
when='FEB'
Let $NewMon = '02'
break
when='MAR'
Let $NewMon = '03'
break
when='APR'
Let $NewMon = '04'
break
when='MAY'
Let $NewMon = '05'
break
when='JUN'
Let $NewMon = '06'
break
when='JUL'
Let $NewMon = '07'
break
when='AUG'
Let $NewMon = '08'
break
when='SEP'
Let $NewMon = '09'
break
when='OCT'
Let $NewMon = '10'
break
when='NOV'
Let $NewMon = '11'
break
when='DEC'
Let $NewMon = '12'
break
when-other
display 'Wrong date format: ' noline
display $SysDate
STOP
break
end-evaluate
Let $NewDate = substr($SysDate,8,4) || '-' ||
$NewMon || '-' || substr($SysDate,1,2)
else
Let $NewDate = $SysDate
end-if
end-procedure XSFP-Convert-To-DefaultDate($InFormat, $SysDate, :$NewDate)
begin-procedure XSFP-Convert-To-DefaultTime($SysTime, :$NewTime)
Let $SysAmPm = substr($SysTime,7,2)
Let #SysHour = to_number(substr($SysTime,1,2))
if (($SysAmPm = 'PM') AND (#SysHour < 12))
Let #SysHour = #SysHour + 12
end-if
if (($SysAmPm = 'AM') AND (#SysHour > 11))
Let #SysHour = 0
end-if
Let #SysMinute = to_number(substr($SysTime,4,2))
Let $NewTime = edit(#SysHour,'00') || ':' || edit(#SysMinute,'00') || ':00'
end-procedure XSFP-Convert-To-DefaultTime($SysTime, :$NewTime)
Home