server.js :
const express = require('express');
const mysql = require('mysql2');
const path = require('path');
const app = express();
app.use(express.json());
const db = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password',
database: 'db_name',
});
// Connect to MySQL
db.connect((err) => {
if (err) {
console.error('Error connecting to the database:', err);
return;
}
console.log('Connected to MySQL database');
});
// Serve static files (HTML, CSS, JS) from the 'public' folder
app.use(express.static('public'));
// Serve index.html at root
app.get('/', (req, res) => {
res.sendFile(path.join(__dirname, 'public/index.html'));
});
// Serve home.html at /home
app.get('/home', (req, res) => {
res.sendFile(path.join(__dirname, 'public/home.html'));
});
// Serve about.html at /about
app.get('/about', (req, res) => {
res.sendFile(path.join(__dirname, 'public/about.html'));
});
app.get('/get-data', (req, res) => {
db.query('SELECT * FROM Weather_Stations', (err, results) => {
if (err) {
res.status(500).send('Error fetching data from Weather_Stations');
return;
}
res.json(results);
});
});
// API endpoint to get station_id values from Weather_Stations for Weather_Data table
app.get('/get-station-ids', (req, res) => {
db.query('SELECT station_id FROM Weather_Stations', (err, results) => {
if (err) {
console.error('Error fetching station IDs:', err);
res.status(500).send('Error fetching station IDs');
return;
}
res.json(results);
});
});
// API endpoint to get data from Weather_Data
app.get('/get-weather-data', (req, res) => {
db.query('SELECT * FROM Weather_Data', (err, results) => {
if (err) {
console.error('Error fetching Weather_Data:', err);
res.status(500).send('Error fetching Weather_Data');
return;
}
res.json(results);
});
});
// --------------------------------- Genarate Weather ID --------------------------------------------
app.get('/generate-weatherData-id', async (req, res) => {
try {
const [result] = await db.query("CALL region_id_gen(?)", ['WD']);
const generatedId = result[0]?.NextNumber || null; // Extract the ID from the result
if (generatedId) {
res.status(200).json({ weatherData_id: `WD-${generatedId}` });
} else {
res.status(500).json({ error: 'Failed to generate weatherData_id' });
}
} catch (error) {
console.error("Error generating weatherData_id:", error);
res.status(500).json({ error: 'Internal server error' });
}
});
// --------------------------------- Start the Server --------------------------------------------
app.use((req, res, next) => {
console.log(`Request URL: ${req.url}`);
next();
});
const PORT = 3000;
app.listen(PORT, () => {
console.log(`Server is running on port ${PORT}`);
});
error:
Server is running on port 3000
Connected to MySQL database
You have tried to call .then(), .catch(), or invoked await on the result of query that is not a promise, which is a programming error. Try calling con.promise().query(), or require(‘mysql2/promise’) instead of ‘mysql2’ for a promise-compatible version of the query interface. To learn how to use async/await or Promises check out documentation at https://sidorares.github.io/node-mysql2/docs#using-promise-wrapper, or the mysql2 documentation at https://sidorares.github.io/node-mysql2/docs/documentation/promise-wrapper
Error generating weatherData_id: Error: You have tried to call .then(), .catch(), or invoked await on the result of query that is not a promise, which is a programming error. Try calling con.promise().query(), or require(‘mysql2/promise’) instead of ‘mysql2’ for a promise-compatible version of the query interface. To learn how to use async/await or Promises check out documentation at https://sidorares.github.io/node-mysql2/docs#using-promise-wrapper, or the mysql2 documentation at https://sidorares.github.io/node-mysql2/docs/documentation/promise-wrapper
at Query.then (C:UsersPramudith RanganaDocumentsJavaScript ProjectsmySqlDataShownode_modulesmysql2libcommandsquery.js:43:11)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
