mysql2 nodejs pass table name as variable / right syntax to use near '??'

Question

Hi I am trying to run a query but getting the following error: Nodej Mysql2

query

tableName = "some_table"
id = "some_id";

const readForwardedMessageDetails = pool.execute('SELECT * FROM ?? WHERE `id` = ? LIMIT 1',
        [tableName, id],
        function(err, row) {
            if (err) {
                console.log(err)
                return
            } else {
            
            }
            
            code: 'ER_PARSE_ERROR',
                errno: 1064,
                sqlState: '42000',
                sqlMessage: "You have an error in your SQL syntax; check the manual 
            that corresponds to your MariaDB server version
            for the right syntax to use
            near '?? WHERE `id` = ? LIMIT 1'
            at line 1 ",
            sql: 'SELECT * FROM ?? WHERE `id` = ? LIMIT 1'

Was trying from here : Nodejs-Mysql Query table name as a variable

Also use of ?? double question marks for tablename is given here : https://github.com/mysqljs/mysql#preparing-queries

MySql2 suggests it is mostly compatible with mysql so tried the answers above. https://github.com/sidorares/node-mysql2#history-and-why-mysql2 : MySQL2 is mostly API compatible with mysqljs and supports majority of features. MySQL2 also offers these additional features (hence the inference)

You seem to have some fundamental misunderstandings on how SELECT works in MySQL (or any RDBMS, for that matter) - it would behoove you to research more closely what the syntax for such a query should be. Cursory research would yield you the idea that these question marks should be replaced with the name of the table you're attempting to query from your database. Would you care to show us how exactly you arrived at the conclusion that your query as-is should work? How to Ask — esqew
– esqew, Commented Oct 26, 2021 at 2:47
You have indicated you're using mysql2 in your question, but both your links refer to the mysql package - which are you using? I cannot find any mention of this functionality being provided in the mysql2 package; nor would I recommend such a design where you are reading dynamically from a table at runtime (such designs are usually indicative of more problematic design decisions elsewhere). — esqew
– esqew, Commented Oct 26, 2021 at 3:00
Your rudeness aside, I see you've tagged mysql2 - yet both the Stack Overflow thread and the GitHub documentation are referring to a completely different package - it's not clear how ever you reached the conclusion that they are interchangable 1:1. — esqew
– esqew, Commented Oct 26, 2021 at 3:03
I'm not sure how you got the idea that I edited my comment - edited comments appear with the pencil icon appended after my username - the only comment I've edited was my initial one, which made no mention of mysql2 at all. It's also not clear how it would be relevant if I did in fact edit my comment, anyway. Finally, you still have not addressed the fact that it appears you're referencing documentation for a completely different package (mysql) than you are actually employing in your script (mysql2). — esqew
– esqew, Commented Oct 26, 2021 at 3:11

DragonFire · Accepted Answer · 2021-10-26 07:17:47Z

I had raised the issue with MySql2 since they have mentioned on their website to report any incompatibilities with MySql https://www.npmjs.com/package/mysql2#api-and-configuration stating If you find any incompatibility with Node MySQL, Please report via Issue tracker. We will fix reported incompatibility on priority basis.

Their response is https://github.com/sidorares/node-mysql2/issues/1435

while similar to the .query() .execute() syntax is different and only supports what valid input for PREPARE is ( see https://dev.mysql.com/doc/refman/8.0/en/prepare.html ). You can get your ?? interpolated on the client side in a separate step or just switch to non-prepares statements call with .query()

Passing table name in query is just fine depending on your program requirement especially reusable components can be made where table names are switched/column names are switched. For eg. if you have comments table for different entities and maintaining different tables for these (imagine how one big comment table would perform for the whole website)

Collectives™ on Stack Overflow

mysql2 nodejs pass table name as variable / right syntax to use near '??'

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related