Hello Robert,
Nice! When do you post it?
Does your code support multi threading for INSERTs?
The way it is written, it will support:Multithreaded requests through any Rexx framework that supports multithreading and some form of IPC.
- each SQL operation instance (retrieve, delete, update, etc) creates its own connection and unique connection pointer
- you are only limited by the following: (a) the number of connections your MySQL server can maintain at once and (b) the resources available to the Rexx session
That is:
Call the function to run the SQL Command and it will return
Update the screen, progressbar etc. and look for more data to add/retrieve.
Call it again and again and again... and it will queue each statement and return to the script, though process and run each command internally within the thread until an error occur or there are no more commands to process.
This method is probably doable, but the way it is written, since there's very little overhead even on big tables (assuming indexes have been created) is that the program will maintain a "pointer" to what offset it is on and request only the subset it needs.
So, for instance, lets say you've got a result set of 24. You request the first ten (the quantity statement is unnecessary and redundant in this) by sending "Limit 0,10" in place of the "LIMIT" parameter. You've just retrieved records 0-10. On a person hitting "next" or whatever mechanism, the program updates its pointer to start at the 10th offset (Limit 10,10) which retrieves rows 11-20. On the next iteration, using the same format, it will request Limit 20,10, and only 4 rows will be returned. The return value will thus be 4, and, as is standard for the SQLFetch function in the library, my wrapper function will set the stem variable accordingly (such as SQLResults.Column1.0=4) - thus giving you two methods of determining the size of the return set.
What I do in such cases is the following:(1) Grab a count: VALS gets set to Count(*) as Count
(2) Determine the number of offsets I need to retrieve ALL records defined by the "Where" clause: If I want 25 records per request, and there are 102, then I have 5 sets (4 of 25 records, and one of 2 records). I "cheat" and simply divide Count/RecPerPage, add .999999999 and Trunc:
- NumResultPages=Trunc((Count/RecPerPage)+.9999999999)
(3) I start my offset counter at 0 and simply ensure it is always less than or equal to NumResultPages and request with a Limit Offset,25
It now internally use the "describe" function to find out what columns the stement will return (if any).
//Jan-Erik
I'm working on an easy Rx_SQL_Describe function to add to the collection, but haven't bothered, because I also wrote an easy "table builder" script where I can simply drop the defining statements in a text file in a format such as:
*UniqueColumn VarChar(20)
-KeyedColumn1 VarChar(50)
UnIndexedColumn Integer
-AnotherKeyed IntegerI'd been "cheating" by simply loading the table definition text file in other code when I needed to know what each column was. For those who haven't noted the "method", a "*" preceding a column name makes my table builder script create a unique key for it, while a "-" makes it create a "regular" index. That code does support other things as well, such as more complex indexes (eg: containing multiple columns, or whatever)
Currently, the only "constraints" the software has are Certain variables MUST be named a certain way - this has to do with the fact that I am using "EXPOSE" to be able to ensure results are able to be passed back.
- The variable "STEM" in the function calls must always be "STEM", eg:
STEM="MyResults."
ReturnCode=Rx_SQL_Retrieve(Username, Password, IP, Database, Table(s), Quantity, Where, OrderBy, Stem, Vals, Limits)
That does NOT mean that your results can only go to the same stem. It simply means that the stem you want the results to go to can only be defined in a variable NAMED stem.
For instance:
STEM="MyResults1."
ReturnCode=Rx_SQL_Retrieve(Username, Password, IP, Database, SomeTable, Quantity, Where1, OrderBy, Stem, Vals, Limits)
STEM="MyResults2."
ReturnCode=Rx_SQL_Retrieve(Username, Password, IP, Database, SomeTable, Quantity, Where2, OrderBy, Stem, Vals, Limits)
/* MyResults1 will have the results from the first retrieve call */
/* MyResults2 will have the results from the second retrieve call */
- A variable named "RxSQL_Log" must be defined - and it must be defined with a filename in a valid path (or with no path, in which case it uses the execution path - but it cannot have an invalid path)
RxSQL_Log="E:\Logs\SQL\Errors.Log"
OPTIONALLY, one can set one other variable, RxSQL_LogFormat, which will do the following:
RxSQL_LogFormat="" /* If set to nothing, or NOT set at all, error logs will be written to the exact file specified */
/* OR */
RxSQL_LogFormat="D" /* If set to "D", logs will have the date prepended to the file name specified in RxSQL_Log */
/* For instance, "Errors.Log" above would be written as "2013-05-02_Errors.Log" */
RxSQL_LogFormat="DT" /* If set to "DT", logs will have the date AND time prepended to the file name specified in RxSQL_Log */
/* For instance, "Errors.Log" above would be written as "2013-05-02-13-44-22_Errors.Log" */
The logging variables are global, and a separate function is used by each Rx_SQL_* function to do the logging to check for file lock contentions.