Raised This Month: $ Target: $400
 0% 

SQL Problem


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Buckshot
Senior Member
Join Date: Mar 2014
Location: Sweden
Old 04-09-2014 , 13:42   SQL Problem
Reply With Quote #1

So i'm a total noob at sqlx, just tried it out and my plugin wasn't working at all. What is wrong? It did work without sql, so obviously some failstate is triggered somewhere. I added client_prints for the errors as you can see, to figure out which part is having a problem, but i'm not getting any client_prints at all. I'm very unexperienced with SQLX, so if you find the problem, it would be very nice if you explained to me what i was doing wrong, so that it does not happen again. Thanks!
PHP Code:

#include < amxmodx >
#include < amxmisc >
#include  < sqlx >

#define VERSION "1.0"
#pragma semicolon 1

new const szPREFIX[ ] = "[AMXX]";
new 
Handle:g_SqlTuple;
new 
g_Error512 ];
new 
Host64 ],Username64 ],Password64 ],Database64 ];
new 
Table[ ] = "Reports";

public 
plugin_init()
{
    
register_plugin"Simple Player Report"VERSION"BuckShot" );
    
register_clcmd"say ""SayText" );
    
    
get_cvar_string"amx_sql_host",Host,63 );
    
get_cvar_string"amx_sql_user",Username,63 );
    
get_cvar_string"amx_sql_pass",Password,63 );
    
get_cvar_string"amx_sql_db",Database,63 );
    
    
set_task(0.1"sql_init");
}

public 
sql_init()
{
    
g_SqlTuple SQL_MakeDbTuple(Host,Username,Password,Database);
    
    new 
ErrorCode,Handle:SqlConnection SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511);
    if(
SqlConnection == Empty_Handle)
    {
        
client_print(0print_chat"Error: Line 36");
        
set_fail_state(g_Error);
    }
    
    new 
Handle:Queries[1];
    
Queries[0] = SQL_PrepareQuerySqlConnection,"CREATE TABLE IF NOT EXISTS `%s`(`id` int(7) NOT NULL auto_increment, `Time` int(22) NOT NULL default '', `Player` varchar(32) NOT NULL default '', `Reason` varchar(152) NOT NULL default '', `Authid` varchar(35) NOT NULL default '',`Ip` varchar(16) NOT NULL default '', `Player2` varchar(32) NOT NULL default '', `Ip2` varchar(16) NOT NULL default '', `Authid2` varchar(35) NOT NULL default '', PRIMARY KEY  (`id`));",Table);
    
    if(!
SQL_Execute(Queries[0]))
    {
        
client_print(0print_chat"Error: Line 45");
        
SQL_QueryError(Queries[0],g_Error,511);
        
set_fail_state(g_Error);
    }
    
    
SQL_FreeHandle(Queries[0]);
    
SQL_FreeHandle(SqlConnection);
}
public 
SayText(id)
{
    
    new 
szMsg193 ], szCmd];
    
read_argsszMsgcharsmaxszMsg ) );
    
remove_quotesszMsg );
    
strbreakszMsgszCmdcharsmaxszCmd ), szMsgcharsmax(szMsg) - charsmaxszCmd ) );
    
    if( !
equaliszCmd"/report" ) )
    {
        return;
    }
    new 
ErrorCode,Handle:SqlConnection SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511);
    if(
SqlConnection == Empty_Handle)
    {
        
client_print(idprint_chat"Error: Line 68");
        
set_fail_state(g_Error);
    }
    
    new 
szUserName32 ], szReason153 ];
    
strbreakszMsgszUserNamecharsmaxszUserName ), szReasoncharsmax(szReason) );
    
    if( 
strlenszUserName ) == )
    {
        
client_print(idprint_chat"%s Usage: /report ^"username^" ^"reason^""szPREFIX );
        return;
    }
    
    new 
