Skip to Content
author's profile photo Former Member
Former Member

sql call single quote

Hi all,

I have created an flat to UDT scenario. After i get the file i want to put the data to UDT by sql call atom.

Some of the column has a single quote in the text.

What is the best to do that?

Kind regards,

Nico

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Apr 21, 2016 at 07:07 AM

    Hi Nico,

    please check the content help of the SQL call atom:

    Directly Entering the SQL Statement

    Directly enter the SQL statement or statements in the input field.

    Enter, for example, #select ItemCode, ItemName as 'Name' from OITM where ItemCode = 'A00003'

    If a value in an SQL statement contains a ‘ (single quote), for example, ’23’5’, the SQL statement does not interpret it correctly and only considers ’23’. The SQL call fails. To hand over the correct value, double the single quote: ’23’’5’

    Alternatively, use the utils2:handleSQLString(string()) function when providing the SQL statement in an XSL transformation atom. For more information about handling single quotes, see section 7 Preventing SQL Injections

    If a database contains special characters in tables that are not Latin, but for example Hebrew, use the N character in the WHERE clause of an SQL statement to include the special characters.

    Enter, for example:

    SELECT T2.E_MAIL as FromEmail FROM OUSR as T2 WHERE T2.USER_CODE=N'$userid'

    Entering an Atom Identifier

    Enter the name of an XSL transformation atom that contains the SQL statement or statements. Enter, for example, #atom4.

    In atom4, provide the SQL statement in the <sql> tag, for example:

    <sql xmlns="">select ItemCode, ItemName as &apos;Name&apos; from OITM where ItemCode = &apos;A00001&apos;</sql>

    The procedure also supports you in processing large SQL statements. For more information, see section 6 of the document

    Entering an XPath Statement

    Enter an XPath that points to the SQL statement in the XSL transformation atom.

    Enter, for example, /vpf:Msg/vpf:Body/vpf:Payload[./@id=&apos;atom4&apos;]/xpath

    The XPath points to the <xpath> tag in atom4 that contains the SQL statement. atom4 is an XSL transformation atom.

    In atom4, enter the SQL statement in the following way:

    <xpath xmlns="">select ItemCode, ItemName as &apos;Name&apos; from OITM where ItemCode = &apos;A00002&apos;</xpath>

    If values in an XPath statement contain a string that starts with ! (exclamation mark) and ends with _ (underscore), the integration platform (B1iP) interprets it as a variable and the SQL statement does not work. To avoid errors at runtime, do not enter the XPath statement in the input field. Instead, enter the transformation atom identifier, for example, #atom4. In the XSL transformation atom, enter the statement in the <sql> </sql> tag.

    To avoid out of memory errors at integration framework runtime, the integration framework in general reduces SQL statements to 1000 characters in the return payload.


    Invalid XML characters in database fields cause exceptions at integration framework runtime. To avoid such errors, do not enter the SQL statement in the input field, but use an XSL transformation atom. In the XSL transformation atom, you can use the filterInvalXMLChar=”true” attribute to filter invalid XML characters.

    Procedure

    1. Create an XSL Transformation atom in front of the Call SQL atom.

    2. in the XSL document, provide the attribute in the following way:

    <sql filterInvalXMLChar=”true” occ="1">select * from table</sql>

    3. In the SQL call atom, in the Default SQL Statement field, enter #atom<number> to reference the XSL transformation atom. <number> is the number of the XSL transformation atom that contains the SQL statement.

    Please handover your SQL statement using the 2nd or 3rd option taking function utils2:handleSQLString(string()) to handle parameters including special characters.

    Best regards

    Bastian

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.