RPGLE prepare statement throwing SQLSTATE=42601

Question

I'm trying to use prepared statements in SQLRPGLE but it's not working.

Here's my RPGLE program.

      * TR * 01/10/21 - CREATION
      **************************************************************************
      * Extraction RCPENT dans Excel
      **************************************************************************
     Dx1nrc            S              8s 0
     Dsql              S           1000a
     DwhereOrAnd       S              7a
     Doperator         S              4a
     Ddate_deb         S             10a
     Ddate_fin         S             10a
     D
      * Date de début
     DDAT_DEB          DS                  QUALIFIED
     DX1DEBA                   1      4  0
     DX1DEBM                   5      6  0
     DX1DEBJ                   7      8  0
      * Date de fin
     DDAT_FIN          DS                  QUALIFIED
     DX1FINA                   1      4  0
     DX1FINM                   5      6  0
     DX1FINJ                   7      8  0
     D
     D RCP1304         PR                  EXTPGM('RCP1304')
     D  ZPDEB                              LIKEDS(DAT_DEB)
     D  ZPFIN                              LIKEDS(DAT_FIN)
     D  ZPRAD                         1
     D  ZPCLO                         1
     D  ZPRET                         1
     D
     D RCP1304         PI
     D  ZPDEB                              LIKEDS(DAT_DEB)
     D  ZPFIN                              LIKEDS(DAT_FIN)
     D  ZPRAD                         1
     D  ZPCLO                         1
     D  ZPRET                         1
     C/exec sql
     C+ set option commit=*none,
     C+ datfmt=*iso
     C/end-exec
      *
      /Free

          sql = 'INSERT INTO XLSRCP +
          SELECT ''Adhérent'', ''Nom'', ''Date Création'', +
         ''Montant récup.'', ''Montant réglé'', +
         ''Cloturé'', ''Radié'' FROM SYSIBM.SYSDUMMY1 +
         UNION ALL +
         SELECT ADSTE || DIGITS(ADGRP) || DIGITS(ADIND), +
         ADNOM,  (DIGITS(RC1CRS) || +
         DIGITS(RC1CRA) || ''-'' || +
         DIGITS(RC1CRM) || ''-'' || +
         DIGITS(RC1CRJ)), CAST(RC1MRC as CHAR(7)), +
         CASE +
            WHEN cumul IS NULL THEN 0 +
            ELSE CAST(cumul as CHAR(7)) +
         END, +
         CASE +
            WHEN RC1CCM = 0 THEN ''Non soldé'' +
            ELSE ''Soldé'' +
         END, +
         CASE +
            WHEN ADRADM = 0 THEN ''Actif'' +
            ELSE ''Radié'' +
         END +
         FROM RCPENT +
         LEFT JOIN (SELECT SJSTE, SJGRP, SJIND, SUM(SJETM + SJEDEP) +
         as cumul FROM QS36F.SINATT +
         GROUP BY SJSTE, SJGRP, SJIND) +
         ON  SJSTE = RC1STE AND SJGRP = RC1GRP AND SJIND = RC1IND +
         JOIN ADHERE01 ON ADSTE = RC1STE +
         AND ADGRP = RC1GRP AND ADIND = RC1IND ';

        //*- Verification si plage de date renseignée
          if zpdeb.X1DEBM > 0 and zpfin.X1FINM > 0;

              date_deb = %editc(zpdeb.X1DEBA:'X') + '-' +
                         %editc(zpdeb.X1DEBM:'X') + '-' +
                         %editc(zpdeb.X1DEBJ:'X');


              date_fin = %editc(zpfin.X1FINA:'X') + '-' +
                         %editc(zpfin.X1FINM:'X') + '-' +
                         %editc(zpfin.X1FINJ:'X');

              sql = %trim(sql) + ' WHERE DATE((DIGITS(RC1CRS) || +
                           DIGITS(RC1CRA) || ''-'' || +
                           DIGITS(RC1CRM) || ''-'' || +
                           DIGITS(RC1CRJ))) +
                       BETWEEN DATE(''?'') AND DATE(''?'') ';

              *In80 = *On;

          endif;
        //*- Verification si on a parametré selon recup cloturée ou pas
          if ZPCLO <> ' ';

              whereOrAnd = ' WHERE ';
              operator = ' != ';

              if *In80;
                  whereOrAnd = ' AND ';
              endif;

              if ZPCLO = 'N';
                  operator = ' = ';
              endif;

              sql = %trim(sql) + whereOrAnd + 'RC1CCM' + operator + '0';
              *In80 = *On;

          endif;

          if ZPRAD <> ' ';

              whereOrAnd = ' WHERE ';
              operator = ' != ';

              if *In80;
                  whereOrAnd = ' AND ';
              endif;

              if ZPCLO = 'N';
                  operator = ' = ';
              endif;

              sql = %trim(sql) + whereOrAnd + 'ADRADM' + operator + '0';
              *In80 = *On;

          endif;

          exec sql prepare s1 from :sql;

          exec sql execute s1 using :date_deb, :date_fin;

      /end-free
     C                   Eval      *Inlr=*On

