AnsweredAssumed Answered

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

Question asked by Paul Duer on May 24, 2016
Latest reply on May 2, 2017 by Sabthami S

Like • Show 1 Like1
Comment • 4

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?

Reuben Fisher

May 24, 2016 1:28 PM

Correct Answer

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}’

For execution, we change it to ‘’ || ? || ‘’ and then execute a prepared statement
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:

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

See the reply in context

No one else had this question

Outcomes

Reuben Fisher

May 24, 2016 1:28 PM

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}’

For execution, we change it to ‘’ || ? || ‘’ and then execute a prepared statement
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:

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

2 people found this helpful

Actions

Paul Duer @ Reuben Fisher on May 24, 2016 1:34 PM

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!

Actions

Reuben Fisher

@ Paul Duer on May 26, 2016 12:12 AM

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?

Actions

Sabthami S @ Reuben Fisher on May 2, 2017 12:16 PM

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

Actions

Incoming Links

Re: 6.9.1 - How to handle form data including apostrophe

4 Replies

Reuben Fisher May 24, 2016 1:28 PM
Unmark Correct
Correct Answer
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
2 people found this helpful
Like • Show 0 Likes0
Actions
- Paul Duer @ Reuben Fisher on May 24, 2016 1:34 PM
  Mark Correct
  Correct Answer
  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!
  Like • Show 0 Likes0
  Actions
  - Reuben Fisher @ Paul Duer on May 26, 2016 12:12 AM
    Mark Correct
    Correct Answer
    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?
    Like • Show 0 Likes0
    Actions
    - Sabthami S @ Reuben Fisher on May 2, 2017 12:16 PM
      Mark Correct
      Correct Answer
      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
      Like • Show 0 Likes0
      Actions