Jump to content
News about the LabVIEW Wiki! Read more... ×

Recommended Posts

I can't see what you're actually doing but I'd say this seems expected...sqlite has somewhat squishy types.

https://www.sqlite.org/datatype3.html

"Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class."

"Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity"."

 

If that makes you feel bad, just remember that almost every interaction you have with your bank relies on a technology whose type system is pure madness.

Edited by smithd

Share this post


Link to post
Share on other sites
9 hours ago, infinitenothing said:

The attached code shows how SQLite will interpret various types into an integer. It makes me a little nervous that there's no warnings or other way to determine that a value might not match what was requested. I guess I could check every value for its column type. Is there an easier way that I'm missing?

 

SQLite differs from other databases in its dynamic type system and philosophy of "best attempt at conversion instead of error".  Note though that SQLite is meant as an embedded database, sitting behind an API written in another language.  Types are enforceable in that API.  I don't recall ever having mixed types in a column, because a table is generally only written to by one subVI, and maybe modified by another one right next to it in the same library/class.  I have one client where there are four applications that access the same db files, but they all use the same LabVIEW class for access.  The API even stores data points in a scaled format to take up less space, which is transparent to the application code.

Share this post


Link to post
Share on other sites

BTW, the SQLite Statement class can be subclassed, and you could easily define your own strict-typed versions of the "Get Column" property nodes.  These would just check type before calling the regular function.

  • Like 1

Share this post


Link to post
Share on other sites
Quote

Save your image files in binary Blobs.

Yes, I know that SQLite needs Binary Blob but not How to do it in labview and pass it to SQLite library using an SQL statement.

Edited by Gepponline

Share this post


Link to post
Share on other sites

What have you tried?  Have you looked at the examples?

Share this post


Link to post
Share on other sites
On 12/6/2018 at 11:32 PM, drjdpowell said:

What have you tried?  Have you looked at the examples?

Which example do you refer to?
I'm trying using this code to write my image but it seems not to work and I can't understand what I'm doing wrong.

 

snippet_SQLITE.thumb.png.c6f428f8545113deeb2eec9d0435da23.png

Share this post


Link to post
Share on other sites

"Seems not to work" is the vagueous description you could give, but I wonder why you aren't just saving your bmp image as a blob, rather than turning it into a LabVIEW image format (and then only storing part of that).

Share this post


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

"but I wonder why you aren't just saving your bmp image as a blob, rather than turning it into a LabVIEW image format (and then only storing part of that)."

As I told in my previous message, Because I Don't Know How to save my image as a blob in labview!
The image is not originally stored in my PC but is acquired with Vision Libraries.

So once acquired, Hwo to save it as a blob?
And then How to pass it to SQLite library to store it into my DB?

Share this post


Link to post
Share on other sites

I suggest you break your question down into parts as different threads and forums will be able to answer each part better. It looks like your SQLite stuff is correct and your issue is just flattening and unflattening so the machine vision forum might be a better place to ask for help. In the interim, here's one way to do your flatten unflatten using the IMAQ toolkit.

 

Store in blob.png

Edited by infinitenothing

Share this post


Link to post
Share on other sites

As you suggested, I've made an example using Vision Library, starting from a previously stored image but nothing change.
Or better, I don't get any error executing the VI and in the DB I can see the new record but using SQLite Administrator I'm not able to see the picture inside the DB.
SQLite Administrator has a specific function that should show the image.
I attach even the DB with 4 records with that should have the same image on it but I can't see it.

 

snippet_SQLITE.thumb.png.ddb15761a4e9b8eb559641219dcaedca.png

DB_CN35.sdb

Share this post


Link to post
Share on other sites
3 hours ago, Gepponline said:

using SQLite Administrator

This is a pretty important piece of information. I took a quick look at their page (unfortunately their support forums are down, and there is no way I'm installing that software on my computer, so this is all I can go on) and it seems to say it supports bmp and jpg images. from your posts, you have so far provided it with:

  • The pixel values of an image, type cast directly into a string with no metadata
  • The NI vision software flattened representation of an image.

I'd suggest the following:

  • If you have a bmp or jpg image, use the read from binary file function with a elements to read set to -1 (to read the whole file as one big string) and pass that directly to sqlite. You may need to make sure that sqlite administrator knows the type, but i think bmp and jpg both use linux-style 'lets hide the extension inside the file' encoding, so its probably not necessary.
  • If you have an imaqdx image in memory, use writestring (https://zone.ni.com/reference/en-XX/help/370281AD-01/imaqvision/imaq_write_string/) to generate a bmp or jpg string, and pass that directly to sqlite.

 

To give a slightly more complete explanation, if you read the first few posts on this page we discuss the sqlite type system, which is very flexible -- in this case, everything is a string. You told everyone you wanted to store images in the database, which you were successfully doing in the formats described above. You did not explain that you had a very specific tool in mind for reading those images back out, and that is where the problem lies. It was expecting the string to be a specific format, and you were storing it not as that format.

Share this post


Link to post
Share on other sites

I think your issue is the multitudes of "image" formats.  There are many formats, and many conversion functions, and it is difficult to get things to work by trial and error.  The webpage for "SQLite Administrator" indicates it understands JPG format, yet your attached code is converting from JPG to a LabVIEW-only flattened image format.  You need to read that JPG image into a LabVIEW string, without converting it into some other format, and insert it.   Look into the "Read form binary file"... (I see smithd has beaten me to it). 

Share this post


Link to post
Share on other sites

I want to clarify that as far as I know there's no standard way to store an image in a SQLite database. That is, @Gepponline, your question has nothing to do with SQLite much less this toolkit. As far as we know, SQLite Administrator uses a proprietary binary format for storing the images as blobs. You should really go to that community to determine what that format is. Unfortunately, that project looks like it was abandoned with no source code available so you might just be out of luck.

Share this post


Link to post
Share on other sites

My initial problem was on HOW to store blob data in an SQLite record field using this toolkit.
So i thought this was the right place to make my question.
I Had no idea about the problem of no standard for storing images on a BLOB field.
Now that I know that, i can say:
snippet_SQLITE.thumb.png.8536839ba188087f3e2ecf5e9e4cf130.png

1)Attached  are 2 solution working with SQLite Manager but not with SQLite administrator neither with DB Browser for SQLite

2) Being so, maybe it's better to store images in a specific folder and store the path in the DB.

Thank you so much for the support.


 

Share this post


Link to post
Share on other sites

DB browser seems to only support png/bmp. I found a video suggesting jpeg support too, but the link said only via nightly builds

Edited by smithd

Share this post


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

Tried inserting png, jpg, and bmp images (by solution 1) and they can be viewed by SQLite Expert Personal, which I use.

I've used that in the past too, but i was creeped out today when I looked at it and saw that the downloads were http instead of https. The reason I noticed was that windows claimed the 32-bit version had a trojan. I'm assuming it was a false detection, but even if thats the case you shouldn't be serving up installers over an unsecure connection.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

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