[DarkLored]

I have been creating a filterscript and for some reason when I open the server it does not properly create the sql_sequence table and messes up my register system.

pawn Код:

LoadDatabase()
{
    new Query[800];
    AdminSys = db_open("darkadmin.db");
    strcat(Query, "CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(24) COLLATE NOCASE, password VARCHAR(129), admin INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));
    strcat(Query, "score INTEGER DEFAULT 0 NOT NULL, money INTEGER DEFAULT 1000 NOT NULL, banned INTEGER DEFAULT 0 NOT NULL, ip VARCHAR(16) NOT NULL )", sizeof(Query));
    db_query(AdminSys, Query);
}

does anyone know why this is happening? help will be appreciated.

[LetsOWN[PL]]

Quote:

Originally Posted by DarkLored I have been creating a filterscript and for some reason when I open the server it does not properly create the sql_sequence table and messes up my register system. pawn Код: LoadDatabase() {     new Query[800];     AdminSys = db_open("darkadmin.db");     strcat(Query, "CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(24) COLLATE NOCASE, password VARCHAR(129), admin INTEGER DEFAULT 0 NOT NULL,", sizeof(Query));     strcat(Query, "score INTEGER DEFAULT 0 NOT NULL, money INTEGER DEFAULT 1000 NOT NULL, banned INTEGER DEFAULT 0 NOT NULL, ip VARCHAR(16) NOT NULL )", sizeof(Query));     db_query(AdminSys, Query); } does anyone know why this is happening? help will be appreciated.

Hello, try with this query:

Код:

CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTO_INCREMENT, username VARCHAR(24), password VARCHAR(129), admin INTEGER DEFAULT 0 NOT NULL, score INTEGER DEFAULT 0 NOT NULL, money INTEGER DEFAULT 1000 NOT NULL, banned INTEGER DEFAULT 0 NOT NULL, ip VARCHAR(16) NOT NULL )

What could be the problem:

1) You've forgotten about underscore in AUTO_INCREMENT
2) 'NOCASE' was unknown collate

I've tried to run this query in my own MySQL server and it ran well.
Any other questions?

Greetings.

[DarkLored]

Quote:

Originally Posted by LetsOWN[PL] Hello, try with this query: Код: CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTO_INCREMENT, username VARCHAR(24), password VARCHAR(129), admin INTEGER DEFAULT 0 NOT NULL, score INTEGER DEFAULT 0 NOT NULL, money INTEGER DEFAULT 1000 NOT NULL, banned INTEGER DEFAULT 0 NOT NULL, ip VARCHAR(16) NOT NULL ) What could be the problem: 1) You've forgotten about underscore in AUTO_INCREMENT 2) 'NOCASE' was unknown collate I've tried to run this query in my own MySQL server and it ran well. Any other questions? Greetings.

It creates the table perfectly, but it does not create the sql sequence table correctly and it doesn't add registered users I have already erased the whole thing and tried to recreate the register system but I had no luck, I use the same method on other of my work and I have checked everything and it's correct I can't seem to figure out the problem.

[LetsOWN[PL]]

Quote:

Originally Posted by DarkLored It creates the table perfectly, but it does not create the sql sequence table correctly and it doesn't add registered users I have already erased the whole thing and tried to recreate the register system but I had no luck, I use the same method on other of my work and I have checked everything and it's correct I can't seem to figure out the problem.

Well, I've tried tried to run this one:

pawn Код:

new DB: xDB;

main() {
    xDB = db_open("TestDB.db");

    new Query[303];
    strcat(Query, "CREATE TABLE IF NOT EXISTS users (userid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(24), password VARCHAR(129), admin INTEGER DEFAULT 0 NOT NULL, ", sizeof( Query ));
    strcat(Query, "score INTEGER DEFAULT 0 NOT NULL, money INTEGER DEFAULT 1000 NOT NULL, banned INTEGER DEFAULT 0 NOT NULL, ip VARCHAR(16) NOT NULL )", sizeof( Query ));

    db_query(xDB, Query);
}

It appears, that in SQLite it is AUTOINCREMENT, without underscore below it.. My bad..

It successfully created TestDB.db in /scriptfiles/ folder with this table in it.

After I've added new record, value in userid behaved like it should, and 1 appeared.
sqlite_sequence also did it's job correctly:


Maybe.. Try with my code and see, if it works for You. If it's not, then I have no idea what's going on, I'm sorry.

Greetings.

[DarkLored]

Still I am experiencing the problem, now it creates it perfectly but when someone connects and registers it does not create a record for the player.

[DarkLored]

I noticed that Query is being executed correctly but it does not create a new row each time I complete a register.

I have been brainstorming looking at topics till now but couldn't find a solution.

Here is my code

pawn Код:

