2 Replies Latest reply on Feb 22, 2012 10:48 AM by kc_chaitu

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

    kc_chaitu Level 2

      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. Re: insert into DB using SQLservice: cannot take text with single quotes:
          $Nith$ Level 4

          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

          • 2. Re: insert into DB using SQLservice: cannot take text with single quotes:
            kc_chaitu Level 2

            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/jobseg ment $},

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

             

            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