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

From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
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 request
Received on Wed Jul 05 2017 - 23:02:45 CEST

Original text of this message