Jump to content

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 ?

Link to comment

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
Link to comment
  • 9 months later...

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
Link to comment
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".

Link to comment
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.

 

Link to comment

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
Link to comment
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?

Link to comment

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

Link to comment
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

Link to comment
  • 4 months later...
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. 

Link to comment
  • 5 weeks later...

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

Link to comment

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.