Jay Pipes wrote:
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....
Jay, that form exists so a database server can cache compiled
statements, which is among the best of all possible optimizations. Not
matter how fast lex, parse, compile, and optimize are, not doing lex,
parse, compile, and optimize is always faster.
As a secondary point, using variables with prepared statements gets you
out of the SQL injection business, including the accidental SQL
injection business. There are numerous MySQL-based web sites where I
can't do business because my street name contains an apostrophe, for one
example.
Anyway, all you have to do is tell an application programmer that a
prepared statement is faster, and the desire to generate SQL strings
inline disappears almost instantly.
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
--
Jim Starkey
Founder, NimbusDB, Inc.
978 526-1376
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
