Best database saving?

[denny199]

Hey there,

I'm scripting a script (doh), which will save objects to a database, but now I need some proper help with these questions:

1: Which database is the strongest, fast and easy to setup? ( please, don't say "XML" -,- )

(Like SQL?)

2:Can you give me a example how it works? ( connection, storing, etc. ) for data for example:

I'm saving objects, first I tought the default MTA:SA account system, but I think that 100 account-data on 1 account won't work so well.

3:Or can I just store tables in the default MTA:SA account system?

Kindly regards,

Danny

[Puma]

1. MySQL.

2. https://wiki.multitheftauto.com/wiki/Se ... _functions

In short: connect to a database using dbConnect function and do stuff (storing, altering and getting data) by sending queries using the dbQuery function. http://en.wikipedia.org/wiki/MySQL

You need to install a MySQL database first, though. http://www.mysql.com/

3. No, and default MTASA account sucks. Use MySQL if you want to do it the right way.

If you need more info, just ask for it .

[denny199]

Thanks,

I'll start tommorow.

My idea was ( for my freeroam server with friends ) to make a appartement, where you can make the interior ingame , just like roleplay easy, buying seats,chais etc, and then place it in your appartment.

Some pics of our map:

Before ( without glass ):

And after ( with the map ):

Kindly Regards,

Danny

[denny199]

Okay, I have one question.

What is the best way to save the objects?

Make more Query's for one house or just store them all on ONE Query?

Because I want to save arround 50 objects like this:

Objectmodel - the model of the object

Posx - posx

posy - posy

posz - posz

apartementid - id

And that for 50 objects, how should I do this?

Create other Query's or just in one Query line?

[Renkon]

Okay, I have one question.

What is the best way to save the objects?

Make more Query's for one house or just store them all on ONE Query?

Because I want to save arround 50 objects like this:

Objectmodel - the model of the object

Posx - posx

posy - posy

posz - posz

apartementid - id

And that for 50 objects, how should I do this?

Create other Query's or just in one Query line?

You gotta make 50 queries for that.

Recommendation, store them in a table and then with a for bucle you insert and insert and insert 50 times.

[DiSaMe]

You gotta make 50 queries for that.

No, you don't. Single SQL statement can insert multiple rows into the table.

[Renkon]

You gotta make 50 queries for that.

No, you don't. Single SQL statement can insert multiple rows into the table.

Ah right, multiples insert into.. it's just that I don't think function dbQuery won't tolerate a big string.

[denny199]

Okay, I'll make 50 queries then the script, but one thing I didn't understand:

"Recommendation, store them in a table and then with a for bucle you insert and insert and insert 50 times."

What do you mean with this?

I think that I'll save the elements direct in the database, and then when the resource start i'll put them into a table, and then when the player will hit the colshape, the table will call fromthe server to the client, so it will only create the objects on the client's screen, since there already are enough objects.

OR

Do I have to save the created elements in a table first, and then when theguy is finished with placing all the objects in his apartement, it will read the table and then store it in the database?

Thanks,

Danny.

[Puma]

"Recommendation, store them in a table and then with a for bucle you insert and insert and insert 50 times."abde]

objectsTable = { object1, object2, object3, object4 et cetera } 
for i, object in pairs ( objectsTable ) do 
    local model =  
    local x, y, z = 
    local rx, ry, rz = 
    local query = dbQuery ( databaseConnection, "INSERT INTO table_name VALUES (?,?,?,?,?,?,?)", model, x, y, z, rx, ry, rz ) 
    dbFree ( query ) 
end 

Something like that, but you need to have some ID column to identify what object belongs to what player/account.

I think that I'll save the elements direct in the database, and then when the resource start i'll put them into a table, and then when the player will hit the colshape, the table will call fromthe server to the client, so it will only create the objects on the client's screen, since there already are enough objects.

OR

Do I have to save the created elements in a table first, and then when theguy is finished with placing all the objects in his apartement, it will read the table and then store it in the database?

Thanks,

Danny.

Second one is better. Get the data from the database and store it into a table when it is needed: a MySQL connection is quite fast, don't worry about lag. You can request all 50 objects using 1 query.

[denny199]

Thanks guys for all your support, MySQL is pretty easy, now there's another big step for me in the scripters world

[IIYAMA]

Is MySQL also for data that will be executed lots and lots times? or is it better to use a table and store it in to the ram?(like normal tables)

MySQL is more hard-drive work isn't?

[J.S.]

Is MySQL also for data that will be executed lots and lots times? or is it better to use a table and store it in to the ram?(like normal tables)

