Four Js Development Tools Forum

Hi,

Only a suggestion, i've tested SqlHandle and it will be nice to have a method "getResultValueByName" so if we change the column order of a SQL we don't need to change the "getResultValue" by position.

Another question, do you know if there is a difference in performance between "classic way" and "sqlHandle way" ?

Regards

Hello,

Please check:

https://4js.com/support/issue/?id=FGL-04063#startissue

There are 2 main reasons why we won't support *ByName() methods with base.SqlHandle:

1) The column names of a SELECT (or any SQL that returns a result set) are provided by the database client API, not by Genero BDL / ODI drivers.
Sometimes it's not fully supported / not reliable.
Try expressions and column aliases with different DB engines supported by Genero and see what you get in getResultName()

2) The purpose of base.SqlHandle is to write generic code. Why would you want to use column names?!

Seb

Regarding performances:

There is not much differences with static SQL or dynamic SQL statements and base.SqlHandle.

However, DO NOT replace all you existing SQL statements with base.SqlHandle!

Keep code readability with static SQL and use dynamic SQL only when needed.

Seb

Hi,

You are absolutely right about the differences in the presentation of a "result set" according to the DB engines. I was only saying that it would be a "nice to have feature" if the "select" is correctly written with the use of "columns alias".

Using the "byname" makes writing the code easier because you don't have to count the positions ...

We always "prepare" SQL statements. The use of SqlHandle would allow declaring "cursors" in a global way. In addition it makes the writing more "structured" and no need to use the "declare cursor".

regards

The equivalent of getResultValueByName() is very easy to implement using tools available to you.

Create a dictionary of integer

DEFINE d DICTIONARY OF INTEGER

Populate it using base.Sqlhandle.getResultName()

    FOR i = 1 TO h.getResultCount()
        LET d[h.getResultName(i)] = i
    END FOR

And then you can code

DISPLAY h.getResultValue(d["column-name"])
 

as an equivalent to the

DISPLAY h.getResultValueByName("column-name")
 

you seek.

As previously noted, you can explore what base.SqlHandle.getResultName returns for expressions, aliases etc, and make appropriate allowances in the above suggestion.

Performance wise, you can always measure performance using the profiler http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_profiler_001.html and come to your own conclusions.  They key thing with base.Sqlhandle is developer productivity.  If you code every SQL in your system, using base.SqlHandle, your developers will be slower, but if you can code a generic function using base.SqlHandle and reuse that generic function over and over then your developers will be faster.