Image usage with sqllite

Discussion in 'Questions (Windows Mobile)' started by tremara1, Jul 9, 2009.

  1. tremara1

    tremara1 Active Member Licensed 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...
     
  2. tremara1

    tremara1 Active Member Licensed 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: Jul 9, 2009
  3. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    Code:
    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
     
  4. tremara1

    tremara1 Active Member Licensed User

    Code:
    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: Jul 9, 2009
  5. RB Smissaert

    RB Smissaert Well-Known Member Licensed 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
     
  6. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

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

    Code:
    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
     
  7. RB Smissaert

    RB Smissaert Well-Known Member Licensed 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
     
  8. Erel

    Erel Administrator Staff Member Licensed User

    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.
     
  9. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

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

    RBS
     
  10. tremara1

    tremara1 Active Member Licensed 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
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice