minikubebyteayugabytedb

Why does YugaByteDB YSQL select query return unexpected bytes when using bytea data type?


I am trying to store some binary data into yugabytedb on my laptop in minikube, so I used the bytea datatype as the documentation says.

But when it comes to retrieving the binary data back using a select query, the bytes returned are very different from what I expect!

What could be the cause of this?


Solution

  • I got a solution

    Here was my code:

        /*
        ===============================
        GOLANG CODE REGISTERING A USER:
       ================================
       */
        db := dbconnect()
        defer db.Close() 
        log.Println("Registration Server says: user.Photo = ", user.Photo[:100])
        usrInsert := fmt.Sprintf("INSERT INTO users (user_id, 
    username, Phone, Email, Nationality, Photo, pxwrd) 
        VALUES ('%s','%s','%s','%s','%s','%x','%s')", 
        user.user_id, user.username, user.Phone, 
    user.Email, user.Nationality, user.Photo, 
    encriptedpassword) 
          
        _, err := db.Exec(usrInsert)
        
        if err != nil {
            fmt.Println(err.Error());
        }
    /*
    =============================================
    OUTPUT OF 'log.Println' ABOVE WAS:
    ============================================= 
        Registration Server says: user.Photo = [1 0 0 0 0 0 6 
    213 194 0 0 0 24 255 216 255 224 0 16 74 70 73 70 0 1 2 1 
    1 44 1 44 0 0 255 225 16 115 69 120 105 102 0 0 77 77 0 42 
    0 0 0 8 0 11 1 15 0 2 0 0 0 18 0 0 0 146 1 16 0 2 0 0 0 10 
    0 0 0 164 1 18 0 3 0 0 0 1 0 1 0 0 1 26 0 5 0 0 0 1 0 0 0]
    */
    
    
    /*
    =============================
    GOLANG CODE FOR USER LOGIN:  
    =============================
    Note: the user profile Photo is 
    saved as 'bytea' data type
    */
        //select query for loging in
        selecQuery := fmt.Sprintf("SELECT * FROM users WHERE 
    username='%s' AND pxwrd='%x' ", 
    uname, encriptedpassword)
            queryResult, err := db.Query(selecQuery)
            if err != nil {
                fmt.Println(err.Error())  
            }  
            defer queryResult.Close() 
            userExists := false 
            for queryResult.Next() {
                userExists = true
                
                var UserX rs_utils.User
                var cnt, psw string
                err = queryResult.Scan(&cnt, &UserX.User_id, 
    &UserX.Username, &UserX.Phone, &UserX.Email, 
    &UserX.Nationality, &UserX.Photo, &psw)   
                  
                if err != nil {
                    log.Println("!! Error: ", err)
                    break
                } 
                 
                log.Println("UserX.Photo, psw :\n", UserX.Photo[:100], psw)
                 
                //...
                /...
                
             }
    
            /*   
            ==============================
            OUTPUT OF 'log.Println' ABOVE:
            ==============================
            UserX.Photo, psw :
            [48 49 48 48 48 48 48 48 48 48 48 48 49 53 97 98 48 53 48 48 48
            48 48 48 50 97 56 57 53 48 52 101 52 55 48 100 48 97 49 97 48 97
            48 48 48 48 48 48 48 100 52 57 52 56 52 52 53 50 48 48 48 48 48
            52 54 99 48 48 48 48 48 50 55 102 48 56 48 54 48 48 48 48 48 48
            50 100 54 50 52 53 53 51 48 48 48 48 48 
            48 48 49] 8ae5776067290c4712fa454006c8ec6
    */
    

    As you can see, the retrieved binary data was very different from what was inserted. The cause:

    usrInsert := fmt.Sprintf("INSERT INTO users (user_id, username, 
    Phone, Email, Nationality, Photo, pxwrd) 
    VALUES ('%s','%s','%s','%s','%s','%x','%s')", 
    user.user_id, user.username, user.Phone, 
    user.Email, user.Nationality, user.Photo, 
    encriptedpassword) 
          
    _, err := db.Exec(usrInsert)
    
    //AND
    
    selecQuery := fmt.Sprintf("SELECT * FROM users WHERE 
    username='%s' AND pxwrd='%x' ", uname, encriptedpassword) 
    
    queryResult, err := db.Query(selecQuery)
    //Notice the string formatting in the queries. 
    
    

    SOLUTION: I replaced the above lines of code with:

    _, err := db.Exec(`INSERT INTO users (user_id, username, 
    Phone, Email, Nationality, Photo, pxwrd) 
    VALUES ($1, $2, $3, $4, $5, $6, $7)`, 
    user.user_id, user.username, user.Phone, user.Email, 
    user.Nationality, user.Photo, encriptedpassword)
    
    //AND
    
    queryResult, err := db.Query(`SELECT * FROM users WHERE 
    username=$1 AND pxwrd=$2 `, uname, encriptedpassword)
    

    respectively.