Binding SQLite Parameters directly by Name

Question

I recently - very recently - started learning how to program for iOS, and have been stumped by what appears (to me) to be a blatant oversight in SQLite3. Let me qualify that by saying that prior to last week I had zero (practical) experience with Macs, Objective C, Xcode, iOS or SQLite, so I have no delusions about waltzing into field of tried-and-true tools and finding obvious errors on my first try. I assume there's a good explanation.

However, after spending the last few months using SQL Server, MySQL, and PostgreSQL, I was amazed to discover that SQLite doesn't have better functionality for adding parameters by name. Everything I could find online (documentation, forums [including SO]) says to assign parameters using their integer index, which seems like it would be a pain to maintain if you ever modify your queries. Even though you can name the parameters in your statements and do something like

sqlite3_bind_int(stmt, sqlite3_bind_parameter_index(stmt, "@my_param"), myInt);

no one seems to do that either. In fact, no one seems to try to automate this at all; the only alternate approach I could find used a parameter array and a loop counter, and inspected each parameter to determine which object type to insert. I originally considered a similar approach, but a) my boss's stance is that database parameters should always be type checked (and I agree, although I realize that SQLite fields aren't strongly typed and I technically could do it anyways), b) it felt like an inelegant hack, and c) I assumed there was a reason this approach wasn't widely used. So:

1) Why aren't there binding methods in SQLite that accept a parameter name (as, say, a 'const char')? Or are there and I'm missing something?

2) Why doesn't anyone seem to use an approach like the example above?

I dug in the source code a little and think I could easily modify the library or just write my own (typed) class methods that would do the above for me, but I'm assuming there's a reason no one has built this into SQLite yet. My only guess is that the additional memory and cycles needed to find the parameter index are too precious on an [insert iDevice here], and aren't worth the convenience of being able to use parameter names . . . ?

Any insight would be appreciated.

Answer 1

1. There are; it's the sqlite3_bind_parameter_index() function you mentioned that you use to turn a parameter name into an index, which you can then use with the sqlite3_bind_*() functions. However, there's no sqlite3_bind_*_by_name() function or anything like that. This is to help prevent API bloat. The popular Flying Meat Database sqlite wrapper has support for named parameters in one of its branches, if you're interested in seeing how it's used.

   If you think about what it would take to implement full named parameter binding methods, consider the current list of bind functions:

   int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
   int sqlite3_bind_double(sqlite3_stmt*, int, double);
   int sqlite3_bind_int(sqlite3_stmt*, int, int);
   int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
   int sqlite3_bind_null(sqlite3_stmt*, int);
   int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
   int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
   int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
   int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);
   

   If we wanted to add explicit support for named parameters, that list would double in length to include:

   int sqlite3_bind_name_blob(sqlite3_stmt*, const char*, const void*, int n, void(*)(void*));
   int sqlite3_bind_name_double(sqlite3_stmt*, const char*, double);
   int sqlite3_bind_name_int(sqlite3_stmt*, const char*, int);
   int sqlite3_bind_name_int64(sqlite3_stmt*, const char*, sqlite3_int64);
   int sqlite3_bind_name_null(sqlite3_stmt*, const char*);
   int sqlite3_bind_name_text(sqlite3_stmt*, const char*, const char*, int n, void(*)(void*));
   int sqlite3_bind_name_text16(sqlite3_stmt*, const char*, const void*, int, void(*)(void*));
   int sqlite3_bind_name_value(sqlite3_stmt*, const char*, const sqlite3_value*);
   int sqlite3_bind_name_zeroblob(sqlite3_stmt*, const char*, int n);
   

   Twice as many functions means a lot more time spent maintaining API, ensuring backwards-compatibility, etc etc. However, by simply introducing the sqlite3_bind_parameter_index(), they were able to add complete support for named parameters with only a single function. This means that if they ever decide to support new bind types (maybe sqlite3_bind_int128?), they only have to add a single function, and not two.

2. As for why no one seems to use it... I can't give any sort of definitive answer with conducting a survey. My guess would be that it's a bit more natural to refer to parameters sequentially, in which case named parameters aren't that useful. Named parameters only seem to be useful if you need to refer to parameters out of order.