Jump to content
drjdpowell

SQLite for OpenG

Recommended Posts

Hello,

is it possible to execute some SQL statements on dates, if they are formatted into Blob ?

For example :

SELECT COUNT(*) FROM Results WHERE DATE(LVTime)=CURRENT_DATE

It doesn't seem to work, maybe I do something wrong ?

Share this post


Link to post
Share on other sites

SQLite's DATE() function works on text formats, including the text format this library can write, but not the Blob format (which is the LabVIEW binary timestamp).

 

You can easily search by time by calling 

SELECT COUNT(*) FROM Results WHERE LVTime>=? and LVTime<?

and binding the two end times.  Bind the end times with the same format that LVTime uses.

  • Like 1

Share this post


Link to post
Share on other sites

I've failed to convert binary data to string suitable for insert in a database as BLOB (via Bind Blob property). May be I'm too new to LabView yet.

So would anybody give me a hint how it can be done? Binary data are jpeg picture (from Read JPEG File.vi) and waveform data (from

Sound File Read.vi). I've used the "Flatten to String" function and "Variant to Flattened String" function. And also have tried a combination

of "Read From Binary File" + "Variant to Flattened String" functions.

In any case the blob data seen by DB Browser for Sqlite seem to be essentially different size. And there is no RIFF tag in case of JPEG file blob.

I know that using Blobs for storing data is not a good idea but I need this kind of experience.

Edited by igorbounov
mistypes

Share this post


Link to post
Share on other sites

@igorbounov I haven't worked with SQLite for blobs, but had some experience with the NI Database Toolkit. I had to flatten data to a string and then convert the string to a U8 byte array before being able to save it correctly. Maybe that helps 

Share this post


Link to post
Share on other sites
13 hours ago, igorbounov said:

Can you give me an example (to get the idea)?

