Hi John,
A few pointers:
1. Is there a reason why there are two SmartSQLEx actions? The first one should be sufficient.
2. I don't think the Smart Parameter as it is currently configured in the SmartSQLEx action will work. There needs to be a "concatenate" operator between the = and the smart parameter, like this:
a WHERE upi=+@P.1234"
This will return the contents of the Page Level Variable with the name 1234. If you look at the RRS log after having run the Ruleset you'll see the resulting SQL Query and any data that it returns.
3. The full-stop in the PopulateWithResult action should be a comma like:
- PopulateWithResult("1,FALSE")
I hope this helps...
------------------------------
Shaun McDowall
------------------------------
Original Message:
Sent: Tue September 14, 2021 01:08 PM
From: John Morgan
Subject: Datacap and DB2
Due to PII in the ruleset I cannot share a screenshot but I can share the ruleset with the PII replaced
PopulateFirstName
Function 1
- ClearErrorMsg()
- CloseConnection()
- OpenConnection ("@APPVAR(lookupdb:cs")
- SmartSQLEx ("SELECT fname FROM TABLE a WHERE upi=@P.1234". false "")
- SmartSQLEx ("SELECT fname FROM TABLE a WHERE upi=@P.1234". false "")
- PopulateWithResult("1.FALSE")
- CloseConnection()
ValidateFirstName
Function 1
- Status_Preserve_ON ()
- IsFieldEmpty("First Name")
Function 2
- Status_Preserve_OFF ()
- SetIsOverrideable("False')
- AllowOnlyChars("ABCD")
- IsFieldLengthMax("25")
- IsFieldLengthMin("25")
Function 3
- AddToErrorMsg("Name is to long, turncating")
Hopefully that is enough information but if not please let me know as we have been fighting this for a while.
------------------------------
John Morgan
Original Message:
Sent: Tue September 14, 2021 03:54 AM
From: Shaun McDowall
Subject: Datacap and DB2
Hi John,
This kind of activity is bread and butter for Datacap. If you execute the SQL query using either ExecuteSQL() or ExecuteSQLEx(), you should be able to use the PopulateWithResult() action to populate a given field with the result of the query. Once the field has been populated, the value should persist.
Please could you share screenshots of your Ruleset?
------------------------------
Shaun McDowall
Original Message:
Sent: Mon September 13, 2021 11:54 AM
From: DAVID Jenness
Subject: Datacap and DB2
Great question, John. That's what this group is for. OK, folks, what have you got for John?
------------------------------
DAVID Jenness
Original Message:
Sent: Fri September 10, 2021 01:30 PM
From: John Morgan
Subject: Datacap and DB2
Is anyone in this group using Datacap and DB2? I.e. Using datacap for forms scanning but pulling information to populate Datacap fields from DB2? The scenario we are trying to get working is as follows:
- Worker scans in a document with a QR code which contains a unique identifier (UPI)
- Datacap reads the UPI and runs an SQL query to retrieve specific information (First Name, Last Name, DOB, etc)
- The relevant fields are populated in Datacap
So far we have not found a way to populate the field. We have successfully queried the database and gotten information back but can not get the information to persist through the various different queues.
Any help or suggestions would be greatly appreciated as so far IBM PMR people have not been able to help us.
TIA
------------------------------
John Morgan
------------------------------