Mysql data need to be written to a json file

Question

I am trying to read data from a mysql database and write it to a JSON file. I am pretty new to node.js and any help would be greatly appreciated.

The code i wrote:

var mysql      = require('mysql');
var fs = require('fs');
var configFile = process.cwd() + "\\config.json";
var configFileContents = fs.readFileSync(configFile, 'utf8');
var config = JSON.parse(configFileContents);
//config = config[process.env.BLENV];
console.log(config.PROD.HTTP);

var connection = mysql.createConnection({
  host     : config.QA.SQLHostName,
  user     : config.QA.SQLUserName,
  password : config.QA.SQLPassword,
  database : config.QA.SQLDatabase,
  multipleStatements: true
});

connection.connect();

var timeInMs = Date.now();
console.log(timeInMs);

connection.query(` 
select 
c.name as "Name",
cl.u_geographic_region as "Region",
CASE
    WHEN cic.os like '%Windows%' THEN 'Windows'
    WHEN cic.os like '%aix%' THEN 'AIX'
    WHEN cic.os like '%esx%' THEN 'ESX'
    WHEN cic.os like '%linux%' THEN 'Linux'
    WHEN cic.os like '%solaris%' THEN 'Solaris'
        ELSE 'UNKNOWN'
    END as "Operating System",

ci.dns_domain as "Host domain",
ci.ip_address as "IP Address",
CASE
    WHEN c.sys_class_name = 'cmdb_ci_computer' THEN 'Computer'
    WHEN c.sys_class_name = 'cmdb_ci_win_server' THEN 'Windows Server'
    WHEN c.sys_class_name = 'cmdb_ci_esx_server' THEN 'ESX Server'
    WHEN c.sys_class_name = 'cmdb_ci_aix_server' THEN 'AIX Server'
    WHEN c.sys_class_name = 'cmdb_ci_lb' THEN 'Load Balancer'
    WHEN c.sys_class_name = 'cmdb_ci_lb_ace' THEN 'lb_ace'
    WHEN c.sys_class_name = 'cmdb_ci_aix_lb_bigip' THEN 'lb_bigip'
    WHEN c.sys_class_name = 'cmdb_ci_linux_server' THEN 'Linux Server'
    WHEN c.sys_class_name = 'cmdb_ci_mainframe' THEN 'IBM Mainframe'
    WHEN c.sys_class_name = 'cmdb_ci_solaris_server' THEN 'Solaris Server'
    WHEN c.sys_class_name = 'cmdb_u_ci_ibmi_server' THEN 'IBMi Server'
    WHEN c.sys_class_name = 'cmdb_ u_ci_ibmi_server' THEN 'IBMi Server'
    WHEN c.sys_class_name = 'cmdb_u_server_appliance' THEN 'ServerAppliance'
        ELSE 'Unknown'
    END as "Class",

CASE 
    WHEN c.install_status = '1' THEN 'Installed'
    WHEN c.install_status = '100' THEN 'Absent'
    WHEN c.install_status = '2' THEN 'On Order'
    WHEN c.install_status = '3' THEN 'In Maintenance'
    WHEN c.install_status = '4' THEN 'Pending Install'
    WHEN c.install_status = '5' THEN 'Pending Repair'
    WHEN c.install_status = '6' THEN 'In Stock'
    WHEN c.install_status = '7' THEN 'Retired'
    WHEN c.install_status = '8' THEN 'Stolen'
    WHEN c.install_status = '9' THEN 'In Use'
                         ELSE 'Unknown'
          END As "lifecycle Status"

from
cmdb as c
join cmdb_ci ci on c.sys_id = ci.sys_id
join cmdb_ci_hardware as cih on c.sys_id = cih.sys_id
join cmdb_ci_computer cic on c.sys_id = cic.sys_id
join cmn_location as cl on cl.sys_id = c.location
where c.name is not null
`, function(err, rows, fields) {
  if (err) throw err;

 for (var i in rows) {
  console.log('the rows are', rows[i]);
}
});

connection.end();

Now when am running the code using "node filename.js" am able to print the output to my console. But i want it to be written to a json file so that i can process the data and apply some logic.

Instead of using `console.log`, you can use the `fs` module to write it to a file. What's the issue in that? — Nehal J Wani, Feb 27 '17 at 17:18
when i run the node command on this file. In the console I get the data in the json format. I need that json data into an file with json format. How can i do that? — Raghu Reddy, Feb 27 '17 at 17:54
I still don't understand how the mysql statement generates json output. Can you share the output for a sample run ? — Nehal J Wani, Feb 27 '17 at 18:00
the file name is main,js, I am running the command node main.js this is the result am getting. the rows are RowDataPacket { Name: 'AM1D**05', Region: 'Americas', 'Operating System': 'Linux', 'Host domain': 'r1', 'IP Address': '10.*.*.*', Class: 'Linux Server', 'lifecycle Status': 'Installed' } — Raghu Reddy, Feb 27 '17 at 18:30

score 0 · Accepted Answer · answered Feb 27 '17 at 19:12

0

If all you want to do is write the output of the MySQL data in JSON to a file, replace...

for (var i in rows) {
 console.log('the rows are', rows[i]);
}

...with:

fs.writeFile("/path/to/file.json", JSON.stringify(rows), function(err) {
    if (err) return console.log(err);    
    console.log("The file was saved!");
});

More about JSON.stringify() at https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/JSON/stringify

answered Feb 27 '17 at 19:12

Nehal J Wani

16,071
3
64
89

That totally worked Nehal. Thanks a lot buddy. My mistake was i was not stringifying the rows. – Raghu Reddy Feb 27 '17 at 19:20
Hi Nehal, I am trying to run multiline mysql statements in my nodejs code like this: var q = "select c.name as 'Name',"+ "cl.u_geographic_region as 'Region',"+ "CASE"+ "WHEN cic.os like '%Windows%' THEN 'Windows'"+ "WHEN cic.os like '%aix%' THEN 'AIX'"+ "WHEN cic.os like '%esx%' THEN 'ESX'"+ "WHEN cic.os like '%linux%' THEN 'Linux'"+ "WHEN cic.os like '%solaris%' THEN 'Solaris'"+ "ELSE 'UNKNOWN'"+ "END as 'Operating System',"+ in my windows environment with the help of back ticks i was able to achieve it but in linux am not able toget – Raghu Reddy Feb 28 '17 at 16:28
@RaghuReddy Please open a new question if you have one. All my answers so far to your questions have been tested on Linux. – Nehal J Wani Feb 28 '17 at 18:47
Hey Nehal, I post this new question. http://stackoverflow.com/questions/42514638/how-to-execute-multiline-mysql-query-statements-in-node-js-which-has-like-50-lin but yea, i somehow solved it by adding \n after every line. – Raghu Reddy Feb 28 '17 at 19:24

Mysql data need to be written to a json file

1 Answers1

Linked