Not Inserting into database

[Debian]

All my other queries working and inserting to tables but this one is not inserting i had made sure all my database inserts properly

Code Select Expand


CREATE TABLE Dstats( Name TEXT, stat BOOLEAN, nogoto BOOLEAN, randspawn BOOLEAN, spawnwep BOOLEAN, wep1 INT, wep2 INT, wep3 INT, wep4 INT, wep5 INT, wep6 INT, wep7 INT, wep8 INT, wep9 INT, wep10 INT, spawnloc BOOLEAN, X FLOAT, Y FLOAT, Z FLOAT )


Code Select Expand


class DebianStats{
// ------------------------------------------------------------------- //

stat = false;
nogoto = false;
randspawn = false;
spawnwep = false;
wep1 = 0;
wep2 = 0;
wep3 = 0;
wep4 = 0;
wep5 = 0;
wep6 = 0;
wep7 = 0;
    wep8 = 0;
wep9 = 0;
wep10 = 0;
spawnloc = false;
X = 0.0;
Y = 0.0;
Z = 0.0;

// ------------------------------------------------------------------- //

constructor( playerName, dbGlobal ){
local query = ::QuerySQL( dbGlobal, "SELECT stat, nogoto, randspawn, spawnwep, wep1, wep2, wep3, wep4, wep5, wep6, wep7, wep8, wep9, wep10, spawnloc, X, Y, Z FROM WStats WHERE Name='" + playerName.tolower() + "'" );
if( ::GetSQLColumnData( query, 15 ) ){
stat = ::GetSQLColumnData( query, 0 );
nogoto = ::GetSQLColumnData( query, 1 );
randspawn = ::GetSQLColumnData( query, 2 );
spawnwep = ::GetSQLColumnData( query, 3 );
wep1 = ::GetSQLColumnData( query, 4 );
wep2 = ::GetSQLColumnData( query, 5 );
wep3 = ::GetSQLColumnData( query, 6 );
wep4 = ::GetSQLColumnData( query, 7 );
wep5 = ::GetSQLColumnData( query, 8 );
wep6 = ::GetSQLColumnData( query, 9 );
wep7 = ::GetSQLColumnData( query, 10 );
wep8 = ::GetSQLColumnData( query, 11 );
wep9 = ::GetSQLColumnData( query, 10 );
wep10 = ::GetSQLColumnData( query, 11 );
spawnloc = ::GetSQLColumnData( query, 12 );
X = ::GetSQLColumnData( query, 13 );
Y = ::GetSQLColumnData( query, 14 );
Z = ::GetSQLColumnData( query, 15 );
}
::FreeSQLQuery( query );
}


function Update( player, dbGlobal ){
::QuerySQL( dbGlobal, "UPDATE Dstats SET stat = "+ stat +", nogoto = "+ nogoto +", randspawn = "+ randspawn +",spawnwep = "+ spawnwep +", wep1 = "+ wep1 +", wep2 = "+ wep2 +", wep3 = "+ wep3 +", wep4 = "+ wep4 +", wep5 = "+ wep5 +", wep6 = "+ wep6 +", wep7 = "+ wep7 +", wep8 = "+ wep8 +", wep9 = "+ wep9 +", wep10 = "+ wep10 +", spawnloc = "+ spawnloc +", X = "+ X +", Y = "+ Y +", Z = "+ Z +" WHERE Name='" + player.Name.tolower() + "'");
}

function Register( player, dbGlobal ){
::QuerySQL( dbGlobal, "INSERT INTO Dstats VALUES ( '" + player.Name.tolower() + "', false, false, false, false, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, false, 0.0, 0.0, 0.0 )" );
}
}


[KAKAN]

I don't so that there's something name BOOLEAN, so you might try to change and try it again

[Thijn]

You called it DStats in your create table, but try to select from WStats.

[Debian]

