Snowflake report

djbrody

connected with Snowflake SDK for node

Report "Daily_State_Balance" render failed.

Cell not found, make sure there are td elements inside tr
Error: Cell not found, make sure there are td elements inside tr
    at addRow (/home/ubuntu/jsreportapp/node_modules/html-to-xlsx/lib/tableToXlsx.js:71:11)
    at /home/ubuntu/jsreportapp/node_modules/html-to-xlsx/lib/tableToXlsx.js:45:7
    at /home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-html-to-xlsx/lib/htmlToXlsxProcess.js:105:17
    at new Promise (<anonymous>)
    at Object.getRows (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-html-to-xlsx/lib/htmlToXlsxProcess.js:99:16)
    at tableToXlsx (/home/ubuntu/jsreportapp/node_modules/html-to-xlsx/lib/tableToXlsx.js:44:17)
    at async convert (/home/ubuntu/jsreportapp/node_modules/html-to-xlsx/lib/conversion.js:53:20)
    at async scriptHtmlToXlsxProcessing (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-html-to-xlsx/lib/htmlToXlsxProcess.js:57:20)
    at async module.exports (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-html-to-xlsx/lib/recipe.js:120:18)
    at async invokeRender (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/render/render.js:95:5)
    at async WorkerReporter._render (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/render/render.js:147:7)
    at async /home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/reporter.js:179:19
    at async Domain.<anonymous> (/home/ubuntu/jsreportapp/node_modules/@jsreport/advanced-workers/lib/workerHandler.js:141:19)

Data script

// Use the "beforeRender" or "afterRender" hook
// to manipulate and control the report generation
async function afterRender (req, res) {
// Load the Snowflake Node.js driver.
var snowflake = require('snowflake-sdk');
// Create a Connection object that we can use later to connect.
var connection = snowflake.createConnection({
    account: 'gm******.eu-west-2',
    username: '********',
    password: '****************'
  });

  // Try to connect to Snowflake, and check whether the connection was successful.
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;'
});

};

Template report

<style>
    td, th {
        padding: 10px;
        text-align: right;
    }
