How do single quotes work in SQL with patch 13 for 6.9.1?

[PaulDuer]

Okay, we have a request worflow with the SQL below as it's first node:

SELECT  NVL(supervisor_id,0) AS supervisor_id, CASE

  WHEN supervisor_id is null THEN '311'  ELSE ''

  END IS_SUP_UPDATED  from           T_MASTER_ENTERPRISE_USERS

  WHERE user_id = '${access_request_cri_meu_userId}'

Before patch 13, everything worked FINE. Now we get this error:

nested exception is: java.sql.SQLException: ORA-00933: SQL command not properly ended Error occurred in Statement #1 in Script ID 17:WPDS, Script Name = 'SQL Query', Script type = 'Action'. Job ID = 170625:WPDS, Process Ref = 'WF_RR_8900', Node Name = 'Null Supervisor', WorkItem = 1034344:WPDS:1

What are the NEW rules around single quotes?

We know that USER_ID is a NUMBER in the master users table. So WHY when I remove the single quotes from the where clause does it break?

Is it because of the VIA parameter right there? Will the SQL processor just plain not save that?

What gives?

[ReubenFisher · Accepted solution]

I believe the issue is that we now keep the quotes as literal quotes that are concatenated to the variable value.

So - If we start with ‘${x}’

1. For execution, we change it to ‘’ || ? || ‘’ and then execute a prepared statement
2. For design-time validation, we are executing a static sql statement so  we change it to ‘’ || 0 || ‘’

I am working on changing the processing as follows:

1. Remove the quotes around the variable name so they are ignored. This is the original intent of the sql.
   
2. Use a prepared statement for design-time validation

[PaulDuer]

Thanks Reuben, that does help a lot.

Looking at my original SQL, I *think* now that the '${access_request_cri_meu_userId}' variable is JUNK.

I don't think it exists! I think that our original DEV built this SQL and they got a result that was a FALSE positive!

Workpoint has been driving me nuts that it doesn't do compile time checking of types that they are defined and initialized.

I really want to just dump this all out to a C# based WebService where I can log out WHAT I got in!

[ReubenFisher]

I'm not up to speed on the workflow editor sql syntax and all of its peculiarities. But I'm working on it.
I am currently workng on a more comprehensive fix for several issued related to how we handle parameter names with prepared statements.

Glad this was helpful. Were you able to work around it?

[SabthamiS]

Hi Reuben,

I have a similar issue after the upgrade. Hope you can help on the below thread. I'm not sure which part of the SQL 

Query needs to be updated.Kindly help me on this.

Invalid column index - SQL Query Node 

Many thanks in advance !!!

Regards,

Sabthami Subramanian