Subscribe for automatic updates: RSS icon RSS

Login icon Sign in for full access | Help icon Help
Advanced search

Pages: [1]
  Reply  |  Print  
Author Topic: Postgres ODI with array[] operator  (Read 8780 times)
Juan Pedro M.
Posts: 7


« on: December 15, 2016, 07:01:20 pm »

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

Code
  1. SELECT *
  2.                FROM avrr_mroutecost (
  3.                          array[('A1',(-13636312,5710537)),
  4.                                   ('ABT1',(-13634114,5695273)),
  5.                                   ('ABT10',(-13633029,5704870))]::avrr_unit[],
  6.                          (-13643477,5699713),
  7.                          '9920 SE BUSH ST',
  8.                          500,
  9.                          4000,
  10.                          'CAD',
  11.                          'CAD')
When I put that query in a Genero 3.0 program like this:
Code
  1. MAIN
  2.  DEFINE my_query STRING
  3.  CONNECT TO "gisdb"
  4.  
  5. LET my_query =  "SELECT * FROM avrr_mroutecost ",
  6.                   "( array[('A1',(-13636312,5710537)),",
  7.                   "        ('ABT1',(-13634114,5695273)),",
  8.                   "        ('ABT10',(-13633029,5704870))]::avrr_unit[],",
  9.                   "(-13643477,5699713),",
  10.                   "'9920 SE BUSH ST',500,4000,'CAD','CAD')"
  11.  EXECUTE IMMEDIATE my_query
  12. END MAIN
  13.  

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
  1. 4gl source      : gis.4gl line=13
  2. | 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')
  3.  

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.
Four Js
Posts: 45


« Reply #1 on: December 16, 2016, 11:27:28 am »

Hello,

You can disable specific Informix SQL translations with FGLPROFILE entries.

For column subscripts like ident[start,end]:

Code
  1. 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
  1. PREPARE s1 FROM "/* fglhint_no_ifxemul */ SELECT ... "


Regards,
Bernard
Reuben B.
Four Js
Posts: 1047


« Reply #2 on: December 19, 2016, 06:14:36 am »

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
  1. dbi.database.[i]mydb[/i].ifxemul = false

to turn off all the emulations.

Product Consultant (Asia Pacific)
Developer Relations Manager (Worldwide)
Author of https://4js.com/ask-reuben
Contributor to https://github.com/FourjsGenero
Sebastien F.
Four Js
Posts: 509


« Reply #3 on: December 19, 2016, 08:47:11 am »

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.
Posts: 7


« Reply #4 on: December 19, 2016, 02:02:03 pm »

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
Pages: [1]
  Reply  |  Print  
 
Jump to:  

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines