Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

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

Avatar

Level 5

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

0 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

Level 5

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