Need change compression of images stored in SQLite database with XOJO but getting error during process

Question

I have a table that stores instruction book pages. while working on the app and finished adding an instuction book, I found that that the table was over 200MB, however the PDF containing the pages was only 70MB 438 Pages.

The problem is that it saves the images as max quality. now i want to make a script that goes over every record opens the images and saves it again as medium compression.

I've made a record set and a loop to go through each record to change the compression, but but the app crashes half way the process.

No matter how i Change the code, it always crashes.

So i took an other approach and make a for loop of 200 records. This wend well, but it didn't change the file size of the database???

The runtime error is an UnsupportedOperationException, as shown here:

This is the code:

  dim rs as RecordSet
  rs=lego.LegoData.SQLSelect("SELECT * FROM Books")
  dim resize as Picture
  while not rs.EOF
    resize = picture.FromData(rs.Field("intructions").StringValue)
    rs.Edit
    rs.Field("intructions").StringValue = resize.GetData(Picture.FormatJPEG, Picture.QualityMedium)
    rs.Update
    rs.MoveNext
  wend

Somehow it reads NIL after 200 records, but it's not NIL. The error is not happening every time at the same record, it has its own will?

Any suggestions? I want to build-in a book image compression function as well so people can make the exported manual smaller.

Thomas Tempelmann · Answer 1 · 2017-02-04T11:17:12.633

The database file does not automatically get smaller if you remove or compact data inside it. You have to issue the VACCUM command (e.g. with SQLExecute("VACUUM")). You can issue that command only after committing, so first do a SQLExecute("COMMIT"). Or use a SQLite tool such as SQLVue to do that by hand.
If resize.GetData returns nil, it means that it can't read the data as JPEG. Maybe it's not in JPEG data but a GIF or something else. Try loading the data into a string first and look at it in the debugger, using the Binary (hex bytes) view to see what's up with it.

If you get an exception, wrap the code in an try block to keep the app from stopping, like this:

try
  resize = picture.FromData(rs.Field("intructions").StringValue)
catch exc as RuntimeException
  // The image could not be loaded - let's skip it
  rs.MoveNext
  continue
end try

score 1 · Answer 2 · answered Jan 30 '17 at 01:55

I've found a solution to make the code faster, still having the crash OutofMemorry exception

  dim rs as RecordSet
  dim pic as RecordSet
  rs=lego.LegoData.SQLSelect("SELECT ID, SETID, Page FROM Books")
  dim resize as Picture
  dim count as Integer = 0
  while not rs.EOF
    pic = LegoData.SQLSelect("Select ID, Intructions FROM Books WHERE ID = " + Str(rs.Field("ID").IntegerValue))
    if pic <> nil then
      if len(pic.Field("intructions").StringValue) > 0 then
        resize = picture.FromData(pic.Field("intructions").StringValue)
        pic.Edit
        pic.Field("intructions").StringValue = resize.GetData(Picture.FormatJPEG, Picture.QualityMedium)
        pic.Update
        if count = 100 then
          LegoData.SQLExecute("COMMIT")
          LegoData.SQLExecute("VACUUM")
          count = 0
        else
          count = count + 1
        end if
      end if
    end if
    rs.MoveNext
  wend
  LegoData.SQLExecute("COMMIT")
  LegoData.SQLExecute("VACUUM")

Before the crash its start updating my images with Nil like 15 images before it crash

I added a LIMIT to the sql to do only 200 rows, and changed it manually to go through the whole table 800+ rows. My database is smaller now, and was a quick workaround for now. no error so this means its not because corrupt data — Berry, Jan 30 '17 at 02:12
It may be that Xojo is having a memory leak here. I suggest you ask in the Xojo Forum for help instead. This is not the right place for figuring out bugs. — Thomas Tempelmann, Feb 04 '17 at 11:13

Need change compression of images stored in SQLite database with XOJO but getting error during process

2 Answers2