Four Js Customer Forum

Is there some way to create dynamically the "execute" statement of a query? More specifically, the "using" block.

Here is the situation, i have a "Prepare" statement for a query that is very dynamic, having more or less tables to insert and the blob types vary a lot according to the tables that i have to insert.

So i have to make a "case" block with all the possible combinations of tables and blob types. So i would like to know if is possible to create dynamically the "execute" statement.

Other possibility is to change the variable type during the program execution, like i have the "execute ... using var1, var2, var3" and the type of var1, var2 and var3 can change according to the table.

Thanks for your attention

Hello Anderson,

Unfortunatly, the USING clause of EXECUTE or OPEN/FOREACH must provide the exact number variables corresponding to the ? parameters used in the SQL statement, otherwise you get the error:

SQL statement error number -254.
Too many or too few host variables given.

Note that you can use more variables in an INTO clause is the number for fetched SELECT items...

As a general advice, avoid to concat constant values to build dynamic SQL statements as in:
DEFINE sql STRING, d DATE
LET d = TODAY
LET sql = "SELECT ... WHERE order_date > '", d, "'"
Because this is not portable accross different databases (ex: date formatting rules are db specific).

In your case, since there can be so many parameter combinations, using SQL constants could be the best solution, assuming that you generate SQL constants according to the target database type.

But unfortunately, you want to pass Large OBject values, and these cannot be converted to SQL constants...
The only way it to use TEXT/BYTE variables.

Assuming that you are using Informix and TEXT/BYTE columns, I wanted to suggest you to use FILETOCLOB/FILETOBLOB functions, but unfortunately it's not possible to cast CLOB to TEXT / BLOB to BYTE:

MAIN
   DATABASE test1
   CREATE TEMP TABLE tt1 ( k INT, t TEXT )
   EXECUTE IMMEDIATE "INSERT INTO tt1 VALUES ( 1, FILETOCLOB('xx','client')::TEXT )"
END MAIN

gives:

SQL statement error number -9634.
No cast from clob to text.


So far, I see no other way as a large CASE switch will all possible USING combinations, but that seems undoable, if you want to support all possible data types!

Best thing to do is to you contact our support to give more details about your case, we could then properly analyze all options.

- what is the target db?
- what kind of SQL params can be used (max number, possible types)?
- why is it possible to have so many BYTE/TEXT param combinations in your application?

Seb

Hello Seb

I understand that this is not a normal situation, actually there is about 20 cases that i need to support, so it's possible to do and in this case it's the best solution.

Other solution will be to make Genero generates his own code and write a .4gl file, so i use "base.Channel.openPipe" to call "gcomp" in the server and compile the self generated code, then i call the self generated program do execute the SQL.  Yeah, now THAT is crazy!

Generating 4gl code on the fly and run it is maybe not that crazy.
As long as you have a development license on the production server this can be a good option.
Of course if you have to deploy your application on many production servers with a runtime license, you cannot compile.

We have also the idea to extend the language with so called "dynamic record" concept, where you could define RECORD members at runtime.
But this is not easy to implement - long term feature -
However to me it's a must to write generic code in Genero.

Back to your need, I am still wondering what kind of query you have to deal with.

Is this for INSERT, UPDATE or SELECT? Any kind? Are you writing a SQL command interpreter? ;-)

Maybe you could split the task into several SQL statements, each taking a single LOB param, if the LOB columns allow NULLs.

I mean:

PREPARE s FROM "INSERT INTO mytab (pk,b1,b2) VALUES (?,?,?)"
EXECUTE s USING rec.pk, rec.b1, rec.b2
is like:

PREPARE s1 FROM "INSERT INTO mytab (pk,b1,b2) VALUES (?,NULL,NULL)"
EXECUTE s1 USING rec.pk
PREPARE s2 FROM "UPDATE mytab SET b1 = ? WHERE pk = ?"
EXECUTE s2 USING rec.pk, rec.b1
PREPARE s3 FROM "UPDATE mytab SET b2 = ? WHERE pk = ?"
EXECUTE s3 USING rec.pk, rec.b2

... you can repeat s2...sN

Seb

So, actually the program is a "table replicator". Since our company has many servers worldwide it's necessary to replicate some informations between servers.

The only option we had was to "hang" the program in the user's computer until the copy was done, and in the case of some server be unavailable at the time the user must retry the operation latter.

What we has developed is a program that run SQL query's, any query to anywhere. The user must call a function informing the destination, the desired execution time and the SQL query to execute, like this:


