Retrieve Pictures from Access Database w/LabSQL

[acolunga07]

I am trying to import pictures from a Access Database w/LabSQL.  I made a a database and inserted the pictures as objects into my Table.  The name of the table is Pictures, and it has 3 columns (ID, XY_Pic, ZY_Pic).  ID is numeric 1-X for each entry and XY_Pic + ZY_Pic are bmp pictures.  I ran through the tutorials included in LabSQL examples but it is all text data.  I am able to import the first column of data (the numbers 1-X) but I am unable to get the pictures.  Does LabSQL even support what I am trying to do or do I need to purchase LabView's Database package?

[adambrewster]

The database shouldn't care if you're trying to access text or graphics, so you should be able to do this with what you have.

When you say that you can't get the pictures, what exactly does that mean?  There are three steps to the process: fetch the raw BMP data, parse the data,  and then show it to the user.  Can you do any of the steps?

If you're failing at the first step (getting the raw data from the database), what's the mode of failure?  Are you getting an error message?  Is your query returning no data at all or returning incorrect data?

Also, can you post your code?

[acolunga07]

The output of the SQL Execute VI is a 2D array of strings.  When I wire an indicator to it, all I get are the IDs (1, 2, etc) which are stored in the first column but not pictures which are stored in the 2nd and 3rd columns.  I am not sure how useful my code will be, because it specifically references a database on my computer.  I had to a few things before hand with the ODBC thing in the administrative tools before I could get it working.

As far as the 3 steps... I am able to import a picture from a file using a few VIs to get the data and display it, is this what you mean by parseing and displaynig the picture?

Sorry, I am completely new to picture handling and database stuff.

[adambrewster]

Okay, looking at your VI it looks like you have everyhting you need to extract the raw data from the database.

What is your query string (command text)?

What does the error out terminal say when you run the VI?

[acolunga07]

SELECT * FROM Pictures

Pictures is the name of the Table.  I also tried SELECT XY_Pic, ZY_Pic FROM Pictures.  XY_Pic & ZY_Pic are the names of the 2nd and 3rd column that have the bmps in them.  When I do this I get the following error:

Error Code: -2147217904

Error Message: Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. in ADO Connection Execute.vi->SQL Execute.vi->Picture Handling.vi

[adambrewster]

That error message indicates that there's something wrong with your query.  I don't use access, (I was testing with mysql), but from what I can tell the only time you see that message is when you select on a column or table that doesn't exist.

You mentioned that you were able to fetch the ID column.  Were you using a different query for that, or does "SELECT * FROM Pictures;" return the first column and an error message?

Also, you might want to try quoting the table and column names with backticks

SELECT * FROM `Pictures`;
SELECT `XY_Pic`,`ZY_Pic` FROM `Pictures`;

[acolunga07]

The table and the columns exist.  I can open the databse in Access and see it.  I was thinking about using mySQL since it doesnt have the file size limitation.  How would I create a mySQL database?

The SELECT * FROM Pictures would return the IDs and no errors.

The other command that I tried would give me the errors.

[adambrewster]

This seems to be more of an access problem than a labview problem, so I'm out of my depth here.

I'm not sure access supports this, but try to "DESCRIBE Pictures;".  That should return a list of columns that are available.  I bet you can only see the ID column.

If the other colums exist but aren't showing up then there may be some setting that's making them private.  It's also possible that your labview program is accessing the wrong database.  Did you ever save a version that didn't have the extra columns?

If you want to use mysql, the first thing you have to do is download it (from http://www.mysql.org/downloads/) and install it.  Once it's running, you can create tables with SQL queries.  I think this one will give you the table you described earlier:

CREATE TABLE `Pictures` ( `ID` int unsigned NOT NULL, `XY_graph` blob, `YZ_graph` blob, PRIMARY KEY (`ID`) );

If you do install mysql, and you've got a webserver with php, you'll probably also want phpmyadmin.  It's a nice web based interface that lets you do basically everything you ever need to do to a mysql server without using the command line client.  You can get it from http://www.phpmyadmin.net/.

[acolunga07]

The DESCRIBE command didnt work.  That seems like a whole lot I have to do in order to get the simple task of storing pictures done.  I mean I guess I'll do it if it is absolutely necessary.  Do you have this setup on your computer?  If so, would you mind testing it out?  I want to make sure it is not a LabSQL limitation.  Tell me if you are able to create a table, store pictures in there and retrieve them.  If so, would you mind pasting the code?

[adambrewster]

I took your program and extended it so that it will actually render the picture, and I put a couple of bitmaps into the database.

Now when I try to "SELECT * from Pictures;" it returns two columns (which is all I have in my table).  The first column is the IDs and the second is empty.

Is this what you were seeing?  If so, then it looks like you were right: this is a LabSQL limitation, but it's pretty easy to work around.

I tried changing the picture column from binary to text and when I did that LabSQL started returning data in the second column.

The only problem with this is that you need to make sure that the values you put into the database won't get destroyed by the way the system handles text columns.  The easiest way to do that is to encode the files before you store them.  (and decode them after you fetch them, of course).  Given that you're using the BMP format, I guess disk space isn't an issue, so you can probably just use hex encoding.  If you want to be a bit more conservative, you can use something like base64.