# SQLite data into PDF report

**URL:** <https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782>\
**Category:** General\
**Created:** [29 July 2022 12:59 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782 "2022-07-29T12:59:14Z")\
**Posts on this page:** 17\
**Page:** 2

<div class="post-metadata">

**Author:** ![SplitSecComp](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/splitseccomp/32/39802_2.png) [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)\
**Post date:** [30 July 2022 13:00 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/24 "2022-07-30T13:00:19Z")

</div>

i have one more question 🙂

how can i limit/change latitude, longitude, accuracy to 2 decimal places in the report?

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [30 July 2022 13:05 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/25 "2022-07-30T13:05:30Z")

</div>

i had the same problem with some tempratures that i was saving in my db as a full float  
decided that it would be cleaner to do it from the sqlite sql query

**Example :**

```auto
msg.topic = `SELECT round(latitude, 2) as lat, round(longitude, 2) as lon FROM yourTable LIMIT 10` 

```

---

<div class="post-metadata">

**Author:** ![SplitSecComp](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/splitseccomp/32/39802_2.png) [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)\
**Post date:** [30 July 2022 13:24 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/26 "2022-07-30T13:24:47Z")

</div>

Many thanks.

Works perfectly and yes it is a better idea to limit it with the query.

Thanks a lot.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [30 July 2022 15:19 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/27 "2022-07-30T15:19:27Z")

</div>

Though 2decimal places for lat and lon isn’t very accurate

---

<div class="post-metadata">

**Author:** ![SplitSecComp](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/splitseccomp/32/39802_2.png) [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)\
**Post date:** [4 August 2022 09:01 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/28 "2022-08-04T09:01:20Z")

</div>

Good Morning.

Can you kindly assist me again please. I have added a second table to the report that i want to populate with a second SQL query.

How do i get the second SQL query's data to the second table?

Again many thanks.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [4 August 2022 10:58 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/29 "2022-08-04T10:58:44Z")

</div>

Run your sql queries, saving each to a separate msg property or context, then you would add each saved sql return to the table as shown above. Instead of msg.payload use the saved msg property, i.e msg.sqlquery1[0] and msg.sqlquery1, where sqlquery1 is where you saved the sql query result.

---

<div class="post-metadata">

**Author:** ![SplitSecComp](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/splitseccomp/32/39802_2.png) [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)\
**Post date:** [5 August 2022 06:00 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/30 "2022-08-05T06:00:47Z")

</div>

That's exactly the part that i cant figure out 🙂

I use a "Edit sqlite node" to run the queries. How do i change the msg.payloads. The change node does not want to work.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/5/65abd42c321390a715e68a2ab699d266f52e9107.png)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [5 August 2022 06:12 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/31 "2022-08-05T06:12:07Z")

</div>

sql query 1 \> move msg.payload to msg.query1 \> sql query 2 \> create pdf use msg.query1 for first table, use msg.payload for second table.

If your change node will not move the payload then you should report an issue.  
If msg.query1 is missing after second query then try saving to context instead, and report that the sql node is overwriting the original msg to the node author

---

<div class="post-metadata">

**Author:** ![SplitSecComp](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/splitseccomp/32/39802_2.png) [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)\
**Post date:** [9 August 2022 06:09 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/32 "2022-08-09T06:09:36Z")

</div>

I think i have figured out why i cant get the second table working 🙂

How do i modify this portion of the code to accommodate msg.query1 with msg.payload and any additional tables that i want to create?

...  
};  
Object.values(msg.payload).forEach(obj =\>{  
payload.content[1].table.body.push(Object.values(obj));  
})

msg.payload = payload;

return msg;  
...

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [9 August 2022 07:53 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/33 "2022-08-09T07:53:21Z")

</div>

IF you are pushing the the table rows to the content table body, it would be something like this

```auto
//push query1 to content[1]
Object.values(msg.query1).forEach(obj =>{
payload.content[1].table.body.push(Object.values(obj));
})
//push payload to content[ index of second table
// i have guessed as to it's location [6]
Object.values(msg.payload).forEach(obj => {
payload.content[6].table.body.push(Object.values(obj));
})

msg.payload = payload;

return msg;

```

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [9 August 2022 08:29 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/34 "2022-08-09T08:29:35Z")

</div>

I tried this, and may be something was wrong in my adaptation to my flow, and it didn't work,  
i will figure it out,  
but here the question is, if a `pdfmake` fails for some reason, is it normal that is _stops the flow_ the error is stopping my windows service, is this expected?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 August 2022 09:41 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/35 "2022-08-09T09:41:10Z")

</div>

> [@smanjunath211](#):
>
> is it normal that is _stops the flow_ the error is stopping my windows service, is this expected

No. The node should handle it's errors and not kill node-red.

You should raise an issue on the repository and providers much detail as possible (there will be an error stack log in the console when the process crashes. I realise you are running as a service so you may not have visibility of the console or logs saved to file. So instead of running the service, you should run node red in a terminal so that you can see the error stack and provide this on the repository issue that you raise)

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [9 August 2022 10:17 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/36 "2022-08-09T10:17:28Z")

</div>

Example of two separate queries joined with a Join node and modified code in Function node  
(Topic could not be used to join the messages as that was used for the sql query .. used **key** instead )

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/2/821bdf2797433df409d2f51ee625b2337ca301de.png)

