JSON output using Express using Mysql

Question

I have Created a database in mysql

mysql> SELECT * FROM test456;
+-----+-------+-------------------------+
| _id | name  | image                   |
+-----+-------+-------------------------+
|   1 | Chris | /home/images/index.jpeg |
+-----+-------+-------------------------+
1 row in set (0.00 sec)

and My Express program is below

var express = require('express')
  , http = require('http')
  , mysql = require('mysql'); // <---- HERE

var app = express();

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: "root",
    database: 'test123'
});

connection.connect(); // <---- AND HERE

// all environments
app.set('port', process.env.PORT || 7005);


app.get('/',function(request,response){
connection.query('SELECT * FROM test456', function(err, rows, fields)

   {
            console.log('Connection result error '+err);
            console.log('no of records is '+rows.length);
                    response.writeHead(200, { 'Content-Type': 'application/json'});
            response.end(JSON.stringify(rows));
    });

} );



http.createServer(app).listen(app.get('port'), function(){
  console.log('Express server listening on port ' + app.get('port'));
});

Output::

[{"_id":1,"name":"Chris","image":[47,104,111,109,101,47,105,109,97,103,101,115,47,105,110,100,101,120,46,106,112,101,103]}]

Clearly you can see that i am not able to generate the image url instead i am generating hexadecimal number ...... how to make the hexadecimal number to a url

My research shows that i need to use base64 encoding but how can i apply that here

any ideas

Community · Accepted Answer · 2017-05-23 11:54:19Z

1

The output is probably because each image is a Buffer rather than a String.

console.log(Buffer.isBuffer(rows[0].image)); // true

You may need to alter the character set in MySQL to UTF-8 for compatibility with Node.js:

ALTER TABLE test456 CONVERT TO CHARACTER SET utf8;

But, you can also specify a replacer with JSON.stringify():

response.end(JSON.stringify(rows, function (key, value) {
    if (Buffer.isBuffer(value)) {
        return value.toString();
    } else {
        return value;
    }
}));

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Sep 17, 2013 at 6:55

Jonathan Lonowski

124k35 gold badges203 silver badges202 bronze badges

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

Collectives™ on Stack Overflow

JSON output using Express using Mysql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related