Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

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

Avatar

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

1 Accepted Solution

Avatar

Correct answer by
Level 10

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

View solution in original post

2 Replies

Avatar

Correct answer by
Level 10

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

Avatar

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