</style>
<table name="Data">
    <tr>
        <td class="title" colspan="10">DAILY STATE BALANCE</td>
    </tr>
    <tr class="cell-titles">
    <tr>
        <th>STATE</th>
        <th>CREDITS</th>
        <th>ACTUAL_CASH</th>
        <th>UNREVEALED</th>
        <th>CUSTOMER_FUNDS</th>
    </tr>
    {{#each rows}}
        <tr>
            <td>{{{STATE}}}</td>
            <td>{{{STATE_CREDITS}}}</td>
            <td>{{{STATE_ACTUAL_CASH}}}</td>
            <td>{{{STATE_UNREVEALED}}}</td>
            <td>{{{STATE_CUSTOMER_FUNDS}}}</td>
        </tr>
    {{/each}}
</table>

I have checked the query in db tools and here is the sample data with table headers.

STATE	STATE_CREDITS	STATE_ACTUAL_CASH	STATE_UNREVEALED	STATE_CUSTOMER_FUNDS
"NH"	0.00	392278.37	0.00	392278.37
"Wisconsin"	0.00	583727.00	0.00	583727.00
"Ohio"	0.00	737135.00	0.00	737135.00
"ON"	0.00	998745.00	0.00	998745.00

djbrody

This post is deleted!

jan_blaha

Unended tag:

 <tr class="cell-titles">

djbrody

Thanks that worked for snowflake

djbrody

@jan_blaha no data populated

info+0Render request 6 queued for execution and waiting for availible worker
info+19Starting rendering request 6 (user: admin)
info+1Rendering template { name: Daily_State_Balance, recipe: html-to-xlsx, engine: handlebars, preview: true }
debug+2Data item not defined for this template.
debug+3Executing script snowfake-daily-balance (beforeRender)
debug+14Base url not specified, skipping its injection.
debug+0Rendering engine handlebars
debug+16Executing recipe html-to-xlsx
info+0html-to-xlsx generation is starting
info+337html-to-xlsx generation was finished
debug+165Executing script snowfake-daily-balance (afterRender)
debug+454Skipping storing report.
info+1Rendering request 6 finished in 1012 ms

djbrody

Updated report to match sample exl

<html>
    <head>
        <style>
            {{asset "sd-styles.css" "utf8"}}
        </style>
    </head>
    <body>
        <table name="daily_state_balance">
            <tbody class="head">
                <tr>
                    <td></td>
                    <td colspan="5" class="title">DAILY STATE BALANCE</td>
                    <td></td>
                </tr>
                <tr>
                    {{generateEmptyCell 7}}
                </tr>
                <tr>
                    {{generateEmptyCell 1}}
                    <td class="label content border-top border-left">SITE</td>
                    <td class="content border-top">igamingsd.com</td>
                    {{generateEmptyCell 1 "content border-top"}}
                    <td class="label content border-top">Report Date</td>
                    <td class="content border-top border-right t-right" data-cell-type="date" data-cell-format-str="dd-mm-yyyy">{{nowStr}}</td>
                    {{generateEmptyCell 1}}
                </tr>
                <tr>
                    {{generateEmptyCell 1}}
                    <td class="label content border-bottom border-left">Month</td>
                    <td class="content border-bottom">{{month}}</td>
                    {{generateEmptyCell 1 "content border-bottom"}}
                    <td class="label content border-bottom">License</td>
                    <td class="content border-bottom border-right t-right">ORC</td>
                    {{generateEmptyCell 1}}
                </tr>
                <tr>
                    {{generateEmptyCell 7}}
                </tr>
            </tbody>
            <tbody class="detail">
                <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 detail}}
                    <tr class="{{oddClassName 5}}">
                        <td dclass="t-right" data-cell-type="text">{{{STATE}}}</td>
                        <td class="t-right" data-cell-type="number">{{{STATE_CREDITS}}}</td>
                        <td class="t-right" data-cell-type="number">{{{STATE_ACTUAL_CASH}}}</td>
                        <td class="t-right" data-cell-type="number">{{{STATE_UNREVEALED}}}</td>
                        <td class="t-right" data-cell-type="number">{{{STATE_CUSTOMER_FUNDS}}}</td>
                    </tr>
                {{/each}}
            </tbody>
        </table>
    </body>
</html>

const moment = require('moment')
const rowOffset = 5

function nowStr () {
    return moment().format('YYYY-MM-DD')
}

function generateEmptyCell (repeat, className) {
    const cells = []

    for (let i = 0; i < repeat; i++) {
        cells.push(`<td class="empty-cell ${className || ''}"></td>`)
    }

    return new Handlebars.SafeString(cells.join(''))
}

function oddClassName (index) {
    return (index + 1) % 2 !== 0 ? 'odd' : ''
}

function sum (a, b) {
    return a + b
}

function getDetailRowIndex (index) {
    return (index + 1) + rowOffset
}

djbrody

created a js file to test

ubuntu@jsreports:~/jsreportapp$ node testsf-conn.js

Start: 1 tests loaded
✓ testsf-conn.js Math.random() should return a number between 0 and 1 0.2ms
Completed in 0ms. Pass: 1, fail: 0, skip: 0.

    Tom
} = require('test-runner')
const assert = require('assert').strict
const fetch = require('node-fetch')

const tom = new Tom()
tom.test('Math.random() should return a number between 0 and 1', function () {
    const result = Math.random()
    async function beforeRender(req, res) {
        // Load the Snowflake Node.js driver.
        var snowflake = require('snowflake-sdk');
        // Create a Connection object that we can use later to connect.
        var connection = snowflake.createConnection({
            account: 'gm----------.-west-2',
            username: '--------',
            password: '----------'
        })

        // Try to connect to Snowflake, and check whether the connection was successful.
        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('Successfully executed statement: ' + stmt.getSqlText());
                }
            }
        });
    }
    assert.ok(result >= 0 && result <= 1)
})
module.exports = tom

This may help some test.

djbrody

0_1656593540440_Screen Shot 2022-06-30 at 8.51.21 AM.png

