Re: mod_dbd and prepared statements (httpd-2.2.9)

[Tom Donovan]

Andrej van der Zee wrote:
Hi,
I did not find a solution, I just stopped using prepared statements
altogether. But I tried to isolate the problem just now, and found somehow
that I cannot use FLOAT in prepared statement somehow (when I tried INT
columns it even segfaults). Below the source code of a mini-module to
illustrate this. This is the table I created in MySQL:

CREATE TABLE simple_table (duration FLOAT NOT NULL) ENGINE=INNODEDB;

And this is what appears in the MySQL log:

Prepare    INSERT INTO simple_table (duration) VALUES (?)
Execute    INSERT INTO simple_table (duration) VALUES ('')

If you want to reproduce it, dont forget to put this in httpd.conf:
LoadModule prep_stmt_module   modules/mod_prep_stmt.so
PrepStmt on

You missed a couple of details:

* apr_dbd_pvquery is only for string values.

  You must use apr_dbd_pvbquery (with a "b") for binary values.

  see: http://apr.apache.org/docs/apr-util/1.3/group___a_p_r___util___d_b_d.html

* You don't pass a float value directly - %f takes a *pointer* to a float.

* It is best not to call ap_dbd_prepare and ap_dbd_acquire directly.

  You should populate your own function pointers at config time using 
APR_RETRIEVE_OPTIONAL_FN.

  If APR_RETRIEVE_OPTIONAL_FN gives you NULL pointers, that means that mod_dbd 
is not loaded.

  When you need to prepare a stmt or acquire a connection, call these function 
pointers.

Here is your code back with these changes (and with all definitions moved to the top of functions so 
I could compile it on Windows).  It works for me with httpd-2.2.10.

Regards,
-tom-



#include <httpd.h>
#include <http_config.h>
#include <http_core.h>
#include <http_log.h>
#include <http_protocol.h>
#include <http_connection.h>
#include <apr_file_info.h>
#include <apr_file_io.h>
#include <apr_dbd.h>
#include <mod_dbd.h>

module AP_MODULE_DECLARE_DATA prep_stmt_module;
static int prep_stmt_write(request_rec *r);
static void (*dbd_prepare_fn)(server_rec*, const char*, const char*) = NULL;
static ap_dbd_t *(*dbd_acquire_fn)(request_rec*) = NULL;

typedef struct prep_stmt_config
{
  int prep_stmt_on;
} prep_stmt_config;

static const char * prep_stmt_config_set_prep_stmt_on(cmd_parms *cmd,
                                                        void *dummy, int flag)
{
  prep_stmt_config *cfg =
    ap_get_module_config(cmd->server->module_config,&prep_stmt_module);
  dbd_prepare_fn (cmd->server,
    "INSERT INTO simple_table (duration) VALUES(%f)", "insert_row");
  cfg->prep_stmt_on = flag;
  return NULL;
}

static int prep_stmt_write(request_rec *r)
{
    int rv, nrows;
    ap_dbd_t * dbd ;
    apr_dbd_prepared_t *prepared ;
    float val = 10.2;

    prep_stmt_config *cfg =
        ap_get_module_config(r->server->module_config,&prep_stmt_module);
    if (!cfg->prep_stmt_on)
        return DECLINED;
    dbd = dbd_acquire_fn(r);
    prepared = apr_hash_get(dbd->prepared, "insert_row",APR_HASH_KEY_STRING);
    if (!prepared) {
        ap_log_error(APLOG_MARK, APLOG_ERR, 0, r->server,
                "DBD Log: Failed to get prepared statement: update_request");
        return DECLINED;
    }

    if (rv = apr_dbd_pvbquery(dbd->driver, r->pool, dbd->handle, &nrows,
                                prepared, &val, NULL)) {
        const char *errmsg = apr_dbd_error(dbd->driver, dbd->handle, rv);
        ap_log_error(APLOG_MARK, APLOG_ERR, 0, r->server,
                "DBD Log: Failed to execute prepared statement: insert_row");
        return DECLINED;
    }
}

static const command_rec prep_stmt_cmds[] = {
    AP_INIT_FLAG("PrepStmt", prep_stmt_config_set_prep_stmt_on,
            NULL, RSRC_CONF, "Enable DBD Log"),
    { NULL }
};

static void prep_stmt_register_hooks(apr_pool_t *p)
{
    ap_hook_log_transaction(prep_stmt_write, NULL, NULL, APR_HOOK_MIDDLE);
}

static void * prep_stmt_create_config(apr_pool_t *pool, server_rec *s)
{
  prep_stmt_config *cfg = apr_pcalloc(pool, sizeof(prep_stmt_config));
  dbd_prepare_fn = APR_RETRIEVE_OPTIONAL_FN(ap_dbd_prepare);
  dbd_acquire_fn = APR_RETRIEVE_OPTIONAL_FN(ap_dbd_acquire);
  return cfg;
}

module AP_MODULE_DECLARE_DATA prep_stmt_module =
{
    STANDARD20_MODULE_STUFF,    /* stuff in every 2.0 mod                */
    NULL,                       /* create per-directory config structure */
    NULL,                       /* merge per-directory config structures */
    prep_stmt_create_config,    /* create per-server config structure    */
    NULL,                       /* merge per-server config structures    */
    prep_stmt_cmds,             /* command apr_table_t                   */
    prep_stmt_register_hooks    /* register hooks                        */
};