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