topic Re: Store a file in a varbinary DB field of sql server in Talend Studio

Store a file in a varbinary DB field of sql server

nivedhitha — Wed, 06 Mar 2019 22:07:41 GMT

Hi everyone,

I have a table with schema as shown below

I have to save my source file(.xlsx) in the Mapping_Document_File field. Please provide me suggestions as to how to achieve this

Re: Store a file in a varbinary DB field of sql server

DataTeam1 — Wed, 06 Mar 2019 22:26:35 GMT

Here you can see an example how to achieve this

Inserting BLOB or CLOB data into a database

Re: Store a file in a varbinary DB field of sql server

Anonymous — Thu, 07 Mar 2019 04:12:04 GMT

Hi,

While you can load the file to the database, why didn't you question the designer for following this approach? Now, we are having modern cloud storage facilities like S3 where we can store the files in most secured fashion with minimum cost. Ideally it should be the approach instead of pushing the files inside DB.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Store a file in a varbinary DB field of sql server

nivedhitha — Thu, 07 Mar 2019 16:20:20 GMT

Hi @DataTeam ,

Thanks for your reply.

I tried doing it but what gets inserted into the table is a sequence of strings rather than my xlsx file.

The datatype of the field in my SQL Server table is Varbinary.. and if I import the schema in the Dboutput component, the type is object and the datatype is varbinary. so I copied the same schema to the output of tMap and followed the same steps as the link you provided.

And the output is

Attaching the screenshots of tmap and dboutput

Schema as imported by dboutput component

tmap

Please let me know what is the issue here.

thanks in advance

Re: Store a file in a varbinary DB field of sql server

nivedhitha — Tue, 19 Mar 2019 15:11:10 GMT

Hi,

Does someone have any suggestion for me?

Appreciate any inputs.

Thanks

Re: Store a file in a varbinary DB field of sql server

DataTeam1 — Wed, 20 Mar 2019 08:29:32 GMT

@nivedhitha I promise come back to your issue next week because now I have many other task to do in my work.

Until then maybe @nthampi can help you.

Re: Store a file in a varbinary DB field of sql server

nivedhitha — Thu, 21 Mar 2019 21:40:32 GMT

@DataTeam ,

Thank you. Looking forward to your suggestion.

Re: Store a file in a varbinary DB field of sql server

DataTeam1 — Fri, 29 Mar 2019 22:33:17 GMT

Hi @nivedhitha sorry for my absece.

Write me what do you want to store in database.

I suppose you want to have xml string (content of xml file) instead of xml as binary format.

Re: Store a file in a varbinary DB field of sql server

nivedhitha — Wed, 03 Apr 2019 04:27:10 GMT

@DataTeam ,

Thanks for getting back.

Yeah right I want the file itself to be saved onto database. But my routine now just inserts a string.

Any suggestions/corrections?