I have an issue with accessing a table in my database after using the truncate command on it. Having to delete the whole database and recreate it not every time but often. The table is used to hold the index of a card and the card's value.

This is the truncate code:

try {
            con = DriverManager.getConnection(host, uName, uPass);
            Clean = con.createStatement();
            Clean.executeUpdate("TRUNCATE cards");

            Clean.close();

        } catch (SQLException err) {
            System.out.println(err.getMessage());
        }

This is the update code:

    Set<Integer> generated = new LinkedHashSet<Integer>();
        while (generated.size() < Deck.length) {
            Integer index = rand.nextInt(Deck.length) + 1;
            // As we're adding to a set, this will automatically do a containment check
            generated.add(index);
        }

        //Write card index to database
        Iterator itr = generated.iterator();

        try {
            con = DriverManager.getConnection(host, uName, uPass);
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            String SQL = "SELECT * FROM cards";

            while (itr.hasNext()) {
                int thisCard = ((int)itr.next()-1)%52;//Makes sure card is a value between 1 and 52
                int thisVal = (thisCard)%13;//Writes a value of 1 to 13 in the datbase associated with thisCard
                rs = stmt.executeQuery(SQL);
                rs.moveToInsertRow();
                rs.updateInt("CardIndex", thisCard +1);//(int) itr.next());
                rs.updateInt("CardValue", thisVal +1);
                rs.insertRow();
            }

            stmt.close();
            rs.close();

        } catch (SQLException err) {
            System.out.println(err.getMessage());
        }
        generated.clear();

Any Ideas? I tried the delete command but the auto increment started at like 400 and I need it to start at 1 evrytime. That is why I truncate.

I have an issue with accessing a table in my database after using the truncate command on it.

What is the issue you are talking about? Please ensure that whenever you say anything about "not working," you need to explain the issue from 1)What is your input?, 2)What is your expected output?, 3)What do you actually get?, and 4)What is the error message you are getting if any?

You do not need truncate to reset your auto increment value. Simply use ALTER TABLE cards AUTO_INCREMENT = 1; (in MySQL) and it should be reset to 1. Or if you use Postgresql, it should be setval('card_id_seq', 1); if I remember correctly.

Edited 2 Years Ago by Taywin

These are The errors that are being printed to the out box:

Got error -1 from storage engine
Tablespace is missing for table '(null)'

I can sometimes deal a hand, stop the app. Then restart the app and get these errors.
NOTE: I only truncate the table when the game is loaded and after several hands have been played and a reshuffle is needed. Haven't got to the point where a resuffle would be needed yet but working towards that.

I think I got the solution to my problem. I replaced this line of code:

Clean.executeUpdate("TRUNCATE cards");

With these 2 lines:

Clean.executeUpdate("DELETE FROM cards WHERE ID > 0");//("TRUNCATE cards");
Clean.executeUpdate("ALTER TABLE cards AUTO_INCREMENT = 1");

and so far it seems to be working properly.

This question has already been answered. Start a new discussion instead.