iTarget cmd_targetidszUserNameCMDTARGET_ALLOW_SELF );
    
    if( !
iTarget )
    {
        
client_printidprint_chat"%s Player does not exist, or there are too many players with a similar name"szPREFIX );
        return;
    }
    
    if(
iTarget == id)
    {
        
client_printidprint_chat"%s You cannot report yourself"szPREFIX);
        return;
    }
    
    if( 
strlenszReason ) < )
    {
        
client_printidprint_chat"%s Please supply more information about the player"szPREFIX );
        return;
    }
    
    new 
TimeAndDate22 ];
    
get_time"%Y/%m/%d - %H:%M:%S"TimeAndDatecharsmaxTimeAndDate ) );  
    
    
/* Reported Player */
    
    
new szName32 ];
    
get_user_nameiTargetszNamecharsmaxszName ) );
    
    new 
szAuthid35 ];
    
get_user_authidiTargetszAuthidcharsmaxszAuthid ) );
    
    new 
szIP16 ];
    
get_user_ipiTargetszIPcharsmaxszIP ), );
    
    
/***********/
    
    /* Reporter */
    
    
new szName232 ];
    
get_user_nameidszName2charsmaxszName2 ) );
    
    new 
szAuthid235 ];
    
get_user_authididszAuthid2charsmaxszAuthid2 ) );
    
    new 
szIP235 ];
    
get_user_ipidszIP2charsmaxszIP2 ), );
    
    
/***********/
    
new Handle:Query SQL_PrepareQuery(SqlConnection"INSERT into %s (Time,Player,Reason,Authid,Ip,Player2,Ip2,Authid2) values ('%d','%s','%s','%s','%s','%s','%s','%s')"TableTimeAndDateszNameszReasonszAuthidszIPszName2szAuthid2szIP2);
    if(!
SQL_Execute(Query))
    {
        
SQL_QueryError(Query,g_Error,511);
        
client_print(idprint_chat"Error: Line 135");
        
set_fail_state(g_Error);
    }
    
    
client_printidprint_chat"%s Thank you! Your abuse report has been recieved"szPREFIX );
    
SQL_FreeHandle(Query);
    
SQL_FreeHandle(SqlConnection);
    
    
}

public 
plugin_end()
{    
    
SQL_FreeHandle(g_SqlTuple);

__________________
PM me for private work.



Last edited by Buckshot; 04-09-2014 at 15:01.
Buckshot is offline
Send a message via Skype™ to Buckshot
Black Rose
Veteran Member
Join Date: Feb 2011
Location: Stockholm, Sweden
Old 04-12-2014 , 20:02   Re: SQL Problem
Reply With Quote #2

Now. I'm terrible at SQL. But I'm getting this error while trying to run your code.
Code:
L 04/13/2014 - 01:59:18: [AMXX] Plugin ("test1.amxx") is setting itself as failed.
L 04/13/2014 - 01:59:18: [AMXX] Plugin says: Invalid default value for 'Time'
L 04/13/2014 - 01:59:18: [AMXX] Displaying debug trace (plugin "test1.amxx")
L 04/13/2014 - 01:59:18: [AMXX] Run time error 1: forced exit
L 04/13/2014 - 01:59:18: [AMXX]    [0] test1.sma::sql_init (line 45)
__________________

Last edited by Black Rose; 04-12-2014 at 20:03.
Black Rose is offline
NikKOo31
Senior Member
Join Date: May 2013
Location: Home
Old 04-12-2014 , 20:10   Re: SQL Problem
Reply With Quote #3

I don't know too much of sql but common sense

PHP Code:
Queries[0] = SQL_PrepareQuerySqlConnection,"CREATE TABLE IF NOT EXISTS `%s`(`id` int(7) NOT NULL auto_increment, `Time` int(22) NOT NULL default '', `Player` varchar(32) NOT NULL default '', `Reason` varchar(152) NOT NULL default '', `Authid` varchar(35) NOT NULL default '',`Ip` varchar(16) NOT NULL default '', `Player2` varchar(32) NOT NULL default '', `Ip2` varchar(16) NOT NULL default '', `Authid2` varchar(35) NOT NULL default '', PRIMARY KEY  (`id`));",Table); 
PHP Code:
Queries[0] = SQL_PrepareQuerySqlConnection,"CREATE TABLE IF NOT EXISTS `%s`(`id` int(7) NOT NULL auto_increment, `Time` varchar(22) NOT NULL default '', `Player` varchar(32) NOT NULL default '', `Reason` varchar(152) NOT NULL default '', `Authid` varchar(35) NOT NULL default '',`Ip` varchar(16) NOT NULL default '', `Player2` varchar(32) NOT NULL default '', `Ip2` varchar(16) NOT NULL default '', `Authid2` varchar(35) NOT NULL default '', PRIMARY KEY  (`id`));",Table); 
NikKOo31 is offline
Black Rose
Veteran Member
Join Date: Feb 2011
Location: Stockholm, Sweden
Old 04-12-2014 , 20:54   Re: SQL Problem
Reply With Quote #4

