Author Topic: RexxSQL for MySQL Simplified Routines  (Read 3059 times)

Robert Mauro

  • Global Moderator
  • Newbie
  • *****
  • Thank You
  • -Given: 0
  • -Receive: 0
  • Posts: 13
    • View Profile
RexxSQL for MySQL Simplified Routines
« on: May 01, 2013, 11:44:54 pm »
Hello all,

In this thread, I will be posting a collection of Rexx scripts that allow easy use of RexxSQL for MySQL. They incorporate a bunch of the standard functionality allowed in RexxSQL and make that functionality easier to use while also adding a few little things.

The direct callable Rexx Functions I have written are:


Rx_SQL_Insert. . . . .This function supports a variety of insert methods, INCLUDING bulk inserts:
  • Standard insert with ("COLS") ("VALS") syntax
  • Standard insert with COL1="VAL1",COL2="VAL2" syntax
  • Bulk insert with ("COLS") ("VALSET1"),("VALSET2")...("VALSETN") syntax
Rx_SQL_Retrieve. . . . .This function supports all standard SQL Select methods, including "count(*) AS label"
  • Standard "Where" supported
  • "Enhanced" "where" clause support including with nested statements in the where (those statements must be written out as standard MySQL SQL statements)
  • Easily pass Quantity, Limits and Order By (and/or) Group By values
  • Specify columns to return
Rx_SQL_Delete_Row. . . . .Standard SQL delete using standard "Where" clauses
Rx_SQL_Update_Row. . . . .Standard SQL Row Update call using standard "where" clauses
Rx_SQL_Execute. . . . .Execute most other single SQL statement (ie: "Drop table TABLENAME" or "Create Table TABLENAME (PARAMS)"

One may ask "why did you create these functions when the RexxSQL library you are calling in the back end does all of this?"

The reason is simple. As a for instance, to retrieve a row using RexxSQL, it takes calls to the following (keeping track of and using various parameters and return values:
Code: [Select]
SQLConnect()
SQLPrepare()
SQLOpen()
SQLFetch()
SQLClose()
SQLDispose()
SQLDisconnect()

On the other hand, my function requires just this (and in the event of an error, returns the numeric error code, and logs the errors to an error log you define globally):
Code: [Select]
ReturnCode=Rx_SQL_Retrieve(Username, Password, IP, Database, Table(s), Quantity, Where, OrderBy, Stem, Vals, Limits)
That's it! One line, instead of SEVEN, AND it will log errors, generate the unique pointers required for the connection and handle returning count values - as well as fill a multi-"column" stem array with the results.

I'm doing some code cleanup (yay, finally getting around to it) and should have it posted within two weeks. The code is tried and tested and was created by me 15 years ago (and I've been using it ever since).

This code of course means it is easier to make calls in a web script or in a variety of other places (we use this for websites, web apps, and GUI applications). The collection will include a few supporting functions as well - some of which may directly be callable elsewhere without modification.
« Last Edit: May 02, 2013, 04:37:04 pm by Robert Mauro »



Jan-Erik Lärka

  • Global Moderator
  • Full Member
  • *****
  • Thank You
  • -Given: 5
  • -Receive: 11
  • Posts: 151
    • View Profile
Re: RexxSQL for MySQL Simplified Routines
« Reply #1 on: May 02, 2013, 09:16:28 am »
Hello Robert,

Nice! When do you post it?

Does your code support multi threading for INSERTs?

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.

sqlstr = "SELECT filename,*,SUM(*) FROM file_sys WHERE filesize > 1000"

IF rxSQLCommand( 'CONNECT_NAME', sqlstr, 10 ) THEN /* 10 rows, even if more has been returned */
   ...

IF rxSQLCommand( 'CONNECT_NAME', sqlstr, 0 ) THEN /* No rows */
   ...

IF rxSQLCommand( 'CONNECT_NAME', sqlstr, -1 ) THEN /* Don't care */
   ...

IF rxSQLCommand( 'CONNECT_NAME', sqlstr, -2 ) THEN /* Multi thread */
   ...

Interesting that there isn't just one function to handle sql statements. Guess that was I wrote a rxODBC.dll on my own in the first place and use it to unload and reload databases, recover broken databases etc. :-)
It now internally use the "describe" function to find out what columns the stement will return (if any).

//Jan-Erik

Robert Mauro

  • Global Moderator
  • Newbie
  • *****
  • Thank You
  • -Given: 0
  • -Receive: 0
  • Posts: 13
    • View Profile
Re: RexxSQL for MySQL Simplified Routines
« Reply #2 on: May 02, 2013, 04:35:44 pm »
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               Integer


I'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:
Code: [Select]
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:
Code: [Select]
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)
Code: [Select]
RxSQL_Log="E:\Logs\SQL\Errors.Log"
OPTIONALLY, one can set one other variable, RxSQL_LogFormat, which will do the following:
Code: [Select]
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.
« Last Edit: May 02, 2013, 04:56:52 pm by Robert Mauro »

Robert Mauro

  • Global Moderator
  • Newbie
  • *****
  • Thank You
  • -Given: 0
  • -Receive: 0
  • Posts: 13
    • View Profile
Re: RexxSQL for MySQL Simplified Routines
« Reply #3 on: May 02, 2013, 04:49:51 pm »
A LONG time ago, I was told that this call could be simpler by globally defining the user/pass/IP/db portions (and of course table):
Code: [Select]
ReturnCode=Rx_SQL_Retrieve(Username, Password, IP, Database, Table(s), Quantity, Where, OrderBy, Stem, Vals, Limits)
I've left it as I wrote it all those years ago because it creates limitations in capabilities. The reason such is specified on each and every call is:
- It allows the ability to do calls to multiple databases or tables within the same code very easily, from anywhere, without having to worry about global vs local variables
- It allows connecting to databases that require different username/password combinations, all in the same code
- It allows connecting to different servers

One final note on it. On OS/2, RexxSQL and MySQL both support using named sockets for connections. If an IP address is not specified (IP=""), the code presumes you want a named socket connection to the local MySQL instance and uses that instead (\socket\MySQL specifically). Additionally, it of course also allows you to specify a MySQL server on a different machine by simply using an actual IP address if you wanted.

oREXX vs cREXX for large result sets:
ANYWAYS, using cREXX, performance using RexxSQL and my wrapper functions is rather rather decent. Far faster than ODBC connections using MSSQL on WinServer 2003 on a box with many times the CPU power and memory. Performance using various versions of oREXX range from very decent to absolutely pathetic (some interesting issues were introduced in certain versions of oREXX that make using stems eat memory and take ages). Yes, by "eat memory" I mean leak. Create a stem, fill it, drop the stem and watch only a portion of your resources get returned, eventually exhausting your resource pool - all while actually using MORE memory than necessary.

I have not tested it with the version of oREXX that comes with eCS v2.1 yet to see how much that issue persists, though I did point out the most flawed oREXX releases (and the ones I found to be least flawed in this matter) to the eCS team a few years ago, and I think they've looked into it to try to ensure one of the better versions was included in later versions of eCS (such as hopefully 2.0/2.1/etc). Many people may not run into the memory leak issue, but I've got some code that queries multiple result sets from multiple database tables, and builds stem arrays with easily 100,000 values. Running it more than twice would guarantee an "out of resources" error - and occasionally, running it only once would do so. All those machines are currently running cREXX obviously, since that wasn't an acceptable result.