how to insert file in database(using clob or blob data type) using SAP XI/PI ?

[former_member197830]

Hi All,

Can any one explain me how insert binary files(.txt,.csv,.pdf) files in database using SAP PI.

Thank you,

Narasaiah T.

[Former Member · Accepted solution]

You may need to check the respective XSD data type for your SQL Data type. A quick search takes me here:

https://www.w3.org/2001/sw/rdb2rdf/wiki/Mapping_SQL_datatypes_to_XML_Schema_datatypes

you can use either : xsd:hexBinary or xsd:base64Binary for FileData field. FileName will be xsd:string.

Going by receiver structure for JDBC adapter for Stored procedure, you can create your ESR structure similar to

<StatementName5> <storedProcedureName action=” EXECUTE”>

<table>realStoredProcedureeName</table>

<FileName isInput=”true” type=varchar>fileName from dyn config</FileName>

<FileData isInput=”true” type=varbinary>hes tring created</FileData>

</storedProcedureName > </StatementName5>

So you can read the file in binary format and write the entire payload to the field in the java mapping mentioned in Vila's reply (https://blogs.sap.com/2010/03/28/sap-xipi-storing-binaries-images-pdfs-etc-in-the-database-blobs-usi.../) while forming the XML. You just have add file name tag to the XML.

[former_member197830]

Hi Aditya,

In above mention java code they didn't fill isInput and type attributes, is PI will take of those those things are where we can fill this attributes

do you any idea to add FileName field in target side using java mapping dynamic configuration?

Please help me....

Thank you,

Narasaiah T.

[former_member197830]

Hi All,

Below one is my stored procedure, then how to create receiver datatype(i.e stored procedure calling structure) if use above mentioned java mapping and above java mapping only for single field.

Thank you,

Narasaiah.T

[iaki_vila]

Hi Naraiah,

In PI 7.0 i had one scenario to read a PDF from an Oracle database, i remember, may be im wrong, i had to do nothing the PI read the binary field and in the monitoring i only notice a codification similar to base64 and the size the double that the real file. I'm sorry i cant check right now because we had to change it because we had a huge files and the PI couldn't deal with them.

REgards.

[iaki_vila]

Hi Narsaiah,

Have you checked this blog

https://blogs.sap.com/2010/03/28/sap-xipi-storing-binaries-images-pdfs-etc-in-the-database-blobs-usi...?

Regards.

[former_member197830]

Hi Vila,

Can you please suggest me what are the data type for sender(to select file name) and receiver type(to store images) ?

Thank you,

Narasaiah T