mysql C-API mysql_stmt_bind_param : how to pass binary values in input?

Question

I try to call a procedure with binary parameter in a prepared statement. But it fails in all cases.

All the exemples I could find on web, relate to output binary value , never when input value.

How can I do ?

thanks in advance.

A very simple procedure :

CREATE PROCEDURE `testVarsGet2` (
    IN i_ulv BINARY(12),
  OUT o_cnt INT(11),
    OUT o_errno INT(11))
testVarsGet2:BEGIN
DECLARE t_ulv BINARY(12);

select `ulv` into t_ulv from `testVars`  where `ulv` =i_ulv;

IF  ROW_COUNT() = 0 THEN 
    SET o_errno = -1002;
    LEAVE testVarsGet2;
END IF;
select `cnt` into o_cnt from `testVars`  where `ulv` = i_ulv;

SET o_errno = 0;

LEAVE testVarsGet2;
END;

If ulv is ok, o_errno = 0 , if not o_errno =- 1002

command line :

mysql> call testVarsGet2 (0x7124A57EF08100010000DF3F,@cnt , @err); select  @cnt, @err;
Query OK, 1 row affected (0.00 sec)

+------+------+
| @cnt | @err |
+------+------+
|    1 |    0 |
+------+------+
1 row in set (0.00 sec)

or

mysql> call testVarsGet2 (unhex('7124A57EF08100010000DF3F'),@cnt , @err); select  @cnt, @err;
Query OK, 1 row affected (0.00 sec)

+------+------+
| @cnt | @err |
+------+------+
|    1 |    0 |
+------+------+
1 row in set (0.00 sec)

but in a C/C++ program , if I use statement , it fails

 mysql_stmt_prepare(stmt, "call testVarsGet2 ( unhex(?), ? , ?)", strlen(..));

with 

const char * value = "7124A57EF08100010000DF3F"
len = 24;

    bind[0].buffer_type = MYSQL_TYPE_STRING;
    bind[0].buffer = (char *)value;
    bind[0].buffer_length = 32;
    bind[0].is_null= 0;
    bind[0].length= &len;

or

mysql_stmt_prepare(stmt, "call testVarsGet2 ( ?, ? , ?)", strlen(..));

value = "0x7124A57EF08100010000DF3F"

or with a binary value

const char * value = "7124A57EF08100010000DF3F"
len = 12

unsigned char * value_bin = hex2bin (value );

    bind[0].buffer_type = MYSQL_TYPE_BLOB;
    bind[0].buffer = (char *)value_bin;
    bind[0].buffer_length = 12;
    bind[0].is_null= 0;
    bind[0].length= &len;

In all cases I get o_errno = -1002

How to pass a binary value ????

Thank you in advance.

Are you sure that it's the binary data which causes your queries to fail? I find your use of placeholders for output variables somewhat confusing. Try to direct output to user variables, and see if the query succeeds in that case. — MvG
– MvG, Commented Nov 11, 2012 at 14:45
you are right . this is not the program but the procedure . with a more simple procedure CREATE PROCEDURE testVarsGet3` (IN i_ulv BINARY(12)) BEGIN select hex(i_ulv) END ` I finally found the problem in my code the good method is buffer_type = MYSQL_TYPE_BLOB with buffer = value_bin = hex2bin ("7124A57EF08100010000DF3F"); — user1815977
– user1815977, Commented Nov 11, 2012 at 16:42

MvG · Accepted Answer · 2012-11-11 22:33:14Z

1

Turning my comment into an answer.

You should not use a quotation mark as a placeholder for an output parameter. Instead, provide a user variable for each output parameter, just like you did in your command line version. Then issue a subsequent SELECT statement to retrieve the values of these user variables. You can bind result values to your C variables for these.

Once you are down to input parameters only, you should be able to bind them to your C variables the way you attempted to.

answered Nov 11, 2012 at 22:33

MvG

61.8k25 gold badges158 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql C-API mysql_stmt_bind_param : how to pass binary values in input?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related