Four Js Development Tools Forum

Hello,
 look this sample code, i don't see what is wrong ...

MAIN
  CONNECT TO "db@server" AS "db"
  CALL testcursor_1()   -- doesn't work
  CALL testcursor_2()   -- work
  EXIT PROGRAM
END MAIN

FUNCTION testcursor_1()
  DEFINE numcli STRING
  DEFINE nomcli STRING = '?'
  DEFINE cussup STRING

  LET numcli = '8905'
  DECLARE ctest1 CURSOR FOR
    SELECT custname FROM fdecus WHERE custnr=$numcli AND cussupcd=?
  TRY
    LET cussup = 'C'
    OPEN ctest1 USING cussup  # SQL ERROR -254 ?
    FETCH ctest1 INTO nomcli
    CLOSE ctest1
    FREE ctest1
    DISPLAY "TESTCURSOR_1 SUCCESS : " || numcli.trim() || ' is ' || nomcli
  CATCH
    DISPLAY "TESTCURSOR_1 CATCH ERROR : " || err_get(status)
  END TRY
END FUNCTION

FUNCTION testcursor_2()
  DEFINE numcli STRING
  DEFINE nomcli STRING = '?'
  DEFINE cussup STRING
  DEFINE rq     STRING

  LET numcli = '8905'
  LET rq = 'SELECT custname FROM fdecus WHERE custnr="' || numcli.trim() || '" AND cussupcd=?'
  DECLARE ctest2 CURSOR FROM rq
  LET cussup = 'C'
  TRY
    OPEN ctest2 USING cussup  # SQL ERROR -254 ?
    FETCH ctest2 INTO nomcli
    CLOSE ctest2
    FREE ctest2
    DISPLAY "TESTCURSOR_2 SUCCESS : " || numcli.trim() || ' is ' || nomcli
  CATCH
    DISPLAY "TESTCURSOR_2 CATCH ERROR : " || err_get(status)
  END TRY
END FUNCTION

*** Debugging 'testcursor' ***
TESTCURSOR_1 CATCH ERROR : Too many or too few host variables given.

TESTCURSOR_2 SUCCESS : 8905 is GARCIA BENJAMIN                         
Program exited normally.
*** Execution of 'testcursor' finished. Exit code: 0 ***

Hello,

In your first cursor, as you specify SQL parameters in the OPEN, you must specify all parameters:

  DECLARE ctest1 CURSOR FOR
    SELECT custname FROM fdecus WHERE custnr=$numcli AND cussupcd=?
   ...
    OPEN ctest1 USING cussup  # SQL ERROR -254 ?
 

Try with:

  DECLARE ctest1 CURSOR FOR
    SELECT custname FROM fdecus WHERE custnr=? AND cussupcd=?
   ...
    OPEN ctest1 USING numcli, cussup
 

As a general advice, I recommend to not use program variables (especially local function variables) in DECLARE CURSOR

I do always use ? placeholders, and specify the actual value when doing the
 EXECUTE stmt USING var-list
 OPEN curs USING var-list
 FOREACH curs USING var-list

Understand that it's when executing the SQL at OPEN / EXECUTE / FOREACH, that the SQL parameter values are really used.

With variables provided in DECLARE CURSOR static SQL, the runtime has to remind the variables used in the cursor declaration, to apply the values at OPEN/FOREACH/EXECUTE time...

This is known I4GL / Genero feature with cursors, but I don't like to use it.

Seb

BTW:

In you second cursor, why do you concatenate the numcli parameter value?

LET rq = 'SELECT custname FROM fdecus WHERE custnr="' || numcli.trim() || '" AND cussupcd=?'
 

Instead of using 2 ? place holders:

LET rq = 'SELECT custname FROM fdecus WHERE custnr=? AND cussupcd=?'
 

Your current code is subject of SQL injection, if you don't control numcli content, and is not SQL standard because you are using " double quotes as string delimiters: You should use single quotes.

What is the SQL data type of the custnr column? "nr" sounds like it's a number?
If this is an INTEGER you should use INTEGER variables, not STRING.

Seb

@Benjamin We do have the "strange behaviour" documented, see the 3rd sentence of the "Important note" here https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_result_sets_DECLARE.html


To also add understanding, look at the FGLSQLDEBUG of a static SQL statement with a host variable.

For instance

LET numcli = '8905'
  SELECT custname FROM fdecus WHERE custnr=$numcli AND cussupcd='C'

yields in FGLSQLDEBUG, note the host variable is a parameter and passed using using ...

SQL: SELECT custname FROM fdecus WHERE custnr = ? AND cussupcd = 'C'
 | 4gl source      : play.4gl line=33
 | ../ODI_common.h:00829(3)  : adaptStatement: stmt type = 1
 | ../ODI_common.h:00834(3)  : adaptStatement: ifxemul = 1
 | sqt.c:00861(3)  : Nat stmt1 = select custname from   fdecus where custnr = ? AND cussupcd = 'C'
 | sqlcode         : 100
 |   sqlerrd3      : 0
 | curr driver     : ident='dbmsqt'
 | curr connection : ident=':memory:' (dbspec=[:memory:])
 | using: 1
 |  t:                        STRING f:01 v:"8905"
 | Timestamp       : 2024-10-11 09:48:44.17306
 | Execution time  :         0 00:00:00.00007

@Seb - perhaps we can improve the visibility of that note

Hi,

i didn't see that note ...
maybe better put that warning here : https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_static_sql_003.html

Regards

Hi,

OK I will add the same warning note in the page

https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_static_sql_003.html

Seb