MySQL: プリペアドステートメントを使う

#include <windows.h>

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include <mysql.h>

#define MY_HOST   "127.0.0.1"
#define MY_USER   "root"
#define MY_PASSWD NULL
#define MY_DB     "foo"

int main() {
  MYSQL *my;
  MYSQL_RES *res;
  MYSQL_STMT *stmt;
  MYSQL_BIND bnd[1], rbnd[1];
  char *query = NULL;
  char col_val[BUFSIZ];
  unsigned long col_val_len;
  long param;
  char *errmsg = NULL;

  if ((my = mysql_init((MYSQL *) NULL)) == NULL) {
    errmsg = "mysql_init()";
    goto end;
  }

  if (mysql_real_connect(my, MY_HOST, MY_USER, MY_PASSWD, NULL, MYSQL_PORT, NULL, 0) == NULL) {
    errmsg = "mysql_real_connect()";
    goto end;
  }

  if (mysql_select_db(my, MY_DB) != 0) {
    errmsg = "mysql_select_db()";
    goto end;
  }

  if ((stmt = mysql_stmt_init(my)) == NULL) {
    errmsg = "mysql_stmt_init()";
    goto end;
  }

  query = "select val from bar where id = ?";

  if (mysql_stmt_prepare(stmt, query, strlen(query)) != 0) {
    errmsg = "mysql_stmt_prepare()";
    goto end;
  }

  if ((res = mysql_stmt_result_metadata(stmt)) == NULL) {
    errmsg = "mysql_stmt_result_metadata()";
    goto end;
  }

  memset(bnd , 0 , sizeof(bnd));
  param = 2;
  bnd[0].buffer = &param;
  bnd[0].buffer_length = sizeof(param);
  bnd[0].buffer_type = MYSQL_TYPE_LONG;

  if (mysql_stmt_bind_param(stmt, bnd) != 0) {
    errmsg = "mysql_stmt_bind_param()";
    goto end;
  }

  if (mysql_stmt_execute(stmt) != 0) {
    errmsg = "mysql_stmt_execute()";
    goto end;
  }

  if (mysql_stmt_store_result(stmt) != 0) {
    errmsg = "mysql_stmt_store_result()";
    goto end;
  }

  memset(rbnd , 0 , sizeof(rbnd));
  rbnd[0].buffer = col_val;
  rbnd[0].length = &col_val_len;
  rbnd[0].buffer_type = MYSQL_TYPE_VAR_STRING;
  rbnd[0].buffer_length = sizeof(col_val);

  if (mysql_stmt_bind_result(stmt, rbnd) != 0) {
    errmsg = "mysql_stmt_bind_result()";
    goto end;
  }

  while(mysql_stmt_fetch(stmt) == 0) {
    printf("val:%s(%d)\n", col_val, col_val_len);
  }

  mysql_stmt_close(stmt);

end:
  if (my) {
    mysql_close(my);
  }

  if (errmsg) {
    fprintf(stderr, errmsg);
    exit(1);
  }

  return 0;
}

val:BBB(3)

続行するには何かキーを押してください . . .

おなじSQLでもMySQL/Rubyだと失敗する…と。

require 'rubygems'
require 'mysql'

my = Mysql.new('localhost', 'root', '', 'foo')

begin
  stmt = my.prepare('select val from bar where id = ?')

  stmt.execute(1).each do |row|
    p row
  end

  stmt.close
ensure
  my.close
end

~/work$ ruby foo.rb

foo.rb:9:in `execute': Using unsupported buffer type: 0  (parameter: 1) (Mysql::Error)

        from foo.rb:9