[Tutorial] How to: SQLite - Printable Version

[Tutorial] How to: SQLite - 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)
+---- Forum: Tutorials (https://sampforum.blast.hk/forumdisplay.php?fid=70)
+---- Thread: [Tutorial] How to: SQLite (/showthread.php?tid=262417)

Pages: 1 2

Re: How to: SQLite - __ - 15.06.2012

Quote:

Originally Posted by TheLazySloth

It's a bit slow at updating queries.

Takes 3secs to run this code.

Код:

stock SaveObjectVariables(ObjectId) {
	KillTimer(oTimerId[ObjectId]);

	new Query[250],
		DBResult: Result;

	oDB = db_open("Objects.db");
	format(Query, 250, "SELECT * FROM object_data WHERE Id=%d", ObjectId);
	Result = db_query(oDB, Query);

	if(db_num_rows(Result) != 1) {
		format(Query, 250, "DELETE FROM object_data WHERE Id=%d", ObjectId), db_free_result(db_query(oDB, Query));
		format(Query, 250, "INSERT INTO object_data (Id) VALUES (%d)", ObjectId), db_free_result(db_query(oDB, Query));
	}

	db_free_result(Result);
	format(Query, 250, "UPDATE object_data SET ModelId=%d WHERE Id=%d", oModelId[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET Spawned=%d WHERE Id=%d", oSpawned[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnWorld=%d WHERE Id=%d", oSpawnWorld[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnInterior=%d WHERE Id=%d", oSpawnInterior[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnX=%f WHERE Id=%d", oSpawnX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnY=%f WHERE Id=%d", oSpawnY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnZ=%f WHERE Id=%d", oSpawnZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnRX=%f WHERE Id=%d", oSpawnRX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnRY=%f WHERE Id=%d", oSpawnRY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET SpawnRZ=%f WHERE Id=%d", oSpawnRZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosWorld=%d WHERE Id=%d", oPosWorld[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosInterior=%d WHERE Id=%d", oPosInterior[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosX=%f WHERE Id=%d", oPosX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosY=%f WHERE Id=%d", oPosY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosZ=%f WHERE Id=%d", oPosZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosRX=%f WHERE Id=%d", oPosRX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosRY=%f WHERE Id=%d", oPosRY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET PosRZ=%f WHERE Id=%d", oPosRZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET Timestamp=%d WHERE Id=%d", oTimestamp[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET IsGate=%d WHERE Id=%d", oIsGate[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedX=%f WHERE Id=%d", oClosedX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedY=%f WHERE Id=%d", oClosedY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedZ=%f WHERE Id=%d", oClosedZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedRX=%f WHERE Id=%d", oClosedRX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedRY=%f WHERE Id=%d", oClosedRY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET ClosedRZ=%f WHERE Id=%d", oClosedRZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedX=%f WHERE Id=%d", oOpenedX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedY=%f WHERE Id=%d", oOpenedY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedZ=%f WHERE Id=%d", oOpenedZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedRX=%f WHERE Id=%d", oOpenedRX[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedRY=%f WHERE Id=%d", oOpenedRY[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	format(Query, 250, "UPDATE object_data SET OpenedRZ=%f WHERE Id=%d", oOpenedRZ[ObjectId], ObjectId), db_free_result(db_query(oDB, Query));
	db_close(oDB);

	printf("Object %d has saved.", ObjectId);

	oTimerId[ObjectId] = SetTimerEx("UpdateObject", 500, true, "d", ObjectId);
	return true;
}

Wish it was a little faster.

You only need one query!

Re: How to: SQLite - ReneG - 15.06.2012

Quote:

Originally Posted by [HLF]Southclaw

Also, Vincent is right, you can just save them all on one query (Though this might be a long line so you may have to do 2 or 3 separate lines and stick them together, or query them separately. I'm not sure which would be faster)

From what I've learned, formatting one large query would be much more efficient than updating each column in a separate query. Use a string counter and estimate to see how long the queries will be. I tend to stray away from queries that are longer than 1024 cells.

Question.

Is it efficient to free all queries that don't use the SELECT clause?

I've read somewhere you have to free results of all queries, and I also read in this tutorial that you're only supposed to free results that are returning data (SELECT clause).

Re: How to: SQLite - ReneG - 15.06.2012

Quote:

Originally Posted by [HLF]Southclaw

Код:

UPDATE `Player` (`data1`, `data2`, `data3`) VALUES('%d', '%d', '%d') WHERE `name` = '%s'

Excluding the WHERE clause and everything after it, that syntax is only for using the INSERT clause.

To update more than one value, you must follow this syntax.

Example

pawn Код:

UPDATE `Users` SET Password = '%s', Level = %d, AdminLevel = %d WHERE `Name` = '%s'

SQL is like talking.

That query will update the Users table by setting password = x, level = x, adminlevel = x only WHERE the Name = x.

You can also add more checks by using the AND clause.

pawn Код:

UPDATE `Users` SET Password = '%s', Level = %d, AdminLevel = %d WHERE Name = '%s' AND Regged = 1

Re: How to: SQLite - TheLazySloth - 15.06.2012

Quote:

Originally Posted by __

You only need one query!

Well guys, what if I decide a variable has to go, I'd only have to delete 1 line instead of edit a few.

Re: How to: SQLite - Jonny5 - 15.06.2012

Quote:

Originally Posted by VincentDunn

Question.

Is it efficient to free all queries that don't use the SELECT clause?

I've read somewhere you have to free results of all queries, and I also read in this tutorial that you're only supposed to free results that are returning data (SELECT clause).

I would say if your not returning data then theirs no need to free the results,
eg: Insert,Update and Delete dont return data.

But im not sure, I haven't found anyway to directly
see whats in the results and how they are formatted.

Re: How to: SQLite - ReneG - 15.06.2012

Quote:

Originally Posted by [HLF]Southclaw

Oh that sucks D: It's gonna take a while, I may as well just code a system for saving data rather than writing the queries.

This is what put me off SQL, it's just so long and drawn out to save small parts of data!

I'm going to use strcat and a similar system to my file script to build a query string from input data to be updated or created.

Well you can use your method by using the escape char, that's a pretty good idea if you have trouble reading the data, but if you just take the time to write it out. It's almost like writing a chunk of file-based saving code. Shouldn't take you longer than 10 minutes.

For me, it's easiest to read it like this.

pawn Код:

format(string1, 500, "UPDATE `Users` SET Password = '%s', Level = '%d', \
                      AdminLevel = '%d', VIPLevel = '%d', ConnectedTime = '%d', \
                      Sex = '%d', Age = '%d', Origin = '%d', Muted = '%d', \
                      Respect = '%d', Cash = '%d', BankAccount = '%d', \
                      Kills = '%d', Deaths = '%d', WantedDeaths = '%d', \
                      TimesArrested = '%d', Phonebook = '%d', Number = '%d', LottoNumber = '%d', ",

    db_escape(PlayerInfo[playerid][pKey]),
    PlayerInfo[playerid][pLevel],
    PlayerInfo[playerid][pAdminLevel],
    PlayerInfo[playerid][pVip],
    PlayerInfo[playerid][pConnectTime],
    PlayerInfo[playerid][pSex],
    PlayerInfo[playerid][pAge],
    PlayerInfo[playerid][pOrigin],
    PlayerInfo[playerid][pMuted],
    PlayerInfo[playerid][pRespect],
    PlayerInfo[playerid][pCash],
    PlayerInfo[playerid][pBank],
    PlayerInfo[playerid][pKills],
    PlayerInfo[playerid][pDeaths],
    PlayerInfo[playerid][pWantedDeaths],
    PlayerInfo[playerid][pTimesArrested],
    PlayerInfo[playerid][pPhoneBook],
    PlayerInfo[playerid][pNumber],
    PlayerInfo[playerid][pLotto]
);

Then format the rest of the data, finish the second query, strcat them together, and query them.

Re: How to: SQLite - kingchandio - 17.06.2012

please some create Sql based register system or tutorial so that we can understand, with saving kills, deaths, name,

Re: How to: SQLite - Calgon - 17.06.2012

Quote:

Originally Posted by kingchandio

please some create Sql based register system or tutorial so that we can understand, with saving kills, deaths, name,

Read the first page. There's a good example with the temporary banning system.

Re: How to: SQLite - kingchandio - 17.06.2012

Bro if you could create simple register system with saving data like name and money then it would be very helpful for me to understand it otherwise im confused where to use what, i will be thankful to you

Re: How to: SQLite - Calgon - 17.06.2012

I'm not really willing to make an entire script for that. Search, there's probably a few already.

Re: How to: SQLite - Dodo9655 - 24.06.2012

Do I have to use DB_Escape for every string that is in a query to prevent SQL Injection?

Re: How to: SQLite - Kreatyve - 12.10.2012

This is interesting, good to know.

~ Kreatyve ~

Re: How to: SQLite - Guest4390857394857 - 22.02.2014

nice tutorial !