node-mysql - problem with update statement

[James Nash]

Hello,

I'm a Node Nubie. I've been playing with it about a week and I love it. I have come across one problem that I can't seem to parse through.

I'm using the latest version of Node, node-mysql and connect (freshened through npm today). The node-mysql readme doesn't show an example of an UPDATE statement, and I could not find an example anywhere else on the web, surprising as that may seem. So my confidence is not 100% that I'm doing it correctly.

Below are the relevant parts my application, this.dbConnect() is called early on and set to this.DB. When I call this.assignToken(), I get no errors, both expires and fields are logged to the console and the callback, callback(params), is executed. It seems to work fine except the record doesn't update.

I have select statements that work fine on the same table in other parts of the application. Yes, I have confirmed that the user has write access to the table.

Am I missing something? Is the update correct? Have I found a bug? I appreciate any help provided either specifically about this problem or with my code in general.

var sso = (function(){
    this.db      = {};
    this.DB      = {};
    this.db.user = "[REDACTED]";
    this.db.pass = "[REDACTED]";
    this.db.db   = "[REDACTED]";
   
    this.dbConnect = function () {
        var Client = require('mysql').Client;
        client = new Client();
        client.debug = false;
        client.user = this.db.user;
        client.password = this.db.pass;
        client.connect();
        client.query('USE ' + this.db.db);
        return client;
    };
   
    this.assignToken = function (params, callback){
        var now = new Date().getTime();
        params.SESS.token = this.mkToken();
        params.SESS.cookie.expires = new Date(Date.now() + 60*60*3);
        var expires =  new Date(Date.now() + 60*60*2);

        this.DB.query(
            "update appsys_users set token=?, expires=? where user_pk = ?",
            [params.SESS.token, expires, params.data.user_pk],
            function(err, fields, files) {
                if (err) throw err;
                console.log(expires);
                console.log(fields);
                callback(params);
            }
        );
        return;
    };
   
    return this;

})();

[mscdex]

On Mar 9, 3:53 pm, James Nash <ja...@atthebox.com> wrote:
>  Below are the relevant parts my application, this.dbConnect() is called
> early on and set to this.DB. When I call this.assignToken(), I get no
> errors, both expires and fields are logged to the console and the callback,
> callback(params), is executed. It seems to work fine except the record
> doesn't update.

Updating works fine for me. What if you execute the resulting SQL
query in the mysql console manually?

[James Nash]

I'm not sure how to inspect the sql before it goes to the mysql engine. That would be helpful. Is that possible? 

But it updates when I approximate it with: 

update appsys_users set token='5ffe4bf87871082709317098d5b99b7c2ce10780910621422aabc3ecc6b24768', expires='2011-03-09 10:55:29' where user_pk = 1

I thought maybe the date was creating the problem, but I've tried both a date object and a formatted string. I even tried date_add(now(), INTERVAL 2 HOUR) and all quietly failed. 

I've had other php applications access this database and table with this user and using the exact same query, so I'm fairly confident the problem isn't on the mysql side of the equation. 

[cwolves]

Have you tried date.toISOString() ?

-Mark

[James Nash]

Thanks that was a nice thought, but I got the same result. 

[mscdex]

On Mar 9, 11:02 pm, James Nash <ja...@atthebox.com> wrote:
> Thanks that was a nice thought, but I got the same result.

What if you omit updating the date field? Not sure what else to
suggest...

[James Nash]

That was a good idea! It proved that the date isn't the issue. 

Clearly something deeper than a malformed query is going on here. After all if it was just malformed query then It should have thrown an error. 

Thanks for your help. I haven't solved the problem, but I gained some insights. I know it's not the date, I know I'm not missing something obvious. And I know I'm passing the parameter into query correctly.

Maybe I'll time to post a bug report on github.

[mscdex]

FWIW the test I used was updating a varchar field only and I didn't
use the "?" substitution feature.

[James Nash]

Well, turns out it was something stupid. I was missing an array reference in params.data.user_pk, it should have been params.data[0].user_pk. How did I miss that for so long?

Just to round things out, this is how the query function ended up:

this.DB.query(

    "update `appsys_users` set `token`=\'" + params.SESS.token + "\', `expires`=\'" + sqldate + "\' where `user_pk` = " + params.data[0].user_pk,

    [],

    function updateCb(err, fields, files) {

        if (err) throw err;

        //console.log(expires);

        //console.log(fields);

        callback(params);

    }

);

This also worked:

this.DB.query(

    "update `appsys_users` set `token`=?, `expires`=? where `user_pk` = ?",

    [params.SESS.token, sqldate,  params.data[0].user_pk],

    function updateCb(err, fields, files) {

        if (err) throw err;

        //console.log(expires);

        //console.log(fields);

        callback(params);

    }

);

I did have to format the date properly before it would update without error:

var expires =  new Date(Date.now() + 60*60*2);

var sqldate = expires.getUTCFullYear()  + "-" +

                expires.getUTCMonth() + "-" +

                expires.getUTCDate() + " " +

                expires.getUTCHours() + ":" +

                expires.getUTCMinutes() + ":" +

                expires.getUTCSeconds();

Thanks for helping me get over my stupidity. 

[Tim Smart]

You can also make a MySQL compliant date by doing:

    new Date().toISOString();

Tim.

--
You received this message because you are subscribed to the Google Groups "nodejs" group.
To post to this group, send email to nod...@googlegroups.com.
To unsubscribe from this group, send email to nodejs+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nodejs?hl=en.