I only got that error on creation of the DB, never again.

Found the problem. You're trying to force the AuthId2 into the Ip2 column, and it won't fit.
Code:
L 04/13/2014 - 02:47:00: [AMXX] Plugin ("test1.amxx") is setting itself as failed.
L 04/13/2014 - 02:47:00: [AMXX] Plugin says: Data too long for column 'Ip2' at row 1
L 04/13/2014 - 02:47:00: [AMXX] Displaying debug trace (plugin "test1.amxx")
L 04/13/2014 - 02:47:00: [AMXX] Run time error 1: forced exit
L 04/13/2014 - 02:47:00: [AMXX]    [0] test1.sma::SayText (line 133)
EDIT: The time field is malformated too. You're sending a string(or at least the first character of the string as an integer), the db is expecting an integer.

Here's the result:
Code:
mysql> select * from reports;
+----+------+------------+---------+--------------------+--------------+------------+--------------+--------------------+
| id | Time | Player     | Reason  | Authid             | Ip           | Player2    | Ip2          | Authid2            |
+----+------+------------+---------+--------------------+--------------+------------+--------------+--------------------+
|  1 |   50 | Black Rose | Asshole | STEAM_0:1:XXXXXXXX | 192.168.0.10 | Black Rose | 192.168.0.10 | STEAM_0:1:XXXXXXXX |
+----+------+------------+---------+--------------------+--------------+------------+--------------+--------------------+
1 row in set (0.00 sec)
EDIT2: You also need to replace some characters. For example, I wrote "He's" and got a SQL syntax error.
__________________

Last edited by Black Rose; 04-12-2014 at 21:21.
Black Rose is offline
Buckshot
Senior Member
Join Date: Mar 2014
Location: Sweden
Old 04-12-2014 , 23:19   Re: SQL Problem
Reply With Quote #5

Quote:
Originally Posted by Black Rose View Post
You also need to replace some characters. For example, I wrote "He's" and got a SQL syntax error.
How do i do that?

I'll try your fixes out when i wake up, and i will come back with a response if it is working or not.. I'm too tired right now to look at a bunch of confusing code. Hopefully it works correctly. SQL confuses me alot.. I appreciate you actually testing the plugin and using your time to help me, and not just giving me codes that might, or might not work.
__________________
PM me for private work.



Last edited by Buckshot; 04-12-2014 at 23:39.
Buckshot is offline
Send a message via Skype™ to Buckshot
Black Rose
Veteran Member
Join Date: Feb 2011
Location: Stockholm, Sweden
Old 04-12-2014 , 23:56   Re: SQL Problem
Reply With Quote #6

For example
Code:
replace_all(text, charsmax(text), "'", "");
to remove them all or
Code:
replace_all(text, charsmax(text), "'", "&#39 ;"); // Without the space. The forum replaces it with '.
to be able to replace them in your other script.
It all depends if you want to show it in the end. It will however add to the size of the message field.
__________________

Last edited by Black Rose; 04-13-2014 at 07:47.
Black Rose is offline
Buckshot
Senior Member
Join Date: Mar 2014
Location: Sweden
Old 04-13-2014 , 13:22   Re: SQL Problem
Reply With Quote #7

Heres how it looks like right now. Replacing characters etc will be done later, i just want it to be able to write to the database first, which i havent gotten it to do even once..

PHP Code:
#include < amxmodx >
#include < amxmisc >
#include  < sqlx >

