SQLite problem [+REP] - Printable Version

SQLite problem [+REP] - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: SQLite problem [+REP] (/showthread.php?tid=592817)

SQLite problem [+REP] - Hessu - 30.10.2015

What am i doing wrong with this code? Apparently there is something wrong with the query (or stings).

pawn Код:

else if(db_num_rows(Result)) // The house ID is in the database. Update the values
    {
        print("House found in database! Updaing...");

        new Owned33 = 0;
        if(AHouseData[HouseID][Owned] == true)
        {
            Owned33 = 1;
        }
        else if(AHouseData[HouseID][Owned] == true)
        {
            Owned33 = 0;
        }

        printf("House owned %d House Owner %s", Owned33, AHouseData[HouseID][Owner]);

    //  strcat(HouseStrCat, "UPDATE `Houses` SET `HouseAddress` = '%s', `HouseName` = '%s', `HouseX` = '%f', `HouseY` = '%f', `HouseZ` = '%f', `HouseInterior` = '%d', `HouseMaxSlots` = '%d', `HouseNewMaxSlots` = '%d', `HousePrice` = '%d', `Owned` = '%d', `HouseOpened` = '%d', ", sizeof(HouseStrCat)); // 11
    //  strcat(HouseStrCat, "`PlayerIsTycoon` = '%d', `Owner` = '%s', `Insurance` = '%d' WHERE `HouseID` = '%d'", sizeof(HouseStrCat)); // 15

        print("Failstep 9");

        format(Query, sizeof(Query), "UPDATE `Houses` SET `HouseAddress` = '%s', `HouseName` = '%s', `HouseX` = '%f', `HouseY` = '%f', `HouseZ` = '%f', `HouseInterior` = '%d', `HouseMaxSlots` = '%d', `HouseNewMaxSlots` = '%d', `HousePrice` = '%d', `Owned` = '%d', `HouseOpened` = '%d', `PlayerIsTycoon` = '%d', `Owner` = '%s', `Insurance` = '%d' WHERE `HouseID` = '%d'",
        AHouseData[HouseID][HouseAddress], AHouseData[HouseID][HouseName], AHouseData[HouseID][HouseX], AHouseData[HouseID][HouseY], AHouseData[HouseID][HouseZ], AHouseData[HouseID][HouseInterior], AHouseData[HouseID][HouseMaxSlots], AHouseData[HouseID][HouseNewMaxSlots], AHouseData[HouseID][HousePrice], Owned33,
        AHouseData[HouseID][HouseOpened], AHouseData[HouseID][PlayerIsTycoon], AHouseData[HouseID][Owner], AHouseData[HouseID][Insurance], HouseID); // 15

        print("Failstep 10");
        db_free_result(db_query(Database, Query));

        print("Failstep 11");

    //  strdel(HouseStrCat, 0, 800);

        print("Failstep 12");

    }

It outputs this in console:

Код:

[06:36:23] House found in database! Updaing...
[06:36:23] House owned 1 House Owner Kulottaja
[06:36:23] Failstep 9
[06:36:23] Failstep 10
[06:36:23] SQLitei Notice: (db_free_result_hook) Invalid result given.
[06:36:23] Failstep 11
[06:36:23] Failstep 12

Please, someone that knows these things, take a look at the code.

Re: SQLite problem [+REP] - PrO.GameR - 30.10.2015

Failstep 9, do you even send the query ?! I can only see the format
I never worked with SQLite, but I guess it's pretty similar to MySQL ? I'm not sure if I can help if they are not

Re: SQLite problem [+REP] - RoboN1X - 30.10.2015

Why are you checking something same?? (true or else true)

Quote:

Originally Posted by Hessu

Код:

	
		if(AHouseData[HouseID][Owned] == true)
		{
		    Owned33 = 1;
		}
		else if(AHouseData[HouseID][Owned] == true)
		{
		    Owned33 = 0;
		}

Also SQLite syntax would be preferably like this:

Код:

UPDATE Houses SET HouseAddress = '%s', HouseName = '%s', HouseX = %f, HouseY = %f, HouseZ = %f, HouseInterior = %d, HouseMaxSlots = %d, HouseNewMaxSlots = %d, HousePrice = %d, Owned = %d, HouseOpened = %d, PlayerIsTycoon = %d, Owner = '%s', Insurance = %d WHERE HouseID = %d

If nothing works, show your AHouseData enum.

Re: SQLite problem [+REP] - Vince - 30.10.2015

The first line in the code you've shown shows that you still have a result in memory. I think you need to free this result before you can send a new query. Not sure, though.

Re: SQLite problem [+REP] - Hessu - 30.10.2015

It sends it on the line :

db_free_result(db_query(Database, Query));

