'ER_PARSE_ERROR' When running Query within node MYSQL

Question

I have a query which I have tested already and it works fine as just standard MYSQL, but I keep getting the ER_PARSE_ERROR when I implement it within node like so:

function InsertPresenceVal(pval, status) {
    connection.query({
        sql : "SELECT count(*)INTO @exist FROM information_schema.columns\
        WHERE table_schema = database()\
        and COLUMN_NAME = 'presence'\
        AND table_name = 'userman_users';\
        set @query = IF(@exist <= 0, 'alter table userman_users add column presence varchar(20) NULL after username', 'select \'Column Exists\' status');\
        prepare stmt from @query;\
        EXECUTE stmt;",
        timeout : 40000
    });
}

I am not sure why and am not sure how to resolve the issue, it seems to have an issue with this line: set @query = IF(@exist <= 0, 'alter table userman_users add column presence varchar(20) NULL after username', 'select \'Column Exists\' status');\

I think its an issue with \ but ave no joy resolving the issue, the query itself is to simply insert a field into the table if it does not exist.

Any suggestions or advice to resolve the issue?

score 1 · Accepted Answer · edited May 23 '17 at 12:09

Never mind I found the answer I was looking for here:

node-mysql multiple statement in one query

Executing multiple query's is automatically disabled for security reasons hence why am getting the error, issue was fixed when I separated the query's.

And used the following:

function InsertPresenceVal(pval, status) {
  connection.query({
    sql : 'SET @preparedStatement = (SELECT IF(\
        (SELECT COUNT(*)\
            FROM INFORMATION_SCHEMA.COLUMNS\
            WHERE  table_name = "userman_users"\
            AND table_schema = DATABASE()\
            AND column_name = "presence"\
        ) > 0,\
        "SELECT 1",\
        "ALTER TABLE `userman_users` ADD `presence` varchar(20) NULL DEFAULT NULL;"\
    ));',
    timeout : 40000
  });

  connection.query({
    sql : 'PREPARE alterIfNotExists FROM @preparedStatement;',
    timeout : 40000
  });

  connection.query({
    sql : 'EXECUTE alterIfNotExists;',
    timeout : 40000
  });

  connection.query({
    sql : 'DEALLOCATE PREPARE alterIfNotExists;',
    timeout : 40000
  });

}

'ER_PARSE_ERROR' When running Query within node MYSQL

1 Answers1