MYSQL XDev API Javascript Select all from a table

Question

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)
            })
            
    
    })

ruiquelhas · Accepted Answer · 2021-11-25 17:04:12Z

1

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()

edited Nov 25, 2021 at 17:04

answered Oct 18, 2021 at 8:51

ruiquelhas

2,0251 gold badge18 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nmsdev Over a year ago

I made the changed described above and I've added a console.log(session.inspect) into my code and there is data in the table. (1 row for testing). Here is what I get:

{   auth: 'MYSQL41',   host: '193.122.179.108',   pooling: false,   port: 33060,   schema: undefined,   socket: undefined,   tls: false,   user: 'robot',   dbUser: [Getter],   ssl: [Getter] } Here is the result undefined the following error occured: Cannot read property 'fetchAll' of undefined

ruiquelhas Over a year ago

I'm not sure I understand the issue. Maybe you we can move this discussion to the MySQL community Slack. That way, you should be able to provide more and better context.

Wai Ha Lee · Accepted Answer · 2021-11-27 09:32:47Z

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)
        })
})

Collectives™ on Stack Overflow

MYSQL XDev API Javascript Select all from a table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related