Question 204694 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – DATE_ADD() is not working in MySQL XDev API with Node.js

In MySQL shell I am able to run

select * from meeting where startTime > date_add(now(), interval 2 day);

This same command is not working using MySQL XDev API

var meetingRows = (await sqldb.getTable('meeting').select().where('startTime > DATE_ADD(NOW(), interval 2 DAY)').execute()).fetchAll()

I am getting error

-- PARSING FAILED --------------------------------------------------

> 1 | DATE_ADD(NOW(), interval 2 DAY)
    |                          ^

Expected one of the following: 

'!=', '%', '&&', '&', '*', '+', ',', '-', '/', '<', '<<', '<=', '<>', '=', '==', '>', '>=', '>>', '^', 'and', 'between', 'div', 'in', 'is', 'like', 'not', 'or', 'overlaps', 'regexp', '|', '||'

    at _.tryParse (/usr/src/app/node_modules/parsimmon/src/parsimmon.js:928:15)
    at Object.parse (/usr/src/app/node_modules/@mysql/xdevapi/lib/ExprParser/index.js:46:27)
    at Object.getValue (/usr/src/app/node_modules/@mysql/xdevapi/lib/DevAPI/ProjectedSearchExprStr.js:88:27)
    at /usr/src/app/node_modules/@mysql/xdevapi/lib/DevAPI/Table.js:276:95
    at Array.map (<anonymous>)
    at Object.select (/usr/src/app/node_modules/@mysql/xdevapi/lib/DevAPI/Table.js:276:18)

What is wrong in my code? How can I make this working?

Tags:

2

Answers


  1. 0 
    const mysqlx = require('@mysql/xdevapi');

const session = mysqlx.getSession({
  user: 'your_user',
  password: 'your_password',
  host: 'localhost',
  port: 33060,
  schema: 'your_schema'
});

const meetingTable = session.getSchema('your_schema').getTable('meeting');
const now = mysqlx.expr('NOW()');
const interval = mysqlx.expr('INTERVAL 2 DAY');

const meetingRows = await meetingTable
  .select()
  .where(mysqlx.expr('startTime > :startTime', { startTime: now.add(interval) }))
  .execute()
  .fetchAll();

console.log(meetingRows);
    Login or Signup to reply.
  2. 0

    In theory, with an X DevAPI expression, you should be able to use a simplified flavour of that SQL statement:

    NOW() + interval 2 DAY
CURDATE() + interval 2 DAY

    So, you can use the API like the following:

    .where('startTime > NOW() + interval 2 DAY')

    In practice, I think it’s not really working, so you can still use the regular SQL with session.sql().

    I suggest you report a bug at https://bugs.mysql.com/ using the Connector for Node.js category.

    Disclaimer: I’m the lead developer of the MySQL X DevAPI connector for Node.js

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search