NodeJS MYSQL Query result Buffer?

Question

mysql select query result ...

//nodejs database.js
var mysql      = require('mysql');
var connection = mysql.createConnection({
    host     : 'localhost',
    user     : 'me',
    database : 'test',
    password : '1234'
});

// nodejs app.js
app.get('/api/v0.1/getPostList', function(req, res) {
        limit_count = 5;
        db.query(postModel.postList(limit_count) , function(err, rows) {
            if (err) throw err;
            console.log(rows)
            res.json(rows);
        });
    });

//result
RowDataPacket {
    POST_SEQ: 13,
    POST_TYPE: <Buffer 31 31 30 30>,
    CATEGORY: <Buffer 49 54 20 2f 20 4d 4f 42 49 4c 45>, ...

why query data buffer type?
I do not know the cause.

java query data success
[DEBUG][2016-10-28 19:20:24,160] <== Row: 13, 1100, GAME ...

Nick Dallett · Accepted Answer · 2018-06-09 03:18:46Z

4

I had this issue with int values generated by COUNT. I was able to resolve the issue by casting these values to CHAR within my query. They then came back correctly formatted as js strings. Here's a sample query:

SELECT Date, Park, COUNT(FileID) FROM SkatePix WHERE Park != '' AND Date != 0 GROUP BY Date, Park LIMIT 5;

I changed the COUNT statement to be:

 CAST(COUNT(FileID) AS CHAR) /* formerly COUNT(FileID) */

This did the trick.

answered Jun 9, 2018 at 3:18

Nick Dallett

411 bronze badge

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

Comments

Ekjot Kaur · Accepted Answer · 2017-01-07 06:23:22Z

2

I solved this issue by accessing values:

rows[0].POST_TYPE.toString('utf8');
rows[0].CATEGORY.toString('utf8');

answered Jan 7, 2017 at 6:23

Ekjot Kaur

212 bronze badges

Comments

Taking hours · Accepted Answer · 2017-11-15 09:08:00Z

1

but this would cause performance issue if you are having a lot of records and fields. You would have to iterate and tostring all buffer fields. It should receive data in the predefined datatypes. ie VARCHAR -> String

answered Nov 15, 2017 at 9:08

Taking hours

614 bronze badges

Comments

Hirai Momo · Accepted Answer · 2018-12-18 18:17:40Z

1

This topic is very old.

However, I do not have a good answer anywhere.

I found good solution. So I reply this topic

reference (npm - mysql) https://www.npmjs.com/package/mysql#type-casting

// Modify the typeCast option to suit your situation.
createConnection({
    host: config.host,
    port: config.port,
    user: config.user,
    password: config.password,
    database: config.database,
    multipleStatements: true,
    typeCast: function (field, next) {
        if (field.type == 'VAR_STRING') {
            return field.string();
        }
        return next();
    }
})

answered Dec 18, 2018 at 18:17

Hirai Momo

111 bronze badge

Collectives™ on Stack Overflow

NodeJS MYSQL Query result Buffer?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related