Today’s subject: SQLite
I’m a novice programmer. As such, I often find that a lot of the sample code and documentation I find online goes over my head. I can’t be the only one for whom this is true.
Thus, I thought I’d post a little sample application I wrote that demonstrates some of the basic functionality of SQLite, the “self-contained, serverless, zero-configuration, transactional SQL database engine.”
We’re using SQLite to track materials data in our GENIUS application, but this sample program, which I wrote for practice, creates two music-related tables: an iTunes-like table of song information and a table of phone numbers of Madison-area clubs (stored as arrays of integers to demonstrate how to handle blobs).
It doesn’t do much and certainly shouldn’t be used for any real applications, but I nevertheless hope that you find this program helpful if you’re trying to learn SQLite. I try to explain things as completely as possible, but I didn’t want to annotate the arguments for every function call, so you’ll need to consult the SQLite documentation as well. Finally, special thanks to the author of this example, which I unfortunately found much too late.
Looking good, Kyle
Your blob code is almost right. It will fail if you try to query for Shank Hall’s phone number, though. When you call sqlite3_bind_blob(), you give it sizeof(ccNum), which is 8 * sizeof(int) = 32. But when currNum is fcNum, you need to store 11 * sizeof(int) = 44 bytes.
You only write 32 bytes of data, and so you only fetch 32 bytes, but when you print out the number, you use the first element of the array to tell you how many more items there are. You’ll read 10 even though there are only (32 – 4) / 4 = 7 elements remaining. i will reach 8 and read beyond the end of the buffer.
Rather than use sizeof(ccNum) when you call sqlite3_bind_blob(), use the first element of the array to calculate the size:
(currNum[0] + 1) * sizeof(int)
Thanks, Rob. Correction appended.