Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

Finally i move forward from postgresql 9.1 to postresql 9.3 that supports JSON data type. Then the same code function properly.

However i think that what i want to do in the first place can be done... if someone know how i still want to know.


Enviroment
node v0.10.28
pg v3.3.0
postgresql 9.1

I got this insert query

INSERT INTO sessions(sid, user_id, session_object) VALUES ('id1', 1, '{"id":"fX2HkXYLclB","data": testing"}') RETURNING session_id

When testing it from pgAdmin (or command line) it works fine, but when my app try to run it from pg.client.query it try to turn the object as a string saving "[object object]". Here is the node code:

var session = {"id":"fX2HkXYLclB","data": "testing"};
var sql = 'INSERT INTO sessions(sid, user_id, session_object) VALUES (\'id1\', ' +
          ' 1, \''+JSON.stringify(session)+'\' ) RETURNING session_id';
console.log(sql);
client.query(sql, function(err, info) {
  if(err) {
  return addSessionCB(err, null);
  }
  return addSessionCB(null, info.rows[0].session_id);
});

After runing the application and testing the query manually on pg_admin the table show this 2 results.

 session_id |     sid     | user_id |            session_object             | active 
------------+-------------+---------+---------------------------------------+---------
          1 | fX2HkXYLclB |       1 | [object Object]                       | t
          2 | fX2HkXYLclB |       1 | {"id":"fX2HkXYLclB","data": "testing"}| t
(2 filas)

So, the question is ¿what im doing wrong with the pg.query?


Adding the original code, the code before is simplified for readability.

function addSession(session, addSessionCB) {
  log.log('debug', '[PSQL]Add new session on DB. \nsession: '+ session.id +
    '\nuser:'+ session.data.user);
  function executeAddSessionQuery(user_id){
    var sql = 'INSERT INTO sessions(sid, user_id, session_object) VALUES (\'' +
      session.id + '\', '+user_id+', \''+JSON.stringify(session)+'\' ) ' +
      'RETURNING session_id';
    log.log('debug', '[PSQL]Adding session: '+session.id+' for user_id: '+
      user_id+'\nSQL: '+sql);
    client.query(sql, function(err, info) {
      if(err) {
        log.log('debug', '[PSQL]Error adding new session. \n'+err);
        return addSessionCB(err, null);
      }
      return addSessionCB(null, info.rows[0].session_id);
    });
  };
//to save session we need to know user id
  getUserByName({name: session.data.user},
    function getUserByNameCB(err, user_result){

      if(err || !user_result){
        //if error or not result we try to save new user
        log.log('debug', '[PSQL]Associated user not found. Creating a new ' +
          'user entry. ');
        addUser({name: session.data.user},
          function addUserCB(err, newUserID){
            if(err){
              log.log('warn', '[PSQL]Session user didn\'t exists and cannot be ' +
                'added to DB');
              return addSessionCB(err, null);
            }
            executeAddSessionQuery(newUserID);
        });
      } else {
        //if the user exist we use his id.
        executeAddSessionQuery(user_result.user_id);
      }
  });
}
share|improve this question
    
That sounds like an obj.toString() problem. You're not using toString() anywhere on an object are you? I don't see it here, but otherwise your code should work. –  glortho Jun 30 '14 at 15:26
    
Nope. The code is simplified here but very similar to to original version. JSON.stringify(session)give me a string so i dont need session.toString() fot nothing. –  darofar Jun 30 '14 at 15:34
    
Can you share original code? –  glortho Jun 30 '14 at 15:36
    
You got it, hopes it helps. –  darofar Jun 30 '14 at 15:45

1 Answer 1

The node postgres module has another form of query; where the 2nd parameter is an array of objects. So in your case

var sql = 'INSERT INTO sessions(sid,user_id,session_object) VALUES ($1,$2,$3) RETURNING session_id';
var values = [id1,1,JSON.stringify(session)];

and then follow that up with

client.query(sql,values,function(err,info) {
...

This also has the added benefit of guarding against SQL injection attacks.

share|improve this answer
    
Yeah... i have try that too, but still got the same result. –  darofar Jun 30 '14 at 17:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.