Is it possible to Fetch data from mysql ?



  • 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 ?



  • 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



  • 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();
            });
        });
    }
    


  • Thanks for sharing the code.



  • That was very helpful! Thank you!



  • 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.



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


  • 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();
    }

Log in to reply
 

Looks like your connection to jsreport forum was lost, please wait while we try to reconnect.