SqlHanded method and performance ?

[Benjamin G.]

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

[Sebastien F.]

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

[Sebastien F.]

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

[Benjamin G.]

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

[Reuben B.]

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

Create a dictionary of integer

Code (genero) Select Expand

DEFINE d DICTIONARY OF INTEGER

Populate it using base.Sqlhandle.getResultName()

Code (genero) Select Expand

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

And then you can code

Code (genero) Select Expand

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

as an equivalent to the

Code (genero) Select Expand

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.