Thanks Thijn , it is not fixed because it is still not inserting
::QuerySQL( dbGlobal, "INSERT INTO Dstats VALUES ( '" + player.Name.tolower() + "', false, false, false, false, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, false, 0.0, 0.0, 0.0 )" );
 
Update 1 : fixed column data

New code :

Code Select Expand




class DebianStats{
// ------------------------------------------------------------------- //

stat = false;
nogoto = false;
randspawn = false;
spawnwep = false;
wep1 = 0;
wep2 = 0;
wep3 = 0;
wep4 = 0;
wep5 = 0;
wep6 = 0;
wep7 = 0;
    wep8 = 0;
wep9 = 0;
wep10 = 0;
spawnloc = false;
X = 0.0;
Y = 0.0;
Z = 0.0;

// ------------------------------------------------------------------- //

constructor( playerName, dbGlobal ){
local query = ::QuerySQL( dbGlobal, "SELECT stat, nogoto, randspawn, spawnwep, wep1, wep2, wep3, wep4, wep5, wep6, wep7, wep8, wep9, wep10, spawnloc, X, Y, Z FROM Dstats WHERE Name='" + playerName.tolower() + "'" );
if( ::GetSQLColumnData( query, 17 ) ){
stat = ::GetSQLColumnData( query, 0 );
nogoto = ::GetSQLColumnData( query, 1 );
randspawn = ::GetSQLColumnData( query, 2 );
spawnwep = ::GetSQLColumnData( query, 3 );
wep1 = ::GetSQLColumnData( query, 4 );
wep2 = ::GetSQLColumnData( query, 5 );
wep3 = ::GetSQLColumnData( query, 6 );
wep4 = ::GetSQLColumnData( query, 7 );
wep5 = ::GetSQLColumnData( query, 8 );
wep6 = ::GetSQLColumnData( query, 9 );
wep7 = ::GetSQLColumnData( query, 10 );
wep8 = ::GetSQLColumnData( query, 11 );
wep9 = ::GetSQLColumnData( query, 12 );
wep10 = ::GetSQLColumnData( query, 13 );
spawnloc = ::GetSQLColumnData( query, 14 );
X = ::GetSQLColumnData( query, 15 );
Y = ::GetSQLColumnData( query, 16 );
Z = ::GetSQLColumnData( query, 17 );
}
::FreeSQLQuery( query );
}


function Update( player, dbGlobal ){
::QuerySQL( dbGlobal, "UPDATE Dstats SET stat = "+ stat +", nogoto = "+ nogoto +", randspawn = "+ randspawn +",spawnwep = "+ spawnwep +", wep1 = "+ wep1 +", wep2 = "+ wep2 +", wep3 = "+ wep3 +", wep4 = "+ wep4 +", wep5 = "+ wep5 +", wep6 = "+ wep6 +", wep7 = "+ wep7 +", wep8 = "+ wep8 +", wep9 = "+ wep9 +", wep10 = "+ wep10 +", spawnloc = "+ spawnloc +", X = "+ X +", Y = "+ Y +", Z = "+ Z +" WHERE Name='" + player.Name.tolower() + "'");
}

function Register( player, dbGlobal ){
::QuerySQL( dbGlobal, "INSERT INTO Dstats VALUES ( '" + player.Name.tolower() + "', false, false, false, false, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, false, 0.0, 0.0, 0.0 )" );
}
}




[DizzasTeR]

There is no such thing as Boolean in SQLite, use integer instead in which 0 presents false and 1 presents true

[Debian]

Thanks , It is working now but it is not updating when player leaves server

dstats is my array
fuction OnplayerJoin(){
   dstats[ player.ID ] = DebianStats( player.Name, sqliteDB );
}

function OnplayerSpawn(player) {
dstats[ player.ID ].Update( player, sqliteDB );
}

but it wont update

[DizzasTeR]

Show your Update function in class.

[Debian]

Code Select Expand