```auto
[{"id":"ec950ec457b45f9e","type":"function","z":"54efb553244c241f","name":"data to pdf","func":"let tableData = {\n\n pageSize: 'A4',\n pageOrientation: 'landscape',\n content: [\n\n { text: 'This is my Sqlite Table from Query 1:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },\n\n {\n style: 'tableExample',\n table: {\n headerRows: 1,\n body: [\n Object.keys(msg.payload.query1[0]).map(key => { return { text: key, style: 'tableHeader' } }),\n ...msg.payload.query1.map(obj => Object.values(obj))\n ]\n },\n layout: 'lightHorizontalLines'\n },\n\n { text: 'This is my Sqlite Table from Query 2:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },\n \n {\n style: 'tableExample',\n table: {\n headerRows: 1,\n body: [\n Object.keys(msg.payload.query2[0]).map(key => { return { text: key, style: 'tableHeader' } }),\n ...msg.payload.query2.map(obj => Object.values(obj))\n ]\n },\n layout: 'lightHorizontalLines'\n }\n ],\n styles: {\n header: {\n fontSize: 18,\n bold: true,\n margin: [0, 0, 0, 10]\n },\n subheader: {\n fontSize: 16,\n bold: true,\n margin: [0, 10, 0, 5]\n },\n tableExample: {\n margin: [0, 5, 0, 15]\n },\n tableHeader: {\n bold: true,\n fontSize: 13,\n color: 'black'\n }\n },\n defaultStyle: {\n // alignment: 'justify'\n }\n\n};\n\nmsg.payload = tableData;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":830,"y":720,"wires":[["e2dc81e103eb3abd","2c4abf85948ac92c"]]},{"id":"a29425b024fb291c","type":"join","z":"54efb553244c241f","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"key","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":670,"y":720,"wires":[["9dfdbc67b342674b","ec950ec457b45f9e"]]},{"id":"e2dc81e103eb3abd","type":"debug","z":"54efb553244c241f","name":"debug 10","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":960,"y":660,"wires":[]},{"id":"2c4abf85948ac92c","type":"pdfmake","z":"54efb553244c241f","name":"","outputType":"Buffer","inputProperty":"payload","options":"{}","outputProperty":"payload","x":1020,"y":720,"wires":[["3f2acb163d057cda"]]},{"id":"61b2b64dbecdd02f","type":"sqlite","z":"54efb553244c241f","mydb":"3901692ba9c81a0f","sqlquery":"msg.topic","sql":"","name":"Sqlite","x":530,"y":720,"wires":[["a29425b024fb291c"]]},{"id":"9dfdbc67b342674b","type":"debug","z":"54efb553244c241f","name":"debug 14","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":720,"y":640,"wires":[]},{"id":"3f2acb163d057cda","type":"file","z":"54efb553244c241f","name":"","filename":"c:/share/pdfTest.pdf","filenameType":"str","appendNewline":false,"createDir":false,"overwriteFile":"true","encoding":"none","x":1220,"y":720,"wires":[[]]},{"id":"184a494dca2081dc","type":"function","z":"54efb553244c241f","name":"sql query1","func":"\nlet columns = [\"TS01\", \"TS02\", \"TS03\", \"TS04\", \"TS05\", \"TS06\", \"TS07\", \"TS08\", \"TS09\", \"TS10\", \"TS11\", \"TS12\"]\ncolumns = columns.map(el => `round(${el}, 2) as ${el}`).join(\",\")\n\nmsg.topic = `SELECT dt, ${columns} FROM Temp LIMIT 10`\nmsg.key = \"query1\"\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"moment","module":"moment"}],"x":350,"y":680,"wires":[["61b2b64dbecdd02f"]]},{"id":"ac089368684fb955","type":"function","z":"54efb553244c241f","name":"sql query2","func":"\nlet columns = [\"FM01\", \"FM02\", \"FM03\", \"FM04\", \"FM05\", \"FM06\", \"FM07\"]\ncolumns = columns.map(el => `round(${el}, 2) as ${el}`).join(\",\")\n\nmsg.topic = `SELECT dt, ${columns} FROM Flow LIMIT 10`\nmsg.key = \"query2\"\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[{"var":"moment","module":"moment"}],"x":350,"y":780,"wires":[["61b2b64dbecdd02f"]]},{"id":"0f33788a7b027c5a","type":"inject","z":"54efb553244c241f","name":"","props":[{"p":"time","v":"","vt":"date"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":150,"y":680,"wires":[["184a494dca2081dc"]]},{"id":"18da12bf2f07d832","type":"inject","z":"54efb553244c241f","name":"","props":[{"p":"time","v":"","vt":"date"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":150,"y":780,"wires":[["ac089368684fb955"]]},{"id":"3901692ba9c81a0f","type":"sqlitedb","db":"C:\\Share\\easyio.db","mode":"RWC"}]

```

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [10 August 2022 00:59 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/37 "2022-08-10T00:59:15Z")

</div>

Thanks for this. I was struggling to get TWO tables into a PDF.  
I am also having one more issue, unable to pass on a dynamic value into the page title. I want to get a report date to the header of the PDF page, there are several examples in the forum to get a static value (hard coded into function node), but i want a message passed on into the pdfmake say with an inject or a change node . for example msg.header = (Report Date &\<selected\_date\>).

the answer is there in the below image, but i cannot understand it.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/4/c4772ea6bfdba827bec739f639c0affb1c0a67eb.png)

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [10 August 2022 01:04 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/38 "2022-08-10T01:04:33Z")

</div>

This is working,

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/f/bffff5f005f557598c7c314ad0a9218376ff016c.png)

---

<div class="post-metadata">

**Author:** ![SplitSecComp](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/splitseccomp/32/39802_2.png) [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)\
**Post date:** [19 August 2022 10:45 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/39 "2022-08-19T10:45:21Z")

</div>

Many thanks this works

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [2 September 2022 10:46 UTC](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782/40 "2022-09-02T10:46:06Z")

</div>

Closed on 2 September 2022 10:46 UTC

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

[Previous page](https://discourse.nodered.org/t/sqlite-data-into-pdf-report/65782.md?page=1)
