我尝试在SQLRPGLE中使用预准备语句，但没有效果。
这是我的RPGLE程序。
* 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下面是连接后的语句。
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('?')我试着在iAccess SQL实用程序中手动运行它，用日期替换“？”，它工作正常。
然而，当我正常运行程序时，它不工作，并抛出SQLSTATE = 42601（发现调试）。
我试着去掉"？“周围的引号，但没有用。
更新1：
因此，我尝试使用EXECUTE IMMEDIATE语句而不是prepare/execute语句，只是将 date_deb 和 date_fin 变量的值连接起来。这没有帮助，但是我注意到这两个方法的SQLCA是相同的。这使我认为错误来自其他方面。
下面是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'我无法从中提取出任何有趣的内容，除了SQLERRD OF SQLCA(5) = 169，据说它代表PREPARE和EXECUTE语句中的列或错误位置，但我不知道如何使用它。
这是我在我的sql字符串以防万一。
....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 ' '我做错了什么？
1条答案
按热度按时间bq9c1y661#
最后，SQLCA找到了我的问题的答案。
这里的消息指出了我在查询中用来替换CONCAT的管道。这些管道可以处理预编译的SQL，但是由于某些原因，它们不能处理动态SQL...
顺便说一下，SQLERRD（5）确实给予了语法错误的位置。在我的例子
SQLERRD OF SQLCA(5) = 169中，它是字符串的第169个字符（管道）。更换所有的管道解决了问题，但我得到了一个新的错误。
这次是DB2 SQL错误：SQL代码= -418，SQL状态=42610
这一个来自我使用参数的方式，再次出于超出我所知的原因，这不工作
DATE(?)，但这做CAST(? AS date)。随着这两个错误的解决，我终于设法使我的程序工作。
如果有人对这种行为有解释，我完全有兴趣在评论中看到它！