JDBC Receiver - What if table to be updated has rows with spec chars??

[andresousa]

Hello Guys,

I am working with JDBC receiver adapter and trying to insert rows into a table. I am having problems when trying to create a structure for an insert statement for this table in integration repository, as some rows of this table have special char.

<StatementName>

<dbTableName action=u201DINSERTu201D>

<table>realDbTableName</table>

<access>

<QF@DFI>val1</QF@DFI>

Here's the problem since I cannot create a data type which would represent an strucuture with a field containing '@".

</access>

Any ideas to overcome this issue? Any help will be greatly appreciated.

Regards,

Andre

[MichalKrawczyk · Accepted solution]

hi,

the easiest way to overcome this you can create a stored procedure which will update your table

or create a mirror table without @ inside in any fields

and then some job on oyur DB server that will copy once in a while

from mirrow table to the original one

Regards,

Michal Krawczyk

[andresousa · Accepted solution]

Hi Michal,

Yes, stored procedure is for sure the easiest approach, however this customer is somehow resistant to pull strings when it comes to their AS/400 database. So I was wondering if there were an (easy) way out through XI. If you have an idea for this, please, let me know, cause, as far as I know, that doesn't seem to be possible.

Thanks for your attention.

Regards,

Andre

[VijayKonam · Accepted solution]

If writing Java code is an option, you might want to write an adapter module in which you change the colum names with @ wherever you want just before the JDBC adapter calls the actual database.

VJ

[henrique_pinto · Accepted solution]

Maybe not even a custom Adapter Module is necessary.

I think a Java mapping which changes from something into the @ character would suffice.

Regards,

Henrique.

[andresousa · Accepted solution]

Hi Guys,

OK, I got your idea, plus the option of working with SQL_DML, because working this way, I don't have to specify the rows (insert into <table> values ('value1', 'value2'), so a quite simple UDF to build the SQL statement receiving the fields as parameters did the job.

My problem is solved, thanks again for your time and best regards.

Andre