function Update( player, dbGlobal ){
::QuerySQL( dbGlobal, "UPDATE Dstats SET stat = "+ stat +", nogoto = "+ nogoto +", randspawn = "+ randspawn +",spawnwep = "+ spawnwep +", wep1 = "+ wep1 +", wep2 = "+ wep2 +", wep3 = "+ wep3 +", wep4 = "+ wep4 +", wep5 = "+ wep5 +", wep6 = "+ wep6 +", wep7 = "+ wep7 +", wep8 = "+ wep8 +", wep9 = "+ wep9 +", wep10 = "+ wep10 +", spawnloc = "+ spawnloc +", X = "+ X +", Y = "+ Y +", Z = "+ Z +" WHERE Name='" + player.Name.tolower() + "'");
}


[Debian]

This is my complete on player part
only stats[player.id] is getting updated

Code Select Expand


function onPlayerPart( player, reason ){
if( stats[ player.ID ].Level != 0 ){
stats[ player.ID ].Update( player, sqliteDB );
wstats[ player.ID ].Update( player, sqliteDB );
bstats[ player.ID ].Update( player, sqliteDB );
dstats[ player.ID ].Update( player, sqliteDB );

}
stats[ player.ID ] = null;
wstats[ player.ID ] = null;
bstats[ player.ID ] = null;
dstats[ player.ID ] = null;
playerson.remove( player.ID );

if(ActiveAdmins.find( player.ID ) != null) ActiveAdmins.remove( player.ID );
}


[KAKAN]

Update function is of dbGLobal and other is sqliteDB? Change it

[DizzasTeR]

Update function is of dbGLobal and other is sqliteDB? Change it

That doesn't matter, its a predefined variable in the class. To the topic, the only reason I can guess is the failure of query.

[KAKAN]

Update function is of dbGLobal and other is sqliteDB? Change it

That doesn't matter, its a predefined variable in the class. To the topic, the only reason I can guess is the failure of query.

I think it does matter, the same problem was happening to me when I was using Fuzziee account system, i changed them to sqliteDB, and they worked!

[DizzasTeR]

@KAKAN...

Code Select Expand

Stat[player.ID].Update(player, sqliteDB);

Class {

... // other stuff

function Update( player, dbGlobal )
{
//This will work.

}
}
I guess clear now.

[KAKAN]

Okay! Thanks

[Debian]

Im trying to test and in this code Accounts and Acc are working fine even Dstats is inserting and updating but not loading column data and each time a player joins dstats will start from 0. acc working fine.  you caan test it with /test

Where i made a mistake?

Create tables

Code Select Expand

CREATE TABLE Accounts ( Name VARCHAR(255), NameLower VARCHAR(255), Password VARCHAR(255), Cash INT, Bank INT, Kills INT, Deaths INT, Level INT, LastUsedIP VARCHAR(255) )

CREATE TABLE Acc( Name VARCHAR(255), NameLower VARCHAR(255), Password VARCHAR(255), Cash INT, Bank INT, Kills INT, Deaths INT, Level INT, LastUsedIP VARCHAR(255) )

CREATE TABLE Dstats( Name VARCHAR(32), stat INT, nogoto INT, randspawn INT)

Code

Code Select Expand


function onScriptLoad(){

pstats <- array( GetMaxPlayers(), null );
acc <- array( GetMaxPlayers(), null );
dstats <- array( GetMaxPlayers(), null );

sqliteDB <- ConnectSQL( "database.db" );

print( "Fuzzie's Account System v3 - SQLite variant has successfully loaded..." );
}

function onScriptUnload(){
if( sqliteDB ){
DisconnectSQL( sqliteDB );
sqliteDB = null;
}
print( "Fuzzie's Account System v3 - SQLite variant has successfully unloaded..." );
}

function onPlayerJoin( player ){
pstats[ player.ID ] = PlayerClass( player.Name, sqliteDB );
acc[ player.ID ] = Test( player.Name, sqliteDB );
dstats[ player.ID ] = DebianStats( player.Name, sqliteDB );
pstats[ player.ID ].Join( player );
}

