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