com!online 2002 April

home *** CD-ROM | disk | FTP | other *** search

/ com!online 2002 April / comcd0402.iso / vollver / adabas / f_0001 / env / sqlpl.enh < prev next >

Wrap

Text File | 1999-11-01 | 170.0 KB | 2,394 lines

ENG|02 | 0 SQLPL.enh 10.05.01 1998-04-24 ENG|02 | 1| | HELP Resources of SQL-PL ENG|02 | 2| | ------------------------ ENG|02 | 4| I| ##11Workbench ENG|02 | 5| I| ##04Editor ENG|02 | 6| I| ##0203SQL-PL Routines ENG|02 | 7| I| ##05FORM ENG|02 | 8| I| ##09SQL Statements ENG|02 | 9| I| ##08REPORT Statements ENG|02 | 10| I| ##0208Restrictions ENG|02 | 11| I| ##0209Debugging Aids ENG|02 | 12| I| ##0211DB^Procedures ENG|02 | 14|RI| ##0404Help on Help ENG|0203 | 1| | SQL-PL Routines ENG|0203 | 2| | --------------- ENG|0203 | 4| M| #00Introductory Example ENG|0203 | 5| M| #01Statements ENG|0203 | 6| I| ##0207SQL-PL Functions ENG|0203 | 7| M| #03Standard^Functions ENG|0203 | 8| M| #04Syntax^Description ENG|0203 | 9|RM| #05Options and#05Debugging Aids ENG|020300 | 1| | ENG|020300 | 2| E| PROC customer.account PARM ( @cno ); ENG|020300 | 3| E| /* Program CUSTOMER - module ACCOUNT ENG|020300 | 4| E| /* Calculates the bank balance of a customer from the database ENG|020300 | 6| E| WRITE CLEAR, '--- bank balance ---'; ENG|020300 | 7| E| REPEAT WRITE NL(2), 'customer no.: '; ENG|020300 | 8| E| READ @cno; ENG|020300 | 9| E| IF @cno IS NULL THEN RETURN; /* end condition ENG|020300 | 10| E| SQL ( SELECT DIRECT bank_balance ENG|020300 | 11| E| INTO :balance /* searching the ENG|020300 | 12| E| FROM customer /* balance of the ENG|020300 | 13| E| WHERE KEY cno = :@cno ); /* customer in DB ENG|020300 | 14| E| CASE $RC OF ENG|020300 | 15| E| 0 : WRITE 'bank balance:' , HI (balance); /* customer known ENG|020300 | 16| E| 100 : WRITE HI('customer not found'); /* customer unknown ENG|020300 | 17|SE| END; ENG|020300 | 19| E| UNTIL $RC NOT IN (0,100); ENG|020300 | 20| E| WRITE NL,'Please call your EDP support'; /* error event ENG|020300 | 21|R | ENG|020301 | 1| | SQL-PL Statements ENG|020301 | 2| | ----------------- ENG|020301 | 4| I| ##02030201Assignments of Variables/Vectors/Values ##02030107CALL-FORM -Statement ENG|020301 | 5| I| ##02030106CALL-PROC -Statement ##02030104CASE -Statement ENG|020301 | 6| I| ##02030207EDIT -Statement ##02030212EXEC -Statement ENG|020301 | 7| I| ##02030112FOR -Statement ##02030103IF -Statement ENG|020301 | 8| I| ##02030209LTSORT/GTSORT -Statement ##02030205QUERY Commands ENG|020301 | 9| I| ##02030101REPEAT -Statement ##02030203READ -Statement ENG|020301 | 10| I| ##02030105RETURN -Statement ##02030210SET -Statement ENG|020301 | 11| I| ##02030109SKIP -Statement ##02030204SQL -Statement ENG|020301 | 12| I| ##02030111STOP -Statement ##02030108SWITCH -Statement ENG|020301 | 13| I| ##02030114SWITCHCALL -Statement ##02030211System^Time ENG|020301 | 14| I| ##02030116TRY-CATCH -Statement ##02030102WHILE -Statement ENG|020301 | 15| I| ##02030202WRITE -Statement ##02030206Processing Files ENG|020301 | 16| I| ##02030113Function Calls ##02030110Condition ENG|020301 | 17|SI| Calling ##02030115DB^Procedures ENG|020301 | 19|RI| ##0203040202->^Syntax ENG|02030101 | 1| | The REPEAT Statement is used for repeating a sequence of statements. ENG|02030101 | 2| | Repetition is ended once the termination condition is met. The condi- ENG|02030101 | 3| | tion is checked a f t e r the statement(s) has (have) been processed. ENG|02030101 | 5| | Example : ENG|02030101 | 7| | ENG|02030101 | 8| E| i := 1; ENG|02030101 | 9| E| REPEAT ENG|02030101 | 10| E| i := i + 1; ENG|02030101 | 11| E| WRITE 'i = ', i ENG|02030101 | 12| E| UNTIL i = 10; ENG|02030101 | 13| | ENG|02030101 | 15|RI| ##0203040209->^Syntax ENG|02030102 | 1| | The WHILE Statement is used for repeating a sequence of statements. ENG|02030102 | 2| | Repetition is ended once the termination condition is met. The ENG|02030102 | 3| | condition is checked p r i o r to processing the statement(s). ENG|02030102 | 5| | Example : ENG|02030102 | 6| | ENG|02030102 | 8| E| i := 1; ENG|02030102 | 9| E| WHILE i < 10 ENG|02030102 | 10| E| DO BEGIN ENG|02030102 | 11| E| i := i + 1; ENG|02030102 | 12| E| WRITE 'i = ', i; ENG|02030102 | 13| E| END; ENG|02030102 | 14| | ENG|02030102 | 16|RI| ##0203040210->^Syntax ENG|02030103 | 1| | The IF Statement is used for executing statements depending on a ENG|02030103 | 2| | condition. If the condition is met, it performs the (sequence of) ENG|02030103 | 3| | statement(s) of the THEN branch, otherwise the (sequence of) ENG|02030103 | 4| | statement(s) of the ELSE branch (if any). ENG|02030103 | 6| | Examples : ENG|02030103 | 8| | ENG|02030103 | 9| E| IF (i > j) OR (i = j) ENG|02030103 | 10| E| THEN BEGIN ENG|02030103 | 11| E| WRITE 'i is greater than or equal to j'; ... ENG|02030103 | 12| E| END ENG|02030103 | 13| E| ELSE write 'i is smaller than j'; ENG|02030103 | 14| E| IF name = 'CARL' ENG|02030103 | 15| E| THEN WRITE 'NAME <> ''carl'''; ENG|02030103 | 16|S | ENG|02030103 | 19| | In case of doubt ELSE belongs to the last IF. ENG|02030103 | 21|RI| ##0203040206->^Syntax ENG|02030104 | 1| | The CASE Statement is used for executing statements depending on the ENG|02030104 | 2| | value of an expression. That (sequence of) statement(s) is performed ENG|02030104 | 3| | which contains the value of the expression in its value list. If this ENG|02030104 | 4| | value does not occur in any value list, the OTHERWISE case is performed ENG|02030104 | 5| | (if any). ENG|02030104 | 7| | Examples : ENG|02030104 | 9| | ENG|02030104 | 10| E| CASE i OF CASE UPPER (s) OF ENG|02030104 | 11| E| 1.. 3 : WRITE 'i in (1,2,3)'; 'ONE', 'TWO' : BEGIN ... END; ENG|02030104 | 12| E| 4,5,6 : BEGIN 'THREE' : ... ; ENG|02030104 | 13| E| WRITE 'i in (4,5,6)'; ... 'FOUR' : ... ; ENG|02030104 | 14| E| END; OTHERWISE : BEGIN ... END; ENG|02030104 | 15| E| END; END; ENG|02030104 | 16|S | ENG|02030104 | 19|RI| ##0203040207->^Syntax ENG|02030105 | 1| | The RETURN statement ends the execution of SQL-PL subroutines and ENG|02030105 | 2| | branches to the calling environment. The RETURN statement is performed ENG|02030105 | 3| | implicitly at the end of every SQL-PL routine. ENG|02030105 | 5| | Example : ENG|02030105 | 6| | ENG|02030105 | 7| E| IF $KEY = 'F3' ENG|02030105 | 8| E| THEN RETURN; ENG|02030105 | 9| | ENG|02030105 | 11| | A value provided as the result of the function can be passed via RETURN ENG|02030105 | 12| I| in ##0207SQL-PL functions. ENG|02030105 | 14| | Example : ENG|02030105 | 15| | ENG|02030105 | 16| E| FUNCTION stdlib.plus (@sum,@addend); ENG|02030105 | 17|SE| RETURN (@sum+@addend); ENG|02030105 | 19| | ENG|02030105 | 21|RI| ##0203040213->^Syntax ENG|02030106 | 1| | The CALL PROC statement can be used for branching to SQL-PL routines. ENG|02030106 | 3| | Example : ENG|02030106 | 5| | ENG|02030106 | 6| E| CALL PROC processing; ENG|02030106 | 7| | ENG|02030106 | 9| | Once processing of the called routine is completed, the calling routine ENG|02030106 | 10| | is continued. If the routine does not exist, the program is cancelled. ENG|02030106 | 11| | Upon calling the routine, current parameters may be specified. The ENG|02030106 | 12| | values of these parameter values are assigned to the formal parameters ENG|02030106 | 13| | of the called routine. ENG|02030106 | 15| | Example : ENG|02030106 | 17|S | Definition : PROC customer.reservation PARMS (@cno, @today, @counter); ENG|02030106 | 19| | Call : CALL PROC reservation PARMS (cno, DATE(yymmdd), counter+1); ENG|02030106 | 21|RI| ##0203040216->^Syntax ENG|02030107 | 1| | The CALL FORM statement can be used for branching to FORM routines. ENG|02030107 | 3| | Example : ENG|02030107 | 5| | CALL FORM main_menu; ENG|02030107 | 7| | Once processing of the called routine is completed, the calling routine ENG|02030107 | 8| | is continued. ENG|02030107 | 10| | Calling a form which requires a user response implicitly terminates the ENG|02030107 | 11| | current database transaction, unless the call comes from an SQL-PL ENG|02030107 | 12| | routine compiled with the option AUTOCOMMIT OFF. ENG|02030107 | 14| | If the form does not exist, the program is cancelled. Current ENG|02030107 | 15|S | parameters can be specified as for CALL PROC. ENG|02030107 | 19| | When calling forms, options may be specified which influence the ENG|02030107 | 20| | processing of the routine. ENG|02030107 | 22| | Examples : ENG|02030107 | 25| | ENG|02030107 | 26| E| CALL FORM form1 OPTIONS (NOINIT, ACCEPT (F1,F3,ENTER), MARK (3)); ENG|02030107 | 27| E| CALL FORM form3 OPTIONS ( PRINT (LINEFEED 2, NEWPAGE), NOINIT): ENG|02030107 | 28| E| CALL FORM form4 ( PRINT ('X', LINEFEED 2)) PARMS (cno, DATE(yymmdd)); ENG|02030107 | 29| | ENG|02030107 | 31| I| ##0506Further^Information^ about the Call Options. ENG|02030107 | 33|RI| ##0203040217->^Syntax ENG|02030108 | 1| | The SWITCH statement ends the execution of a program and branches to a ENG|02030108 | 2| | successor program. ENG|02030108 | 4| | Examples : ENG|02030108 | 6| | ENG|02030108 | 7| E| SWITCH customer CALL PROC processing; ENG|02030108 | 8| E| SWITCH product.customer CALL FORM main_menu; ENG|02030108 | 9| | ENG|02030108 | 11| | Thus several small programs may be put together to form larger ones. ENG|02030108 | 12| | Options can be specified as for CALL FORM; current parameters are ENG|02030108 | 13| | specified as for CALL. ENG|02030108 | 15|RI| ##0203040218->^Syntax ENG|02030109 | 1| | The SKIP statement performs an unconditional branch to the specified ENG|02030109 | 2| | label. SKIP can only move toward the end of the routine; i.e. it is not ENG|02030109 | 3| | possible that loops are programmed. ENG|02030109 | 5| | Example : ENG|02030109 | 7| | ENG|02030109 | 8| E| PROC x.y ENG|02030109 | 9| E| ... ENG|02030109 | 10| E| IF $RC <> 0 THEN SKIP error; ENG|02030109 | 11| E| ... ENG|02030109 | 12| E| error : WRITE 'error (' & $RC & ')'; RETURN; ENG|02030109 | 13| | ENG|02030109 | 15| | Skip labels are only allowed outside control structures. ENG|02030109 | 17|RI| ##0203040212->^Syntax ENG|02030110 | 1| | The SQL-PL language allows conditions to be formulated within IF, ENG|02030110 | 2| | REPEAT, and WHILE Statements. ENG|02030110 | 5| M| logical predicates : #01AND^OR^NOT ENG|02030110 | 7| M| comparison predicates : #02=^<>^<^>^<=^>=^IN^BETWEEN^LIKE^EOF ENG|02030110 | 9| M| type predicates : #03ALPHA^NUMERIC^FIXED^DATE^TIME^NULL^MODIFIED ENG|02030110 | 11| M| vector predicates : #04ALL^EACH^ANY^SOME^ONE ENG|02030110 | 13| M| trigger predicates : #05FIRSTCALL^LASTCALL^INSERTING^UPDATING^DELETING ENG|02030110 | 15| | Examples : ENG|02030110 | 17|S | ENG|02030110 | 19| E| IF pno = 4711 AND name = 'Smith' THEN ...; ENG|02030110 | 21| E| REPEAT ... UNTIL (name IS NULL) OR ($KEY = F3); ENG|02030110 | 23| E| WHILE (name IS ALPHA) AND NOT (name IN ('Meyer','Miller')) DO ...; ENG|02030110 | 25| E| IF (name LIKE 'T*') OR (name IN ('Meyer','Myer')) THEN ...; ENG|02030110 | 27| E| IF number BETWEEN 1 AND 99 THEN ... ; ENG|02030110 | 29| E| IF ALL list (1..5) IN ('0',NULL) ENG|02030110 | 31| E| IF DELETING AND FIRSTCALL ENG|02030110 | 33| | ENG|02030110 | 34|S | Processing can be controlled by bracketing. ENG|02030110 | 37|RI| ##0203040146->^Syntax ENG|0203011001 | 1| | Logical Predicates ENG|0203011001 | 3| | The expression yields true, if ENG|0203011001 | 5| | AND : both operands are true. ENG|0203011001 | 7| | OR : one of the operands is true. ENG|0203011001 | 9| | NOT : the operand is false. ENG|0203011001 | 12|RI| ##0203040160->^Syntax ENG|0203011002 | 1| | Comparison Predicates ENG|0203011002 | 3| | The expression yields true, if ENG|0203011002 | 5| | = : both operands are equal ENG|0203011002 | 7| | <> : the operands differ. ENG|0203011002 | 9| | < : the first operand is smaller than the second operand. ENG|0203011002 | 11| | > : the first operand is greater than the second operand. ENG|0203011002 | 13| | <= : the first operand is smaller or equal to the second operand. ENG|0203011002 | 15| | >= : the first operand is smaller or equal to the second operand. ENG|0203011002 | 16|SI| ##0203040161->^Syntax ENG|0203011002 | 19| | IN : the first operand is an element in the given list. ENG|0203011002 | 20| I| ##0203040151->^Syntax ENG|0203011002 | 22| | BETWEEN : the first operand is restricted to the given interval. ENG|0203011002 | 23| I| ##0203040154->^Syntax ENG|0203011002 | 25| | LIKE : the first operand matches the given pattern. ENG|0203011002 | 26| I| ##0203040155->^Syntax ENG|0203011002 | 28| | EOF : the end of the given file has been reached. ENG|0203011002 | 29|RI| ##0203040145->^Syntax ENG|0203011003 | 1| | Type Predicates ENG|0203011003 | 3| | The expression yields true, if ENG|0203011003 | 5| | ALPHA : the operand may be transformed into a string. ENG|0203011003 | 7| | NUMERIC : the operand may be transformed into a number. ENG|0203011003 | 9| | FIXED : the operand may be transformed into a number ENG|0203011003 | 10| | with the given precision. ENG|0203011003 | 12| | DATE : the operand can be used as a date. ENG|0203011003 | 14| | TIME : the operand can be used as a time. ENG|0203011003 | 16|S | NULL : the operand is undefined. ENG|0203011003 | 19| | MODIFIED : the operand has been modified during the last FORM call. ENG|0203011003 | 22|RI| ##0203040150->^Syntax ENG|0203011004 | 1| | Vector Predicates ENG|0203011004 | 3| | The expression yields true, if ENG|0203011004 | 5| | ALL, EACH : each vector element conforms to the given condition. ENG|0203011004 | 7| | ANY, SOME : at least one vector element conforms to the given condition. ENG|0203011004 | 9| | ONE : exactly one vector element conforms to the given condition. ENG|0203011004 | 12|RI| ##0203040145->^Syntax ENG|0203011005 | 1| | Trigger Predicates ENG|0203011005 | 3| | These predicates are meaningful only inside a trigger. They yield false ENG|0203011005 | 4| | in any other case. ENG|0203011005 | 6| | The expression yields true, if ENG|0203011005 | 8| | FIRSTCALL : the trigger gets called by a single row command ENG|0203011005 | 9| | or the first row of a set oriented command. ENG|0203011005 | 11| | LASTCALL : the trigger is called by a single row command ENG|0203011005 | 12| | or the last row of a set oriented command. ENG|0203011005 | 14| | INSERTING, UPDATING, DELETING : the trigger is called by ENG|0203011005 | 15|S | an INSERT, UPDATE or DELETE respectively. ENG|0203011005 | 19|RI| ##0203040116->^Syntax ENG|02030111 | 1| | The STOP statement ends the execution of an SQL-PL program. If the ENG|02030111 | 2| | program was called without going through the workbench, then a return ENG|02030111 | 3| | code and message are passed to the calling operating system ENG|02030111 | 4| | environment. ENG|02030111 | 5| | This return code must only assume values from the interval 0..127; the ENG|02030111 | 6| | return code is implicitly mapped to this interval, if necessary. ENG|02030111 | 8| | Examples: ENG|02030111 | 10| | ENG|02030111 | 11| E| IF $RC < 0 STOP ($COUNT); ENG|02030111 | 12| E| THEN STOP ($RC, $RT); STOP; ENG|02030111 | 13| | ENG|02030111 | 15|RI| ##0203040214->^Syntax ENG|02030112 | 1| | The FOR Statement is used for repeating a sequence of statements. The ENG|02030112 | 2| | number of repetitions is calculated b e f o r e passing the loop for ENG|02030112 | 3| | the first time, using the upper and lower limit specifications. Upon ENG|02030112 | 4| | each pass the loop variable is either increased or decreased by 1. ENG|02030112 | 6| | Examples : ENG|02030112 | 8| | ENG|02030112 | 9| E| FOR i := 1 to 5 DO ENG|02030112 | 10| E| WRITE 'I =',i; ENG|02030112 | 11| E| FOR i := LENGTH (string) DOWNTO 1 DO ENG|02030112 | 12| E| BEGIN ENG|02030112 | 13| E| WRITE NL, SUBSTR (string, i, 1); ENG|02030112 | 14| E| END; ENG|02030112 | 15| | ENG|02030112 | 17|RI| ##0203040211->^Syntax ENG|02030113 | 1| | Calling Functions ENG|02030113 | 3| | An SQL-PL function call is preceded by the percent (%) sign. ENG|02030113 | 5| | Function calls can be used in expressions; but they may also occur as ENG|02030113 | 6| | statements in their own right. ENG|02030113 | 8| | Examples : ENG|02030113 | 10| | ENG|02030113 | 11| E| sum := %sum (a, b, c, d, e); ENG|02030113 | 12| E| %list ('customers'); ENG|02030113 | 13| | ENG|02030113 | 15| | When used in expressions, a value must be returned to the function via ENG|02030113 | 16|SI| the RETURN statement ( --> ##0207SQL-PL Functions ). ENG|02030113 | 19|RI| ##0203040141->^Syntax ENG|02030114 | 1| | The SWITCHCALL statement branches to another program. Once this program ENG|02030114 | 2| | is completed, the processing is continued after the SWITCHCALL ENG|02030114 | 3| | statement. All variables keep the values assigned to them. ENG|02030114 | 5| | Examples : ENG|02030114 | 7| | ENG|02030114 | 8| E| SWITCHCALL db_io CALL PROC insert_customer (cno, cname, rc); ENG|02030114 | 9| E| SWITCHCALL global.info CALL FORM start; ENG|02030114 | 10| | ENG|02030114 | 12| | Thus standard programs can be put together which can be used in other ENG|02030114 | 13| | programs. ENG|02030114 | 15| | Options can be specified as for CALL FORM; current parameters are ENG|02030114 | 16|S | specified as for CALL. ENG|02030114 | 19|RI| ##0203040219->^Syntax ENG|02030115 | 1| | Calling DB Procedures ENG|02030115 | 3| | The CALL DBPROC statement can be used to execute DB procedures. ENG|02030115 | 5| | Example : ENG|02030115 | 7| | ENG|02030115 | 8| E| CALL DBPROC hotel.reservation ( @customer, @theDate, @hotel, @sum ); ENG|02030115 | 10| E| CALL DBPROC author.hotel.reservation ( @customer, @theDate, @hotel, @sum ) ENG|02030115 | 11| E| WITH COMMIT; ENG|02030115 | 12| | ENG|02030115 | 14| | After the call $RC and $RT are set to values. If the DB procedure was ENG|02030115 | 15| | terminated without an error, the output variables contain new values. ENG|02030115 | 17|S | If WITH COMMIT was specified, a successful execution is concluded with ENG|02030115 | 19| | an implicit COMMIT WORK ( an implicit ROLLBACK WORK is not performed in ENG|02030115 | 20| | case of a failure ). ENG|02030115 | 23|RI| ##0203040225->^Syntax ENG|02030116 | 1| | Exception Handling ENG|02030116 | 3| | The TRY-CATCH statement allows the programmer to handle runtime errors ENG|02030116 | 4| | without aborting the running program. ENG|02030116 | 6| | If an error or a STOP statement occurs inside the TRY statement, then ENG|02030116 | 7| | control is transfered to the CATCH clause with the appropriate error ENG|02030116 | 8| | number. If a selector matches the error number, then the corresponding ENG|02030116 | 9| | statement executed, execution continues after the CATCH clause. ENG|02030116 | 10| | Otherwise, the error is not handled and the program is aborted ( unless ENG|02030116 | 11| | the error is handled in an enclosing TRY-CATCH statement ). Selectors ENG|02030116 | 12| | are specified similar to the CASE statement, an OTHERWISE branch is not ENG|02030116 | 13| | allowed. ENG|02030116 | 15| | Beispiel : ENG|02030116 | 17|S | ENG|02030116 | 19| E| TRY ENG|02030116 | 20| E| BEGIN ENG|02030116 | 21| E| CALL PROC do_command; ENG|02030116 | 22| E| END ENG|02030116 | 23| E| CATCH @errno OF ENG|02030116 | 24| E| 16102 : ERROR := 'Out of memory'; ENG|02030116 | 25| E| 16801 : ERROR := 'Command cancelled'; ENG|02030116 | 26| E| END; ENG|02030116 | 27| | ENG|02030116 | 30|RI| ##0203040240->^Syntax ENG|02030201 | 1| | SQL-PL distinguishes between simple variables and vector variables. ENG|02030201 | 2| | Vector variables are a collection of simple variables. They can have up ENG|02030201 | 3| | to 255 elements. The scope of variables may be limited. Therefore there ENG|02030201 | 4| | are global, local-dynamic, and local-static variables. ENG|02030201 | 6| | Global variables are known everywhere within a program; values can be ENG|02030201 | 7| | assigned to them anywhere. ENG|02030201 | 9| | Local-dynamic variables are only known within one module and when ENG|02030201 | 10| | branching back to the calling environment, they are deleted. For ENG|02030201 | 11| | recursive calls of the module, a new shaping of the variables is ENG|02030201 | 12|S | generated each time! ENG|02030201 | 19| | Local-static variables, too, are only accessible within the module they ENG|02030201 | 20| | have been declared. In contrast to the dynamic variables, they keep the ENG|02030201 | 21| | values assigned to them even if the procedure temporarily branches back ENG|02030201 | 22| | to the calling environment. They only lose their values, when the ENG|02030201 | 23| | program is left. With recursive calls of the module, the same (!) ENG|02030201 | 24| | variable may always be used. ENG|02030201 | 26| | Attention: Local-static variables declared in SQL-PL functions ENG|02030201 | 27|S | keep their values beyond program changes. ENG|02030201 | 37| | A value assignment assigns a value of an expression to ENG|02030201 | 38| | an SQL-PL variable. ENG|02030201 | 40| | Examples : ENG|02030201 | 42| | ENG|02030201 | 43| E| name (i) := lastname (i) & firstname (i);/* global vectors ENG|02030201 | 44| E| @name := 'Smith'; /* local dynamic variable ENG|02030201 | 45| E| @@name (1..10) := NULL; /* local static variable ENG|02030201 | 46| E| account_new (1..no) := account_old (1..no); ENG|02030201 | 47| E| @i := @i + 1; ENG|02030201 | 48| E| j := NULL; ENG|02030201 | 49| E| time := TIME (hhmmss); ENG|02030201 | 50| E| sum := %sum (v1,v2,v3,v4); /* function call ENG|02030201 | 51| | ENG|02030201 | 53|S | Rem.: Prior to the first assignment, variables implicitly have the ENG|02030201 | 55| | NULL value. ENG|02030201 | 56| | SQL-PL variables have no particular type; i.e. both digits and strings ENG|02030201 | 57| | may be assigned to them. ENG|02030201 | 59| I| ##0203040203<assign^stmt>->^Syntax ENG|02030201 | 60|RI| ##0203040205<vector^assign^stmt>->^Syntax ENG|02030202 | 1| | In contrast to the CALL FORM statement, the WRITE statement writes to ENG|02030202 | 2| | the screen in a line-oriented manner. ENG|02030202 | 4| | The following control instructions may be used : ENG|02030202 | 6| | NL : causes one line feed. ENG|02030202 | 7| | NL (n) : causes n line feeds. ENG|02030202 | 8| | COL (n) : determines the output column. ENG|02030202 | 9| | HIGH : has the effect that the next value is bright on output ENG|02030202 | 10| | HI (v,...) : has the effect that several values are bright on ENG|02030202 | 11| | output. ENG|02030202 | 12| | CLEAR : clears the screen. ENG|02030202 | 13| | NOCLEAR has the effect that the screen is not cleared, ENG|02030202 | 14| | when the screen margin is reached by NL. ENG|02030202 | 15| | POS (l, c) : determines the coordinates of the screen ENG|02030202 | 16| | for the next output. ENG|02030202 | 17|S | SIZE (n) : determines the length of the next output field. ENG|02030202 | 19| | PAUSE : requires acknowledgement (e.g. ENTER) by the user. ENG|02030202 | 23| | OPEN has the effect that subsequent WRITE output ENG|02030202 | 24| | is not displayed on the screen ENG|02030202 | 25| | (until CLOSE or PAUSE is specified). ENG|02030202 | 26| | CLOSE releases screen output of the collected data. ENG|02030202 | 27| | HOLD has the effect that a release key must be ENG|02030202 | 28| | pressed in order to automatically clear a ENG|02030202 | 29| | screen page filled with writing. ENG|02030202 | 31|RI| ##0203040248->^Syntax ENG|02030203 | 1| | The READ statement allows data to be read from the screen into SQL-PL ENG|02030203 | 2| | variables. After each input, a release key is to be pressed. ENG|02030203 | 4| | The following control instructions may be used : ENG|02030203 | 6| | NL : causes one line feed. ENG|02030203 | 7| | NL (n) : causes n line feeds. ENG|02030203 | 8| | COL (n) : determines the output column. ENG|02030203 | 9| | HIGH : has the effect that the characters in the input ENG|02030203 | 10| | field appear brightly. ENG|02030203 | 11| | DARK : has the effect that the characters in the input ENG|02030203 | 12| | field are not visible. ENG|02030203 | 13| | CLEAR : clears the screen. ENG|02030203 | 14| | POS (l, c) : determines the coordinates of the screen ENG|02030203 | 15| | for the next input field. ENG|02030203 | 16|S | SIZE (n) : determines the length of the next input field. ENG|02030203 | 19| | OUTIN (v) : has the effect that the current value of the variable ENG|02030203 | 20| | is displayed and may be overwritten. ENG|02030203 | 21| | PROMPT 'c' : may be used together with OUTIN. The specified ENG|02030203 | 22| | character will be displayed when the variable ENG|02030203 | 23| | has the NULL value. ENG|02030203 | 25|RI| ##0203040249->^Syntax ENG|02030204 | 1| | Database Commands with SQL ENG|02030204 | 2| | -------------------------- ENG|02030204 | 4| M| #01Accessing^the^Database ENG|02030204 | 5| M| #02Dynamic^SQL^Commands ENG|02030204 | 6| M| #03Dynamic^FETCH^Commands ENG|02030204 | 7| M| #04Mass^Fetch ENG|02030204 | 8| M| Routines for #05SQL^Error^Handling ENG|02030204 | 9| M| #06Multi-DB^Accessibility ENG|02030204 | 10|RI| ##0203040227->^Syntax ENG|0203020401 | 1| | The SQL interface of the ADABAS database system is integrated into ENG|0203020401 | 2| | SQL-PL. It handles the data exchange between database and SQL-PL ENG|0203020401 | 3| | program. After each SQL call a check can be made via $RC in order to ENG|0203020401 | 4| | know which code has been returned by the DBMS. ENG|0203020401 | 6| | Static SQL commands: ENG|0203020401 | 8| | These commands are checked while storing the routine. Input and output ENG|0203020401 | 9| | variables may occur in the command. These SQL variables are denoted by ENG|0203020401 | 10| | a colon within the command. ENG|0203020401 | 17|S | Examples : ENG|0203020401 | 19| | ENG|0203020401 | 20| E| SQL ( SELECT DIRECT name ENG|0203020401 | 21| E| INTO :c_name ENG|0203020401 | 22| E| FROM customer ENG|0203020401 | 23| E| KEY cno = :c_no ); ENG|0203020401 | 26| E| SQL ( INSERT customer ENG|0203020401 | 27| E| SET cno = :c_no, name = :c_name, city = :c_city ); ENG|0203020401 | 29| E| SQL ( UPDATE customer ENG|0203020401 | 30| E| SET city = 'Washington', account = :@sum ENG|0203020401 | 31| E| KEY cno = :c_no ); ENG|0203020401 | 33| E| SQL ( SELECT res (cno, name) ENG|0203020401 | 34| E| FROM customer ENG|0203020401 | 35|SE| WHERE city = :@c_city ); ENG|0203020401 | 37| E| SQL ( FETCH FIRST res ENG|0203020401 | 38| E| INTO :c_no, :c_name ); ENG|0203020401 | 39| | ENG|0203020401 | 41|RI| ##0203040227->^Syntax ENG|0203020402 | 1| | Dynamic SQL commands: ENG|0203020402 | 3| | These commands are only checked at execution time. ENG|0203020402 | 4| | !!! No SQL variables may occur in dynamic commands. !!! ENG|0203020402 | 6| | Example : ENG|0203020402 | 8| | ENG|0203020402 | 9| E| command := 'SELECT * FROM customer WHERE name = '''; ENG|0203020402 | 11| E| READ name; ENG|0203020402 | 13| E| SQL EXECUTE IMMEDIATE command & name & ''''; ENG|0203020402 | 14|S | ENG|0203020402 | 19| | Dynamic SQL commands: ENG|0203020402 | 21| | These commands are only checked at execution time. ENG|0203020402 | 23| | Execution without preparation : ENG|0203020402 | 25| M| #01EXECUTE^IMMEDIATE ENG|0203020402 | 27| M| #01EXECUTE^IMMEDIATE^USING ENG|0203020402 | 30| | Preparation and multiple execution : ENG|0203020402 | 32| M| #02PREPARE^FROM ENG|0203020402 | 34|SM| #02EXECUTE^USING ENG|0203020402 | 37| M| #02DESCRIBE^INTO ENG|0203020402 | 39|RI| ##0203040229->^Syntax ENG|020302040201| 1| | Immediate execution without SQL variables : ENG|020302040201| 3| | ENG|020302040201| 4| E| cmd := 'SELECT * FROM customer WHERE name = ''''; ENG|020302040201| 6| E| READ name; ENG|020302040201| 8| E| SQL EXECUTE IMMEDIATE cmd & name & ''''; ENG|020302040201| 9| | ENG|020302040201| 10| | Immediate execution with SQL variables : ENG|020302040201| 12| | Positions of SQL variables in the command string are represented ENG|020302040201| 13| | by question marks. ENG|020302040201| 15| | ENG|020302040201| 16|SE| cmd := 'SELECT * FROM customer WHERE name = ?'; ENG|020302040201| 19| E| READ name; ENG|020302040201| 21| E| SQL EXECUTE IMMEDIATE cmd USING name; ENG|020302040201| 22| | ENG|020302040201| 24|RI| ##0203040229->^Syntax ENG|020302040202| 1| | Preparation of a dynamic SQL command ENG|020302040202| 3| | Positions of SQL variables in the command string are represented ENG|020302040202| 4| | by question marks. ENG|020302040202| 6| | ENG|020302040202| 7| E| cmd := 'SELECT DIRECT cust_no INTO ?' ENG|020302040202| 8| E| & ' WHERE name = ? AND city = ?'; ENG|020302040202| 9| E| SQL PREPARE sel FROM cmd; ENG|020302040202| 10| E| ; ENG|020302040202| 11| | ENG|020302040202| 13| | Execution of a prepared command : ENG|020302040202| 15| | A prepared command may be executed multiple times using the same or ENG|020302040202| 16|S | different variables. ENG|020302040202| 19| | ENG|020302040202| 20| E| SQL EXECUTE sel USING c_no, name, @city; ENG|020302040202| 21| | ENG|020302040202| 23| | Accessing information about SQL variables : ENG|020302040202| 25| | ENG|020302040202| 26| E| SQL DESCRIBE sel INTO @info(1..10); ENG|020302040202| 27| | ENG|020302040202| 28| | @info(1) -> 'OUT FIXED ( 6 , 0 ) NOT NULL' ENG|020302040202| 29| | @info(2) -> 'IN CHAR ( 20 ) NOT NULL' ENG|020302040202| 30| | @info(3) -> 'IN CHAR ( 20 )' ENG|020302040202| 31| | $COUNT -> 3 ENG|020302040202| 34|RI| ##0203040229->^Syntax ENG|0203020403 | 1| | Dynamic FETCH Commands : ENG|0203020403 | 3| | The dynamic FETCH command is provided in order that the values selected ENG|0203020403 | 4| | by means of a dynamic SQL command can be assigned to variables. The ENG|0203020403 | 5| | values of the current line of the specified result set are assigned to ENG|0203020403 | 6| | the variables (local or global variables or vector slices) designated ENG|0203020403 | 7| | by a ':'. ENG|0203020403 | 8| | If no result set is specified, the FETCH command refers to the last ENG|0203020403 | 9| | generated result set. ENG|0203020403 | 11| | Example : ENG|0203020403 | 13| | ENG|0203020403 | 14| E| table := 'customer'; ENG|0203020403 | 15| E| READ upper_limit; ENG|0203020403 | 16| E| command := 'SELECT res (name, city) FROM ' & table ENG|0203020403 | 17|SE| & ' where ' & 'cno < ' & upper_limit; ENG|0203020403 | 19| E| SQL EXECUTE IMMEDIATE command; ENG|0203020403 | 20| E| SQL FETCH IMMEDIATE FIRST res INTO :@cname, :@ccity; ENG|0203020403 | 21| E| WHILE $RC = 0 DO ENG|0203020403 | 22| E| BEGIN ENG|0203020403 | 23| E| WRITE NL, @cname, @ccity; ENG|0203020403 | 24| E| SQL FETCH IMMEDIATE NEXT res INTO :@cname, :@ccity; ENG|0203020403 | 25| E| END; ENG|0203020403 | 26| | ENG|0203020403 | 28|RI| ##0203040230->^Syntax ENG|0203020404 | 1| | Mass Fetch : ENG|0203020404 | 3| | SQL-PL offers an efficient possibility of processing result sets with ENG|0203020404 | 4| | an extended FETCH command. Specifying vector slices in the FETCH ENG|0203020404 | 5| | command has the effect that up to 255 rows of a result set are provided ENG|0203020404 | 6| | with one SQL order. ENG|0203020404 | 8| | Example : ENG|0203020404 | 10| | ENG|0203020404 | 11| E| SQL ( select cno, name FROM customer order by cno ); ENG|0203020404 | 12| E| IF $RC = 0 AND $COUNT <= 255 ENG|0203020404 | 13| E| THEN SQL ( FETCH INTO cno(1..$COUNT),name(1..$COUNT)) ENG|0203020404 | 14| E| ELSE ... ENG|0203020404 | 15| | ENG|0203020404 | 17|RI| ##0203040231->^Syntax ENG|0203020405 | 1| | SQL-PL supports the handling of SQL errors in the following way: ENG|0203020405 | 2| | It checks, according to the SQL return code, whether the current ENG|0203020405 | 3| | program has a routine with the name specified below. If so, this ENG|0203020405 | 4| | routine is called implicitly via CALL, otherwise the current routine is ENG|0203020405 | 5| | continued with the next statement. ENG|0203020405 | 6| | SQL error handling routines may have one formal parameter. The SQL-PL ENG|0203020405 | 7| | runtime system assigns the name of the routine where the SQL error ENG|0203020405 | 8| | occurred to it. ENG|0203020405 | 11| | SQLEXCEPTION : 100 < $RC < 700 ENG|0203020405 | 12| I| ##0203040236->^Syntax ENG|0203020405 | 13| | SQLNOTFOUND : $RC = 100 ENG|0203020405 | 14| I| ##0203040237->^Syntax ENG|0203020405 | 15| | SQLTIMEOUT : $RC = 700 ENG|0203020405 | 16| I| ##0203040238->^Syntax ENG|0203020405 | 17|S | SQLERROR : $RC < 0 ENG|0203020405 | 19| I| ##0203040235->^Syntax ENG|0203020405 | 20| | SQLWARNING : $SQLWARNING (a warning is set) ENG|0203020405 | 21|RI| ##0203040239->^Syntax ENG|0203020406 | 1| | The CONNECT statement establishes the connection to other databases or ENG|0203020406 | 2| | other user areas located on the same database. ENG|0203020406 | 4| | CONNECT symbolic_dbname AS (<username>, <password>, <serverdb>, ENG|0203020406 | 5| | /<servernode>/) ENG|0203020406 | 7| | The <servernode> must only be specified, if the database is stored on a ENG|0203020406 | 8| | servernode other than the current node. ENG|0203020406 | 10| | Example : ENG|0203020406 | 12| | ENG|0203020406 | 13| E| READ @username, DARK, @password; ENG|0203020406 | 14| E| CONNECT db2 AS (@username, @password, 'OTHERDB'); ENG|0203020406 | 16| E| db := 'DB2'; ENG|0203020406 | 17|SE| CONNECT :db AS ('MILLER', 'BEGINNING', 'OTHERDB', 'NODE2'); ENG|0203020406 | 19| | ENG|0203020406 | 21| I| ##0203040233->^Syntax ENG|0203020406 | 23| | The error codes and error messages are available in $RC and $RT. ENG|0203020406 | 25| | The connection is implicitly released after program execution. ENG|0203020406 | 27| | The connection can be released explicitly by means of the RELEASE ENG|0203020406 | 28| | statement. ENG|0203020406 | 30| | ENG|0203020406 | 31| E| RELEASE symbolic_dbname ENG|0203020406 | 32| | ENG|0203020406 | 34| | The symbolic database name to which a physical database name was ENG|0203020406 | 35|S | assigned in the CONNECT statement is specified after the keyword SQL. ENG|0203020406 | 37| | Then the specified command is sent to the corresponding DBMS in order ENG|0203020406 | 38| | to be executed. ENG|0203020406 | 40| | Example : ENG|0203020406 | 42| | ENG|0203020406 | 43| E| READ @username, DARK, @password; ENG|0203020406 | 44| E| CONNECT db2 AS (@username, @password, 'OTHERDB'); ENG|0203020406 | 45| E| SQL db2 ( SELECT cno, name FROM customer ); ENG|0203020406 | 46| E| SQL db2 ( FETCH INTO :cno, :name ); ... ENG|0203020406 | 47| E| RELEASE db2; ENG|0203020406 | 48| | ENG|0203020406 | 50|RI| ##0203040234->^Syntax ENG|02030205 | 1| | REPORT and Stored Commands ENG|02030205 | 3| M| Editing Tables with #01REPORT ENG|02030205 | 4| M| Calling #02Stored^Commands ENG|02030205 | 5| M| in #03Multi-DB^Mode ENG|02030205 | 6| M| Specifying a #04Header ENG|02030205 | 7| M| #05Options ENG|02030205 | 8|RM| Reusing the Results ( #06RESULT ) ENG|0203020501 | 1| | The interface with the ADABAS report generator is provided for editing ENG|0203020501 | 2| | database result sets. ENG|0203020501 | 4| | ENG|0203020501 | 5| E| SQL ( SELECT * FROM customer ); ENG|0203020501 | 6| E| REPORT CMD ( TTITLE ''== customers ==''); ENG|0203020501 | 7| | ENG|0203020501 | 9| I| Any ##08REPORT commands may be specified - separated by semicolons or the ENG|0203020501 | 10| | end of line. ENG|0203020501 | 12| | Example : ENG|0203020501 | 14| | ENG|0203020501 | 15| E| SQL ( SELECT * FROM customer ); ENG|0203020501 | 16| E| REPORT CMD ( TTITLE ''== customers =='' ; TOTAL bank balance ENG|0203020501 | 17|SE| GROUP customer number ); ENG|0203020501 | 19| | ENG|0203020501 | 23| | SQL-PL variables denoted by a colon are replaced by text prior to being ENG|0203020501 | 24| | called. ENG|0203020501 | 26| | Example : ENG|0203020501 | 28| | ENG|0203020501 | 29| E| variable_with_REPORT_commands := 'GROUP customer number ; TOTAL 1'; ENG|0203020501 | 30| E| SQL ( SELECT * FROM customer ); ENG|0203020501 | 31| E| READ @title; ENG|0203020501 | 32| E| REPORT CMD (:variable_with_REPORT_commands ; TTITLE :@title); ENG|0203020501 | 33| | ENG|0203020501 | 35|S | To edit named result sets with REPORT, the result set name must be ENG|0203020501 | 37| | specified. This may also be a variable name (denoted by a leading ':'). ENG|0203020501 | 39| | ENG|0203020501 | 40| E| SQL ( SELECT result (*) FROM customer ); ENG|0203020501 | 41| E| REPORT result CMD (TTITLE ''== customers ==''); ENG|0203020501 | 43| E| res := 'RESULT'; ENG|0203020501 | 44| E| REPORT :res CMD (TTITLE ''== customers ==''); ENG|0203020501 | 45| | ENG|0203020501 | 47| | REPORT SHOW edits the result set of the last SHOW or EXPLAIN command. ENG|0203020501 | 49| I| ##0203020503Another^database, a ##0203020504header, ##0203020505options, or ##0203020506results^to^be^reused may be ENG|0203020501 | 50| | specified in addition. ENG|0203020501 | 52|S | Example : ENG|0203020501 | 55| | ENG|0203020501 | 56| E| REPORT res ENG|0203020501 | 57| E| DBNAME = database2 ENG|0203020501 | 58| E| HEADER 'customers' ENG|0203020501 | 59| E| CMD ( TTITLE 'all customers' ENG|0203020501 | 60| E| TOTAL 'total sum : ' 3 ) ENG|0203020501 | 61| E| OPTIONS ( BACKGROUND, SETLOCAL ) ENG|0203020501 | 62| E| RESULT ( tot_sum = SUM (3), average = AVG (4) ); ENG|0203020501 | 63| | ENG|0203020501 | 65|RI| ##0203040243->^Syntax ENG|0203020502 | 1| | There are two syntactical ways of calling stored commands defined in ENG|0203020502 | 2| | QUERY. ENG|0203020502 | 5| | On the one hand the QUERY syntax may be used for calling the stored ENG|0203020502 | 6| | command. The keyword CMD is followed by the call as it is used in the ENG|0203020502 | 7| | QUERY command line. In SQL-PL variables may be specified in addition. ENG|0203020502 | 8| | These must be denoted by a leading ':'. Before the stored command is ENG|0203020502 | 9| | called, the variables will be substituted by their current values. ENG|0203020502 | 11| | Example : ENG|0203020502 | 13| | ENG|0203020502 | 14| E| QUERY CMD ( run customer_list ); ENG|0203020502 | 16| E| QUERY CMD ( run :$USER.customer_list :no 'customers' ); ENG|0203020502 | 17|S | ENG|0203020502 | 19| | The second way of calling a stored command is to specify its name and ENG|0203020502 | 20| | that of its owner, if necessary. This specification may be done as ENG|0203020502 | 21| | constant or variable (denoted by a leading ':'). ENG|0203020502 | 23| | Parameters which may be required are specified after the keyword PARM ENG|0203020502 | 24| | (or PARMS), separated from each other by a comma. Any expressions may ENG|0203020502 | 25| | be passed as parameters. NULL values are not allowed !!! ENG|0203020502 | 27| | Example : ENG|0203020502 | 29| | ENG|0203020502 | 30| E| QUERY customer_list; ENG|0203020502 | 32| E| QUERY :$USER.customer_list ENG|0203020502 | 33| E| PARMS ( no, 'customers' ); ENG|0203020502 | 34|S | ENG|0203020502 | 37| I| ##0203020503Another^database, and - if the stored command contains a REPORT call - ENG|0203020502 | 38| I| a ##0203020504header, ##0203020505options, or ##0203020506results^to^be^reused may be specified in ENG|0203020502 | 39| | addition. ENG|0203020502 | 41| | Examples : ENG|0203020502 | 43| | ENG|0203020502 | 44| E| QUERY DBNAME = database2 ENG|0203020502 | 45| E| HEADER 'customers' ENG|0203020502 | 46| E| CMD ( run :$USER.customer_list :no 'customers' ) ENG|0203020502 | 47| E| OPTION ( SETOFF ) ENG|0203020502 | 48| E| RESULT ( tot_sum = SUM (3) ); ENG|0203020502 | 50| E| QUERY :$USER.customer_list ENG|0203020502 | 51| E| DBNAME = database2 ENG|0203020502 | 52| E| HEADER 'customers' ENG|0203020502 | 53|SE| PARMS ( no, 'customers' ) ENG|0203020502 | 55| E| OPTION ( SETOFF ) ENG|0203020502 | 56| E| RESULT ( tot_sum = SUM (3) ); ENG|0203020502 | 57| | ENG|0203020502 | 59|RI| ##0203040241->^Syntax ENG|0203020503 | 1| | Result sets which were not generated in the default database can also ENG|0203020503 | 2| | be edited with REPORT. The symbolic database name is therefore ENG|0203020503 | 3| | specified. ENG|0203020503 | 5| | In the same way stored commands may be called by another database. ENG|0203020503 | 7| | Examples: ENG|0203020503 | 9| | ENG|0203020503 | 10| E| SQL db2 ( SELECT result (*) FROM customer ); ENG|0203020503 | 11| E| REPORT result CMD (TTITLE ''== customers =='') ENG|0203020503 | 12| E| DBNAME = db2; ENG|0203020503 | 14| E| QUERY customer_list ENG|0203020503 | 15| E| DBNAME = db2; ENG|0203020503 | 17|SE| QUERY :$USER.customer_list PARMS ( no, 'customers' ) ENG|0203020503 | 19| E| DBNAME = db2; ENG|0203020503 | 20| | ENG|0203020503 | 22|RI| ##0203040241->^Syntax ENG|0203020504 | 1| | A title of up to 40 characters may be defined for a REPORT page. An ENG|0203020504 | 2| | expression (e.g. a character string or a variable) is therefore ENG|0203020504 | 3| | specified after the keyword HEADER. The title is truncated to the first ENG|0203020504 | 4| | 40 characters, if necessary. If no title is specified, the name of the ENG|0203020504 | 5| | result table is output as such. ENG|0203020504 | 7| | Examples : ENG|0203020504 | 9| | ENG|0203020504 | 10| E| QUERY HEADER 'customers' ENG|0203020504 | 11| E| CMD ( run :$USER.customer_list :no 'customers' ); ENG|0203020504 | 13| E| QUERY :$USER.customer_list ENG|0203020504 | 14| E| PARMS ( no, 'customers' ) ENG|0203020504 | 15| E| HEADER 'customers of the ' & DATE (dd.mm.yyyy); ENG|0203020504 | 17|SE| REPORT HEADER 'customers' ENG|0203020504 | 19| E| CMD ( TTITLE 'all customers' ENG|0203020504 | 20| E| TOTAL 'total sum : ' 3 ) ENG|0203020504 | 21| | ENG|0203020504 | 23|RI| ##0203040241->^Syntax ENG|0203020505 | 1| | It is possible to specify options for the execution of REPORT. The ENG|0203020505 | 2| | option BACKGROUND prevents a result set edited by the REPORT generator ENG|0203020505 | 3| | from being output to the screen up to the next screen output. ENG|0203020505 | 5| | During REPORT execution interactive modifications to the SET parameters ENG|0203020505 | 6| | can be prevented by means of SETOFF, ENG|0203020505 | 7| | can be allowed temporarily by means of SETLOCAL, i.e. when the ENG|0203020505 | 8| | REPORT display is left again, the SET parameters are reset to the ENG|0203020505 | 9| | values they had before the REPORT call. ENG|0203020505 | 10| | If no option is specified for the modification of the SET parameters, ENG|0203020505 | 11| | any modification made to these parameters will have a global effect, ENG|0203020505 | 12| | i.e. it will be valid up to the next alteration of the SET parameters. ENG|0203020505 | 17|S | Examples : ENG|0203020505 | 19| | ENG|0203020505 | 20| E| QUERY OPTION ( SETOFF ) ENG|0203020505 | 21| E| CMD ( run :$USER.customer_list :no 'customers' ); ENG|0203020505 | 23| E| QUERY :$USER.customer_list ENG|0203020505 | 24| E| PARMS ( no, 'customers' ) ENG|0203020505 | 25| E| OPTION ( SETOFF ); ENG|0203020505 | 27| E| REPORT CMD ( TTITLE 'all customers' ENG|0203020505 | 28| E| TOTAL 'total sum : ' 3 ) ENG|0203020505 | 29| E| OPTIONS ( BACKGROUND, SETLOCAL ); ENG|0203020505 | 30| | ENG|0203020505 | 32|RI| ##0203040245->^Syntax ENG|0203020506 | 1| | Total results computed by the REPORT generator for result table columns ENG|0203020506 | 2| | (SUM, AVG, MIN, MAX, COUNT, or user-defined arithmetic expression) may ENG|0203020506 | 3| | be used in SQL-PL. The corresponding result is therefore assigned to a ENG|0203020506 | 4| | variable. A result is only not NULL if during REPORT execution any ENG|0203020506 | 5| | result was computed for the specified column, i.e. when the result is ENG|0203020506 | 6| | defined either with the REPORT call or interactively during REPORT ENG|0203020506 | 7| | execution. ENG|0203020506 | 9| | Examples : ENG|0203020506 | 11| | ENG|0203020506 | 12| E| QUERY CMD ( run :$USER.customer_list :no 'customers' ) ENG|0203020506 | 13| E| RESULT ( tot_sum = SUM (3) ); ENG|0203020506 | 15| E| QUERY :$USER.customer_list PARMS ( no, 'customers' ) ENG|0203020506 | 16|SE| RESULT ( tot_sum = SUM (3) ); ENG|0203020506 | 19| E| REPORT CMD ( TTITLE 'all customers'; TOTAL 'total sum : ' 3 ) ENG|0203020506 | 20| E| RESULT ( tot_sum = SUM (3), average = AVG (4) ); ENG|0203020506 | 21| | ENG|0203020506 | 23|RI| ##0203040244->^Syntax ENG|02030206 | 1| | Editing Operating System Files ENG|02030206 | 2| | ------------------------------ ENG|02030206 | 4| M| #01Opening and#01Closing OS Files ENG|02030206 | 5| M| #02Reading OS Files ENG|02030206 | 6|RM| #03Writing OS Files ENG|0203020601 | 1| | ENG|0203020601 | 2| E| OPEN fileid filename READ or WRITE or APPEND ENG|0203020601 | 4| E| CLOSE fileid ENG|0203020601 | 5| | ENG|0203020601 | 7| | The <fileid> is an internal name for a file. This <fileid> can be used ENG|0203020601 | 8| | with READFILE, WRITEFILE, or CLOSE to relate to the file opened with ENG|0203020601 | 9| | OPEN. READ opens a file in read mode, WRITE in write mode. APPEND opens ENG|0203020601 | 10| | an existing file in write mode preserving its previous contents. ENG|0203020601 | 12| | Examples : ENG|0203020601 | 14| | ENG|0203020601 | 15| E| OPEN input 'input file' READ ENG|0203020601 | 16| E| READFILE input a:5,b:6; ENG|0203020601 | 17|SE| CLOSE input ENG|0203020601 | 19| | ENG|0203020601 | 21| I| ##0203040250<open^file^stmt>->^Syntax ENG|0203020601 | 22|RI| ##0203040251<close^file^stmt>->^Syntax ENG|0203020602 | 1| | The READFILE statement can be used for reading data from OS files into ENG|0203020602 | 2| | SQL-PL variables. The data must exist in readable form. ENG|0203020602 | 4| | Example : ENG|0203020602 | 6| | ENG|0203020602 | 7| E| OPEN input 'input file' READ; ENG|0203020602 | 8| E| READFILE input l:5 , b:l , c; ENG|0203020602 | 9| | ENG|0203020602 | 10| | /* The length of the subsequent value will be read into the ENG|0203020602 | 11| | /* variable l ! ENG|0203020602 | 13| | A length specification must be made for each variable - except for the ENG|0203020602 | 14| | last one. ENG|0203020602 | 16|RI| ##0203040254->^Syntax ENG|0203020603 | 1| | The WRITEFILE statement can be used for writing data to an OS file. ENG|0203020603 | 3| | Example : ENG|0203020603 | 5| | ENG|0203020603 | 6| E| filename := 'input file'; ENG|0203020603 | 7| E| OPEN output filename WRITE ENG|0203020603 | 8| E| WRITEFILE output 'This file is named ', filename:7 ; ENG|0203020603 | 9| E| WRITEFILE output 'Now follow 25 blanks ',' ':25; ENG|0203020603 | 10| | ENG|0203020603 | 12| | A length specification can follow the values. If the length ENG|0203020603 | 13| | specification is omitted, the value is output in its actual length. If ENG|0203020603 | 14| | the length specification is too large or too small, the value is ENG|0203020603 | 15| | truncated or padded with blanks. ENG|0203020603 | 17|RI| ##0203040252->^Syntax ENG|02030207 | 1| | It is possible to call the editor out of an SQL-PL routine. A vector ENG|02030207 | 2| | slice or a variable must be specified as argument. ENG|02030207 | 4| | If a vector slice is specified, each vector component corresponds to ENG|02030207 | 5| | one line of the edit form. ENG|02030207 | 7| | nf Example: ENG|02030207 | 9| | ENG|02030207 | 10| E| EDIT ( text (1..20) ); E$} ENG|02030207 | 12| E| If a variable is specified, the text edited within the editor is ENG|02030207 | 13| E| assigned to it. This text can be reedited at any time. It can, ENG|02030207 | 14| E| particularly, be stored in the database in a STRING column. ENG|02030207 | 16| E| Example : ENG|02030207 | 17|SE| {$E ENG|02030207 | 19| E| PROC customer.c_maintain ENG|02030207 | 20| E| ... ENG|02030207 | 21| E| SQL ( OPEN COLUMN ... IN WRITE MODE ); ENG|02030207 | 22| E| SQL ( READ COLUMN ... BUFFER :text ); ENG|02030207 | 23| E| EDIT ( text ); ENG|02030207 | 24| E| SQL ( WRITE COLUMN ... BUFFER :text ); ENG|02030207 | 25| E| SQL ( CLOSE COLUMN ... ); ENG|02030207 | 26| | ENG|02030207 | 28| | The following parameters can be specified in addition: ENG|02030207 | 30| | POS (<line>, <column>) determines the position of the upper left-hand ENG|02030207 | 31| | corner of the editor window on the screen. ENG|02030207 | 33| | SIZE (<lines>, <columns>) determines the size of the window. ENG|02030207 | 35|S | MARK (<lines>, <columns>) determines the cursor position. ENG|02030207 | 37| | MSG = <expression> sets the message line of the editor to a value. ENG|02030207 | 39| | LABEL = <expression> (or HEADER) sets the header line of the editor to ENG|02030207 | 40| | a value. ENG|02030207 | 42| | COMPID = <expression> sets the left field in the header line of the ENG|02030207 | 43| | editor to a value. ENG|02030207 | 45| | PRINT prints the contents of the editor. ENG|02030207 | 47| | KEYSWAP has the effect that the key assignment is transferred from the ENG|02030207 | 48| | SQL-PL routine to the editor. ENG|02030207 | 50| | NOINIT has the effect that the position of the editor window is stored ENG|02030207 | 51| | when leaving the editor. This position will be reestablished with the ENG|02030207 | 52|S | next editor call made with NOINIT option and the same SQL-PL variable. ENG|02030207 | 55| | F1 = <expression> activates the first function key (PF1 or SK1). ENG|02030207 | 56| | Possible is the specification of F1 to F9, HELP, UP, and DOWN. ENG|02030207 | 58| | MAXLINES = <expression> determines the number of alterable vector ENG|02030207 | 59| | components. ENG|02030207 | 61| | After calling the editor, the variables $CMD, $EDITLINES, and $KEY are ENG|02030207 | 62| | set. ENG|02030207 | 64|RI| ##0203040247->^Syntax ENG|02030209 | 1| | The components of the first vector are sorted either in ascending order ENG|02030209 | 2| | (GTSORT) or in descending (LTSORT) order. The components of all the ENG|02030209 | 3| | other vectors are permuted accordingly. ENG|02030209 | 5| | Examples : ENG|02030209 | 7| | ENG|02030209 | 8| E| GTSORT ( lastname(1..10), firstname(), city() ); ENG|02030209 | 9| E| LTSORT ( length(1..number) ); ENG|02030209 | 10| | ENG|02030209 | 12|RI| ##0203040215->^Syntax ENG|02030210 | 1| | The values of the user-specific SET parameters can be changed in SQL-PL ENG|02030210 | 2| | programs by means of the SET statement. ENG|02030210 | 4| | Examples : ENG|02030210 | 6| | ENG|02030210 | 7| E| SET ( NULL, '' ); ENG|02030210 | 8| E| SET ( DECIMALREP, '//./' ); ENG|02030210 | 9| | ENG|02030210 | 11| M| Only runtime-relevant SET parameters can be determined via #01keywords. ENG|02030210 | 13| I| The ##0203030311SET^function can be used to find out the values of the SET ENG|02030210 | 14| | parameters. ENG|02030210 | 16|RI| ##0203040311->^Syntax ENG|0203021001 | 1| | The following key words are available for the SET-command ENG|0203021001 | 3| | LANGUAGE the language of the current program ENG|0203021001 | 4| | NULLVALUE the representation of the null value ENG|0203021001 | 5| | SEPARATOR the symbol used as separator of REPORT columns ENG|0203021001 | 6| | DECIMALREP the representation of numeric values ENG|0203021001 | 7| | DATE the representation of date values ENG|0203021001 | 8| | TIME the representation of time values ENG|0203021001 | 9| | COPIES the number of copies for print jobs ENG|0203021001 | 10| | PROTOCOL the name of the file for SQLTRACE/MODULETRACE output ENG|0203021001 | 11| I| SYSEDITOR the name of the editor specified for ##040314SYSEDIT ENG|0203021001 | 12| | PRINFORMAT the name of the current print format ENG|0203021001 | 13| | PRESENTATION the name of the current screen setting ENG|0203021001 | 15|RI| ##0203040302->^Syntax ENG|02030211 | 1| | The dollar functions $SEC and $MICRO can be used to find out the ENG|02030211 | 2| | duration of runs. ENG|02030211 | 3| | For this purpose the statement INITTIME starts the stop watch and ENG|02030211 | 4| | initializes the two variables. GETTIME assigns the seconds or ENG|02030211 | 5| | microseconds passed since INITTIME to $SEC and $MICRO. ENG|02030211 | 7| | Example : ENG|02030211 | 9| | ENG|02030211 | 10| E| PROC systime.control_appl; ENG|02030211 | 12| E| READ @applname, @startmodule; ENG|02030211 | 14| E| INITTIME; ENG|02030211 | 15| E| SWITCHCALL :@applname CALL PROC :@startmodule; ENG|02030211 | 16|SE| GETTIME; ENG|02030211 | 19| E| WRITE 'program execution', :@applname, NL; ENG|02030211 | 20| E| WRITE $SEC, $MICRO, PAUSE; ENG|02030211 | 21| | ENG|02030211 | 23|RI| ##0203040313->^Syntax ENG|02030212 | 1| | The EXEC command may be used for calling operating system commands out ENG|02030212 | 2| | of an SQL-PL program. ENG|02030212 | 4| | Operating system commands usually are called synchronously. Some ENG|02030212 | 5| | operating systems allow an asynchronous command call in addition. ENG|02030212 | 7| | In case of a synchronous call a program result is returned to any ENG|02030212 | 8| | SQL-PL variable. In case of an asynchronous call there is not such a ENG|02030212 | 9| | result. ENG|02030212 | 11| | Examples: ENG|02030212 | 13| | ENG|02030212 | 14| E| EXEC 'ls -l' RESULT resultvar; /* synchronous call ENG|02030212 | 16| E| EXEC ASYNC 'ls -l > list'; /* asynchronous call ENG|02030212 | 17|S | ENG|02030212 | 19|RI| ##0203040257->^Syntax ENG|020303 | 1| | Standard Functions ENG|020303 | 2| | ------------------ ENG|020303 | 4| M| #01$^Functions ENG|020303 | 5| M| #02Arithmetic^Functions ENG|020303 | 6| M| #03String^Functions ENG|020303 | 7|RM| #04Date^and^Time^Functions ENG|02030301 | 1| | $-Funktionen ENG|02030301 | 2| | ------------ ENG|02030301 | 4| M| #02$ACTION #05$MAXLINES #03$SEC ENG|02030301 | 5| M| #02$CMD #05$MAXCOLS #03$SERVERDB ENG|02030301 | 6| M| #09$COLNO ##02030211$MICRO #03$SQLCODE ENG|02030301 | 7| M| #04$COUNT #07$MSGLINES #03$SQLERRMC ENG|02030301 | 8| M| #02$CURSOR #08$OS #03$SQLERRPOS ENG|02030301 | 9| M| #02$EDITLINES #08$PROCESSID #06$SQLWARN ENG|02030301 | 10| M| #02$FUNCTION #03$RC #10$SYSRC ENG|02030301 | 11| M| #01$GROUP #09$ROWNO #10$SYSRT ENG|02030301 | 12| M| #10$ITEMS #03$RT #08$TERM ENG|02030301 | 13| M| #07$KEY #05$SCREENCOLS #01$USER ENG|02030301 | 14| M| #05$KEYLINES #05$SCREENLNS #01$USERMODE ENG|02030301 | 16|RI| ##0203040253->^Syntax ENG|0203030101 | 1| | $USER provides the name of the user who called the routine. ENG|0203030101 | 3| | $GROUP provides the name of the usergroup. ENG|0203030101 | 5| | $SERVERDB provides the name of the database. ENG|0203030101 | 7| | $USERMODE provides the status of the user (STANDARD, RESOURCE, DBA) ENG|0203030101 | 9|RI| ##0203040315->^Syntax ENG|0203030102 | 1| | $KEY provides the last release key used. ENG|0203030102 | 2| | Possible keys are : F1, ..., F9, ENTER, HELP ENG|0203030102 | 4| | $CURSOR provides the number of the input field where the ENG|0203030102 | 5| | cursor was placed for the last FORM call. ENG|0203030102 | 7| | $CMD provides the contents of the editor command line, when ENG|0203030102 | 8| | the editor was called out of an SQL-PL routine. ENG|0203030102 | 10| | $EDITLINES provides the number of lines edited for an editor call. ENG|0203030102 | 12| | $ACTION provides the activated field of the action bar. ENG|0203030102 | 14| | $FUNCTION provides the selected field of a pulldown menu. ENG|0203030102 | 16|RI| ##0203040316->^Syntax ENG|0203030103 | 1| | $RC provides the return code of the last SQL command. ENG|0203030103 | 3| | Some important return codes : ENG|0203030103 | 5| | 0 - everything OK ENG|0203030103 | 6| | 100 - row not found ENG|0203030103 | 7| | 200 - duplicate key ENG|0203030103 | 8| | 300 - integrity violation ENG|0203030103 | 10| | $RT provides the (error) text appropriate for $RC. ENG|0203030103 | 12| | Example : ENG|0203030103 | 14| | $RC = 100 --> $RT = '100 row not found' ENG|0203030103 | 16|S | Synonym for $RC is $SQLCODE; for $RT $SQLERRMC. ENG|0203030103 | 19| | $SQLERRPOS provides the error position within the SQL statement ENG|0203030103 | 20| | in case the analysis fails. ENG|0203030103 | 22|RI| ##0203040316->^Syntax ENG|0203030104 | 1| | $COUNT provides the number of rows found with a mass select. ENG|0203030104 | 2| | After the SQL command ENG|0203030104 | 4| | SQL ( select * from customer ); ENG|0203030104 | 6| | the following is possible: ENG|0203030104 | 8| | $COUNT = 0 no row found ( $RC = 100 ) ENG|0203030104 | 9| | $COUNT = n n rows found ENG|0203030104 | 10| | $COUNT IS NULL rows found, but the number ENG|0203030104 | 11| | is not known yet. ENG|0203030104 | 13| | After UPDATE, INSERT, DELETE, and SHOW commands $COUNT provides the ENG|0203030104 | 14| | number of affected rows in addition. ENG|0203030104 | 16|RI| ##0203040315->^Syntax ENG|0203030105 | 1| | $SCREENLNS provides the maximum number of lines in the form ENG|0203030105 | 2| | called last. ENG|0203030105 | 4| | $SCREENCOLS provides the maximum number of characters possible ENG|0203030105 | 5| | per line in the form called last. ENG|0203030105 | 7| | $MAXLINES provides the maximum number of lines possible per ENG|0203030105 | 8| | form. ENG|0203030105 | 10| | $MAXCOLS provides the maximum number of characters per screen ENG|0203030105 | 11| | line. ENG|0203030105 | 13|RI| ##0203040314->^Syntax ENG|0203030106 | 1| | $SQLWARN ENG|0203030106 | 3| | The warnings returned by the database system can be checked with this ENG|0203030106 | 4| | function. The function must be used within logical expressions (e.g. ENG|0203030106 | 5| | IF). $SQLWARN results true, if further warnings have been returned; ENG|0203030106 | 6| | these can be checked with $SQLWARN(1) ... $SQLWARN(15). ENG|0203030106 | 8| | Example : ENG|0203030106 | 10| | ENG|0203030106 | 11| E| IF $SQLWARN /* any warning ? ENG|0203030106 | 12| E| THEN FOR i := 1 to 15 ENG|0203030106 | 13| E| IF $SQLWARN (i) ENG|0203030106 | 14| E| THEN WRITE NL,'Warning ',i,' is set'; ENG|0203030106 | 15| | ENG|0203030106 | 17|RI| ##0203040316->^Syntax ENG|0203030107 | 1| | $KEYLINES provides 1, if there is a key for the softkey line ENG|0203030107 | 2| | available on the screen, otherwise it provides 0. ENG|0203030107 | 4| | $MSGLINES provides 1, if there is an additional system line ENG|0203030107 | 5| | available on the screen for the message, otherwise ENG|0203030107 | 6| | it provides 0. ENG|0203030107 | 8|RI| ##0203040315->^Syntax ENG|0203030108 | 1| | $OS provides the name of the current operating system ENG|0203030108 | 3| | $TERM provides the value of the environment variable TERM ENG|0203030108 | 5| | $PROCESSID provides the numerical value of the current operating ENG|0203030108 | 6| | system process ENG|0203030108 | 8|RI| ##0203040315->^Syntax ENG|0203030109 | 1| | $ROWNO, $COLNO provide, after a REPORT call, the row and column ENG|0203030109 | 2| | of the REPORT display where the cursor was ENG|0203030109 | 3| | positioned last. If the cursor was positioned ENG|0203030109 | 4| | outside the REPORT display, then $ROWNO and $COLNO ENG|0203030109 | 5| | are 0. ENG|0203030109 | 7|RI| ##0203040314->^Syntax ENG|0203030110 | 1| | $SYSRC provides the return code of the last file or EXEC command ENG|0203030110 | 3| | $SYSRT provides an explanation of the error ENG|0203030110 | 5| | $ITEMS provides the number of fields parsed during the last ENG|0203030110 | 6| | TOKENIZE or SEPARATE. $ITEMS provides also the number ENG|0203030110 | 7| | of replacements made during the last CHANGE ENG|0203030110 | 9|RI| ##0203040315->^Syntax ENG|02030302 | 1| | Arithmetic Functions ENG|02030302 | 2| | -------------------- ENG|02030302 | 4| M| #07ABS #01MIN ENG|02030302 | 5| M| #03ARCTAN #05PI ENG|02030302 | 6| M| #01AVG #02ROUND ENG|02030302 | 7| M| #03COS #03SIGN ENG|02030302 | 8| M| #04EXP #03SIN ENG|02030302 | 9| M| #04LN #06SQR ENG|02030302 | 10| M| #01MAX #06SQRT ENG|02030302 | 11| M| #05MDS #02TRUNC ENG|02030302 | 12|RI| ##0203040259->^Syntax ENG|0203030201 | 1| | MIN provides the smallest value of the specified set. ENG|0203030201 | 3| | MAX provides the largest value of the specified set. ENG|0203030201 | 5| | AVG provides the average value of the specified set. ENG|0203030201 | 7| | Examples : ENG|0203030201 | 9| | ENG|0203030201 | 10| E| MIN (1,2,3) --> 1 ENG|0203030201 | 11| E| MAX (1,1/0,3) --> 3 ( 1/0 -> NULL ! ) ENG|0203030201 | 12| E| AVG (1,10/0,3) --> NULL ENG|0203030201 | 13| | ENG|0203030201 | 15|RI| ##0203040302->^Syntax ENG|0203030202 | 1| | ROUND rounds the number to the specified number of fractional digits. ENG|0203030202 | 3| | TRUNC truncates the number to the specified number of fractional digits. ENG|0203030202 | 5| | Examples : ENG|0203030202 | 7| | ENG|0203030202 | 8| E| ROUND (1234.567 , 2) --> 1234.57 ENG|0203030202 | 9| E| TRUNC (1234.567 , 2) --> 1234.56 ENG|0203030202 | 10| E| TRUNC (1/0,3) --> NULL ( 1/0 -> NULL ! ) ENG|0203030202 | 11| | ENG|0203030202 | 13|RI| ##0203040259->^Syntax ENG|0203030203 | 1| | SIN COS ARCTAN ENG|0203030203 | 3| | The known trigonometrical functions. ENG|0203030203 | 4| | The radian is expected as argument. ENG|0203030203 | 6| | Examples : ENG|0203030203 | 8| | ENG|0203030203 | 9| E| SIN (0) --> 0 ENG|0203030203 | 10| E| COS (0) --> 1 ENG|0203030203 | 11| E| ARCTAN (0) --> 0 ENG|0203030203 | 12| | ENG|0203030203 | 14|RI| ##0203040259->^Syntax ENG|0203030204 | 1| | EXP LN ENG|0203030204 | 3| | The known mathematical functions ENG|0203030204 | 5| | Example : ENG|0203030204 | 7| | ENG|0203030204 | 8| E| LN (EXP(5)) --> 5 (approx.) ENG|0203030204 | 9| | ENG|0203030204 | 11|RI| ##0203040259->^Syntax ENG|0203030205 | 1| | PI provides the value of Pi with a precision of 18 digits. ENG|0203030205 | 3| | MDS provides the maximum size of a variable value. ENG|0203030205 | 5|RI| ##0203040259->^Syntax ENG|0203030206 | 1| | SQR provides the square of an expression. ENG|0203030206 | 3| | SQRT provides the square root. ENG|0203030206 | 5| | Examples : ENG|0203030206 | 7| | ENG|0203030206 | 8| E| SQR (2) --> 4 ENG|0203030206 | 9| E| SQRT (4) --> 2 ENG|0203030206 | 10| | ENG|0203030206 | 12|RI| ##0203040259->^Syntax ENG|0203030207 | 1| | ABS provides the absolute amount of a numeric expression. ENG|0203030207 | 3| | Examples : ENG|0203030207 | 5| | ENG|0203030207 | 6| E| ABS (-1) --> 1 ENG|0203030207 | 7| E| ABS (1) --> 1 ENG|0203030207 | 8| | ENG|0203030207 | 10|RI| ##0203040259->^Syntax ENG|0203030208 | 1| | SIGN provides the sign of a numeric expression. ENG|0203030208 | 3| | Examples : ENG|0203030208 | 5| | ENG|0203030208 | 6| E| SIGN(100) -> 1 ENG|0203030208 | 7| E| SIGN(-PI) -> -1 ENG|0203030208 | 8| E| SIGN(0) -> 0 ENG|0203030208 | 9| | ENG|0203030208 | 11|RI| ##0203040259->^Syntax ENG|02030303 | 1| | String Functions ENG|02030303 | 2| | ---------------- ENG|02030303 | 4| M| #12BREAK #15SEPARATE ENG|02030303 | 5| M| #07CHANGE #11SET ENG|02030303 | 6| M| #08CHR #13SPAN ENG|02030303 | 7| M| #04FORMAT #06STRPOS ENG|02030303 | 8| M| #05INDEX #02SUBSTR ENG|02030303 | 9| M| #09HEX #03TRIM ENG|02030303 | 10| M| #03LENGTH #15TOKENIZE ENG|02030303 | 11| M| #10LOWER #10UPPER ENG|02030303 | 12| M| #08ORD #02Concatenation (&) ENG|02030303 | 13| M| #14PAD #01Character Repetition ENG|02030303 | 14|RI| ##0203040305->^Syntax ENG|0203030301 | 1| | The specified character is repeated according to the number defined by ENG|0203030301 | 2| | the numeric expression. ENG|0203030301 | 4| | Examples : ENG|0203030301 | 6| | ENG|0203030301 | 7| E| '.'(5) --> '.....'; ENG|0203030301 | 8| E| a := 5; ENG|0203030301 | 9| E| BLANK(a) --> ' ' ENG|0203030301 | 10|R | ENG|0203030302 | 1| | Concatenation (&) combines strings. ENG|0203030302 | 3| | SUBSTR starting with the specified position finds out the substring ENG|0203030302 | 4| | in the specified length or, if no length is specified, up to ENG|0203030302 | 5| | the end of the string. ENG|0203030302 | 7| | Examples : ENG|0203030302 | 9| | ENG|0203030302 | 10| E| name := firstname & lastname; ENG|0203030302 | 11| E| name := 'Lastname : ' & lastname & ' Firstname : ' & firstname; ENG|0203030302 | 12| E| string := SUBSTR ('abcdefg',3,2) --> 'cd' ENG|0203030302 | 13| E| string := SUBSTR ('abcdefg',3) --> 'cdefg' ENG|0203030302 | 14| E| string := SUBSTR ('abcdefg',8) --> NULL ENG|0203030302 | 15| E| string := SUBSTR ('abcdefg',4,10) --> 'defg' ENG|0203030302 | 16|S | ENG|0203030302 | 19|RI| ##0203040305->^Syntax ENG|0203030303 | 1| | LENGTH finds out the length of the specified expression. ENG|0203030303 | 3| | TRIM removes the specified character (blank, if nothing else ENG|0203030303 | 4| | has been specified) from the beginning and end of the ENG|0203030303 | 5| | string expression. An additional argument may restrict ENG|0203030303 | 6| | trimming to one side. ENG|0203030303 | 8| | Examples : ENG|0203030303 | 10| | ENG|0203030303 | 11| E| LENGTH ('three') --> 4 ENG|0203030303 | 12| E| LENGTH ('thirty' & 'three') --> 15 ENG|0203030303 | 13| E| TRIM (' A '); --> 'A' ENG|0203030303 | 14| E| TRIM (' A ','.'); --> ' A ' ENG|0203030303 | 15| E| TRIM ('...A ','.'); --> 'A ' ENG|0203030303 | 16| E| TRIM (' A ', ' ', RIGHT ) --> ' A' ENG|0203030303 | 17|SE| TRIM (' A ', ' ', LEFT ) --> 'A ' ENG|0203030303 | 19| E| TRIM (' A ', ' ', BOTH ) --> 'A' ENG|0203030303 | 20| | ENG|0203030303 | 22|RI| ##0203040305->^Syntax ENG|0203030304 | 1| | FORMAT serves to edit numbers for output. ENG|0203030304 | 3| | Examples : ENG|0203030304 | 5| | ENG|0203030304 | 6| E| FORMAT ( 1234, '9 999' ) --> '1 234' ENG|0203030304 | 7| E| FORMAT ( 1234, '9,999.99 Kg' ) --> '1,234.00 Kg' ENG|0203030304 | 8| E| FORMAT ( 12.3, 'DM 999,99' ) --> 'DM 12,30' ENG|0203030304 | 9| E| FORMAT ( 1.234, '9 Kg 555 g' ) --> '1 Kg 234 g' ENG|0203030304 | 10| E| FORMAT ( 12.34, '.9999e-99' ) --> '.1234e+02' ENG|0203030304 | 11| E| FORMAT ( -123, '99 999' ) --> ' -123' ENG|0203030304 | 12| E| FORMAT ( 123, '+9 999' ) --> '+ 123' ENG|0203030304 | 13| E| FORMAT (-1234, '99 999-' ) --> ' 1 234-' ENG|0203030304 | 14| E| FORMAT ( 123, '099 999' ) --> '000 123' ENG|0203030304 | 15| E| FORMAT ( 123, '*99 999' ) --> '****123' ENG|0203030304 | 16| E| FORMAT ( 123, '$>99 999' ) --> ' $123' ENG|0203030304 | 17|S | ENG|0203030304 | 19|RI| ##0203040305->^Syntax ENG|0203030305 | 1| | INDEX searches a vector for a certain value. ENG|0203030305 | 2| | If this value is encountered, the function ENG|0203030305 | 3| | provides the index of the vector component. ENG|0203030305 | 5| | Example : ENG|0203030305 | 7| | ENG|0203030305 | 8| E| a (5..7) := 'x'; ENG|0203030305 | 9| E| INDEX (a(1..10),'x') --> 5 ENG|0203030305 | 10| E| INDEX (a(10..1),'x') --> 7 ENG|0203030305 | 11| E| INDEX (a(1..3),'x') --> NULL ENG|0203030305 | 12| E| INDEX (a(1..10), NOT NULL ) --> 5 ENG|0203030305 | 13| | ENG|0203030305 | 15|RI| ##0203040301->^Syntax ENG|0203030306 | 1| | STRPOS searches a variable value for the specified character string. ENG|0203030306 | 2| | If this is encountered, STRPOS returns the initial position ENG|0203030306 | 3| | as the result; otherwise it provides the NULL value. ENG|0203030306 | 4| | It is possible to specify the starting position of the ENG|0203030306 | 5| | search. ENG|0203030306 | 7| | Examples : ENG|0203030306 | 9| | ENG|0203030306 | 10| E| STRPOS ('aabbccbbee','bb') --> 3; ENG|0203030306 | 11| E| STRPOS ('aabbccbbee','bb',4) --> 7; ENG|0203030306 | 12| E| STRPOS ('aabbccbbee','xx',4) --> NULL; ENG|0203030306 | 13| | ENG|0203030306 | 15|RI| ##0203040301->^Syntax ENG|0203030307 | 1| | CHANGE replaces a character string in a variable value with another ENG|0203030307 | 2| | character string. $ITEMS contains the number of changes. ENG|0203030307 | 4| | Example : ENG|0203030307 | 6| | ENG|0203030307 | 7| E| string := CHANGE (string,' ','_'); /* replaces blanks with underscores ENG|0203030307 | 8| E| string := CHANGE (string,' '); /* eliminates blanks ENG|0203030307 | 9| | ENG|0203030307 | 11|RI| ##0203040305->^Syntax ENG|0203030308 | 1| | CHR provides for the given numeric value the character that ENG|0203030308 | 2| | corresponds to the CHAR representation of this value. If ENG|0203030308 | 3| | there is something else specified instead of a numeric value, ENG|0203030308 | 4| | or if there is no CHAR representation for the numeric value, ENG|0203030308 | 5| | CHR provides the NULL value as the result. ENG|0203030308 | 7| | ORD is the inverse function of CHR. It provides the corresponding ENG|0203030308 | 8| | numeric value for a character. ENG|0203030308 | 10| | Examples : ENG|0203030308 | 12| | ENG|0203030308 | 13| E| b := ORD ('a'); ENG|0203030308 | 14| E| a := CHR (b); --> a = 'a'; ENG|0203030308 | 15| | ENG|0203030308 | 17|RI| ##0203040305->^Syntax ENG|0203030309 | 1| | HEX can be applied to any string expression. ENG|0203030309 | 2| | It provides a string of double length in ENG|0203030309 | 3| | hexadecimal representation. ENG|0203030309 | 5| | Examples : ENG|0203030309 | 7| | ENG|0203030309 | 8| E| hxstr := HEX ('Hallo'); ENG|0203030309 | 9| | ENG|0203030309 | 11|RI| ##0203040305->^Syntax ENG|0203030310 | 1| | LOWER converts the specified character string into ENG|0203030310 | 2| | lower case characters. ENG|0203030310 | 4| | UPPER converts the specified character string into ENG|0203030310 | 5| | upper case characters. ENG|0203030310 | 7| | Examples: ENG|0203030310 | 9| | ENG|0203030310 | 10| E| LOWER ('Good MORNING !') --> 'good morning !' ENG|0203030310 | 11| E| UPPER ('Good MORNING !') --> 'GOOD MORNING !' ENG|0203030310 | 12| | ENG|0203030310 | 14|RI| ##0203040305->^Syntax ENG|0203030311 | 1| | The values of the user-specific SET parameters can be found out by ENG|0203030311 | 2| | means of the SET function. ENG|0203030311 | 4| | Examples : ENG|0203030311 | 6| | ENG|0203030311 | 7| E| @lang := SET ( LANGUAGE ); ENG|0203030311 | 8| E| @lang := SET ( 2 ); ENG|0203030311 | 9| | ENG|0203030311 | 11| | Only runtime-relevant SET parameters can be retrieved/requested via ENG|0203030311 | 12| I| ##0203021001keywords. ENG|0203030311 | 14| I| The SET parameters are changed by means of the ##02030210SET statement. ENG|0203030311 | 16|RI| ##0203040310->^Syntax ENG|0203030312 | 1| | BREAK searches a variable value for the first occurence of a ENG|0203030312 | 2| | character included in the specified character string. ENG|0203030312 | 3| | BREAK returns the initial position as the result. ENG|0203030312 | 4| | It is possible to specify the starting position of the ENG|0203030312 | 5| | search. ENG|0203030312 | 7| | Beispiele : ENG|0203030312 | 9| | ENG|0203030312 | 10| E| BREAK ( '999.999,99', '.,' ) --> 4 ENG|0203030312 | 11| E| BREAK ( '999.999,99', '.,', 5 ) --> 8 ENG|0203030312 | 12| E| BREAK ( '999.999,99', 'abc', 5 ) --> 11 ENG|0203030312 | 13| | ENG|0203030312 | 15|RI| ##0203040301->^Syntax ENG|0203030313 | 1| | SPAN searches a variable value for the first occurence of a ENG|0203030313 | 2| | character not included in the specified character string. ENG|0203030313 | 3| | SPAN returns the initial position as the result. ENG|0203030313 | 4| | It is possible to specify the starting position of the ENG|0203030313 | 5| | search. ENG|0203030313 | 7| | Beispiele : ENG|0203030313 | 9| | ENG|0203030313 | 10| E| SPAN ( 'aabbccdd', 'ab' ) --> 5 ENG|0203030313 | 11| E| SPAN ( '999.999,99', '0123456789' ) --> 4 ENG|0203030313 | 12| E| SPAN ( 'aabbccdd', 'abcd' ) --> 9 ENG|0203030313 | 13| | ENG|0203030313 | 15|RI| ##0203040301->^Syntax ENG|0203030314 | 1| | PAD expands a character string to the given length. An additional ENG|0203030314 | 2| | argument may specify whether blanks are added left, right or ENG|0203030314 | 3| | on both sides. ENG|0203030314 | 5| | Examples : ENG|0203030314 | 7| | ENG|0203030314 | 8| E| PAD ( 'abc', 7 ) --> 'abc ' ENG|0203030314 | 9| E| PAD ( 'abc', 7, RIGHT ) --> 'abc ' ENG|0203030314 | 10| E| PAD ( 'abc', 7, LEFT ) --> ' abc' ENG|0203030314 | 11| E| PAD ( 'abc', 7, BOTH ) --> ' abc ' ENG|0203030314 | 12| | ENG|0203030314 | 14|RI| ##0203040305->^Syntax ENG|0203030315 | 1| | TOKENIZE and SEPARATE fill a vector slice using the fields contained in ENG|0203030315 | 2| | a character string. Fields a separated by any character of the second ENG|0203030315 | 3| | argument. TOKENIZE merges multiple continous separators into one, ENG|0203030315 | 4| | SEPARATE assumes a field between two separators and assigns to it the ENG|0203030315 | 5| | value NULL. After the call, $ITEMS contains the number of parsed ENG|0203030315 | 6| | fields. ENG|0203030315 | 8| | Beispiele : ENG|0203030315 | 10| | ENG|0203030315 | 11| E| @t(1..20) := TOKENIZE ( '1,2,,3', ',' ); ENG|0203030315 | 12| | ENG|0203030315 | 13| | @t(1) -> '1'; ENG|0203030315 | 14| | @t(2) -> '2'; ENG|0203030315 | 15| | @t(3) -> '3'; ENG|0203030315 | 17|S | ENG|0203030315 | 19| E| @t(1..20) := SEPARATE ( '1,2,,3', ',' ); ENG|0203030315 | 20| | ENG|0203030315 | 21| | @t(1) -> '1'; ENG|0203030315 | 22| | @t(2) -> '2'; ENG|0203030315 | 23| | @t(3) -> NULL; ENG|0203030315 | 24| | @t(4) -> '3'; ENG|0203030315 | 26|RI| ##0203040305->^Syntax ENG|02030304 | 1| | Date and Time Functions ENG|02030304 | 2| | ----------------------- ENG|02030304 | 4| M| #02ADDDATE ENG|02030304 | 5| M| #04ADDTIME ENG|02030304 | 6| M| #01DATE ENG|02030304 | 7| M| #02DATEDIFF ENG|02030304 | 8| M| #03DAYOFWEEK ENG|02030304 | 9| M| #03DAYOFYEAR ENG|02030304 | 10| M| #05MAKETIME ENG|02030304 | 11| M| #02SUBDATE ENG|02030304 | 12| M| #04SUBTIME ENG|02030304 | 13| M| #01TIME ENG|02030304 | 14| M| #04TIMEDIFF ENG|02030304 | 15| M| #03WEEKOFYEAR ENG|02030304 | 16|RI| ##0203040307->^Syntax ENG|0203030401 | 1| | DATE and TIME can be used for generating and transforming date and time ENG|0203030401 | 2| | specifications. ENG|0203030401 | 4| | Examples of current date and current time : ENG|0203030401 | 6| | January 21, 89 produces ENG|0203030401 | 7| | ENG|0203030401 | 8| E| DATE (mm/dd/yy) --> '01/21/89' ENG|0203030401 | 9| E| DATE (yyyy mm.dd) --> '1989 01.21' ENG|0203030401 | 10| E| DATE (dd mmm yyyy) --> '21 Jan 1989' ENG|0203030401 | 11| E| DATE (yyyy) --> '1989' ENG|0203030401 | 12| | ENG|0203030401 | 13| | 17.23 and 11 seconds produces ENG|0203030401 | 14| | ENG|0203030401 | 15| E| TIME (hh:mm-ss) --> '17:23-11' ENG|0203030401 | 16| E| TIME (hhmm) --> '1723' ENG|0203030401 | 17|S | ENG|0203030401 | 19| | Examples of given dates : ENG|0203030401 | 20| | ENG|0203030401 | 21| E| today := '01.01.88'; ENG|0203030401 | 22| E| today := DATE ( YYYY/MM/DD, today, dd.mm.yy ); ENG|0203030401 | 24| E| today := '09.03.94'; ENG|0203030401 | 25| E| today := DATE ( , today, dd.mm.yy ); ENG|0203030401 | 27| E| now := '12:00:05'; ENG|0203030401 | 28| E| now := TIME ( hhmmss, now, hh:mm:ss ); ENG|0203030401 | 29| | ENG|0203030401 | 30| | The second mask describes the format of the input value. If one of the ENG|0203030401 | 31| | descriptions is missing, the format defined in the SET parameters is ENG|0203030401 | 32| | used. ENG|0203030401 | 34|RI| ##0203040307->^Syntax ENG|0203030402 | 1| | ADDDATE adds n days to a given date. ENG|0203030402 | 3| | SUBDATE subtracts n days from a given date. ENG|0203030402 | 5| | DATEDIFF indicates the difference between two date ENG|0203030402 | 6| | specifications in days. ENG|0203030402 | 8| | Examples : ENG|0203030402 | 10| | ENG|0203030402 | 11| E| ADDDATE ('19871231',1) --> '19880101' ENG|0203030402 | 12| E| SUBDATE ('19871231',31) --> '19871130' ENG|0203030402 | 13| E| DATEDIFF ('19880101','19870101') --> 365 ENG|0203030402 | 14| | ENG|0203030402 | 16|S | Date specifications must be made in the format 'YYYYMMDD'. ENG|0203030402 | 19|RI| ##0203040307->^Syntax ENG|0203030403 | 1| | DAYOFWEEK provides a numeric value between 1 and 7 ENG|0203030403 | 2| | which identifies the day of the week, ENG|0203030403 | 3| | where 1 represents Monday. ENG|0203030403 | 5| | DAYOFYEAR provides a numeric value between 1 and 366. ENG|0203030403 | 7| | WEEKOFYEAR provides a value between 1 and 53. ENG|0203030403 | 9| | Examples : ENG|0203030403 | 11| | ENG|0203030403 | 12| E| DAYOFWEEK ('19880105') --> 2 ENG|0203030403 | 13| E| DAYOFYEAR ('19870101') --> 1 ENG|0203030403 | 14| E| WEEKOFYEAR ('19880101') --> 53 ENG|0203030403 | 15| | ENG|0203030403 | 17|S | Date specifications must be made in the format 'YYYYMMDD'. ENG|0203030403 | 19|RI| ##0203040309->^Syntax ENG|0203030404 | 1| | SUBTIME provides a time which results from the difference ENG|0203030404 | 2| | between the two specified values. ENG|0203030404 | 4| | ADDTIME provides a time which results from the sum of the two ENG|0203030404 | 5| | specified values. ENG|0203030404 | 7| | TIMEDIFF provides the positive difference of the specified times ENG|0203030404 | 8| | in seconds. ENG|0203030404 | 9| | Examples : ENG|0203030404 | 11| | ENG|0203030404 | 12| E| SUBTIME ('00105523','00000023') --> '00105500' ENG|0203030404 | 13| E| ADDTIME ('00105523','00000037') --> '00105600' ENG|0203030404 | 14| E| TIMEDIFF ('00000005','00000000') --> 5 ENG|0203030404 | 15| | ENG|0203030404 | 17|S | Time specifications must be made in the format 'HHHHMMSS'. ENG|0203030404 | 19|RI| ##0203040308->^Syntax ENG|0203030405 | 1| | MAKETIME provides a time which results from the sum of the three ENG|0203030405 | 2| | arguments ( MAKETIME (<hours>,<minutes>,<seconds>) ). ENG|0203030405 | 4| | Examples : ENG|0203030405 | 6| | ENG|0203030405 | 7| E| MAKETIME (10,59,33) --> '00105933' ENG|0203030405 | 8| E| MAKETIME (10,59,63) --> '00110003' ENG|0203030405 | 9| | ENG|0203030405 | 11|RI| ##0203040308->^Syntax ENG|020304 | 1| | Syntax Description ENG|020304 | 2| | ------------------ ENG|020304 | 4| M| #01Syntax elements ENG|020304 | 5|RM| #05Keywords ENG|02030401 | 1| I| ##0203040101<prog^name> ENG|02030401 | 2| I| ##0203040102<mod^name> ENG|02030401 | 3| I| ##0203040103<routine> ENG|02030401 | 4| I| ##0203040104<parm^decl> ENG|02030401 | 5| I| ##0203040107<function> ENG|02030401 | 6| I| ##0203040108<db^procedure> ENG|02030401 | 7| I| ##0203040109<dbproc^parm^decl^list> ENG|02030401 | 8| I| ##0203040110<dbproc^parm^decl> ENG|02030401 | 9| I| ##0203040111<dir> ENG|02030401 | 10| I| ##0203040112<data^type> ENG|02030401 | 11| I| ##0203040113<trigger> ENG|02030401 | 12| I| ##0203040114<trigger^parm^decl> ENG|02030401 | 13| I| ##0203040115<trigger^column^spec> ENG|02030401 | 14| I| ##0203040116<trigger^predicat> ENG|02030401 | 15| I| ##0203040117<help^form> ENG|02030401 | 16| I| ##0203040118<name> ENG|02030401 | 17|SI| ##0203040119<literal> ENG|02030401 | 19| I| ##0203040120<numeric> ENG|02030401 | 20| I| ##0203040121<unsigned^integer> ENG|02030401 | 21| I| ##0203040122<digit> ENG|02030401 | 22| I| ##0203040123<fixed^point> ENG|02030401 | 23| I| ##0203040124<floating^point> ENG|02030401 | 24| I| ##0203040125<sign> ENG|02030401 | 25| I| ##0203040126<string^literal> ENG|02030401 | 26| I| ##0203040127<any^char> ENG|02030401 | 27| I| ##0203040128<hexstring^literal> ENG|02030401 | 28| I| ##0203040129<langdep^literal> ENG|02030401 | 29| I| ##0203040133<variable> ENG|02030401 | 30| I| ##0203040134<vector^slice> ENG|02030401 | 31| I| ##0203040135<variable^name> ENG|02030401 | 32| I| ##0203040136<expr> ENG|02030401 | 33| I| ##0203040137<num^expr> ENG|02030401 | 34| I| ##0203040138<term^list> ENG|02030401 | 35|SI| ##0203040139<term> ENG|02030401 | 37| I| ##0203040140<factor> ENG|02030401 | 38| I| ##0203040141<function^call> ENG|02030401 | 39| I| ##0203040142<str^expr> ENG|02030401 | 40| I| ##0203040143<basic^str> ENG|02030401 | 41| I| ##0203040144<repeat^string> ENG|02030401 | 42| I| ##0203040145<boolean^term> ENG|02030401 | 43| I| ##0203040146<boolean^expr> ENG|02030401 | 44| I| ##0203040147<expr^comparison> ENG|02030401 | 45| I| ##0203040148<check^cond> ENG|02030401 | 46| I| ##0203040149<simple^cond> ENG|02030401 | 47| I| ##0203040150<is^cond> ENG|02030401 | 48| I| ##0203040151<in^cond> ENG|02030401 | 49| I| ##0203040152<value^spec^list> ENG|02030401 | 50| I| ##0203040153<value^spec> ENG|02030401 | 51| I| ##0203040154<between^cond> ENG|02030401 | 52| I| ##0203040155<like^cond> ENG|02030401 | 53|SI| ##0203040156<var^section> ENG|02030401 | 55| I| ##0203040157<var^decl> ENG|02030401 | 56| I| ##0203040158<array^spec> ENG|02030401 | 57| I| ##0203040159<lab^stmt^list> ENG|02030401 | 58| I| ##0203040160<logic^op> ENG|02030401 | 59| I| ##0203040161<comp^op> ENG|02030401 | 60| I| ##0203040201<compound> ENG|02030401 | 61| I| ##0203040202<stmt> ENG|02030401 | 62| I| ##0203040203<assign^stmt> ENG|02030401 | 63| I| ##0203040204<assign^expr> ENG|02030401 | 64| I| ##0203040205<vector^assign^stmt> ENG|02030401 | 65| I| ##0203040206<if^stmt> ENG|02030401 | 66| I| ##0203040207<case^stmt> ENG|02030401 | 67| I| ##0203040208<case^list> ENG|02030401 | 68| I| ##0203040209<repeat^stmt> ENG|02030401 | 69| I| ##0203040210<while^stmt> ENG|02030401 | 70| I| ##0203040211<for^stmt> ENG|02030401 | 71|SI| ##0203040212<skip^stmt> ENG|02030401 | 73| I| ##0203040213<return^stmt> ENG|02030401 | 74| I| ##0203040214<stop^stmt> ENG|02030401 | 75| I| ##0203040215<vectsort^stmt> ENG|02030401 | 76| I| ##0203040216<proc^call> ENG|02030401 | 77| I| ##0203040216<form^call> ENG|02030401 | 78| I| ##0203040218<switch^stmt> ENG|02030401 | 79| I| ##0203040219<switchcall^stmt> ENG|02030401 | 80| I| ##0203040220<name^expr> ENG|02030401 | 81| I| ##0203040221<param> ENG|02030401 | 82| I| ##0203040222<parm^spec^list> ENG|02030401 | 83| I| ##0203040225<dbproc^call> ENG|02030401 | 84| I| ##0203040227<sql^stmt> ENG|02030401 | 85| I| ##0203040228<sql^cmd> ENG|02030401 | 86| I| ##0203040229<dynamic^sql^stmt> ENG|02030401 | 87| I| ##0203040230<dynamic^fetch^stmt> ENG|02030401 | 88| I| ##0203040231<mass^fetch^stmt> ENG|02030401 | 89|SI| ##0203040233<connect^stmt> ENG|02030401 | 91| I| ##0203040234<release^stmt> ENG|02030401 | 92| I| ##0203040235<sqlerror^routine> ENG|02030401 | 93| I| ##0203040236<sqlexception^routine> ENG|02030401 | 94| I| ##0203040237<sqlnotfound^routine> ENG|02030401 | 95| I| ##0203040238<sqltimeout^routine> ENG|02030401 | 96| I| ##0203040239<sqlwarning^routine> ENG|02030401 | 97| I| ##0203040240<try^catch^stmt> ENG|02030401 | 98| I| ##0203040241<query^stmt> ENG|02030401 | 99| I| ##0203040242<querycmd^spec> ENG|02030401 | 100| I| ##0203040243<report^stmt> ENG|02030401 | 101| I| ##0203040244<further^facilities> ENG|02030401 | 102| I| ##0203040245<query^option> ENG|02030401 | 103| I| ##0203040246<result^spec> ENG|02030401 | 104| I| ##0203040247<edit^call> ENG|02030401 | 105| I| ##0203040248<write^stmt> ENG|02030401 | 106| I| ##0203040249<read^stmt> ENG|02030401 | 107|SI| ##0203040250<open^file^stmt> ENG|02030401 | 109| I| ##0203040251<close^file^stmt> ENG|02030401 | 110| I| ##0203040252<write^file^stmt> ENG|02030401 | 111| I| ##0203040253<write^file^args> ENG|02030401 | 112| I| ##0203040254<read^file^stmt> ENG|02030401 | 113| I| ##0203040255<read^file^args> ENG|02030401 | 114| I| ##0203040256<writetrace^stmt> ENG|02030401 | 115| I| ##0203040257<exec^command> ENG|02030401 | 116| I| ##0203040258<operating^system^command> ENG|02030401 | 117| I| ##0203040259<arith^function> ENG|02030401 | 118| I| ##0203040260<index^function> ENG|02030401 | 119| I| ##0203040301<strpos^function> ENG|02030401 | 120| I| ##0203040302<set^function> ENG|02030401 | 121| I| ##0203040303<mixed^expr> ENG|02030401 | 122| I| ##0203040304<sign^function> ENG|02030401 | 123| I| ##0203040305<string^function> ENG|02030401 | 124| I| ##0203040301<strpos^function> ENG|02030401 | 125|SI| ##0203040307<date^function> ENG|02030401 | 127| I| ##0203040308<date^str^function> ENG|02030401 | 128| I| ##0203040309<date^arith^function> ENG|02030401 | 129| I| ##0203040311<set^stmt> ENG|02030401 | 130| I| ##0203040312<set^id> ENG|02030401 | 131| I| ##0203040313<systime^func> ENG|02030401 | 132| I| ##0203040314<dollar^numeric^variable> ENG|02030401 | 133| I| ##0203040315<dollar^string^variable> ENG|02030401 | 134| I| ##0203040316<dollar^boolean^variable> ENG|02030401 | 135| I| ##0203040317<object^header> ENG|02030401 | 136|RI| ##0203040318<module^option> ENG|0203040101 | 1| | <prog name> ::= ENG|0203040101 | 2|RI| ##0203040118<name> ENG|0203040102 | 1| | <mod name> ::= ENG|0203040102 | 2|RI| ##0203040118<name> ENG|0203040103 | 1| | <routine> ::= ENG|0203040103 | 2| I| PROC ##0203040101<prog^name> . ##0203040102<mod^name> ENG|0203040103 | 3| I| [PARMS ( ##0203040104<parm^decl> ,...)] ENG|0203040103 | 4| I| [OPTIONS ( ##0203040318<module^option> ,...)] ENG|0203040103 | 5| I| [ ##0203040156<var^section> ] ENG|0203040103 | 6|RI| ##0203040159<lab^stmt^list> ENG|0203040104 | 1| | <parm decl> ::= ENG|0203040104 | 2|R | <varname> [()] ENG|0203040107 | 1| | <function> ::= ENG|0203040107 | 2| I| FUNCTION ##0203040101<prog^name> . ##0203040102<mod^name> ENG|0203040107 | 3| I| [PARMS ( ##0203040104<parm^decl> ,...)] ENG|0203040107 | 4| I| [OPTIONS ( ##0203040318<module^option> ,...)] ENG|0203040107 | 5| I| [ ##0203040156<var^section> ] ENG|0203040107 | 6|RI| ##0203040159<lab^stmt^list> ENG|0203040108 | 1| | <db procedure> ::= ENG|0203040108 | 2| I| DBPROC ##0203040101<prog^name> . ##0203040102<mod^name> ENG|0203040108 | 3| I| [PARMS ( ##0203040110<dbproc^parm^decl> ,...)] ENG|0203040108 | 4| I| [OPTIONS ( ##0203040318<module^option> ,...)] ENG|0203040108 | 5| I| [ ##0203040156<var^section> ] ENG|0203040108 | 6|RI| ##0203040159<lab^stmt^list> ENG|0203040109 | 1| | <dbproc parm decl list> ::= ENG|0203040109 | 2| I| ##0203040104<parm^decl> ,... ENG|0203040109 | 3|RI| | ##0203040110<dbproc^parm^decl> ,... ENG|0203040110 | 1| | <dbproc parm decl> ::= ENG|0203040110 | 2|RI| ##0203040111<dir> ##0203040118<name> ##0203040112<data^type> ENG|0203040111 | 1|R | <dir> ::= IN | OUT | INOUT ENG|0203040112 | 1| | <data type> ::= ENG|0203040112 | 2| I| FIXED [ ( ##0203040121<unsigned^integer> [, <unsigned integer> ] ) ] ENG|0203040112 | 3| | | FLOAT [ ( <unsigned integer> ) ] ENG|0203040112 | 4| | | CHAR [ ( <unsigned integer> ) ] [ BYTE ] ENG|0203040112 | 5| | | DBYTE [ ( <unsigned integer> ) ] ENG|0203040112 | 6| | | DATE ENG|0203040112 | 7|R | | TIME ENG|0203040113 | 1| | <trigger> ::= ENG|0203040113 | 2| | TRIGGER <libname>.<funcname> ENG|0203040113 | 3| I| [PARMS ( ##0203040114<trigger^parm^decl> ,...)] ENG|0203040113 | 4| I| [OPTIONS ( ##0203040318<module^option> ,...)] ENG|0203040113 | 5| I| [ ##0203040156<var^section> ] ENG|0203040113 | 6|RI| ##0203040159<lab^stmt^list> ENG|0203040114 | 1| | <trigger parm decl> ::= ENG|0203040114 | 2|RI| ##0203040111<dir> ##0203040115<trigger^column^spec> ##0203040112<data^type> ENG|0203040115 | 1| | <trigger column spec> ::= ENG|0203040115 | 2| | <column name> ENG|0203040115 | 3| | | NEW.<column name> ENG|0203040115 | 4|R | | OLD.<column name> ENG|0203040116 | 1| | <trigger predicat> ::= ENG|0203040116 | 2| | FIRSTCALL|LASTCALL|INSERTING ENG|0203040116 | 3|R | | UPDATING|DELETING ENG|0203040117 | 1| | <help form> ::= ENG|0203040117 | 2| I| HELPFORM ##0203040101<prog^name> . ##0203040102<mod^name> ENG|0203040117 | 3| | [OPTIONS (<form option>,...)] ENG|0203040117 | 4| I| [PARMS ( ##0203040104<parm^decl> ,...)] ENG|0203040117 | 5| I| [ ##0203040156<var^section> ] ENG|0203040117 | 6| | <form layout> ENG|0203040117 | 7|R | [ <processing spec>;... ] ENG|0203040118 | 1| | <name> ::= <firstchar> [<name char> ... ] ENG|0203040118 | 3| | <firstchar> ::= <letter> | # | @ | $ ENG|0203040118 | 5| I| <name char> ::= <letter> | ##0203040122<digit> | _ | # | @ | $ ENG|0203040118 | 7|R | <letter> ::= a | .. | z | A |..| Z ENG|0203040119 | 1| | <literal> ::= <numeric literal> ENG|0203040119 | 2| I| | ##0203040126<string^literal> ENG|0203040119 | 3| I| | ##0203040128<hexstring^literal> ENG|0203040119 | 4| I| | ##0203040129<langdep^literal> ENG|0203040119 | 5| | | <key literal> ENG|0203040119 | 7|RI| <numeric literal> ::= ##0203040120<numeric> ENG|0203040120 | 1| I| <numeric> ::= ##0203040121<unsigned^integer> ENG|0203040120 | 2| I| | ##0203040123<fixed^point> ENG|0203040120 | 3|RI| | ##0203040124<floating^point> ENG|0203040121 | 1| I| <unsigned integer> ::= ##0203040122<digit> [<unsigned integer>] ENG|0203040121 | 2|R | maximum 18 places ENG|0203040122 | 1|R | <digit> ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ENG|0203040123 | 1|RI| <fixed point> ::= ##0203040121<unsigned^integer> [.<unsigned integer>] ENG|0203040124 | 1|RI| <floating point> ::= ##0203040123<fixed^point> E ##0203040125<sign> ##0203040122<digit> [<digit>] ENG|0203040125 | 1|R | <sign> ::= + | - ENG|0203040126 | 1|RI| <string literal> ::= ' ##0203040127<any^char> ...' ENG|0203040127 | 1|R | <any char> ::= any character on the keyboard ENG|0203040128 | 1| | <hexstring literal> ::= ENG|0203040128 | 2| | X'<hex digit seq>' | x'<hex digit seq>' ENG|0203040128 | 4| | <hex digit seq> ::= ENG|0203040128 | 5| | <hex digit><hex digit>[<hex digit seq>] ENG|0203040128 | 7| | <hex digit> ::= ENG|0203040128 | 8| | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ENG|0203040128 | 9|R | | A | B | C | D | E | F | a | b | c | d | e | f ENG|0203040129 | 1| I| <langdep literal> ::= ##0203040118!<name> [ (<literal size>) ] ENG|0203040129 | 3|R | <literal size> ::= S | M | L | XL ENG|0203040133 | 1| I| <variable> ::= ##0203040135<variable^name> [ (##0203040136<expr>) ] ENG|0203040133 | 2| | | MESSAGE ENG|0203040133 | 3|R | | ERROR ENG|0203040134 | 1|RI| <vector slice> ::= ##0203040135<variable^name> ( ##0203040136<expr> .. <expr>) ENG|0203040135 | 1|RI| <variable name> ::= ##0203040118<name> ENG|0203040136 | 1|RI| <expr> ::= ##0203040137<num^expr> | ##0203040142<str^expr> ENG|0203040137 | 1|RI| <num expr> ::= [ ##0203040125<sign> ] ##0203040139<term> [ ##0203040138<term^list> ] ENG|0203040138 | 1|RI| <term list> ::= ##0203040125<sign> ##0203040139<term> [<term list>] ENG|0203040139 | 1| I| <term> ::= ##0203040140<factor> [<mult op> <term>] ENG|0203040139 | 3|R | <mult op> ::= * | / | MOD | DIV ENG|0203040140 | 1| | <factor> ::= ENG|0203040140 | 2| I| ##0203040120<numeric> ENG|0203040140 | 3| I| | ##0203040133<variable> ENG|0203040140 | 4| I| | ( ##0203040136<expr> ) ENG|0203040140 | 5| I| | ##0203040259<arith^function> ENG|0203040140 | 6| I| | ##0203040141<function^call> ENG|0203040140 | 7|RI| | ##0203040314<dollar^numeric^variable> ENG|0203040141 | 1|R | <function call> ::= %<function name> ENG|0203040142 | 1| | <str expr> ::= ENG|0203040142 | 2|RI| ##0203040143<basic^str> [& ##0203040136<expr>] ENG|0203040143 | 1| | <basic str> ::= ENG|0203040143 | 2| I| ##0203040153<value^spec> ENG|0203040143 | 3| I| | ##0203040140<factor> ENG|0203040143 | 4| I| | ##0203040305<string^function> ENG|0203040143 | 5| I| | ##0203040301<strpos^function> ENG|0203040143 | 6| I| | ##0203040307<date^function> ENG|0203040143 | 7|RI| | ##0203040302<set^function> ENG|0203040144 | 1| | <repeat string> ::= ENG|0203040144 | 2| I| <repeat char> ( ##0203040136<expr> ) ENG|0203040144 | 4| | <repeat char> ::= ENG|0203040144 | 5|RI| '##0203040127<any^char>' | BLANK ENG|0203040145 | 1| | <boolean term> ::= ENG|0203040145 | 2| I| ( ##0203040146<boolean^expr> ) ENG|0203040145 | 3| I| | ##0203040147<expr^comparison> ENG|0203040145 | 4| I| | ##0203040136<expr> ##0203040148<check^cond> ENG|0203040145 | 5| I| | ALL ##0203040134<vector^slice> <check cond> ENG|0203040145 | 6| | | EACH <vector slice> <check cond> ENG|0203040145 | 7| | | ANY <vector slice> <check cond> ENG|0203040145 | 8| | | SOME <vector slice> <check cond> ENG|0203040145 | 9| | | ONE <vector slice> <check cond> ENG|0203040145 | 10| | | EOF (<fileid>) ENG|0203040145 | 11| I| | $SQLWARN [( ##0203040136<expr> )] ENG|0203040145 | 12|R | | FORM IS MODIFIED ENG|0203040146 | 1| | <boolean expr> ::= ENG|0203040146 | 2|RI| [NOT] ##0203040145<boolean^term> [ ##0203040160<logic^op> <boolean expr>] ENG|0203040147 | 1| | <expr comparison> ::= ENG|0203040147 | 2|RI| ##0203040136<expr> ##0203040161<comp^op> <expr> ENG|0203040148 | 1| | <check cond> ::= ENG|0203040148 | 2|RI| ##0203040149<simple^cond> [ ##0203040160<logic^op> <check cond>] ENG|0203040149 | 1| | <simple cond> ::= ENG|0203040149 | 2| I| ##0203040150<is^cond> ENG|0203040149 | 3| I| | [NOT] ##0203040151<in^cond> ENG|0203040149 | 4| I| | [NOT] ##0203040154<between^cond> ENG|0203040149 | 5|RI| | [NOT] ##0203040155<like^cond> ENG|0203040150 | 1| | <is cond> ::= ENG|0203040150 | 2| | IS [NOT] ALPHA ENG|0203040150 | 3| | | IS [NOT] NUMERIC ENG|0203040150 | 4| | | IS [NOT] BLANK ENG|0203040150 | 5| | | IS [NOT] NULL ENG|0203040150 | 6| | | IS [NOT] MODIFIED ENG|0203040150 | 7| I| | IS [NOT] FIXED ( ##0203040136<expr> ,<expr>) ENG|0203040150 | 8| | | IS [NOT] DATE [ (<date mask>) ] ENG|0203040150 | 9|R | | IS [NOT] TIME [ (<time mask>) ] ENG|0203040151 | 1| | <in cond> ::= ENG|0203040151 | 2|RI| IN ( ##0203040152<value^spec^list> ) ENG|0203040152 | 1| | <value spec list> ::= ENG|0203040152 | 2| I| ##0203040153<value^spec> [..<value spec>] [,<value spec list>] ENG|0203040152 | 3|R | | NULL [,<value spec list> ] ENG|0203040153 | 1| | <value spec> ::= ENG|0203040153 | 2| I| ##0203040119<literal> ENG|0203040153 | 3| I| | ##0203040144<repeat^string> ENG|0203040153 | 4| | | BLANK ENG|0203040153 | 5| I| | ##0203040314<dollar^numeric^variable> ENG|0203040153 | 6|RI| | ##0203040315<dollar^string^variable> ENG|0203040154 | 1| | <between cond> ::= ENG|0203040154 | 2|RI| BETWEEN ##0203040136<expr> AND <expr> ENG|0203040155 | 1| | <like cond> ::= ENG|0203040155 | 2|RI| LIKE ##0203040136<expr> [ ESCAPE <expr> ] ENG|0203040156 | 1| | <var section> ::= ENG|0203040156 | 2| I| VAR ##0203040157<var^decl> ,...; ENG|0203040156 | 3|R | [ VAR STATIC <var decl>,...; ] ENG|0203040157 | 1| | <var decl> ::= ENG|0203040157 | 2|RI| <varname> [ ##0203040158<array^spec> ] ENG|0203040158 | 1| | <array spec> ::= ENG|0203040158 | 2|R | () ENG|0203040159 | 1| | <lab stmt list> ::= ENG|0203040159 | 2|RI| [<label>] ##0203040201<compound> [<lab stmt list>] ENG|0203040160 | 1| | <logic op> ::= ENG|0203040160 | 2|R | AND | OR ENG|0203040161 | 1| | <comp op> ::= ENG|0203040161 | 2|R | = | < | > | <> | <= | >= ENG|0203040201 | 1| | <compound> ::= ENG|0203040201 | 2|RI| BEGIN ##0203040202<stmt> ;... END | <stmt> ENG|0203040202 | 1| | <stmt> ::= ENG|0203040202 | 2| I| ##0203040203<assign^stmt> | ##0203040205<vector^assign^stmt> ENG|0203040202 | 3| I| | ##0203040206<if^stmt> | ##0203040207<case^stmt> ENG|0203040202 | 4| I| | ##0203040209<repeat^stmt> | ##0203040210<while^stmt> ENG|0203040202 | 5| I| | ##0203040211<for^stmt> | ##0203040212<skip^stmt> ENG|0203040202 | 6| I| | ##0203040213<return^stmt> | ##0203040214<stop^stmt> ENG|0203040202 | 7| I| | ##0203040215<vectsort^stmt> | ##0203040219<switchcall^stmt> ENG|0203040202 | 8| I| | ##0203040216<proc^call> | ##0203040216<form^call> ENG|0203040202 | 9| I| | ##0203040218<switch^stmt> | ##0203040141<function^call> ENG|0203040202 | 10| I| | ##0203040225<dbproc^call> | ##0203040227<sql^stmt> ENG|0203040202 | 11| I| | ##0203040233<connect^stmt> | ##0203040234<release^stmt> ENG|0203040202 | 12| I| | ##0203040241<query^stmt> | ##0203040243<report^stmt> ENG|0203040202 | 13| I| | ##0203040247<edit^call> | ##0203040248<write^stmt> ENG|0203040202 | 14| I| | ##0203040249<read^stmt> | ##0203040250<open^file^stmt> ENG|0203040202 | 15| I| | ##0203040252<write^file^stmt> | ##0203040254<read^file^stmt> ENG|0203040202 | 16| I| | ##0203040251<close^file^stmt> | ##0203040257<exec^command> ENG|0203040202 | 17|RI| | ##0203040311<set^stmt> | <systime stmt> ENG|0203040203 | 1|RI| <assign stmt> ::= ##0203040133<variable> := ##0203040204<assign^expr> ENG|0203040204 | 1|RI| <assign expr> ::= ##0203040136<expr> | NULL ENG|0203040205 | 1| | <vector assign stmt> ::= ENG|0203040205 | 2| I| ##0203040134<vector^slice> := ##0203040204<assign^expr> ENG|0203040205 | 3|R | | <vector slice> := <vector slice> ENG|0203040206 | 1| I| <if stmt> ::= IF ##0203040146<boolean^expr> ENG|0203040206 | 2| I| THEN ##0203040201<compound> ENG|0203040206 | 3|R | [ELSE <compound>] ENG|0203040207 | 1| | <case stmt> ::= ENG|0203040207 | 2| I| CASE ##0203040136<expr> OF ENG|0203040207 | 3| I| ##0203040208<case^list> ENG|0203040207 | 4| I| [OTHERWISE ##0203040201<compound> ] ENG|0203040207 | 5|R | END ENG|0203040208 | 1| | <case list> ::= ENG|0203040208 | 2|RI| ##0203040152<value^spec^list> : ##0203040201<compound> [;<case list>] ; ENG|0203040209 | 1|RI| <repeat stmt> ::= REPEAT ##0203040202<stmt> ;... UNTIL ##0203040146<boolean^expr> ENG|0203040210 | 1|RI| <while stmt> ::= WHILE ##0203040146<boolean^expr> DO ##0203040201<compound> ENG|0203040211 | 1| | <for stmt> ::= ENG|0203040211 | 2| I| FOR ##0203040133<variable> := ##0203040136<expr> <direction> <expr> ENG|0203040211 | 3| I| DO ##0203040201<compound> ENG|0203040211 | 5|R | <direction> ::= TO | DOWNTO ENG|0203040212 | 1|RI| <skip stmt> ::= SKIP ##0203040118<name> ENG|0203040213 | 1| | <return stmt> ::= ENG|0203040213 | 2| | RETURN ENG|0203040213 | 3|RI| | RETURN (##0203040136<expr>) <-- in SQL-PL functions only ENG|0203040214 | 1|RI| <stop stmt> ::= STOP [( ##0203040136<expr> [, <expr>] )] ENG|0203040215 | 1| | <vectsort stmt> ::= ENG|0203040215 | 2| I| LTSORT ( ##0203040134<vector^slice>,... ) ENG|0203040215 | 3|R | | GTSORT ( <vector slice>,... ) ENG|0203040216 | 1| | <proc call> ::= ENG|0203040216 | 2|RI| CALL PROC ##0203040220<name^expr> [PARMS ( ##0203040221<param> ,...)] ENG|0203040217 | 1| | <form call> ::= ENG|0203040217 | 2| I| CALL FORM ##0203040220<name^expr> ENG|0203040217 | 3| | [OPTIONS (<form calling options>)] ENG|0203040217 | 4|RI| [PARMS ( ##0203040221<param> ,...)] ENG|0203040218 | 1| | <switch stmt> ::= ENG|0203040218 | 2| I| SWITCH [ ##0203040220<name^expr> .] <name expr> ##0203040216<form^call> ENG|0203040218 | 3|RI| | SWITCH [<name expr>.] <name expr> ##0203040216<proc^call> ENG|0203040219 | 1| | <switchcall stmt> ::= ENG|0203040219 | 2| I| SWITCHCALL [ ##0203040220<name^expr> .] <name expr> ##0203040216<form^call> ENG|0203040219 | 3|RI| | SWITCHCALL [<name expr>.] <name expr> ##0203040216<proc^call> ENG|0203040220 | 1| | <name expr> ::= ENG|0203040220 | 2|RI| ##0203040118<name> | : ##0203040136<expr> ENG|0203040221 | 1| | <param> ::= ENG|0203040221 | 2|RI| ##0203040118<name> ENG|0203040222 | 1| | <parm spec list> ::= ENG|0203040222 | 2|RI| PARM/S/ ( ##0203040157<var^decl> ,...) ENG|0203040225 | 1| | <dbproc call> ::= ENG|0203040225 | 2| | CALL DBPROC [[<owner>.]<progname>.]<dbproc name> ENG|0203040225 | 3| I| [PARMS ( ##0203040221<param> ,...] [WITH COMMIT] ENG|0203040225 | 4| | | SQL ( DBPROC[EDURE] [[<owner>.]<progname>.]<dbproc name> ENG|0203040225 | 5|RI| [( ##0203040226<host^var> ,...)] [WITH COMMIT] ) ENG|0203040226 | 1|RI| <host var> := ##0203040118:<name> ENG|0203040227 | 1| | <sql stmt> ::= ENG|0203040227 | 2| I| SQL [<dbname>] ( ##0203040228<sql^cmd> ) ENG|0203040227 | 3| I| | ##0203040229<dynamic^sql^stmt> ENG|0203040227 | 4| I| | ##0203040230<dynamic^fetch^stmt> ENG|0203040227 | 5|RI| | ##0203040231<mass^fetch^stmt> ENG|0203040228 | 1| | <sql cmd> ::= ENG|0203040228 | 2|R | see ADABAS Reference Manual ENG|0203040229 | 1| | <dynamic sql stmt> ::= ENG|0203040229 | 2| I| SQL [<dbname>] EXECUTE IMMEDIATE ##0203040136<expr> ENG|0203040229 | 3| | | SQL EXECUTE IMMEDIATE <expr> USING <expr list> ENG|0203040229 | 4| I| | SQL EXECUTE ##0203040118<name> USING <expr list> ENG|0203040229 | 5| | | SQL PREPARE <name> FROM <expr> ENG|0203040229 | 6|RI| | SQL DESCRIBE <name> INTO ##0203040134<vector^slice> ENG|0203040230 | 1| | <dynamic fetch stmt> ::= ENG|0203040230 | 2|RI| SQL [<dbname>] FETCH IMMEDIATE INTO ##0203040226<host^var> ,... ENG|0203040231 | 1| | <mass fetch stmt> ::= ENG|0203040231 | 2|RI| SQL [<dbname>] ( FETCH INTO ##0203040134<vector^slice> ,... ) ENG|0203040232 | 1| | <fetch dynamic descr> ::= ENG|0203040232 | 2|RI| SQL FETCH LONGDESCR INTO ##0203040133:<variable> ENG|0203040233 | 1| | <connect stmt> ::= ENG|0203040233 | 2| | CONNECT <dbname> ENG|0203040233 | 3| | AS ( <username>, <password>, <serverdb> ENG|0203040233 | 4| | [,<servernode>] ) ENG|0203040233 | 5| | | CONNECT :<var> ENG|0203040233 | 6| | AS ( <username>, <password>, <serverdb> ENG|0203040233 | 7|R | [,<servernode>] ) ENG|0203040234 | 1| | <release stmt> ::= ENG|0203040234 | 2| | RELEASE <dbname> ENG|0203040234 | 3|R | | RELEASE :<var> ENG|0203040235 | 1| | <sqlerror routine> ::= ENG|0203040235 | 2| | PROC <progname>.SQLERROR ( <modulename> ) ENG|0203040235 | 3|RI| ##0203040159<lab^stmt^list> ENG|0203040236 | 1| | <sqlexception routine> ::= ENG|0203040236 | 2| | PROC <progname>.SQLEXCEPTION ( <modulename> ) ENG|0203040236 | 3|RI| ##0203040159<lab^stmt^list> ENG|0203040237 | 1| | <sqlnotfound routine> ::= ENG|0203040237 | 2| | PROC <progname>.SQLNOTFOUND ( <modulename> ) ENG|0203040237 | 3|RI| ##0203040159<lab^stmt^list> ENG|0203040238 | 1| | <sqltimeout routine> ::= ENG|0203040238 | 2| | PROC <progname>.SQLTIMEOUT ( <modulename> ) ENG|0203040238 | 3|RI| ##0203040159<lab^stmt^list> ENG|0203040239 | 1| | <sqlwarning routine> ::= ENG|0203040239 | 2| | PROC <progname>.SQLWARNING ( <modulename> ) ENG|0203040239 | 3|RI| ##0203040159<lab^stmt^list> ENG|0203040240 | 1| | <try catch stmt> ::= ENG|0203040240 | 2| | TRY ENG|0203040240 | 3| I| ##0203040201<compound> ENG|0203040240 | 4| I| CATCH ##0203040136<expr> OF ENG|0203040240 | 5| I| ##0203040208<case^list> ENG|0203040240 | 6|R | END ENG|0203040241 | 1| | <query stmt> ::= ENG|0203040241 | 2| | QUERY <querycmd spec> ENG|0203040241 | 3|RI| [ ##0203040244<further^facilities> ] ENG|0203040242 | 1| | <querycmd spec> ::= ENG|0203040242 | 2| | [ CMD ] ( <querycmd> ) ENG|0203040242 | 3|RI| | EXEC[UTE] ##0203040136<expr> ENG|0203040243 | 1| | <report stmt> ::= ENG|0203040243 | 2| | REPORT [<result table name>] ENG|0203040243 | 3| | [<report cmd spec>] ENG|0203040243 | 4| I| [ ##0203040244<further^facilities> ] ENG|0203040243 | 6| | <report cmd spec> ::= ENG|0203040243 | 7| | [ CMD ] ( <report cmd>;... ) ENG|0203040243 | 9| | <report cmd> ::= ENG|0203040243 | 10| | see REPORT description ENG|0203040243 | 12|RI| <result table name> ::= ##0203040220<name^expr> ENG|0203040244 | 1| | <further facilities> ::= ENG|0203040244 | 2| | [DBNAME [=] <dbname>] ENG|0203040244 | 3| I| [PROGNAME [=] ##0203040136<expr> ] ENG|0203040244 | 4| | [VERSION [=] <expr>] ENG|0203040244 | 5| | [MODE [=] <expr>] ENG|0203040244 | 6| | [HEADER [=] <expr>] ENG|0203040244 | 7| I| [OPTION[S] ( ##0203040245<query^option> ,... )] ENG|0203040244 | 8|RI| [RESULT ( ##0203040246<result^spec> ,... )] ENG|0203040245 | 1| | <query option> ::= ENG|0203040245 | 2|R | BACKGROUND | SETOFF | SETLOCAL | NOHEADLINE ENG|0203040246 | 1| | <result spec> ::= ENG|0203040246 | 2| I| [:] ##0203040133<variable> = <erg spec> ENG|0203040246 | 4| | <erg spec> ::= ENG|0203040246 | 5| | SUM ( <columnid> ) ENG|0203040246 | 6| | | AVG ( <columnid> ) ENG|0203040246 | 7| | | COUNT ( <columnid> ) ENG|0203040246 | 8| | | MIN ( <columnid> ) ENG|0203040246 | 9| | | MAX ( <columnid> ) ENG|0203040246 | 10| | | VAL1 ( <columnid> ) ENG|0203040246 | 11| | | VAL2 ( <columnid> ) ENG|0203040246 | 12| | | VAL3 ( <columnid> ) ENG|0203040246 | 13| | | VAL4 ( <columnid> ) ENG|0203040246 | 15| | <columnid> ::= ENG|0203040246 | 16|R | <natural> ENG|0203040247 | 1| | <edit call> ::= ENG|0203040247 | 2| I| EDIT ( ##0203040134<vector^slice> [, <edit option>,...] ) ENG|0203040247 | 3| I| | EDIT ( ##0203040133<variable> [, <edit option>,...] ) ENG|0203040247 | 5| | <edit option> ::= ENG|0203040247 | 6| I| POS ( ##0203040136<expr> ,<expr>) ENG|0203040247 | 7| | | SIZE (<expr>,<expr>) ENG|0203040247 | 8| | | MSG = <expr> ENG|0203040247 | 9| | | ERR = <expr> ENG|0203040247 | 10| | | LABEL = <expr> ENG|0203040247 | 11| | | MARK (<expr>,<expr>) ENG|0203040247 | 12| | | HEADER = <expr> ENG|0203040247 | 13| | | PROGNAME = <expr> ENG|0203040247 | 14| | | MAXLINES = <expr> ENG|0203040247 | 15| | | PRINT ENG|0203040247 | 16| | | NOINIT ENG|0203040247 | 17|R | | <basic key> = <expr> ENG|0203040248 | 1| | <write stmt> ::= ENG|0203040248 | 2| | WRITE <write expr>,... ENG|0203040248 | 4| | <write expr> ::= ENG|0203040248 | 5| I| ##0203040136<expr> ENG|0203040248 | 6| | | HIGH ENG|0203040248 | 7| | | SIZE (<expr>) ENG|0203040248 | 8| | | POS (<expr>,<expr>) ENG|0203040248 | 9| | | PAUSE ENG|0203040248 | 10| | | CLEAR ENG|0203040248 | 11| | | NL [(<natural>)] ENG|0203040248 | 12| | | COL (<natural>) ENG|0203040248 | 13| | | HI (<expr>,...) ENG|0203040248 | 14| | | NOCLEAR ENG|0203040248 | 15| | | OPEN ENG|0203040248 | 16| | | CLOSE ENG|0203040248 | 17|R | | HOLD ENG|0203040249 | 1| | <read stmt> ::= ENG|0203040249 | 2| | READ <read expr>,... ENG|0203040249 | 4| | <read expr> ::= ENG|0203040249 | 5| I| ##0203040133<variable> ENG|0203040249 | 6| | | HIGH ENG|0203040249 | 7| | | DARK ENG|0203040249 | 8| I| | SIZE ( ##0203040136<expr> ) ENG|0203040249 | 9| | | POS (<expr>,<expr>) ENG|0203040249 | 10| | | CLEAR ENG|0203040249 | 11| | | NL [(<natural>)] ENG|0203040249 | 12| | | COL (<natural>) ENG|0203040249 | 13| I| | PROMPT '##0203040127<any^char>' ENG|0203040249 | 14|RI| | OUTIN ( ##0203040133<variable> ) ENG|0203040250 | 1| | <open file stmt> ::= ENG|0203040250 | 2|R | OPEN <fileid> <filename> <open spec> ENG|0203040251 | 1| | <close file stmt> ::= ENG|0203040251 | 2|R | CLOSE <fileid> ENG|0203040252 | 1| | <write file stmt> ::= ENG|0203040252 | 2|RI| WRITEFILE <fileid> ##0203040253<write^file^args> ENG|0203040253 | 1| | <write file args> ::= ENG|0203040253 | 2|RI| ##0203040136<expr> [: <expr>] [,<write file args>] ENG|0203040254 | 1| | <read file stmt> ::= ENG|0203040254 | 2|RI| READFILE <fileid> ##0203040255<read^file^args> ENG|0203040255 | 1| | <read file args> ::= ENG|0203040255 | 2|RI| ##0203040133<variable> [: ##0203040136<expr> [,<read file args>] ] ENG|0203040256 | 1|RI| <writetrace stmt> ::= WRITETRACE ##0203040253<write^file^args> ENG|0203040257 | 1| | <exec command> ::= ENG|0203040257 | 2| I| EXEC [SYNC] ##0203040258<operating^system^command> RESULT variable ENG|0203040257 | 3|R | | EXEC ASYNC <operating system command> ENG|0203040258 | 1| | <operating system command> ::= ENG|0203040258 | 2| | arbitrary command or ENG|0203040258 | 3|R | arbitrary program call of operating system. ENG|0203040259 | 1| | <arith function> ::= ENG|0203040259 | 2| I| ABS ( ##0203040136<expr> ) ENG|0203040259 | 3| | | SQR (<expr>) ENG|0203040259 | 4| | | ROUND (<expr>,<expr>) ENG|0203040259 | 5| | | SQRT (<expr>) ENG|0203040259 | 6| | | TRUNC (<expr>,<expr>) ENG|0203040259 | 7| | | SIN (<expr>) ENG|0203040259 | 8| | | COS (<expr>) ENG|0203040259 | 9| | | ARCTAN (<expr>) ENG|0203040259 | 10| | | EXP (<expr>) ENG|0203040259 | 11| | | LN (<expr>) ENG|0203040259 | 12| I| | INDEX ( ##0203040134<vector^slice> ,<expr>) ENG|0203040259 | 13| | | LENGTH(<expr>) ENG|0203040259 | 14| | | ORD (<expr>) ENG|0203040259 | 15| I| | ##0203040260<index^function> ENG|0203040259 | 16| I| | ##0203040302<set^function> ENG|0203040259 | 17|SI| | ##0203040301<strpos^function> ENG|0203040259 | 19|RI| | ##0203040304<sign^function> ENG|0203040260 | 1| | <index function> ::= ENG|0203040260 | 2| I| INDEX ( ##0203040134<vector^slice> , [NOT] ##0203040136<expr> ) ENG|0203040260 | 3|R | | INDEX (<vector slice>, [NOT] NULL) ENG|0203040301 | 1| | <strpos function> ::= ENG|0203040301 | 2| I| STRPOS ( ##0203040136<expr>, <expr> [, ##0203040137<num^expr> ] ) ENG|0203040301 | 3| | | SPAN ( <expr>, <expr> [, <num expr> ] ) ENG|0203040301 | 4|R | | BREAK ( <expr>, <expr> [, <num expr> ] ) ENG|0203040302 | 1| | <set function> ::= ENG|0203040302 | 2| I| MIN ( ##0203040303<mixed^expr> ,...) ENG|0203040302 | 3| | | MAX (<mixed expr>,...) ENG|0203040302 | 4|R | | AVG (<mixed expr>,...) ENG|0203040303 | 1| | <mixed expr> ::= ENG|0203040303 | 2|RI| ##0203040136<expr> | ##0203040134<vector^slice> ENG|0203040304 | 1| | <sign function> ::= ENG|0203040304 | 2|RI| SIGN ( ##0203040136<expr> ) ENG|0203040305 | 1| | <string function> ::= ENG|0203040305 | 2| I| TRIM ( <expr [ , ##0203040127'<any^char>' ] ) ENG|0203040305 | 3| I| | TRIM ( ##0203040136<expr> ,'<any char>',<side>) ENG|0203040305 | 4| | | PAD (<expr>[, <expr>]) ENG|0203040305 | 5| | | PAD (<expr>, <expr>,<side>) ENG|0203040305 | 6| | | SUBSTR(<expr>,<expr> [,<expr>] ) ENG|0203040305 | 7| | | UPPER (<expr>) ENG|0203040305 | 8| | | LOWER (<expr>) ENG|0203040305 | 9| | | FORMAT (<expr>,'<char>...') ENG|0203040305 | 10| | | HEX (<expr>) ENG|0203040305 | 11| | | CHR (<expr>) ENG|0203040305 | 12| I| | CHANGE ( <expr>, <expr> [, ##0203040137<num^expr> ] ) ENG|0203040305 | 13| | | TOKENIZE (<expr>, <expr>) ENG|0203040305 | 14| | | SEPARATE (<expr>, <expr>) ENG|0203040305 | 16|R | <side> ::= RIGHT/LEFT/BOTH ENG|0203040307 | 1| | <date function> ::= ENG|0203040307 | 2| I| ##0203040309<date^arith^function> ENG|0203040307 | 3|RI| | ##0203040308<date^str^function> ENG|0203040308 | 1| | <date str function> ::= ENG|0203040308 | 2| I| DATE [ ([<date mask>] , ##0203040136<expr> [,<date mask>]) ] ENG|0203040308 | 3| | | TIME [ ([<time mask>] ,<expr> [,<time mask>])] ENG|0203040308 | 4| | | ADDDATE (<expr>,<expr>) ENG|0203040308 | 5| | | SUBDATE (<expr>,<expr>) ENG|0203040308 | 6| | | MAKETIME (<expr>,<expr>,<expr>) ENG|0203040308 | 7| | | ADDTIME (<expr>,<expr>) ENG|0203040308 | 8|R | | SUBTIME (<expr>,<expr>) ENG|0203040309 | 1| | <date arith function> ::= ENG|0203040309 | 2| I| DAYOFWEEK ( ##0203040136<expr> ) ENG|0203040309 | 3| | | WEEKOFYEAR (<expr>) ENG|0203040309 | 4| | | DAYOFYEAR (<expr>) ENG|0203040309 | 5| | | DATEDIFF (<expr>,<expr>) ENG|0203040309 | 6|R | | TIMEDIFF (<expr>,<expr>) ENG|0203040310 | 1| | <set function> ::= ENG|0203040310 | 2|RI| SET ( ##0203040312<set^id> ) ENG|0203040311 | 1| | <set stmt> ::= ENG|0203040311 | 2|RI| SET ( ##0203040312<set^id> , ##0203040136<expr> ) ENG|0203040312 | 1| | <set id> ::= ENG|0203040312 | 2| | COPIES ENG|0203040312 | 3| | | CODEAREA ENG|0203040312 | 4| | | DATE ENG|0203040312 | 5| | | DECIMALREP ENG|0203040312 | 6| | | NESTING ENG|0203040312 | 7| | | NULLVALUE ENG|0203040312 | 8| | | LANGUAGE ENG|0203040312 | 9| | | PRESENTATION ENG|0203040312 | 10| | | PRETTY ENG|0203040312 | 11| | | PRINTFORMAT ENG|0203040312 | 12| | | PROTOCOL ENG|0203040312 | 13| | | SEPARATOR ENG|0203040312 | 14| | | SYSEDITOR ENG|0203040312 | 15| | | TARGET_LANGS ENG|0203040312 | 16| | | TIME ENG|0203040312 | 17|S | | VERSION ENG|0203040312 | 19|R | | VALUEAREA ENG|0203040313 | 1| | <systime func> ::= ENG|0203040313 | 2|R | INITTIME | GETTIME ENG|0203040314 | 1| | <dollar numeric variable> ::= ENG|0203040314 | 2| | $COLNO ENG|0203040314 | 3| | | $COUNT ENG|0203040314 | 4| | | $CURSOR ENG|0203040314 | 5| | | $EDITLINES ENG|0203040314 | 6| | | $MAXLINES ENG|0203040314 | 7| | | $MAXCOLS ENG|0203040314 | 8| | | $MICRO ENG|0203040314 | 9| | | $RC ENG|0203040314 | 10| | | $ROWNO ENG|0203040314 | 11| | | $SCREENCOLS ENG|0203040314 | 12| | | $SCREENLNS ENG|0203040314 | 13| | | $SEC ENG|0203040314 | 14|R | | $SYSRC ENG|0203040315 | 1| | <dollar string variable> ::= ENG|0203040315 | 2| | $ACTION ENG|0203040315 | 3| | | $CMD ENG|0203040315 | 4| | | $FUNCTION|$FUNCTION1...|$FUNCTION4 ENG|0203040315 | 5| | | $GROUP ENG|0203040315 | 6| | | $KEY ENG|0203040315 | 7| | | $OS ENG|0203040315 | 8| | | $RT ENG|0203040315 | 9| | | $SERVERDB ENG|0203040315 | 10| | | $SYSRT ENG|0203040315 | 11| | | $TERM ENG|0203040315 | 12| | | $USER ENG|0203040315 | 13|R | | $USERMODE ENG|0203040316 | 1| | <dollar boolean variable> ::= ENG|0203040316 | 2| | $SQLWARN ENG|0203040316 | 3|RI| | $SQLWARN [( ##0203040136<expr> )] ENG|0203040317 | 1| | <object header> ::= ENG|0203040317 | 2| | <object type> <progname>.<modname> ENG|0203040317 | 3| I| OPTION/S/ ( ##0203040318<module^option> ,... ) ENG|0203040317 | 5| | <object type> ::= ENG|0203040317 | 6| | PROC ENG|0203040317 | 7| | | FUNCTION ENG|0203040317 | 8| | | DBPROC ENG|0203040317 | 9| | | TRIGGER ENG|0203040317 | 10| | | FORM ENG|0203040317 | 11|R | | HELPFORM ENG|0203040318 | 1| | <module option> ::= ENG|0203040318 | 2| | <loop option> ENG|0203040318 | 3| | | <autocommit option> ENG|0203040318 | 4| | | <trace option> ENG|0203040318 | 5| | | <test dbproc option> ENG|0203040318 | 6| | | <lib option> ENG|0203040318 | 7| | | <keyswap option> ENG|0203040318 | 9| | <loop option> ::= ENG|0203040318 | 10| | LOOP /=/ <natural> ENG|0203040318 | 12| | <autocommit option> ::= ENG|0203040318 | 13| | AUTOCOMMIT OFF ENG|0203040318 | 15| | <trace option> ::= ENG|0203040318 | 16| | SQLTRACE /ALL/ ENG|0203040318 | 17|S | | MODULETRACE ENG|0203040318 | 19| | <test dbproc option> ::= TEST DBPROC ENG|0203040318 | 21| | <lib option> ::= ENG|0203040318 | 22| | LIB [<username>.] <libname> ENG|0203040318 | 24| | <keyswap option> ::= ENG|0203040318 | 25|R | <keyswap spec> ENG|02030405 | 1| | Keywords ENG|02030405 | 2| | -------- ENG|02030405 | 4| I| ##0203030102$ACTION ##0203030102$CMD ##0203030109$COLNO ##0203030104$COUNT ##0203030102$CURSOR ENG|02030405 | 5| I| ##0203030102$EDITLINES ##0203030102$FUNCTION ##0203030101$GROUP ##0203030110$ITEMS ##0203030102$KEY ENG|02030405 | 6| I| ##0203030107$KEYLINES ##0203030105$MAXLINES ##0203030105$MAXCOLS ##02030211$MICRO ##0203030107$MSGLINES ENG|02030405 | 7| I| ##0203030108$OS ##0203030108$PROCESSID ##0203030103$RC ##0203030109$COLNO ##0203030103$RT ENG|02030405 | 8| I| ##0203030105$SCREENCOLS ##0203030105$SCREENLNS ##02030211$SEC ##0203030101$SERVERDB ##0203030103$SQLCODE ENG|02030405 | 9| I| ##0203030103$SQLERRMC ##0203030103$SQLERRPOS ##0203030106$SQLWARN ##0203030110$SYSRC ##0203030110$SYSRT ENG|02030405 | 10| I| ##0203030108$TERM ##0203030101$USER ##0203030101$USERMODE ENG|02030405 | 12| I| ##0203030207ABS ##050408ACCEPT ##0203030402ADDDATE ##0203030404ADDTIME ##0203011004ALL ENG|02030405 | 13| I| ##0203011003ALPHA ##0203011001AND ##0203011004ANY ##0203030203ARCTAN ##0203030201AVG ENG|02030405 | 15| I| ##02030404BEGIN ##0203011002BETWEEN ##0203030301BLANK ##0203030312BREAK ENG|02030405 | 17|SI| ##02030106CALL ##02030104CASE ##02030116CATCH ##0203030307CHANGE ##0203030308CHR ENG|02030405 | 19| I| ##05040404CHECK ##02030202CLEAR ##0203020601CLOSE ##02030202CMDKEY ##02030202COL ENG|02030405 | 20| I| ##0203020406CONNECT ##050411CONTROL ##0203030203COS ##050607CPAGE ENG|02030405 | 22| I| ##02030203DARK ##0203030401DATE ##0203030402DATEDIFF ##0203030403DAYOFWEEK ##0203030403DAYOFYEAR ENG|02030405 | 23| I| ##0211DBPROC ##0203011005DELETING ##020302040202DESCRIBE ##02030401DIV ##02030112DO ENG|02030405 | 24| I| ##050408DOWN ##050408DOWNKEY ##02030112DOWNTO ENG|02030405 | 26| I| ##0203011004EACH ##02030207EDIT ##02030103ELSE ##02030404END ##050408ENDKEY ENG|02030405 | 27| I| ##0502ENDLAYOUT ##110301ENDMODULE ##050408ENTER ##0203011003EOF ##0502ERROR ENG|02030405 | 28| I| ##050409EXCEPT ##02030212EXEC ##0203020402EXECUTE ##0203030204EXP ENG|02030405 | 30| I| ##0203020401FETCH ##050404FIELD ##0203011005FIRSTCALL ##0203011003FIXED ##02030112FOR ENG|02030405 | 31| I| ##0502FORM ##0203030304FORMAT ##050603FORMPOS ##020701FUNCTION ##050408F1^..^F12 ENG|02030405 | 33| I| ##02030211GETIME ##02030209GTSORT ENG|02030405 | 35|SI| ##050408HELP ##05040403HELPFORM ##050408HELPKEY ##0203030309HEX ##02030202HI ENG|02030405 | 37| I| ##02030202HIGH ENG|02030405 | 39| I| ##02030103IF ##050409IGNORE ##050403IN ##0203030305INDEX ##05040402INIT ENG|02030405 | 40| I| ##02030211INITTIME ##021102INOUT ##0203011005INSERTING ##0203020403INTO ##02030110IS ENG|02030405 | 42| I| ##02030207KEYSWAP ENG|02030405 | 44| I| ##0203011005LASTCALL ##0502LAYOUT ##050408LEFT ##050408LEFTKEY ENG|02030405 | 45| I| ##0203030303LENGTH ##0203011002LIKE ##050607LINEFEED ##050607LINESPACE ##0203030204LN ENG|02030405 | 46| I| ##050402LOW ##0203030310LOWER ##02030209LTSORT ENG|02030405 | 48| I| ##0203030405MAKETIME ##0203030201MAX ##0203030205MDS ##0502MESSAGE ##0203030201MIN ENG|02030405 | 49| I| ##02030401MOD ##0203011003MODIFIED ENG|02030405 | 51| I| ##050607NEWPAGE ##050905NEXTFIELD ##050906NEXTGROUP ##02030202NL ##050606NOINIT ENG|02030405 | 52|SI| ##0203011001NOT ##0203011003NULL ##0203011003NUMERIC ENG|02030405 | 55| I| ##02030104OF ##0203011004ONE ##0203020601OPEN ##020305OPTION ##020305OPTIONS ENG|02030405 | 56| I| ##0203011001OR ##0203030308ORD ##02030104OTHERWISE ##050403OUT ##02030203OUTIN ENG|02030405 | 58| I| ##0203030314PAD ##050405PAGE ##02030106PARM ##02030106PARMS ##02030202PAUSE ENG|02030405 | 59| I| ##050408PF1^..^PF12 ##0203030205PI ##050406PICK ##02030202POS ##020302040202PREPARE ENG|02030405 | 60| I| ##050607PRINT ##02030106PROC ##02030203PROMPT ##050406PUT ENG|02030405 | 62| I| ##0203020502QUERY ENG|02030405 | 64| I| ##02030203READ ##0203020602READFILE ##0203020406RELEASE ##02030101REPEAT ##02030205REPORT ENG|02030405 | 65| I| ##02030105RETURN ##050416RETURNONLAST##050408RIGHT ##050408RIGHTKEY ##0203030202ROUND ENG|02030405 | 67| I| ##050603SCREENPOS ##050603SCREENSIZE ##0203030315SEPARATE ##02030210SET ##0203030208SIGN ENG|02030405 | 68| I| ##0203030203SIN ##02030202SIZE ##02030109SKIP ##050408SK1^..^SK9 ##0203011004SOME ENG|02030405 | 69| I| ##0203030313SPAN ##02030204SQL ##0203030206SQR ##0203030206SQRT ##02030111STOP ENG|02030405 | 70| I| ##0203030306STRPOS ##0203030402SUBDATE ##0203030302SUBSTR ##0203030404SUBTIME ##02030108SWITCH ENG|02030405 | 71|SI| ##02030114SWITCHCALL ENG|02030405 | 73| I| ##02030103THEN ##0203030401TIME ##0203030404TIMEDIFF ##02030112TO ##0203030315TOKENIZE ENG|02030405 | 74| I| ##0211TRIGGER ##0203030303TRIM ##0203030202TRUNC ##02030116TRY ENG|02030405 | 76| I| ##02030101UNTIL ##050408UP ##0203011005UPDATING ##050408UPKEY ##0203030310UPPER ENG|02030405 | 78|RI| ##0203030403WEEKOFYEAR ##050409WHEN ##02030102WHILE ##02030202WRITE ##0203020603WRITEFILE ENG|020305 | 1| | Some specifications which are listed in the following may be made in ENG|020305 | 2| | the OPTIONS statement (directly after the module header). ENG|020305 | 4| | LOOP <number> delimits the number of iterations for FOR, WHILE, and ENG|020305 | 5| | REPEAT loops to the specified number. If this number is exceeded, ENG|020305 | 6| | program execution is cancelled and the message 'endless loop ENG|020305 | 7| | assumed' is output. ENG|020305 | 9| | AUTOCOMMIT OFF has the effect that in this routine no implicit COMMIT ENG|020305 | 10| | WORK is performed before user input (READ, FORM call). Moreover, ENG|020305 | 11| | the error 700 ( Command inactivity timeout ) is passed and must be ENG|020305 | 12| | intercepted by the program. ENG|020305 | 14| | MODULETRACE has the effect that all subroutine calls made since the ENG|020305 | 15| | start of this routine are recorded in a file. The name of this ENG|020305 | 16|S | file can be defined via the workbench command SET (protocol). ENG|020305 | 19| | SQLTRACE has the effect that all SQL calls performed in the routine ENG|020305 | 20| | are recorded in a file. ENG|020305 | 22| | SQLTRACE ALL has the effect that all subsequent SQL calls are recorded ENG|020305 | 23| | in a file. ENG|020305 | 25| | LIB /<user name>./ <library name> has the effect that after this ENG|020305 | 26| | routine has been called user-defined SQL-PL functions are searched ENG|020305 | 27| | in the specified library. The default library is <user>.STDLIB. ENG|020305 | 28| | This setting remains valid, until a new declaration is made or the ENG|020305 | 29| | routine is left. ENG|020305 | 31| I| TEST DBPROC has the effect that all ##02030115CALL^DBPROC statements contained in ENG|020305 | 32| | this routine are translated for a special test mode. As a result, at ENG|020305 | 33| | runtime the actual call of the DB procedure will be simulated, thus ENG|020305 | 34| I| providing the standard test facilities ( ##0209Debugger, SQLTRACE, ENG|020305 | 35|S | MODULETRACE ). ENG|020305 | 37| | SUBPROC has the effect that any statement not allowed in a DB procedure ENG|020305 | 38| | is marked as an error. ENG|020305 | 40| I| ##050410KEYSWAP^Option. ENG|020305 | 42| | Examples : ENG|020305 | 44| | ENG|020305 | 45| E| PROC customer.start OPTIONS ( LOOP 50, AUTOCOMMIT OFF ); ENG|020305 | 46| E| PROC customer.start OPTIONS ( LIB customerfct, SQLTRACE, MODULETRACE ); ENG|020305 | 47| | ENG|020305 | 49|RI| ##0203040318->^Syntax ENG|0207 | 1| | SQL-PL Functions ENG|0207 | 3| | SQL-PL functions help to develop large applications. Functionality ENG|0207 | 4| | required in the entire application ( i.e. in various programs ) can be ENG|0207 | 5| | gathered in a library. The functions can be called without leaving the ENG|0207 | 6| | current program. ENG|0207 | 8| M| #01Declaring Functions ENG|0207 | 9| M| #02Calling Functions ENG|0207 | 10|RM| #03Restrictions ENG|020701 | 1| | Declaring an SQL-PL Function ENG|020701 | 3| | Functions are distinguished from normal SQL-PL routines by the leading ENG|020701 | 4| | keyword FUNCTION. A value can be returned to the calling environment by ENG|020701 | 5| | means of the RETURN statement. ENG|020701 | 7| | Examples : ENG|020701 | 9| | ENG|020701 | 10| E| FUNCTION stdlib.sum PARMS (@s1, @s2, @s3, @s4, @s5); ENG|020701 | 11| E| RETURN (@s1 + @s2 + @s3 + @s4 + @s5); ENG|020701 | 13| E| FUNCTION stdlib.list PARM (@reportname); ENG|020701 | 14| E| REPORT CMD ( TTITLE '':@report name'') ENG|020701 | 15| | ENG|020701 | 17|RI| ##0203040107->^Syntax ENG|020702 | 1| | Calling Functions ENG|020702 | 3| | An SQL-PL function call is preceded by the percent (%) sign. The ENG|020702 | 4| I| function is searched in the library currently set ( ##020305Options and ENG|020702 | 5| | Debugging Aids ). ENG|020702 | 7| | Function calls can be used in expressions, but they may also occur as ENG|020702 | 8| | statements in their own right. ENG|020702 | 10| | Examples : ENG|020702 | 12| | ENG|020702 | 13| E| sum := %sum (a,b,c,d,e); ENG|020702 | 14| E| %list ('customers'); ENG|020702 | 15| | ENG|020702 | 17|S | When used in expressions, a value must be returned in the function by ENG|020702 | 19| | means of the RETURN statement. ENG|020702 | 21|RI| ##0203040141->^Syntax ENG|020703 | 1| | Restrictions ENG|020703 | 3| | SQL-PL functions must not contain CALL and SWITCH statements; SQL-PL ENG|020703 | 4| | functions may be called. ENG|020703 | 6|R | Global variables are not allowed. ENG|0208 | 1| | Restrictions ENG|0208 | 3| | - 512 different g l o b a l variables. ENG|0208 | 4| | - 255 components per vector. ENG|0208 | 5| | - 12276 byte variable values. ENG|0208 | 6| | - 63 modules per program accessing globals variables ENG|0208 | 7| | - 255 output and input fields per form. ENG|0208 | 8| | - 255 SQL variables per SQL command. ENG|0208 | 9| | - Any number of programs per user. ENG|0208 | 10| | - 255 formal parameters per routine. ENG|0208 | 11| | - 20 arguments for form call option ATTR. ENG|0208 | 12| | - 20 variable arguments for form call option INPUT. ENG|0208 | 13|R | - 255 field number arguments for form call option INPUT. ENG|0209 | 1| | The Debugger ENG|0209 | 2| | ------------ ENG|0209 | 4| M| #00Procedure ENG|0209 | 5| M| #01Displaying^Variables ENG|0209 | 6| M| #02Modifying^Variables ENG|0209 | 7| M| #03Setting^Breakpoints ENG|0209 | 8| M| #04Removing^Breakpoints ENG|0209 | 9| M| #06Displaying^Breakpoints ENG|0209 | 10| M| #05Displaying^System^Variables ENG|0209 | 11|RM| #06Other^Commands ENG|020900 | 1| | The SQL-PL debugging aids ( debugger ) shall help the program ENG|020900 | 2| | developper to test the SQL-PL applications. ENG|020900 | 4| | Therefore the following procedure is required for the debugger: ENG|020900 | 5| I| Entering ##1106DOPT^ON into the command line of the workbench or the editor ENG|020900 | 6| | enables the debug option. The modules to be processed with the debugger ENG|020900 | 7| I| must be retranslated with ##110205STORE. ENG|020900 | 9| I| Entering ##1106DOPT^OFF disables the debug option. ENG|020900 | 11| | The program is executed 'in the usual way', until the first module ENG|020900 | 12| | translated with debug option is called. Then SQL-PL branches to the ENG|020900 | 13| | debugger. The SQL-PL module is displayed in the lower part of the ENG|020900 | 14| | screen. The current statement is displayed brightly in the middle line ENG|020900 | 15| | of the edit form. The UP and DOWN keys can be used for scrolling in the ENG|020900 | 16|S | edit form. ENG|020900 | 19| | The debugger commands described below can be specified in the command ENG|020900 | 20| | line. Displays, if any, are made to the upper part of the screen. ENG|020900 | 23| I| The debugger allows variable contents to be ##020901displayed or ##020902modified; ENG|020900 | 24| I| breakpoints to be ##020903defined, ##020904removed, or ##020906displayed. ENG|020900 | 26| I| The developper may have the ##020905System^Variables and the ##020906Call^Sequence ENG|020900 | 27|R | displayed. ENG|020901 | 1| | The value of individual variables, vector components, and vector slices ENG|020901 | 2| | of the current program can be displayed. ENG|020901 | 4| | V <varname> ENG|020901 | 5| | V <varname> ( <index> ) ENG|020901 | 6| | V <varname> ( <lower index> .. <upper index> ) ENG|020901 | 8| | Examples : ENG|020901 | 10| | V counter ENG|020901 | 11| | V list(1..10) ENG|020901 | 12|R | V @customerno(5) ENG|020902 | 1| | The value of individual variables, vector components, and vector slices ENG|020902 | 2| | of the current program can be modified. ENG|020902 | 4| | A number or a character string can be specified here. Character strings ENG|020902 | 5| | are to be specified without single quotes. They are accepted without ENG|020902 | 6| | any changes. ENG|020902 | 8| | If no value is specified after the variable name, the NULL value is ENG|020902 | 9| | assigned to this variable. ENG|020902 | 11| | A <varname> /<value>/ ENG|020902 | 12| | A <varname> ( <index> ) /<value>/ ENG|020902 | 13| | A <varname> ( <lower index> .. <upper index> ) /<value>/ ENG|020902 | 15| | Example : ENG|020902 | 17|S | A counter 10 ENG|020902 | 19| | A @customerno(2) C1234 ENG|020902 | 20|R | A list(1..10) ENG|020903 | 1| | The B function can be used for defining breakpoints in routines which ENG|020903 | 2| | are capable of being debugged. ENG|020903 | 4| | B <program>/<module>/<line number> ENG|020903 | 5| | B <line number> ENG|020903 | 7| | Program execution is interrupted before performing the statement in the ENG|020903 | 8| | editor line <line number>. If no module is specified, the breakpoint is ENG|020903 | 9| | inserted into the corresponding line of the current module. ENG|020903 | 11| | BV <varname> ENG|020903 | 12| | BV <varname> ( <index> ) ENG|020903 | 14| | Program execution is interrupted when the value of the specified ENG|020903 | 15|S | variables was changed. ENG|020903 | 19| | Examples : ENG|020903 | 21| | B customer/start/0005 ENG|020903 | 22| | B 5 ENG|020903 | 23| | BV counter ENG|020903 | 24|R | BV @customerno(2) ENG|020904 | 1| | The R function can be used for deactivating the specified breakpoint. ENG|020904 | 3| | R <program>/<module>/<line number> ENG|020904 | 4| | R <line number> ENG|020904 | 6| | RV <varname> ENG|020904 | 7| | RV <varname> ( <index> ) ENG|020904 | 9| | Examples : ENG|020904 | 11| | R customer/start/0005 ENG|020904 | 12| | R 5 ENG|020904 | 13| | RV counter ENG|020904 | 14|R | RV @customerno(2) ENG|020905 | 1| | $ displays the dollar variables : ENG|020905 | 2| | $USER, $GROUP, $DBNAME, $FILERC, $RC, $COUNT, ENG|020905 | 3| | $CURSOR, $EDITLINES, $SEC, $MICRO, $ROWNO, $COLNO, ENG|020905 | 4| | $SCREENLNS, $SCREENCOLS, $MAXLINES, $MAXCOLS, ENG|020905 | 5| | $MSGLINES, $KEYLINES, $CMD, $KEY ENG|020905 | 7|R | $<name> displays the value of the specified system variables. ENG|020906 | 1| | L displays a list of all breakpoints ENG|020906 | 2| | defined for statements. ENG|020906 | 4| | LV displays a list of all breakpoints ENG|020906 | 5| | defined for variables. ENG|020906 | 7| | T displays the sequence of the called modules. ENG|020906 | 9| | G continues program execution up to the next ENG|020906 | 10| | breakpoint or up to the end of program. ENG|020906 | 12| | H has the effect that the program is terminated ENG|020906 | 13| | at once. ENG|020906 | 15| | S enables or disables the single step mode. ENG|020906 | 17|S | X displays the values of the parameters passed. ENG|020906 | 19| | ? or HELP key branches to the HELP mode. ENG|020906 | 20|R | RESET key clears the upper part of the screen. ENG|0211 | 1| | DB Procedures ENG|0211 | 2| | ------------- ENG|0211 | 4| M| #01Restrictions ENG|0211 | 5| M| #02Parameter Declarations ENG|0211 | 6| M| #05Trigger Parameter Declarations ENG|0211 | 7| M| #03Restricted Statements ENG|0211 | 8| M| #04Call ENG|0211 | 9| I| ##1107Administration of DB procedures ENG|0211 | 10|RI| ##1108Administration of trigger procedures ENG|021101 | 1| | Restrictions in DB Procedures ENG|021101 | 3| | If an SQL-PL routine shall be declared a DB procedure, the following ENG|021101 | 4| | restrictions apply in addition : ENG|021101 | 6| I| - the ##021102parameters of the procedure must be declared. ENG|021101 | 8| | - the size of a procedure is restricted to a quarter of the maximum ENG|021101 | 9| | size of a normal SQL-PL routine. ENG|021101 | 11| | - all modules called directly or indirectly must be available, they ENG|021101 | 12| | are subject to the same restrictions. ENG|021101 | 14| I| - not all ##021103SQL-PL statements are permitted ENG|021101 | 16|R | - the compiler options DEBUG and SQLSYNTAX are ignored. ENG|021102 | 1| | Parameter Declaration in DB Procedures ENG|021102 | 3| | DB procedures can also be called out of precompiler programs. As their ENG|021102 | 4| | programming languages are subject to a type concept other than SQL-PL, ENG|021102 | 5| | it is necessary to declare the parameters of the main procedure ENG|021102 | 6| | according to mode and data type. ENG|021102 | 8| | Mode is ENG|021102 | 10| | - IN (input parameter) ENG|021102 | 12| | - OUT (output parameter) ENG|021102 | 14| | - INOUT (input/output parameter) ENG|021102 | 16| | The type is equivalent to the SQL data types of ADABAS : ENG|021102 | 17|S | FIXED, FLOAT, CHAR (BYTE/DBYTE), TIME, DATE, TIMESTAMP. ENG|021102 | 19| | A parameter of type LONG cannot be declared. NOT NULL, default values, ENG|021102 | 20| | and constraints are not supported either. ENG|021102 | 22| | These declarations are only important for the parameter transfer; ENG|021102 | 23| | within the DB procedure the parameter variables can assume any type. ENG|021102 | 25| | Example : ENG|021102 | 27| | DBPROC hotel.reservation ( ENG|021102 | 28| | IN @customername CHAR(40), ENG|021102 | 29| | IN @day DATE, ENG|021102 | 30| | OUT @hotel CHAR(20), ENG|021102 | 31| | OUT @costs FIXED(6,2) ); ENG|021102 | 34|RI| ##0203040110->^Syntax ENG|021103 | 1| | In DB procedures it is not permitted ENG|021103 | 4| | to make output to the screen, the printer, or an operating system file : ENG|021103 | 5| | WRITE, READ ENG|021103 | 6| | CALL FORM, EDIT, REPORT, QUERY ENG|021103 | 7| | WRITEFILE, READFILE, OPEN, CLOSE ENG|021103 | 8| | option MODULETRACE, SQLTRACE ENG|021103 | 10| | to change the current program ENG|021103 | 11| | SWITCH, SWITCHCALL ENG|021103 | 13| | to use the extended SQL interface : ENG|021103 | 14| | dynamic SQL ENG|021103 | 15| | multi-DB accessibility ENG|021103 | 17|S | to perform DB procedures : ENG|021103 | 19| | CALL DBPROC ENG|021103 | 20| | SQL ( DBPROCEDURE ... ) ENG|021103 | 21| | Calling the corresponding SQL-PL routine, however, is possible via the ENG|021103 | 22| | CALL PROC statement. ENG|021103 | 24| | to conclude a transaction : ENG|021103 | 25| | SQL ( COMMIT ); SQL ( ROLLBACK ) ENG|021103 | 26|R | Subtransactions are permitted. ENG|021104 | 1| | Calling DB Procedures ENG|021104 | 3| | Calling from SQL-PL : ENG|021104 | 5| I| ##02030115CALL^DBPROC Statement ENG|021104 | 8| | Calling from precompiler programs : ENG|021104 | 10| | EXEC SQL ( DBPROCEDURE owner.appl.module ( :var1, :var2, ... ) ) ENG|021104 | 12|R | For details see the precompiler documentation. ENG|021105 | 1| | Trigger parameter declarations ENG|021105 | 3| | Parameters of a trigger have to be declared like those of a DB ENG|021105 | 4| I| procedure according to ##021102mode and ##021102data^type. The parameter names cannot ENG|021105 | 5| | be chosen at random, but have to match the column names of the table ENG|021105 | 6| | for which the trigger is to be defined. Parameters may be additionally ENG|021105 | 7| | qualified by the prefixes "NEW." and "OLD." to access new ( INSERT, ENG|021105 | 8| | UPDATE ) or old column values ( DELETE, UPDATE ), respectivly. ENG|021105 | 9| | Beispiel : ENG|021105 | 11| | ENG|021105 | 12| E| TRIGGER catalog.change_price ( ENG|021105 | 13| E| IN OLD.price FIXED ( 10, 3 ), ENG|021105 | 14| E| IN NEW.price FIXED ( 10, 3 ) ); ENG|021105 | 16| E| IF NEW.price > ( OLD.price * 1.1 ) ENG|021105 | 17|SE| THEN ENG|021105 | 19| E| STOP ( -999, 'Price increase of more than 10 % not allowed' ); ENG|021105 | 20|RE| E$}