[ReneG]

I've made a dynamic family system with SQLite that is supposed to work like the Godfather one's did.

When I save:

1. I first delete all the rows.
2. Next, I insert a new row as a 'blank' family.
3. I then check if the family is taken, if it is, then it is updated.
4. If it's not then the slot just stays as a blank family.

But, for some reason the amount of rows doubles each time this saving function is called.

pawn Код:

public SaveFamilies()
{
    new
        id=0,
        query[256]
    ;
    db_free_result(db_query(Database, "DELETE * FROM `Families`"));
    while(id < MAX_FAMILIES)
    {
        format(query, sizeof(query), "INSERT INTO `Families` (`Slot`, `Taken`, `Name`, `Leader`, `MOTD`, `Count`, `Rank1`, `Rank2`, `Rank3`, `Rank4`, `Rank5`, `Rank6`) \
                                                                VALUES ('%d', '0', 'None', 'None', 'None', '0', 'Rank1', 'Rank2', 'Rank3', 'Rank4', 'Rank5', 'Rank5')", id);
        db_free_result(db_query(Database, query));
        
        if(FamilyInfo[id][fTaken] == 1)
        {
            format(query, sizeof(query), "UPDATE `Families` SET Name = '%s', Leader = '%s', MOTD = '%s', Count = '%d', Taken = '%d' WHERE Slot = '%d'",
            FamilyInfo[id][fName],
            FamilyInfo[id][fLeader],
            FamilyInfo[id][fMOTD],
            FamilyInfo[id][fCount],
            FamilyInfo[id][fTaken],
            id);
            db_free_result(db_query(Database, query));
            format(query, sizeof(query), "UPDATE `Families` SET Rank1 = '%s', Rank2 = '%s', Rank3 = '%s', Rank4 = '%s', Rank5 = '%s', Rank6 = '%s' WHERE Slot = '%d'",
            FamilyRank[id][0],
            FamilyRank[id][1],
            FamilyRank[id][2],
            FamilyRank[id][3],
            FamilyRank[id][4],
            FamilyRank[id][5],
            id);
            db_free_result(db_query(Database, query));
        }
        id++;
    }
    return 1;
}

Any help would be appreciated.

[Lorenc_]

Код:

DELETE * FROM `Families`

This query is failing to submit, have you tried TRUNCATE?

Код:

TRUNCATE TABLE `Families`

Do not use db_free_result unless you're fetching something via the SELECT method, it's inefficient doing that.

[ReneG]

Quote:

Originally Posted by Lorenc_ Код: DELETE * FROM `Families` This query is failing to submit, have you tried TRUNCATE? Код: TRUNCATE TABLE `Families` Do not use db_free_result unless you're fetching something via the SELECT method, it's inefficient doing that.

I tried the truncate optimization, to no avail.

As for the excessive freeing of results, I've read so many views on SQLite, and am having a hard time deciphering right from wrong. Thanks for heads up.

Here is the updated code.

pawn Код:

public SaveFamilies()
{
    new
        id,
        query[256]
    ;
    db_query(Database, "TRUNCATE TABLE `Families`");
    while(id < MAX_FAMILIES)
    {
        format(query, sizeof(query), "INSERT INTO `Families` (`Slot`, `Taken`, `Name`, `Leader`, `MOTD`, `Count`, `Rank1`, `Rank2`, `Rank3`, `Rank4`, `Rank5`, `Rank6`) \
                                                                VALUES ('%d', '0', 'None', 'None', 'None', '0', 'Rank1', 'Rank2', 'Rank3', 'Rank4', 'Rank5', 'Rank5')", id);
        db_query(Database, query);
        if(FamilyInfo[id][fTaken] == 1)
        {
            format(query, sizeof(query), "UPDATE `Families` SET Name = '%s', Leader = '%s', MOTD = '%s', Count = '%d', Taken = '%d' WHERE Slot = '%d'",
            FamilyInfo[id][fName],
            FamilyInfo[id][fLeader],
            FamilyInfo[id][fMOTD],
            FamilyInfo[id][fCount],
            FamilyInfo[id][fTaken],
            id);
            db_query(Database, query);
            format(query, sizeof(query), "UPDATE `Families` SET Rank1 = '%s', Rank2 = '%s', Rank3 = '%s', Rank4 = '%s', Rank5 = '%s', Rank6 = '%s' WHERE Slot = '%d'",
            FamilyRank[id][0],
            FamilyRank[id][1],
            FamilyRank[id][2],
            FamilyRank[id][3],
            FamilyRank[id][4],
            FamilyRank[id][5],
            id);
            db_query(Database, query);
        }
        id++;
    }
    return 1;
}

Here is a copy of the database after SaveFamilies is called the first time to prove there aren't any query problems.


After every restart, the rows increase by 10.

[Lorenc_]

Well, you could try dropping the table and recreating it, but it'd be frankly quite pointless.

I don't seem to understand why TRUNCATE isn't working, try removing your database and redoing the process of adding all those families; it sometimes does bug mysteriously.

Have you tried manually using those queries on your SQLite manager?

[ReneG]

Every time I make a change in the code, I always scrap the current database to avoid bugs.

Executing the queries on the SQLite manager worked fine.
I guess I'll just have to go into major-debugging or just re-write the code completely.

Leaving the discussion open for anyone else who wants to help.

[PrawkC]

You can't "TRUNCATE" with SQL Lite, try "DELETE FROM Families"

typo'd

[ReneG]

Quote:

Originally Posted by PrawkC You can't "TRUNCATE" with SQL Lite, try "DELETE FROM Families" typo'd

That miraculously worked, thank you very much.