Cannot INSERT into database (node)

[mikekok]

I'm using sql.js in an Electron app, and although I can successfully retrieve data using db.exec and SELECT, I cannot INSERT anything into my database using db.run.

[lovasoa]

What exactly are you expecting ? INSERT works, it’s just that the DB is in memory, you have to handle file saving yourself if you want to persist your data.

[lovasoa]

Plus, if you are doing a desktop app, then you’d better not use this library, but a native one.

[lovasoa]

For instance https://www.npmjs.com/package/sqlite

[mikekok]

Thanks for letting me know.

[Zayelion]

I'm having the issue this @mikekok is describing.

I created a blank database (in memory), created tables in it (in memory), then tried to run an series of INSERT commands. When the code get to the that part it crashes, as in throws the following exception.

Error: "Database closed"
sql.js:490
    at a.run (c:\Users\Jamezs\Documents\GitHub\YGO_DB\node_modules\sql.js\js\sql.js:490:256)
    at generateCDB (c:\Users\Jamezs\Documents\GitHub\YGO_DB\generator.js:124:13)
    at (anonymous) (c:\Users\Jamezs\Documents\GitHub\YGO_DB\app.js:137:22)
    at exithandler (child_process.js:267:6)
    at emitTwo (events.js:126:12)
    at emit (events.js:214:6)
    at maybeClose (internal/child_process.js:925:15)
    at stream.socket.on (internal/child_process.js:346:10)
    at emitOne (events.js:116:12)
    at emit (events.js:211:6)
    at _handle.close (net.js:554:11)

function generateCDB(db, options, response, callback) {
    var injections = [], cdb = new SQL.Database();

    var dataStrings = db.map(function (card) {
        var id = card.id,
            ot = card.ot || 0,
            alias = card.alias || 0,
            setcode = card.setcode ||0 ,
            type = card.type || 0,
            atk = card.atk || 0,
            def = card.def || 0,
            level = card.level || 0,
            race = card.race || 0,
            attribute = card.attribute || 0,
            category = card.category || 0,
            datasSQL = [id, ot, alias, setcode, type, atk, def, level, race, attribute, category].join(','),
            datas = 'REPLACE OR INSERT INTO datas VALUES (' + datasSQL +');'

        return datas;
    });

    // var textStrings = db.map( maketextsSQL);

    response.write('Creating in memory database...<br />');
    cdb.run('CREATE TABLE datas (id, ot, alias, setcode, type, atk, def, level, race, attribute, category);');
    //cdb.run('CREATE TABLE texts (id, name, description, str1, str2, str3, str4, str5, str6, str7, str8, str9, str10, str11, str12, str13, str14);');
    response.write('Writing data...<br />');
    dataStrings.forEach(cdb.run);  //// <-- Error appears here.
    response.write('Writing text...<br />');
    //textStrings.forEach(cdb.run);
    response.write('Saving file...<br />');

    var buffer = new Buffer(cdb.export());
    fs.writeFileSync("cards.cdb", buffer);
    response.write('File Saved...<br />');
    callback();

}

[dinedal]

I use sql.js in memory without issue for my app, inserting, deleting, selecting, etc.

I don't know enough about your code to say what's wrong. I see cdb is your sql.js db, but what is db ?

[Zayelion]

I figured it out, my .join(',') needed to be .join(' , '). But the error message was very unhelpful for tracking down the cause of the error. @dinedal thank you for responding.

I suggest that if the SQL is malformed a different error be thrown.

[dinedal]

That's a good point, it's likely in the handleError function: https://github.com/kripken/sql.js/blob/master/coffee/api.coffee#L423-L428