3. Snowflake report

Snowflake report


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

    0

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

    0

  • @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 2}}">
                <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
}
    0

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

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

    0

  • @bjrmatos @jan_blaha any update on this issue?

    0

  • Just name the res differently....

    const resFromSql = await connection.execute({

    I'm sorry but this is more about javascript general development than jsreport.
    We would like to focus this forum primarily on jsreport.

    0

  • @jan_blaha thanks the fixing the error but still don't have data

      const resFromSql = 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

}
    0
  • administrators

    @djbrody you are missing to rename also the places where your old res variable was used, you should update it to the new name resFromSql.

    also you seem to use the name detail on your template, so change in the script .rows -> .detail, see bellow for how it should look like:

    const resFromSql = 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.detail = resFromSql

    please try to analyze your code deeply if you hit any other error.

    0

  • @bjrmatos yes I am doing too many things, at the same time so sorry that I'm asking dumb questions.

    if I change the

    const resFromSql = await connection.execute({
    sqlText: 'select * FROM SUPERDRAFT_PROD.SD_REPORTS.VW_DAILY_STATE_BALANCE_SDPRO;'
  });
  req.data.detail = resFromSql

}

    if I use this

        {{#each .detail}}  or     {{#each .row}}

    then I get

    Error when evaluating engine handlebars for template /Prod11/Daily_State_Balance
each.detail doesn't match each - 55:15

(sandbox.js line 41:18)

Error: each.detail doesn't match each - 55:15
    at validateClose (/home/ubuntu/jsreportapp/node_modules/handlebars/dist/cjs/handlebars/compiler/helpers.js:28:11)
    at Object.prepareBlock (/home/ubuntu/jsreportapp/node_modules/handlebars/dist/cjs/handlebars/compiler/helpers.js:142:5)
    at Object.anonymous (/home/ubuntu/jsreportapp/node_modules/handlebars/dist/cjs/handlebars/compiler/parser.js:65:33)
    at Parser.parse (/home/ubuntu/jsreportapp/node_modules/handlebars/dist/cjs/handlebars/compiler/parser.js:367:48)
    at parseWithoutProcessing (/home/ubuntu/jsreportapp/node_modules/handlebars/dist/cjs/handlebars/compiler/base.js:46:33)
    at HandlebarsEnvironment.parse (/home/ubuntu/jsreportapp/node_modules/handlebars/dist/cjs/handlebars/compiler/base.js:52:13)
    at Object.precompile (/home/ubuntu/jsreportapp/node_modules/handlebars/dist/cjs/handlebars/compiler/compiler.js:485:17)
    at HandlebarsEnvironment.hb.precompile (/home/ubuntu/jsreportapp/node_modules/handlebars/dist/cjs/handlebars.js:42:40)
    at Object.compile (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-handlebars/lib/handlebarsEngine.js:33:52)
    at Object.compile (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-handlebars/lib/worker.js:25:43)
    at executionFn (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/render/executeEngine.js:189:42)
    at sandbox.js:41:18

info+0Render request 39 queued for execution and waiting for availible worker
info+10Starting rendering request 39 (user: admin)
info+1Rendering template { name: Daily_State_Balance, recipe: html-to-xlsx, engine: handlebars, preview: true }
debug+0Data item not defined for this template.
debug+8Executing script prod_daily_state_balance (beforeRender)
debug+381Base url not specified, skipping its injection.
    0 

  •  {{#each detail}

    gives all the lines of content as NaN

    Data item not defined for this template.

    0
  • administrators

    what is the result in the logs if you add this to your script (the console.log)?

    console.log(resFromSql)
req.data.detail = resFromSql

    this should clarify what is the SQL query returning, perhaps the shape of what the execute query returns does not match with what your template expects, it sounds to me that this is the main problem now.

    0 

  • 2022-07-12T20:33:09.165Z - info: Render request 48 queued for execution and waiting for availible worker rootId=6zazpyz7xu4jclu, id=6zazpyz7xu4jclu
2022-07-12T20:33:09.181Z - info: Starting rendering request 48 (user: admin) rootId=6zazpyz7xu4jclu, id=6zazpyz7xu4jclu
2022-07-12T20:33:09.181Z - info: Rendering template { name: Daily_State_Balance, recipe: html-to-xlsx, engine: handlebars, preview: true } rootId=6zazpyz7xu4jclu, id=6zazpyz7xu4jclu
2022-07-12T20:33:09.572Z - info: html-to-xlsx generation is starting rootId=6zazpyz7xu4jclu, id=6zazpyz7xu4jclu
2022-07-12T20:33:09.936Z - info: html-to-xlsx generation was finished rootId=6zazpyz7xu4jclu, id=6zazpyz7xu4jclu
2022-07-12T20:33:10.124Z - info: Rendering request 48 finished in 959 ms rootId=6zazpyz7xu4jclu, id=6zazpyz7xu4jclu

    Data item not defined for this template.
debug+7Executing script prod_daily_state_balance (beforeRender)
debug+399RowStatementPreExec {
  _events: [Object: null prototype] {},
  _eventsCount: 0,
  _maxListeners: undefined,
  getSqlText: [Function (anonymous)],
  getStatus: [Function (anonymous)],
  getColumns: [Function (anonymous)],
  getColumn: [Function (anonymous)],
  getNumRows: [Function (anonymous)],
  getNumUpdatedRows: [Function (anonymous)],
  getSessionState: [Function (anonymous)],
  getRequestId: [Function (anonymous)],
  getStatementId: [Function (anonymous)],
  cancel: [Function (anonymous)],
  fetchRows: [Function (anonymous)],
  streamRows: [Function (anonymous)],
  [Symbol(kCapture)]: false
}
debug+1Base url not specified, skipping its injection.
debug+0Rendering engine handlebars
    0
  • administrators

    according to the snowflake-sdk docs you are missing code to consume the rows.

    checks the docs of the snowflake carefully, by doing a general check I think the code you need should look like this in your script, but again, if it does not work, check the docs of the snowflake-sdk.

    const statement = 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;'
  });
  
const rows = []

const rowsStream = statement.streamRows()

await new Promise((resolve, reject) => {
             rowsStream.on('data', function (row) {
                rows.push(row)
            })

            rowsStream.on('end', function (row) {
                if (row) {
                  rows.push(row)
                }
            })
            
            rowsStream.on('error', function (streamError) {
                reject(streamError)
            })
})

  req.data.detail = rows
    0 

  • info+0Render request 52 queued for execution and waiting for availible worker
info+23Starting rendering request 52 (user: admin)
info+0Rendering template { name: Daily_State_Balance, recipe: html-to-xlsx, engine: handlebars, preview: true }
debug+2Data item not defined for this template.
debug+4Executing script prod_daily_state_balance (beforeRender)
warn+351Error when processing render request 52 Error when evaluating custom script /Prod11/prod_daily_state_balance
Unable to perform operation because a connection was never established. ClientError: Unable to perform operation because a connection was never established.
    at createError (/home/ubuntu/jsreportapp/node_modules/snowflake-sdk/lib/errors.js:535:15)
    at Object.exports.createClientError (/home/ubuntu/jsreportapp/node_modules/snowflake-sdk/lib/errors.js:350:10)
    at /home/ubuntu/jsreportapp/node_modules/snowflake-sdk/lib/services/sf.js:977:21
    at processTicksAndRejections (node:internal/process/task_queues:78:11)

    code

    // 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: '*********'
  });
  const statement = 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;'
  });

  const rows = []

  const rowsStream = statement.streamRows()

  await new Promise((resolve, reject) => {
    rowsStream.on('data', function (row) {
      rows.push(row)
    })

    rowsStream.on('end', function (row) {
      if (row) {
        rows.push(row)
      }
    })

    rowsStream.on('error', function (streamError) {
      reject(streamError)
    })
  })
  req.data.detail = rows
}
    0
Log in to reply
 

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