Hello,
I'm working quite alot with databases these days and therefore came up with the idea to write one on my own ;)
So here's my initial version, that support group and sort* with the help of SysStemSort (available in the Object Rexx version of rexxutil included with eComStation)
Please feel free to try it out.
There are hand made functions at the end of the script that may be handy to use elsewhere as well.
Usage:
Example for table "files" with some columns related to file info
CREATE TABLE files COLUMNS (COMPUTER,DRIVE,PATH,NAME,FILESIZE,CRC32,MODIFIED)
INSERT INTO files (DRIVE,PATH,NAME) VALUES ('J:','\Temp\','MyFile.txt')
UPDATE files SET COMPUTER = 'eComStation PC' WHERE PATH = '\Temp\'
SELECT * FROM files WHERE DRIVE = 'J:' GROUP BY COMPUTER ORDER BY PATH
DELETE FROM files WHERE COMPUTER != 'eComStation PC'
VACUUM TABLE files WHERE DRIVE != 'J:'
Notice!
The engine doesn't care about
duplicate data (so you may add the same info several times)
primary keys
or
relations between tables
You can leave out columns in SELECT and INSERT INTO ... and add them unorderd, the "engine" will take care of how to match the entered data to the table layout on disk, just as other database engines do.
INSERT INTO files (PATH,DRIVE,NAME) VALUES ('\Temp\','J:','MyFile.txt')
and
INSERT INTO files (DRIVE,PATH,NAME) VALUES ('J:','\Temp\','MyFile.txt')
in the example will both be saved to the table created by
CREATE TABLE files COLUMNS (COMPUTER,DRIVE,PATH,NAME,FILESIZE,CRC32,MODIFIED)
as
"","J:","\Temp\","MyFile.txt","","",""
The engine contain code to handle strings that contain ' and , that would otherwise cause problems.
You may want to use != to exclude data:
rxdb SELECT * FROM files WHERE DRIVE != 'C:'
to exclude entries that contain C: in the column DRIVE as you can't use <> on the command line.
*SysStemSort sort data according to each characters ascii value.
Therefore, add numeric data formatted/padded with space or zeros in front if you want to sort by something like FILESIZE.
Example (non padding):
0
16
168
5
54
75
91
99
Example (space):
0
5
16
54
75
91
99
168
which can be achieved with RIGHT( mynumber, 3 ) for numbers up to 999
/*
* Filename: rxdb.cmd
* Author: JAN-ERIK LÄRKA
* Created: Sat Dec 24 2011
* Purpose: Database engine entirly written in REXX
* Changes:
*/
/* Don't echo commands */
'@ECHO OFF'
/* place argument in variable */
sqlstr.input = ARG(1)
/* Add procedures to handle problems */
SIGNAL ON ERROR NAME SignalError
SIGNAL ON FAILURE NAME SignalFailure
SIGNAL ON HALT NAME SignalHalt
SIGNAL ON NOVALUE NAME SignalNoValue
SIGNAL ON SYNTAX NAME SignalSyntax
SIGNAL ON NOTREADY NAME SignalNotReady
/* Load RexxUtil Library */
IF RxFuncQuery('SysLoadFuncs') THEN
Do
CALL RxFuncAdd 'SysLoadFuncs', 'RexxUtil', 'SysLoadFuncs'
CALL SysLoadFuncs
END
values = ''
columns = ''
/* An array with supported arguments */
sqlstr.1 = "'SELECT 'columns' FROM 'table' 'rest"
sqlstr.2 = "'INSERT INTO 'table' ('columns') VALUES ('rest"
sqlstr.3 = "'UPDATE 'table' SET 'rest"
sqlstr.4 = "'DELETE FROM 'table' 'rest"
sqlstr.5 = "'CREATE TABLE 'table' COLUMNS ('columns')'rest"
sqlstr.6 = "'VACUUM TABLE 'table' 'rest"
sqlstr.0 = 6
/* Check to see what argument has been entered */
DO sqlcase = 1 TO sqlstr.0
IF WORD( sqlstr.input, 1 ) = STRIP( WORD( sqlstr.sqlcase, 1 ),, "'" ) THEN
DO
INTERPRET 'PARSE VALUE sqlstr.input WITH '||sqlstr.sqlcase
/* Fix to handle the case where data may contain ) that would cause problems */
IF sqlcase = 2 THEN
DO
values = WRDS( rest, ',', COUNTSTR( ',', columns ), ')' )
rest = SUBSTR( rest, LENGTH( values ) + 2 )
END
LEAVE sqlcase
END
END
/* Show usage if argument didn't match those supported */
IF sqlcase > sqlstr.0 THEN SIGNAL VALUE "USAGE"
/* Create an array for the names of the trailing parameters */
sqlstr.1.1 = 'table'
sqlstr.1.2 = ''
sqlstr.1.3 = 'columns'
sqlstr.1.4 = 'table'
sqlstr.1.5 = ''
sqlstr.1.6 = 'table'
/* Create arrays for the optional additional arguments */
sqlvar.2 = 'conditions'
sqlvar.3 = 'group'
sqlvar.4 = 'order'
sqlvar.5 = '.'
sqlclause.2 = 'WHERE'
sqlclause.3 = 'GROUP BY'
sqlclause.4 = 'ORDER BY'
sqlclause.5 = 'DESC'
sqlclause.0 = 5
/* Check if the last word is DESC or D and modify sort order from the default Ascending */
IF SUBWRDS( rest, ',', -1 ) = 'DESC' THEN
sort = '"D"'
ELSE
sort = ''
/* Check the optional arguments */
i = 1
DO j = sqlclause.0 TO 2 BY -1
IF j > 1 & j < 5 THEN INTERPRET sqlvar.j' = ""'
IF POS( sqlclause.j, rest ) > 0 THEN
DO
INTERPRET 'PARSE VALUE rest WITH rest "'||sqlclause.j||'" '||sqlvar.j
IF sqlvar.j <> '.' THEN
INTERPRET sqlvar.j||' = STRIP( VALUE( "'||sqlvar.j||'" ) )'
END
END
/* Name the trailing parameters */
IF LENGTH( STRIP( rest ) ) > 0 THEN
INTERPRET 'PARSE VALUE rest WITH '||sqlstr.1.sqlcase||';IF '||sqlstr.1.sqlcase||' <> "" THEN '||sqlstr.1.sqlcase||' = STRIP( VALUE( "'||sqlstr.1.sqlcase||'" ) )'
/* Combine groups and order as both specify how to order data */
IF ( LENGTH( STRIP( order ) ) > 0 ) THEN
DO
order = order||','
IF ( LENGTH( STRIP( group ) ) > 0 ) THEN
order = ','||order
END
ELSE IF ( LENGTH( STRIP( group ) ) > 0 ) THEN
group = group||','
group = STRIP( CHANGESTR( ',', group||order, '","' ), 'T', '"' )
/* Set file name extension of table */
table = table||'.rxdb'
/* Check if the table has been created */
IF STREAM( table, 'C', 'QUERY EXISTS' ) <> '' THEN
DO
columns = STRIP( CHANGESTR( '"', columns, '' ) )
colnames = LINEIN( table )
cols = COUNTSTR( ',', colnames ) + 1
IF sqlcase = 1 THEN /* SELECT FROM ... */
DO
parse_columns = CHANGESTR( "'", CHANGESTR( '"', colnames, '' ), '' )
columns = CHANGESTR( '*', CHANGESTR( ',', columns, '","' ), colnames )
select_direct = ( LENGTH( group ) = 0 )
/* Modify condition(s) to look at the right "column" */
DO j = 1 TO cols
PARSE VALUE parse_columns WITH colname','parse_columns
conditions = CHANGESTR( colname, conditions, 'STRIP( SUBWRD( input, '||j||', '||"'"||'","'||"'"||' ),, '||"'"||'"'||"' )" )
END
/* Modify the != command line argument to <> (One doesn't work on the command line while the other can't be interpreted) :-/ */
conditions = CHANGESTR( '!=', conditions, '<>' )
count = 0
grps = COUNTSTR( ',', group )
/* Go through each line in the database */
DO WHILE LINES( table ) > 0
input = LINEIN( table )
IF LEFT( input, 1 ) = '*' | LENGTH( input ) = 0 THEN ITERATE
IF LENGTH( conditions ) > 0 THEN
INTERPRET 'result = ('||conditions||')'
ELSE result = 1
/* Does the condition match?! */
IF result THEN
DO
count = count + 1
INTERPRET 'PARSE VALUE input WITH '||STRIP( colnames,, '"' )||';sqlstr.count = '||STRIP( group||columns,, '"' )
IF select_direct THEN SAY sqlstr.count
END
END
/* We need go through this as well if the data has to be sorted */
IF \select_direct THEN
DO
sqlstr.0 = count
IF sqlstr.0 > 0 & LENGTH( order ) > 0 THEN
Call SysStemSort 'sqlstr.', sort
DO count = 1 TO sqlstr.0
SAY SUBWRDS( sqlstr.count, ',', grps )
END
END
CALL STREAM table, 'C', 'CLOSE'
END
ELSE IF sqlcase = 2 THEN /* INSERT INTO... */
DO
/* Set up the arguments with the right style */
columns = CHANGESTR( '*', CHANGESTR( ',', columns, '","' ), colnames )
/* Empty all variables (column names) to recieve data */
INTERPRET 'PARSE VALUE "" WITH '||STRIP( colnames,, '"' )
/* Special handling for ' and , that are crutial parts of the database file format */
INTERPRET 'PARSE VALUE "'||CHANGESTR( D2C(1), CHANGESTR( ",", CHANGESTR( "'", STRIP( CHANGESTR( "','", values, D2C(1) ),, "'" ), D2C(2) ), D2C(3) ), ',' )||'" WITH '||columns||';temp = '||STRIP( colnames,, '"' )
CALL STREAM table, 'C', 'SEEK <0'
/* Write the added data to the database table file */
CALL LINEOUT table, '"'||CHANGESTR( D2C(3), CHANGESTR( ',', CHANGESTR( D2C(2), temp, "'" ), '","' ), ',' )||'"'
CALL STREAM table, 'C', 'CLOSE'
END
ELSE IF sqlcase = 3 | sqlcase = 4 THEN /* UPDATE ... and DELETE FORM ... */
DO
IF sqlcase = 3 THEN /* UPDATE ... */
DO
parse_columns = columns
/* Replace with the input that point to a column name */
DO k = 1 TO cols
PARSE VALUE parse_columns WITH col'='val','parse_columns
col = STRIP( col )
val = STRIP( val )
columns = CHANGESTR( col'='val, columns, 'input = CHANGEWRD( input, '||j||', ",", '||"'"||'"'||"'"||val||"'"||'"'||"'"||' )' )
END
END
/* Go through the database table */
DO WHILE LINES( table ) > 0
input = LINEIN( table )
IF LEFT( input, 1 ) = '*' | LENGTH( input ) = 0 THEN ITERATE
IF LENGTH( conditions ) > 0 THEN
INTERPRET 'result = ('||conditions||')'
ELSE result = 1
/* Mark each matching entry as deleted */
IF result THEN
DO
strpos = STREAM( table, 'C', 'SEEK -'||( LENGTH( input ) + 2 ) )
CALL LINEOUT table, '*'||SUBSTR( input, 2 )
/* Add the modified version last for UPDATE ... */
IF i = 3 THEN /* UPDATE ... */
DO
CALL STREAM table, 'C', 'SEEK <0'
INTERPRET columns
CALL LINEOUT table, input
CALL STREAM table, 'C', 'SEEK ='||( strpos + LENGTH( input ) )
END
END
END
CALL STREAM table, 'C', 'CLOSE'
RETURN 0
END
ELSE IF sqlcase = 6 THEN /* VACUUM TABLE ... */
DO
/* Write the table names to a new temporary database table file */
CALL LINEOUT table||'_tmp', colnames
/* Write each non-deleted row the new temporary database table file */
DO WHILE LINES( table ) > 0
input = LINEIN( table )
IF LEFT( input, 1 ) = '*' THEN ITERATE
CALL LINEOUT table||'_tmp', input
END
CALL STREAM table||'_tmp', 'C', 'CLOSE'
CALL STREAM table, 'C', 'CLOSE'
/* Write all row back to the new database table file */
IF SysFileDelete( table ) = 0 THEN
DO WHILE LINES( table||'_tmp' ) > 0
CALL LINEOUT table, LINEIN( table||'_tmp' )
END
CALL STREAM table||'_tmp', 'C', 'CLOSE'
CALL STREAM table, 'C', 'CLOSE'
CALL SysFileDelete table||'_tmp'
END
ELSE SIGNAL VALUE "TABLEALREADYEXISTS"
END
ELSE IF sqlcase = 5 THEN /* CREATE TABLE ... */
DO
/* Create a file if not found */
CALL LINEOUT table, '"'||CHANGESTR( ',', STRIP( columns,, '"' ), '","' )||'"'
CALL STREAM table, 'C', 'CLOSE'
END
ELSE SIGNAL VALUE "TABLENOTFOUND"
RETURN 0
TableAlreadyExists:
CALL LINEOUT 'STDERR', '[REXX SQL Engine] Table already exists, please specify a new table name or use the existing.'
SAY ''
CALL Usage
RETURN 7
TableNotFound:
CALL LINEOUT 'STDERR', '[REXX SQL Engine] Table not found, create the table first'
CALL LINEOUT 'STDERR', ''
CALL Usage
RETURN 7
Usage:
CALL LINEOUT 'STDERR', 'Usage:'
CALL LINEOUT 'STDERR', ' Example for table "files" with some columns related to file info'
CALL LINEOUT 'STDERR', ''
CALL LINEOUT 'STDERR', ' CREATE TABLE files COLUMNS (COMPUTER,DRIVE,PATH,NAME,FILESIZE,CRC32,MODIFIED)'
CALL LINEOUT 'STDERR', " INSERT INTO files (DRIVE,PATH,NAME) VALUES ('J:','\Temp\','MyFile.txt')"
CALL LINEOUT 'STDERR', " UPDATE files SET COMPUTER = 'eComStation PC' WHERE PATH = '\Temp\'"
CALL LINEOUT 'STDERR', " SELECT * FROM files WHERE DRIVE = 'J:' GROUP BY COMPUTER ORDER BY PATH"
CALL LINEOUT 'STDERR', " DELETE FROM files WHERE COMPUTER != 'eComStation PC'"
CALL LINEOUT 'STDERR', " VACUUM TABLE files WHERE DRIVE != 'J:'"
CALL LINEOUT 'STDERR', ''
RETURN 0
SignalError:
SIGNAL OFF ERROR
CALL LINEOUT 'STDERR', '[REXX SQL Engine] Error: Error processing command.'
CALL LINEOUT 'STDERR', ''
RETURN 1
SignalHalt:
SIGNAL OFF HALT
CALL LINEOUT 'STDERR', '[REXX SQL Engine] Halt: Execution of the command has stopped.'
CALL LINEOUT 'STDERR', ''
RETURN 2
SignalSyntax:
SIGNAL OFF SYNTAX
CALL LINEOUT 'STDERR', '[REXX SQL Engine] Syntax: The syntax of the command is incorrect.'
CALL LINEOUT 'STDERR', ''
CALL LINEOUT 'STDERR', ARG(1)||D2C(13)||D2C(10)||D2C(13)||D2C(10)||'should be on the form: '||SPACE( STRIP( TRANSLATE( sqlstr.sqlcase,, "'" ) ), 1 )||D2C(13)||D2C(10)||D2C(13)||D2C(10)||'where "rest" can be combined (in specified order) with:'
DO i = 2 TO sqlclause.0 - 1
CALL LINEOUT 'STDERR', sqlclause.i||' '||sqlvar.i
END
CALL LINEOUT 'STDERR', sqlclause.i||' (to sort in descending order)'
CALL LINEOUT 'STDERR', ''
CALL STREAM table, 'C', 'CLOSE'
RETURN 3
SignalFailure:
SIGNAL OFF FAILURE
CALL LINEOUT 'STDERR', '[REXX SQL Engine] Failure: The system could not process the command.'
CALL LINEOUT 'STDERR', ''
RETURN 4
SignalNoValue:
SIGNAL OFF NOVALUE
CALL LINEOUT 'STDERR', "[REXX SQL Engine] NoValue: The variable "||CONDITION( 'D' )||" doesn't contain data."
CALL LINEOUT 'STDERR', ''
CALL Usage
RETURN 5
SignalNotReady:
SIGNAL OFF NOTREADY
CALL LINEOUT 'STDERR', '[REXX SQL Engine] Not Ready: Could not process the command.'
CALL LINEOUT 'STDERR', ''
RETURN 6
/* Change word number x in haystack, delimited by "delimiter" to new word */
CHANGEWRD: PROCEDURE /* haystack, wrdpos, <delimiter>, newwrd */
PARSE ARG haystack, wrdpos, delimiter, newwrd
IF delimiter = '' THEN delimiter = ' '
retval = ''
DO i = 2 TO wrdpos
PARSE VALUE haystack WITH pre(delimiter)haystack
retval = retval||pre||delimiter
END
PARSE VALUE haystack WITH pre(delimiter)haystack
RETURN retval||newwrd||delimiter||haystack
/* Count occurances of delimiter after the first occurance of needle */
WRDPOS: PROCEDURE /* needle, haystack<, delimiter> */
PARSE ARG needle, haystack, delimiter
IF POS( needle, haystack ) = 0 THEN RETURN 0
PARSE VALUE haystack WITH pre(needle)post
IF delimiter = '' THEN delimiter = ' '
RETURN COUNTSTR( delimiter, pre ) + 1
/* N:th word in haystack delimited by delimiter (space by delfault) */
SUBWRD: PROCEDURE /* haystack, wrdpos<, delimiter> */
PARSE ARG haystack, wrdpos, delimiter
IF delimiter = '' THEN delimiter = ' '
DO i = 2 TO wrdpos
PARSE VALUE haystack WITH .(delimiter)haystack
END
PARSE VALUE haystack WITH haystack(delimiter).
RETURN haystack
/* Number of word in haystack delimited by delimiter (space by delfault) starting at start word */
SUBWRDS: PROCEDURE /* haystack, delimiter<, start_wrd<, wrds>> */
PARSE ARG haystack, delimiter, start_wrd, wrds
start_pos = 0
end_pos = 0
IF start_wrd = '' THEN start_wrd = 1
IF start_wrd = -1 THEN
start_pos = LASTPOS( delimiter, haystack )
ELSE IF start_wrd = 0 THEN
start_pos = 1
ELSE DO
DO i = 1 TO start_wrd
start_pos = POS( delimiter, haystack, start_pos + 1 )
IF start_pos = 0 THEN RETURN ''
END
END
IF DATATYPE( wrds, 'W' ) THEN
DO
end_pos = start_pos
DO i = 1 TO wrds
end_pos = POS( delimiter, haystack, end_pos + 1 )
IF end_pos = 0 THEN LEAVE i
END
END
IF start_pos > 0 THEN
DO
IF end_pos < 1 | wrds = '' | start_wrd < 0 THEN end_pos = LENGTH( haystack )
haystack = SUBSTR( haystack, start_pos + 1, end_pos - start_pos - 1 )
END
ELSE RETURN ''
RETURN haystack
/* Up to characters in text (haystack) after the N:th number of occurances of delimiter */
WRDS: PROCEDURE /* haystack<, delimiter<, wrdpos<, tochar>>> */
PARSE ARG haystack, delimiter, wrdpos, tochar
IF delimiter = '' THEN delimiter = ' '
IF wrdpos = '' THEN wrdpos = COUNTSTR( delimiter, haystack )
strpos = POS( tochar, haystack, LENGTH( SUBWRDS( haystack, delimiter, 0, wrdpos ) ) + 1 ) - 1
IF strpos > 0 THEN
RETURN SUBSTR( haystack, 1, strpos )
RETURN haystack
/* Replace one string (needle) with another (newneedle) in text (haystack) */
CHANGESTR: PROCEDURE /* needle, haystack <, newneedle> */
PARSE ARG needle, haystack, newneedle
new_haystack = ''
DO WHILE POS( needle, haystack ) > 0
PARSE VALUE haystack WITH pre(needle)haystack
new_haystack = new_haystack||pre||newneedle
END
RETURN new_haystack||haystack
/* Count the number of occurances of needle in haystack from start pos to end pos (whole string by default) */
COUNTSTR: PROCEDURE /* needle, haystack< <, startpos>, endpos> */
IF ARG() < 2 THEN RETURN -1
IF DATATYPE( ARG(3), 'W' ) THEN
next = ARG(3)
ELSE
next = 1
needle = ARG(1)
haystack = ARG(2)
IF DATATYPE( ARG(4), 'W' ) THEN
haystack = SUBSTR( haystack, next, ABS( ARG(4) - next ) )
next = 1
count = 0
DO WHILE next > 0
next = POS( needle, haystack, next )
IF next > 0 THEN DO
next = next + LENGTH( needle )
count = count + 1
END
END
RETURN count
Merry XMas from Sweden
//Jan-Erik
I have some Rexx code written for RxSQL that simplifies a bunch of the calls... I can put it together into a zipfile or something if you think it might help you.
Basically (as one example), inserting a row (or multiple ones) requires one call: all connect, pointer, command prep, execute, drop pointer, close connection stuff is handled by the Rx_SQL_Insert Rexx function I wrote. Same goes for most of the other command sets generally used (update, delete, create, general command passing, etc).
Hello,
please do post it... the sooner the better.
Had to rewrite the rexx code myself compared to the one posted here as I found various aspects that would cause problems. ) ' & , etc. in filenames cause problems, also added possibility to output data to file out of a SELECT-string and thus create a new database.
Accidentally (quick) formatted the wrong drive (out of two identical ones) and Jan van Wijk has helped me to recover the files (See DFSee v11.0 (http://www.dfsee.com/index.php)) and it can use the stored info .LONGNAME EA (Extended Attributes) that some of my other scripts set. The problem is/was that those doesn't contain path information. So I have to find out where those file was placed before with info stored in PostgreSQL databases on the same drive. I've written scripts that extract data and now try to merge the info into .rxdb files.
Quote from: RobertM on 2011.12.30, 00:18:40
I have some Rexx code written for RxSQL that simplifies a bunch of the calls... I can put it together into a zipfile or something if you think it might help you.
Basically (as one example), inserting a row (or multiple ones) requires one call: all connect, pointer, command prep, execute, drop pointer, close connection stuff is handled by the Rx_SQL_Insert Rexx function I wrote. Same goes for most of the other command sets generally used (update, delete, create, general command passing, etc).
//Jan-Erik
Hello,
here's my version 0.0.2, a total rewrite from the previous version.
It can listen to input from rexx queue and output it to another rexx queue, screen and/or a file.
It can be used in interactive mode (like dbisql)
It can be used for single queries
Modify behaviour depending on environment variable settings:
SET PROGRESS=1 To show progress bars during processing
SET VERBOSE=1 To show some more info
SET QUIET=1 To show less info
SET AUTOCOMMIT=1 To save changes right away (only useful in Interactive mode, see next)
SET INTERACTIVE=1 To enter more queries after the first (use QUIT to exit)
SET IGNORECASESORT=0 To not ignore case during sort operation
Preferred way to start interactive mode:
rxdb PRELOAD MyTable
or
rxdb SELECT * FROM MyTable
Note:
It can only handle 1 table at the time in each sql statement, but switch between them.
/*
* Filename: rxdb.cmd
* Author: JAN-ERIK LÄRKA
* Created: Sat Dec 24 2011
* Purpose: A database engine entirely written in REXX
* Changes:
* 0.0.2 Tue Dec 27 2011 to March 18 2012, Total rewrite
*/
/* Don't echo commands */
'@ECHO OFF'
/* Add procedures to handle problems */
SIGNAL ON ERROR NAME SignalError
SIGNAL ON FAILURE NAME SignalFailure
SIGNAL ON HALT NAME SignalHalt
SIGNAL ON NOVALUE NAME SignalNoValue
SIGNAL ON SYNTAX NAME SignalSyntax
SIGNAL ON NOTREADY NAME SignalNotReady
/* Load RexxUtil Library */
IF RxFuncQuery('SysLoadFuncs') THEN
DO
CALL RxFuncAdd 'SysLoadFuncs', 'RexxUtil', 'SysLoadFuncs'
CALL SysLoadFuncs
END
/* An array with supported arguments */
!_sqltype_!.1 = "'SELECT '!_columns_!' FROM '!_table_!' '!_rest_!"
!_sqltype_!.2 = "'INSERT INTO '!_table_!' ('!_columns_!') VALUES ('!_rest_!"
!_sqltype_!.3 = "'UPDATE '!_table_!' SET '!_rest_!"
!_sqltype_!.4 = "'DELETE FROM '!_table_!' '!_rest_!"
!_sqltype_!.5 = "'CREATE TABLE '!_table_!' COLUMNS ('!_columns_!')'!_rest_!"
!_sqltype_!.6 = "'VACUUM TABLE '!_table_!' '!_rest_!"
!_sqltype_!.7 = "'COMMIT'!_rest_!"
!_sqltype_!.8 = "'PRELOAD '!_table_!' INQ '!_q_in_!' OUTQ '!_q_out_!' MSGQ '!_q_msg_!' '!_rest_!"
!_sqltype_!.9 = "'QUIT'"
!_sqltype_!.10 = ''
/* Create arrays for the optional additional arguments */
!_sqlvar_!.2 = '!_conditions_!'
!_sqlvar_!.3 = '!_group_!'
!_sqlvar_!.4 = '!_order_!'
!_sqlvar_!.5 = ''
!_sqlvar_!.6 = '!_tofile_!'
!_sqlclause_!.2 = 'WHERE'
!_sqlclause_!.3 = 'GROUP BY'
!_sqlclause_!.4 = 'ORDER BY'
!_sqlclause_!.5 = 'DESC'
!_sqlclause_!.6 = '>'
!_sqlclause_!.0 = 6
!_sqlselect_!.1 = '"UNIQUE "!_columns_!'
!_sqlselect_!.2 = '"TOP "!_TopRows_!" "!_columns_!'
!_sqlselect_!.3 = '"NOT UNIQUE "!_columns_!'
!_sqlselect_!.0 = 3
/* Useful functions + (columns) not yet implemented */
!_sqlfunc_!.1 = 'COUNT('
!_sqlfunc_!.2 = 'SUM('
!_sqlfunc_!.3 = 'MIN('
!_sqlfunc_!.4 = 'MAX('
!_sqlfunc_!.0 = 0
!_sqlstr_!.0 = 0
!_sqlstr_!.0.!_fpos_! = 0
!_sqlstr_!.0 = 0
!_sqlgrp_!.0 = 0
!_sqltype_!.0 = 6
!_rest_! = ''
!_table_! = ''
!_tmp_table_! = ''
!_values_! = ''
!_columns_! = ''
!_colnames_! = ''
!_cols_! = 0
!_commited_! = 0
!_isUnique_! = 0
!_TopRows_! = 0
!_isQueueMode_! = 0
!_isToFileReadOnly_! = 0
!_sqleng_!.!_title_! = '[REXX SQL Engine] '
!_sqleng_!.!_multi_queues_! = 0
!_sqleng_!.!_sqlcmd_! = ARG(1)
!_q_in_! = 0
!_q_out_! = 0
!_q_msg_! = 0
DO FOREVER
/* Modify behaviour depending on environment variable settings */
!_sqleng_!.!_showprogress_! = ( VALUE( 'PROGRESS',,'OS2ENVIRONMENT' ) = 1 )
!_sqleng_!.!_verbose_! = ( VALUE( 'VERBOSE',,'OS2ENVIRONMENT' ) = 1 )
!_sqleng_!.!_quiet_! = ( VALUE( 'QUIET',,'OS2ENVIRONMENT' ) = 1 ) & \!_sqleng_!.!_verbose_!
!_sqleng_!.!_autocommit_! = ( VALUE( 'AUTOCOMMIT',,'OS2ENVIRONMENT' ) = 1 )
!_isInteractiveMode_! = ( VALUE( 'INTERACTIVE',,'OS2ENVIRONMENT' ) = 1 )
IF VALUE( 'IGNORECASESORT',,'OS2ENVIRONMENT' ) <> 0 THEN
!_sqleng_!.!_sortcase_! = 'I'
ELSE
!_sqleng_!.!_sortcase_! = 'C'
/* Place argument in variable */
IF !_isInteractiveMode_! THEN
!_sqltype_!.0 = 9
ELSE IF WORD( !_sqleng_!.!_sqlcmd_!, 1 ) = 'PRELOAD' THEN
DO
!_isQueueMode_! = 1
!_isInteractiveMode_! = 0
!_sqltype_!.0 = 9
END
/* Check to see what argument has been entered */
DO !_sqlcase_! = 1 TO !_sqltype_!.0
IF WORD( !_sqleng_!.!_sqlcmd_!, 1 ) = STRIP( WORD( !_sqltype_!.!_sqlcase_!, 1 ),, "'" ) THEN
DO
INTERPRET 'PARSE VALUE !_sqleng_!.!_sqlcmd_! WITH '||!_sqltype_!.!_sqlcase_!
/* Fix to handle the case where data may contain ) that would cause problems */
IF !_sqlcase_! = 2 THEN
DO
!_values_! = WRDS( !_rest_!, '","', COUNTSTR( ',', !_columns_! ), ')' )
!_rest_! = SUBSTR( !_rest_!, LENGTH( !_values_! ) + 2 )
END
LEAVE !_sqlcase_!
END
END
/* Set the queue name */
IF !_isQueueMode_! THEN
DO
IF WORD( !_sqleng_!.!_sqlcmd_!, 1 ) = 'PRELOAD' THEN
DO
PARSE UPPER VALUE !_table_! WITH !_table_!'.RXDB'
IF LENGTH( !_q_in_! ) > 0 THEN
DO
!_sqleng_!.!_q_in_name_! = STRIP( TRANSLATE( !_q_in_! ) )
!_q_in_! = 1
END
ELSE
DO
!_sqleng_!.!_q_in_name_! = 'STDIN'
!_q_in_! = 0
END
IF LENGTH( !_q_out_! ) > 0 THEN
DO
!_sqleng_!.!_q_out_name_! = STRIP( TRANSLATE( !_q_out_! ) )
PARSE VALUE !_sqleng_!.!_q_out_name_! WITH '{'!_qs_!'}'
!_sqleng_!.!_multi_queues_! = ( LENGTH( !_qs_! ) > 0 )
!_q_out_! = 1
END
ELSE
DO
!_sqleng_!.!_q_out_name_! = 'STDOUT'
!_q_out_! = 0
END
IF LENGTH( !_q_msg_! ) > 0 THEN DO
!_sqleng_!.!_q_msg_name_! = STRIP( TRANSLATE( !_q_msg_! ) )
!_q_msg_! = 1
END
ELSE
DO
!_sqleng_!.!_q_msg_name_! = 'STDERR'
!_q_msg_! = 0
END
IF !_q_in_! THEN
CALL rxOutput 'Input: "'||!_sqleng_!.!_q_in_name_!||'"', 0
IF !_q_out_! THEN
CALL rxOutput 'Output: "'||!_sqleng_!.!_q_out_name_!||'"', 0
IF !_q_msg_! THEN
CALL rxOutput 'Message: "'||!_sqleng_!.!_q_msg_name_!||'"', 0
CALL rxOutput 'Starting up, preloading table "'||TRANSLATE( !_table_! )||'"'
END
ELSE
CALL rxOutput !_sqleng_!.!_sqlcmd_!
IF \!_sqleng_!.!_multi_queues_! THEN
CALL RXQUEUE 'Set', !_sqleng_!.!_q_out_name_!
END
ELSE
DO
!_q_msg_! = 0
IF !_isInteractiveMode_! & WORD( !_sqleng_!.!_sqlcmd_!, 1 ) = 'PRELOAD' THEN CALL rxOutput 'Preloading table "'||TRANSLATE( !_table_! )||'"'
ELSE IF !_isInteractiveMode_! & TRANSLATE( !_sqleng_!.!_sqlcmd_! ) = 'QUIT' THEN RETURN 0
IF !_sqleng_!.!_showprogress_! & \!_isInteractiveMode_! THEN CALL rxOutput 'Processing data. Please wait...'
!_sqleng_!.!_q_in_name_! = 'STDIN'
!_sqleng_!.!_q_out_name_! = 'STDOUT'
!_sqleng_!.!_q_msg_name_! = 'STDERR'
END
/* Show usage if argument didn't match those supported */
IF \!_isQueueMode_! & !_sqlcase_! > !_sqltype_!.0 THEN CALL Usage
!_sort_! = 'A'
/* Check the optional arguments */
DO j = !_sqlclause_!.0 TO 2 BY -1
IF j > 1 & j <> 5 THEN INTERPRET !_sqlvar_!.j||' = ""'
IF POS( !_sqlclause_!.j, !_rest_! ) > 0 THEN
DO
/* Check if the last word is DESC or D and modify sort order from the default Ascending */
IF j = 5 THEN
DO
IF SUBWRDS( !_rest_!, ',', -1 ) = 'DESC' THEN
!_sort_! = 'D'
ELSE
ITERATE
END
INTERPRET 'PARSE VALUE !_rest_! WITH !_rest_! "'||!_sqlclause_!.j||'" '||!_sqlvar_!.j
IF !_sqlvar_!.j <> '.' THEN
INTERPRET !_sqlvar_!.j||' = STRIP( VALUE( "'||!_sqlvar_!.j||'" ) )'
END
END
IF !_sqlcase_! = 3 THEN !_set_values_! = STRIP( !_rest_! )
/* Output to file but not screen?! */
!_isToScreen_! = ( !_tofile_! = '' )
!_isToFile_! = ( !_tofile_! <> 'NUL' & \!_isToScreen_! )
IF !_isToFile_! THEN
DO
SIGNAL OFF NOTREADY
IF STREAM( !_tofile_!, 'C', 'QUERY EXISTS' ) <> '' THEN
!_isToFileReadOnly_! = ( STREAM( !_tofile_!, 'C', 'OPEN' ) <> 'READY:' )
ELSE !_isToFileReadOnly_! = -1
SIGNAL ON NOTREADY
IF !_isToFileReadOnly_! = 1 THEN !_isToFile_! = 0
END
/* Combine groups and order as both specify how to order data */
IF ( LENGTH( STRIP( !_order_! ) ) > 0 ) THEN
DO
!_order_! = !_order_!||','
IF ( LENGTH( STRIP( !_group_! ) ) > 0 ) THEN
!_order_! = ','||!_order_!
END
ELSE IF ( LENGTH( STRIP( !_group_! ) ) > 0 ) THEN
!_group_! = !_group_!||','
!_group_! = STRIP( CHANGESTR( ',', !_group_!||!_order_!, '","' ), 'T', '"' )
/* Check if we've changed table since last run */
PARSE UPPER VALUE !_table_! WITH !_chk_table_!'.RXDB'
IF LENGTH( !_tmp_table_! ) > 0 & !_chk_table_! <> !_tmp_table_! THEN
DO
DROP !_sqlstr_!.
DROP !_colnames_!
!_sqlstr_!.0 = 0
!_colnames_! = ''
CALL rxOutput 'Switching over to table "'||!_chk_table_!||'"'
END
/* Set file name extension of table */
PARSE UPPER VALUE !_table_! WITH !_tmp_table_!'.RXDB'
!_table_! = !_tmp_table_!||'.RXDB'
/* Check if the table has been read already */
IF !_colnames_! = '' | !_sqlstr_!.0 = 0 THEN
/* Check if the table has been created */
IF STREAM( !_table_!, 'C', 'QUERY EXISTS' ) <> '' THEN
DO
!_exist_! = 1
!_isUnique_! = 0
!_TopRows_! = 0
SIGNAL OFF NOTREADY
!_readonly_! = ( STREAM( !_table_!, 'C', 'OPEN' ) <> 'READY:' )
IF !_sqlstr_!.0 = 0 THEN
DO
IF !_readonly_! THEN
DO
CALL rxOutput 'Table "'||!_chk_table_!||'" is locked. Please try again later.'
CALL SysSleep 10
RETURN 13
END
!_colnames_! = LINEIN( !_table_! )
!_sqlstr_!.0.!_fpos_! = LENGTH( !_colnames_! ) + 2
!_cols_! = COUNTSTR( ',', !_colnames_! ) + 1
END
SIGNAL ON NOTREADY
END
ELSE !_exist_! = 0
IF POS( 'columns', !_sqltype_!.!_sqlcase_! ) = 0 THEN !_columns_! = !_colnames_!
IF WORDS( !_columns_! ) > 0 THEN
DO j = 1 TO !_sqlselect_!.0
IF WORD( !_columns_!, 1 ) = STRIP( WORD( !_sqlselect_!.j, 1 ),, '"' ) THEN
DO
INTERPRET "PARSE VALUE !_columns_! WITH "||!_sqlselect_!.j
IF j = 1 THEN
!_isUnique_! = 1
ELSE IF j = 3 THEN
!_isUnique_! = -1
END
END
IF WORDS( !_columns_! ) > 0 THEN
DO j = 1 TO !_sqlfunc_!.0
IF POS( !_sqlfunc_!.j, TRANSLATE( !_columns_! ) ) > 0 THEN
DO
INTERPRET "PARSE VALUE !_columns_! WITH !_pre_!'"||!_sqlfunc_!.j||"'!_mid_!')'!_post_!"
!_columns_! = !_pre_!||STRIP( !_sqlfunc_!.j, 'T', '(' )||!_post_!
END
END
!_columns_! = STRIP( CHANGESTR( '"', !_columns_!, '' ) )
!_parse_columns_! = CHANGESTR( "'", CHANGESTR( '"', !_colnames_!, '' ), '' )
/* Set up the arguments with the right style */
!_columns_! = '"'||STRIP( CHANGESTR( '*', CHANGESTR( 'COUNT(*)', CHANGESTR( ',', !_columns_!, '","' ), '!_COUNT_!' ), STRIP( !_colnames_!,, '"' ) ),, '"' )||'"'
!_select_direct_! = ( LENGTH( !_group_! ) = 0 )
!_static_conditions_!.0 = COUNTSTR( "'", !_conditions_! ) % 2
/* Modify the condition(s) */
DO j = 1 TO !_static_conditions_!.0
PARSE VALUE !_conditions_! WITH !_pre_!"'"!_static_conditon_!"'"!_post_!
!_static_conditons_!.j = !_static_conditon_!
!_conditions_! = !_pre_!||"¤"||!_post_!
END
/* Modify condition(s) to look at the right "column" */
DO j = 1 TO !_cols_!
PARSE VALUE !_parse_columns_! WITH !_colname_!','!_parse_columns_!
!_conditions_! = CHANGESTR( !_colname_!, !_conditions_!, 'STRIP( SUBWRD( !_input_!, '||j||', '||"'"||'","'||"'"||' ),, '||"'"||'"'||"' )" )
END
/* Replace AND with & within condition(s) */
!_conditions_! = CHANGESTR( ' OR ', CHANGESTR( ' AND ', !_conditions_!, ' & ' ), ' | ' )
/* Reinsert static data into !_conditions_! */
DO j = 1 TO !_static_conditions_!.0
PARSE VALUE !_conditions_! WITH !_pre_!"¤"!_post_!
!_conditions_! = !_pre_!||"'"||!_static_conditons_!.j||"'"||!_post_!
END
/* Modify the != command line argument to <> (One doesn't work on the command line while the other can't be interpreted) :-/ */
!_conditions_! = CHANGESTR( '!=', !_conditions_!, '<>' )
!_counted_! = 0
!_counter_! = 0
!_count_! = 0
!_sqlgrp_!.0 = 0
/* Go through each line in the database */
IF !_exist_! & ( ( !_sqlstr_!.0 = 0 & ( !_isQueueMode_! | !_isInteractiveMode_! ) ) | ( ( !_sqlcase_! = 1 | !_sqlcase_! = 3 | !_sqlcase_! = 4 | !_sqlcase_! = 6 ) & ( \!_isQueueMode_! | !_isInteractiveMode_! ) ) ) THEN
DO
IF !_sqlcase_! = 1 & !_select_direct_! & \!_isQueueMode_! THEN
DO
!_temp_! = ''
!_chk_unique_pre_! = '!_temp_! = '||STRIP( !_columns_!,, '"' )
!_chk_unique_mid_! = ''
!_chk_unique_post_! = ''
IF !_isUnique_! = 1 THEN
!_chk_unique_pre_! = 'IF '||!_chk_unique_pre_!||' THEN ITERATE;'||!_chk_unique_pre_!
ELSE IF !_isUnique_! = -1 THEN
DO
!_chk_unique_post_! = ';END;!_prev_! = !_temp_!;'||!_chk_unique_pre_!
!_chk_unique_pre_! = 'IF '||!_chk_unique_pre_!||' | !_prev_! = !_temp_! THEN DO;'
END
IF !_isToScreen_! | !_isToFile_! THEN !_chk_unique_mid_! = ';CALL LINEOUT '||!_tofile_!||', !_temp_!'
IF !_sqleng_!.!_showprogress_! THEN
DO
!_table_!.!_f_size_! = STREAM( !_table_!, 'C', 'QUERY SIZE' )
!_table_!.!_r_size_! = 0
END
DO WHILE LINES( !_table_! ) > 0
!_count_! = !_count_! + 1
!_input_! = LINEIN( !_table_! )
IF !_sqleng_!.!_showprogress_! & \!_isInteractiveMode_! THEN
DO
!_table_!.!_r_size_! = !_table_!.!_r_size_! + LENGTH( !_input_! ) + 2
CALL rxStatusBar !_table_!.!_r_size_! / !_table_!.!_f_size_!, !_counter_! / !_count_!
END
IF LEFT( !_input_!, 1 ) = '*' | LENGTH( !_input_! ) = 0 THEN ITERATE
IF LENGTH( !_conditions_! ) > 0 THEN
INTERPRET '!_result_! = ('||!_conditions_!||')'
ELSE !_result_! = 1
IF !_result_! THEN
DO
INTERPRET 'PARSE VALUE !_input_! WITH '||STRIP( !_colnames_!,, '"' )||';'||!_chk_unique_pre_!||';'||!_chk_unique_mid_!||';'||!_chk_unique_post_!
!_counter_! = !_counter_! + 1
END
IF !_TopRows_! > 0 & !_counter_! >= !_TopRows_! THEN LEAVE
END
IF !_isUnique_! = -1 THEN
INTERPRET !_chk_unique_pre_!||';'||!_chk_unique_mid_!||';END'
!_count_! = !_count_! - 1
CALL STREAM !_table_!, 'C', 'CLOSE'
!_sqlstr_!.0 = !_counter_!
END
ELSE IF !_sqlstr_!.0 = 0 & !_exist_! THEN
DO
IF !_sqleng_!.!_showprogress_! & !_tmp_table_! <> '' THEN
DO
!_table_!.!_f_size_! = STREAM( !_table_!, 'C', 'QUERY SIZE' )
!_table_!.!_r_size_! = 0
END
IF !_tmp_table_! <> '' THEN
DO WHILE LINES( !_table_! ) > 0
!_count_! = !_count_! + 1
!_input_! = LINEIN( !_table_! )
IF !_sqleng_!.!_showprogress_! & \( \!_isInteractiveMode_! & !_isToScreen_! ) THEN
DO
!_table_!.!_r_size_! = !_table_!.!_r_size_! + LENGTH( !_input_! ) + 2
CALL rxStatusBar !_table_!.!_r_size_! / !_table_!.!_f_size_!, !_counter_! / !_count_!
END
IF LEFT( !_input_!, 1 ) <> '*' & LENGTH( !_input_! ) > 0 THEN
DO
!_counter_! = !_counter_! + 1
!_sqlstr_!.!_counter_! = !_input_!
!_sqlstr_!.!_counter_!.!_fpos_! = !_sqlstr_!.0.!_fpos_!
END
!_sqlstr_!.0.!_fpos_! = !_sqlstr_!.0.!_fpos_! + LENGTH( !_input_! ) + 2
END
IF !_tmp_table_! <> '' THEN
DO
CALL STREAM !_table_!, 'C', 'CLOSE'
!_sqlstr_!.0 = !_counter_!
CALL rxOutput 'Table "'||!_tmp_table_!||'" has been loaded. ( '||!_counter_!||' out of '||!_count_!||' records )'
END
END
IF !_select_direct_! & !_sqlcase_! = 1 & \!_isQueueMode_! THEN
DO
IF !_isToFile_! & !_isToFileReadOnly_! = 1 & !_counter_! > 0 THEN
CALL STREAM !_tofile_!, 'C', 'CLOSE'
IF !_isInteractiveMode_! & !_select_direct_! THEN
!_sqlcase_! = 0
ELSE
RETURN 0
END
END
IF ( LENGTH( !_conditions_! ) > 0 | LENGTH( !_group_! ) > 0 | !_isUnique_! <> 0 ) & ( !_sqlcase_! = 1 | !_sqlcase_! = 3 | !_sqlcase_! = 4 ) THEN
DO
!_conditions_! = CHANGESTR( '!_input_!', !_conditions_!, '!_sqlstr_!.!_counter_!' )
!_parse_columns_! = STRIP( !_colnames_!,, '"' )
!_groups_columns_! = STRIP( !_group_!||!_columns_!,, '"' )
!_temp_! = ''
!_chk_unique_pre_! = ''
!_chk_unique_post_! = ''
IF !_isUnique_! = 1 THEN
!_chk_unique_pre_! = ';IF !_temp_! = !_sqlstr_!.!_counter_! THEN ITERATE !_counter_!;'
ELSE IF !_isUnique_! = -1 THEN
DO
!_chk_unique_pre_! = ';IF !_temp_! = !_sqlstr_!.!_counter_! | !_prev_! = !_temp_! THEN DO;'
!_chk_unique_post_! = ';END;!_prev_! = !_temp_!;!_temp_! = !_sqlstr_!.!_counter_!;'
END
!_count_! = 0
SIGNAL OFF NOVALUE
CALL trace '?i'
DO !_counter_! = 1 TO !_sqlstr_!.0
IF !_sqleng_!.!_showprogress_! THEN CALL rxStatusBar !_count_! / !_sqlstr_!.0, !_counter_! / !_sqlstr_!.0, !_count_! / !_counter_!
/* Does the condition match?! */
IF LEFT( !_sqlstr_!.!_counter_!, 1 ) = '*' | LENGTH( !_sqlstr_!.!_counter_! ) = 0 THEN ITERATE
IF LENGTH( !_conditions_! ) > 0 THEN
INTERPRET '!_result_! = ('||!_conditions_!||')'
ELSE !_result_! = 1
IF !_result_! THEN
INTERPRET !_chk_unique_pre_!||'!_count_! = !_count_! + 1;PARSE VALUE !_sqlstr_!.!_counter_! WITH '||!_parse_columns_!||';!_sqlgrp_!.!_count_! = '||!_groups_columns_!||'||"¤"||!_counter_!||"¤"||!_tofile_!||"¤"'||!_chk_unique_post_!
IF LENGTH( !_conditions_! ) > 0 & !_TopRows_! > 0 & !_count_! >= !_TopRows_! THEN LEAVE !_counter_!
END
IF !_isUnique_! = -1 THEN
INTERPRET !_chk_unique_pre_!||';!_count_! = !_count_! + 1;!_sqlgrp_!.count = '||!_groups_columns_!||'||"¤"||!_counter_!||"¤"||!_tofile_!||"¤";END'
SIGNAL ON NOVALUE
!_sqlgrp_!.0 = !_count_!
CALL rxOutput 'Conditions applied to "'||!_tmp_table_!||'". ( '||!_sqlgrp_!.0||' out of '||!_sqlstr_!.0||' records )'
END
ELSE IF \!_select_direct_! & !_sqlcase_! <> 6 & !_sqlgrp_!.0 = 0 & !_sqlstr_!.0 > 0 THEN
DO
IF !_sqleng_!.!_showprogress_! THEN CALL rxStatusBar 0.1
IF !_sqlstr_!.0 > 10000 THEN
CALL rxStemCopy
ELSE IF !_TopRows_! > 0 THEN
CALL SysStemCopy '!_sqlstr_!.', '!_sqlgrp_!.', 1, 1, !_TopRows_!
ELSE
CALL SysStemCopy '!_sqlstr_!.', '!_sqlgrp_!.'
IF !_sqleng_!.!_showprogress_! THEN CALL rxStatusBar 0.9
END
!_grps_! = COUNTSTR( ',', !_group_! )
/* Data has to be sorted */
IF LENGTH( !_group_! ) > 0 THEN
DO
IF !_sqlgrp_!.0 > 0 THEN
DO
IF \rxSorted( '!_sqlgrp_!.' ) THEN
CALL rxOutput !_sqlgrp_!.0||' of '||!_sqlstr_!.0||' records in table "'||!_tmp_table_!||'" sorted.'
END
ELSE
DO
IF \rxSorted( '!_sqlstr_!.' ) THEN
CALL rxOutput !_sqlstr_!.0||' records in table "'||!_tmp_table_!||'" sorted.'
END
IF !_sqleng_!.!_showprogress_! THEN CALL rxStatusBar 1
END
April 6, 2012
The problem can be found in this section further down, right below /* INSERT INTO ... */
Changed:
!_columns_!
To:
STRIP( !_columns_!,, '"* )
INTERPRET 'PARSE VALUE !_temp_! WITH '||STRIP( !_columns_!,, '"' )||';!_temp_! = '||STRIP( !_colnames_!,, '"' )
IF STREAM( !_table_!, 'C', 'QUERY EXISTS' ) <> '' THEN
DO
IF !_sqlcase_! = 1 THEN /* SELECT ... */
DO
!_count_! = 0
!_temp_! = ''
IF !_isToFile_! THEN
CALL STREAM !_tofile_!, 'C', 'OPEN'
DO !_counter_! = 1 TO !_sqlgrp_!.0
PARSE VALUE SUBWRDS( !_sqlgrp_!.!_counter_!, ',"', !_grps_! ) WITH !_temp_!'¤'.'¤'tmp_file'¤'
IF LEFT( !_sqlgrp_!.!_counter_!, 1 ) = '*' | LENGTH( !_sqlgrp_!.!_counter_! ) = 0 THEN ITERATE
IF !_isQueueMode_! & !_sqleng_!.!_q_out_name_! <> '' THEN CALL LINEOUT 'QUEUE:', !_temp_!
ELSE IF !_isInteractiveMode_! | ( \!_isQueueMode_! & \!_isInteractiveMode_! ) THEN CALL LINEOUT 'STDOUT', !_temp_!
ELSE IF !_sqleng_!.!_showprogress_! & \!_isInteractiveMode_! THEN CALL rxStatusBar !_counter_! / !_sqlgrp_!.0, !_counter_! / !_sqlstr_!.0
IF !_isToFile_! THEN DO
CALL STREAM tmp_file, 'C', 'SEEK <0'
CALL LINEOUT tmp_file, !_temp_!
END
!_count_! = !_count_! + 1
IF !_TopRows_! > 0 & !_count_! >= !_TopRows_! THEN
DO
!_counter_! = !_counter_! + 1
LEAVE !_counter_!
END
END
CALL rxOutput 'Selection performed. ( '||!_counter_! - 1||' out of '||!_sqlgrp_!.0||' records )'
IF \!_isToScreen_! & !_isToFileReadOnly_! = 1 & !_isToFile_! & !_count_! > 0 THEN
CALL STREAM !_tofile_!, 'C', 'CLOSE'
END
ELSE IF !_sqlcase_! = 2 THEN /* INSERT INTO... */
DO
/* Empty all variables (column names) to recieve data */
INTERPRET 'PARSE VALUE "" WITH '||STRIP( !_colnames_!,, '"' )
/* Special handling for ' and , that are crutial parts of the database file format */
!_temp_! = CHANGESTR( D2C(1), CHANGESTR( ",", CHANGESTR( "'", STRIP( CHANGESTR( "','", !_values_!, D2C(1) ),, "'" ), D2C(2) ), D2C(3) ), ',' )
INTERPRET 'PARSE VALUE !_temp_! WITH '||STRIP( !_columns_!,, '"' )||';!_temp_! = '||STRIP( !_colnames_!,, '"' )
/* Write the added data to the database table file */
!_counter_! = !_sqlstr_!.0 + 1
!_sqlstr_!.!_counter_! = '"'||CHANGESTR( D2C(3), CHANGESTR( ',', CHANGESTR( D2C(2), !_temp_!, "'" ), '","' ), ',' )||'"'
!_sqlstr_!.0 = !_counter_!
IF !_sqleng_!.!_autocommit_! | \!_isQueueMode_! THEN
DO
IF !_readonly_! THEN
CALL rxOutput 'Table "'||!_tmp_table_!||'" is locked, output has been postponed.'
ELSE DO
CALL STREAM !_table_!, 'C', 'SEEK <0'
CALL LINEOUT !_table_!, !_sqlstr_!.!_counter_!
CALL STREAM !_table_!, 'C', 'CLOSE'
!_commited_! = !_counter_!
CALL rxOutput 'New record has been written to table "'||!_tmp_table_!||'".'
END
END
ELSE
CALL rxOutput 'New record added to table "'||!_tmp_table_!||'".'
END
ELSE IF !_sqlcase_! = 3 | !_sqlcase_! = 4 THEN /* UPDATE ... and DELETE FROM ... */
DO
CALL STREAM !_table_!, 'C', 'OPEN'
IF !_sqlcase_! = 3 THEN /* UPDATE ... */
DO
!_parse_columns_! = !_set_values_!
!_cols_! = COUNTSTR( ',', !_parse_columns_! )
/* Replace with the input that point to a column name */
DO k = 1 TO !_cols_!
PARSE VALUE !_parse_columns_! WITH !_col_!'='!_val_!','!_parse_columns_!
!_columns_! = CHANGESTR( !_col_!'='!_val_!, !_set_values_!, '!_sqlgrp_!.count = CHANGEWRD( !_temp_!, '||k||', ",", '||"'"||'"'||"'"||STRIP( !_val_! )||"'"||'"'||"'"||' )' )
END
END
!_counter_! = !_sqlgrp_!.0
!_colnames_! = "'"||'"'||"'"||CHANGESTR( D2C(1), CHANGESTR( ",", CHANGESTR( "'", CHANGESTR( '","', STRIP( !_colnames_!,, '"' ), D2C(1) ), D2C(2) ), D2C(3) ), "'"||'","'||"'" )||"'"||'"'||"'"
/* Go through the database table */
IF !_sqlgrp_!.0 > 0 THEN
DO !_count_! = 1 TO !_sqlgrp_!.0
PARSE VALUE !_sqlgrp_!.!_count_! WITH !_temp_!'¤'!_counted_!'¤'!_tmp_file_!'¤'
IF !_sqleng_!.!_showprogress_! THEN CALL rxStatusBar !_count_! / !_sqlgrp_!.0, !_counted_! / !_sqlstr_!.0
!_sqlstr_!.!_counted_! = '*'||SUBSTR( !_temp_!, 2 )
/* Add the modified version last for UPDATE ... */
IF !_sqlcase_! = 3 THEN /* UPDATE ... */
DO
!_counter_! = !_counter_! + 1
INTERPRET "PARSE VALUE !_temp_! WITH "||!_colnames_!||";"||!_set_values_!||";!_temp_! = "||!_colnames_!
IF !_sqleng_!.!_autocommit_! | \!_isQueueMode_! THEN
DO
IF !_readonly_! THEN
DO
CALL rxOutput 'Table "'||!_tmp_table_!||'" is locked, output has been postponed.'
LEAVE !_count_!
END
ELSE
DO
CALL STREAM !_table_!, 'C', 'SEEK <0'
CALL LINEOUT !_table_!, !_temp_!
END
END
END
IF !_sqleng_!.!_autocommit_! | \!_isQueueMode_! THEN
DO
IF !_readonly_! THEN
DO
CALL rxOutput 'Table "'||!_tmp_table_!||'" is locked, output has been postponed.'
LEAVE !_count_!
END
ELSE
DO
CALL STREAM !_table_!, 'C', 'SEEK ='||!_sqlstr_!.!_counted_!.!_fpos_! + 1
CALL CHAROUT !_table_!, '*'
END
END
IF !_sqlcase_! = 3 THEN
DO
!_counted_! = !_sqlstr_!.0 + 1
!_sqlstr_!.!_counted_! = !_temp_!
!_sqlstr_!.0 = !_counted_!
END
END
IF !_sqleng_!.!_autocommit_! | \!_isQueueMode_! THEN
DO
IF !_sqlcase_! = 3 THEN
DO
CALL rxOutput ( !_counter_! - !_sqlgrp_!.0 )||' record(s) updated in table "'||!_tmp_table_!||'".'
!_commited_! = !_counter_! - !_sqlgrp_!.0
END
ELSE
DO
CALL rxOutput !_sqlgrp_!.0||' record(s) deleted from table "'||!_tmp_table_!||'" on disk.'
!_commited_! = !_sqlstr_!.0
END
END
ELSE IF !_sqlcase_! = 3 THEN
CALL rxOutput ( !_counter_! - !_sqlgrp_!.0 )||' records updated in table "'||!_tmp_table_!||'"'
ELSE
CALL rxOutput !_sqlgrp_!.0||' records deleted from table "'||!_tmp_table_!||'"'
END
ELSE IF !_sqlcase_! = 6 THEN /* VACUUM TABLE ... */
DO
IF !_readonly_! THEN
CALL rxOutput 'Table "'||!_tmp_table_!||'" is locked, output has been postponed.'
ELSE
DO
/* Delete the table file */
CALL SysFileDelete !_table_!
/* Write the table names to database table file */
CALL LINEOUT !_table_!, !_colnames_!
/* Write each non-deleted row to the new database table file */
!_counter_! = 0
!_sqlstr_!.0.!_fpos_! = 0
DO !_count_! = 1 TO !_sqlstr_!.0
IF LEFT( !_sqlstr_!.!_count_!, 1 ) = '*' | LENGTH( !_sqlstr_!.!_count_! ) = 0 THEN ITERATE
!_counter_! = !_counter_! + 1
IF !_sqleng_!.!_showprogress_! THEN CALL rxStatusBar !_count_! / !_sqlstr_!.0, !_counter_! / !_sqlstr_!.0, !_counter_! / !_count_!
!_sqlstr_!.!_counter_! = !_sqlstr_!.!_count_!
!_sqlstr_!.!_counter_!.!_fpos_! = !_sqlstr_!.0.!_fpos_!
!_sqlstr_!.0.!_fpos_! = !_sqlstr_!.0.!_fpos_! + LENGTH( !_sqlstr_!.!_count_! ) + 2
CALL LINEOUT !_table_!, !_sqlstr_!.!_counter_!
END
!_sqlstr_!.0 = !_counter_!
!_commited_! = !_sqlstr_!.0
CALL STREAM !_table_!, 'C', 'CLOSE'
CALL rxOutput !_sqlstr_!.0||' record(s) ( '||!_sqlstr_!.0.!_fpos_!||' bytes written to table "'||!_tmp_table_!||'" on disk.'
END
END
ELSE IF !_sqlcase_! = 7 THEN /* COMMIT */
DO
IF !_readonly_! THEN
CALL rxOutput 'Table "'||!_tmp_table_!||'" is locked, output has been postponed.'
ELSE
DO
!_counter_! = !_commited_! + 1
IF !_counter_! < !_sqlstr_!.0 THEN
DO
CALL STREAM !_table_!, 'C', 'SEEK <0'
/* Write each non-deleted row to the new database table file */
DO !_count_! = !_counter_! TO !_sqlstr_!.0
IF !_sqleng_!.!_showprogress_! THEN CALL rxStatusBar !_count_! / !_sqlstr_!.0
IF LEFT( !_sqlstr_!.!_count_!, 1 ) = '*' THEN
DO
PARSE VALUE !_sqlstr_!.!_count_! WITH .'¤'!_counted_!
CALL STREAM !_table_!, 'C', 'SEEK ='||!_sqlstr_!.!_counted_!.!_fpos_!
CALL CHAROUT !_table_!, '*'
ITERATE
END
CALL LINEOUT !_table_!, !_sqlstr_!.!_count_!
!_counter_! = !_counter_! + 1
END
IF !_count_! <> !_counter_! THEN
DO
CALL STREAM !_table_!, 'C', 'CLOSE'
CALL rxOutput ( !_counter_! - !_commited_! )||' records written to table "'||!_tmp_table_!||'" on disk.'
END
!_commited_! = !_sqlstr_!.0
END
END
END
ELSE IF !_sqlcase_! = 9 THEN /* Shut down */
DO
CALL rxOutput 'Shutting down...'
RETURN 0
END
IF !_isQueueMode_! THEN
DO
IF RXQUEUE( 'Query', !_sqleng_!.!_q_in_name_! ) THEN
DO
SIGNAL OFF NOTREADY
!_sqleng_!.!_sqlcmd_! = ''
CALL RXQUEUE 'Set', !_sqleng_!.!_q_in_name_!
DO WHILE STRIP( !_sqleng_!.!_sqlcmd_! ) = ''
!_sqleng_!.!_sqlcmd_! = LINEIN( 'QUEUE:' )
END
SIGNAL ON NOTREADY
DROP !_sqlgrp_!.
END
ELSE
DO
CALL rxOutput 'Connection closed, shutting down...'
RETURN 10
END
END
ELSE IF !_isInteractiveMode_! THEN
DO
DROP !_sqlgrp_!.
!_sqlgrp_!.0 = 0
CALL CHAROUT 'STDERR', 'SQL QUERY: '
PARSE PULL !_sqleng_!.!_sqlcmd_!
END
END
ELSE IF !_sqlcase_! = 5 THEN /* CREATE TABLE ... */
DO
/* Create a file if not found */
CALL LINEOUT !_table_!, '"'||CHANGESTR( ',', CHANGESTR( "'", CHANGESTR( '"', !_columns_!, '' ), '' ), '","' )||'"'
CALL STREAM !_table_!, 'C', 'CLOSE'
CALL rxOutput '"'||!_tmp_table_!||'" has been created.'
END
ELSE IF !_isQueueMode_! THEN CALL TableNotFound
ELSE IF \!_isInteractiveMode_! THEN RETURN TableNotFound()
ELSE DO
DROP !_sqlgrp_!.
!_sqlgrp_!.0 = 0
CALL CHAROUT 'STDERR', 'SQL QUERY: '
PARSE PULL !_sqleng_!.!_sqlcmd_!
END
IF \( !_isInteractivemode_! | !_isQueueMode_! ) THEN LEAVE
END
RETURN 0
rxOutput: PROCEDURE EXPOSE __meter. !_sqleng_!. !_verbose_! !_title_! !_q_msg_! !_q_msg_name_! !_q_out_name_!
IF !_sqleng_!.!_verbose_! THEN
temp = '['||DATE()||' '||TIME()'] '
ELSE
temp = ''
IF \!_sqleng_!.!_quiet_! THEN
DO
PARSE VALUE SysTextScreenSize() WITH . screenwidth
CALL LINEOUT 'STDERR', LEFT( !_sqleng_!.!_title_!||temp||ARG(1), screenwidth )
END
IF !_q_msg_! & ARG(2) = '' THEN
DO
CALL RXQUEUE 'Set', !_sqleng_!.!_q_msg_name_!
CALL LINEOUT 'QUEUE:', !_sqleng_!.!_title_!||temp||ARG(1)
CALL RXQUEUE 'Set', !_sqleng_!.!_q_out_name_!
END
RETURN 0
SystemServiceUnavailable:
CALL rxOutput 'System: Service Unavailable to provide a queue ( "'||ARG(1)||'" )'||D2C(13)||D2C(10)
CALL SysSleep 10
RETURN 9
TableAlreadyExists:
CALL rxOutput 'Exists: Table "'||!_tmp_table_!||'" already exist, please specify a new table name or use the existing.'||D2C(13)||D2C(10)
CALL Usage
RETURN 8
TableNotFound:
CALL rxOutput "Nonexistant: Couldn't find the table "||'"'||!_tmp_table_!||'", create the table first'||D2C(13)||D2C(10)
RETURN 7
Usage:
CALL LINEOUT 'STDERR', 'Usage:'
CALL LINEOUT 'STDERR', ' Example for table "files" with some columns related to file info'
CALL LINEOUT 'STDERR', ''
CALL LINEOUT 'STDERR', ' CREATE TABLE files COLUMNS (COMPUTER,DRIVE,PATH,NAME,FILESIZE,CRC32,MODIFIED)'
CALL LINEOUT 'STDERR', " INSERT INTO files (DRIVE,PATH,NAME) VALUES ('J:','\Temp\','MyFile.txt')"
CALL LINEOUT 'STDERR', " UPDATE files SET COMPUTER = 'eComStation PC' WHERE PATH = '\Temp\'"
CALL LINEOUT 'STDERR', " SELECT * FROM files WHERE DRIVE = 'J:' GROUP BY COMPUTER ORDER BY PATH"
CALL LINEOUT 'STDERR', " DELETE FROM files WHERE COMPUTER != 'eComStation PC'"
CALL LINEOUT 'STDERR', " VACUUM TABLE files WHERE DRIVE != 'J:'"
CALL LINEOUT 'STDERR', ''
CALL SysSleep 10
RETURN 10
SignalError:
row = SIGL
SIGNAL OFF ERROR
CALL rxOutput 'Error: Error processing command on row '||row||'.'
CALL LINEOUT 'STDERR', ''
RETURN 1
SignalHalt:
row = SIGL
SIGNAL OFF HALT
CALL rxOutput 'Halt: Execution of the command has stopped on row '||row||'.'
CALL LINEOUT 'STDERR', ''
RETURN 2
SignalSyntax:
row = SIGL
SIGNAL OFF SYNTAX
CALL rxOutput 'Syntax: The syntax of the command is incorrect on row '||row||'.'
CALL LINEOUT 'STDERR', ARG(1)||D2C(13)||D2C(10)||D2C(13)||D2C(10)||'should be on the form: '||SPACE( STRIP( TRANSLATE( !_sqltype_!.!_sqlcase_!,, "'_!" ) ), 1 )||D2C(13)||D2C(10)||D2C(13)||D2C(10)||'where "rest" can be combined (in specified order) with:'
DO i = 2 TO !_sqlclause_!.0
IF i = 5 THEN
CALL LINEOUT 'STDERR', SPACE( TRANSLATE( !_sqlclause_!.i,, '_!' ) )||' (to sort in descending order)'
ELSE
CALL LINEOUT 'STDERR', SPACE( TRANSLATE( !_sqlclause_!.i,, '_!' ) )||' '||SPACE( TRANSLATE( !_sqlvar_!.i,, '_!' ) )
END
CALL LINEOUT 'STDERR', ''
CALL SysSleep 10
RETURN 3
SignalFailure:
row = SIGL
SIGNAL OFF FAILURE
CALL rxOutput 'Failure: The system could not process the command on row '||row||'.'
CALL LINEOUT 'STDERR', ''
RETURN 4
SignalNoValue:
row = SIGL
SIGNAL OFF NOVALUE
CALL rxOutput "NoValue: The variable "||CONDITION( 'D' )||" doesn't contain data on row "||row||'.'
CALL LINEOUT 'STDERR', ''
CALL Usage
RETURN 5
SignalNotReady:
row = SIGL
SIGNAL OFF NOTREADY
CALL rxOutput 'Not Ready: Could not process the command on row '||row||'.'
CALL LINEOUT 'STDERR', ''
CALL SysSleep 10
RETURN 6
rxStemCopy: PROCEDURE EXPOSE !_sqlgrp_!. !_sqlstr_!. !_sort_! !_grps_! !_sqleng_!. !_sortcase_! !_verbose_! !_TopRows_!
DO i = 0 TO MIN( !_TopRows_! % 10000, !_sqlstr_!.0 % 10000 )
CALL SysStemCopy '!_sqlstr_!.', '!_sqlgrp_!.', i * !_sqlstr_!.0 % 10000 + 1, i * !_sqlstr_!.0 % 10000 + 1, MIN( !_TopRows_!, ( i + 1 ) * !_sqlstr_!.0 % 10000 + 1 )
END
RETURN 0
rxSorted: PROCEDURE EXPOSE !_sqlgrp_!. !_sqlstr_!. !_sort_! !_grps_! !_sqleng_!. !_sortcase_! !_verbose_!
PARSE ARG sqlstem
total_count = VALUE( sqlstem||'0' )
IF !_sqleng_!.!_showprogress_! THEN CALL rxStatusBar 0
startAt = 1
stopAt = 20001
/* IF total_count < stopAt THEN
RETURN ( SysStemSort( sqlstem, !_sort_!, !_sqleng_!.!_sortcase_!, startAt, stopAt ) < 0 )
ELSE*/
DO i = 1 TO total_count % 5000 + 1
IF !_sqleng_!.!_showprogress_! THEN CALL rxStatusBar i / 2 * total_count
IF stopAt < total_count THEN
stopAt = MIN( startAt + i * 10000, total_count )
ELSE
stopAt = total_count
startAt = MAX( 1, stopAt - 10000 )
IF SysStemSort( sqlstem, !_sort_!, !_sqleng_!.!_sortcase_!, startAt, stopAt ) < 0 THEN
RETURN 1
END
IF !_sqleng_!.!_showprogress_! THEN CALL rxStatusBar 1
RETURN 0
/* part */
rxStatusBar: PROCEDURE EXPOSE __meter. !_sqleng_!. !_tmp_table_!
IF TRACE() = '?I' THEN RETURN 0
PARSE VALUE SysCurPos() WITH row .
IF SYMBOL( "__meter.height" ) <> "VAR" THEN
PARSE VALUE SysTextScreenSize() WITH __meter.height .
ELSE IF \DATATYPE( __meter.height, 'W' ) THEN
PARSE VALUE SysTextScreenSize() WITH __meter.height .
DO WHILE row > __meter.height - 1
CALL LINEOUT 'STDERR', ''
row = row - 1
CALL SysCurPos row, 0
END
RETURN rxProgress( row - 1, ARG(1), ARG(2), ARG(3) )
/* display_on_row, part */
rxProgress: PROCEDURE EXPOSE __meter. !_sqleng_!. !_tmp_table_!
IF TRACE() = '?I' THEN RETURN 0
row = ARG(1)
IF SYMBOL( "__meter.row.t_stamp" ) = "VAR" THEN
IF __meter.row.t_stamp + 1 > TIME( 'S' ) THEN RETURN 0
chr = '█▓▒░■█ '
IF SYMBOL( "__meter.width" ) <> "VAR" THEN
PARSE VALUE SysTextScreenSize() WITH __meter.height __meter.width
ELSE IF \DATATYPE( __meter.width, 'W' ) THEN
PARSE VALUE SysTextScreenSize() WITH __meter.height __meter.width
DO i = 2 TO MIN( ARG(), LENGTH( chr ) )
j = i - 1
IF LENGTH( ARG(i) ) = 0 THEN progress.j = 1
ELSE IF \DATATYPE( ARG(i), 'N' ) THEN RETURN 0
ELSE progress.j = FORMAT( ARG(i),,, 0 )
IF LENGTH( FORMAT( 100 * progress.j,, 0, 0 ) ) > 3 THEN
RETURN 0
END
progress.0 = j
processed.0 = 0
output = ''
CALL SysStemSort 'progress'
DO i = 1 TO MIN( progress.0, LENGTH( chr ) )
j = i - 1
processed.i = FORMAT( MIN( ( __meter.width - 4 ) * progress.i , __meter.width - 4 ),, 0, 0 )
progress.i = FORMAT( 100 * progress.i, 3, 0, 0 )
IF processed.i > processed.j THEN
output = output||COPIES( SUBSTR( chr, i, 1 ), processed.i - processed.j )
END
i = i - 1
output = output||COPIES( SUBSTR( chr, LENGTH( chr ), 1 ), __meter.width - 4 - processed.i )
CALL rxOut ARG(1), output||RIGHT( progress.1||'%', 4 )
RETURN 0
/* display_on_row, text */
rxWorking: PROCEDURE EXPOSE __meter. !_sqleng_!. !_tmp_table_!
IF TRACE() = '?I' THEN RETURN 0
PARSE ARG row, txt
IF SYMBOL( "__meter."||row||".t_stamp" ) <> "VAR" THEN
__meter.row.t_stamp = TIME( 'S' )
ELSE IF \DATATYPE( __meter.row.t_stamp , 'N' ) THEN
__meter.row.t_stamp = TIME( 'S' )
IF __meter.row.t_stamp + 1 < TIME( 'S' ) THEN
DO
SELECT
WHEN __meter.counter = 1 THEN
CALL rxOut row, '/ '||txt
WHEN __meter.counter = 2 THEN
CALL rxOut row, '- '||txt
WHEN __meter.counter = 3 THEN
CALL rxOut row, '\ '||txt
OTHERWISE
__meter.counter = 0
CALL rxOut row, '| '||txt
END
__meter.counter = __meter.counter + 1
END
RETURN 0
/* display_on_row, text */
rxOut: PROCEDURE EXPOSE __meter. !_sqleng_!. !_tmp_table_!
IF TRACE() = '?I' THEN RETURN 0
PARSE ARG row, txt
IF SYMBOL( "__meter.width" ) <> "VAR" THEN
PARSE VALUE SysTextScreenSize() WITH __meter.height __meter.width
IF SYMBOL( "__meter."||row||".t_stamp" ) <> "VAR" THEN
__meter.row.t_stamp = TIME( 'S' )
IF \DATATYPE( __meter.row.t_stamp, 'W' ) | \DATATYPE( __meter.width, 'W' ) THEN
PARSE VALUE SysTextScreenSize() WITH __meter.height __meter.width
isNum = DATATYPE( row, 'W' )
IF isNum THEN
DO
IF DATATYPE( __meter.row.t_stamp, 'W' ) THEN
IF __meter.row.t_stamp + 1 > TIME( 'S' ) THEN RETURN 0
PARSE VALUE SysCurPos( row, 0 ) with prev_row prev_col
END
ELSE IF DATATYPE( __meter.row.t_stamp, 'W' ) THEN
IF __meter.row.t_stamp + 1 > TIME( 'S' ) THEN RETURN 0
CALL CHAROUT 'STDERR', LEFT( txt, MAX( __meter.width, MIN( LENGTH( txt ), __meter.width ) ) )
IF isNum THEN
DO
CALL SysCurPos prev_row, prev_col
__meter.row.t_stamp = TIME( 'S' )
END
ELSE
__meter.row.t_stamp = TIME( 'S' )
RETURN 0
/* Change word number x in haystack, delimited by "delimiter" to new word */
CHANGEWRD: PROCEDURE /* haystack, wrdpos, <delimiter>, newwrd */
IF TRACE() = '?I' THEN CALL TRACE 'O'
PARSE ARG haystack, wrdpos, delimiter, newwrd
IF delimiter = '' THEN delimiter = ' '
retval = ''
DO i = 2 TO wrdpos
PARSE VALUE haystack WITH pre(delimiter)haystack
retval = retval||pre||delimiter
END
PARSE VALUE haystack WITH pre(delimiter)haystack
RETURN retval||newwrd||delimiter||haystack
/* Count occurances of delimiter after the first occurance of needle */
WRDPOS: PROCEDURE /* needle, haystack<, delimiter> */
IF TRACE() = '?I' THEN CALL TRACE 'O'
PARSE ARG needle, haystack, delimiter
IF POS( needle, haystack ) = 0 THEN RETURN 0
PARSE VALUE haystack WITH pre(needle)post
IF delimiter = '' THEN delimiter = ' '
RETURN COUNTSTR( delimiter, pre ) + 1
/* N:th word in haystack delimited by delimiter (space by delfault) */
SUBWRD: PROCEDURE /* haystack, wrdpos<, delimiter> */
IF TRACE() = '?I' THEN CALL TRACE 'O'
PARSE ARG haystack, wrdpos, delimiter
IF delimiter = '' THEN delimiter = ' '
DO i = 2 TO wrdpos
PARSE VALUE haystack WITH .(delimiter)haystack
END
PARSE VALUE haystack WITH haystack(delimiter).
RETURN haystack
/* Number of word in haystack delimited by delimiter (space by delfault) starting at start word */
SUBWRDS: PROCEDURE /* haystack, delimiter<, start_wrd<, wrds>> */
IF TRACE() = '?I' THEN CALL TRACE 'O'
PARSE ARG haystack, delimiter, start_wrd, wrds
start_pos = 0
end_pos = 0
IF start_wrd = '' THEN start_wrd = 1
IF start_wrd = -1 THEN
start_pos = LASTPOS( delimiter, haystack )
ELSE IF start_wrd = 0 THEN
start_pos = 1
ELSE DO
DO i = 1 TO start_wrd
start_pos = POS( delimiter, haystack, start_pos + 1 )
IF start_pos = 0 THEN RETURN ''
END
END
IF DATATYPE( wrds, 'W' ) THEN
DO
end_pos = start_pos
DO i = 1 TO wrds
end_pos = POS( delimiter, haystack, end_pos + 1 )
IF end_pos = 0 THEN LEAVE i
END
END
IF start_pos > 0 THEN
DO
IF end_pos < 1 | wrds = '' | start_wrd < 0 THEN end_pos = LENGTH( haystack )
IF start_pos < end_pos THEN
haystack = SUBSTR( haystack, start_pos + 1, end_pos - start_pos - 1 )
END
ELSE RETURN ''
RETURN haystack
/* Up to characters in text (haystack) after the N:th number of occurances of delimiter */
WRDS: PROCEDURE /* haystack<, delimiter<, wrdpos<, tochar>>> */
IF TRACE() = '?I' THEN CALL TRACE 'O'
PARSE ARG haystack, delimiter, wrdpos, tochar
IF delimiter = '' THEN delimiter = ' '
IF wrdpos = '' THEN wrdpos = COUNTSTR( delimiter, haystack )
strpos = POS( tochar, haystack, LENGTH( SUBWRDS( haystack, delimiter, 0, wrdpos ) ) + 1 ) - 1
IF strpos > 0 THEN
RETURN SUBSTR( haystack, 1, strpos )
RETURN haystack
/* Replace one string (needle) with another (newneedle) in text (haystack) */
CHANGESTR: PROCEDURE /* needle, haystack <, newneedle> */
IF TRACE() = '?I' THEN CALL TRACE 'O'
PARSE ARG needle, haystack, newneedle
new_haystack = ''
DO WHILE POS( needle, haystack ) > 0
PARSE VALUE haystack WITH pre(needle)haystack
new_haystack = new_haystack||pre||newneedle
END
RETURN new_haystack||haystack
/* Count the number of occurances of needle in haystack from start pos to end pos (whole string by default) */
COUNTSTR: PROCEDURE /* needle, haystack< <, startpos>, endpos> */
IF TRACE() = '?I' THEN CALL TRACE 'O'
IF ARG() < 2 THEN RETURN -1
IF DATATYPE( ARG(3), 'W' ) THEN
next = ARG(3)
ELSE
next = 1
needle = ARG(1)
haystack = ARG(2)
IF DATATYPE( ARG(4), 'W' ) THEN
haystack = SUBSTR( haystack, next, ABS( ARG(4) - next ) )
next = 1
count = 0
DO WHILE next > 0
next = POS( needle, haystack, next )
IF next > 0 THEN DO
next = next + LENGTH( needle )
count = count + 1
END
END
RETURN count
Some useful functions from REXX Tips & Tricks 3.60
/* ------------------------------------------------------------------ */
/* function: Extended RXQUEUE function */
/* */
/* usage: RXQUEUE action {,queue_name} */
/* */
/* where: action */
/* - QUERY - check if the queue "queue_name" exists */
/* */
/* syntax: RXQUEUE query , queuename */
/* */
/* - TEST - check if the queue "queue_name" is usable */
/* */
/* syntax: RXQUEUE test {, queuename} */
/* default for queuename is the current queue */
/* */
/* All other values for action are processed by the */
/* original RXQUEUE function. */
/* */
/* returns: if action = "QUERY": */
/* 1 - the queue exists */
/* 0 - the queue does not exist */
/* 40 - syntax error */
/* else */
/* error code of the original RXQUEUE function */
/* */
/* if action = "TEST": v3.60 */
/* 0 - the queue is working */
/* 1 - the queue does not work */
/* 40 - syntax error */
/* else */
/* error description (e.g SYNTAX ERROR) */
/* */
/* if action <> "QUERY" and <> "TEST": */
/* return code of the original RXQUEUE function */
/* */
/* history */
/* RXTT v3.60 - added the function TEST */
/* */
RXQUEUE: PROCEDURE
PARSE ARG action, queue_name
/* init the return code (40 = incorrect call) */
rc = 40
currentQueue = ""
/* install local error handler */
SIGNAL ON SYNTAX NAME RxQueueError
SIGNAL ON FAILURE NAME RxQueueError
SIGNAL ON ERROR NAME RxQueueError
curAction = TRANSLATE( action )
curQueue = TRANSLATE( STRIP( queue_name ) )
SELECT
WHEN curAction = "QUERY" THEN
DO
IF curQueue <> "" THEN
DO
/* try to create the queue ... */
tempQueue = "RXQUEUE"( "CREATE", curQueue )
/* ... and delete the just created queue */
CALL "RXQUEUE" "DELETE", tempQueue
/* set the return code */
rc = ( tempQueue <> curQueue )
END
END
WHEN curAction = "TEST" THEN
DO
rc = 1
/* save the current queue name */
IF queue_name <> "" THEN
currentQueue = "RXQUEUE"( "SET", queue_name )
/* The current queue is !_rest_!ored a the end of the routine */
queue_teststring = "rxqueue test function"
QUEUE queue_teststring
IF QUEUED() <> 0 THEN
DO
curString = LINEIN( 'QUEUE:' )
IF curString <> queue_testString THEN
QUEUE curString
ELSE
rc = 0
END
END
OTHERWISE
DO
/* CALL the original RXQUEUE function */
IF queue_name <> "" THEN
rc = "RXQUEUE"( action, queue_name )
ELSE
rc = "RXQUEUE"( action )
END
END
RxQueueError:
/* restore the current queue if necessary */
IF currentQueue <> "" THEN
CALL "RXQUEUE" 'set', currentQueue
RETURN rc
/* ------------------------------------------------------------------ */
/* function: Get the error message for an error code */
/* */
/* usage: GetQueueErrorMessage( errorNumber ) */
/* */
/* where: errorNumber - error number */
/* */
/* returns: the error message */
/* */
GetQueueErrorMessage: PROCEDURE
PARSE ARG errorCode
errorMessages. = "Unknown error code"
errorMessages.0 = "Operation successfull."
errorMessages.5 = "Not a valid queue name or tried to delete queue named 'SESSION'."
errorMessages.9 = "Queue named DOes not exist."
errorMessages.10 = "Queue is busy; wait is active."
errorMessages.12 = "A memory failure has occurred."
errorMessages.40 = "Incorrect call (invalid or missing queue name)."
errorMessages.48 = "Failure in system service (the queue does not exist)."
errorMessages.1000 = "Initialization error; check file OS2.INI."
IF errorCode = "" THEN
RETURN "Parameter for GetQueueErrorMessage missing"
ELSE
RETURN errorMessages.errorCode
//Jan-Erik
Well, this is embarrassing, but I had to modify the code as it contains a problem in the sections that deal with INSERT INTO.
Se comment in modified post.
Noone tried it and seen it as that render the engine only to insert rows with quotes and commas?
Hmm, maybe it's a bit to large to even dare try out ;D
but anyway, now you should be able to add data.
Do you know any other way to communicate with other applications without the use of rexx queues as they're slow?
//Jan-Erik
Quote from: jep on 2012.04.06, 06:24:23
Do you know any other way to communicate with other applications without the use of rexx queues as they're slow?
//Jan-Erik
They also can get unreliable if you try pushing a ton of data (though that could have been an early OREXX issue).
Anyway, there are a few rexx DLLs you can get that will allow other methods of communicating - here's a few:
* RexxIPC - will allow you to use OS/2's standard methods of doing so via using Named Pipe, Event Semaphore and
Mutex Semaphore OS/2 system services.
* rxsem105.zip - similar to above
* rxasyn20.zip - will allow all sorts of inter-process communication
* rxu18.zip (or rxu1a.zip - can remember which) - all sorts of functions including inter process communication
You can find most or all of those on Hobbes - and you might run into a few more... check the dev/rexx section. Also, "Rexx Tips & Tricks" I think covers a few methods.
Best,
Rob
Yes, I've looked at rxu as it seem to be what most people have already... a really important factor so one doesn't have to add yet another duplicated feature.
//Jan-Erik
Hello,
I've updated the code once more and now added some more functions to it.
It should now handle
COUNT(*), SUM(<numeric_field>), MIN(<any_field>), MAX(<any_field>)
such as:
SELECT COUNT(*),SUM(FILESIZE),MIN(FILESIZE),MAX(FILESIZE) FROM FILE
will only return 1 row with summary calculation for all rows
SELECT COUNT(*),SUM(FILESIZE),MIN(FILESIZE),MAX(FILESIZE) FROM FILE GROUP BY PATH,FILESIZE
will group rows together based on PATH (and FILESIZE)
SELECT COUNT(*),SUM(FILESIZE),MIN(FILESIZE),MAX(FILESIZE) FROM FILE GROUP BY PATH
will group rows together based on PATH that is not otherwise involved in the SELECT statement.
SELECT COUNT(*),SUM(FILESIZE),MIN(FILESIZE),MAX(FILESIZE),PATH FROM FILE GROUP BY PATH
will do the same, but also show data from "PATH".
Note that a field may have to be included in the GROUP BY clause to give the expected result(s).
Could you please take your time and test the script for me?
Can I add TCP/IP support through stack? How?
See attached script (if you're logged in to the forum/site)
//Jan-Erik
Jan-Erik, please email me. I have a present for you. It addresses login and IP uses, count, insert, bulk insert, delete, and virtually any statement execute. It reduces all SQL calls to one of five (or is it four?) Rexx statements.
R
Hello Robert,
I've sent you an email some days ago.
//Jan-Erik
REXX SQL Engine - Mid Summer Edition
| Tweaks added | The options you can set as environment variables |
| TCP/IP network connection | Tested with a slightly modified example from EDM/2 |
| External NLS | National Language Support available in Swedish and English |
I'd like to ask for your help to add IPC through rxu.
A short example would be nice to have.
Regarding TCP/IP
Ensure that the port 2638 (41592, 41593, 42595, 41596, 41597, 42598, 45299 & 6110 if you consume ports by crashing) is open in your firewall and router.
Type in the command line:
rxdb TCPIP PRELOAD mytablewhere "mytable" is a table in the right file format.
ON the "client" ide you can type
tmsg <ip-number> SELECT TOP 300 COUNT(*),PATH FROM mytable WHERE PATH != '\' GROUP BY EXTENSION where <ip-number> should be replaced by something similar to 192.168.0.123
Close the server with
QUIT on the client.
Use tmsg.cmd from EDM/2 (with port set to the one the server select to send the SQL query to use) and modify some of the code for example to:
/*------------------------------------------------------------------
* send message across ... loop in case entire message can't be sent
* in one SockSend()
*------------------------------------------------------------------*/
cr = d2c(13)
lf = d2c(10)
crlf = cr || lf
message = message || crlf
do while (message <> "")
bytes = SockSend(socket,message)
message = substr(message,bytes+1)
end
say date() time() ': sent'
text = ''
IF TRANSLATE( ARG(1) ) <> 'QUIT' THEN
DO WHILE rc > -1 & chunk <> ''
text = ''
rc = SockRecv( socket, 'chunk', 1000 )
/* IF rc < 0 | chunk = '' THEN
text = text||crlf*/
text = text||chunk
SAY 'recieved:' rc ':' text
END
say date() time()Is it possible to write code (workstation/client side) to figure out which port this db-server use when the client tries to connect?
How?
//Jan-Erik
Update to rxdb.cmd
Can now handle mixed and lower case statements.
Before:
SELECT * FROM mytable WHERE pos( 'DOCUMENTS', fname ) != 0
Now:
sELect * FRoM mytable WhErE pos( 'DOCUMENTS', fname ) != 0
You should now also get better results from:
SELECT count(*), sum(filesize) from mytable WHERE pos( 'DOCUMENTS', fname ) != 0 GROUP BY path
Note that you have to handle mixed case comparisons like before.
Example:
select * from mytable Where pos( 'DOCUMENTS', fname ) != 0
can only find filenames with the text DOCUMENTS in upper case, while
select * from mytable Where pos( 'DOCUMENTS', translate( fname ) ) != 0
will find any filename that contain the text documents as the function "translate(...)" convert the text to upper case before the comparison.
Note: rxdb.cmd is a stand alone database engine but it doesn't (yet) work with ODBC.
//Jan-Erik