if(dialogid == Register)
    {
        if(response)
        {
            if(!IsValidPassword(inputtext))
            {
                SendClientMessage(playerid, -1, "[ERROR]Invalid password, valid characters are A-Z, a-z, 0-9.");
                ShowPlayerDialog(playerid, Register, DIALOG_STYLE_PASSWORD, "Register", "Welcome new comer! Please choose a password and type it in to register.", "Register", "Quit");
                return 1;
            }
            if(strlen(inputtext) < 3 || strlen(inputtext) > 24)
            {
                SendClientMessage(playerid, -1, "[ERROR]You can only write a password that contains 3 to 24 characters.");
                ShowPlayerDialog(playerid, Register, DIALOG_STYLE_PASSWORD, "Register", "Welcome new comer! Please choose a password and type it in to register.", "Register", "Quit");
                return 1;
            }
            new Query[800];
            WP_Hash(dAdmin[playerid][USER_PASSWORD], 129, inputtext);
            format(Query, sizeof(Query), "INSERT INTO users (username, password) VALUES ('%s', '%s')", DB_Escape(dAdmin[playerid][USER_NAME]), DB_Escape(dAdmin[playerid][USER_PASSWORD]));
            db_query(Database, Query);
            ShowPlayerDialog(playerid, Login, DIALOG_STYLE_PASSWORD, "Login", "Congratulations! You have successfully registered to the server to complete the proccess re enter your password.", "Login", "Quit");
        }
        else Kick(playerid);
    }

print

Код:

[23:56:22] [join] DarkLored has joined the server (0:127.0.0.1)
[23:56:24] INSERT INTO users (username, password) VALUES ('DarkLored', '947BDCAF9B8FAC501D1C9DAA37DC57714F85AA0D781B18914B29BCF379DFBA665BBEDC15817873B1D92ADE4ABFE8ECDC8EBFC07F463D268ED4C595B4E383A8DB')
[23:56:35] [part] DarkLored has left the server (0:1)

[LetsOWN[PL]]

Quote:

Originally Posted by DarkLored I noticed that Query is being executed correctly but it does not create a new row each time I complete a register. I have been brainstorming looking at topics till now but couldn't find a solution. Here is my code pawn Код: if(dialogid == Register)     {         if(response)         {             if(!IsValidPassword(inputtext))             {                 SendClientMessage(playerid, -1, "[ERROR]Invalid password, valid characters are A-Z, a-z, 0-9.");                 ShowPlayerDialog(playerid, Register, DIALOG_STYLE_PASSWORD, "Register", "Welcome new comer! Please choose a password and type it in to register.", "Register", "Quit");                 return 1;             }             if(strlen(inputtext) < 3 || strlen(inputtext) > 24)             {                 SendClientMessage(playerid, -1, "[ERROR]You can only write a password that contains 3 to 24 characters.");                 ShowPlayerDialog(playerid, Register, DIALOG_STYLE_PASSWORD, "Register", "Welcome new comer! Please choose a password and type it in to register.", "Register", "Quit");                 return 1;             }             new Query[800];             WP_Hash(dAdmin[playerid][USER_PASSWORD], 129, inputtext);             format(Query, sizeof(Query), "INSERT INTO users (username, password) VALUES ('%s', '%s')", DB_Escape(dAdmin[playerid][USER_NAME]), DB_Escape(dAdmin[playerid][USER_PASSWORD]));             db_query(Database, Query);             ShowPlayerDialog(playerid, Login, DIALOG_STYLE_PASSWORD, "Login", "Congratulations! You have successfully registered to the server to complete the proccess re enter your password.", "Login", "Quit");         }         else Kick(playerid);     } print Код: [23:56:22] [join] DarkLored has joined the server (0:127.0.0.1) [23:56:24] INSERT INTO users (username, password) VALUES ('DarkLored', '947BDCAF9B8FAC501D1C9DAA37DC57714F85AA0D781B18914B29BCF379DFBA665BBEDC15817873B1D92ADE4ABFE8ECDC8EBFC07F463D268ED4C595B4E383A8DB') [23:56:35] [part] DarkLored has left the server (0:1)

The query is good, but not good enough for SQLite though.
This query won't execute, because of NOT NULL attribute, which is assigned for each column in this table (see the CREATE TABLE.. structure).

There are (I guess only those are) 3 possible solutions:

1. You've to remove NOT NULL attribute from table structure
2. You have to assign values in 'INSERT INTO' query FOR EACH column in this table
3. Recreate this table structure and assign the default values for each column, so that You won't worry about assigning values for each column in one single query.

Hope You'll fix this problem, for any further questions, just go ahead.

Greetings.

[DarkLored]

Quote:

Originally Posted by LetsOWN[PL] The query is good, but not good enough for SQLite though. This query won't execute, because of NOT NULL attribute, which is assigned for each column in this table (see the CREATE TABLE.. structure). There are (I guess only those are) 3 possible solutions: You've to remove NOT NULL attribute from table structure You have to assign values in 'INSERT INTO' query FOR EACH column in this table Recreate this table structure and assign the default values for each column, so that You won't worry about assigning values for each column in one single query. Hope You'll fix this problem, for any further questions, just go ahead. Greetings.

I use the table to create default values, and the Insert is correct as I am using it in two different gamemodes and I haven't experienced this problem with them already checked differences but couldn't find out the issue.