I have an SQLite Database from another program I wrote in another language (Lua).
The problem is many people want a Mac version and the program I wrote it in
does not do Mac, so now I am looking for an alternative software to write a
Mac version. I need the program to be able to use the SQLite database thats
in the windows version. Thats why I am researching LiveCode.
Anyway My biggest problem is BLOBS. My Windows version of the program can store
many pictures for each record. I can not seem to get a picture out from any of
the records. I can get any data out with out a problem just not blobs (images).
I have searched the forums for answers on SQLite Blobs and all seem pretty vague.
Does anyone have any sample code THAT WORKS for retrieving pictures from an SQLite
database ? Does the image have to be entered in the base64 coding ?
Below is a sample of the database table I use in my windows program.
Code: Select all
Table = CGPictures
Fields are: RecNo ## Created by SQLite
CGID ## This is the ID to match all table and fields for each record
Pic1 ## This field is used to determine if the picture is the default
ImageID ## This field is used for the image id as many can be added for the same record
PicFileName ## This is the name of the pictures file name when it was imported
ImageTitle ## This field is used to store a description of the picture
Image ## This is the picture itself.Below is the code used to read and write Blobs in my old language. It sure is different from Lua
to LiveCode. I included it just in case some questions were asked how I did it in Lua.
Code: Select all
--########################################################################################################
-- Write Image to Blob Routine Section
--########################################################################################################
----------------------------------------------------------------------------------------------------------
--- Function
function WriteOutDefaultPicture()
sPicImage = _SourceFolder.."\\Tucglog\\Images\\CGLogPic.jpg"
if (db:isopen()) then
-- Database routine
if File.DoesExist(sPicImage) then
-- Delete the CGLogPic.* from the database
db:exec("DELETE FROM CGLogPics WHERE ImageID = 0")
-- Delete the current CGLogPic.* file from the folder
File.Delete(TUCGLogDatabase.."\\CGLData\\CGLogPic.*", false, false, true, nil);
DefaultLogoPic = "CGLogPic.jpg"
local inn = assert(io.open(sPicImage, "rb"))
local blob = inn:read("*all")
local q = "INSERT INTO CGLogPics (ImageID, PicFileName, Image) VALUES(0, '"..DefaultLogoPic.."', @P1)"
local stm = db:prepare(q)
if stm then
stm:bind_blob(1, blob)
stm:step()
stm:finalize()
inn:close()
-- Tell user it was saved
Dialog.TimedMessage("Success", "Original Picture was added to the database", 3000, MB_ICONINFORMATION);
WriteCGLogPic()
Page.Jump("WelcomePage");
else
Dialog.Message("Failed", "Failed to create prepared statement.\r\nOrigimal Picture was not replaced.")
end
end
end
end -- End of function
----------------------------------------------------------------------------------------------------------
--########################################################################################################
-- Write Out (Get from Blob) Pictures Routine Section
--########################################################################################################
----------------------------------------------------------------------------------------------------------
--- Function 5
--- Writes out CGLogPic.jpg to CGLData's folder
function WriteCGLogPic()
local q = "SELECT PicFileName, Image FROM CGLogPics WHERE ImageID = 0"
for row in db:nrows(q) do
PicFileName = (row.PicFileName)
output_file = io.open(sProgramPicturePath..PicFileName, "wb")
output_file:write(row.Image)
output_file:close()
end
end