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

:sign0085:

 

[Erel]

How did you first create the database?

 

[willisgt]

Problem solved

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

:sign0161: