1

The backstory here is that I'm working on a Discord bot. One of the requested functions is having it lob insults. I thought it would be fun to give users the ability to add insults via a SQLite 3 database (specifically, better-sqlite3), and in doing so, I also want to keep track of the most recent use of each insult (this is for future use in an "insultinfo" type command). I added a DATETIME column called "lastUsed". My insultadd command is able to write to this column when new insults are added. The issue is when I call the insult command, it's supposed to say the insult and then update the lastUsed field with the current date. Pasting the code below.

The problem is that I'm getting an error that says "SQLiteError: no such column:" and then it prints the date value even though I'm trying to update the lastUsed column and I can't figure out how to resolve this. The issue has to be in the db.prepare statement, I'm just not seeing what I need to do to fix it.

execute(msg, args) {
    const SQLite = require("better-sqlite3");
    const db = new SQLite('./userinputs.sqlite');
    // Check if the table "userinputs" exists and has content from this guild.
    const table = db.prepare(`SELECT count(*) FROM userinputs WHERE (guild = ${msg.guild.id} OR guild = 'Global') AND type = 'insult';`).get();
    if (!table['count(*)']) {
        return msg.channel.send("I don't have any insults yet");
    }
    var date = new Date();
    const rawInsult = db.prepare(`SELECT * FROM userinputs WHERE type = 'insult' AND (guild = ${msg.guild.id} OR guild = 'Global') ORDER BY RANDOM() LIMIT 1;`).get();
    const insult = rawInsult['content'];
    const insultID = rawInsult['row'];
    if (args[0]) {
        var target = args[0];
    } else {
        var target = msg.author.username;
    }
    if (insult.includes('{}')) {
        var finalInsult = insult.replace('{}', target);
    } else {
        var finalInsult = target + ' ' + insult;
    }
    msg.channel.send(finalInsult);
    db.prepare(`UPDATE userinputs SET lastUsed = "${date}" WHERE row = ${insultID};`).run();
},
boydster
  • 31
  • 5
  • I'm not sure if more info is needed, but in case it helps explain why some of the code is there, I'm going to be adding fortune cookie and magic 8 ball commands too, both of which will have a baseline of responses that are global (work on all servers the bot is on), and allow for users to add custom responses that will only show up in the guild where their response was added. Ideally, all of these I want to track the most recent usage so an admin can run an info command against the last use to see who added it. – boydster Aug 02 '20 at 21:02
  • I just tried changing the db.prepare statement to `db.prepare('UPDATE userinputs SET lastUsed = ? WHERE row = ?').run(date,insultID);` and I got the following error: `TypeError: SQLite3 can only bind numbers, strings, bigints, buffers, and null` – boydster Aug 02 '20 at 21:44
  • `date` is a `Date` and hence cannot be bound... as it says, you can bind only number, strings, bigints and buffers. Depending on how you store the date in the database, you may want to use `date.valueOf()`, `Math.floor(date.valueOf() / 1000)` or `date.toISOString()` instead. – CherryDT Aug 03 '20 at 00:14

1 Answers1

0

I think I got it. I ended up using date.toString() and it appears to have done the job. The error message I got after updating the db.prepare statement indicated it wasn't viewing that date variable as something it could work with, hence the .toString() workaround. I'll have to test this to see if that impacts my ability to sort on that column, since this is still all a pretty new to me, but at least the command itself is working and the database table is updating.

I'm open to more feedback though if anyone sees the probable folly in my ways here.

UPDATE: Upon further testing, setting date = Math.floor(new Date() / 1000) ended up working better in my case.

boydster
  • 31
  • 5
  • If it's always the current date anyway, you can just use [SQLite's `now`](https://www.sqlite.org/lang_datefunc.html): `db.prepare("UPDATE userinputs SET lastUsed = DATETIME('now') WHERE row = ?").run(insultID)` – CherryDT Aug 03 '20 at 00:16
  • I didn't even know there was a `now` until, well, `now`. Thanks! – boydster Aug 03 '20 at 01:51