How can I insert an image(JPG) into a SQL database?

[Ramsey H]

I have written an application to perform some tests in the lab.  After gathering the data I store it into a SQL Server.  I successfully managed to export all my raw numbers and calculations to the DB.  However I have not been able to export a screenshot of the Front panel.  I can write the screenshot to a JPG file just fine.  The problem is putting the image (binary data) into the DB.

So from what work I've managed to do, two questions come to mind:

1)  Is there a way to compress the screenshot, using standard JPG compression, without actually writing out to a file.  ie:  Just have a binary stream of the compressed image that I can write to the SQL database.

2)  Even if I don't use compression, I can't write any kind of data that appears as an image.  What kind of data do I need to be writing from LabView to store it properly in an 'image' or 'binary' type field in a SQL database?  I am not looking for answer "binary data."  Nor do I want the answer that is stated in the Help file for the DB Connectivity Toolkit(type - Data Cluster).  What I need is to know what kind of conversions I need to do on the image data that comes out of the FP.GetScaledImage.

I am using LabView 8.0 and have the Database connectivity toolkit.  We are running SQL Server 2000 and 2005.

[Rob_Calhoun]

> 1) Is there a way to compress the screenshot, using standard JPG compression, without actually writing out to a file.

Not that I know of since "Write JPEG file" take uncompressed data and writes to disk. (Export Image methods can also target the clipboard.) However, it is easy enough (and very fast) to write to a temp file, read it back and delete the temp file. The OS will cache all of this, so you'll not notice any performance hit.

> 2) Even if I don't use compression, I can't write any kind of data that appears as an image. What kind of data do I need to be writing from LabView to store it properly in an 'image' or 'binary' type field in a SQL database?

The 'image' datatype (deprecated by Microsoft; use varbinary() in new applications) is a binary field. In LabView, you store binary data as a string. You can write to any binary field in SQL (always allowed in the DB toolkit using the CommandText property) by writing the string out in hex with 0x in front. (No quotes, just Insert into myTable values (0x11223344....,...) ). I don't use the database toolkit but it may also support ADO's binary data methods, which would be more efficient. (see http://support.microsoft.com/kb/258038)

[Ramsey H]

This has been helpful.  I can now write images to the DB and retrieve them to be displayed in LabView.  I'm still working on the JPG thing, but I don't think it will be too difficult.  I will post what I figure out.

[MarkBlack]

Ramsey H,

I would like you to look at the following Developer Zone Article (Transfer Images Over the Network).  This article contains an example that describes a method of JPEG compression similar to what you require.  Unfortunately most of these examples require the NI Vision Module.  

[HWJ]

Hi,

found this thread when i was looking for help on this objective of inserting jpg files into SQL database. 

I'm using SQL Express Editiion 2005. I converted the JPEG file into binary string format and try to insert into the SQL database using the database connectivity toolkit. It works however limited to only 8000 bytes of binary string data. I receive this error message when i tried to insert more than 8k bytes.

Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver]String data, right truncation in Rec Create - Command.vi->Cmd Execute.vi->DB Tools Insert Data.vi->Test image file.vi

Can someone advice me what was wrong? and the right approach of using labview to insert the image file? How can I insert the entire JPEG file into the SQL database?

Is there an example program around that prove to be working?

 Appreciate greatly for any help given

[Mythilt]

What is the database datatype you are trying to write to?  Is the database value's datatype setup to accept more than 8k?

[HWJ]

hi, i set the datatype in the sql database as image which should be able to hold 0 - 2GB .. i tried changing it to varbinary (max)  and i received the same error message.

[Ramsey H]

I also hit this limit.  In the end I just wrote the JPGs to disk, and stored the file path in the DB.  Not the ideal solution, I know.

I'm trying to remember now, exactly what the problem was.  You're right to say the image data type should handle up to 2GB.  I think in the end, it was an issue of the LabView toolkit not playing well with newer versions of SQL Server, which have the image type.  Re-reading the error though, it sounds like it might be an issue with the ODBC driver.  FYI, 8KB used to be the max size for the largest types in SQL Server (I think binary in 2000).  I suspect this limit was coded into the driver or LabView, and can't handle the newer, larger types.

That being said, the LabView DB toolkit was either written poorly or written a long time ago.  It has issues with generating SQL queries that have the proper quotes.  For example, if your column names have spaces in them, the toolkit will be unable to generate a valid query, without some modification of the toolkit *.vi's.  Images in a database is not a new concept and not new to me.  I've never used a language where it was more complicated or difficult to get working.  Unless you absolutely have to, I'd work out a way to just store the JPGs to disk, with the path in the DB.  Spending too much time on this defeats the purpose of using LabView.

If NI decides to update the DB toolkit, then it might be worth giving it a try again.

In case any NI people are reading this, this is a not so subtle hint that you need to update the DB toolkit to support modern DB features.  Especially, if NI is charging $1000 for it.

[Mythilt]

Hmmm. 

I was going to suggest saving the file to disk, then loading it into the database using the INSERT command, but that still requires an intermediary step using PHP or similar.

Message Edited by Mythilt on 10-09-2008 12:44 PM

[HWJ]

Hi Jon, That is precisely what I have been doing, converting the jpeg file to binary file, then read back in binary string and writing to the database. I tried using the direct method of DB tools insert data.vi to insert the data and i also tried using the DB tools execute Query where you write the SQL command to do the insert of record, apparently as what Ramsey has mentioned, the limit seems to be stuck at 8k. I guess meanwhile, I shall just store the JPGs to disk and reference back with the path logged in the DB. Perhaps together with some further explanations to the end user of not logging to the DB direct.. Thanks for the feedback 😃