#define VERSION "1.0"
#pragma semicolon 1

new const szPREFIX[ ] = "[AMXX]";
new 
Handle:g_SqlTuple;
new 
g_Error512 ];
new 
Host64 ],Username64 ],Password64 ],Database64 ];
new 
Table[ ] = "Reports";


public 
plugin_init()
{
    
register_plugin"Simple Player Report"VERSION"BuckShot" );
    
register_clcmd"say ""SayText" );

    
get_cvar_string"amx_sql_host",Host,63 );
    
get_cvar_string"amx_sql_user",Username,63 );
    
get_cvar_string"amx_sql_pass",Password,63 );
    
get_cvar_string"amx_sql_db",Database,63 );

    
set_task(0.1"sql_init");
}

public 
sql_init()
{
    
g_SqlTuple SQL_MakeDbTupleHost,Username,Password,Database );
    
    new 
ErrorCode,Handle:SqlConnection SQL_Connectg_SqlTuple,ErrorCode,g_Error,511 );
    if( 
SqlConnection == Empty_Handle )
    {
        
set_fail_stateg_Error );
    }
    new 
Handle:Queries];
    
Queries] = SQL_PrepareQuerySqlConnection,"CREATE TABLE IF NOT EXISTS `%s`(`id` int(7) NOT NULL auto_increment, `Time` varchar(22) NOT NULL default '', `Player` varchar(32) NOT NULL default '', `Reason` varchar(152) NOT NULL default '', `Authid` varchar(35) NOT NULL default '',`Ip` varchar(16) NOT NULL default '', `Player2` varchar(32) NOT NULL default '', `Authid2` varchar(16) NOT NULL default '', `Ip2` varchar(35) NOT NULL default '', PRIMARY KEY  (`id`));"Table );
    
    if( !
SQL_ExecuteQueries] ) )
    {
        
SQL_QueryErrorQueries],g_Error,511 );
        
set_fail_stateg_Error );
    }
    
    
SQL_FreeHandleQueries] );
    
SQL_FreeHandleSqlConnection );
}
public 
SayTextid )
{
    
    new 
szMsg193 ], szCmd];
    
read_argsszMsgcharsmaxszMsg ) );
    
remove_quotesszMsg );
    
strbreakszMsgszCmdcharsmaxszCmd ), szMsgcharsmax(szMsg) - charsmaxszCmd ) );
    
    if( !
equaliszCmd"/report" ) )
    {
        return;
    }
    new 
ErrorCode,Handle:SqlConnection SQL_Connectg_SqlTuple,ErrorCode,g_Error,511 );
    if( 
SqlConnection == Empty_Handle )
    {
        
set_fail_stateg_Error );
    }
    
    new 
szUserName32 ], szReason153 ];
    
strbreakszMsgszUserNamecharsmaxszUserName ), szReasoncharsmax(szReason) );

    if( 
strlenszUserName ) == )
    {
        
client_print(idprint_chat"%s Usage: /report ^"username^" ^"reason^""szPREFIX );
        return;
    }
    
    new 
iTarget cmd_targetidszUserNameCMDTARGET_ALLOW_SELF );
    
    if( !
iTarget )
    {
        
client_printidprint_chat"%s Player does not exist, or there are too many players with a similar name"szPREFIX );
        return;
    }
    
    if(
iTarget == id)
    {
        
client_printidprint_chat"%s You cannot report yourself"szPREFIX);
        return;
    }
    
    if( 
strlenszReason ) < )
    {
        
client_printidprint_chat"%s Please supply more information about the player"szPREFIX );
        return;
    }
    
    new 
szTime22 ];
    
get_time"%Y/%m/%d - %H:%M:%S"szTimecharsmaxszTime ) );
    
    
/* Reported Player */
    
    
new szName32 ];
    
get_user_nameiTargetszNamecharsmaxszName ) );
    
    new 
szAuthid35 ];
    
get_user_authidiTargetszAuthidcharsmaxszAuthid ) );
    
    new 
szIP16 ];
    
get_user_ipiTargetszIPcharsmaxszIP ), );
    
    
/***********/
    
    /* Reporter */
    
    
