Livecycle: SQL Statement Info Editor

Question

I have a datatype in a table as uniqueidentifer. In the SQL statement Editor I'm trying to map my Xpath to the statement.

An Error is thrown: cannot cast uniqueidentifier to varchar. I have tried all the functions and data types I can think of.

ex EXEC SetSomeSP @ProcessID = {$/process_data/@ProcessID$}

I have tired with quotes, casting with functions and using parameterized query with no luck.

Any help would be great.

Answer 1

You may have more luck with an explicit conversion?

declare @procid varchar(36)
select @procid = cast({$/process_data/@ProcessID$} as varchar(36))
EXEC SetSomeSP @ProcessID = @procid

Answer 2

Make sure the UUID variable is a string variable in Livecycle then use the statement like
EXEC SetSomeSP @ProcessID = '{$/process_data/@ProcessID$}'

If this doesn't work, you can try recording the process, running it, and playing it back to check the value of your parameter before the Query is ran.

Also, you can use parameterized queries like
EXEC SetSomeSP @ProcessID = ? and check the box titled "Use Parameterized Query", then click the "+" button, and select String as the type, and your process variable as the value.
You can also enter a test value and click the "Test" button to get results back for the test value.