Something like this should work... I use the NI DB Toolkit and an Oracle database to write to the blob using parameterized queries (you can't write binary data using pure string queries). Maybe with SQLite you have something similar.

snip.png

Share this post


Link to post
Share on other sites
16 hours ago, igorbounov said:

Can you give me an example (to get the idea)?

I think you need to provide an example. Otherwise people can only guess what the issue is.  For putting a binary file into an Sqlite blob l would just use "Read from Binary" followed by "Bind Blob".

Share this post


Link to post
Share on other sites
7 hours ago, drjdpowell said:

I think you need to provide an example.

There is almost nothing to provide as an example, it seems that I simply don't know how to prepare data for Bind Blob:

binary_to_blob.png

And here is the contents of resulted Blob as shown by DB Browser for SQlite:

blob_contents.png

Only five bytes :-(  .. from a jpeg image (about 80 kbytes). And here is another way - from jpeg to blob:

jpeg_to_blob.png

And here is its blob contents (4899028 bytes from the same 72821-bytes image).

blob_contents_2.png

So it looks like I do something (or may be everything) wrong.

Here is the way I try to flatten the data from wav-file:

wav_to_blob.png

There is nothing to be proud of because I get this blob contents from a 1982508-bytes of wav-file:

blob_contents_3.png

I can not understand why I should represent the binary data (some portion of memory - if it were a C-program)

as a text.

 

Share this post


Link to post
Share on other sites

Quite a lot of things wrong indeed.

In the first you use Variant to Flatten to flatten the binary string that you read from the file into a Flatten data. But a Binary string is already flattened, turning it into a variant and then flattening it again is not only unnecessary but is synonym to forcing a square peg into a round one in order to force it through a triangle hole.

I'm not familiar with this implementation of the SQL Lite Library but the BLOB data seems to be a binary string already so I would assume that connecting the wire from the read file directly would be the best option. Personally I would have made the BLOB function only accept a Byte Array as that is more what a Blob normally is. In that case the String to Byte Array would be the right choice to use for converting the File Contents binary string into the Byte Array.

The second attempt is not flattening the JPEG binary content but the LabVIEW proprietary Pixmap format. This is uncompressed and likely 32 bits in nature so a decent sized JPEG image gets suddenly 6 to 10 times as large in this format. You could write that as blob into the database but the only client who can do anything with that blob without reverse engineering the LabVIEW pixamp format (which is not really difficult since it is simply a cluster with various elements inside but still quite some work to redo in a different programming language) is a LabVIEW program that reads this data and turns it back into the LabVIEW Pixmap format and then uses it.

In the third you take a LabVIEW waveform and flatten that. A LabVIEW waveform is also a LabVIEW proprietary binary data format. The pure data contents wouldn't be that difficult to extract from that stream but a LabVIEW waveform also can contain attributes and that makes the format pretty complicated if they are present (and to my knowledge NI doesn't really document this format on binary level).

The first is a clear error, the other two might be ok if you never intend to use anything else than a LabVIEW written program to retrieve and interpret the data.

Edited by rolfk
  • Like 1

Share this post


Link to post
Share on other sites
43 minutes ago, rolfk said:

I would assume that connecting the wire from the read file directly would be the best option.

No, it gives only one-byte Blob, but this is the correct byte! So this is the way in the right direction!

So I need extracting remaining bytes.

 

Share this post


Link to post
Share on other sites
1 hour ago, rolfk said:

I would assume that connecting the wire from the read file directly would be the best option.

 

Yes, finally it works this way:

binary_to_blob_solved.png

It is so simple! Thanks!

Share this post


Link to post
Share on other sites

Are you familiar with the "Probes" in LabVIEW?  I ask because you seem to be debugging by looking at the db file with a viewer program, but you could have quickly seen that "Read from Binary file" was only returning one byte by just putting a probe on the output.

Share this post


Link to post
Share on other sites
17 hours ago, drjdpowell said:

Are you familiar with the "Probes" in LabVIEW?  I ask because you seem to be debugging by looking at the db file with a viewer program, but you could have quickly seen that "Read from Binary file" was only returning one byte by just putting a probe on the output.

Yes, it was a mistery to me when I've seen by means of probe only one byte of data until I have thoroughly read the documentation. And also it appeared that "little-endian" is not a default byte order for reading binary files.

I was also puzzled when "Bind Timestamp" for time field appeared to produce a 16-byte Blob that DB Browser for SQlite sees as pure binary data. This item was discussed a fiew pages ago so "Bind Timestamp (Unix)" looks like a more conveniant format for selecting, sorting and comparing.

When I've built a test executable application for my LabView program, application builder produced a dll for SQlite support - should it be shipped along with the exe-applications?

Share this post


Link to post
Share on other sites

drjdpowell, is there something special in the way that SQLite for Labview uses for storing Blobs?

I can not recover back the binary data stored in a blob:

recover_from_binary.png

Here the wire to Unflatten from String comes from a "Sqlite EXEC SQL" VI, the data on this wire as shown in a String indicator look like

a JPEG data should look but "LabVIEW:  Unflatten or byte stream read operation failed due to corrupt, unexpected, or truncated data."

So something is wrong with binary string from Sqlite Query (may be it is not a binary string?).

Share this post


Link to post
Share on other sites
7 hours ago, igorbounov said:

look like a JPEG data

Then it's probably JPEG data.  Why are you trying to unflatten as a LabVIEW 2D Picture format (which is NOT JPEG data)?   I think you need to research data formats and how to convert between them.   Stop guessing randomly and read the documentation.  Also use your probes to verify that what you put into SQLite you get back unchanged.  You aren't having any problems with the SQLite part of your program; you just having issues understanding formats.

This might help you:

JPEG to 2D Picture.png

Share this post


Link to post
Share on other sites

I was wondering if this toolkit supports the SQLite backup functions for saving an in memory database to a file.  If not, are there alternatives to accomplish this efficiently?

Thanks for your help

Share this post


Link to post
Share on other sites
On 20/07/2018 at 0:57 PM, AndrewJR said:

I was wondering if this toolkit supports the SQLite backup functions for saving an in memory database to a file.  If not, are there alternatives to accomplish this efficiently?

Thanks for your help

I'm afraid  those functions have not been implemented yet. 

Share this post


Link to post
Share on other sites

Hi James,

Thanks for the tool, it's great! A quick question: is it possible to create encrypted DB files (with the password hard-coded in the block diagram)?

Thanks,

Sergey

 

Share this post


Link to post
Share on other sites

A great toolkit, thanks a lot!

When I used it the last time a year ago I had the problem that inserting a csv file took a long time due to the whole insert loops in labview compared to the import a table from a csv command in the DB Browser for SQLite.

Wouldn't it be possible to have a vi that imports a table from a csv directly with the dll what would be comparable in speed to the DB Browser tool?

thanks in advance

Share this post


Link to post
Share on other sites
6 minutes ago, mischl said:

I had the problem that inserting a csv file took a long time due to the whole insert loops in labview

Did you wrap your INSERTs in a transaction?  

Share this post


Link to post
Share on other sites

I suspect your problem is not using BEGIN/COMMIT or some other way of wrapping many INSERTS in one transaction.  Try looking at Example1, which inserts a large number of points.

Share this post


Link to post
Share on other sites

Thank you for the feedback and help James! I am going to examine SQLite Example 1 -- Create Table.vi

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.