The query is correct, tested. The blob export routine works, tested. Just need an ex-SPURT opinion if this needs doing better.
Code: Select all
Procedure ExportDatabase()
; Process the current table name and dump the contents to an XML file on disk. Only need specific fields
QueryString.s = "SELECT m.Title, " +
"m.Information, " +
"m.Category, " +
"m.Archived, " +
"m.Favourite, " +
"m.Locked, " +
"m.Deleted, " +
"m.Updated, " +
"m.Owner, " +
"n.Attachment, " +
"n.Filename " +
"FROM MyInfo " +
"AS m " +
"LEFT JOIN Attachments " +
"AS n " +
"ON m.Recordid = n.Recordid " +
"ORDER BY Title ASC"
If DatabaseQuery(Program\DatabaseHandle, QueryString.s) <> #DatabaseQueryFail
; Process the next returned row
While NextDatabaseRow(Program\DatabaseHandle)
; Have we created this XML tree already?
If *XmlTree = #False ; We haven't created this xml before so do so now
*XmlTree = CreateXML(#PB_Any) ; Creates a new empty XML tree identified by the #XML number
*RootXmlNode = CreateXMLNode(RootXMLNode(*XmlTree), #Empty$) ; Creates a new XML node and inserts it into the given parent node.
SetXMLNodeName(*RootXmlNode, "MyInfoList") ; Changes the tagname of the given XML node (root in this case)
EndIf ; No more tests to run
; Creates a new XML node and inserts it into main XML node of the tree.
*RowNode = CreateXMLNode(MainXMLNode(*XmlTree), "MyInfoData")
; If the main node was created, dump all the columns using the column names as node names
If *RowNode
; Get the column names and write them first
NumberOfColumns.i = DatabaseColumns(Program\DatabaseHandle)
; Iterate through the columns
For ColumnNamesLoop.i = 0 To NumberOfColumns.i -1
CurrentColumn.s = DatabaseColumnName(Program\DatabaseHandle, ColumnNamesLoop.i)
*ColumnNode = CreateXMLNode(*RowNode, CurrentColumn.s) ; Creates a new XML node and inserts it into main XML node of the tree.
If DatabaseColumnType(Program\DatabaseHandle, ColumnNamesLoop.i) = #PB_Database_Blob
AttachmentLength.i = DatabaseColumnSize(Program\DatabaseHandle, ColumnNamesLoop.i)
If AttachmentLength.i
*AttachmentBlobBuffer = AllocateMemory(AttachmentLength.i)
If *AttachmentBlobBuffer
BlobResult.i = GetDatabaseBlob(Program\DatabaseHandle, ColumnNamesLoop.i, *AttachmentBlobBuffer, AttachmentLength.i)
; Enough room for message size and expansion
If BlobResult.i
EncodedSize.i = AttachmentLength.i * 1.35
If EncodedSize.i < 64
EncodedSize.i = 64
EndIf
; Now create the encoding space buffer
*EncodedBuffer = AllocateMemory(EncodedSize.i)
; Proceed only if you got an encoding buffer
If *EncodedBuffer
; Encode the text now
ActualBytesRead.i = Base64DecoderBuffer(*AttachmentBlobBuffer, AttachmentLength.i, *EncodedBuffer, EncodedSize.i)
; Did we get anything decoded?
If ActualBytesRead.i
SetXMLNodeText(*ColumnNode, PeekS(*EncodedBuffer, ActualBytesRead.i, #PB_Ascii)))
Else
SetInfoBarArea("Headings", "Error", " No bytes of data were found encoded in the buffer", #PB_Compiler_Procedure)
EndIf
; Free encoding space
FreeMemory(*EncodedBuffer)
EndIf
; Free the file buffer space
FreeMemory(*AttachmentBlobBuffer)
EndIf
Else
SetInfoBarArea("Headings", "Error", "Could not allocate a memory area for the blob data", #PB_Compiler_Procedure)
EndIf
Else
SetInfoBarArea("Headings", "Error", "Could not get the attachment data size from the database", #PB_Compiler_Procedure)
EndIf
Else
SetXMLNodeText(*ColumnNode, TextToXml(GetDatabaseString(Program\DatabaseHandle, ColumnNamesLoop.i)))
EndIf
Next ; Next column
; Main node wasn't created, exit out of this procedure
Else
SetInfoBarArea("Headings", "Error", "Could not create the export data file.", #PB_Compiler_Procedure)
ProcedureReturn
EndIf
Wend
FinishDatabaseQuery(Program\DatabaseHandle)
If *XmlTree
FormatXML(*XmlTree, #PB_XML_ReFormat)
SaveXML(*XmlTree, Program\BackupDirectory + "MyInfo_Export.xml")
FreeXML(*XmlTree)
SetInfoBarArea("Headings", "Info", "Data exported to " + Program\BackupDirectory, #PB_Compiler_Procedure)
EndIf
Else
SetInfoBarArea("Headings", "Error", "The database query failed or was empty. " + DatabaseError(), #PB_Compiler_Procedure)
EndIf
EndProcedure