OS2 World.Com Forum
2012.05.26, 04:02:33 *
Welcome, Guest. Please login or register.

Login with username, password and session length
News:
 
   Home   Help Search Login Register  
Pages: [1]
  Print  
Author Topic: Database engine written in rexx  (Read 1777 times)
jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« on: 2011.12.24, 12:02:42 »

Hello,

I'm working quite alot with databases these days and therefore came up with the idea to write one on my own  Wink
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
Code:
/*
 * 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
« Last Edit: 2011.12.25, 11:51:01 by jep » Logged
RobertM
Global Moderator
Hero Member
*****
Posts: 2017



View Profile WWW
« Reply #1 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).

Logged

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

jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« Reply #2 on: 2012.01.04, 12:42:57 »

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.


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
Logged
jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« Reply #3 on: 2012.03.18, 19:19:53 »

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.
« Last Edit: 2012.03.21, 07:15:56 by jep » Logged
jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« Reply #4 on: 2012.03.18, 19:22:07 »

Code:
/*
 * 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
« Last Edit: 2012.03.19, 08:18:52 by jep » Logged
jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« Reply #5 on: 2012.03.18, 19:23:11 »

Code:
    /* 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
« Last Edit: 2012.03.19, 08:13:14 by jep » Logged
jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« Reply #6 on: 2012.03.18, 19:23:47 »

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_!,, '"' )


Code:
   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
« Last Edit: 2012.04.06, 06:17:11 by jep » Logged
jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« Reply #7 on: 2012.03.18, 19:24:54 »

Code:
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
« Last Edit: 2012.03.19, 08:15:25 by jep » Logged
jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« Reply #8 on: 2012.03.18, 19:25:14 »

Some useful functions from REXX Tips & Tricks 3.60
Code:
/* ------------------------------------------------------------------ */
/* 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
« Last Edit: 2012.03.19, 08:16:05 by jep » Logged
jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« Reply #9 on: 2012.04.06, 06:24:23 »

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  Grin
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
Logged
RobertM
Global Moderator
Hero Member
*****
Posts: 2017



View Profile WWW
« Reply #10 on: 2012.04.06, 06:59:21 »

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

Logged

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

jep
Global Moderator
Sr. Member
*****
Posts: 402


View Profile
« Reply #11 on: 2012.04.06, 11:54:07 »

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
Logged
Pages: [1]
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.14 | SMF © 2006-2011, Simple Machines LLC Valid XHTML 1.0! Valid CSS!