I'm trying to get all the promocodes out of a table but I'm not sure what we should use for the bind parameter. I tried '*', ' ', '%%', and '%' but the results came out undefined/no results. Anyone know how to get all the results?
router.post('/getpromocodes', function(res){
mysqlx.getSession(stageConnectionmysqlx)
.then(function(session){
var promoTable = session.getSchema('exampleTable').getTable('promo')
promoTable
.select(['promo_code', 'promo_percentage', 'expiration_date'])
.where('promo_code like :promo_code')
.bind('promo_code', '*')
.execute()
})
.then(function(result){
let data = result.fetchAll()
console.log('Here is the data', data)
res.send(data)
})
.catch(function(err){
console.log('the following error occured: ' + err.message)
})
})
Is there a specific reason for using the where() clause? In the end, the X Plugin will convert the CRUD operation into an SQL statement using LIKE, so you are bound by the same syntax limitations.
https://dev.mysql.com/doc/refman/8.0/en/pattern-matching.html
In the best case scenario, you should simply just drop the where() clause, like the following:
promoTable.select(['promo_code', 'promo_percentage', 'expiration_date'])
.execute()
The issue was where I placed my closing } and ).
The .then needs to come right after the execute. And as Rui described to pull everything from the table, do not use the .where and .bind methods.
router.post('/getpromocodes', function(res){
mysqlx.getSession(stageConnectionmysqlx)
.then(function(session){
var promoTable = session.getSchema('exampleSchema').getTable('promo')
promoTable
.select(['promo_code', 'promo_percentage', 'expiration_date'])
.execute()
.then(function(result){
let data = result.fetchAll()
console.log('Here is the data', data)
res.send(data)
})
})
.catch(function(err){
console.log('the following error occured: ' + err.message)
})
})