@jan_blaha my template is not displaying any data.

djbrody

I have tried select * and sqlText: 'select STATE, STATE_CREDITS, STATE_ACTUAL_CASH, STATE_UNREVEALED, STATE_CUSTOMER_FUNDS FROM SUPERDRAFT_PROD.SD_REPORTS.VW_DAILY_STATE_BALANCE_SDPRO;' same result no data

info+0Render request 4 queued for execution and waiting for availible worker
info+10Starting rendering request 4 (user: admin)
info+1Rendering template { name: Daily_State_Balance, recipe: html-to-xlsx, engine: handlebars, preview: true }
debug+1Data item not defined for this template.
debug+5Executing script prod_daily_state_balance (beforeRender)
debug+448Base url not specified, skipping its injection.
debug+0Rendering engine handlebars
debug+22Executing recipe html-to-xlsx
info+0html-to-xlsx generation is starting
info+374html-to-xlsx generation was finished
debug+160Skipping storing report.
info+1Rendering request 4 finished in 1022 ms

bjrmatos

can you show what your "prod_daily_state_balance" script contains? perhaps you are missing there to propagate the data to the template after you execute the query

djbrody

<html>

<head>
    <style>
        {
                {
                asset "sd-styles.css""utf8"
            }
        }
    </style>
</head>

<body>
    <table name="daily_state_balance">
        <tbody class="head">
            <tr>
                <td></td>
                <td colspan="5" class="title">DAILY STATE BALANCE</td>
                <td></td>
            </tr>
            <tr>
                {{generateEmptyCell 7}}
            </tr>
            <tr>
                {{generateEmptyCell 1}}
                <td class="label content border-top border-left">SITE</td>
                <td class="content border-top">igamingsd.com</td>
                {{generateEmptyCell 1 "content border-top"}}
                <td class="label content border-top">Report Date</td>
                <td class="content border-top border-right t-right" data-cell-type="date"
                    data-cell-format-str="dd-mm-yyyy">{{nowStr}}</td>
                {{generateEmptyCell 1}}
            </tr>
            <tr>
                {{generateEmptyCell 1}}
                <td class="label content border-bottom border-left">Month</td>
                <td class="content border-bottom">{{month}}</td>
                {{generateEmptyCell 1 "content border-bottom"}}
                <td class="label content border-bottom">License</td>
                <td class="content border-bottom border-right t-right">ORC</td>
                {{generateEmptyCell 1}}
            </tr>
            <tr>
                {{generateEmptyCell 7}}
            </tr>
        </tbody>
        <tbody class="detail">
            <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 detail}}
            <tr class="{{oddClassName 10}}">
                <td dclass="t-right" data-cell-type="text">{{{STATE}}}</td>
                <td class="t-right" data-cell-type="number">{{{STATE_CREDITS}}}</td>
                <td class="t-right" data-cell-type="number">{{{STATE_ACTUAL_CASH}}}</td>
                <td class="t-right" data-cell-type="number">{{{STATE_UNREVEALED}}}</td>
                <td class="t-right" data-cell-type="number">{{{STATE_CUSTOMER_FUNDS}}}</td>
            </tr>
            {{/each}}
        </tbody>
    </table>
</body>

</html>

const moment = require('moment')
const rowOffset = 5

function nowStr() {
    return moment().format('YYYY-MM-DD')
}

function generateEmptyCell(repeat, className) {
    const cells = []

    for (let i = 0; i < repeat; i++) {
        cells.push(`<td class="empty-cell ${className || ''}"></td>`)
    }

    return new Handlebars.SafeString(cells.join(''))
}

function oddClassName(index) {
    return (index + 1) % 2 !== 0 ? 'odd' : ''
}

function sum(a, b) {
    return a + b
}

function getDetailRowIndex(index) {
    return (index + 1) + rowOffset
}

bjrmatos

@djbrody this is the template, but what i want to see is the content of the entity with the name prod_daily_state_balance

djbrody

[0_1656622497674_Daily_State_Balance.zip](Uploading 100%)

djbrody

@bjrmatos see attached zip of the folder

bjrmatos

try to upload the zip again, wait a bit more after it no longer says Uploading 100% , sometimes the upload takes a bit more to be completed and when the reply is submitted earlier the attachment is lost

djbrody

[2_1656637846929_helpers.js](Uploading 100%) [1_1656637846929_content.handlebars](Uploading 100%) [0_1656637846928_config.json](Uploading 100%)

djbrody

@bjrmatos were you able to get the files? it does say I don't have enough privilege to upload

djbrody

@bjrmatos not sure but i could not upload here are all the files

prod_daily_state_balance
Config.json

{
    "name": "prod_daily_state_balance",
    "scope": "template",
    "shortid": "ZKAnvq80BQ",
    "creationDate": {
        "$$date": 1655932737760
    },
    "modificationDate": {
        "$$date": 1656596411821
    },
    "inheritedReadPermissions": [],
    "inheritedEditPermissions": [],
    "_id": "J766MARMirLz7Od0",
    "$entitySet": "scripts"
}

content.js

// Use the "beforeRender" or "afterRender" hook
// to manipulate and control the report generation
async function beforeRender(req, res) {
  // Load the Snowflake Node.js driver.
  var snowflake = require('snowflake-sdk');
  // Create a Connection object that we can use later to connect.
  var connection = snowflake.createConnection({
    account: 'gm36774.eu-west-2',
    username: '-----------',
    password: '----------------------'
  });

  // Try to connect to Snowflake, and check whether the connection was successful.
  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 STATE, STATE_CREDITS, STATE_ACTUAL_CASH, STATE_UNREVEALED, STATE_CUSTOMER_FUNDS FROM SUPERDRAFT_PROD.SD_REPORTS.VW_DAILY_STATE_BALANCE_SDPRO;'
  });

}

Daily_State_Balance
config.json

{
    "name": "Daily_State_Balance",
    "engine": "handlebars",
    "recipe": "html-to-xlsx",
    "data": null,
    "shortid": "aKnm5hPi_",
    "htmlToXlsx": {
        "htmlEngine": "chrome"
    },
    "creationDate": {
        "$$date": 1655934139734
    },
    "modificationDate": {
        "$$date": 1655994759371
    },
    "inheritedReadPermissions": [],
    "inheritedEditPermissions": [],
    "_id": "AbP1PwV1JjhNJSv1",
    "$entitySet": "templates",
    "scripts": [
        {
            "shortid": "ZKAnvq80BQ"
        }
    ]
}

content.handlebars

<html>

<head>
    <style>
        {
                {
                asset "sd-styles.css""utf8"
            }
        }
    </style>
</head>

<body>
    <table name="daily_state_balance">
        <tbody class="head">
            <tr>
                <td></td>
                <td colspan="5" class="title">DAILY STATE BALANCE</td>
                <td></td>
            </tr>
            <tr>
                {{generateEmptyCell 7}}
            </tr>
            <tr>
                {{generateEmptyCell 1}}
                <td class="label content border-top border-left">SITE</td>
                <td class="content border-top">igamingsd.com</td>
                {{generateEmptyCell 1 "content border-top"}}
                <td class="label content border-top">Report Date</td>
                <td class="content border-top border-right t-right" data-cell-type="date"
                    data-cell-format-str="dd-mm-yyyy">{{nowStr}}</td>
                {{generateEmptyCell 1}}
            </tr>
            <tr>
                {{generateEmptyCell 1}}
                <td class="label content border-bottom border-left">Month</td>
                <td class="content border-bottom">{{month}}</td>
                {{generateEmptyCell 1 "content border-bottom"}}
                <td class="label content border-bottom">License</td>
                <td class="content border-bottom border-right t-right">ORC</td>
                {{generateEmptyCell 1}}
            </tr>
            <tr>
                {{generateEmptyCell 7}}
            </tr>
        </tbody>
        <tbody class="detail">
            <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 detail}}
            <tr class="{{oddClassName 17}}">
                <td dclass="t-right" data-cell-type="text">{{{STATE}}}</td>
                <td class="t-right" data-cell-type="number">{{{STATE_CREDITS}}}</td>
                <td class="t-right" data-cell-type="number">{{{STATE_ACTUAL_CASH}}}</td>
                <td class="t-right" data-cell-type="number">{{{STATE_UNREVEALED}}}</td>
                <td class="t-right" data-cell-type="number">{{{STATE_CUSTOMER_FUNDS}}}</td>
            </tr>
            {{/each}}
        </tbody>
    </table>
