Re: Programmers toolkit - C api sprintf-like mysql_query()
Date: Wed, 05 Jul 2017 17:02:45 -0400
Message-ID: <ojjjuh$go0$1_at_dont-email.me>
[Quoted] Peter H. Coffin wrote:
> On Sun, 02 Jul 2017 12:26:08 -0400, Lew Pitcher wrote:
>> I've recently taken up writing some C code to access mysql databases. One
>> tedious part of that process is the preparation of query statements (not
>> "prepared statement" queries, however) using program-supplied values.
>>
>> Typically, to me, that means using sprintf() on a query string like
>> "INSERT name, number VALUES ('%s',%d) INTO table"
>> to plug in the program-supplied values, then executing mysql_query() on
>> the resulting string to actually perform the query.
>>
>> Yes, I know that I could use a "prepared statement", but for the code I'm
>> writing, that would be overkill. However, the sprintf()/mysql_query()
>> sequence occurs often enough (and there doesn't seem to be an equivalent
>> mysql_*() api) that I encapsulated it into a single function.
> > So you want to do something that's almost exactly like using prepared > statements, but doing prepared statements is "overkill"? You're already > doing 95% of the (nominal, very small) amount of extra work involved. > And prepared statements also address the issue Jerry brings up. > > What's "overkill" about using two statements instead of one?
[Quoted] [Quoted] OK, I've never used prepared statements, although I have reviewed the documentation. So, I took a closer look, seeing if I could simplify my original code by using prepared statements instead of mysql_query.
Here's a single query from the process I've automated: SELECT COUNT(SELECT COUNT(icmIcnId) FROM icm WHERE icmCallerId = ?;
[Quoted] Using mysql_query, it takes the following mysql_()* calls to satisfy this query (excluding calls to establish the mysql connection or sanitize the input)...
mysql_query() mysql_store_result() mysql_fetch_row() mysql_free_result()
Now, looking at the equivalent, in a prepared statement form, I get (and please correct me if I am wrong)
[Quoted] mysql_stmt_init() mysql_stmt_prepare() mysql_stmt_bind() for my selection value [Quoted] mysql_stmt_execute() mysql_stmt__bind() for my returned data mysql_fetch()
[Quoted] [Quoted] I have an additional question: for the returned data mysql_stmt_bind(), what buffer_type do I use for an SQL COUNT() value? The manual(s) don't say.
-- Lew Pitcher "In Skills, We Trust" PGP public key available upon requestReceived on Wed Jul 05 2017 - 23:02:45 CEST