Electron, SQL.JS, jQuery and populating Datatables

Question

I have a table as follows in an app I am creating using Electron. I am using jQuery, Datatables and SQL.js:-

<table id="dataTable" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Date</th>
            <th>Code</th>
            <th>Category</th>
            <th>Hours</th>
            <th>Cost</th>
            <th>Billed</th>
            <th>Description</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th>Date</th>
            <th>Code</th>
            <th>Category</th>
            <th>Hours</th>
            <th>Cost</th>
            <th>Billed</th>
            <th>Description</th>
        </tr>
    </tfoot>
</table>

The following code to populate the table is not working correctly:-

$(document).ready(function() {

    var DB = null;  

    var t = $('#dataTable').DataTable();

        $(".bottomMenuContainer").on("click", ".loadButton", function(e) {
            var fs = require('fs');
            var sql = require('sql.js');
            var bfr = fs.readFileSync(__dirname + '/../data/EliteData.db');
            DB = new sql.Database(bfr);
            var stmt = DB.prepare("SELECT * FROM ProductEntries ORDER BY Category");
            while(stmt.step()){
                var row = stmt.getAsObject(); 
                t.rows.add([row.Date, row.Code, row.Category, row.Hours, row.Cost, row.Billed, row.Description]).draw(false);
            }
        });



});

I get a requested unknown parameter '1' for row 0, column 1 error message and the data ends up spread over the table when it finally displays, and there are 21 results instead of the 3 in the actual database.

Any idea what's happening?

If you're getting 21 rather than 3 then it looks as though your step() isn't iterating over the results as you think it should. It looks as though it's iterating over each cell rather than each row... I've not used SQL.JS myself but perhaps do a console log(stmt) within your while loop and see what you're getting? — annoyingmouse
– annoyingmouse, Commented Mar 11, 2017 at 13:15

Resurgent · Accepted Answer · 2017-03-11 13:44:06Z

2

The answer is to create an array and push the data into the array and then add it to the table. Then draw the table.

            while(stmt.step()){
                var row = stmt.getAsObject(); 
                var result = [];
                    result.push(row.Date);
                    result.push(row.Code);
                    result.push(row.Category);
                    result.push(row.Hours);
                    result.push(row.Cost);
                    result.push(row.Billed);
                    result.push(row.Description);
                t.row.add(result);
            }
            t.draw();

answered Mar 11, 2017 at 13:44

Resurgent

5552 gold badges9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

annoyingmouse · Accepted Answer · 2017-03-11 13:53:05Z

1

Nice to see that you've sorted it yourself @Resurgent :-). Though I had a little play myself and scrapped the adding of single rows like this:

let res = db.exec("SELECT * FROM ProductEntries");
table.rows.add(res[0].values).draw();

This is a working JSFiddle, thanks for the introduction to SQL.js though, looks fun!

answered Mar 11, 2017 at 13:53

annoyingmouse

5,6991 gold badge31 silver badges48 bronze badges

Collectives™ on Stack Overflow

Electron, SQL.JS, jQuery and populating Datatables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related