Node.js Mysql with Query from Variable how to get it working?

[tecnewb]

Hi,

i have the following problem.. i can use a variable to be used as a query, but it only works when i use the "current" file, not when i make an extra file like "mysql" in Node.js editor.

so this works:
File Current

// get the client
const mysql = require('mysql2');

// create the connection to database
const connection = mysql.createConnection({
    host: '10.10.10.1',
    user: 'aaaa',
    database: 'test1',
    password: 'aaaa'
});

console.log("making request")

var query = [[QUERY]];

await(new Promise((resolve, reject) => {
    connection.query(
        query,
        function (err, results, fields) {
            if(err)
            {
                reject(err)
                return
            }
            
            [[RESULTS]] = results
            resolve()
        }
    ); 
}));

console.log([[RESULTS]])

connection.end();

console.log("ended connection")

Output is:
Thread #1 : making request
Thread #1 : [{"COL1":1,"COL2":"abc","COL3":"def"}]
Thread #1 : ended connection

But this works not:

File Current:

await require("../mysql")();

console.log("query done")

File mysql:

module.exports = async function(){
// get the client
const mysql = require('mysql2');

// create the connection to database
const connection = mysql.createConnection({
    host: '10.10.10.1',
    user: 'aaaa',
    database: 'test1',
    password: 'aaaa'
});

console.log("making request")

var query = [[QUERY]];

await(new Promise((resolve, reject) => {
    connection.query(
        query,
        function (err, results, fields) {
            if(err)
            {
                reject(err)
                return
            }
            
            [[RESULTS]] = results
            resolve()
        }
    ); 
}));

console.log([[RESULTS]])

connection.end();

console.log("ended connection")
}

I get the error
Thread #1 : making request
Thread #1 : TypeError: Cannot read property 'constructor' of undefined

how to get it done?

[tecnewb]

or is there any other way to make it easier to make multiple queries in BAS without writing all code again every time?

i tried pools but it does not work, as BAS "can not invoke functions from other files" written in the Wiki

[tecnewb]

hmm for anybody with the same problem i did the following:

Make a new BAS function: mysql
put the node.js with the first code from above inside the function.

now when i need to make a query i make a template with SELECT * FROM 'tableabcd' to var QUERY and then call the function mysql after this...

not a very clean solution but it works.. hmm :/

Please give Advice for a better solution!

[jimhill21]

@tecnewb said in Node.js Mysql with Query from Variable how to get it working?:

await(new Promise((resolve, reject) => {

I have found a solution that can keep the BAS variables in your extra files. Simply set a initial value to the BAS variables in the "Current" file before they are used in the extra files. Example: in your current file near the top add [[RESULTS]] = ''.

Once you set the BAS variable to any value in the "Current" file, you're able to get and set it in the extra files.

[Fox]

@jimhill21 said in Node.js Mysql with Query from Variable how to get it working?:

@tecnewb said in Node.js Mysql with Query from Variable how to get it working?:

await(new Promise((resolve, reject) => {

I have found a solution that can keep the BAS variables in your extra files. Simply set a initial value to the BAS variables in the "Current" file before they are used in the extra files. Example: in your current file near the top add [[RESULTS]] = ''.

Once you set the BAS variable to any value in the "Current" file, you're able to get and set it in the extra files.

@Support knows about this problem and will fix it in the next version. By setting any value in the "current" file for the BAS variable, you will get a bug in the "Run" mode:

---

current file

[[RESULT]] = "hello"
await require("../bas.js")();

---

bas.js

module.exports = async function () {
    await BAS_API("VAR_RESULT = thread_number()");
    return console.log([[RESULT]])
}

result:

Test script