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: declare cursor with static variables ... strange behaviour  (Read 899 times)
Benjamin G.
Posts: 130


« on: October 10, 2024, 10:19:49 am »

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 ***
Sebastien F.
Four Js
Posts: 545


« Reply #1 on: October 10, 2024, 11:44:03 am »

Hello,

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

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

Try with:

Code
  1.  DECLARE ctest1 CURSOR FOR
  2.    SELECT custname FROM fdecus WHERE custnr=? AND cussupcd=?
  3.   ...
  4.    OPEN ctest1 USING numcli, cussup
  5.  

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
Sebastien F.
Four Js
Posts: 545


« Reply #2 on: October 10, 2024, 11:53:58 am »

BTW:

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

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

Instead of using 2 ? place holders:

Code
  1. LET rq = 'SELECT custname FROM fdecus WHERE custnr=? AND cussupcd=?'
  2.  

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
Reuben B.
Four Js
Posts: 1119


« Reply #3 on: October 10, 2024, 10:51:33 pm »

@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

Quote
... Do not mix ? SQL parameter placeholders with program variables: This would lead to a runtime error at OPEN or at FOREACH time, because the number of SQL parameters will not match the number of variable provided in the USING clause.

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

For instance

Code
  1. LET numcli = '8905'
  2.  SELECT custname FROM fdecus WHERE custnr=$numcli AND cussupcd='C'

yields in FGLSQLDEBUG, note the host variable is a parameter and passed using using ...
Code
  1. SQL: SELECT custname FROM fdecus WHERE custnr = ? AND cussupcd = 'C'
  2. | 4gl source      : play.4gl line=33
  3. | ../ODI_common.h:00829(3)  : adaptStatement: stmt type = 1
  4. | ../ODI_common.h:00834(3)  : adaptStatement: ifxemul = 1
  5. | sqt.c:00861(3)  : Nat stmt1 = select custname from   fdecus where custnr = ? AND cussupcd = 'C'
  6. | sqlcode         : 100
  7. |   sqlerrd3      : 0
  8. | curr driver     : ident='dbmsqt'
  9. | curr connection : ident=':memory:' (dbspec=[:memory:])
  10. | using: 1
  11. |  t:                        STRING f:01 v:"8905"
  12. | Timestamp       : 2024-10-11 09:48:44.17306
  13. | Execution time  :         0 00:00:00.00007



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


Product Consultant (Asia Pacific)
Developer Relations Manager (Worldwide)
Author of https://4js.com/ask-reuben
Contributor to https://github.com/FourjsGenero
Benjamin G.
Posts: 130


« Reply #4 on: October 11, 2024, 08:01:45 am »

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
Sebastien F.
Four Js
Posts: 545


« Reply #5 on: October 11, 2024, 08:17:57 am »

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

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines