3. Snowflake integration issue

Snowflake integration issue

  • A

    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
    0

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

    0
Log in to reply
 

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