Good methods for user accounts? [SQLite help needed also]
#21

Out of interest do you have any information on this problem? Such as cases where the problem occurred?
Reply
#22

After around 10 minutes trying to learn how to create a database in SQLite, its already doing my head in.

I found a few tutorials on ******* and on ******.

I type the commands exactly as it says in the tutorial, only to get an error message.

All i want to do is create a database and then create the rows and colums for my data to be stored..

SQLite itself was quite scare for windows. I dont use Linux, but my samp server does. So will the database work if i create it in Windows, and use it in Linux?
Reply
#23

SQLite is built into the server as I said, you don't need to find it for Windows. The commands should be the same for MySQL and SQLite, they're both based on SQL, but it's up to you how you do it, that was just a suggestion based on the fact that you couldn't get MySQL working.
Reply
#24

Yeah i noticed its on wiki, how to use the functions. But how do i create the database?

The only thing i found on wiki was how to open an existing one.
Reply
#25

http://www.******.co.uk/search?q=sql+create+database&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-GBfficial&client=firefox-a

Not trying to tell you to "search", I just honestly don't know which is the best resource for you. And as I said it's the same syntax for SQLite and MySQL (that's what the "SQL" part is).
Reply
#26

I did around 30mins reading and searching, until i found a download called SQLite3.

I downlaoded that, found some tutorials on it, one being on *******.

It explained how to create a simple table.

so i tried the same method. Using sqlite3 test.db

But i got no luck with that just a like that looked like " ...> "



EDIT:

If i was to open a blank gamemode. Then write db_open("text.db"); under OnGameModeInit

Would that automatically create "test.db" in my scriptfiles folder, but with no content?

Is it that easy?
Reply
#27

All I could suggest is try it (also try without the .db), I'm not an expert on the SQL functions.
Reply
#28

There's a surprising lack of examples for sqlite in samp considering how easy it is to use. A simple example:

pawn Код:
#define GAMEMODE_DATABASE   "Gamemode.sqlite"

public OnGameModeInit()
{
    new DB:db = db_open(GAMEMODE_DATABASE);
    if(db) {
        db_free_result(db_query(db, "CREATE TABLE IF NOT EXISTS `Users` ( `name` TEXT NOT NULL PRIMARY KEY , `skin` INTEGER NOT NULL DEFAULT 0 )"));
        db_close(db);
    }
}

public OnPlayerConnect(playerid)
{
    if(!IsPlayerNPC(playerid)) {
        new DB:db = db_open(GAMEMODE_DATABASE);
        if(db) {
            new string[128];
            format(string, sizeof(string), "SELECT * FROM `Users` WHERE ( `name` = '%s' ) LIMIT 1", ReturnPlayerName(playerid));
            new DBResult:result = db_query(db, string);
            if(db_num_rows(result)) {
                db_get_field_assoc(result, "skin", string, sizeof(string));
                gPlayerSkin[playerid] = strval(string);
            }
            db_free_result(result);
            db_close(db);
        }
    }
    return 1;
}

public OnPlayerRequestSpawn(playerid)
{
    if(!IsPlayerNPC(playerid)) {
        new DB:db = db_open(GAMEMODE_DATABASE);
        if(db) {
            new string[128];
            format(string, sizeof(string), "SELECT * FROM `Users` WHERE ( `name` = '%s' ) LIMIT 1", ReturnPlayerName(playerid));
            new DBResult:result = db_query(db, string);
            if(db_num_rows(result)) {
                format(string, sizeof(string), "UPDATE `Users` SET `skin` = %i WHERE ( `name` = '%s' )", gPlayerSkin[playerid], ReturnPlayerName(playerid));
            } else {
                format(string, sizeof(string), "INSERT INTO `Users` ( `name` , `skin` ) VALUES ( '%s' , %i )", ReturnPlayerName(playerid), gPlayerSkin[playerid]);
            }
            db_free_result(result);
            db_free_result(db_query(db, string));
            db_close(db);
        }
    }
    return 1;
}
The database can have be any valid filename I think. It's created if it doesn't exist or db_open returns 0 if it can't be opened. Pretty much everything you need to know about sqlite can be found here.

One thing to be careful of is NULL values, which crash db_get_field_assoc and db_get_field. You can either put a NOT NULL constraint on your columns or do select statements like [code=sql]SELECT ifnull(`skin`,'') AS `skin` FROM `Users` WHERE ( `name` = '%s' ) LIMIT 1[/code]

For testing you might want to put something like
pawn Код:
stock DBResult:debug_db_query(DB:db, query[])
{
    print(query);
    return db_query(db, query);
}
#define db_query    debug_db_query
just after including a_samp, then if a query doesn't work you can try executing it in something like the firefox addon which will tell you if there's an error.

Also, if you're putting text from the user into the database (ban reasons or whatever), you need to escape it
pawn Код:
strreplace(string "'", "''");
Reply
#29

Ah thanks man, this is awesome!

Its had my head in bits trying to work it out.

Is there a tutorial available also, where it is relevent to using SQLite in SAMP?

Thanks again
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)