RODA Gurus,
Can you please offer some suggestions as to why I am getting the error “DAPARAM_1 not found” when attempting to apply updates to a given dataset?
I experienced this in the past when the {WHERE} was not defined. This in not the case this time. In fact I still get the error whether I use DynamicWhere or not.
Please Advise
Regards,
Monte Carver
pls specify what platform you are using (.NET, Delphi, etc)
In this situation we are using Delphi Seattle with RODA 9.1.99.1273
can you show your DynamicWhere expression.
you can show it here or drop an email to support@
Sent via Email to easily save formatting. Can I provide anything else to better understand what I am missing?
you can put code into block with back quotes like :
``` some code ```
Here is the Dynamic Where, which in this case has no conditions other than 1=1:
'<?xml version="1.0"?><query xmlns="http://www.remobjects.com/schemas/dataabstract/queries/5.0" version="5.0"><where><binaryoperation operator="Equal"><constant type="Byte" null="0">1</constant><constant type="Byte" null="0">1</constant></binaryoperation></where></query>'
The DynamicWhere is against this query/Dataset
SELECT
MSTR.PLAN_FILING_GEY
, MSTR.PLAN_PRODUCT_GEY
, MSTR.FILING_NAME
, MSTR.FILING_MEMO
, MSTR.PLAN_ID
, MSTR.INS_OK_NBR
, MSTR.NETWORK_TYPE_LK
, MSTR.METAL_TYPE_LK
, MSTR.FILING_CROSSWALK_GEY
, MSTR.HP_ID
, MSTR.NETWORK_ID
, MSTR.FORMULARY_GEY
, MSTR.SERFF_SUBMIT_ID
, MSTR.USE_STATUS_LK
, MSTR.SORT_ORDER
, MSTR.CREATE_DATE
, MSTR.CREATE_USER_ID
, MSTR.MODIFY_DATE
, MSTR.MODIFY_USER_ID
, MSTR.AUDIT_LINK
, CAST(FX_DISPLAY_NAME(MSTR.MODIFY_USER_ID, 'DISPLAY') AS VARCHAR(200)) AS SALC_MODIFY_USER
, CAST(FX_DISPLAY_NAME(MSTR.CREATE_USER_ID, 'DISPLAY') AS VARCHAR2(200)) AS SALC_CREATE_USER
, BPROD.AMISYS_BUSINESS_UNIT AS SALC_BU
, LKM.LONG_DESC AS SALC_METAL_TYPE
, LKN.LONG_DESC AS SALC_NETWORK_TYPE
, LKL.LONG_DESC AS SALC_LOB
, LKP.LONG_DESC AS SALC_PRODUCT_TYPE
, LKG.LONG_DESC AS SALC_GROUP_TYPE
, BPROD.PRODUCT_ID AS SALC_PRODUCT_ID
, CW.FILING_NAME AS SALC_CW_FILING_NAME
, LKU.LONG_DESC AS SALC_USE_STATUS
, BPROD.IS_SHOP AS SALC_IS_SHOP
, BF.FORMULARY_ID AS SALC_FORMULARY_ID
FROM BENEFIT_FILING MSTR
LEFT OUTER JOIN WEBDESK.WD_LOOK_UP LKM
ON MSTR.METAL_TYPE_LK = LKM.LOOKUP_ID
AND LKM.FIELD_REFERENCE = 'BENEFIT_FILING.METAL_TYPE_LK'
LEFT OUTER JOIN WEBDESK.WD_LOOK_UP LKN
ON MSTR.NETWORK_TYPE_LK = LKN.LOOKUP_ID
AND LKN.FIELD_REFERENCE = 'BENEFIT_BUPG.NETWORK_TYPE_LK'
LEFT OUTER JOIN BENEFIT_PRODUCT BPROD
ON MSTR.PLAN_PRODUCT_GEY = BPROD.PLAN_PRODUCT_GEY
LEFT OUTER JOIN WEBDESK.WD_LOOK_UP LKG
ON BPROD.GROUP_TYPE_LK = LKG.LOOKUP_ID
AND LKG.FIELD_REFERENCE = 'BENEFIT_GROUP.GROUP_TYPE_LK'
LEFT OUTER JOIN WEBDESK.WD_LOOK_UP LKP
ON BPROD.PRODUCT_TYPE_LK = LKP.LOOKUP_ID
AND LKP.FIELD_REFERENCE = 'BENEFIT_BUPG.PRODUCT_TYPE_LK'
LEFT OUTER JOIN WEBDESK.WD_LOOK_UP LKL
ON BPROD.LOB_LK = LKL.LOOKUP_ID
AND LKL.FIELD_REFERENCE = 'BENEFIT_BUPG.LOB_LK'
LEFT OUTER JOIN BENEFIT_FILING_SPAN SPAN
ON MSTR.PLAN_FILING_GEY = SPAN.PLAN_FILING_GEY
LEFT OUTER JOIN BENEFIT_FILING_STATUS STATUS
ON MSTR.PLAN_FILING_GEY = STATUS.PLAN_FILING_GEY
LEFT OUTER JOIN BENEFIT_FILING CW
ON MSTR.FILING_CROSSWALK_GEY = CW.PLAN_FILING_GEY
LEFT OUTER JOIN WEBDESK.WD_LOOK_UP LKU
ON MSTR.USE_STATUS_LK = LKU.LOOKUP_ID
LEFT OUTER JOIN BENEFIT_FORMULARY BF
ON MSTR.FORMULARY_GEY = BF.FORMULARY_GEY
WHERE {WHERE}
ORDER BY BPROD.AMISYS_BUSINESS_UNIT, MSTR.PLAN_ID, LKN.SHORT_DESC
try to use Integer type in DynamicWhere instead of Byte.
btw, why you are using 1=1 in dynamic where ? it is used by default if DynamicWhere isn’t set
Also, there is a Business Processor bound to this DataTable with an “OnBeforeProcessChange” event, which follows:
procedure TSvcMnM.bpBNFilingBeforeProcessChange(Sender: TDABusinessProcessor; aChangeType: TDAChangeType; aChange: TDADeltaChange;
var ProcessChange: Boolean);
begin
try
if aChangeType = ctInsert then
begin
if aChange.NewValueByName['PLAN_FILING_GEY'] = Unassigned then
aChange.NewValueByName['PLAN_FILING_GEY'] := fxGetSmallGUID;
aChange.NewValueByName['AUDIT_LINK'] := fxGetSmallGUID;
end;
if aChangeType <> ctDelete then
begin
with aChange do
begin
if NewValueByName['FILING_NAME'] = Unassigned then
raise Exception.Create('<br><b>"Filing Name" is required.</b>')
else if NewValueByName['PLAN_PRODUCT_GEY'] = Unassigned then
raise Exception.Create('<br><b>"Plan Product" is required.</b>')
else if NewValueByName['PLAN_ID'] = Unassigned then
raise Exception.Create('<br><b>"Plan ID" is required.</b>')
else if NewValueByName['NETWORK_TYPE_LK'] = Unassigned then
raise Exception.Create('<br><b>"Network Type" is required.</b>')
else if NewValueByName['METAL_TYPE_LK'] = Unassigned then
raise Exception.Create('<br><b>"Metal Type" is required.</b>');
if fxIsDupFilingName(aChange) then
begin
raise Exception.Create('<br><b>The Filing: "' + NewValueByName['FILING_NAME'] + '" already exsits. Please check your parameters and try again.</b>');
end;
end; // aChange
ProcessChange := True;
end; // if aChangeType
except
// ReRaise and set ProcessChange = False
ProcessChange := False;
Raise;
end; // try
end;
I’ve been using the 1=1 approach in my DynamicWheres simply because it makes it easier to add additional conditions. This is especially true if other conditions are inserted as a loop. So I typically start out with:
//QUESTION: How do I make this "integer" vs "byte"
lWhere.Expression := lWhere.NewBinaryExpression(
lWhere.NewConstant(1)
, lWhere.NewConstant(1)
, dboEqual);
Then all my other conditions (IF USED) become something like:
if cbLOBQ.Val <> '' then
lWhere.Expression := lWhere.NewBinaryExpression(
lWhere.Expression
, lWhere.NewBinaryExpression('BPROD', 'LOB_LK', dboEqual, cbLOBQ.Val, datString)
, dboAnd);
But as I mentioned. I get this error without using DynamicWhere AT ALL !
DAPARAM_# name is used during processing of constants. we insert each constant into result SQL as parameter by security reasons.
you can use lWhere.NewConstant(1, datInteger).
you can use something like
procedure _AddExpression(aWhere: TDAWhereBuilder; aExpression: TDAWhereExpression);
begin
if aWhere.isEmpty then
aWhere.Expression := aExpression
else
aWhere.Expression := aWhere.NewBinaryExpression(aWhere.Expression, aExpression, dboAnd);
end;
...
// generate expressions in loop:
if cbLOBQ.Val <> '' then
_AddExpression(lWhere, lWhere.NewBinaryExpression('BPROD', 'LOB_LK', dboEqual, cbLOBQ.Val, datString));
have you cleared DynamicWhere before generation expressions with lWhere.Clear ?
Yes… I am doing:
var
lWhere : TDAWhereBuilder;
begin
with rDMMnM.cdsBNFiling do
begin
Active := False;
lWhere := DynamicWHere;
lWhere.Clear;
So… EvgenyK
Back to my original question. What causes the DA_PARAM error to surface? Is it ALWAYS associated with the use of a dynamic where?
I’ve been hoping this problem would solve itself. But alas, it still persists.
Thoughts
Regards,
Monte Carver
DA_PARAM name is used only during generation of constants.
constants always are passed as parameters by security reasons.
if you have errors with DA_PARAM, this means that you have dynamic where that uses constants.
I can suggest to set breakpoint at TDAEBaseDataset.DoSetActive (uDAEBaseDataset.pas) at
NativeDatabaseAccess.Active := True;
line and check generated SQL in debugger with Self.GetSQL command. also you can check parameters here
Thanks for the hints… got to the bottom of the issue. Had a query hidden in the business processor that was using a DynamicWhere without a {WHERE}