I've wanted to use SQLite Blobs for storing images for some time, and a new update to an iOS app gave me the opportunity - our Corporate Directory mobile app. I had considered it a few years before but after reading some posts that suggested a better way was to store links in SQLite and the images as normal files, I avoided the process. Now, I'm doing it in Blobs.
However, there have been some hiccups - occasional corrupted data - so I'd like to get this group's collective thought on best practices. I will outline the process without code, which I'll post later if there is a request to do so. 1. The database table has four fields: Employee GUID (varchar), EmployeePic (Blob), isPicAvail (Bool) and picDate (int). 2. When a employee is selected from a search list, a new record is added to the table with only the GUID and a NO for the isPicAvail. 3. If there is connectivity to our intranet via VPN, we send out a web service call to get the employee's photo from SharePoint, async. If no connectivity, we will batch it when connected. 4. When a image comes back as a mime string, it is converted to NSData and stored using UPDATE into the Blob field, along with the unix time in picDate, and we change the isPicAvail to YES. 5. Whenever the app is restarted with connectivity, I do a refresh all photos, which walks the table and fetches images for every record, regardless of whether there is one already stored. I use the same UPDATE call, as above in 4. I don't delete the previous Blob, just assuming that SQLite will take care of those details. NOTE: this process will be optimized later, using modified dates to fetch photos only when needed. The process works well except that occasionally two images end up getting corrupted. Not just one, it always happens in pairs. I get the upper part of both images, with just the lower part showing garbage. The questions: * Is this general approach wise? * Should I clear the old Blob before rewriting the new image, or should I simply delete the old rec and write a new one? * Should I store the mime string coming back in the JSON return rather than converting the string to an image, and then converting it every time we need to display the image. * Should I simply store a string link to the image and load that whenever it is needed. Any thoughts are gratefully accepted. ________________________ Joseph Rooney Team Lead - Mobile Development CommScope, Inc. Hickory, NC 828-315-2920 Ext: 52920
