after argument list sandbox.js:16



  • Postgres report

    Render request 16 queued for execution and waiting for availible worker
    info+7Starting rendering request 16 (user: admin)
    info+3Rendering template { name: Daily_State_Balance(clone), recipe: html-to-xlsx, engine: handlebars, preview: true }
    debug+4Data item not defined for this template.
    debug+3Executing script dev11db-test (beforeRender)
    error+10Error when processing render request 16 Error when evaluating custom script /Dev11/dev11db-test
    missing ) after argument list sandbox.js:16
        const res = await client.query('with balances as ( select acco_id, ROUND(SUM(CASE WHEN wallet_type_id=1 and fund_type_id=1 THEN balance ELSE 0 END), 2) ACTUAL_CASH, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=11 THEN balance ELSE 0 END), 2) CREDITS, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=16 THEN balance ELSE 0 END), 2) UNREVEALED, current_date -1  as trans_date from betcatch.account_balances where brand_id = 3 group by 1 ) select contact_details::json->'ContactDetail'->>'address4' as State, SUM(CREDITS) STATE_CREDITS, SUM(ACTUAL_CASH) STATE_ACTUAL_CASH, SUM(UNREVEALED) STATE_UNREVEALED, SUM((ACTUAL_CASH + UNREVEALED)) "state_customer_funds" from accounts a inner join balances b on a.id = b.acco_id where brand_id = 3 group by 1 by state ORDER BY 1;') 
                                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    
    SyntaxError: missing ) after argument list
        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)
    error+0Rendering request 16 finished with error in 27 ms
    

    DATA script

    async function afterRender (req, res) {
      const { Client } = require('pg')
    
      const credentials = {
        user: 'sddev',
        host: 'dev11-ro-database-01.************.us-east-2.rds.amazonaws.com',
        database: 'ats_whitelabel',
        password: '*************',
        port: 5432
      }
      // Connect with a client.
    
      async function clientDemo () {
        const client = new Client(credentials)
        await client.connect()
        const res = await client.query('with balances as ( select acco_id, ROUND(SUM(CASE WHEN wallet_type_id=1 and fund_type_id=1 THEN balance ELSE 0 END), 2) ACTUAL_CASH, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=11 THEN balance ELSE 0 END), 2) CREDITS, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=16 THEN balance ELSE 0 END), 2) UNREVEALED, current_date -1  as trans_date from betcatch.account_balances where brand_id = 3 group by 1 ) select contact_details::json->'ContactDetail'->>'address4' as State, SUM(CREDITS) STATE_CREDITS, SUM(ACTUAL_CASH) STATE_ACTUAL_CASH, SUM(UNREVEALED) STATE_UNREVEALED, SUM((ACTUAL_CASH + UNREVEALED)) "state_customer_funds" from accounts a inner join balances b on a.id = b.acco_id where brand_id = 3 group by 1 by state ORDER BY 1;') 
        await client.end()
      }
    
      const clientResult = await clientDemo()
      console.log('Time with client: ' + clientResult.rows[0].now)
    
    };
    

    Report

    <style>
        td, th {
            padding: 5px;
            text-align: right;
        }
    </style>
    <table name="Data">
        <tr>
            <td class="title" colspan="10">DAILY STATE BALANCE</td>
        </tr>
        <tr class="cell-titles">
            <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>


  • You have a syntax error in the script as the error explains. Check that line carefully.



  • this SQL correct line

    contact_details::json->'ContactDetail'->>'address4' as State,

    But I guess the script does not like this what would be the option for jsreport?


  • administrators

    hi! the fix is not anything in particular related to jsreport or the jsreport script, it is more a problem with javascript strings.

    as you know you can delimit a javascript string with either ' or ", however since your string is expected to contain SQL queries, then you are using ' inside the string when this happens javascript gets confused, because it thinks you are trying to close the string, in this case, you have two options you either use template literals `string` or escape the ' characters inside your string:

    option 1:

    `with balances as ( select acco_id, ROUND(SUM(CASE WHEN wallet_type_id=1 and fund_type_id=1 THEN balance ELSE 0 END), 2) ACTUAL_CASH, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=11 THEN balance ELSE 0 END), 2) CREDITS, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=16 THEN balance ELSE 0 END), 2) UNREVEALED, current_date -1 as trans_date from betcatch.account_balances where brand_id = 3 group by 1 ) select contact_details::json->'ContactDetail'->>'address4' as State, SUM(CREDITS) STATE_CREDITS, SUM(ACTUAL_CASH) STATE_ACTUAL_CASH, SUM(UNREVEALED) STATE_UNREVEALED, SUM((ACTUAL_CASH + UNREVEALED)) "state_customer_funds" from accounts a inner join balances b on a.id = b.acco_id where brand_id = 3 group by 1 by state ORDER BY 1;`

    option 2:

    'with balances as ( select acco_id, ROUND(SUM(CASE WHEN wallet_type_id=1 and fund_type_id=1 THEN balance ELSE 0 END), 2) ACTUAL_CASH, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=11 THEN balance ELSE 0 END), 2) CREDITS, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=16 THEN balance ELSE 0 END), 2) UNREVEALED, current_date -1 as trans_date from betcatch.account_balances where brand_id = 3 group by 1 ) select contact_details::json->\'ContactDetail\'->>\'address4\' as State, SUM(CREDITS) STATE_CREDITS, SUM(ACTUAL_CASH) STATE_ACTUAL_CASH, SUM(UNREVEALED) STATE_UNREVEALED, SUM((ACTUAL_CASH + UNREVEALED)) "state_customer_funds" from accounts a inner join balances b on a.id = b.acco_id where brand_id = 3 group by 1 by state ORDER BY 1;'

    (note that now this part \'ContactDetail\'->>\'address4\' is escaped)



  • Thanks for the help with sql

    hit this but I know I run npm install -g pg

    Error when evaluating custom script /Dev11/dev11db-test
    Unable to find module pg
    The require calls:
    require('pg')
    require('/home/ubuntu/jsreportapp/pg')
    
    
    (sandbox.js line 2:22)
    
      1 | async function afterRender (req, res) {
    > 2 |   const { Client } = require('pg')
        |                      ^
      3 |
      4 |   const credentials = {
      5 |     user: 'sddev',

  • administrators

    hit this but I know I run npm install -g pg

    you installed the pg module globally, however in order for the jsreport script to recognize such module it should be installed locally in your jsreport server project, so basically it needs to be part of the package.json of the jsreport server, you can go to the folder where your package.json is and execute npm i pg --save



  • Ok so update pg locally and restarted now have this error. This is the config for my production

    Report "dev11_Daily_State_Balance" render failed.
    
    Connection terminated
    Error: Connection terminated
        at Connection.<anonymous> (/home/ubuntu/jsreportapp/node_modules/pg/lib/client.js:132:36)
        at Object.onceWrapper (node:events:641:28)
        at Connection.emit (node:events:539:35)
        at Connection.emit (node:domain:537:15)
        at Socket.<anonymous> (/home/ubuntu/jsreportapp/node_modules/pg/lib/connection.js:57:12)
        at Socket.emit (node:events:527:28)
        at Socket.emit (node:domain:537:15)
        at TCP.<anonymous> (node:net:709:12)
        at TCP.callbackTrampoline (node:internal/async_hooks:130:17)

  • administrators

    this looks like an issue with the connection to postgres itself, have you tried running the code alone? i mean outside of jsreport, you can take all the code inside your afterRender function and put it in a file like test-connection.js inside your jsreport project, then start testing with node test-connection.js, if you hit the same error you should likely check the https://github.com/brianc/node-postgres docs, perhaps there is something else that you are missing to configure in the connection options.


Log in to reply
 

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