where db_query is inside db_free_result (works like this, i have it in several places in my code too).

The strcat(HouseStrCat is the same as i have in the format. I took the query from strcat and pasted into format since the line was not too long (simplifying code).

Im pretty sure the query itself is working, so i think the issue is with strings, idk. The Query vaiable is [1100] long btw.

Re: SQLite problem [+REP] - Hessu - 30.10.2015

Thanks RoboN1X. That was a pure and calssic mistake i made there. And i use that code, because i think its best to save 1 for true and 0 for false in database.

Let me test these things...

Re: SQLite problem [+REP] - Hessu - 30.10.2015

Nothing of that dint work, and Vince, i have annother code for saving player stats the same way built, and it works fine, so i dont think its that. Interesting not though.

The code looks like this now, and has exacly the same problems. Here i have more of it if you can spot something interesting from it. Sorry for the failsteps!

pawn Код:

printf("Started to save house %d", HouseID);
    //Open sqlite database

    print("Failstep 1");

    new DB:Database, DBResult:Result;

    print("Failstep 2");

    new HouseStrCat[800];

    print("Failstep 3");
    new Query[1100];
    new HouseIDQuery[128];

    print("Failstep 4");

    Database = db_open("DataHouses.db");

    print("Failstep 5");

    format(HouseIDQuery, sizeof(HouseIDQuery), "SELECT * FROM `Houses` WHERE `HouseID` = '%d'", HouseID);

    print("Failstep 6");

    Result = db_query(Database, HouseIDQuery);

    print("Searching for house in database...");

    
    if(!db_num_rows(Result)) //No house with this ID was found in the database
    {
        print("No house with that id was found. Adding in in the database...");

        print("Failstep 7");

        strcat(HouseStrCat, "INSERT INTO `Houses` (`HouseID`, `HouseAddress`, `HouseName`, `HouseX`, `HouseY`, `HouseZ`, `HouseInterior`, `HouseMaxSlots`, `HouseNewMaxSlots`, `HousePrice`, `Owned`, `HouseOpened`, `PlayerIsTycoon`, `Owner`, `Insurance`) ", sizeof(HouseStrCat)); // 15
        strcat(HouseStrCat, "VALUES('%d','%s','%s','%f','%f','%f','%d','%d','%d','%d','%d','%d','%d','%s','%d')", sizeof(HouseStrCat)); // 44

        print("Failstep 8");

        format(Query, sizeof(Query), HouseStrCat, HouseID, AHouseData[HouseID][HouseAddress], AHouseData[HouseID][HouseName], AHouseData[HouseID][HouseX], AHouseData[HouseID][HouseY], AHouseData[HouseID][HouseZ], AHouseData[HouseID][HouseInterior], AHouseData[HouseID][HouseMaxSlots], AHouseData[HouseID][HouseNewMaxSlots],
        AHouseData[HouseID][HousePrice], AHouseData[HouseID][Owned], AHouseData[HouseID][HouseOpened], AHouseData[HouseID][PlayerIsTycoon], AHouseData[HouseID][Owner], AHouseData[HouseID][Insurance]);

        print("Failstep 9");

        db_free_result(db_query(Database, Query));

        print("Failstep 10");

        strdel(HouseStrCat, 0, 800);

        print("Added!");
    }
    else if(db_num_rows(Result)) // The house ID is in the database. Update the values
    {
        print("House found in database! Updaing...");

        new Owned33 = 0;
        if(AHouseData[HouseID][Owned] == true)
        {
            Owned33 = 1;
        }
        else if(AHouseData[HouseID][Owned] == false)
        {
            Owned33 = 0;
        }

        printf("House owned %d House Owner %s", Owned33, AHouseData[HouseID][Owner]);

        print("Failstep 9");

        //UPDATE `Houses` SET `HouseAddress` = '%s', `HouseName` = '%s', `HouseX` = '%f', `HouseY` = '%f', `HouseZ` = '%f', `HouseInterior` = '%d', `HouseMaxSlots` = '%d', `HouseNewMaxSlots` = '%d', `HousePrice` = '%d', `Owned` = '%d', `HouseOpened` = '%d', `PlayerIsTycoon` = '%d', `Owner` = '%s', `Insurance` = '%d' WHERE `HouseID` = '%d'

        format(Query, sizeof(Query), "UPDATE Houses SET HouseAddress = '%s', HouseName = '%s', HouseX = %f, HouseY = %f, HouseZ = %f, HouseInterior = %d, HouseMaxSlots = %d, HouseNewMaxSlots = %d, HousePrice = %d, Owned = %d, HouseOpened = %d, PlayerIsTycoon = %d, Owner = '%s', Insurance = %d WHERE HouseID = %d",
        AHouseData[HouseID][HouseAddress], AHouseData[HouseID][HouseName], AHouseData[HouseID][HouseX], AHouseData[HouseID][HouseY], AHouseData[HouseID][HouseZ], AHouseData[HouseID][HouseInterior], AHouseData[HouseID][HouseMaxSlots], AHouseData[HouseID][HouseNewMaxSlots], AHouseData[HouseID][HousePrice], Owned33,
        AHouseData[HouseID][HouseOpened], AHouseData[HouseID][PlayerIsTycoon], AHouseData[HouseID][Owner], AHouseData[HouseID][Insurance], HouseID); // 15

        print("Failstep 10");
        db_free_result(db_query(Database, Query));

        print("Failstep 11");
    }
    db_free_result(Result);

    print("House saved. Stating to save vehicles...");

And the AHouseData as RoboN1X asked for :

pawn Код:

// Setup a custom type that holds all data for houses
enum THouseData
{
    PickupID, // Holds the pickup-id that is linked to this house
    Text3D:DoorText, // Holds the reference to the 3DText above the house's pickup
    MapIconID, // Holds the ID of the mapicon for the house

    HouseAddress[100], // Holds the address of the house (this will be displayed above the pickup near the house when it's owned)
    HouseName[100], // Holds the name of the house (this will be displayed above the pickup near the house when it's owned)
    Insurance, // Holds "1" if the house has an insurance for the vehicles belonging to this house
    Float:HouseX, // Holds the X-coordinate of the pickup for the house
    Float:HouseY, // Holds the Y-coordinate of the pickup for the house
    Float:HouseZ, // Holds the Z-coordinate of the pickup for the house
    HouseInterior, // Holds the interior of the house
    HouseMaxSlots, // Holds the maximum ammount of slots this house has originally
    HouseNewMaxSlots, // Holds the maximum level this house can be upgraded to
    HousePrice, // Holds the price for buying the house, the same price applies when upgrading a house per level
    bool:Owned, // Holds true if the house is owned by somebody
    bool:PlayerIsTycoon, // Holds true if the house owner has 1000 or more truckloads
    Owner[24], // Holds the name of the owner of the house
    bool:HouseOpened, // Holds true if the house is open to the public (anyone can enter), false means: only the owner can enter it
    VehicleIDs[50] // Holds the vehicle-id's of the vehicles linked to this house
}
// Holds the data for all houses
new AHouseData[MAX_HOUSES][THouseData];

Let me clearify that creating a house and putting the values of the house into database using "INSERT INTO `Houses`" works. I just checked with a database browser and saw that the values for the house was correct. The problem is that when a player buys the house, it wont save the new values eg. house owned/owner/house name etc.

Thanks for your replies!

Re: SQLite problem [+REP] - RoboN1X - 30.10.2015

Try this:

Код:

printf("Started to save house %d", HouseID);
//Open sqlite database

print("Failstep 1");

new DB:Database, DBResult:Result;

print("Failstep 2");

new HouseStrCat[800];

print("Failstep 3");
new Query[1100];
new HouseIDQuery[128];

print("Failstep 4");

Database = db_open("DataHouses.db");

print("Failstep 5");

format(HouseIDQuery, sizeof(HouseIDQuery), "SELECT * FROM Houses WHERE HouseID = %d", HouseID);

print("Failstep 6");

Result = db_query(Database, HouseIDQuery);

print("Searching for house in database...");


if(!db_num_rows(Result)) //No house with this ID was found in the database
{
	print("No house with that id was found. Adding in in the database...");

	print("Failstep 7");

	strcat(HouseStrCat, "INSERT INTO Houses (HouseID, HouseAddress, HouseName, HouseX, HouseY, HouseZ, HouseInterior, HouseMaxSlots, HouseNewMaxSlots, HousePrice, Owned, HouseOpened, PlayerIsTycoon, Owner, Insurance VALUES");
	format(Query, sizeof(Query), "%s(%d,'%s','%s',%f,%f,%f,%d,%d,%d,%d,%d,%d,%d,'%s',%d)", HouseStrCat,
	HouseID, AHouseData[HouseID][HouseAddress], AHouseData[HouseID][HouseName], AHouseData[HouseID][HouseX], AHouseData[HouseID][HouseY], AHouseData[HouseID][HouseZ], AHouseData[HouseID][HouseInterior], AHouseData[HouseID][HouseMaxSlots], AHouseData[HouseID][HouseNewMaxSlots],
	AHouseData[HouseID][HousePrice], AHouseData[HouseID][Owned], AHouseData[HouseID][HouseOpened], AHouseData[HouseID][PlayerIsTycoon], AHouseData[HouseID][Owner], AHouseData[HouseID][Insurance]); // 44

	print("Failstep 8");

	print("Failstep 9");

	db_free_result(db_query(Database, Query));

	print("Failstep 10");

	strdel(HouseStrCat, 0, 800);

	print("Added!");
}
else if(db_num_rows(Result)) // The house ID is in the database. Update the values
{
	print("House found in database! Updaing...");

	new Owned33 = 0;
	if(AHouseData[HouseID][Owned] == true)
	{
		Owned33 = 1;
	}
	else if(AHouseData[HouseID][Owned] == false)
	{
		Owned33 = 0;
	}

	printf("House owned %d House Owner %s", Owned33, AHouseData[HouseID][Owner]);

	print("Failstep 9");

	//UPDATE `Houses` SET `HouseAddress` = '%s', `HouseName` = '%s', `HouseX` = '%f', `HouseY` = '%f', `HouseZ` = '%f', `HouseInterior` = '%d', `HouseMaxSlots` = '%d', `HouseNewMaxSlots` = '%d', `HousePrice` = '%d', `Owned` = '%d', `HouseOpened` = '%d', `PlayerIsTycoon` = '%d', `Owner` = '%s', `Insurance` = '%d' WHERE `HouseID` = '%d'

	format(Query, sizeof(Query), "UPDATE Houses SET HouseAddress = '%s', HouseName = '%s', HouseX = %f, HouseY = %f, HouseZ = %f, HouseInterior = %d, HouseMaxSlots = %d, HouseNewMaxSlots = %d, HousePrice = %d, Owned = %d, HouseOpened = %d, PlayerIsTycoon = %d, Owner = '%s', Insurance = %d WHERE HouseID = %d",
	AHouseData[HouseID][HouseAddress], AHouseData[HouseID][HouseName], AHouseData[HouseID][HouseX], AHouseData[HouseID][HouseY], AHouseData[HouseID][HouseZ], AHouseData[HouseID][HouseInterior], AHouseData[HouseID][HouseMaxSlots], AHouseData[HouseID][HouseNewMaxSlots], AHouseData[HouseID][HousePrice], Owned33,
	AHouseData[HouseID][HouseOpened], AHouseData[HouseID][PlayerIsTycoon], AHouseData[HouseID][Owner], AHouseData[HouseID][Insurance], HouseID); // 15

	print("Failstep 10");
	db_free_result(db_query(Database, Query));

	print("Failstep 11");
}
db_free_result(Result);

print("House saved. Stating to save vehicles...");

Not tested.

Quote:

Originally Posted by Hessu

I just checked with a database browser and saw that the values for the house was correct. The problem is that when a player buys the house, it wont save the new values eg. house owned/owner/house name etc.

Thanks for your replies!

Can you show the CREATE table schema?

Re: SQLite problem [+REP] - Hessu - 30.10.2015

Sorry, but now it says the same error at inserting data too, and i looked in the database, and no house had been inserted.

Here is how i create the table (all visible in browser too) on ongamemodeinit.

pawn Код:

//Create the databases and tables if not existed

    //Open vehicle database
    new DB:Database, Query[500], catted[800];
    Database = db_open("DataHouses.db");

    format(Query, sizeof(Query), "CREATE TABLE IF NOT EXISTS `Houses` (`HouseID`, `HouseAddress`, `HouseName`, `HouseX`, `HouseY`, `HouseZ`, `HouseInterior`, `HouseMaxSlots`, `HouseNewMaxSlots`, `HousePrice`, `Owned`, `HouseOpened`, `PlayerIsTycoon`, `Owner`, `Insurance`)");
    db_free_result(db_query(Database, Query));

    strcat(catted, "CREATE TABLE IF NOT EXISTS `Vehicle` (`HouseID`, `Pass`, `Model`, `Fuel`, `Heal`, `PaintJob`,  `Component0`, `Component1`, `Component2`, `Component3`, `Component4`, `Component5`, `Component6`, `Component7`, `Component8`, `Component9`", sizeof(catted));
    strcat(catted, " `Component10`, `Component11`, `Component12`, `Component13`, `Color1`, `Color2`, `SpawnX`, `SpawnY`, `SpawnZ`, `SpawnRot`, `RemainingInsurance`, `PanelPanels`, `PanelDoors`, `PanelLights`, `PanelTires`, `Clamped`, `HasPass`, `CarSlot`)", sizeof(catted));
    db_free_result(db_query(Database, catted));
    strdel(catted, 0, 800);

    //Close the vehicle database
    db_close(Database);

If you want, you can help me via teamwiever.

Thanks!

Re: SQLite problem [+REP] - RoboN1X - 30.10.2015

Whoops! that must be the reason why even my code probably wont work... you are saving all data with TEXT type... so is that done on purpose?

Btw i missed a closing bracket before "VALUES".