</body>

</html>

helper.js

const moment = require('moment')
const rowOffset = 5

function nowStr() {
    return moment().format('YYYY-MM-DD')
}

function generateEmptyCell(repeat, className) {
    const cells = []

    for (let i = 0; i < repeat; i++) {
        cells.push(`<td class="empty-cell ${className || ''}"></td>`)
    }

    return new Handlebars.SafeString(cells.join(''))
}

function oddClassName(index) {
    return (index + 1) % 2 !== 0 ? 'odd' : ''
}

function sum(a, b) {
    return a + b
}

function getDetailRowIndex(index) {
    return (index + 1) + rowOffset
}

jan_blaha

Your beforeRender script will be the problem.

Problem 1 is wrong callback and promises handling. If you call connect like this, the callback function is invoked anytime in the future when the script can be already finished.

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

I didn't study your library API, but you probably want to do this

await connection.connect(...)

Problem 2, is that you don't set the req.data anywhere there?
You probably want to do something close to this, but again, I don't know what structures library you use returns.

  const res = await connection.execute({
    sqlText: 'select STATE, STATE_CREDITS, STATE_ACTUAL_CASH, STATE_UNREVEALED, STATE_CUSTOMER_FUNDS FROM SUPERDRAFT_PROD.SD_REPORTS.VW_DAILY_STATE_BALANCE_SDPRO;'
  });
  req.data.rows = res

djbrody

Report "Daily_State_Balance" render failed.

Error when evaluating custom script /Prod11/prod_daily_state_balance
Identifier 'res' has already been declared
sandbox.js:26
    const res = await connection.execute({
          ^

SyntaxError: Identifier 'res' has already been declared
    at new Script (node:vm:100:7)
    at doCompileScript (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/createSandbox.js:224:14)
    at run (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/createSandbox.js:169:72)
    at WorkerReporter._runInSandbox (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/runInSandbox.js:163:12)
    at WorkerReporter.runInSandbox (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/reporter.js:152:17)
    at async executeScript (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-scripts/lib/executeScript.js:122:12)
    at async Scripts._runScript (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-scripts/lib/worker.js:140:30)
    at async Scripts.handleBeforeRender (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-scripts/lib/worker.js:64:7)
    at async ListenerCollection.fire (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/shared/listenerCollection.js:157:21)
    at async beforeRender (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/render/render.js:25:5)
    at async WorkerReporter._render (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/render/render.js:146:7)
    at async /home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/reporter.js:179:19
    at async Domain.<anonymous> (/home/ubuntu/jsreportapp/node_modules/@jsreport/advanced-workers/lib/workerHandler.js:141:19)

@jan_blaha here is the connection

// Use the "beforeRender" or "afterRender" hook
// to manipulate and control the report generation
async function beforeRender(req, res) {
// Load the Snowflake Node.js driver.
var snowflake = require('snowflake-sdk');
// Create a Connection object that we can use later to connect.
var connection = snowflake.createConnection({
account: 'gm36774.eu-west-2',
username: 'dbrody',
password: 'k5MeAqgNs7hgQZ3'
});

// Try to connect to Snowflake, and check whether the connection was successful.
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();
}
}
);

const res = await connection.execute({
sqlText: 'select STATE, STATE_CREDITS, STATE_ACTUAL_CASH, STATE_UNREVEALED, STATE_CUSTOMER_FUNDS FROM SUPERDRAFT_PROD.SD_REPORTS.VW_DAILY_STATE_BALANCE_SDPRO;'
});
req.data.rows = res
}

djbrody

@bjrmatos @jan_blaha any update on this issue?