Hi all,
Maybe it’s a stupid question, but I haven’t any idea about how to acces to the value of an element from a record variable. I want to access it from a execSQL call in $dbParamNames parameter.
Thanks in advance
Two ways
- map the value to a variable and then use the new variable.
- substitute the value of the variable from the record using
%orders/body/OrderNumber% in the pipeline.
Good Luck
Hi, jnco.
When using pub.db:execSQL, the $dbParamNames paramter is optional.
The cleanest way to map variables to SQL statements is use variable substitution.
In the pub.db:execSQL step, select the $dbSQL variable and then select the “Set Value” icon. The “Set Value” icon is the blue arrow shaped like an “L”.
Right-click the text field and select “Use Larger Editor”. In the new, larger text box, type your SQL statement. It may help to start with REAL values to keep perspective on the complete SQL statement.
Next, replace the real values with substituted values. Remember that when this step is reached within your Flow, there will be pipeline objects and you want to use those pipeline objects in your SQL statement.
You indicated that there are elements in your Record. The root name of the Record begins the path to your variable. For example, if your Record name is “rec_customer”, the record path beings with “rec_customer/”.
Slashes separate levels of a Record. You can, therefore, identify an element by its complete path. “rec_customer/name/first_name” is a potential path to the variable “first_name”.
The easiest way to get the proper path is to step through a service using F7. At any point, select the “Results” tab and locate an element within a record. In selecting the element on the Record tree, you can copy its path using CTRL-C.
CTRL-V will paste the path somewhere. In the instance, paste the variable into the SQL statement, surrounded by %. The “%” signals the server to substitute to value of the variable at the indicated path. Your element may resemble %rec_customer/name/first_name%.
The variable substitution can be applied across the entire SQL statement in as many places as relevant.
After completing the SQL statement, select “OK” to close the large editor.
Be sure to select “Perform Variable Substitution” in the “Input for $dbSQL” box.
Click “OK” and everything is ready to go.
Test your service and confirm that the expected result set is returned. The result set will be returned in the results Record List.
Use [url=“wmusers.com”]wmusers.com to read up on extracting values from the results Record List.
There may be a different issue here which prompted the original question. I’m running into it as well.
I usually do use variable substitution in the $dbSQL variable as Dan suggests. But if I need to specify the JDBC type (inserting a UNID) I use the $dbParamNames and $dbParamTypes variables as well.
The issue is that the $dbParamNames doesn’t accept a variable if it’s part of a record structure.
So it will accept the following:
PositionID
But not:
Position/PositionID
Variable substitution doesn’t come into play here because $dbParamNames expects the name of the variable, not the value.
So Vinod’s first suggestion is the only way I can find to get this to work. Any ideas?
I’ve also just come across the dbParamNames bug. Whilst I can understand wM not fixing this minor bug in over 2 years, it seems a little poor that they haven’t updated the documentation to reflect this, so saving people like me time in figuring out what’s wrong.