type t_agendar_query dynamic array of record
                                        sql     like diacpa.cpasql,
                                        tabname like diacpa.tabname,
                                        blob    dynamic array of    record
                                                                        arq     like diapab.pabarq,
                                                                        text    like diapab.pabtext,
                                                                        varchar like diapab.pabvarchar
                                                                    end record
                                      end record


sql is the actual SQL query to run.
tabname is the name of the table where the query will be ran, this is not used, it's just to keep a record.
Blob is a dynamic array, so each "?" in the query equates to a position in the array, the user must inform only one of the types in the record.

And here come the case, i have to "execute" the query informed in the "sql" and evaluate the "blob" length, testing for each position witch is null and witch in informed, like this:

foreach pont_pab into la_cpa[l_indcpa].blob[l_indpab].pabarq,
                              la_cpa[l_indcpa].blob[l_indpab].pabtext,
                              la_cpa[l_indcpa].blob[l_indpab].pabvarchar
           
            if   la_cpa[l_indcpa].blob[l_indpab].pabarq is not null
              then
                 let la_cpa[l_indcpa].blobind = la_cpa[l_indcpa].blobind clipped, "A"
            end if
           
            if   la_cpa[l_indcpa].blob[l_indpab].pabtext is not null
              then
                 let la_cpa[l_indcpa].blobind = la_cpa[l_indcpa].blobind clipped, "T"
            end if

            if   la_cpa[l_indcpa].blob[l_indpab].pabvarchar is not null
              then
                 let la_cpa[l_indcpa].blobind = la_cpa[l_indcpa].blobind clipped, "V"
            end if

        end foreach

blobind is a code that show the array length and the data type of each position.

Our system have a limit of 3 files in the blob array, combined with 3 types in each of the 3 positions, so the full case is this:

case la_cpa[l_i].blobind
          when "0"
            execute com_sql
          when "A"
            execute com_sql using la_cpa[l_i].blob[1].pabarq
          when "T"
            execute com_sql using la_cpa[l_i].blob[1].pabtext
          when "V"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar
          when "AA"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabarq
          when "AT"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabtext
          when "AV"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabvarchar
          when "TA"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabarq
          when "TT"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabtext
          when "TV"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabvarchar
          when "VA"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabarq
          when "VT"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabtext
          when "VV"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabvarchar           
          when "AAA"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabarq, la_cpa[l_i].blob[3].pabarq
          when "AAT"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabarq, la_cpa[l_i].blob[3].pabtext
          when "AAV"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabarq, la_cpa[l_i].blob[3].pabvarchar
          when "ATA"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabtext, la_cpa[l_i].blob[3].pabarq
          when "ATT"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabtext, la_cpa[l_i].blob[3].pabtext
          when "ATV"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabtext, la_cpa[l_i].blob[3].pabvarchar
          when "AVA"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabvarchar, la_cpa[l_i].blob[3].pabarq
          when "AVT"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabvarchar, la_cpa[l_i].blob[3].pabtext
          when "AVV"
            execute com_sql using la_cpa[l_i].blob[1].pabarq, la_cpa[l_i].blob[2].pabvarchar, la_cpa[l_i].blob[3].pabvarchar
          when "TAA"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabarq, la_cpa[l_i].blob[3].pabarq
          when "TAT"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabarq, la_cpa[l_i].blob[3].pabtext
          when "TAV"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabarq, la_cpa[l_i].blob[3].pabvarchar
          when "TTA"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabtext, la_cpa[l_i].blob[3].pabarq
          when "TTT"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabtext, la_cpa[l_i].blob[3].pabtext
          when "TTV"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabtext, la_cpa[l_i].blob[3].pabvarchar
          when "TVA"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabvarchar, la_cpa[l_i].blob[3].pabarq
          when "TVT"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabvarchar, la_cpa[l_i].blob[3].pabtext
          when "TVV"
            execute com_sql using la_cpa[l_i].blob[1].pabtext, la_cpa[l_i].blob[2].pabvarchar, la_cpa[l_i].blob[3].pabvarchar
          when "VAA"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabarq, la_cpa[l_i].blob[3].pabarq
          when "VAT"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabarq, la_cpa[l_i].blob[3].pabtext
          when "VAV"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabarq, la_cpa[l_i].blob[3].pabvarchar
          when "VTA"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabtext, la_cpa[l_i].blob[3].pabarq
          when "VTT"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabtext, la_cpa[l_i].blob[3].pabtext
          when "VTV"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabtext, la_cpa[l_i].blob[3].pabvarchar
          when "VVA"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabvarchar, la_cpa[l_i].blob[3].pabarq
          when "VVT"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabvarchar, la_cpa[l_i].blob[3].pabtext
          when "VVV"
            execute com_sql using la_cpa[l_i].blob[1].pabvarchar, la_cpa[l_i].blob[2].pabvarchar, la_cpa[l_i].blob[3].pabvarchar
          otherwise
            let gg_erro = true
        end case

