LabVIEW
LabVIEW Database Parameterized Query prepends array sizes to binary data
Just to add to complexity of databases...
When 'database' is used, it almost always refers to a relational database.
There are other database paradigms, like graph databases (Neo4J) or time series databased (InfluxData).
These are not better, they're just suitable for other situations.
People tend to pick a relational database and then coarse their situation in it. Probably violating normal forms, adding to the problem, not to the solution.
This is understandable, as relational databases are the easiest accessible from LabVIEW. And also, the others are not. This makes a relational database easiest short term solution, but that doesn't make it a good solution.
Search LabVIEW like a graph! 
@tst wrote:
I don't know if you can use the parameterized query VIs for this, as LV flattens the data in the binary case and that includes the prepended length of the array. One option is to create a copy of the VIs, which doesn't do this, but I expect this might complicate you in the future, if NI changes the DB library to restrict access to the VIs.
Another option is to build an explicit binary string version of the data and pass that directly into the full query, rather than using a parameterized query. I'm assuming you're using SQL Server and the column type is defined as binary or varbinary, where I know this works. It might work differently in other cases. This would like something like CALL store_img (@image_in=0x01030498fg...). The 0x indicates this will be binary data and then each two chars is the hex value of the byte. This is easy enough to do in LV using Number to Hex String with a width of 2 and then wire the array into a concatenate primitive:
We basically ended up doing this to insert large amounts of varbinary data into a SQL Server database, but we did have to add in the 4 bytes of size information at the start of the BLOB ourselves:
@ The OP... Taking BLOBs out of the database using "Database Variant to Data.vi", you shouldn't see the extra bytes. But if you just use the regular "Variant to Data.vi", you will. That was our experience anyway.
@jmerrill wrote:
...
Of course, all the toolkit functions are locked so I cannot help troubleshoot this farther.
The toolkit should not prepend array size to binary parameters.
It might be the evaluation software, in my paid version of LabVIEW you can open the Database Connectivity Toolkit VIs. When you see the size getting added to the binary data, how/where are you seeing that happen?
@tst wrote:
@Jay14159265 wrote:
When you see the size getting added to the binary data, how/where are you seeing that happen?Variant to Flattened String prepends the size data for variable sized data to the string.
I'm not seeing the behavior you describe, or I'm not understanding the issue.
Either way the point is if the OP can't open the VI, how are they seeing this happen? And if they can open the VI, why not extend it to do what they want it to do.
@Jay14159265 wrote:
I'm not seeing the behavior you describe, or I'm not understanding the issue.
Your first node is Flatten To String, which is not used in the code.
The second node is Variant To Data, which is also not used and not relevant here.
The relevant one is Variant To Flattened String, which is used in the code (as can be seen in your previous screenshot) and that produces a flat format, which includes the size for variable-sized data.
@Jay14159265 wrote:
Either way the point is if the OP can't open the VI, how are they seeing this happen? And if they can open the VI, why not extend it to do what they want it to do.
I don't know why they can't open the code (some of the VIs are locked and maybe they looked in the wrong place) and I don't know if they'll respond, as the thread is a couple of years old by now.
As for not modifying the VI, the main reason is that if you want to modify it, you need to create a copy of the entire library if you want to be safe, which means you won't get updates and might have issues if it depends on NI-specific components (I expect that it works directly with the ADO ActiveX component built into Windows, but I haven't checked).
@tst wrote:
I expect that it works directly with the ADO ActiveX component built into Windows, but I haven't checked.
It actually used ADO (ActiveX Database Objects) since NI took it over from (I believe the company was called Ellipsis Inc. I still have the manual somewhere). Ellipsis Inc had implement a CIN to interface to a specific third party API (no sure about the company that provided that, it's way to long ago and that company has long gone too) that was using internally the ODBC API.
The use of ADO is also the approach of some other Database driver libraries in LabVIEW such as LabSQL. Some others that were created only much later instead use the .Net Database API.
@rolfk wrote:The use of ADO is also the approach of some other Database driver libraries in LabVIEW such as LabSQL. Some others that were created only much later instead use the .Net Database API.
I've used the .Net Database API. The code will be open sourced in the next few months.
.NET (or probably how LV calls .NET) seems to be a bit slower than AX. Even after moving all the looping\iterating into a .NET assembly, it's slower.
.NET has a dedicated SQLServer child, but we've noticed the general parent is often faster, especially when the SQLServer is in a docker container.
I used to work on the ActiveX based Test & Measurement Solutions Database Toolkit back when that company was still a thing. That toolkit was even sold for some time. And I implemented a DLL to convert OLE Variants into LabVIEW native datatypes because the build in conversion in LabVIEW was not very efficient for iterating data types like arrays of anything but scalars. Not surprised that .Net is even worse.
.Net invocation (and ActiveX too) is in LabVIEW always over the IDispatch interface. That for sure adds some overhead to every method invocation and if done over every element in an array this tends to be VERY noticeable. Unfortunately the resolution from the method (and property) name to the dispatch ID is done in LabVIEW at compile time. This is the main reason that LabVIEW fails to properly execute automation server and assembly calls for newer versions of the component if the method signature has changed (additional optional parameters) such as for the Save method for pretty much every Office version.
Doing the name to dispatchID resolution at compile time is a good thing as it costs performance to do that, but the compiled method call should also contain the original name as a fallback, so that when the IDispatch call fails because of dispatchID mismatch, it can retry to do the dispatchID resolution at runtime and avoid the problem that ActiveX and .Net VI libraries will fail if they are attempting to call a newer version with updated dispatch interface.
With access to the LabVIEW source code and some minor changes to how the compiled data structure for ActiveX (and .Net calls is constructed) this could have been fixed already 20 years ago.