Here is what the statement looks like once concatenated.

SELECT 'Adhérent', 'Nom', 'Date Création',
 'Montant recup.', 'Montant réglé.', 'Cloturé', 'Radié' FROM SYSIBM.SYSDUMMY1 

UNION ALL 

SELECT ADSTE  || DIGITS(ADGRP) || DIGITS(ADIND), 
       ADNOM,  
       (DIGITS(RC1CRS) || 
        DIGITS(RC1CRA) || '-' || 
        DIGITS(RC1CRM) || '-' || DIGITS(RC1CRJ)), 
       CAST(RC1MRC as CHAR(7)), 
       CASE WHEN cumul IS NULL THEN '0' ELSE CAST(cumul as CHAR(7)) END,
       CASE WHEN RC1CCM = '0' THEN 'Non soldé' ELSE 'Soldé' END, 
       CASE WHEN ADRADM = '0' THEN 'Actif' ELSE 'Radié' END 
 FROM CGMF99.RCPENT 
 LEFT JOIN (SELECT SJSTE, SJGRP, SJIND, SUM(SJETM + SJEDEP) as cumul 
            FROM QS36F.SINATT 
            GROUP BY SJSTE, SJGRP, SJIND) 
   ON SJSTE = RC1STE AND SJGRP = RC1GRP AND SJIND = RC1IND 
 LEFT JOIN CGMF99.ADHERE01 
   ON ADSTE = RC1STE AND ADGRP = RC1GRP AND ADIND = RC1IND 
 WHERE DATE((DIGITS(RC1CRS) || 
             DIGITS(RC1CRA) || '-' || 
             DIGITS(RC1CRM) || '-' || 
             DIGITS(RC1CRJ))) BETWEEN DATE('?') AND DATE('?')

I tried running it in the iAccess SQL utility manually replacing the '?' with the dates and it works properly.

However when I run the program normally it doesn't work and throws SQLSTATE = 42601 (found out debugging).

I tried to remove the quotes around the '?' but it didn't help.

UPDATE 1 :

So instead of a prepare/execute statement I tried to use the EXECUTE IMMEDIATE statement, just concatenating the values of my date_deb and date_fin variables. It did not help, however I noticed that the SQLCA for both of these methods is the same. This makes me presume that the error comes from something else.

Here's what I have in SQLCA

EVAL SQLCA                         
SQLCAID OF SQLCA = 'SQLCA   '      
SQLAID OF SQLCA = 'SQLCA   '       
SQLABC OF SQLCA = 000000136.       
SQLCABC OF SQLCA = 136             
SQLCODE OF SQLCA = -104            
SQLCOD OF SQLCA = -000000104.      
SQLERRML OF SQLCA = 24             
SQLERL OF SQLCA = 0024.            
SQLERM OF SQLCA =                  
          ....5...10...15...20...25...30...3
     1   ' ?| ?+ - AS <IDENTIFIER> '
    61   '          '              
SQLERRMC OF SQLCA =       
          ....5...10...15...20...25...30...3
     1   ' ?| ?+ - AS <IDENTIFIER> '         
    61   '          '                       
SQLERRP OF SQLCA = 'QSQRPARS'               
SQLERP OF SQLCA = 'QSQRPARS'                
SQLER1 OF SQLCA = 000000000.                
SQLERRD OF SQLCA(1) = 0                     
SQLERRD OF SQLCA(2) = 0                     
SQLERRD OF SQLCA(3) = 0                     
SQLERRD OF SQLCA(4) = 0                     
SQLERRD OF SQLCA(5) = 169                   
SQLERRD OF SQLCA(6) = 0                     
SQLERR OF SQLCA = '                   z    '
SQLER2 OF SQLCA = 000000000.
SQLER3 OF SQLCA = 000000000.          
SQLER4 OF SQLCA = 000000000.          
SQLER5 OF SQLCA = 000000169.          
SQLER6 OF SQLCA = 000000000.          
SQLWRN OF SQLCA = '           '       
SQLWN0 OF SQLCA = ' '                 
SQLWARN OF SQLCA(1) = ' '             
SQLWARN OF SQLCA(2) = ' '             
SQLWARN OF SQLCA(3) = ' '             
SQLWARN OF SQLCA(4) = ' '             
SQLWARN OF SQLCA(5) = ' '             
SQLWARN OF SQLCA(6) = ' '             
SQLWARN OF SQLCA(7) = ' '             
SQLWARN OF SQLCA(8) = ' '             
SQLWARN OF SQLCA(9) = ' '            
SQLWARN OF SQLCA(10) = ' '           
SQLWARN OF SQLCA(11) = ' '           
SQLWN1 OF SQLCA = ' '                
SQLWN2 OF SQLCA = ' '                
SQLWN3 OF SQLCA = ' '                
SQLWN4 OF SQLCA = ' '                
SQLWN5 OF SQLCA = ' '                
SQLWN6 OF SQLCA = ' '                
SQLWN7 OF SQLCA = ' '                
SQLWN8 OF SQLCA = ' '                
SQLWN9 OF SQLCA = ' '                
SQLWNA OF SQLCA = ' '                
SQLSTATE OF SQLCA = '42601'

