8 Replies Latest reply on Sep 6, 2010 9:21 AM by user1188419

    double up quotes (apostrophe)

    user1188419
    user1188419 Sep 2, 2010 11:32 AM
      Hello

      I have a variable which contains a french string with a quote: L'experience....
      This string could come from XML-File, text file or error messages.
      How is it possible to double up the single quote to insert this string into a database?
      A simple insert fails because the database does not accept this:
      INSERT INTO xxx VALUES(#variable_name)

      I found a technical document for ODI: ID 867906.1 but this document does not explain how to double up.

      Any hints are appreciated.

      Regards

      Markus
        • 1. Re: double up quotes (apostrophe)
          774570
          774570 Sep 2, 2010 3:28 PM (in response to user1188419)
          Please try this way INSERT INTO XXX VALUES ('L''experience') SO make the french string as shown and try . Hope this helps.
          • 2. Re: double up quotes (apostrophe)
            user1188419
            user1188419 Sep 2, 2010 3:33 PM (in response to user1188419)
            Hello

            I know that I must add a 2nd quote for inserting into a database.
            But I am looking for a "tool way" to add this quote, because the string is coming from a query or other sources. This is not a manually retrieved string.

            Is there a way to do it by a Jython script or something similar to replace the single quote by two single quotes?

            Regards

            Markus
            • 3. Re: double up quotes (apostrophe)
              Ankit J
              Ankit J Sep 2, 2010 3:36 PM (in response to user1188419)
              Markus,

              You can use this INSERT statement inside a procedure.
              Create a procedure and use "Command on Target" pane. Select Oracle as Technology and relevant Schema.
              In the Command window, use this:
              <@ import lang; @>
<@ v_var="#variable_name".replace("'","''"); @>
insert into xxx values('<@ out.print(v_var);@>')
              Jython substitution methods are needed to replace one single quote with two single quotes.

              Another point to note is that - This ' symbol should not be in the XML file otherwise your XML file will be malformed.
              • 4. Re: double up quotes (apostrophe)
                user1188419
                user1188419 Sep 3, 2010 6:37 AM (in response to Ankit J)
                Good morning

                I tried this, but no success.
                He my procedure: (Target tab: Technology Teradata)
                <@ import lang; @>
                <@ v_var="#DEMO.input_test".replace("'","''"); @>
                insert into MB_Test.test values(5,'xxx''xxx') <--- here I tried it with fixed values

                And the error message:
                java.lang.Exception: BeanShell script error: Sourced file: inline evaluation of: `` import lang; out.print("\n") ; v_var="xxx'xxx ".replace("'"," . . . '' : reflection error: bsh.ReflectError: Method replace( java.lang.String, java.lang.String ) not found in class'java.lang.String' : at Line: 2 : in file: inline evaluation of: `` import lang; out.print("\n") ; v_var="xxx'xxx ".replace("'"," . . . '' : .replace ( "'" , "''" )
                BSF info: step1 at line: 0 column: columnNo

                     at com.sunopsis.dwg.codeinterpretor.a.a(a.java)

                ....

                I tried also in other procedures the replace function, but it never worked...
                Any help is appreciated.

                Markus
                If I run only the insert with variables
                • 5. Re: double up quotes (apostrophe)
                  783453
                  783453 Sep 3, 2010 11:52 AM (in response to user1188419)
                  Can you try with replace('Your Input Field', '' , '"') ?
                  • 6. Re: double up quotes (apostrophe)
                    user1188419
                    user1188419 Sep 3, 2010 1:42 PM (in response to 783453)
                    Hello

                    I tried a lot of combinations:

                    <@ v_var="#DEMO.input_test".replace("'","a"); @> --> Error

                    <@ v_var="#DEMO.input_test".replace('x','a'); @> --> is working, Error follows in the insert step (which is OK)

                    <@ v_var="#DEMO.input_test".replace(''','a'); @> -->Error:java.lang.Exception: BeanShell script error: Sourced file: inline evaluation of: `` import lang; out.print("\n") ; v_var="xxx'xxx ".replace(''',' . . . '' Token Parsing Error: Lexical error at line 2, column 45. Encountered: "\'" (39), after : "\'": <at unknown location>
                    BSF info: step1 at line: 0 column: columnNo

                    It seems that it is a problem to give as first value a ' and i was not able with: ''' "'" "\'" '\''
                    In all cases an error was following.

                    What does the experts recommend?

                    Have a nice weekend

                    Markus
                    • 7. Re: double up quotes (apostrophe)
                      Ankit J
                      Ankit J Sep 3, 2010 1:54 PM (in response to user1188419)
                      Your error says that this method wasnt found in java.lang.string.

                      Do you know what version of Jython you have ? This could be a version issue !
                      Mine is Jython 2.1 and Java 1.6 and I tried similar commands on Windows Command Prompt 
                      $ODI_HOME\oracledi\bin>jython
OracleDI: Starting Jython ...
A JDK is required to execute Web Services with OracleDI. You are currently using a JRE.
Jython 2.1 on java1.6.0_07 (JIT: null)
Type "copyright", "credits" or "license" for more information.
$ import java.lang
$ v_var="abc'something"
$ v_var1=v_var.replace("'","''")
$ print v_var1
abc''something
$ print v_var
abc'something
$ v_var1=v_var.replace("'","''",2)
$ print v_var1
abc''something
$ v_var1=v_var.replace("'","''",0)
$ print v_var1
abc'something
                      Try using v_var.replace("'","''",1) - This is an overloaded method that uses third parameter as number of occurrences that you want to replace.
                      Also, try using import java.lang instead of import lang
                      • 8. Re: double up quotes (apostrophe)
                        user1188419
                        user1188419 Sep 6, 2010 9:21 AM (in response to Ankit J)
                        Hello

                        Great information.
                        The solution is to do with "import java.lang"

                        Here's my code which is running:

                        <@ import java.lang; @>
                        <@ v_var="#DEMO.input_test".replace("'","''"); @>
                        insert into MB_Test.test values(#DEMO.input_test_1,'<@ out.print(v_var);@>')

                        This code inserts the following value: ha'llo (the vaslue was refreshed in the variable input_test

                        Have a nice week

                        Markus