Hi.
I want to display SQLite data in a PDF report in a table format. I can get the PDF file to work as well as the table in in the PDF file with PDFMake. I can also get the SQL data out of the data basis with queries.
How can i get the SQLite query data into the PDF Table?
Or alternatively PDFMake uses JSON how do i get the sql query data into JSON?
Kindly assist.
The sql query should return a JSON object, to make that a JSON string pass it through a Json node.
Just to calrify ... are you using the PDFMake node node-red-contrib-pdfmake ?
if yes .. i dont think there is a need to make the msg.payload into a JSON string
but you have to structure your sqlite db's output msg to be in the table structure the pdfmake node expects.
Here is a demo flow of how to make a table :
[{"id":"f8a7f83d80d07ff5","type":"inject","z":"54efb553244c241f","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":300,"y":3960,"wires":[["924e9713d93837d6"]]},{"id":"924e9713d93837d6","type":"function","z":"54efb553244c241f","name":"fake data","func":"\nmsg.payload = {\n content: [\n\n { text: 'This is my Sqlite Table:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },\n {\n style: 'tableExample',\n table: {\n headerRows: 1,\n body: [\n [{ text: 'Header 1', style: 'tableHeader' }, { text: 'Header 2', style: 'tableHeader' }, { text: 'Header 3', style: 'tableHeader' }],\n ['Sample value 1', 'Sample value 2', 'Sample value 3'],\n ['Sample value 1', 'Sample value 2', 'Sample value 3'],\n ['Sample value 1', 'Sample value 2', 'Sample value 3'],\n ['Sample value 1', 'Sample value 2', 'Sample value 3'],\n ['Sample value 1', 'Sample value 2', 'Sample value 3'],\n ]\n },\n layout: 'lightHorizontalLines'\n },\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\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":480,"y":3960,"wires":[["2c4abf85948ac92c"]]},{"id":"2c4abf85948ac92c","type":"pdfmake","z":"54efb553244c241f","name":"","outputType":"Buffer","inputProperty":"payload","options":"{}","outputProperty":"payload","x":640,"y":3960,"wires":[["3f2acb163d057cda"]]},{"id":"3f2acb163d057cda","type":"file","z":"54efb553244c241f","name":"","filename":"c:/share/pdfTest.pdf","filenameType":"str","appendNewline":false,"createDir":false,"overwriteFile":"true","encoding":"none","x":840,"y":3960,"wires":[[]]}]
if you need help restructuring the db data, then send us a sample msg of a few rows to play with
Many thanks.
This part i got working. Your example also works.
My problem is how to i get the SQL query data instead of your fake data. I get the data from the query but how do i get it into json table code
This is the data that i get from the sql query. How do i get that data into the json code/node and into the pdf table?
If you hover over the msg.payload array (in the Debug window) and use the button Copy value
and share a sample of 10 rows (SQL : LIMIT 10) to see ways it can be re-structured.
maybe with Jsonata or with Javascript
How do i replace the sample values in your example with the real values from the SQL query?
[{"Probe_no":"Probe0010","Time":"7/12/2022, 12:08:10 AM","Site":"Mo-Chicks_1","Area":"Delmas","Shift_no":"Night","Bat_percentage":"86","latitude":-26.06963,"longitude":28.607964999999997,"accuracy":11.699999809265137},{"Probe_no":"Probe0010","Time":"7/12/2022, 12:12:25 AM","Site":"Mo-Chicks_1","Area":"Delmas","Shift_no":"Night","Bat_percentage":"85","latitude":-26.069961666666668,"longitude":28.608556666666665,"accuracy":6.5},{"Probe_no":"Probe0010","Time":"7/12/2022, 12:14:51 AM","Site":"Mo-Chicks_1","Area":"Delmas","Shift_no":"Night","Bat_percentage":"84","latitude":-26.06991833333333,"longitude":28.60752166666667,"accuracy":6.300000190734863},{"Probe_no":"Probe0010","Time":"7/12/2022, 12:15:32 AM","Site":"Mo-Chicks_1","Area":"Delmas","Shift_no":"Night","Bat_percentage":"84","latitude":-26.069635,"longitude":28.60752,"accuracy":2.799999952316284},{"Probe_no":"Probe0010","Time":"7/12/2022, 12:17:39 AM","Site":"Mo-Chicks_1","Area":"Delmas","Shift_no":"Night","Bat_percentage":"84","latitude":-26.069729999999996,"longitude":28.608585,"accuracy":4.800000190734863},{"Probe_no":"Probe0010","Time":"7/12/2022, 1:05:48 AM","Site":"Mo-Chicks_1","Area":"Delmas","Shift_no":"Night","Bat_percentage":"84","latitude":-26.069713333333333,"longitude":28.608646666666665,"accuracy":6.900000095367432},{"Probe_no":"Probe0010","Time":"7/12/2022, 1:09:03 AM","Site":"Mo-
Create the table body array with the header row, then push the sql rows to the body array.
e.g.
[{"id":"f8a7f83d80d07ff5","type":"inject","z":"30af2d3e.d94ea2","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"one\":\"Sample value 1\",\"two\":\" Sample value 2\",\"three\":\"Sample value 3\"},{\"one\":\"Sample value 1\",\"two\":\" Sample value 2\",\"three\":\"Sample value 3\"},{\"one\":\"Sample value 1\",\"two\":\" Sample value 2\",\"three\":\"Sample value 3\"}]","payloadType":"json","x":110,"y":1700,"wires":[["924e9713d93837d6"]]},{"id":"924e9713d93837d6","type":"function","z":"30af2d3e.d94ea2","name":"fake data","func":"let payload = {\n content: [\n\n { text: 'This is my Sqlite Table:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },\n {\n style: 'tableExample',\n table: {\n headerRows: 1,\n body: [\n [{ text: 'Header 1', style: 'tableHeader' }, { text: 'Header 2', style: 'tableHeader' }, { text: 'Header 3', style: 'tableHeader' }],\n ]\n },\n layout: 'lightHorizontalLines'\n },\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};\nObject.values(msg.payload).forEach(obj =>{\n payload.content[1].table.body.push(Object.values(obj));\n})\n\nmsg.payload = payload;\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":390,"y":1680,"wires":[["729b9bbd.0f0b44"]]},{"id":"729b9bbd.0f0b44","type":"debug","z":"30af2d3e.d94ea2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":690,"y":1640,"wires":[]}]
Not sure how to do it but i will give it a try 
Nice solution .. maybe you can populate the table headers also dynamically with
...
table: {
headerRows: 1,
body: [
Object.keys(msg.payload[0]).map( key => { return { text: key, style: 'tableHeader' }})
]
},
...
You could then also concat the rows at same time.
...
table: {
headerRows: 1,
body: [
Object.keys(msg.payload[0]).map( key => { return { text: key, style: 'tableHeader' }})
].concat(
Object.values(msg.payload).map(obj => Object.values(obj))
)
},
...
Then no need to push the rows.
Many thanks.
I really appreciate the help. It works perfectly
I must now just get the page to landscape and figure out how to get some of the data on a graph.
Again many thanks.
where would i put the following 2 lines in your example code?
pageSize: 'A4',
pageOrientation: 'landscape'
i tried everything cant get it to work
Thanks i have that page. But where in the code do i put it?
No matter where i put it it does not work.
This is how i have it but for some reason its not working?
let payload = {
content: [
{
pageSize: 'A4',
pageOrientation: 'landscape',
text: 'Probe Data:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },
{
style: 'tableExample',
table: {
headerRows: 1,
body: [
Object.keys(msg.payload[0]).map( key => { return { text: key, style: 'tableHeader' }})
]
},
layout: 'lightHorizontalLines'
},
],
styles: {
header: {
fontSize: 18,
bold: true,
margin: [0, 0, 0, 10]
},
subheader: {
fontSize: 16,
bold: true,
margin: [0, 10, 0, 5]
},
tableExample: {
margin: [0, 5, 0, 15]
},
tableHeader: {
bold: true,
fontSize: 13,
color: 'black'
}
},
defaultStyle: {
// alignment: 'justify'
}
};
Object.values(msg.payload).forEach(obj =>{
payload.content[1].table.body.push(Object.values(obj));
})
msg.payload = payload;
return msg;
Above content.
Ps, on every post you have made, the code is all messed up because you don't use the code fence...
In order to make code readable and usable it is necessary to surround your code with three backticks (also known as a left quote or backquote ```)
```
code goes here
```
You can edit and correct your post by clicking the pencil 
icon.
See this post for more details - How to share code or flow json
Try
let payload = {
pageSize: 'A4',
pageOrientation: 'landscape',
content: [
{ text: 'This is my Sqlite Table:', fontSize: 14, bold: true, margin: [0, 20, 0, 8] },
{
style: 'tableExample',
table: {
headerRows: 1,
body: [
Object.keys(msg.payload[0]).map( key => { return { text: key, style: 'tableHeader' }})
].concat(
Object.values(msg.payload).map(obj => Object.values(obj))
)
},
layout: 'lightHorizontalLines'
},
],
styles: {
...
Apologies.....i restarted node-red now its working.
Again many thanks.
Thanks for starting this thread , i had same issue and was stuck.
Now i have a perfect pdf report (in landscape!) created and sent by email automatically by getting data from MySQL.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy