next »

[Juan Pedro M.]

Hello,
I have the following SQL query that works in Postgres 9.4 using pgAdmin or psql

Code

SELECT * 
                FROM avrr_mroutecost (
                          array[('A1',(-13636312,5710537)),
                                   ('ABT1',(-13634114,5695273)),
                                   ('ABT10',(-13633029,5704870))]::avrr_unit[],
                          (-13643477,5699713),
                          '9920 SE BUSH ST',
                          500,
                          4000,
                          'CAD',
                          'CAD')

When I put that query in a Genero 3.0 program like this:

Code

MAIN
  DEFINE my_query STRING
  CONNECT TO "gisdb"
 
 LET my_query =  "SELECT * FROM avrr_mroutecost ",
                   "( array[('A1',(-13636312,5710537)),",
                   "        ('ABT1',(-13634114,5695273)),",
                   "        ('ABT10',(-13633029,5704870))]::avrr_unit[],",
                   "(-13643477,5699713),",
                   "'9920 SE BUSH ST',500,4000,'CAD','CAD')"
  EXECUTE IMMEDIATE my_query
END MAIN
 

The program fails with the following error:
    Program stopped at 'gis.4gl', line number 13.
    SQL statement error number -201 (-1).
    syntax error at or near "from"

Running the program with FGLSQLDEBUG set I see the query gets converted to:
 

Code

4gl source      : gis.4gl line=13
 | pgs.c:01513(3)  : Nat stmt1 = declare scu1 cursor WITH hold FOR SELECT * FROM   avrr_mroutecost ( substring(array FROM ('A1' FOR ((-13636312,5710537)),        ('ABT1',(-13634114,5695273)),        ('ABT10',(-13633029,5704870)))-(('A1')+1)::substring(avrr_unit FROM  FOR 1),(-13643477,5699713),'9920 SE BUSH ST',500,4000,'CAD','CAD')
 

Looks like the [] gets converted to substring by the ODI. Is there anyway to get around this and have the ODI not modify the query?

Thanks

Juan Pedro Martinez

[Bernard M.]

Hello,

You can disable specific Informix SQL translations with FGLPROFILE entries.

For column subscripts like ident[start,end]:

Code

dbi.database.mydb.ifxemul.colsubs = false

See http://4js.com/online_documentation/fjs-fgl-manual-html/#c_fgl_Connections_015.html

In the next Genero BDL version, it will be possible to disable Informix SQL translations for a given SQL statement, by using special SQL comments:

Code

PREPARE s1 FROM "/* fglhint_no_ifxemul */ SELECT ... "

Regards,
Bernard

[Reuben B.]

I do wonder sometimes if there are developers unaware of this feature to turn on/off the emulations, and if they would get a small performance improvement by setting

Code

dbi.database.[i]mydb[/i].ifxemul = false

to turn off all the emulations.

[Sebastien F.]

Indeed, if your programs uses no Informix specific SQL features at all (i.e. uses only native SQL syntax of the target database like PostgreSQL), you want to globally disable Informix emulations with ifxemul=false.

Keep in mind that ALL emulations would be disabled, including data type name conversions (in CREATE TABLE, etc), temporary tables, serials, double-quotes strings ... (see doc for details).

There will be a slight improvement for SQL execution time, since SQL statements will no longer go through the SQL translator (at PREPARE/DECLARE time). But most important, is that you can use all native SQL syntax without problem.

You should however consider to use simple portable SQL, to keep our code independent from the type of database. If special SQL needs to be used such as PostgreSQL arrays, you can encapsulate DB specific syntax in a FUNCTION, which can then be adapted when using another database server type.

Seb

[Juan Pedro M.]

Thanks the colsubs should work.  We will review if we can encapsulate into a function in case we need to support other databases in the future.
I knew about the ifxemul , but we cannot use it because the application relies on it for some things like temporary tables and serials. While searching through the documentation I missed the colsubs or I did not correlate the error we were getting with that setting.

Thanks

Juan Pedro Martinez