function onPlayerPart( player, reason ){
if( pstats[ player.ID ].Level != 0 ){
pstats[ player.ID ].Update( player, sqliteDB );
acc[ player.ID ].Update( player, sqliteDB );
dstats[ player.ID ].Update( player, sqliteDB );
}
pstats[ player.ID ] = null;
acc[ player.ID ] = null;
dstats[ player.ID ] = null;
}

function onPlayerCommand( player, cmd, text ){
if( cmd == "register" ){
if( !text ){
MessagePlayer( "Syntax Error!", player );
MessagePlayer( "Correct syntax: /register <password>", player );
}
else if( pstats[ player.ID ].Logged == true ){
MessagePlayer( "You are already logged in.", player );
}
else{
pstats[ player.ID ].Register( player, text, sqliteDB );
acc[ player.ID ].Register( player, text, sqliteDB );
dstats[ player.ID ].Register( player, sqliteDB );
}
}
else if( cmd == "login" ){
if( !text ){
MessagePlayer( "Syntax Error!", player );
MessagePlayer( "Correct syntax: /login <password>", player );
}
else if( pstats[ player.ID ].Logged == true ){
MessagePlayer( "You are already logged in.", player );
}
else{
pstats[ player.ID ].Login( player, text, sqliteDB );
}
}

else if( cmd == "test" ){
acc[ player.ID ].Level++;
dstats[ player.ID ].stat++;
Message( acc[ player.ID ].Level + " "+dstats[ player.ID ].stat )
}
}


/* Fuzzie's Account System
 * Version 3
 *
 * SQLite variant
 * Revision 1
 *CREATE TABLE Accounts ( Name VARCHAR(255), NameLower VARCHAR(255), Password VARCHAR(255), Cash INT, Bank INT, Kills INT, Deaths INT, Level INT, LastUsedIP VARCHAR(255) )



 * Coded by Fuzzie
*/

class PlayerClass{
// ------------------------------------------------------------------- //

LastUsedIP = "0.0.0.0";
Cash = 0;
Bank = 0;
Kills = 0;
Deaths = 0;
Level = 0;
Logged = false;

// ------------------------------------------------------------------- //

constructor( playerName, dbGlobal ){
local query = ::QuerySQL( dbGlobal, "SELECT Cash, Bank, Kills, Deaths, Level, LastUsedIP FROM Accounts WHERE Name='" + playerName + "' AND NameLower='" + playerName.tolower() + "'" );
if( ::GetSQLColumnData( query, 5 ) ){
Cash = ::GetSQLColumnData( query, 0 );
Bank = ::GetSQLColumnData( query, 1 );
Kills = ::GetSQLColumnData( query, 2 );
Deaths = ::GetSQLColumnData( query, 3 );
Level = ::GetSQLColumnData( query, 4 );
LastUsedIP = ::GetSQLColumnData( query, 5 );
}
::FreeSQLQuery( query );
}

function Join( player ){
if( Level == 0 ){
::MessagePlayer( "Please register to play.", player );
::MessagePlayer( "Register with /register <password>", player );
}
else if( LastUsedIP == player.IP ){
Logged = true;
::MessagePlayer( "Welcome back!", player );
}
else{
::MessagePlayer( "Please login to play.", player );
::MessagePlayer( "Login with /login <password>", player );
}
}

function Update( player, dbGlobal ){
::QuerySQL( dbGlobal, "UPDATE Accounts SET Cash=" + Cash + ", Bank=" + Bank + ", Kills=" + Kills + ", Deaths=" + Deaths + ", Level=" + Level + ", LastUsedIP='" + LastUsedIP + "' WHERE Name='" + player.Name + "' AND NameLower='" + player.Name.tolower() + "'" );
}

function Register( player, password, dbGlobal ){
::QuerySQL( dbGlobal, "INSERT INTO Accounts VALUES('" + player.Name + "', '" + player.Name.tolower() + "', '" + ::SHA256( password ) + "', 0, 0, 0, 0, 1, '" + player.IP + "')" );
Level = 1;
LastUsedIP = player.IP;
Logged = true;
::MessagePlayer( "Successfully registered.", player );
::MessagePlayer( "Don't forget your password. [" + password + "]", player );
}

function Login( player, password, dbGlobal ){
local query = ::QuerySQL( dbGlobal, "SELECT Password FROM Accounts WHERE Name='" + player.Name + "' AND NameLower='" + player.Name.tolower() + "'" );
if( ::GetSQLColumnData( query, 0 ) == null ){
::MessagePlayer( "You are not registered.", player );
}
else if( ::SHA256( password ) != ::GetSQLColumnData( query, 0 ) ){
::MessagePlayer( "Wrong password.", player );
}
else{
LastUsedIP = player.IP;
Logged = true;
::MessagePlayer( "Successfully logged in.", player );
}
}
}


