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?
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!