JDBC: Delete Table with non-XML-compatible characters

[Former Member]

Hello,

I want to delete a table using a JDBC receiver interface. According to the PI documentation, the element <table> is not defined for the "DELETE" action in the receiver XML SQL structure.

Problem is that the tablename to be deleted contains non-XML-compatible characters, so I cannot define the <dbTableName> element using the table name.

Any idea how to solve this issue?

Thank you!

[Former Member · Accepted solution]

Can you tell us what type of characters are coming?

You can write UDF to replace the characters accordingly.

You will find many examples on SDN.

Divyesh

[Former Member · Accepted solution]

Hello,

thank you for your answer. The name of the table contains dots ".".

How should an UDF be used to replace the name of XML tags? Still even it would be possible I assume that I would get an error in the JDBC receiver that the XML is not valid.

Maybe native SQL is an option to be considered....

[stefan_grube · Accepted solution]

Have you tried using the element "table"? The example in online help has no table node, but the description below does not say that you cannot use it for DELETE.

[baskar_gopalakrishnan2 · Accepted solution]

You can use the below syntax and avoid calling table name...

<StatementName6>

<anyName action= u201CSQL_DMLu201D>

<access>SQL-String with optional placeholder(s)</access>

<key>

<placeholder1>value1</placeholder1>

<placeholder2>value2<placeholder2>

</key>

</anyName >

</StatementName6>

Example:

access tag query string as "Delete customer where orderid='$placheholder1$' and order_name='$placeholder2$';

This should work for sure.

[Former Member · Accepted solution]

The SQL|DML approach seems to best approach. I will move forward testing it. Thank you!