Java memory hsqldb

Question

I am making a discord bot using Java, and I use hsqldb for a memory database, but it doesn't work fine for me, i insert data into the table and the result set next time gives 0 rows. the most confusing point is that it worked before in another feature in the bot

Code

String statement = "SELECT * FROM waiting_rolls WHERE user=" + sender.getIdLong() + " AND channel=" + channel.getIdLong() + "";
        System.out.println(statement);
        PreparedStatement p1 = conn.prepareStatement("SELECT * FROM waiting_rolls WHERE user='" + sender.getIdLong() + "' AND channel='" + channel.getIdLong() + "'");
        p1.execute();
        ResultSet r1 = p1.executeQuery();
        if (!r1.next()) {
            System.out.println("INSERT INTO waiting_rolls (user, channel, isWaiting) VALUES (" + sender.getIdLong() + ", " + channel.getIdLong() + ", 'true')");
            PreparedStatement p2 = conn.prepareStatement("INSERT INTO waiting_rolls (user, channel, isWaiting) VALUES (" + sender.getIdLong() + ", " + channel.getIdLong() + ", 'true')");
            p2.execute();
            EmbedBuilder embed = new EmbedBuilder();
            embed.setColor(Color.cyan);
            embed.setTitle("Create roll");
            embed.setDescription("Please insert your first value in the roll.");
            e.getChannel().sendMessage(embed.build()).queue();
        }

I know that the first reply will be use p1.setLong but I did this and one in the SQL support server do this for debugging, and they both don't work

Query that created table

CREATE TABLE waiting_rolls (channel Bigint, user Bigint, isWaiting varchar(50), lastOutput Bigint)

Answer 1

user is a reserved SQL keyword. You should not use its as a column name.

I used your schema to reproduce the issue - renaming the column to 'userid' made everything work as expected.

user is confirmed to be a reserved keyword by this page: https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words