insert into DB using SQLservice: cannot take text with single quotes:

[Former Community Member]

I'm using JDBC service Execute sql statement.

I will explain with simple code:

insert into mytable (id, desc) values (1, 'this is Client document');

This works perfect.

But if I have single quotes in the desc -

insert into mytable (id, desc) values (1, 'this is Client's document');

It's throwing error to stalled operation errors.

I know that in Oracle, to escape single quotes we write

insert into mytable (id, desc) values (1, 'this is Client''s document');

But, we cant control here in Adobe as the desc is coming from a form's field where the user enters it.

I'm guessing this could be a known problem & can anyone tell me the solution.

Thanks,

kc

[_Nith_ · Accepted solution]

Use parameterized query to avoid this issue.

insert into mytable (id, desc) values (?, ?);

The two parameters (? marks) can be replaced with actual values using XPATH Expression.

If you do this, you query will execute without throwing any error.

Nith

[Former Community Member]

Hi Nith,

Sorry for the delayed response.

Thanks a lot for helping me on this. Earlier, I directly referred the process variables in the query which I didnt realize that cause my problem.

For anyone's reference,

Bad practice:

INSERT INTO CREW_FEEDBACK_SENT

          (JOB_SEGMENT,

          JOB_COUNTRY  )

  VALUES

          (GSI_GENERAL_S.NEXTVAL,

          {$ /process_data/crew_feedback_input_form/object/data/xdp/datasets/data/form1/Details/jobsegment $},

          {$ /process_data/crew_feedback_input_form/object/data/xdp/datasets/data/form1/Details/jobregion $})

Good practice:

INSERT INTO CREW_FEEDBACK_SENT

  (JOB_SEGMENT,

  JOB_COUNTRY  )

  VALUES

  (GSI_GENERAL_S.NEXTVAL,

?,?)

use them in Parametrized query & that helps.

Regards,

Krishna