base.SqlHandle

One of the strengths of the 4gl language is its close ties with the database.  SQL syntax such as SELECT, INSERT, DELETE, UPDATE, CREATE, ALTER, DROP etc are all part of the 4gl language syntax.  This extends to database cursors where PREPARE, DECLARE, OPEN, FETCH, FOREACH etc are also part of the syntax.  However there are some things you can’t do such as passing a database cursor as a parameter to a function, interrogating the results (i.e what is the name and data-type that SELECT * is returning), and there are places where you can end with duplicitous code such as …

IF some-condition THEN
    OPEN my_cursor USING param1
ELSE
    OPEN my_cursor USING param1, param2
END IF

or perhaps

IF some-condition THEN
    FETCH my_cursor INTO var1
ELSE
    FETCH my_cursor INTO var1, var2
END IF

… that is the number of input parameters or columns returned varies under certain circumstances.  You might think that does not look too bad, but what if there were multiple conditions, or conditions with more than one value.  Ideally you would code one OPEN, one FETCH, not a multiple of them for every combination of conditions.

The base.SqlHandle class is a built-in class allowing you to execute SQL statements with a 3gl API.  These statements can have result sets e.g. SELECT, or not e.g. INSERT, DELETE, UPDATE.  They can cater for a variable number of input parameters, and can cater for returning a variable number of columns.

To understand the base.Sqlhandle class I suggest looking through the list of available methods.  You should note that there are many familiar method names such as prepare, open, fetch, close.  These methods have similar meaning as to their 4gl syntax equivalents and need to be executed in the same order i.e prepare then open then fetch then close.

The variations or methods with new names include …

• you need to instantiate a base.SqlHandle variable with the create method.  This variable then has the relevant scope and can be passed to functions.
• you can pass a variable numbers of parameters to the open using setParameter before the open.
• you can interrogate what is returned using the four get methods.  getResultCount will tell you how many columns were returned, getResultName and getResultType can tell you about each column returned, and getResultValue is used to get the values returned by the SQL statement.
• If you are eagle-eyed you might have observed that there is no FOREACH, but what is a FOREACH?  It is simply an OPEN, and then consecutive FETCH’s in a WHILE loop until status is NOTFOUND.  Using base.Sqlhandle you code a FOREACH equivalent using a WHILE loop and the open() and fetch() methods, exiting the loop when status = NOTFOUND.

The documentation has a good usage section, and  4 good examples illustrating different uses of base.SqlHandle, with and without results sets, insert and scroll cursor.

A good candidate for places to use base.Sqlhandle is in generic or library functions.  One such generic function is in the fgl_zoom repository I have covered in the past few articles.  The reason I use base.Sqlhandle is because each SQL returns a variable number of columns of various types that are not known at compile time, and these are then fed into the dynamic dialog.  If I did not use base.SqlHandle I would have to do something like define an arbitrary maximum number of columns, fetch into each column, and then hide the unnecessary columns in the dialog.  In fact a precursor to fgl_zoom had the line …

FETCH ABSOLUTE l_rec qdcurs
    INTO ma_desc[1], ma_desc[2], ma_desc[3], ma_desc[4], ma_desc[5],ma_desc[6], ma_desc[7], ma_desc[8], ma_desc[9], ma_desc[10]

… and it used this wether the cursor returned 1,2,3,…,9,10 columns.

So if we look inside fgl_zoom.4gl and look for base.SqlHandle, we will find the usage of the base.SqlHandle class (this should be in the list function).

First we define a variable of base.SqlHandle type

DEFINE l_sqlh base.SqlHandle

Before I work with the base.SqlHandle I determine the full SQL statement.

LET l_sql = SFMT(this.sql, this.where, this.columnlist_get())

It is really outside the scope of this discussion but the above means the developer can code the SQL simply as

LET zoom.sql = "SELECT %2 FROM table WHERE %1"

and the SFMT clause will put the where clause generated by the dynamic dialog construct in the right place(s) as indicated by the %1, and the developer does not have to repeat the column list in the SQL clause but can use the columns as defined in the zoom.column dynamic array and these will replace %2 in the SQL definition.  So thats what the SFMT is doing.

So having got the SQL statement, then a base.SqlHandle variable is instantiated, and a database cursor prepared and opened.  There are no parameters in the fgl_zoom usage of base.Sqlhandle, if there were they would be before the open().

LET l_sqlh = base.SqlHandle.create()
CALL l_sqlh.prepare(l_sql)
CALL l_sqlh.open()

Then we enter a WHILE loop, use the fetch() method to retrieve a row from the database query, and exit the while loop if we get a NOTFOUND i.e get to the end of the result set.

WHILE TRUE
    CALL l_sqlh.fetch()
    IF SQLCA.SQLCODE = NOTFOUND THEN
        EXIT WHILE
    END IF

This next bit of code simply exits the loop if we only want to return a maximum number of rows (in hindsight this would be better placed at the end of the WHILE loop to save an unnecessary fetch)

LET l_row_count = l_row_count + 1
IF this.maxrow > 0 THEN   
    IF l_row_count > this.maxrow THEN
        LET l_maxrow_flg = TRUE
        LET l_row_count = this.maxrow
        EXIT WHILE
    END IF
END IF

To read each row returned, I interrogate the number of columns returned, and then place this into a two dimensional array.  I could have put the results straight into the dynamic dialog via the ui.Dialog.setFieldValue method but I think I found I needed to keep the values after the dialog had been terminated.

FOR l_column = 1 TO l_sqlh.getResultCount()
    LET this.data[l_row_count, l_column] = l_sqlh.getResultValue(l_column)
END FOR

Having fetched the data we need, we then close the base.sqlHandle variable and free up the associated resources.

    END WHILE
CALL l_sqlh.close()

Hopefully you can see that this series of 3gl methods is still making the same series of calls that a 4gl equivalent would’ve had, that is PREPARE, OPEN, FETCH, CLOSE etc, and that the FETCH equivalent returned a variable number of columns thus precluding the use of the 4gl statements.

I did not need to use the setParameter() method as there was no parameters for the SQL, only a where clause, and I did not need to use the getResultType(), or getResultName() methods as I was happy to take the results and place them straight into a STRING.

The key to working with base.SqlHandle is to understand that it is the same sequence of PREPARE, DECLARE, OPEN, FETCH, CLOSE type calls, only one or more of the number of parameters, SQL, number of columns returned is not known at code/compile time.

Where should you use base.Sqlhandle?  Your first instinct should be to use the 4gl syntax, but I think in some libraries or generic functions you may find that base.SqlHandle is the more appropriate syntax.