Question 20482 in Mysql Question posted in
The official MySQL documentation can be found here.

javascript function to check if a mysql table exists and return an integer

I have a javascript function which is intended to output a 0 if a mysql table doesn’t exist and a 1 if it does. But I have not been able to understand why it does not function as intended.

function TableExistsOrNot (checktable){
    try{
        console.log("the function was entered");
        var sqlcheck = 'SELECT id FROM ?? ORDER BY id DESC LIMIT 1';
        con.query(sqlcheck,[checktable],function (err, result) {
            if (result > 0){
                console.log(result);
                console.log("table exists");
                return 1;
            }
            if(result === undefined){
                console.log("result was undefined");
                return 0.0;
            }

        });
    }

    catch (err) {
    console.log(err);
    console.log("error locating table");
    return 0;
    }

}

When I console log the result it just says undefined. And if I try to assign the result of the function to a variable it also says undefined.

edit based on the feedback I have added code for async nature, installed and required express. But this has not solved the problem. See the code below. What is strange is console.log(a); in the else statement is logging 5. But the last line console.log(datatablepresent); which should be equal to ‘a’ is logging Promise { <pending> }

function checktable(tablename) {
    try{
        let sql = 'SELECT id FROM ?? ORDER BY id DESC LIMIT 1';
        return new Promise((resolve, reject) => {
        con.query(sql,[tablename],(err, result) => {
            if (err) {
                console.log("entered err");
            }
            else {
                resolve(result);
                console.log("entered else");
                var a = result[0].id;
                if (a>1){
                    console.log(a);
                    return a;
                }
                
            }
        });
        });
    }
    catch (err) {
        console.log(err);
        console.log("error locating table");
        //return 0;
    }
}
    

app.get("/", async function (req, res) {
    console.log("entered callback");
    const result = await checktable();
    res.send(result);
    console.log(result);
});

datatablepresent = checktable('beta5');
console.log(datatablepresent);
Tags:

2

Answers


  1. 0

    Here is the right query to find out if a table exists. Use the table — actually the system view — named TABLES information_schema.

    It’s too hard to issue a query and cope with a sometimes-expected error when you can just get a number. What you want to do is simply ask the system. And you’re presenting the name of the table as an ordinary string value to go into a placeholder.

    SELECT COUNT(*) table_exists 
FROM information_schema.TABLES 
 WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_NAME = ?;

    You give the table name as the substitution parameter. The query always returns one row and one column. The column, named table_exists in the result set, has the value 1 if the table does exist, and 0 otherwise.

    Then you have to structure the query and error handler correctly, and handle the async nature of the query correctly. The easiest way to do that is with the mysql2 package and an async function.

    const mysql = require('mysql2/promise');

const table_exists_query = `
  SELECT COUNT(*) table_exists 
    FROM information_schema.TABLES 
   WHERE TABLE_SCHEMA = DATABASE()
     AND TABLE_TYPE = 'BASE TABLE'
     AND TABLE_NAME = ?;`

async function TableExistsOrNot (checktable) {
  try {
    console.log("the function was entered")
    const [rows, fields] = await con.query(table_exists_query, [checktable])
    return result[0].table_exists > 0 ? true : false
  }
  catch (error) {
    console.error ('should not happen', error)
    return false
  }
}


const exists = await TableExistsOrNot( 'my_table_name' )

    Edit You can put all your Javascript code in an async function, like this.

    async function allMyCode () {
  ...
  const exists = await TableExistsOrNot( 'my_table_name' )
  ...

}

allMyCode().catch(console.error)

    Or you can do use a promises’ then clause to call async code from a non-async context:

    TableExistsOrNot( 'my_table_name' )
 .then(function (exists) {
     if (exists) console.log ('the table exists')
  })
    Login or Signup to reply.
  2. 0

    You can simply check whether the table exists or not in the particular database just with

    SHOW TABLES LIKE "<table_name>"

    If the table exists, it will return:

    [ RowDataPacket { 'Tables_in_<db_name> (<table_name>)':'<table_name>' } ]

    If not, it will return:

    []

    So it can return 1 or 0 (exist or not) with the value of result.length.

    async function checktable(tablename) {
    try {
        let sql = `SHOW TABLES LIKE "${tablename}"`;
        return new Promise((resolve, reject) => {
            con.query(sql, (err, result) => {
                if (err) {
                    console.log("entered err");
                    reject(err);
                } else {
                    console.log("entered else");
                    // check if tablename already exists
                    resolve(result.length);
                }
            });
        });
    } catch (err) {
        return err;
    }
}

app.get("/", async function(req, res) {
    console.log("entered callback");
    const result = await checktable('item');
    res.json(result);
    console.log(result);
});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search