Reputation: 635
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?
Upvotes: 0
Views: 394
Reputation: 635
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
});
}
Upvotes: 1