So, for those who might be interested, below is a very simple and succinct example (not all error handling tests have been implemented) of how to copy a blob from one DB to another.
Remember that you can also use the SQLite "ATTACH DATABASE" statement, which makes things much easier in some cases.
Code: Select all
EnableExplicit
; ╔═════════════════════════════════════════════════════════════════════════════════════╗
; ║ PLUGINS - STRUCTURES - ENUMERATIONS - CONSTANTS - MACROS - MAPS - GLOBAL VARIABLES ║
; ╚═════════════════════════════════════════════════════════════════════════════════════╝
;{ ════ PLUGINS ════
;- ════ PLUGINS
UseSQLiteDatabase()
UseJPEGImageDecoder()
;}
;{ ════ ENUMERATIONS ════
;- ════ ENUMERATIONS ════
Enumeration Windows ;{
#WIN_MAIN
EndEnumeration ;}
Enumeration Gadgets ;{ Gadgets fenêtre principale
#GAD_BT_COPYDATA
#GAD_ED_REPORT
#GAD_IMG_IMAGEPB
EndEnumeration ;}
Enumeration Images ;{
#IMG_IMAGE
EndEnumeration ;}
Enumeration SqlDB ;{
#SQLDB_SOURCE
#SQLDB_TARGET
EndEnumeration ;}
;}
;{ ════ CONSTANTS ════
;- ════ CONSTANTS ════
#SQL_CREATETABLE ="CREATE TABLE MyTable (Id INTEGER,ImageBlob LONGBLOB)"
#SQL_GETRECORDS ="SELECT * FROM MyTable"
#SQL_INSERTRECORD ="INSERT INTO MyTable VALUES (?,?)"
;}
;{ ════ GLOBAL VARIABLES ════
; -════ GLOBAL VARIABLES ════
Global.s TempDir=GetTemporaryDirectory()
Global.s SourceDBName=TempDir+"Temp_SourceDB.sqlite"
Global.s TargetDBName=TempDir+"Temp_TargetDB.sqlite"
;}
;-══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
; ╔════════════╗
; ║ PROCEDURES ║
; ╚════════════╝
Procedure.a Fc_DBsDeletion()
Protected.a Result=#True
; Source DB
If FileSize(SourceDBName)>=0
If Not DeleteFile(SourceDBName,#PB_FileSystem_Force)
Result=#False
MessageRequester("","Error while deleting source DB!",#PB_MessageRequester_Error)
EndIf
EndIf
; Target DB
If FileSize(TargetDBName)>=0
If Not DeleteFile(TargetDBName,#PB_FileSystem_Force)
Result=#False
MessageRequester("","Error while deleting target DB!",#PB_MessageRequester_Error)
EndIf
EndIf
ProcedureReturn Result
EndProcedure
Procedure Pc_DBsClosing()
If IsDatabase(#SQLDB_SOURCE):CloseDatabase(#SQLDB_SOURCE):EndIf
If IsDatabase(#SQLDB_TARGET):CloseDatabase(#SQLDB_TARGET):EndIf
EndProcedure
Procedure.a Fc_DBsCreation()
Protected *ImageBuffer
Protected.l ImageSize ; .l because we don't need bigger in our example
; Previous DBs deletion
If Not Fc_DBsDeletion():ProcedureReturn #False:EndIf
;
; -----------------------
; Source DB file creation
; -----------------------
If Not CreateFile(#SQLDB_SOURCE,SourceDBName)
MessageRequester("","Error while creating source DB!",#PB_MessageRequester_Error)
Goto Fc_DBCreation_Error
EndIf
CloseFile(#SQLDB_SOURCE)
;
; Source DB file opening
If Not OpenDatabase(#SQLDB_SOURCE,SourceDBName,"","",#PB_Database_SQLite)
MessageRequester("","Error while opening source DB!",#PB_MessageRequester_Error)
Goto Fc_DBCreation_Error
EndIf
;
; Table creation
If Not DatabaseUpdate(#SQLDB_SOURCE,#SQL_CREATETABLE)
MessageRequester("","Error while create source table!",#PB_MessageRequester_Error)
Goto Fc_DBCreation_Error
EndIf
;
; -----------------------
; Target DB file creation
; -----------------------
If Not CreateFile(#SQLDB_TARGET,TargetDBName)
MessageRequester("","Error while creating target DB!",#PB_MessageRequester_Error)
Goto Fc_DBCreation_Error
EndIf
CloseFile(#SQLDB_TARGET)
;
; Target DB file opening
If Not OpenDatabase(#SQLDB_TARGET,TargetDBName,"","",#PB_Database_SQLite)
MessageRequester("","Error while opening target DB!",#PB_MessageRequester_Error)
Goto Fc_DBCreation_Error
EndIf
;
; Table creation
If Not DatabaseUpdate(#SQLDB_TARGET,#SQL_CREATETABLE)
MessageRequester("","Error while create target table!",#PB_MessageRequester_Error)
Goto Fc_DBCreation_Error
EndIf
;
; -----------------------
; Source table populating
; -----------------------
; First record
ImageSize=?ImagePB_End-?ImagePB_Start ; See datasection below
SetDatabaseLong(#SQLDB_SOURCE,0,1) ; Field 1 ID
*ImageBuffer=AllocateMemory(ImageSize)
If *ImageBuffer
CopyMemory(?ImagePB_Start,*ImageBuffer,ImageSize)
SetDatabaseBlob(#SQLDB_SOURCE,1,*ImageBuffer,ImageSize) ; Field 2 Blob
Else
SetDatabaseNull(#SQLDB_SOURCE,1)
EndIf
DatabaseUpdate(#SQLDB_SOURCE,#SQL_INSERTRECORD)
If *ImageBuffer:FreeMemory(*ImageBuffer):EndIf
;
; Second record
SetDatabaseLong(#SQLDB_SOURCE,0,2) ; Field 1 ID
SetDatabaseNull(#SQLDB_SOURCE,1) ; Field 2 Blob
DatabaseUpdate(#SQLDB_SOURCE,#SQL_INSERTRECORD)
ProcedureReturn #True
Fc_DBCreation_Error:
Pc_DBsClosing()
Fc_DBsDeletion()
ProcedureReturn #False
EndProcedure
Procedure Pc_SourceRecordsCopying()
Protected *ImageBuffer
Protected.i Id
Protected.l ImageSize ; .l because we don't need bigger in our example
Protected.s RecordReport
Select GetGadgetData(#GAD_BT_COPYDATA)
Case 0
; -------------------------------------
; Records copying from source to target
; -------------------------------------
; Record selection
AddGadgetItem(#GAD_ED_REPORT,-1,"Selection of source table records")
If DatabaseQuery(#SQLDB_SOURCE,#SQL_GETRECORDS,#PB_Database_StaticCursor)
DatabaseUpdate(#SQLDB_TARGET,"BEGIN")
While NextDatabaseRow(#SQLDB_SOURCE)
ImageSize=DatabaseColumnSize(#SQLDB_SOURCE,1)
RecordReport=" Record "+GetDatabaseString(#SQLDB_SOURCE,0)+" : ImageSize : "+Str(ImageSize)
If ImageSize
*ImageBuffer=AllocateMemory(ImageSize)
If *ImageBuffer
If GetDatabaseBlob(#SQLDB_SOURCE,1,*ImageBuffer,ImageSize)
Id=GetDatabaseLong(#SQLDB_SOURCE,0)
RecordReport+" & Blob ok"
Else
ImageSize=0
RecordReport+" & Error blob"
EndIf
Else
MessageRequester("","Memory allocation error!",#PB_MessageRequester_Error)
EndIf
AddGadgetItem(#GAD_ED_REPORT,-1,RecordReport)
; Target table populating only if a blob exists
If *ImageBuffer And ImageSize
SetDatabaseLong(#SQLDB_TARGET,0,Id)
SetDatabaseBlob(#SQLDB_TARGET,1,*ImageBuffer,ImageSize)
If DatabaseUpdate(#SQLDB_TARGET,#SQL_INSERTRECORD)
AddGadgetItem(#GAD_ED_REPORT,-1," Copy to target DB : Ok")
EndIf
EndIf
If *ImageBuffer:FreeMemory(*ImageBuffer):EndIf
Else
AddGadgetItem(#GAD_ED_REPORT,-1,RecordReport+" & No blob")
EndIf
Wend
DatabaseUpdate(#SQLDB_TARGET,"COMMIT")
FinishDatabaseQuery(#SQLDB_SOURCE)
EndIf
SetGadgetData(#GAD_BT_COPYDATA,1)
SetGadgetText(#GAD_BT_COPYDATA,"Retrieve data from target table")
Case 1
; ----------------------------
; Target table data retrieving
; ----------------------------
AddGadgetItem(#GAD_ED_REPORT,-1,"")
AddGadgetItem(#GAD_ED_REPORT,-1,"Selection of target table records")
If DatabaseQuery(#SQLDB_TARGET,#SQL_GETRECORDS,#PB_Database_StaticCursor)
While NextDatabaseRow(#SQLDB_TARGET)
ImageSize=DatabaseColumnSize(#SQLDB_TARGET,1)
RecordReport=" Record "+GetDatabaseString(#SQLDB_TARGET,0)+" : ImageSize : "+Str(ImageSize)
If ImageSize
*ImageBuffer=AllocateMemory(ImageSize)
If *ImageBuffer
If GetDatabaseBlob(#SQLDB_TARGET,1,*ImageBuffer,ImageSize)
RecordReport+" & Blob ok"
Else
ImageSize=0
RecordReport+" & Error blob"
EndIf
Else
MessageRequester("","Memory allocation error!",#PB_MessageRequester_Error)
EndIf
AddGadgetItem(#GAD_ED_REPORT,-1,RecordReport)
;
; Image catching to display it in ImageGadget
If *ImageBuffer And ImageSize
If CatchImage(#IMG_IMAGE,*ImageBuffer)
SetGadgetState(#GAD_IMG_IMAGEPB,ImageID(#IMG_IMAGE))
AddGadgetItem(#GAD_ED_REPORT,-1," Image displaying")
EndIf
EndIf
If *ImageBuffer:FreeMemory(*ImageBuffer):EndIf
Else
AddGadgetItem(#GAD_ED_REPORT,-1,RecordReport+" & No blob")
EndIf
Wend
FinishDatabaseQuery(#SQLDB_SOURCE)
EndIf
DisableGadget(#GAD_BT_COPYDATA,#True)
Pc_DBsClosing() ; Closing bases here only because they're no longer needed afterwards
; However, as long as the application is not closed, you can consult the target table with a DB manager to see what the blob contains.
EndSelect
EndProcedure
Procedure Pc_DisplayUI()
Protected.i Event,EventType,GadgetNb
OpenWindow(#WIN_MAIN,0,0,400,310,"Test",#PB_Window_SystemMenu|#PB_Window_ScreenCentered)
EditorGadget(#GAD_ED_REPORT,10,10,380,200,#PB_Editor_ReadOnly|#PB_Editor_WordWrap)
ButtonGadget(#GAD_BT_COPYDATA,10,220,380,22,"Copy data from source DB to target DB")
ImageGadget(#GAD_IMG_IMAGEPB,116,260,168,35,0)
Repeat
Event=WaitWindowEvent()
Select Event
Case #PB_Event_CloseWindow:Break
Case #PB_Event_Gadget
GadgetNb=EventGadget()
Select EventType()
Case #PB_EventType_LeftClick
Select GadgetNb
Case #GAD_BT_COPYDATA:Pc_SourceRecordsCopying()
EndSelect
EndSelect
EndSelect
ForEver
EndProcedure
;-══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
; ╔════════════╗
; ║ MAIN CALLS ║
; ╚════════════╝
If Fc_DBsCreation()
Pc_DisplayUI()
Fc_DBsDeletion()
EndIf
End
;-══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
; ╔═════════════╗
; ║ DATASECTION ║
; ╚═════════════╝
DataSection
ImagePB_Start:
IncludeBinary #PB_Compiler_Home+"\Examples\Sources\Data\PureBasic.bmp"
ImagePB_End:
EndDataSection
Only this? And where are the provided advices?