Insert image into SQL Server 2008 database

One Star

Insert image into SQL Server 2008 database

I have a requirement to insert .jpg pictures into a SQL Server 2008 database table. The column in the table is defined as Image and the picture is a string.
I have had to connect to the database using ODBC as Talend doesn't seem to like a server name in the format AAASQLSERVER/ADU2008
However I am unable to insert the picture as it fails with the error Operand type clash: text is incompatible with image.
Any idea on how to insert an image?
Seven Stars

Re: Insert image into SQL Server 2008 database

If you want to connect to a named instance of a MSSQL server you need to add the instance name in the jdbc additional parameters field as "instance=myInstance;" As for the data type, images are binary and contain non-printable characters and are incompatible with the string data type (unless you base64 encode the entire image) Try instead using a Byte array (Byte[]) which can hold raw or binary data.

HTH,
Josh
One Star

Re: Insert image into SQL Server 2008 database

Thanks for that. I can connect ok now and put data into the image field. But now I have a problem with data translation. The image data that's appearing in the database seems to have had all the bytes in the range x80 - x9f changed to x3f. I can't find any encoding either for storing or retrieving the data correctly.
If I just read the file in and output it to a new file it's ok so it's something in the storing on database that's the problem.
I'm using TIS 3.1.4.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now