Re: Programmers toolkit - C api sprintf-like mysql_query()

From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
Date: Wed, 05 Jul 2017 17:05:15 -0400
Message-ID: <ojjk37$go0$2_at_dont-email.me>


Lew Pitcher wrote:

> 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?

>
> 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 = ?;
>
> 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)
>
> mysql_stmt_init()
> mysql_stmt_prepare()
> mysql_stmt_bind() for my selection value
> mysql_stmt_execute()
> mysql_stmt__bind() for my returned data
> mysql_fetch()
Oops... I forgot

    mysql_stmt_close()

>
> 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 request
Received on Wed Jul 05 2017 - 23:05:15 CEST

Original text of this message