INSERT a file in a BYTEA field: no problem.
Use an INSERT with RETURNING in DatabaseQuery(): no problem.
If I use an INSERT with a BYTEA field (SetDatabaseBlob() in front) and RETURNING, I get a DatabaseError():
'There is no parameter $1'
Is this a limitation of Postgres or is it a bug in PB handling of DatabaseQuery()
Code: Select all
#WithReturning = #True
#WithBYTEA = #True
EnableExplicit
UsePostgreSQLDatabase()
Procedure StoreDataFromFile(DB.i, Table$, Filename$)
Protected Result.i, File.i, Size.i, *Buffer, SQL$
File = ReadFile(#PB_Any, Filename$)
If File
Size = Lof(File)
Debug "FileSize: " + Str(Size)
*Buffer = AllocateMemory(Size)
If *Buffer
If ReadData(File, *Buffer, Size) = Size
SetDatabaseBlob(DB, 0, *Buffer, Size)
CompilerIf #WithReturning = #False
SQL$ = "INSERT INTO " + Table$ + " (filename, filesize, filedata) VALUES (E'" + ReplaceString(Filename$, "\", "\\") + "'," + Str(Size) + ", $1)"
Debug "(Update) " + SQL$
If DatabaseUpdate(DB, SQL$)
CompilerElse
CompilerIf #WithBYTEA = #False
SQL$ = "INSERT INTO " + Table$ + " (filename, filesize) VALUES (E'" + ReplaceString(Filename$, "\", "\\") + "'," + Str(Size) + ") RETURNING id"
CompilerElse
SQL$ = "INSERT INTO " + Table$ + " (filename, filesize, filedata) VALUES (E'" + ReplaceString(Filename$, "\", "\\") + "'," + Str(Size) + ", $1) RETURNING id"
CompilerEndIf
Debug "(Query) " + SQL$
If DatabaseQuery(DB, SQL$)
If NextDatabaseRow(DB)
Debug "Returnvalue: " + Str(GetDatabaseLong(DB, 0))
EndIf
FinishDatabaseQuery(DB)
CompilerEndIf
Result = #True
Else
Debug DatabaseError()
EndIf
EndIf
FreeMemory(*Buffer)
EndIf
CloseFile(File)
EndIf
ProcedureReturn Result
EndProcedure
Procedure RestoreDataToFile(DB.i, Table$, Filename$="")
Protected Result.i, File.i, Size.q, *Buffer
If DatabaseQuery(DB, "SELECT filename, filesize, filedata FROM " + Table$)
If NextDatabaseRow(DB)
If Filename$ = ""
Filename$ = GetDatabaseString(DB, 0)
EndIf
Size = GetDatabaseLong(DB, 1) ;/ Get size of the blob
*Buffer = AllocateMemory(Size)
If *Buffer
Debug "Columnsize: " + Str(DatabaseColumnSize(DB, 2))
GetDatabaseBlob(DB, 2, *Buffer, Size)
File = CreateFile(#PB_Any, Filename$)
If File
If WriteData(File, *Buffer, Size) = Size
Result = #True
EndIf
CloseFile(File)
EndIf
FreeMemory(*Buffer)
EndIf
EndIf
FinishDatabaseQuery(DB)
EndIf
ProcedureReturn Result
EndProcedure
Define DB.i, Table$, Filename$
DB = OpenDatabase(#PB_Any, "host=xxx.xxx.xxx.xxx port=5432", "yyy", "zzz")
If DB
Table$ = "datatest"
If DatabaseUpdate(DB, "CREATE TEMP TABLE " + Table$ + " (id SERIAL, filename TEXT, filesize INTEGER, filedata BYTEA)")
Filename$ = OpenFileRequester("Choose a file", "", "*.*", 0)
If Filename$
If StoreDataFromFile(DB, Table$, Filename$)
RestoreDataToFile(DB, Table$, Filename$ + "." + GetExtensionPart(Filename$))
EndIf
EndIf
EndIf
CloseDatabase(DB)
Else
MessageRequester("Error", "Was not able to connect.")
EndIfOh, you need top adjust the OpenDatabase() line.
Bernd
