Can anyone do me a favour and see if they can spot where I might have broken the loop?
Code: Select all
; Convert the database selected by the user, import all the records
Procedure ConvertDatabaseNow()
;
NewNumberOfItems.i = #False
;
If Program\AttachedDatabase <> #Empty$
;
SetInfoBarArea("Headings", "Info", "Starting to convert and import the attached database, please wait a while", #PB_Compiler_Procedure)
;
AttachedDatabaseQuery.s +
"SELECT * " +
"FROM RemoteDatabase.Keeper " +
"AS m " +
"LEFT JOIN RemoteDatabase.Attachments " +
"AS n " +
"ON m.Record = n.Record"
;
If DatabaseQuery(Program\DatabaseHandle, AttachedDatabaseQuery.s) <> #DatabaseQueryFail
;
While NextDatabaseRow(Program\DatabaseHandle)
;
MyInfo\Title = KillQuote(GetDatabaseString(Program\DatabaseHandle, 0)) : Debug MyInfo\Title
MyInfo\Information = KillQuote(GetDatabaseString(Program\DatabaseHandle, 1)) : Debug MyInfo\Information
MyInfo\Category = KillQuote(GetDatabaseString(Program\DatabaseHandle, 2)) ;
MyInfo\Archived = GetDatabaseString(Program\DatabaseHandle, 3) ;
MyInfo\Favourite = GetDatabaseString(Program\DatabaseHandle, 4) ;
MyInfo\Locked = GetDatabaseString(Program\DatabaseHandle, 5) ;
MyInfo\Deleted = GetDatabaseString(Program\DatabaseHandle, 6) ;
MyInfo\Updated = GetDatabaseString(Program\DatabaseHandle, 7) ;
MyInfo\Owner = KillQuote(GetDatabaseString(Program\DatabaseHandle, 8)) ;
MyInfo\Attachments = GetDatabaseString(Program\DatabaseHandle, 9) ;
MyInfo\Recordid = GetDatabaseString(Program\DatabaseHandle, 10) ;
Attachmentid.s = GetDatabaseString(Program\DatabaseHandle, 11) ; This is the Attachments autoincrement field
AttachmentLength.i = DatabaseColumnSize(Program\DatabaseHandle, 12) ; This is the attachment blob data
AttachmentRecordid.s = GetDatabaseString(Program\DatabaseHandle, 13) ; The linked record id of the attachment
; Workaround to strip rtf data from the old Keeper information field
SetGadgetText(#Gadget_Convert_eStripRtfData, MyInfo\Information)
MyInfo\Information = GetGadgetText(#Gadget_Convert_eStripRtfData)
;
MyInfoInsertString.s +
"INSERT INTO MyInfo(" +
"Title, " +
"Information, " +
"Category, " +
"Archived, " +
"Favourite, " +
"Locked, " +
"Deleted, " +
"Updated, " +
"Owner, " +
"Attachments) " +
"VALUES(" +
"'" + RepQuote(MyInfo\Title) + "'" +
", '" + RepQuote(MyInfo\Information) + "'" +
", '" + RepQuote(MyInfo\Category) + "'" +
", '" + RepQuote(MyInfo\Archived) + "'" +
", '" + RepQuote(MyInfo\Favourite) + "'" +
", '" + RepQuote(MyInfo\Locked) + "'" +
", '" + RepQuote(MyInfo\Deleted) + "'" +
", '" + RepQuote(MyInfo\Updated) + "'" +
", '" + RepQuote(MyInfo\Owner) + "'" +
", '" + "0" + "'" +
")"
;
If DatabaseUpdate(Program\DatabaseHandle, MyInfoInsertString.s) <> #DatabaseUpdateFail
;
NewRecordid.s = DatabaseLastInsertRowId()
;
If NewRecordId.s <> #Empty$
;
AddGadgetItem(#Gadget_Convert_Titles, #AtTheEndOfTheList, KillQuote(MyInfo\Title) + #LF$ + "Added" + #LF$ + NewRecordId.s)
;
NewNumberOfItems.i + 1
;
LastLine(#Gadget_Convert_Titles, NewNumberOfItems.i - 1)
; Get the blob from the database and save it to MyInfo Attachments table relinked
If AttachmentLength.i <> #NoDataFound
*AttachmentBlobBuffer = AllocateMemory(AttachmentLength.i)
BlobResult.i = GetDatabaseBlob(Program\DatabaseHandle, 12, *AttachmentBlobBuffer, AttachmentLength.i)
If BlobResult.i <> #NoBlobFound
; Write this blob to the MyInfo database attachments table
SetDatabaseBlob(Program\DatabaseHandle, 0, *AttachmentBlobBuffer, AttachmentLength.i)
AttachmentUpdate.s +
"INSERT INTO Attachments (" +
"Attachmentid, " +
"Attachment, " +
"Filename) " +
"VALUES('" +
NewRecordid.s + "', " +
"? " + ", '" +
#Empty$ + "' " +
")"
If DatabaseUpdate(Program\DatabaseHandle, AttachmentUpdate.s) <> #DatabaseUpdateFail
SetInfoBarArea("Headings", "Info", "Saved an attachment for " + KillQuote(MyInfo\Title), #PB_Compiler_Procedure)
Else
SetInfoBarArea("Headings", "Error", "No attachment saved for " + KillQuote(MyInfo\Title), #PB_Compiler_Procedure)
EndIf
Else
SetInfoBarArea("Headings", "Info", "No attachment found for " + KillQuote(MyInfo\Title), #PB_Compiler_Procedure)
EndIf
Else
SetInfoBarArea("Headings", "Error", "Could not retrieve the attachment from the old Keeper database ", #PB_Compiler_Procedure)
EndIf
; Update the category table now
NewCategoryInsert.s +
"INSERT INTO Categories(" +
"Category, " +
"Itemcount, " +
"Iconname) " +
"VALUES(" +
"'" +
RepQuote(MyInfo\Category) +
"', '" +
"0" +
"', '" +
#Empty$ +
"')"
If DatabaseUpdate(Program\DatabaseHandle, NewCategoryInsert.s) <> #DatabaseUpdateFail
UpdateCategoryItemCount(MyInfo\Category)
Else
SetInfoBarArea("Headings", "Error", "Database table 'Categories' failed to be updated " + DatabaseError(), #PB_Compiler_Procedure)
EndIf
; Update the Owners table now
NewOwnerInsert.s +
"INSERT INTO Owners(" +
"Owner, " +
"Iconname, " +
"Itemcount) " +
"VALUES('" +
RepQuote(MyInfo\Owner) +
"', " +
"'" +
#Empty$ +
"', " +
"'0')"
If DatabaseUpdate(Program\DatabaseHandle, NewOwnerInsert.s) <> #DatabaseUpdateFail
UpdateOwnerItemCount(MyInfo\Owner)
Else
SetInfoBarArea("Headings", "Error", "Database table 'Owners' failed to be updated " + DatabaseError(), #PB_Compiler_Procedure)
EndIf
;
If Toggle\MessageSave = #True
MessageRequesterEBS("New Item", "New item: " + KillQuote(MyInfo\Title) + " has been saved to the database", #MB_ICONINFORMATION | #PB_MessageRequester_Ok)
;MessageRequester("New Item", "New item: " + KillQuote(MyInfo\Title) + " has been saved to the database", #PB_MessageRequester_Ok)
EndIf
;
Else
SetInfoBarArea("Headings", "Error", "No record number created " + DatabaseError(), #PB_Compiler_Procedure)
AddGadgetItem(#Gadget_Convert_Titles, #AtTheEndOfTheList, KillQuote(MyInfo\Title) + #LF$ + "Skipped" + #LF$ + NewRecordId.s)
NewNumberOfItems.i + 1
LastLine(#Gadget_Convert_Titles, NewNumberOfItems.i - 1)
EndIf
;
Else
SetInfoBarArea("Headings", "Error", "Database table failed to be updated " + DatabaseError(), #PB_Compiler_Procedure)
EndIf
; Clean out all the variables just to be certain
ClearStructure(@Myinfo, MyinfoData)
AttachedDatabaseQuery.s = #Empty$
MyInfoInsertString.s = #Empty$
AttachmentUpdate.s = #Empty$
NewCategoryInsert.s = #Empty$
NewOwnerInsert.s = #Empty$
Attachmentid.s = #Empty$
AttachmentLength.i = #False
AttachmentRecordid.s = #Empty$
NewRecordid.s = #Empty$
BlobResult.i = #False
SetGadgetText(#Gadget_Convert_eStripRtfData, #Empty$)
If *AttachmentBlobBuffer
FreeMemory(*AttachmentBlobBuffer)
EndIf
;
Wend
;
FinishDatabaseQuery(Program\DatabaseHandle)
;
Else
SetInfoBarArea("Headings", "Error", "The database query failed or was empty: " + DatabaseError(), #PB_Compiler_Procedure)
MessageRequesterEBS("Database conversion", "The database failed to be converted" + #CRLF$ + #CRLF$ + DatabaseError(), #MB_ICONINFORMATION | #PB_MessageRequester_Ok)
EndIf
;
Else
SetInfoBarArea("Headings", "Error", "There is currently no attached database selected for conversion", #PB_Compiler_Procedure)
MessageRequesterEBS("No database attached", "There is currently no attached database selected for conversion", #MB_ICONINFORMATION | #PB_MessageRequester_Ok)
EndIf
;
EndProcedure