Eric Day wrote:
Hi!
We've decided to add the prepared statement API sooner than later,
By "we" I assume you mean you and Brian? :)
and I've been looking into various ways of mapping variables in. There
are simple '?' with order of appearance determining order into the
array of vars, there are '$1', '$2', ... which would allow you to
repeat or do things out of order. Or the SQLite method of '?nnn' or
':aaa' and also allow identifies.
What are folks thoughts on PS APIs? Anything they really love or hate?
I'm not fond of many of them at all...most are overly complex due to
copying early legacy APIs from Oracle or ODBC.
I'd love an API that does NOT ask "regular" developers to understand
what's going on under the hood and figure out what "binding" of results
and parameters is or why there is a need to prepare anything at
all...that crap is so 1992.
I've always felt that the prepared statement APIs (all of them) are
overly complex... I mean, just look at MySQL's C PS API and ODBC's API:
http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-execute.html
http://msdn.microsoft.com/en-us/library/ms714562(VS.85).aspx
Unless I am sorely mistaken, a "regular" developer -- i.e. a developer
of an application that is just trying to Get Things Done -- is generally
working on doing one the following:
1) Get data out of the database
2) Put data into the database
For #1 in 14 years of programming, I've never worked on any application
where I have had the need to "prepare" a SELECT statement that would be
executed more than once in a single connection. Never. If I were to do
so, I would ask myself why I wasn't doing a single SELECT statement to
begin with....
For #2 prepared statements can indeed come in handy, particularly when
doing bulk operations.
Because I never *need* prepared statements for #1, but I *do* use them
for #2, I would prefer to have an API that is clear and concise for both
purposes, and masks the underlying preparation and binding.
For instance, something like this would appeal to me (and by "appeal" I
mean I would prefer a C++ API over the below C API, but for argument's
sake...):
(please excuse any typos...I'm not triple-checking the code...)
drizzle_result_st *drizzle_create_statement
(
drizzle_con_st *connection,
drizzle_statement_st *statement,
const char *query,
drizzle_ret_t *return_code,
uint8_t** records
);
#define drizzle_select(A,B,C,D) drizzle_create_statement( \
(A), \
(B), \
(C), \
(D), \
NULL)
const char *sql_select= "SELECT alias, power FROM villains;"
const char *sql_insert= "INSERT INTO heroes (name, alias) VALUES (?)";
const uint8_t records[3][2]=
{
{(const uint8_t *) "Spidey", (const uint8_t *) "Peter Parker"},
{(const uint8_t *) "Supey", (const uint8_t *) "Clark Kent"}
};
drizzle_ret_t ret;
/* Assume the connection has already been established... */
drizzled_con_st current_con= get_current_connection();
drizzle_statement_st statement_select;
drizzle_statement_st statement_insert;
(void) drizzle_select(¤t_con,
&statement_select,
&sql_select,
&ret);
if (ret != DRIZZLE_RETURN_OK)
{
/* Handle OOM */
}
drizzle_result_st villains;
(void) drizzle_get_result(&statement_select, &villains, &ret);
if (ret != DRIZZLE_RETURN_OK)
{
/* Handle OOM */
}
drizzle_row_st record;
while (DRIZZLE_RETURN_EOF != drizzle_next_record(&villains, &record))
{
fprintf(stdout, "Villain name: %s Power: %s\n",
drizzle_get_column(&record, 0),
drizzle_get_column(&record, 1));
}
drizzle_result_free(&villains);
/* Insert some super-heroes...ALL AT ONCE. */
(void) drizzle_create_statement(¤t_con,
&statement_insert,
&sql_insert,
&ret,
records);
if (ret != DRIZZLE_RETURN_OK)
{
/* Print out errors... */
}
else
{
fprintf(stdout, "Inserted %" PRIu64 " heroes.\n",
drizzle_affected_rows(&statement2
}
drizzle_free_statement(&statement_select);
drizzle_free_statement(&statement_insert);
We're also thinking of restricting vars to strings for now, but
possibly simple INT types. Other types could still be given as vars,
but they'd just be passed in as strings.
I would go with uint8_t * and offer common conversion routines...
Cheers,
Jay
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
