topic Re: Insert image into SQL Server 2008 database in Talend Studio

Insert image into SQL Server 2008 database

janhess — Sat, 16 Nov 2024 13:30:40 GMT

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?

Re: Insert image into SQL Server 2008 database

Anonymous — Wed, 10 Mar 2010 21:27:51 GMT

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

Re: Insert image into SQL Server 2008 database

janhess — Thu, 11 Mar 2010 14:17:52 GMT

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.