How to update a DB using a form in Workspace.

[Bibhu_Bikash_Na]

Hi,

I have designed a form which has several fields. I have created a table inside "adobe" schema (Installed through turnkey). I want o create process in such a way that after filling all the requried fields if the user in workspace clicks complete or submit the form then the corresponding columns in the DB should be updated.

Which services do I need? I know I need JDBC service and I guess some XML utilities for that. Can anybody suggest the same for me?

Thanks,

Bibhu.

[Former Community Member]

Hi Bibhu,

First of all I'm not sure it's a good idea to put your table in the adobe schema, I remember it's kind of deprecated ...

Anyway for your needs, you only need an xml variable containing your formData (as input variable, selected in the workspace endpoint) and the JDBC service "Execute SQL Statement". In the properties, you select the right database (IDP_DS is the adobe schema) and create your sql statement using xpath to get your form data values (insert a dynamic process data). It should be working

Hope this helps,

Regards,

Thomas

[Bibhu_Bikash_Na]

Hi Thomas,

I did the same much earlier but with no result.

Don't I need any other activity like "processFormSubmission" for extracting form data? Lets say I have a form which will be opened in the Workspace. The form will be in xdp format? As I know the form data would be of the format .xml. But How to extract the formdata? By just creating a variable would help?? I guess no. Don't we need any XSLT transformation?? How many activities do we need? Do we need a processData or the formdata to update the table in the database? By selecting formdata from the xpath selection and by clicking the test button it saya "Null values can not be inserted". But while doing the same in the actual form in the workspace and by clicking the submit button does not update the data.

Thanks,

Bibhu.

[Former Community Member]

If your form is an xdp based form created with LiveCycle Designer, you'll have the data already separated from the xdp file.  When you create your Workspace endpoint, you select a form and an xml variable to assign to the user. Here I affect the form NewForm1, and my xml data are stored in my variable formData (type xml, with the same xsd as my form), which is declared as an input variable.

What you need to do next is add a new task, using the JDBC Service "Execute SQL Statement". Edit your sql statement (update / insert / etc.) using the "Insert a dynamic process data" where you want to put a value from a field in the form. You can also use "parameterized query" to use parameter instead of direct values. You can read the documentation about the JDBC Execute SQL Statement service here : http://help.adobe.com/en_US/LiveCycle/9.5/WorkbenchHelp/WS92d06802c76abadb799416c212826bea627-8000.h...

To sum up, the user will fill up the form in the workspace, then click "Finish" or "Submit". Then the process will start, and the data typed by the user will be in formData (same data schema as your form). Then in the process, you execute a sql statement using values from formData.

Hope this is clear enough.

Regards,

Thomas

[Bibhu_Bikash_Na]

Hi Thomas,

I followed the same thing. For the output of the no.of rows affected in the SQL Statement I used an integer variable. My table has few columns. employee_id,date,designation,employee_name,evaluator_name,reporting_manager. I had created xsd schema and binded all those fields as per the data connection.

I used the following query.  INSERT INTO adobe.feedback_form VALUES ('{$ /process_data/formData/xdp/datasets/data $}').

While clicking test I again got the same error that "Can not issue NULL query." and the db is not getting updated by filling the form in workspace.

Where am I wrong?

Thanks,

Bibhu.

[Former Community Member]

Hi Bibhu,

Are you familiar with sql statements ?

You need to modify your statement to put all the values of each field, you need to write the complete sql statement, something like :

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

In your case it'll be something like :

INSERT INTO adobe.feedback_form ("employee_id", "date", ...) VALUES ('{$ /process_data/formData/xdp/datasets/data/employee_id $}', '{$process_data/formData/xdp/datasets/data/date $}', ...)

Regards,

Thomas

[Bibhu_Bikash_Na]

Hi Thomas,

Yes, I do have good hold over SQL. I tried this long before but with no luck. One thing I noticed. If I am using any other variable for storing the ouput rows and navigating to the same variable through xpath in the sql editor then while testing it's showing 1 row affected. Later when I test the DB it adds null values. But while doing with the formdata, it's showing can not issue null query.

Thanks,

Bibhu.

[Former Community Member]

Hi Bibhu,

I'm not sure I understand what your problem is.

1. When you record you process, if you playback the recording, do you have the correct values in your formData variable (the one that were entered through workspace) ?

2. If you test your SQL statement with hardcoded values, does it work ?

Thanks,

Thomas

[Bibhu_Bikash_Na]

Hi Thomas,

Yes I do have the correct values in the form data and hardcoded values are also getting inserted.

Thanks,

Bibhu.

[Former Community Member]

Hi Bibhu,

So the problem is inside your SQL statement if you have the correct values in formData and that hardcoded values works.

Could you send me your lca so I can take a look ? (I send you a private message with my address)

Regards,

Thomas

[Former Community Member]

Hi Bibhu,

Did you tried using parameterized query ? I just did a simple test by creating a table feedback_form with all the column as VARCHAR(50). I used this statement and it worked well:

Regards,

Thomas

[Bibhu_Bikash_Na]

Hi Thomas,

Yes, to my surprise parameterized query is working. While clicking the test button values are getting inserted into the DB. But while doing so in workspace in the actual form a blankrow only getting added into the table with no values. Again I am confused. What's really happening. Sent you the LCA filer inyour mail.

Thanks,

Bibhu.

[Former Community Member · Accepted solution]

Hi Bibhu,

I got it working. Here's what was "wrong". The asset reference for xml variable formData was the xsd schema and for some reason it was not working (I think the xpath was wrong, because you didn't check "store data as xdp"). So to be sure I put as asset reference your form NewForm1, so that "store data as xdp" is automatically checked:

Then I rearranged the sql statement according to this new xpath (don't forget to check "Use parameterized query"):

And it works well for me !

Hope it'll be ok on your side

Regards,

Thomas

[Bibhu_Bikash_Na]

Hi Thomas,

Again the same problem. A blank row is getting inserted in the DB if I use parameterised query and again the same error "Exception: Can not issue NULL query." if I use dynamic SQL statement. One more thing. What would be the submitted type of the PDF form? XDP or PDF? In both cases I am getting no result

Could you please give the LCA file?? Few hairs have been left on my head since I am scratching it since last 2 months.

Thanks,

Bibhu.

[Former Community Member]

Hi Bibhu,

To me, the submitted type should be xdp.

Can you paste your log file ?

I sent you back your lca file on your email ...

Regards,

Thomas

[Bibhu_Bikash_Na]

Hi Thomas,

That LCA file worked !!! Where I was wrong then??

Thanks,

Bibhu.

[Former Community Member]

Hi Bibhu,

Glad to hear that you got it working !

As I said in my previous post with the screenshots :

• I changed the import asset of the xml variable to the form (xdp file) instead of the xsd schema
• I changed the SQL statement to insert into adobe.feedback_form values (?,?,?,?,?,?)
• I checked "Use parameterized query"
• I changed the xpath expression of the parameters to match the xdp format

And that's all !

Regards,

Thomas