Returning rows from MySQL with RUST sqlx when a VARBINARY(16) column is part of the where clause

Question

I'm trying to query a simple table using sqlx where the field in the WHERE clause is defined in MySQL as a VARBINARY(16). The table is defined as:

 mysql> desc machine_state;
 +------------+-----------------+------+-----+---------+-------+
 | Field      | Type            | Null | Key | Default | Extra |
 +------------+-----------------+------+-----+---------+-------+
 | id         | binary(16)      | NO   | PRI | NULL    |       |
 | data       | varbinary(2048) | YES  |     | NULL    |       |
 | machine_id | varbinary(16)   | NO   |     | NULL    |       |
 +------------+-----------------+------+-----+---------+-------+

Given an input string from an end user for a "machine id", typically with the mysql command line client I will do this:

 mysql> SELECT * from machine_state where machine_id = UNHEX('b25c07f2d2904704b7921173915c62ea');
 +------------------------------------+------------+--------------------------------- 
---+
 | id                                 | data       | machine_id                         
 |
 +------------------------------------+------------+--------------------------------- 
---+
 | 0x00002422C9CF4D8BB8D44941D4DE66B7 | 0x0100     | 0xB25C07F2D2904704B7921173915C62EA |
 +------------------------------------+------------+---------------------------------
 1 row in set (0.00 sec)

That works as expected, however, I can't seem to accomplish the same thing with sqlx against the very same database table. I've tried using UNHEX, binding Vec[u8] as bind variables, a few other things as shown below. No errors, but the record is never returned.

    // Input variable I am looking for in a binary column
    let machine_id = "b25c07f2d2904704b7921173915c62ea";

    // Try using UNHEX with a bind variable
    let query_result = sqlx::query::<_>("SELECT * from machine_state where machine_id = UNHEX(?)")
      .bind(machine_id)
      .fetch_optional(database_connection_pool).await;

    println!("{:?}", query_result);

    // Try using UNHEX directly as in the command line    
    let query_result = sqlx::query::<_>("SELECT * from machine_state where machine_id.as_bytes = UNHEX('b25c07f2d2904704b7921173915c62ea')")
      .fetch_optional(database_connection_pool).await;

    println!("{:?}", query_result);

    // Try binding a Vec<u8> representation of the string
    let bytes: Vec<u8> = machine_id.as_bytes().to_vec();

    let query_result = sqlx::query::<_>("SELECT * from machine_state where machine_id = ?")
            .bind(bytes)
            .fetch_optional(database_connection_pool).await;

            println!("{:?}", query_result);

    ... OUTPUT ....

     Ok(None)
     Ok(None)
     Ok(None)

I wonder if anyone has any suggestions about the proper format to use when a VARBINARY(16) column is part of the WHERE clause? I feel like I'm missing something obvious but I can't seem to dig up any examples on this.

score 1 · Accepted Answer · answered Dec 22 '22 at 18:13

1

This work?

let query_result = sqlx::query::<_>("SELECT * from machine_state where machine_id = UNHEX('b25c07f2d2904704b7921173915c62ea')")
    .fetch_optional(database_connection_pool).await;

answered Dec 22 '22 at 18:13

Zeppi

1,175
6
11

Thank you! That did work, I could have sworn I tried that : operator error. Binding the field value as a parameter worked as well: (`let query_result = sqlx::query::<_>("SELECT * from machine_state where machine_id = UNHEX(?)").bind(machine_id).fetch_optional(database_connection_pool).await;`) Attempting to bind a Vec didn't work, but need more research on that. (`let vector_bytes: Vec = String::into_bytes(machine_id.to_string()); let query_result = sqlx::query::<_>("SELECT * from machine_state where machine_id = ?") .bind(vector_bytes)`) – SantaCruzDeveloper Dec 23 '22 at 18:43

Returning rows from MySQL with RUST sqlx when a VARBINARY(16) column is part of the where clause

1 Answers1