Well, finally, the code is working! :D

The developer must call the function instead of executing the sql query. Has made some tests, all good, and we are changing our programs one by one to use this function.

Everything is ok, my only question was if there is some better way to do this instead of using this case.

I have a similar need.
I have to write a single SELECT statement for all the table of my database. The SELECT is by SERIAL fields.
I am able to generate the SELECT instruction. The problem is the FETCH.
The problem is how to get the value of all table fields in one single statements. Normally I write SELECT * INTO var_table_like.* FROM table.  What kind of variable I have to use?

It could be useful an element like recordset in java where the access to a value of a field is by a function with the position or the name of the field.
I would like to avoid to use Java interface for performance reason and to avoid to manage 2 kind of database connections for every installation.

Marco,

If i get you right, maybe you can use a record of strings to solve this. String will accept either number and text value, so whatever comes from your table string will accept, expect for byte values.

Marco,

Have a look at my fgl_zoom example here https://code.google.com/p/sourcefourjs/wiki/fgl_zoom

Its a generic zoom / query / lookup window function which evolved from query_window which my previous employers placed in the IIUG software repository many years ago.  It essentially does ...

DEFINE l_result ARRAY[9] OF VARCHAR(255)
...
   FOREACH list_curs INTO l_result[1], l_result[2], l_result[3],
                          l_result[4], l_result[5], l_result[6],
                          l_result[7], l_result[8], l_result[9]

... so determine an arbitrary large number of columns you want to return, declare an arbitrary large type of VARCHAR(255), and fetch into them.  (When STRING can be used in SQL statements, I'll rewrite to use STRINGS).

There maybe some techniques you can use if you are prepared to go down that path.  It is similar to what Marco suggested.

It surprised me when I moved across to Fourjs and started looking at other peoples code, how many did not have something similar.  I'd guestimate that some zoom / query / lookup code was making up 25% of the code base/number of forms, which if they had created a generic function, not only would 25% of the code be eliminated but the user interface would be consistent, and changes would only require changing in one place.  One day I'd still like to create a similar generic single table maintenance function and knock another 25% of some code bases.

QuoteIt could be useful an element like recordset in java where the access to a value of a field is by a function with the position or the name of the field.

You can go close by serializing record as either XML or in 2.50 JSON, and using the XML or JSON methods to access by position or name.

What I think you would want as well as that, is the ability to define record variables at run-time, and then access by position or name of field so that you could write your generic ...

DEFINE var_table_like DYNAMIC RECORD

FOREACH cursor USING tablename INTO column_name, column_datatype -- loop through systables/syscolumns for tablename
   CALL var_table_like.addField(column_name, column_datatype)
END FOREACH

SELECT * INTO var_table_like.* FROM tablename

LET value = var_table_like.getValueByFieldname(column_name)
LET value = var_table_like.getValueByIndex(index)
etc


Reuben

I agree with Reuben, however, with a new "DESCRIBE" instruction:

DEFINE dynrec DYNAMIC RECORD
...
DECLARE c1 CURSOR FOR SELECT * FROM mytable
OPEN c1 USING ...
DESCRIBE c1 INTO dynrec
FETCH c1 INTO dynrec.*
...

FOREACH could implicitely describe the cursor into the dynamic record, so the code would be:

DEFINE dynrec DYNAMIC RECORD, i INT
...
DECLARE c1 CURSOR FOR SELECT * FROM mytable
FOREACH c1 INTO dynrec -- (No .* notation)
   FOR i=1 TO dynrec.getFieldCount()
       DISPLAY dynrec.getFieldName(i), "=", dynrec.getFieldValue()
   END FOR
END FOREACH

Seb

Thank for your answers. I have a generic function for zoom that is valid for all my table. That function has a limited number of exposed fields. What I need now is a generic function for all the record types of my database. And I have to manage 3 different DBMS (IBM Informix, Oracle and MS SQL Server). Waiting for the  DYNAMIC RECORD I will try following the example  using generic varchar variables.
My application is a REST service that queries my database and return the results in JSON format.

For such generic code, you may consider to use the .sch schema file to known the structure of your tables.
That's easier to query as DB vendor specific system catalog tables.
Of course the .sch file must be up to date with the DB.
See also FGLDIR/src/fgldbslib.4gl, but this code is not documented and can change in future versions.
Seb

I for one would certainly love to see DESCRIBE/dynamic variables added to Genero BDL! :-) That's something we have been after for a long time now.  I would also like to see more new language features added, particularly anything that promotes code reuse...