|
jep
|
 |
« 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  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
|
|
|
|
« Last Edit: 2011.12.25, 11:51:01 by jep »
|
Logged
|
|
|
|
|
RobertM
|
 |
« 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
|
|
|
|
|
jep
|
 |
« 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
|
 |
« 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
|
 |
« Reply #4 on: 2012.03.18, 19:22:07 » |
|
/* * 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
|
 |
« Reply #5 on: 2012.03.18, 19:23:11 » |
|
/* 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
|
 |
« 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_!,, '"' ) 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
|
 |
« Reply #7 on: 2012.03.18, 19:24:54 » |
|
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
|
 |
« Reply #8 on: 2012.03.18, 19:25:14 » |
|
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
|
|
|
|
« Last Edit: 2012.03.19, 08:16:05 by jep »
|
Logged
|
|
|
|
|
jep
|
 |
« 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  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
|
 |
« 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
|
|
|
|
|
jep
|
 |
« 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
|
|
|
|
|