Error while executing user script. Unexpected token '.' evaluate-user-script.js:15

[djbrody]

Error while executing user script. Unexpected token '.'
evaluate-user-script.js:15
    Object.assign(req.data, {dbo_Account.recordset});
                                        ^

SyntaxError: Unexpected token '.'
    at new Script (node:vm:99:7)
    at VMScript.compile (/home/ubuntu/jsreportapp/node_modules/jsreport-core/lib/render/safeSandbox.js:174:26)
    at VM.run (/home/ubuntu/jsreportapp/node_modules/vm2/lib/main.js:216:10)
    at run (/home/ubuntu/jsreportapp/node_modules/jsreport-core/lib/render/safeSandbox.js:183:19)
    at module.exports (/home/ubuntu/jsreportapp/node_modules/jsreport-scripts/lib/scriptEvalChild.js:170:5)
    at IncomingMessage.<anonymous> (/home/ubuntu/jsreportapp/node_modules/script-manager/lib/worker-servers.js:279:47)
    at IncomingMessage.emit (node:events:390:22)
    at IncomingMessage.EventEmitter.emit (node:domain:532:15)
    at endReadableNT (node:internal/streams/readable:1307:12)
    at processTicksAndRejections (node:internal/process/task_queues:81:21)

Config file jsreport.config.json:

 "extensions": {
    "authentication": {
      "cookieSession": {
        "secret": "_ih03wiOe3j~KAhs"
      },
      "admin": {
        "username": "admin",
        "password": "*************"
      },
      "enabled": true
    },
    "sample-template": {
      "createSamples": true
    },
    "scripts": {
      "strategy": "http-server"
    },
    "scripts": {
      "allowedModules": [ "mssql"]
     }
  }
}

Database Script for mssql:

const sql = require("mssql");
const config = {
    "user": "ELSReadOnly",
    "password": "*************************",
    "server": "els*****.******.******.com",
    "database": "ELSReadyOnly",
    options: {
        encrypt: true
    }
}
async function beforeRender(req, res) {
    await sql.connect(config);
    const sqlReq = new sql.Request();
    const recordset = await sqlReq.query('select * from dbo_Account')
    Object.assign(req.data, {dbo_Account.recordset});
    const person=req.data.dbo_Account.recordset;
    req.data.dbo_Account=dbo_Account;
    console.log(dbo_Account);
    
}
sql.close()

Report test:

<table>
    {{#each dbo_Account}}
    <tr>
        <td>{{AccountId}}</td>
        <td>{{AccountName}}</td>
    </tr>
    {{/each}}
</table>

[bjrmatos]

hi @djbrody

this is invalid javascript

Object.assign(req.data, {dbo_Account.recordset})

you can change it to be

Object.assign(req.data, { dbo_Account: { recordset } })

or

req.data.dbo_Account = { recordset }

[djbrody]

Thanks for the help... as I am new to jsreports. now have this error. I know the connection is open and working.

ConnectionError: Login failed for user 'ELSReadOnly'.
    at Connection.<anonymous> (/home/ubuntu/jsreportapp/node_modules/mssql/lib/tedious/connection-pool.js:68:17)
    at Object.onceWrapper (node:events:485:26)
    at Connection.emit (node:events:378:20)
    at Connection.EventEmitter.emit (node:domain:532:15)
    at Connection.message (/home/ubuntu/jsreportapp/node_modules/tedious/lib/connection.js:2148:18)
    at Connection.dispatchEvent (/home/ubuntu/jsreportapp/node_modules/tedious/lib/connection.js:1279:15)
    at MessageIO.<anonymous> (/home/ubuntu/jsreportapp/node_modules/tedious/lib/connection.js:1139:14)
    at MessageIO.emit (node:events:378:20)
    at MessageIO.EventEmitter.emit (node:domain:532:15)
    at Message.<anonymous> (/home/ubuntu/jsreportapp/node_modules/tedious/lib/message-io.js:46:14)

[bjrmatos]

hmm, i have no clue, with the error message we just know that the tedious rejected the connection because probably bad credentials for the ELSReadOnly user, are you sure that you are using the right credentials? this is probably just a mistake in the code that does the connection to your database, I recommend you to try the connection first in a normal node.js file (outside of jsreport script) and verify that it works, that should be easy because you just need to commend some parts of the scripts that are jsreport specific, then copy the code in a file and try it with normal node.js execution

[djbrody]

Well, thanks for the direction I did some research and found the issue with our SQL requiring a little different formatting to connect, this may help others.

const sql = require("mssql");

const config = {
  server: "els********.*********.com",
  port: 1433,
  authentication: {
    options : {
        type: "default",
        userName:"ELSReadOnly",
        password:"****************************"
    }
}, 
  options: {
    database: "ELSReadOnly",
    enableArithAbort: true,
    trustServerCertificate: true
  }
}

The current issue is trying to see how database select differed from straight select on SQL.

SELECT AccountId, AccountCreatedDatetime, AccountName, LastCalculatedBalance, AccountDescription, CanOnlyCredit, CurrencyCode, AccountStatusId, AccountUseId, AccountTypeId, BrokerId, ReplicationStatusID, ReplicationStatusDateTime
FROM ELSReadOnly.dbo.Account;

Now we are down to this issue, any guidance to make this work and be simpler would be great.

ReferenceError: Account is not defined
    at Object.beforeRender (evaluate-user-script.js:25:34)
    at processTicksAndRejections (node:internal/process/task_queues:94:5)

below is the function in the jsreport script

async function beforeRender(req, res) {
    await sql.connect(config);
    const sqlReq = new sql.Request();
    const recordset = await sqlReq.query('select * from ELSReadOnly.dbo.Account')
    Object.assign(req.data, { Account: { recordset } })
    const person=req.data.Account.recordset;
    req.data.ELSReadOnly.Account=Account;
    console.log(ELSReadOnly.dbo.Account);
    
}
sql.close()