RE: 4.1.3 and prepared statements

[Rick Robinson]

Looks like my attachment did not make it to the post.  So here's the code in
question - any thoughts?:
 
<code>
#include <iostream>
#include <cstdlib>
#include <cstring>
#include <cstdio>
#include "mysql.h"
using namespace std;
int
main(int argc, char *argv[])
{
    cout << "start up" << endl;
    MYSQL * connection;
    connection = mysql_init(0);
    connection = mysql_real_connect(connection, 0, "sfwd", "sfwd", "sfwd",
0, 0,0);
    if (0 == connection)
    {
        cerr << "connect failed:" << mysql_error(connection) << endl;
        return -1;
    }
    static const char * sql_statement =
        "select property_value from cfg_2 "
        "where config_key=? and section_key=? and property_key=?";
    MYSQL_STMT * stmt_handle = mysql_stmt_init(connection);
    if (0 == stmt_handle)
    {
        cerr << "stmt init failed:" << mysql_error(connection) << endl;
        return -1;
    }
    int mysql_return_code = mysql_stmt_prepare(stmt_handle,
                                               sql_statement,
                                               strlen(sql_statement)
                                               );
    if (0 != mysql_return_code)
    {
        cerr << "stmt prepare failed:" << mysql_stmt_error(stmt_handle) <<
endl;
        mysql_stmt_close(stmt_handle);
        return -1;
    }
 
    MYSQL_BIND bind_var[3];
    unsigned long var_length[3];
    my_bool not_null = 0;
    char config_key[31] = {'\0'};
    char section_key[31] = {'\0'};
    char property_key[256] = {'\0'};
 
    bind_var[0].buffer_type = MYSQL_TYPE_VAR_STRING;
    bind_var[0].buffer = (char *)config_key;
    bind_var[0].buffer_length = sizeof(config_key);
    bind_var[0].length = &var_length[0];
    bind_var[0].is_null = &not_null;
 
    bind_var[1].buffer_type = MYSQL_TYPE_VAR_STRING;
    bind_var[1].buffer = (char *)section_key;
    bind_var[1].buffer_length = sizeof(section_key);
    bind_var[1].length = &var_length[1];
    bind_var[1].is_null = &not_null;
 
    bind_var[2].buffer_type = MYSQL_TYPE_VAR_STRING;
    bind_var[2].buffer = (char *)property_key;
    bind_var[2].buffer_length = sizeof(property_key);
    bind_var[2].length = &var_length[2];
    bind_var[2].is_null = &not_null;
 
    strncpy(config_key, "svp001", sizeof(section_key)-1);
    strncpy(section_key, "stats", sizeof(section_key)-1);
    strncpy(property_key, "number.of.threads", sizeof(property_key)-1);
 
    mysql_return_code = mysql_stmt_bind_param(stmt_handle, bind_var);
    if (0 != mysql_return_code)
    {
        cerr << "stmt bind failed:" << mysql_stmt_error(stmt_handle) <<
endl;
        mysql_stmt_close(stmt_handle);
        return -1;
    }
 
    mysql_return_code = mysql_stmt_execute(stmt_handle);
    if (0 != mysql_return_code)
    {
        cerr << "stmt execute failed:" << mysql_stmt_error(stmt_handle) <<
endl;
        mysql_stmt_close(stmt_handle);
        return -1;
    }
 
    char property_value[256] = {'\0'};
    MYSQL_BIND result_var[1];
    unsigned long result_var_length[1];
    my_bool is_null[1];
 
    result_var[0].buffer_type = MYSQL_TYPE_VAR_STRING;
    result_var[0].buffer = (char *)property_value;
    result_var[0].buffer_length = sizeof(property_value)-1;
    result_var[0].is_null = &is_null[0];
    result_var[0].length = &result_var_length[0];
 
    mysql_return_code = mysql_stmt_bind_result(stmt_handle, result_var);
    if (0 != mysql_return_code)
    {
        cerr << "stmt bind rslt failed:" << mysql_stmt_error(stmt_handle) <<
endl;
        mysql_stmt_close(stmt_handle);
        return -1;
    }
 
    mysql_return_code = mysql_stmt_store_result(stmt_handle);
    if (0 != mysql_return_code)
    {
        cerr << "stmt store rslt failed:" << mysql_stmt_error(stmt_handle)
<< endl;
        mysql_stmt_close(stmt_handle);
        return -1;
    }
 
    mysql_return_code = mysql_stmt_fetch(stmt_handle);
    if (0 != mysql_return_code && MYSQL_NO_DATA != mysql_return_code)
    {
        cerr << "stmt fetch failed:" << mysql_stmt_error(stmt_handle) <<
endl;
        mysql_stmt_free_result(stmt_handle);
        mysql_stmt_close(stmt_handle);
        return -1;
    }
 
    if (MYSQL_NO_DATA != mysql_return_code)
    {
        cout << "stmt - got: " << property_value << endl;
    }
    mysql_stmt_free_result(stmt_handle);
    mysql_stmt_close(stmt_handle);
    cout << "done" << endl;
    return 0;
}
</code>
 

  _____  

From: Rick Robinson [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 26, 2004 6:24 PM
To: Mysql
Subject: 4.1.3 and prepared statements



Hi all- 
Is the MySQL C API for prepared statements working at all in 4.1.3?  I'm
trying to do a relatively simple select test, and I consistently get an
error on execute = "MySQL client run out of memory".  But it's a singleton
select of one column and the first time I execute?

If this doesn't work, I'm very afraid... 

I've attached my simple code. 

Here's the table: 

create table cfg_2 
(config_key varchar(30) not null, 
section_key varchar(30) not null, 
property_key varchar(255) not null, 
property_value varchar(255), 
primary key(config_key, section_key, property_key) 
) engine=myisam; 

insert into cfg_2 values('svp001','stats','number.of.threads','2'); 

Some other notes:
Solaris 9 Sparc, gcc 3.3.2, using 32 bit, max binary version of mysql for
Solaris 9, linked with reentrant client lib (libmyclient_r), using a
my-medium.cnf config.


Should I just bag the whole prepared statement thing until it gets stable?
And when would that be? 

Thx, 
Rick 
<<...>>