97 lines
No EOL
2.8 KiB
Markdown
97 lines
No EOL
2.8 KiB
Markdown
mysql connect
|
|
|
|
Install it first using `npm i mysql2` as mysql v1 may have some issues
|
|
```js
|
|
|
|
const mysql = require("mysql");
|
|
|
|
const con = mysql.createConnection({
|
|
host: "localhost",
|
|
user: "root",
|
|
password: "password",
|
|
database: "hospitals",
|
|
multipleStatements: true,
|
|
});
|
|
|
|
app.post("/register", (req, res) => {
|
|
const { name, address, email, phone_no, password } = req.body;
|
|
const { patient_photo } = req.files;
|
|
const patientPhotoPathname = patient_photo.path;
|
|
|
|
|
|
// Store the registration details
|
|
con.query(
|
|
`use hospitals;
|
|
insert into patients(name,address,email,phone_no,password,patient_photo_path)
|
|
values ('${name}','${address}','${email}','${phone_no}','${password}','${patientPhotoPathname}')
|
|
`,
|
|
(err, result) => {
|
|
if (err) {
|
|
console.log(err);
|
|
|
|
res
|
|
.status(400)
|
|
.json("Error while registering. Email address must be unique");
|
|
} else {
|
|
console.log(result);
|
|
res.json("Registered");
|
|
}
|
|
}
|
|
);
|
|
}
|
|
|
|
app.get("/fetch", (req, res) => {
|
|
const { hospital_id } = req.body;
|
|
|
|
// console.log("body:", req.body);
|
|
const sqlquery = `select hospital_id,count(psychiatrists.id) as psychiatrist_count,
|
|
sum(patients_count) as total_patients_count,hospital.name
|
|
from psychiatrists,hospital
|
|
where hospital.id=psychiatrists.hospital_id and hospital_id=${hospital_id};
|
|
select id,name,patients_count from psychiatrists
|
|
where hospital_id=${hospital_id};
|
|
`;
|
|
|
|
con.query(sqlquery, (err, result) => {
|
|
if (err) {
|
|
res.status(500).json("Some error while fetching");
|
|
console.log(err);
|
|
return;
|
|
}
|
|
|
|
console.log(result);
|
|
}
|
|
```
|
|
|
|
- handling disconnection due to idle timeout
|
|
```js
|
|
function handleDisconnect() {
|
|
con = mysql.createConnection(dbConfig); // Recreate the connection, since the old one cannot be reused.
|
|
con.connect(function onConnect(err) {
|
|
// The server is either down
|
|
if (err) {
|
|
// or restarting (takes a while sometimes).
|
|
console.log("error when connecting to db:", err);
|
|
setTimeout(handleDisconnect, 10000); // We introduce a delay before attempting to reconnect,
|
|
} // to avoid a hot loop, and to allow our node script to
|
|
}); // process asynchronous requests in the meantime.
|
|
// If you're also serving http, display a 503 error.
|
|
con.on("error", function onError(err) {
|
|
console.log("db error", err);
|
|
// if (err.code == "PROTOCOL_CONNECTION_LOST") {
|
|
// // Connection to the MySQL server is usually
|
|
// handleDisconnect(); // lost due to either server restart, or a
|
|
// } else {
|
|
// // connnection idle timeout (the wait_timeout
|
|
// throw err; // server variable configures this)
|
|
// }
|
|
handleDisconnect();
|
|
});
|
|
}
|
|
handleDisconnect();
|
|
|
|
con.on("connection", () => {
|
|
console.log("db connected");
|
|
});
|
|
|
|
``` |