Image usage with sqllite

tremara1

Active Member
Licensed User
Longtime User
I am playing around trying to learn how to store images in a db. I have come up against an error I cant seem to lick. I have put the image in the db but when I select sql the image to an image control I get a 'specified cast not valid' error.
This is probably an obvious code snafu but I guess I will learn from it.
ps I am v6.80 and checked the manual my code seems close.
The example I am using is in the zip........
Thanks again guys...
 

tremara1

Active Member
Licensed User
Longtime User
looks like the insert sql

I have tried a few more things it seems the problem is with the insert sql using addParameters and setParameters.......
It does not seem to save the blob image....
I tried a standard sql insert statement(a real cow to write with all those quotes) and it worked.....
Any chance the addParameters could work??
Anyone help??
Trev Ballarat Victoria Australia
 
Last edited:
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
B4X: 
Any chance the addParameters could work??

Should work fine.
Post the code you got now and somebody will tell how to change to using
parameters.

RBS
 

tremara1

Active Member
Licensed User
Longtime User
B4X: 
cmd.AddParameter("value1")
   cmd.AddParameter("value2")
   cmd.CommandText = "INSERT INTO picA VALUES (@value1,@value2)"
   con.BeginTransaction
      cmd.SetParameter("value1",txtId.text)
      cmd.SetParameter("value2",cmd.FileToBLOB(AppPath & "\abc.gif"))
      cmd.ExecuteNonQuery
   con.EndTransaction
This is the code I used to insert the image........this does the insert but the image(blob) part is not inserting properly when you look at the field in the db (sqllite expert personal a great tool) you can look at it as the hex or an image viewer. There is hex info but the image viewer shows blank. When I used the regular insert the image shows as a thumbnail when viewed in sqllite expert personal
Trev Ballarat Victoria Australia
 
Last edited:
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
That code seems OK.
What is your create table SQL?
How did you decide the blob was not in the database?
Did txtId.text get in the database?

RBS
 
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
Just tried this out by adapting the example that is in the SQL help file and it all works fine:

B4X: 
Sub App_Start

   Form1.Show
   
   con.New1
   reader.New1
   
   con.Open("Data Source = " & AppPath & "\BlobTest.db3") 'Opens the database.
   cmd.New1("CREATE TABLE IF NOT EXISTS pictures([NAME] TEXT, [IMAGE] BLOB)",con.Value)
   cmd.ExecuteNonQuery
      
   'Save the image in the database (change the image name to an existing image file).
   cmd.AddParameter("value1")
   cmd.AddParameter("value2")
   cmd.SetParameter("value1", "Image66.gif")
   cmd.SetParameter("value2", cmd.FileToBLOB(AppPath & "\Image66.gif"))

   cmd.CommandText = "INSERT INTO pictures values(@value1, @value2)"
   cmd.ExecuteNonQuery
   
   'Load the image from the database.
   cmd.CommandText = "SELECT image FROM pictures"
   reader.Value = cmd.ExecuteReader
   reader.ReadNextRow
   Form1.Image = reader.GetImage(0)

End Sub


RBS
 
R

RB Smissaert

Well-Known Member
Licensed User
Longtime User
For some reason I can't do this with a .jpg file and I get:
Specified cast is not valid at the last line:
Form1.Image = reader.GetImage(0)

Also tried with an image control, but same error.

How do show the .jpg blob with reader.GetImage(0) ?


RBS
 

tremara1

Active Member
Licensed User
Longtime User
thanks.....

Thanks everyone......just a bit relieved I was not missing something really basic.
I too tried the example from the manual and got it to work...again the team comes good.
Trevor
Ballarat Victoria Australia
 
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…