Write binary data to DB

[Raydur]

I am trying to write a binary file to an SQL database (with two columns: ID and data). Using the subvi "Read from Binary File", the output is a String which I then try to write to a database. I can do this if two conditions are met:

The data type in the database is string based, such as nvarchar(MAX), varchar(MAX), text, etc.

The amount of data is less than 8000 bytes.

I am running into an issue where if I write more than 8k of data, I get the error: "String data, right truncation". My understanding is the data types I listed above are supposed to handle up to about 2GB, so this is confusing.

I'm not sure if this will make a difference, but I want to use a data type of varbinary(MAX). However, when I use the "Read from Binary File", I get a String output. When I try to write this to the database using "DB Tools Insert Data VI", I get the error:
"Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query."

I'm guessing "CONVERT" is part of the SQL query language and is a standard error returned by either the database or ODBC driver? In other words, not something I have direct access to? Is there a way to convert the data into something (binary) that the database will not complain about?

I'm just savvy enough in SQL to understand some of this functionality, but would consider myself a novice.

I appreciate any help!

[SunshineDesign]

Try using Varchar(MAX). I have used it for all large data storage. The "binary" data is basiclly a large string. Make sure you have the latest updates for Labview and the DataBase Toolkit. NI had serious deficiencies in the SQL Server interface that did not properly support SQL Servery BLOB's or Binary Large Objects and saw them improperly as strings with the string size limits. They only resolved it this spring when I pressed them on the issue. Good luck.

[mikeporter]

First, what DBMS are you using? You need one that can handle BLOBS (Binary Large OBJectS).

Second, you can't use ODBC - you need native ADO drivers for whatever DBMS you are using.

Third, don't use the Database Connectivity Toolkit, it makes everything overly complicated.

Mike...

[SunshineDesign]

With SQL Server 2012 Express or full edition it is ADO.net and as I stated NI Database Toolkit only started supporting the BLOBs this last spring with 2011 SP2 and 3012 SP1. So make sure you have the lateast Labview SPx and Database Toolkit. ODBC will not connect to SQL Server 2012. I used BLOBs all the time and had to press NI to support when I tried to implement in LV2011 and 2012 last winter.

[Ranjeet_Singh]

Attached image file for reference.

[Ranjeet_Singh]

Tried?

[GeneThomas]

Modify NI_Database_API.lvlib:Cmd Create Parameters.vi (db toolkit) with the attached...

Thomas

Portland, OR

[David0217]

Was there a solution for this way to record binary data?

[GeneThomas]

Howdy,

That is the solution.  I pasted the code.

Thomas Drawhorn

[tst]

If you don't want to change the NI VI (you should create a copy of the VI if you do want to modify it), then you can also pass the data encoded as hex, but then I believe you would need to format the SQL string yourself. See here for an example.