LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

Write binary data to DB

Highlighted

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!

Download All
0 Kudos
Message 1 of 7
(2,512 Views)
Highlighted

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.

0 Kudos
Message 2 of 7
(2,485 Views)
Highlighted

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...


Certified Professional Instructor
Certified LabVIEW Architect
LabVIEW Champion

"... after all, He's not a tame lion..."

For help with grief and grieving.
0 Kudos
Message 3 of 7
(2,467 Views)
Highlighted

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.

0 Kudos
Message 4 of 7
(2,459 Views)
Highlighted

Attached image file for reference.

--------------------------------------------------------------------------------------------------------
Kudos are always welcome if you got solution to some extent.

I need my difficulties because they are necessary to enjoy my success.
--Ranjeet
0 Kudos
Message 5 of 7
(2,445 Views)
Highlighted

Tried?

--------------------------------------------------------------------------------------------------------
Kudos are always welcome if you got solution to some extent.

I need my difficulties because they are necessary to enjoy my success.
--Ranjeet
0 Kudos
Message 6 of 7
(2,438 Views)
Highlighted

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

 

Thomas

Portland, OR

 

 

 

 

0 Kudos
Message 7 of 7
(2,062 Views)