Jump to content

Best database saving?


denny199

Recommended Posts

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

Link to comment

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 :).

Link to comment

Thanks, :D

I'll start tommorow.

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

Some pics of our map:

Before ( without glass ):

gallery292.jpg

And after ( with the map ):

gallery291r.jpg

gallery290.jpg

Kindly Regards,

Danny ;-)

Link to comment

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?

Link to comment
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.

Link to comment

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.

Link to comment
"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.

Link to comment
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.

Link to comment

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 :)

Link to comment

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 :P

Link to comment

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).

Link to comment

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:

img17042013143157.png

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

Link to comment

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

Link to comment

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.

Link to comment

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'

Link to comment

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.

Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...