Capture User Group

Expand all | Collapse all

Datacap and DB2

  • 1.  Datacap and DB2

    Posted 9 days ago
    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
    ------------------------------


  • 2.  RE: Datacap and DB2

    Community Leadership
    Posted 8 days ago
    Great question, John.  That's what this group is for.  OK, folks, what have you got for John?

    ------------------------------
    DAVID Jenness
    ------------------------------



  • 3.  RE: Datacap and DB2

    Posted 7 days ago
    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
    ------------------------------



  • 4.  RE: Datacap and DB2

    Posted 7 days ago
    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
    ------------------------------



  • 5.  RE: Datacap and DB2

    Posted 6 days ago
    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
    ------------------------------



  • 6.  RE: Datacap and DB2

    Posted 5 days ago
    Thank you for the response Shaun
    1. This is inherited code and my guess would be they were entering a variable in one of those (not the hard coded number) but changed it for some reason
    2. We are going to try that today and see the results (this was never a suggestion from IBM)
    3. That was a typo on my part


    ------------------------------
    John Morgan
    ------------------------------