Insert image into SQL Server 2008 database

Highlighted
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?
Six 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.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.