new szName232 ];
    
get_user_nameidszName2charsmaxszName2 ) );
    
    new 
szAuthid235 ];
    
get_user_authididszAuthid2charsmaxszAuthid2 ) );
    
    new 
szIP235 ];
    
get_user_ipidszIP2charsmaxszIP2 ), );
    
    
/***********/
    
new Handle:Query SQL_PrepareQuery(SqlConnection"INSERT into %s (Time,Player,Reason,Authid,Ip,Player2,Authid2,Ip2) values ('%s','%s','%s','%s','%s','%s','%s','%s')"TableszTimeszNameszReasonszAuthidszIPszName2szAuthid2szIP2);
    if( !
SQL_ExecuteQuery ) )
    {
        
SQL_QueryErrorQuery,g_Error,511 );
        
set_fail_stateg_Error );
    }
    
    
client_printidprint_chat"%s Thank you! Your abuse report has been recieved"szPREFIX );
    
SQL_FreeHandleQuery );
    
SQL_FreeHandleSqlConnection );
    
    
}

public 
plugin_end()
{    
    
SQL_FreeHandleg_SqlTuple );

I'm getting errors when connecting though. That's strange because the login details are correct, and my server is open to the public, and not LAN. It has internet access..

Code:
L 04/13/2014 - 19:25:10: [AMXX] Plugin ("test.amxx") is setting itself as failed.
L 04/13/2014 - 19:25:10: [AMXX] Plugin says: Can't connect to MySQL server on 'mysql11.000webhost.com' (10060)
L 04/13/2014 - 19:25:10: [AMXX] Displaying debug trace (plugin "test.amxx")
L 04/13/2014 - 19:25:10: [AMXX] Run time error 1: forced exit 
L 04/13/2014 - 19:25:10: [AMXX]    [0] test.sma::sql_init (line 34)
__________________
PM me for private work.



Last edited by Buckshot; 04-13-2014 at 13:30.
Buckshot is offline
Send a message via Skype™ to Buckshot
Black Rose
Veteran Member
Join Date: Feb 2011
Location: Stockholm, Sweden
Old 04-13-2014 , 14:01   Re: SQL Problem
Reply With Quote #8

I doubt 000webhost.com is open for remote connections.
If you want I can set up an account on my connection that you can use temporarily.

It works apart from you shifting around the Ip2 and AuthId2 so the sizes are wrong. Ip2 is too big and AuthId2 is too small.

Suggested values (assuming null is not required)
Time: varchar(21)
Name: varchar(31)
Authid: varchar(24) should be enough and then some.
Ip: varchar(15) or like this
__________________

Last edited by Black Rose; 04-13-2014 at 14:01.
Black Rose is offline
Buckshot
Senior Member
Join Date: Mar 2014
Location: Sweden
Old 04-13-2014 , 15:36   Re: SQL Problem
Reply With Quote #9

Quote:
Originally Posted by Black Rose View Post
I doubt 000webhost.com is open for remote connections.
If you want I can set up an account on my connection that you can use temporarily.

It works apart from you shifting around the Ip2 and AuthId2 so the sizes are wrong. Ip2 is too big and AuthId2 is too small.

Suggested values (assuming null is not required)
Time: varchar(21)
Name: varchar(31)
Authid: varchar(24) should be enough and then some.
Ip: varchar(15) or like this
Oh okay, Yeah sure i'd just need a working sql host while testing my plugins. I'll buy one when i'm done with them all, and they are ready to use. Just pm me with the details I'm trying to develop a "surveillance system" for easy access on a website's admin panel. Basically an admin will be able to login on a website, and easily see chatlogs, reports, and a bunch of information without actually having to enter the server. That's what all this is for.

edit: Can't be too hard to setup a sql server on my own computer.. Guess it just involves some portforwarding and stuff.. I guess i could make my own server later for testing.. But if i could borrow yours for now, that'd be nice
__________________
PM me for private work.



Last edited by Buckshot; 04-13-2014 at 15:51.
Buckshot is offline
Send a message via Skype™ to Buckshot
Reply



Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 17:54.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode