Is it possible to Fetch data from mysql ?

jagadeesh-vj

I understand the jsreport supports mssql, mongodb and postgreSQL for template stores, saving jsreport entities.

Is it possible to just fetch data from mysql database and display in report as pdf or html based on recipe selection ?

jan_blaha

Yes.
Please take a look how to fetch data using mssql. The mysql case will be similar.
https://jsreport.net/blog/pdf-reports-in-sql-server

jagadeesh-vj

Thanks for your quick reply as always. That worked well.

mqsql installation;
npm install mysql

code:

var mysql = require('mysql');

var con = mysql.createConnection({
    host: "server-name",
    user: "user-name",
    password: "password",
    database: "sample",
    port: "3306",
});

function beforeRender(request, response, done) {
    con.connect(function(err) {
        if (err) throw err;
        con.query('select name,value from tblparameter', function(err, result, fields) {
            if (err) throw err;
            request.data = {
                config: result
            };
            done();
        });
    });
}

jan_blaha

Thanks for sharing the code.

LordShiroe

That was very helpful! Thank you!

philippeckert

Hello everybody,

I'm trying to adapt the given example to PostgreSQL, but my report is always empty. Can you tell me, what I'm doing wrong?

function beforeRender(req, res, done) {
    
const { Client } = require('pg')

const client = new Client({
  user: 'TEST',
  host: 'localhost',
  database: 'TEST',
  password: 'TEST',
  port: 5432,
})

client.connect()

client.query('SELECT lastname, firstname from TEST', (err, res) => {
  console.log(err, res)
  console.log(res.rowCount)
  client.end()
})
done();
}

It compiles and the DB connection is queried properly, I tried it with an UPDATE statement and could see the impact. My Problem is to save the results from a SELECT statement and reuse it in my template.

jan_blaha

You don't set req.data at all.
You call done before the query actually finishes.
Compare your code with the one from previous posts.

philippeckert

Thank you, I got it finally working. :)

const {
    Client
} = require('pg')

const client = new Client({
    user: 'USER',
    host: 'HOST',
    database: 'DB',
    password: 'PW',
    port: 5432,
})

async function beforeRender(req, res, done) {
    client.connect()
    const result = await client.query({
        text: 'SELECT column FROM table'
    })
    console.log(result.rowCount)
    Object.assign(req.data, {
        elements: result.rows
    })
    done();
}