I can't pull anything interesting out of it, except maybe SQLERRD OF SQLCA(5) = 169 which is said to represent the column or the position of the error in PREPARE and EXECUTE statements, but I don't understand how to use it.

Here's what i have in my sql string just in case..

       ....5...10...15...20...25...30...35...40...45...50...55...60 
  1   'INSERT INTO CGMF99.XLSRCP SELECT 'Adhérent', 'Nom', 'Date Cr'
 61   'éation', 'Montant récup.', 'Montant réglé', 'Cloturé', 'Radi'
121   'é' FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT ADSTE || DIGITS(AD'
181   'GRP) || DIGITS(ADIND), ADNOM,  (DIGITS(RC1CRS) || DIGITS(RC1'
241   'CRA) || '-' || DIGITS(RC1CRM) || '-' || DIGITS(RC1CRJ)), CAS'
301   'T(RC1MRC as CHAR(7)), CASE WHEN cumul IS NULL THEN '0' ELSE '
361   'CAST(cumul as CHAR(7)) END, CASE WHEN RC1CCM = 0 THEN 'Non s'
421   'oldé' ELSE 'Soldé' END, CASE WHEN ADRADM = 0 THEN 'Actif' EL'
481   'SE 'Radié' END FROM CGMF99.RCPENT LEFT JOIN (SELECT SJSTE, S'
541   'JGRP, SJIND, SUM(SJETM + SJEDEP) as cumul FROM QS36F.SINATT '
601   'GROUP BY SJSTE, SJGRP, SJIND) ON  SJSTE = RC1STE AND SJGRP ='
661   ' RC1GRP AND SJIND = RC1IND JOIN CGMF99.ADHERE01 ON ADSTE = R' 
721   'C1STE AND ADGRP = RC1GRP AND ADIND = RC1IND WHERE DATE((DIGI' 
781   'TS(RC1CRS) || DIGITS(RC1CRA) || '-' || DIGITS(RC1CRM) || '-'' 
841   ' || DIGITS(RC1CRJ))) BETWEEN DATE(?) AND DATE(?)            ' 
901   '                                                            ' 
961   '                                        '

What am I doing wrong with this one?

When you removed the quotes around the `?` are you sure that's all you did? Sometimes it is the changes that we don't think will cause a problem that actually cause that problem. Make one change at a time, and then test the outcome. I believe that you need to remove the quotes for this to work though. Once you do that, try again and report the SQLSTATE, and the SQLCODE (multiple sql codes for this one sql state) — jmarkmurphy, Oct 04 '21 at 14:01
@jmarkmurphy Thank you for your reply. Yes, that's all I did at the time. I usually try to follow what you just said when i'm stuck, do one change at a time and check the result.. I updated the post fyi. — Ilias Timaev, Oct 04 '21 at 15:16

Ilias Timaev · Accepted Answer · 2021-10-04T16:20:36.033

In the end the SQLCA held the answer to my problem.

SQLERRMC OF SQLCA =       
          ....5...10...15...20...25...30...3
     1   ' ?| ?+ - AS <IDENTIFIER> '         
    61   '

The message here pointed out at the pipe which I used to replace CONCAT in my query. The pipes do work with pre-compiled SQL but for some reason they don't work with dynamic SQL...

By the way SQLERRD(5) does indeed give the position of the syntax error. In my case SQLERRD OF SQLCA(5) = 169 it was the 169th character of the string (the pipe).

Replacing ALL the pipes solved the problem, but I got a new error.

This time it was DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610

This one came from the way I use the parameters, once again for reasons beyond my knowledge this does not work DATE(?) but this do CAST(? AS date).

With both of these errors solved I finally managed to make my program work.

If anyone has an explanation for such behaviour I'm totally interested to see it in the comments!

I suspect the pipe `|` is not the problem, but the DATE(?) is. I found in the description for SQLCODE -418 that scalar functions do not allow parameter markers, but you can use CAST instead. The location of the error in SQL error messages is notoriously suspect. — jmarkmurphy, Oct 04 '21 at 16:47

RPGLE prepare statement throwing SQLSTATE=42601

1 Answers1