Found a solution to my problem. I will explain what I did here so anyone else can benefit.
(LiveCode 8.1.1)
The conclusions (in my case):
1) In my case the field type needed to be of type GRAPHIC or BINARY, BLOB did not work with storage and retrieval of base64encode image data.
2) When I retrieved multiple records of data with below code into tRec there must NOT be image data in there. In my case I was not able to split the data anymore with tab and return delimiters.
Code: Select all
revDataFromQuery(,,DatabaseID,tSQL) into tRec
3) Putting and retrieving image data into/from the database can only be done one record and one field at a time.
Therefore I first generate the new record and then UPDATE it with the image field (column). Build and used the below command for this.
Be aware that the image data has already been converted with 'put base64encode(image "GroupLogo") into tBase64ImgData' and I have 'put tBase64ImgData into DBImageData'.
Code: Select all
command AddImageInDBFieldV1 DatabaseID, TableName, KeyFields, DataArrayKeyFields, DBImageFieldName,DBImageData @OutResult
set itemdelimiter to ","
put the number of items of KeyFields into NumberOfKeys // Must at least be 1 !!
if NumberOfFields <0 or DBImageFieldName is empty or DatabaseID is empty or TableName is empty or DataArrayKeyFields is empty or DBImageData is empty
then
put "Error" into AddResult
put "One or more parameters are empty !" into AddMsg
exit AddImageInDBFieldV1
end if
set itemdelimiter to ","
put the number of items of KeyFields into nbrofKeyFields
put 1 into counter
repeat for nbrofKeyFields times
put item counter of KeyFields into tFieldname
if counter is 1 then Put tFieldname &" is '" & DataArrayKeyFields[tFieldname] & "'" into AndString
if counter > 1 then put AndString &" and " & tFieldname &" is '" & DataArrayKeyFields[tFieldname] & "'" into AndString
add 1 to counter
end repeat
put "UPDATE "&TableName&" SET "&DBImageFieldName&" = ' "&DBImageData&"' WHERE "&AndString into tQuery
revExecuteSQL DatabaseID, tQuery
put the result into OutResult
end AddImageInDBFieldV1
4) Retrieving the image is (as mentioned) also done per record.
In My case I first get all the records I need without the image data into an array named OutArrayAllRecords , and I know how many records there are (all in another command not shown here). Then I go over each record and retrieve the image "Logo" with 'get revDatabaseColumnNamed(tRecordSet, "Logo", tLogoData) ' and put it into a datagrid array ' theDataA[tGroupID]["GroupLogo"] '. Then I update the data grid. Important to know is that I still need to convert the image data with 'put base64decode(pDataArray["GroupLogo"]) into image "DG_ImageLogo" of me ' in the FillInData section of the data grid behaviour script.
Code: Select all
put 1 into tCounter
repeat for OutNbrRecords times
put OutArrayAllRecords[tCounter]["GroupID"] into tGroupID
put "SELECT * FROM "& TableName&" WHERE GroupID =" & tGroupID & ";" into tSQLString
put revQueryDatabaseBlob(DatabaseID, tSQLString) into tRecordSet
get revDatabaseColumnNamed(tRecordSet, "GroupID", tGroupID)
get revDatabaseColumnNamed(tRecordSet, "Name", tGroupName)
get revDatabaseColumnNamed(tRecordSet, "Status", tGroupStatus)
get revDatabaseColumnNamed(tRecordSet, "Logo", tLogoData)
--
Put tGroupID into theDataA[tGroupID]["GroupID"]
put tGroupName into theDataA[tGroupID]["GroupName"]
put tGroupStatus into theDataA[tGroupID]["GroupStatus"]
put tLogoData into theDataA[tGroupID]["GroupLogo"]
put tCounter+1 into tCounter
revCloseCursor tRecordSet
end repeat
set the dgData of group "DG_ActiveGroups" to theDataA
Maybe not the best way to do this but after hours and hours of try and error only this worked for me.
Remarks, improvements and ideas are very welcome. I think this is not the fastest way because I need to access a record at least twice for write and read if there is an image field in the record. Running the stack I do not see any lag so it is fine for me.
Regards,
Paul
