I use node.js and mysql module.When I execute the following code I want the table_name to be set to a variable, not a static table name. For example I want table_name to be todays date.
connection.query(
"CREATE TABLE `<table_name>` (" +
" `title` varchar(50) NOT NULL,"+
" `text` varchar(50),"+
" `created` timestamp NULL,"+
" PRIMARY KEY (`title`));"
);
Is it possible to set table name as a variable in mysql module for node.js?
Best Regards
The module has built in methods to handle your case, you need to escape the variables like this:
var tableName = 'THETABLE';
connection.query('CREATE TABLE ?? (column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size))', [tableName], function (error, results) {
// error will be an Error if one occurred during the query
// results will contain the results of the query (if any)
});
You can read more about this feature here:
https://github.com/felixge/node-mysql/#escaping-query-identifiers
? but not about ??
? its for values and ?? is for identifiers.
async function createTable(db_connection,tableName) {
await new Promise((resolve, reject) => {
db_connection.connect(function(err) {
if (err) {
return console.error('error: ' + err.message);
}
db_connection.query('CREATE TABLE ?? (doc_source_type varchar(255) default null,
doc_id varchar(255) default null, company varchar(255) default null)',
[tableName],
function(err, results) {
if (err) {
console.log(err.message);
reject(err);
}
else{
console.log("Table created successful");
resolve(results);
}
});
});
});
}
or you can even try different approach
async function setTable(db_connection,tableName) {
await new Promise((resolve, reject) => {
db_connection.connect(function(err) {
if (err) {
return console.error('error: ' + err.message);
}
let createTodos =`create table if not exists ??(
id int primary key auto_increment,
doc_id varchar(255) default null,
doc_source_type varchar(255) default null,
company varchar(255) default null,
customer varchar(255) default null,
rubric_version varchar(255) default null,
assessment_type varchar(255) default null,
indexed_at datetime default null,
assessment_date datetime default null,
industry_type varchar(255) default null
)`;
//console.log(createTodos);
db_connection.query(createTodos, [tableName], function(err, results) {
if (err) {
console.log(err.message);
reject(err);
}
else{
console.log("Table created successful");
resolve(results);
}
});
});
});
}
