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

From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
Date: Thu, 06 Jul 2017 13:23:36 -0400
Message-ID: <ojlrfi$5pu$1_at_dont-email.me>


Peter H. Coffin wrote:

> On Wed, 05 Jul 2017 17:02:45 -0400, Lew Pitcher wrote:

>>   mysql_stmt_init()
>>   mysql_stmt_prepare()
>>   mysql_stmt_bind()		for my selection value

>
> mysql_stmt_bind_param()
>
>>   mysql_stmt_execute()
>>   mysql_stmt__bind()		for my returned data

>
> mysql_stmt_bind_result()
>
>>   mysql_fetch()
>>
>> 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.

>
> I'm as familiar with the C API as I'd like to be, but I THINK you can
> get that by storing your result and then fetching the field and looking
> in the MYSQL_FIELD structure.
>
> Doing it via sql gets the following:
>
> mysql> select *,count(*) from TableName1 group by id ;
[snip]
> | count(*) | bigint(21) | NO | | 0 | |
> +------------+------------+------+-----+---------+-------+
> 3 rows in set (0.01 sec)
>

Ok, so it appears that COUNT() returns a bigint. That answers one question.

I tried to prototype my process out using prepared statement calls, and I get quite a big bit of code. I still believe that, for my purposes, the mysql_query() api (and my wrapper to it) is /alot/ simpler.

I have an answer to your previous question:
> What's "overkill" about using two statements instead of one?

In the processes I work with, the input data is guaranteed (and edited, outside of mysql) to not have unintended side effects. Of the two input data items, one is a character string *guaranteed* (by input edit code) to contain digit values only, and (also guaranteed, again by the edit code) to not exceed the length of the database column it belongs to. The other is a small integer value, guaranteed (by the input edit code) to range between -1 and 2.

For my purposes, this means that I only need, at most, a single sprintf() (or equivalent) and a mysql_query() call to execute each SQL query. That's /one/ mysql_*() function call per sql statement. And, 3 mysql_*() function calls per sql statement to retrieve the returned data.

OTOH, the "prepared statement" approach requires me to have four or five mysql_*() function calls to execute each SQL query (4 if only binding parameters or results, 5 if binding both), and 1 mysql_*() function to retrieve the returned data.

So, 4 statements for mysql_query(), and between 5 and 6 statements for the prepared statement approach. That's hardly "using two statements instead of one". But, let's add the initialization the programmer must to to the MYSQL_BIND structure for eached parameter or result passed. That adds a lot of additional statements (I counted 7 MYSQL_BIND elements for each data item) whether you do it as a structure initialization or as program logic. But the sprintf()/mysql_query() approach needs none of that.

Thus, my statement that
> but for the code I'm writing, that [prepared statements] would be
> overkill.

I have nothing against prepared statements; they provide a very needed interface to the SQL engine. But /for the code I'm writing/, the overhead of building, binding, executing, and extracting data from a prepared statement is more that I need to do.

And, the formatted_mysql_query() code I posted provides a simple interface to the facility /I/ needed.

Hope this explains things.

-- 
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request
Received on Thu Jul 06 2017 - 19:23:36 CEST

Original text of this message