!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
DO XSFC-SQR-EXECUTION('TX_XFST_TABLE07',#XSFC-Instance,'Begin','ldtbls.sqr')
display 'test'
do Main
DO XSFC-SQR-EXECUTION('TX_XFST_TABLE07',#XSFC-Instance,'End','ldtbls.sqr')
end-report
begin-procedure Main
Let $UserChoice = ''
Print 'Load Tables' (+2,2)
while ($UserChoice <> 'Q')
do Get-User-Input($UserChoice)
Let $Modified = 'Y'
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
end-procedure Main
begin-procedure Get-User-Input(:$UserChoice)
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
end-procedure Get-User-Input(:$UserChoice)
begin-Procedure Begin-Table-Tran Local
end-Procedure Begin-Table-Tran
begin-Procedure Commit-Table-Tran
COMMIT
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 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
end-procedure Create-All-Tables
begin-procedure 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
end-procedure Load-All-Tables
!EXS;MODE=OPEN;FILE=crtbl.sqc;PATH=C:\demo\prod\SQC\;#Include 'crtbl.sqc' !Create Tables
!************************************************************************
! *
! $Date:: 1/15/98 $ *
! $Revision:: 1 $ *
! $Workfil:: crtbl.sqc $ *
! *
! Notes: *
!***********************************************************************
begin-procedure 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
end-procedure Create-Table01
begin-procedure 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
end-procedure Create-Table02
begin-procedure 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
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
end-procedure Create-Table04
begin-procedure 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
end-procedure Create-Table05
begin-procedure 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
end-procedure Create-Table06
begin-procedure 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
end-procedure Create-Table07
begin-procedure 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
end-procedure Create-Table08
begin-procedure 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
end-procedure Create-Table09
begin-procedure 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
end-procedure Create-Table10
begin-procedure drop_table ($table)
begin-sql on-error=table_not_exists($table)
DROP TABLE [$table]
end-sql
end-procedure
begin-procedure table_exists ($MySqlMsg)
if #_sql-status != 6100
display $MySqlMsg
display 'SQL Error status: '
display #_sql-status
display ' message:'
display $_sql-error
stop
end-if
end-procedure
begin-procedure table_not_exists($table)
display 'Table is not dropped: ' noline
display $table
end-procedure
!EXS;MODE=CLOSE;FILE=crtbl.sqc;PATH=C:\demo\prod\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
open 'c:\sqr\odb\samplew\appts.dat' as 1 for-reading record=3000
do read_appts
close 1
end-procedure Load-Table01
begin-procedure Load-Table02
open 'c:\sqr\odb\samplew\cash.dat' as 1 for-reading record=3000
do read_cash
close 1
end-procedure Load-Table02
begin-procedure Load-Table03
open 'c:\sqr\odb\samplew\customer.dat' as 1 for-reading record=3000
do read_customers
close 1
end-procedure Load-Table03
begin-procedure Load-Table04
open 'c:\sqr\odb\samplew\dept.dat' as 1 for-reading record=3000
do read_dept
close 1
end-procedure Load-Table04
begin-procedure Load-Table05
open 'c:\sqr\odb\samplew\emp.dat' as 1 for-reading record=3000
do read_emp
close 1
end-procedure Load-Table05
begin-procedure Load-Table06
open 'c:\sqr\odb\samplew\ordlines.dat' as 1 for-reading record=3000
do read_ordlines
close 1
end-procedure Load-Table06
begin-procedure Load-Table07
open 'c:\sqr\odb\samplew\orders.dat' as 1 for-reading record=3000
do read_orders
close 1
end-procedure Load-Table07
begin-procedure Load-Table08
open 'c:\sqr\odb\samplew\products.dat' as 1 for-reading record=3000
do read_products
close 1
end-procedure Load-Table08
begin-procedure Load-Table09
open 'c:\sqr\odb\samplew\reminder.dat' as 1 for-reading record=3000
do read_reminders
close 1
end-procedure Load-Table09
begin-procedure Load-Table10
open 'c:\sqr\odb\samplew\stocks.dat' as 1 for-reading record=3000
do read_stocks
close 1
end-procedure Load-Table10
begin-procedure 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
end-procedure
begin-procedure insert_appts
begin-sql
insert into appts (
appt_time,
appt_date,
appointment)
values (
$appt_time,
$appt_date_string,
$appointment)
end-sql
end-procedure
begin-procedure 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
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
end-procedure
begin-procedure 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
end-procedure
begin-procedure 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
end-procedure
begin-procedure 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
end-procedure
begin-procedure insert_dept
begin-sql
insert into dept (
deptno,
dname,
loc)
values (
#deptno,
$dname,
$loc)
end-sql
end-procedure
begin-procedure 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
end-procedure
begin-procedure insert_emp
begin-sql
insert into emp (
empno,
ename,
sal,
comm,
deptno)
values (
#empno,
$ename,
#sal,
#comm,
#deptno)
end-sql
end-procedure
begin-procedure 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
end-procedure
begin-procedure insert_ordlines
begin-sql
insert into ordlines (
order_num,
product_code,
quantity)
values (
#order_num,
#product_code,
#quantity)
end-sql
end-procedure
begin-procedure 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
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
end-procedure
begin-procedure 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
end-procedure
begin-procedure insert_products
begin-sql
insert into products (
product_code,
description,
price)
values (
#product_code,
$description,
#price)
end-sql
end-procedure
begin-procedure 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
end-procedure
begin-procedure insert_reminders
begin-sql
insert into reminders (
remind_date,
reminder
)
values (
$remind_date_string,
$reminder
)
end-sql
end-procedure
begin-procedure 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
end-procedure
begin-procedure insert_stocks
begin-sql
insert into stocks (
stock,
quote_date,
price)
values (
$stock,
$quote_date_string,
#price)
end-sql
end-procedure
begin-procedure Convert-Date-Oracle($dateYYYYMMDD,:$NewDate)
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
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=address.sqc;PATH=C:\demo\prod\SQC\;#Include 'address.sqc'
!***************************************************************
! Address: Converts address fields into disply address
!***************************************************************
!***********************************************************************
! *
! Confidentiality Information: *
! *
! This module is the confidential and proprietary information of *
! PeopleSoft, Inc.; it is not to be copied, reproduced, 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 PeopleSoft. *
! *
! Copyright (c) 1988-1995 PeopleSoft, Inc. All Rights Reserved *
! *
!***********************************************************************
! *
! $Date:: 1/26/96 12:56p $ *
! $Revision:: 2 $ *
! $Workfile:: ADDRESS.SQC $ *
! *
!***********************************************************************
begin-procedure Format-Address
create-array name=addr_new size=5 field=addr_line:char
put ' ' into addr_new(0) addr_line
put ' ' into addr_new(1) addr_line
put ' ' into addr_new(2) addr_line
put ' ' into addr_new(3) addr_line
put ' ' into addr_new(4) addr_line
create-array name=addr size=5 field=addr_line:char
put $Address1 into addr(0) addr_line
put $Address2 into addr(1) addr_line
put $Address3 into addr(2) addr_line
put $Address4 into addr(3) addr_line
move $City to $tempStr
let $tempStr = rtrim($tempStr, ' ')
concat $State with $tempStr ,bxxbb
concat $Zip with $tempStr
put $tempStr into addr(4) addr_line
let #i = 0
let #fillLine = 0
while (#i < 5)
get $tempStr from addr(#i) addr_line
if rtrim($tempStr, ' ') <> ''
put $tempStr into addr_new(#fillLine) addr_line
let #fillLine = #fillLine + 1
end-if
let #i = #i + 1
end-while
get $Address1 from addr_new(0) addr_line
get $Address2 from addr_new(1) addr_line
get $Address3 from addr_new(2) addr_line
get $Address4 from addr_new(3) addr_line
get $Address5 from addr_new(4) addr_line
end-procedure
!EXS;MODE=CLOSE;FILE=address.sqc;PATH=C:\demo\prod\SQC\;
!EXS;MODE=OPEN;FILE=XFSINCL.SQC;PATH=C:\demo\prod\SQC\;#Include 'XFSINCL.SQC'
!************************************************************************ *
! 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 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 Calc-Elapsed-Time($WhichTable,$XFSC-SqrName,#WhichInstance,$XSFC-ShortDateTime,$ElapsedTime)
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',
XFSF_TIME = $ElapsedTime
where XFSF_SQRNAME = $XFSC-SqrName
and XFSF_COUNT = #WhichInstance
end-sql
do XFSC-Commit-Table-Tran
end-if
end-procedure XSFC-SQR-EXECUTION($WhichTable,:#WhichInstance,$Where,$WhichSqr)
begin-procedure XSFC-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
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)
end-procedure XSFC-Get-Curr-DateTime(:$OutDate,:$OutTime,:OutDateTime,:$ShortOutDateTime)
begin-procedure Calc-Elapsed-Time($WhichTable,$XFSC-SqrName,#WhichInstance,$EndDateTime,:$ElapsedTime)
Let $ElapsedTime = '00:00'
begin-select
XFSCTB1.XFSF_STARTDATETIME
Let $BeginDateTime = &XFSCTB1.XFSF_STARTDATETIME
Let $BeginDate = substr(&XFSCTB1.XFSF_STARTDATETIME,1,8)
Let #BeginSec = to_number(substr(&XFSCTB1.XFSF_STARTDATETIME,9,2)) * 3600 +
to_number(substr(&XFSCTB1.XFSF_STARTDATETIME,11,2)) * 60 +
to_number(substr(&XFSCTB1.XFSF_STARTDATETIME,13,2))
Let $EndDate = substr($EndDateTime,1,8)
Let #EndSec = to_number(substr($EndDateTime,9,2)) * 3600 +
to_number(substr($EndDateTime,11,2)) * 60 +
to_number(substr($EndDateTime,13,2))
if ($EndDate > $BeginDate)
Let #EndSec = #EndSec + 24 * 3600
end-if
Let #ElapsedMin = to_number(edit(((#EndSec - #BeginSec) / 60),'00'))
Let #ElapsedHr = to_number(edit((#ElapsedMin / 60),'00'))
Let #ElapsedMin = #ElapsedMin - (#ElapsedHr * 60)
Let $ElapsedTime = edit(#ElapsedHr,'00') || ':' || edit(#ElapsedMin,'00')
FROM [$WhichTable] XFSCTB1
WHERE XFSCTB1.XFSF_SQRNAME = $XFSC-SqrName
AND XFSCTB1.XFSF_COUNT = #WhichInstance
end-select
end-procedure Calc-Elapsed-Time($WhichTable,$XFSC-SqrName,#WhichInstance,$ElapsedTime)
begin-procedure XSFC-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='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
end-procedure XSFC-Convert-To-DefaultDate($InFormat, $SysDate, :$NewDate)
begin-procedure XSFC-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 XSFC-Convert-To-DefaultTime($SysTime, :$NewTime)
begin-procedure XFSC-GET-INSTANCE($WhichTable,$WhichSqr,:#WhichInstance)
Let #WhichInstance = 0
begin-select
MAX(XFSF_COUNT) &XFSFACount
Let #WhichInstance = &XFSFACount
FROM [$WhichTable] XFSTA
WHERE XFSF_SQRNAME = $WhichSqr
end-select
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
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
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
end-procedure XFSC-Update-Sql-Error($XFSCSqlErrorMsg)
begin-procedure XFSC-Insert-Sql-Error($XFSCSqlErrorMsg)
display 'Insert'
display $XFSCSqlErrorMsg
display 'SQL Error Status: ' noline
display #_sql-status
display 'SQL Error Message: ' noline
display $_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\;
Home