Image usage with sqllite

[tremara1]

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]

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

[RB Smissaert]

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]

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

[RB Smissaert]

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

[RB Smissaert]

Just tried this out by adapting the example that is in the SQL help file and it all works fine:

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

[RB Smissaert]

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

[Erel]

cmd.FileToBlob and cmd.BytesToBlob create strings which can't be used with SetParameter. For now you will need to add the blob without using parameters (like in the example from the manual).
I will add this feature in the next update.

[RB Smissaert]

I thought I got it to work with a .gif. Will check again later.

RBS

[tremara1]

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