Node-oracledb: return JSON data?

[vrmerlin]

I'm trying to get up to speed with oracledb and orawrap, and am unclear on something. The execute() results I get back are arrays, without any metadata (column names). Is it possible to get back JSON structures, so I can just pass the results back through a REST interface?

[vrmerlin]

Never mind, it looks like this will give me that:

oracledb.outFormat = oracledb.OBJECT;

[cjbj]

We should at least use the word JSON in the doc so you can search for it. I've made a note.

[dmcghan]

@vrmerlin To be clear, the driver doesn't return JSON data. The driver returns native JavaScript data types in 2 different formats as specified by the outFormat property you found: array or object. In either case the results object will be an array, the difference refers to the elements within the array.

The default outFormat is array which means that each element in the results array will be an array of values representing the results of 1 row from your query. The object format gives you an object for each element where the column names/aliases from the query will be used as keys for the values. The array format is more efficient while the object format is often more convenient.

Because JSON, which is a data interchange format, is based on native JavaScript objects it's easy to confuse the two. To put it simply: JSON is the serialized (made into a string) representation of a JavaScript object minus the dates and functions. To convert data to and from JSON format in Node.js you can use the global JSON object:

var jsObj = {};
var jsonRep;

jsObj.name = "Ben";
jsObj.age = 21;

jsonRep = JSON.stringify(jsObj);

console.log(jsonRep);
//outputs this string value: {"name":"Ben","age":21}

jsObj = JSON.parse(jsonRep); //creates a new object with the same properties and values as before

Both the array and object outFormats supported by the driver can be stringified in JSON format.

[vrmerlin]

OK, right. I knew the difference but was bring sloppy in my terminology. Good to clarify though.

[annjawn]

So I am a little lost here. If oracledb.OBJECT returns an object and I am trying to add this object to a JWT token payload, I keep getting an error which say's "Value" must be an object. So then I tried JSON.stringify(results.rows) which works fine but then initializing the JWT token like so --> jwt.sign(payload, config.jwtSecretKey, {expiresIn: 60*5} where var payload = JSON.stringify(results.rows); gives me an error says that a string value cannot have expiresIn which makes sense since a string cannot have a property. I need to pass the results.rows in the payload to be stored at the localStorage (of the browser) since it will contain permission information for the web app for a user who logs into the app, and it also needs to have an expiration. Not sure what I am doing wrong here.

[dmcghan]

@annjawn You should open a new issue for this question. If you do, please provide some code that demonstrates the issue. On the off chance that they will help, have a look at this post and this presentation. Here's the content from the presentation (see /demos/demo-02-securing-a-route/solution).

[annjawn]

@dmcghan yes Dan, i used exactly your post to build an authentication and authorization system which is working fine. But unlike your login.js code where you basically build the jwt payload using just the first row of the query's resultset, like so -

payload = { sub: user.email, role: user.role };

I want to send the entire result set (atleast more than 1 row) of the select query in the payload. I wasn't able to use JSON.Stringify(results.rows) directly into the payload, but as a workaround what I did is I simply built a payload like

jsonRep = JSON.Stringify(results.rows) so this token: jwt.sign({data: jsonRep}, config.jwtSecretKey, {expiresIn: 60*5}) worked.

[dmcghan]

Yeah, I was thinking the same thing... :)