CODEWITHSUNDEEP
mysqlnode.jsamazon-aurora
program_bumble_bee
program_bumble_bee

Reputation: 305

Selecting data from table with UUID as binary()

I have an AWS RDS Aurora MySQL connection. Following are the steps that I'm following to operate create, insert & fetch the data:

create table:

CREATE TABLE user (
    user_id BINARY(16) PRIMARY KEY NOT NULL,
    first_name VARCHAR(25) NOT NULL, 
    last_name VARCHAR(25) NOT NULL,
);

insert into table:

INSERT INTO user VALUES (
    UNHEX(REPLACE("9a9cbfga-7426-471a-af27-31be7tb53ii2", "-","")),
    "Jon",
    "Smith"
);

fetch from table:

SELECT * FROM user WHERE user_id = UNHEX(REPLACE("9a9cbfga-7426-471a-af27-31be7tb53ii2", "-",""));

The handler function that triggers my function to fetch the data goes like:

module.exports.fetchUser = async (event, context, callback) => {

  const params = {
    resourceArn: 'arn:aws:rds:*********:cluster:************',
    secretArn: 'arn:aws:secretsmanager:*************',
    sql: 'SELECT * FROM user WHERE user_id = UNHEX(REPLACE("9a9cbfga-7426-471a-af27-31be7tb53ii2", "-",""))',
    database: 'dev_db1',
    continueAfterTimeout: true,
    includeResultMetadata: true
  }

  try {
    const db_res = await rdsDataService.executeStatement(params).promise();

    const response = {
      body: {
        message: 'Data fetched',
        data: JSON.stringify(db_res, null, 2)
      }
    };
    callback(null, response);
  } catch (error) {
    callback(null, error)
  }
};

Executing the http data API endpoint for the same results me in this:

{
    "body": {
        "message": "Data fetched",
        "data": [
            [
                {
                    "blobValue": {
                        "type": "Buffer",
                        "data": [
                            202,
                            136,
                            22,
                            206,
                            45,
                            214,
                            75,
                            233,
                            156,
                            28,
                            163,
                            223,
                            186,
                            115,
                            89,
                            107
                        ]
                    }
                },
                {
                    "stringValue": "Jon"
                },
                {
                    "stringValue": "Smith"
                },
            ]
        ]
    }
}

All I need is to fetch the complete details of all the users (for now I've inserted just one record for ease) in the table user and show the exact user_id instead of the blobValue. Not sure how to convert this UUID binary into redable form like 9a9cbfga7426471aaf2731be7tb53ii2 or 9a9cbfga-7426-471a-af27-31be7tb53ii2 in the response. I'am bit new to handling queries in MYSQL & Aurora, any help to get me through this will be really appretiated. Thanks in advance.

Upvotes: 0

Views: 1103

Answers (1)

nbk
nbk

Reputation: 49375

Mysql 8 has a function for that as https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_bin-to-uuid and https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid-to-bin

But you have to do it manually.

As i can't insert your data anywhere the select will do

CREATE TABLE user (
    user_id BINARY(16) PRIMARY KEY NOT NULL,
    first_name VARCHAR(25) NOT NULL, 
    last_name VARCHAR(25) NOT NULL
)

INSERT INTO user VALUES (UNHEX(REPLACE("3f06af63-a93c-11e4-9797-00505690773f", "-","")),
    "Jon",
    "Smith");

SELECT * from user where user_id = UNHEX(REPLACE("3f06af63-a93c-11e4-9797-00505690773f","-","")) 

user_id | first_name | last_name
:------ | :--------- | :--------
null    | Jon        | Smith    

SELECT CONCAT(SUBSTRING(HEX(user_id),1,8),'-',
             SUBSTRING(HEX(user_id),9,4),'-',
            SUBSTRING(HEX(user_id),13,4),'-',
            SUBSTRING(HEX(user_id),17,12)) user_id,first_name,last_name FROM user

user_id                         | first_name | last_name
:------------------------------ | :--------- | :--------
3F06AF63-A93C-11E4-979700505690 | Jon        | Smith

db<>fiddle here

But as you need the text anyway, keep the original so you don't need to unhex hex the data again

CREATE TABLE user (
    user_id BINARY(16) PRIMARY KEY NOT NULL,
    first_name VARCHAR(25) NOT NULL, 
    last_name VARCHAR(25) NOT NULL,
    userid_txt Varchar(3&)
);

Upvotes: 1

Related Questions