Automatically adding tables/fields in MySQL database.

Automatically adding tables/fields in MySQL database. - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Help Archive (https://sampforum.blast.hk/forumdisplay.php?fid=89)
+---- Thread: Automatically adding tables/fields in MySQL database. (/showthread.php?tid=275235)

Automatically adding tables/fields in MySQL database. - Scenario - 08.08.2011

I know that in some MySQL scripts, the authors have made them so tables/fields are created automatically if they don't exist to begin with. I was thinking, rather than having the SQL code in the script, save it in a file. I am trying to use the basic file-functions to load the file, copy the information into a string, and execute the string's contents via MySQL. I am getting errors however.

Is this possible to do?

Re: Automatically adding tables/fields in MySQL database. - Grim_ - 08.08.2011

Why would you need to do so in the first place? Why do you decide to execute them via a file rather than the script itself, as it'd just make it less efficient from having to parse the data from the file. The only advantage I can see is not having to re-compile the script every time you want to add a new line, but that takes mere seconds.

Either way, what errors are you receiving?

Re: Automatically adding tables/fields in MySQL database. - Scenario - 08.08.2011

Quote:

Originally Posted by Grim_

Why would you need to do so in the first place? Why do you decide to execute them via a file rather than the script itself, as it'd just make it less efficient from having to parse the data from the file. The only advantage I can see is not having to re-compile the script every time you want to add a new line, but that takes mere seconds.

Either way, what errors are you receiving?

I guess that's true... I would still like to know how to get this working though.

This is the information in the file I am attempting to load:

Код:

CREATE TABLE IF NOT EXISTS `Accounts` (
  `Username` varchar(25) NOT NULL,
  `Password` varchar(129) NOT NULL,
  `aLevel` int(11) NOT NULL,
  `aPassword` varchar(129) NOT NULL,
  `Money` int(11) NOT NULL,
  `Score` int(11) NOT NULL,
  UNIQUE KEY `Username` (`Username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `Ban Logs` (
  `Log ID` int(11) NOT NULL auto_increment,
  `Administrator` varchar(25) NOT NULL,
  `Player` varchar(25) NOT NULL,
  `Reason` varchar(150) NOT NULL,
  `Length` varchar(128) NOT NULL,
  PRIMARY KEY  (`Log ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `CMD Logs` (
  `Log ID` int(11) NOT NULL auto_increment,
  `Player` varchar(25) NOT NULL,
  `cmdtext` varchar(300) NOT NULL,
  PRIMARY KEY  (`Log ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS `Kick Logs` (
  `Log ID` int(11) NOT NULL auto_increment,
  `Administrator` varchar(25) NOT NULL,
  `Player` varchar(25) NOT NULL,
  `Reason` varchar(150) NOT NULL,
  PRIMARY KEY  (`Log ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

This is the error from the "Debug" file. I am using BlueG's MySQL plugin by the way...

Код:

[17:17:33] CMySQLHandler::Query(CREATE TABLE IF NOT EXISTS `Accounts` (

) - An error has occured. (Error ID: 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 '' at line 1)

[17:17:33] OnQueryError() - Called.

Re: Automatically adding tables/fields in MySQL database. - Grim_ - 08.08.2011

There's nothing wrong with the SQL syntax inside the file, so something is screwing up when loading the data or calling it to the query. Please show that code.

Re: Automatically adding tables/fields in MySQL database. - Scenario - 08.08.2011

Quote:

Originally Posted by Grim_

There's nothing wrong with the SQL syntax inside the file, so something is screwing up when loading the data or calling it to the query. Please show that code.

Sure thing:

pawn Код:

stock CreateSQLTables()
{
    new szQuery[2000];
    new File:sqltables = fopen("sql.txt", io_read);
    fread(sqltables, szQuery);
    fclose(sqltables);
    
    mysql_query(szQuery);
}

That function is called under OnGameModeInit().

Re: Automatically adding tables/fields in MySQL database. - Grim_ - 08.08.2011

fread only reads one line of the file at a time. You'll need to read the whole file, then execute the query.

pawn Код:

stock CreateSQLTables( )
{
   new szQuery[ 2000 ];
   new szLine[ 128 ];
   new File: sqltables = fopen( "sql.txt", io_read );
   while( fread( sqltables, szLine ) )
   {
      strcat( szQuery, szLine ); 
      // You may need to add \n to the end of the line due to syntax
   }
   
   fclose( sqltables );
   mysql_query( szQuery );
}

Re: Automatically adding tables/fields in MySQL database. - Scenario - 08.08.2011

Quote:

Originally Posted by Grim_

fread only reads one line of the file at a time. You'll need to read the whole file, then execute the query.

pawn Код:

stock CreateSQLTables( )
{
   new szQuery[ 2000 ];
   new szLine[ 128 ];
   new File: sqltables = fopen( "sql.txt", io_read );
   while( fread( sqltables, szLine ) )
   {
      strcat( szQuery, szLine ); 
      // You may need to add \n to the end of the line due to syntax
   }
   
   fclose( sqltables );
   mysql_query( szQuery );
}

Oh! I thought fread() reads the whole file, without dealing with particular lines or whatever. Thank you! Oh and by the way, how would someone go about using the queries in the file posted above inside the script? I never really attempted doing this and cba. to check the code of another script.

Re: Automatically adding tables/fields in MySQL database. - Grim_ - 08.08.2011

Simple, just run the query/queries inside OnGameModeInit.

pawn Код:

mysql_query( "CREATE TABLE IF NOT EXISTS ..." );
// Cram them all into one query, or separate them by tables

Re: Automatically adding tables/fields in MySQL database. - Scenario - 08.08.2011

I figured as much!

I'll do it that way instead, it's more efficient anyways. Plus, with threaded queries it won't cause any lag!

Final code:

pawn Код:

stock CreateSQLTables()
{
    mysql_query("CREATE TABLE IF NOT EXISTS `Accounts` ( \
    `Username` varchar(25) NOT NULL, \
    `Password` varchar(129) NOT NULL, \
    `aLevel` int(11) NOT NULL, \
    `aPassword` varchar(129) NOT NULL, \
    `Money` int(11) NOT NULL, \
    `Score` int(11) NOT NULL, \
    UNIQUE KEY `Username` (`Username`) \
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;", qNormal);

    mysql_query("CREATE TABLE IF NOT EXISTS `Ban Logs` ( \
    `Log ID` int(11) NOT NULL auto_increment, \
    `Administrator` varchar(25) NOT NULL, \
    `Player` varchar(25) NOT NULL, \
    `Reason` varchar(150) NOT NULL, \
    `Length` varchar(128) NOT NULL, \
    PRIMARY KEY  (`Log ID`) \
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;", qNormal);

    mysql_query("CREATE TABLE IF NOT EXISTS `CMD Logs` ( \
    `Log ID` int(11) NOT NULL auto_increment, \
    `Player` varchar(25) NOT NULL, \
    `cmdtext` varchar(300) NOT NULL, \
    PRIMARY KEY  (`Log ID`) \
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;", qNormal);

    mysql_query("CREATE TABLE IF NOT EXISTS `Kick Logs` ( \
    `Log ID` int(11) NOT NULL auto_increment, \
    `Administrator` varchar(25) NOT NULL, \
    `Player` varchar(25) NOT NULL, \
    `Reason` varchar(150) NOT NULL, \
    PRIMARY KEY  (`Log ID`) \
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;", qNormal);
}