Displaying BLOB

[Saj]

I've a got a SQLite database containing JPG images as BLOB, created using SQLite Expert. How do I go about getting a handle to the the image in the database?
In the attached example, type in '1' and then click on a result.

 

[mjcoon]

I'm sorry that I cannot be bothered (= not sufficiently curious...) to analyse your SQL d/b to determine the column name of the blob, but the Help for SQL function DataReader.GetImage includes an example that should enable you to work out how to do it given that you know the schema.

Else someone who has done this sort of thing will leap forward...

HTH, Mike.

 

[Erel]

You can also check SQLite Viewer: http://www.b4x.com/forum/open-source-projects/843-sqlite-viewer.html
It shows blobs as images.

 

[mjcoon]

I'm sorry that I cannot be bothered (= not sufficiently curious...)...

But of course it got the better of me in the end. Here's a modified version of your sample code which shows the images.

Mike.

 

[Saj]

Many thanks guys.

 

[Saj]

Another question, is it possible to execute a 'for loop' through all the records in a database to read all the values in a particular column, without having to individually query each record?

 

[mjcoon]

Another question, is it possible to execute a 'for loop' through all the records in a database to read all the values in a particular column, without having to individually query each record?

Yes, that is exactly what the Reader object does, and why the

If DBreader.ReadNextRow = False Then

is already present in your code.

So long as it returns True then you are still iterating through the records that match your SELECT command.

The index value in

DBreader.GetImage(index)

(and the other DBreader.GetXXX(index)) allow selection from the multiple column names that can be in the SELECT. In the sample I posted there was of course only the single image column 'JPG' in the SELECT.

These are such basic questions that perhaps it would be a Good Thing to work through some sample code from the forum...

Mike.

 

[Saj]

Hi, I have looked at the examples and its taking me a while to fully grasp all the ideas and concepts behind SQL. Please bear with me.

I'm trying to get a list of all the tables in a database. I tried the following, but this only returns the name of 1 table (FieldCount returns 1):

DBcommand.CommandText = "Select name FROM sqlite_master WHERE Type = 'table'"
DBreader.Value = DBcommand.ExecuteReader
If DBreader.ReadNextRow = False Then
Msgbox("DB not found.","Error",cMsgboxOK,cMsgboxAsterisk)
Else
DBfound = True
Msgbox (DBreader.FieldCount)
Msgbox (DBreader.GetValue(0))
Msgbox (DBreader.GetValue(1))
End If

 

[mjcoon]

DBcommand.CommandText = "Select name FROM sqlite_master WHERE Type = 'table'"

That command refers to a single column or field called "name", so DBreader.FieldCount will be =1 for each of any records found.

If there are multiple tables then they will appear as multiple records and you have to use DBreader.ReadNextRow to work your way through them.

BTW I got a bit confused when I was reading the examples (I'm only a recent convert to SQL) because you can actually access the first DBreader record without doing DBreader.ReadNextRow, but of course there may not be a first record so it is still as well to do the check. Though fields may also be missing, so it is possible, or recommended, to do DBreader.IsDBNull() before making use of a field value. (Note that I did not do that in the example code I posted; I assumed the image was present!)

Mike.

 

[Saj]

...as multiple records and you have to use DBreader.ReadNextRow to work your way through them.

So is there a way of actually knowing the number of records that have been found as a result of the query without having to count them using ReadNextRow?

Point understood about IsDBNull.

And on another point, I'm trying to create a new table using the following code, but it doesn't seem to work:

DBcommand.CommandText = "CREATE TABLE myTable (tilename CHAR, JPG BLOB)"

Saj.

 

[mjcoon]

If you really have to know the number of records that would be returned from a query command then you can do a command with a COUNT() first.

And I have no idea what is wrong with your table create; is the error message a clue?

Mike.