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>
>
> 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
Date: Wed, 05 Jul 2017 17:05:15 -0400
Message-ID: <ojjk37$go0$2_at_dont-email.me>
Lew Pitcher 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 requestReceived on Wed Jul 05 2017 - 23:05:15 CEST