class Test{
// ------------------------------------------------------------------- //

LastUsedIP = "0.0.0.0";
Cash = 0;
Bank = 0;
Kills = 0;
Deaths = 0;
Level = 0;
Logged = false;

// ------------------------------------------------------------------- //

constructor( playerName, dbGlobal ){
local query = ::QuerySQL( dbGlobal, "SELECT Cash, Bank, Kills, Deaths, Level, LastUsedIP FROM Acc WHERE Name='" + playerName + "' AND NameLower='" + playerName.tolower() + "'" );
if( ::GetSQLColumnData( query, 5 ) ){
Cash = ::GetSQLColumnData( query, 0 );
Bank = ::GetSQLColumnData( query, 1 );
Kills = ::GetSQLColumnData( query, 2 );
Deaths = ::GetSQLColumnData( query, 3 );
Level = ::GetSQLColumnData( query, 4 );
LastUsedIP = ::GetSQLColumnData( query, 5 );
}
::FreeSQLQuery( query );
}

function Update( player, dbGlobal ){
::QuerySQL( dbGlobal, "UPDATE Acc SET Cash=" + Cash + ", Bank=" + Bank + ", Kills=" + Kills + ", Deaths=" + Deaths + ", Level=" + Level + ", LastUsedIP='" + LastUsedIP + "' WHERE Name='" + player.Name + "' AND NameLower='" + player.Name.tolower() + "'" );
}

function Register( player, password, dbGlobal ){
::QuerySQL( dbGlobal, "INSERT INTO Acc VALUES('" + player.Name + "', '" + player.Name.tolower() + "', '" + ::SHA256( password ) + "', 0, 0, 0, 0, 1, '" + player.IP + "')" );
}
}



class DebianStats{
// ------------------------------------------------------------------- //

stat = 0;
nogoto = 0;
randspawn = 0;


// ------------------------------------------------------------------- //

constructor( playerName, dbGlobal ){
local query = ::QuerySQL( dbGlobal, "SELECT stat, nogoto, randspawn FROM Dstats WHERE Name='" + playerName.tolower() + "'" );
if( ::GetSQLColumnData( query, 2 ) ){
stat = ::GetSQLColumnData( query, 0 );
nogoto = ::GetSQLColumnData( query, 1 );
randspawn = ::GetSQLColumnData( query, 2 );

}
::FreeSQLQuery( query );
}


function Update( player, dbGlobal ){
::QuerySQL( dbGlobal, "UPDATE Dstats SET stat = "+ stat +", nogoto = "+ nogoto +", randspawn = "+ randspawn +" WHERE Name='" + player.Name.tolower() + "'");
}

function Register( player, dbGlobal ){
::QuerySQL( dbGlobal, "INSERT INTO Dstats VALUES ( '" + player.Name.tolower() + "', 0, 0, 0 )" );
}
}