MySQL is more hard-drive work isn't?

MySQL is only necessary if you want something to save after you power down the server. If you store it in the RAM it's deleted once the server is shut down.

[IIYAMA]

and if I want to store personal stuff, (player data).

Is it recommended to use accountdata or MySOL?

[denny199]

Depends, I think.

BTW, I stored all the data in a table, like what you said IIYAMA.

I'm using the mta accountdata to store like little things now (money, etc.)

And I'm saving CJ-clothes in client-side xml, since I don't need that data for everyone visible.

And saving a huge string, or positions I'm using MySQL these days.

But accountdata is for personal stuff (in my words) handy, you can save the player's X,Y,Z position on that, and money, and points, since it's a little data what you are saving

[IIYAMA]

thank you!

Players/server = RAM (temporary)

Players = accountdata

Server = mysql and SQL

Right?

[denny199]

Yes, but one thing not,

I'm saving now every player on a MySQL database for object saving like:

Playeraccountname: The Player's account name.

Modelid of the object(over than 50 model id's can a player buy): How much does the player have this object.

So it would be a pain in the ass if I need to create 50 account data string for this, that's why MySQL makes it easier now

[paulocf]

As far as I understand how MTA and database function as a whole, you should have permanent data on a database (sqlite for instance) and load them into the RAM (select queries) by using tables, global variables, or classes as you wish, whenever the resource stops (or server shuts down) you will want to get the data stored in RAM to be sent to the server to save in the database, so it stays permanent.

Having player data in client's RAM is logically better because it avoids never-ending database requests (this would be rough), you should only request data from the database if needed (i.e. avoid INSERT's or UPDATE's everytime).

[denny199]

Hi guys, I went over to a another problem, I don't know how to solve it.

My code:

function setPlayerData(player, Ddata, value) 
      local playeraccount = getPlayerAccount ( player ) 
      if ( playeraccount ) and not isGuestAccount ( playeraccount ) then 
local ACCname = getAccountName ( playeraccount ) 
     
  
  
        local query = dbQuery(handler, "UPDATE playerData SET '"..Ddata.."' = '"..value.."' WHERE ACCOUNTNAME = '"..ACCname.."';" ) 
        local result = dbPoll(query, dbpTime) 
        if(result) then 
            dbFree(query) 
        else 
         outputChatBox ( "No result") 
        end 
    end 
end 

My function usage:

  
addCommandHandler ( "testfunc",  
function (player) 
local Ddata = "D2290" 
local value = 5 
setPlayerData(player, Ddata, value) 
end) 
  

Error:

2013-04-18 20:35:07.325: [script] FAIL: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''D2290' = '1' WHERE ACCOUNTNAME = 'denny19'' at line 1 [Query:UPDATE playerData SET 'D2290' = '1' WHERE ACCOUNTNAME = 'denny19';]

My MySQL table:

(http://imageshack.us/a/img221/963/img17042013143157.png)

[Kenix]

You are wrong here.

local query = dbQuery(handler, "UPDATE playerData SET '"..Ddata.."' = '"..value.."' WHERE ACCOUNTNAME = '"..ACCname.."';" ) 

SET 'your_field' It's wrong! Should be SET `your_field` or SET your_field

[DiSaMe]

Also, don't concatenate the values into the string yourself. Let dbQuery take care of this by putting ? or ?? in the places of values in the string and passing the values as separate arguments. Otherwise players will be able to inject the code into queries.

[Kenix]

Also, If you don't need to do something with result - you don't need to use a dbQuery. Just use a dbExec.

[denny199]

I've occured a another problem, why isn't this working":

dbExec( handler, "UPDATE `??` SET `??`=? WHERE ACCOUNTNAME = `?`", "playerData", Ddata, value, ACCname ) 

(using the same code as above)

error message:

[19:26:55] WARNING: appscript\shop_server.lua:170: dbExec failed; (1054) Unknown column ''denny19'' in 'where clause'

[Castillo]

dbExec ( handler, "UPDATE `playerData` SET `??`=? WHERE ACCOUNTNAME = `?`", Ddata, value, ACCname ) 

[denny199]

Nop, that still didn't worked.

WARNING: appscript\shop_server.lua:170: dbExec failed; (1054) Unknown column ''denny19'' in 'where clause'

[DiSaMe]

It doesn't work because backticks cause everything between them to be interpreted as the column name. As a result, ACCOUNTNAME column is not checked against a value passed to the dbQuery function, but instead, it's checked against a value in the column whose name was passed to dbQuery.