• Welcome to OS2World OLD-STATIC-BACKUP Forum.
 

News:

This is an old OS2World backup forum for reference only. IT IS READ ONLY!!!

If you need help with OS/2 - eComStation visit http://www.os2world.com/forum

Main Menu

Database engine written in rexx

Started by jep, 2011.12.24, 12:02:42

Previous topic - Next topic

jep

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

RobertM

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).



|
|
Kirk's 5 Year Mission Continues at:
Star Trek New Voyages
|
|


jep

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) 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

jep

#3
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.

jep

#4
/*
* 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

jep

#5
    /* 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

jep

#6
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

jep

#7
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

jep

#8
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

jep

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

RobertM

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



|
|
Kirk's 5 Year Mission Continues at:
Star Trek New Voyages
|
|


jep

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

jep

#12
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

RobertM

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


|
|
Kirk's 5 Year Mission Continues at:
Star Trek New Voyages
|
|


jep

Hello Robert,

I've sent you an email some days ago.

//Jan-Erik