Problem: fetch and store image to/from database

[willisgt]

I'm trying to (1) store an image in a database, and (2) fetch that image from the database and display it.

The 'fetch and display' routine:

Sub btnShow_Click

    cmd.CommandText = "SELECT photodata FROM photos"
    reader.Value = cmd.ExecuteReader
    reader.ReadNextRow
    Form1.Image = reader.GetImage(0)

End Sub

produces an error:

'File opened that is not a database file
file is encrypted or is not a database
Continue?'

The 'capture' routine:

    Dim img

pfn = camera.GetImage( 640, 480, 0, "Form1", "Camera" )

Msgbox( pfn )

If pfn <> "" Then

    query = "REPLACE INTO photos ( id, photodata ) VALUES ( 1, " & cmd.FileToBLOB( pfn ) & " ) WHERE ( id = 1 )"

    cmd.CommandText = query
    cmd.ExecuteReader

End If

End Sub

produce an error:

'File opened that is not a database file
near 'WHERE': syntax
error
Continue?'


The entire program follows:

(the 'camera' object is from dz's camera capture library; 'con' and 'cmd' are SQL library objects)

Sub Globals

    dbfn = ""

End Sub

Sub App_Start

    Con.New1
    Cmd.New1( "", con.Value )
    dbfn = AppPath & "\photos.s3db"
    Con.Open( "Data Source = " & dbfn )

    reader.New1

    camera.New1


    formHeader.Color = cBlack
    formHeader.FontColor = cWhite
    formHeader.FontSize = 9
    formHeader.Width = form1.Width
    formHeader.Height    = 23
    
    btnShow.Top = 25
    btnCapture.Top = 25


    Form1.Show

End Sub


Sub btnCapture_Click

    Dim img
    
    pfn = camera.GetImage( 640, 480, 0, "Form1", "Camera" )

    Msgbox( pfn )

    If pfn <> "" Then

        query = "REPLACE INTO photos ( id, photodata ) VALUES ( 1, " & cmd.FileToBLOB( pfn ) & " ) WHERE ( id = 1 )"

        cmd.CommandText = query
        cmd.ExecuteReader

    End If

End Sub

Sub btnShow_Click

    cmd.CommandText = "SELECT photodata FROM photos"
    reader.Value = cmd.ExecuteReader
    reader.ReadNextRow
    Form1.Image = reader.GetImage(0)

End Sub

Sub btnClose_Click

    AppClose

End Sub

I'm slowly losing my mind in my old age; does anyone see what I'm doing wrong?


Gary

[Erel]

How did you first create the database?

[willisgt]

I first created the database with SQLite Administrator.

However, I found the problem - the syntax of this line:

query = "REPLACE INTO photos ( id, photodata ) VALUES ( 1, " & cmd.FileToBLOB( pfn ) & " ) WHERE ( id = 1 )"

is completely incorrect. No whereclause was required for this REPLACE INTO.


Gary