Snowflake integration issue



  • I have template to working with a node js script to get data from snowflake but it is getting data but it is not showing up in the template result.

    <html>
    
    <head>
        <style>
            {
                    {
                    asset "sd-styles.css""utf8"
                }
            }
        </style>
    </head>
            <tbody class="posts">
                <tr>
                    <th class="t-center">State</th>
                    <th class="t-center">Credits</th>
                    <th class="t-center">Actual Cash</th>
                    <th class="t-center"> Unrevealed </th>
                    <th class="t-center"> Customer Funds </th>
                </tr>
                {{#each posts}}
                <tr style="height: 20px, class="post">
                <td dclass="t-right" data-cell-type="text">{{STATE}}</td>
                    <td class="t-right" data-cell-type="number" data-cell-format-str="0.00">{{STATE_CREDITS}}</td>
                    <td class="t-right" data-cell-type="number" data-cell-format-str="0.00">{{STATE_ACTUAL_CASH}}</td>
                    <td class="t-right" data-cell-type="number" data-cell-format-str="0.00">{{STATE_UNREVEALED}}</td>
                    <td class="t-right" data-cell-type="number" data-cell-format-str="0.00">{{STATE_CUSTOMER_FUNDS}}</td>
                </tr>
                {{/each}}
            </tbody>
        </table>
    </body>
    
    </html>
    

    Script nodejs:

    async function beforeRender(req, res) {
      var snowflake = require('snowflake-sdk');
        const connection = snowflake.createConnection({
        account: '******.eu-west-2.aws',
        username: '*****',
        password: '*********' 
        //clientSessionKeepAlive: true     
      });
             connection.connect( 
              function(err, conn) {
                if (err) {
                    console.error('Unable to connect: ' + err.message);
                    } 
                else {
                    console.log('Successfully connected to Snowflake.');
                    // Optional: store the connection ID.
                    //connection_ID = conn.getId();
                    }
            }
        )
            connection.execute({
            sqlText: 'select * FROM SUPERDRAFT_PROD.SD_REPORTS.VW_DAILY_STATE_BALANCE_SDPRO',
            complete: function(err, stmt, rows) {
              if (err) {
                console.error('Failed to execute statement due to the following error: ' + err.message);
              } else {
                console.log('Number of rows produced: ' + rows.length);
                console.log(JSON.stringify(rows));
                req.data.posts = rows
              }
            }
          });
            connection.execute({
                  sqlText: 'select * FROM SUPERDRAFT_PROD.SD_REPORTS.VW_DAILY_STATE_BALANCE_SDPRO;',
                  complete: function (err, stmt, rows)
                  {
                      var stream = stmt.streamRows();
                      stream.on('data', function (row)
                      {
                          console.log(row);
                          console.log(JSON.stringify(row));
                      });
                      stream.on('end', function (row)
                      {
                          console.log('All rows consumed');
                      });
    
                      req.data.posts = stream
                  }
                })
            connection.destroy(function(err, conn) {
            if (err) {
              console.error('Unable to disconnect: ' + err.message);
            } else {
              console.log('Disconnected connection with id: ' + connection.getId());
            }
      })
    }
    

    Logs debug details:

    
    info
    +0
    Render request 18 queued for execution and waiting for availible worker
    info
    +15
    Starting rendering request 18 (user: admin)
    info
    +0
    Rendering template { name: daily_state_balance_template, recipe: chrome-pdf, engine: handlebars, preview: true }
    debug
    +1
    Data item not defined for this template.
    debug
    +4
    Executing script daily_state_balance (beforeRender)
    debug
    +532
    Base url not specified, skipping its injection.
    debug
    +1
    Rendering engine handlebars
    debug
    +32
    Executing recipe chrome-pdf
    debug
    +6
    Converting with chrome HeadlessChrome/98.0.4758.0 using chrome-pool strategy
    debug
    +64
    Page request: GET (document) file:///tmp/jsreport/autocleanup/fb23f58e-09f7-43db-ad7d-0859e4b305fb-chrome-pdf.html
    debug
    +20
    Page request finished: GET (document) file:///tmp/jsreport/autocleanup/fb23f58e-09f7-43db-ad7d-0859e4b305fb-chrome-pdf.html
    debug
    +10
    Running chrome with params {"printBackground":true,"timeout":59409,"margin":{}}
    debug
    +25
    Skipping storing report.
    info
    +1
    Rendering request 18 finished in 712 ms
    

    We can see the request is reaching to snowflake and it success

    0_1664389634100_upload-a89620f1-5998-469f-8e50-28147094a695

    Log debug details, see you can see the output from the snowflake here:

    
    info
    +0
    Render request 692 queued for execution and waiting for availible worker
    info
    +19
    Starting rendering request 692 (user: admin)
    info
    +1
    Rendering template { name: daily_state_balance_template, recipe: chrome-pdf, engine: handlebars, preview: true }
    debug
    +3
    Data item not defined for this template.
    debug
    +18
    Executing script daily_state_balance (beforeRender)
    debug
    +657
    Base url not specified, skipping its injection.
    debug
    +0
    Rendering engine handlebars
    debug
    +29
    Executing recipe chrome-pdf
    debug
    +437
    Successfully connected to Snowflake.
    error
    +157
    Failed to execute statement due to the following error: Unable to perform operation using terminated connection.
    debug
    +2
    Disconnected connection with id: 5728cff4-8369-4326-8b37-febf90f0840c
    debug
    +157
    {
      STATE: 'NH',
      STATE_CREDITS: 0,
      STATE_ACTUAL_CASH: 684510.37,
      STATE_UNREVEALED: 0,
      STATE_CUSTOMER_FUNDS: 684510.37
    }
    debug
    +0
    {
      STATE: 'ON',
      STATE_CREDITS: 0,
      STATE_ACTUAL_CASH: 1698745,
      STATE_UNREVEALED: 0,
      STATE_CUSTOMER_FUNDS: 1698745
    }
    debug
    +0
    {
      STATE: 'Ohio',
      STATE_CREDITS: 0,
      STATE_ACTUAL_CASH: 1437135,
      STATE_UNREVEALED: 0,
      STATE_CUSTOMER_FUNDS: 1437135
    }
    debug
    +0
    {
      STATE: 'Wisconsin',
      STATE_CREDITS: 0,
      STATE_ACTUAL_CASH: 1283727,
      STATE_UNREVEALED: 0,
      STATE_CUSTOMER_FUNDS: 1283727
    }
    debug
    +1
    All rows consumed
    debug
    +254
    Converting with chrome HeadlessChrome/98.0.4758.0 using chrome-pool strategy
    debug
    +300
    Page request: GET (document) file:///tmp/jsreport/autocleanup/0af73c52-35b3-42c4-a534-3f42b4f1e068-chrome-pdf.html
    debug
    +22
    Page request finished: GET (document) file:///tmp/jsreport/autocleanup/0af73c52-35b3-42c4-a534-3f42b4f1e068-chrome-pdf.html
    debug
    +29
    Running chrome with params {"printBackground":true,"timeout":59121,"margin":{}}
    debug
    +49
    Skipping storing report.
    info
    +0
    Rendering request 692 finished in 2135 ms


  • Your code badly mixes callbacks with async/await promises.
    The beforeRender already finishes, but your callbacks to connection.execute haven't been invoked yet.
    Try to take your code outside the jsreport into a plain nodejs file and debug it to get a better understanding. This isn't about jsreport but proper javascript coding. You need to set to req.data before the function beforeRender actually exits.


Log in to reply
 

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