Jim Starkey wrote:
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).aspxUnless 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 databaseFor #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.
Jim, I'm fully aware of the purpose of prepared statements.
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.
Depends on the implementation of prepared statements (or stored procedures, for that matter).
In MySQL-land, prepared statements are excellent when used appropriately. They can also slow things down in certain environments, like shared-nothing platforms such as PHP.
Look, I wasn't arguing that prepared statements are bad, nor was I arguing that prepared statements aren't necessary. Please read my actual email response :) My response stated that I think the *API* for using prepared statements is needlessly awkward and that if Eric is designing the prepared statement API for libdrizzle he should keep in mind simplicity of use instead of mimicking the awkward legacy APIs.
Cheers, Jay
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
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
