Forums Scripts & Popups MySQL

Just for fun, I did a little bit more playing around with this to see if I could create the database from mIRC without needing to go into the mysql command-line utility. Assuming that everything is properly installed, as I described earlier, then you can use an ADODB.Connection object with an ADODB.Command object.

To use the Command object, you first create an instance of a Connection object and open it to MySQL (using the mysql database - which I switch to the test database, just because I can). Next, you create an instance of a Command object and set its .ActiveConnection property to the Connection object you just created. Now the Command object is ready for use to send commands to MySQL, nearly as if you were using the mysql command-line utility.



[color:000066]1. CREATING THE DATABASE/TABLE AND ADDING DATA TO IT[/color] (Revisted)

alias MySQL.Create.Database {

• ; Define some local variables at the top to make their values easy to maintain:
  ;
  ; %Server will almost always be "localhost".
  ; %UID will be either "root" or some other user you have defined that can create databases.
  ; %Password is self-explanatory.
  ;
  ; %InitialDB should probably be set to "test" unless you have removed that database.
  ;
  ; %Success.Color is what color the line echos in if everything is going well.
  ; %Failure.Color is what color the line echos in if an operation failed.
  ;
  var %Server = localhost
  var %UID = root
  var %Password = rootpass
  
  var %InitialDB = test
  
  var %Success.Color = 3
  var %Failure.Color = 4
  
  ; Define four more local variables that the script will use:
  ;
  ; %SQL is used to store the SQL strings, so commas are not an issue inside the $com()
  ; %DB is the name of the database connection
  ; %DB_SWITCH is used to capture the results of the $mysql_select_db()
  ; %CMD is the name of the command object
  ;
  var %SQL, %DB, %DB_SWITCH, %CMD
  
  ; Open a connection to the main MySQL database - "mysql"
  ;
  %DB = $mysql_connect(%Server, %UID, %Password)
  
  if (!%DB) { echo %Failure.Color -a * Couldn't connect to MySQL. | halt }
  else { echo %Success.Color -a * Opened MySQL connection. }
  
  ; Switch to a different database. This step isn't really necessary, but since I was borrowing lots of things
  ; from PHP+MySQL anyway, I decided to complete the picture by providing a way to select an initial database.
  ; From this example script's standpoint, this step is unnecessary because we're not going to use the
  ; test database we're switching to, instead creating another brand new database and switching to that
  ; immediately.
  ;
  %DB_Switch = $mysql_select_db(%InitialDB, %DB)
  
  if (!%DB_Switch) { echo %Failure.Color -a * Couldn't switch to the $upper(%InitialDB) database. | .comclose %DB | halt }
  else { echo %Success.Color -a * Using the %InitialDB database. }
  
  ; Open a command object to send SQL commands through.
  ;
  %CMD = $mysql_open_command(%DB)
  
  if (!%CMD) { echo %Failure.Color -a * Couldn't open an ADODB Command object; aborting. | .comclose %DB | halt }
  else { echo %Success.Color -a * Command object opened and ready for use. }
  
  ; Starting from here, I am going to use the return code to decide which color to echo in. I have left it
  ; in this format so that you can change the color at the top.
  ;
  ; Drop the database "myDatabaseName" if it exists; we're going to redefine it.
  ;
  %SQL = DROP DATABASE IF EXISTS `myDatabaseName`;
  echo $iif($mysql_command(%SQL, %CMD),%Success.Color,%Failure.Color) -a * %SQL
  
  ; Now recreate the database - no tables in it so far.
  ;
  %SQL = CREATE DATABASE `myDatabaseName`;
  echo $iif($mysql_command(%SQL, %CMD),%Success.Color,%Failure.Color) -a * %SQL
  
  ; Switch over to the new database by command.
  ;
  %SQL = USE `myDatabaseName`;
  echo $iif($mysql_command(%SQL, %CMD),%Success.Color,%Failure.Color) -a * %SQL
  
  ; Create a user called "myUsername" on "localhost" with full privileges to
  ; the myDatabaseName tables; password is "MyPassword".
  ;
  %SQL = GRANT ALL PRIVILEGES $&
  ON `myDatabaseName`.`*` $&
  TO `myUsername`@`localhost` $&
  IDENTIFIED BY 'MyPassword';
  echo $iif($mysql_command(%SQL, %CMD),%Success.Color,%Failure.Color) -a * %SQL
  
  ; Create a table called "Nicks", with 5 fields:
  ;
  ; NickID (primary key)
  ; Nickname
  ; FirstName
  ; LastName
  ; Gender
  ;
  %SQL = CREATE TABLE `Nicks` ( $&
  `NickID` int(10) unsigned NOT NULL auto_increment, $&
  `Nickname` varchar(30) NOT NULL default 'NoNick', $&
  `FirstName` varchar(50) default '', $&
  `LastName` varchar(50) default '', $&
  `Gender` varchar(1) default 'M', $&
  PRIMARY KEY (`NickID`));
  echo $iif($mysql_command(%SQL, %CMD),%Success.Color,%Failure.Color) -a * %SQL
  
  ; Add a couple of rows of data to the new table.
  ;
  %SQL = INSERT $&
  INTO `Nicks` (`Nickname`, `FirstName`, `LastName`, `Gender`) $&
  VALUES $&
  ('JDoe', 'John', 'Doe', 'M'), $&
  ('AussieGal', 'Shiela', '', 'F');
  echo $iif($mysql_command(%SQL, %CMD),%Success.Color,%Failure.Color) -a * %SQL
  
  ; Clean up and report on each step's status.
  ;
  if ($com(%CMD)) .comclose %CMD
  echo $iif($com(%CMD),%Failure.Color -a * Command object failed to close: %CMD,%Success.Color -a * Command object closed.)
  
  if ($com(%DB)) .comclose %DB
  echo $iif($com(%DB),%Failure.Color -a * Database failed to close: %DB,%Success.Color -a * Database closed.)

}

; MYSQL_CONNECT
;
; This alias "copies" the format PHP uses to connect to a database.
;
; Usage: $mysql_connect(localhost, root, rootpass)
; Returns: a Connection object name.
;
alias -l mysql_connect {

• ; Create a dynamically-named Connection object and open it.
  ;
  var %Connection.Object = $+(MySQL.Database.,$ticks)
  
  .comopen %Connection.Object ADODB.Connection
  
  ; Set up the connection string using the provided arguments.
  ;
  var %Connection.String = $+( $&
  DRIVER={MySQL ODBC 3.51 Driver};Port=3306;Option=16384;Stmt=;Database=mysql;, $&
  Server=,$$1,;Uid=,$$2,;Pwd=,$$3)
  
  ; Try to open the connection to the database. Close the Connection object on failure.
  ;
  if ($com(%Connection.Object, Open, 1, *bstr, %Connection.String)) return %Connection.Object
  else { .comclose %Connection.Object }

}

; MYSQL_OPEN_COMMAND
;
; This alias "copies" the format PHP uses, though this one just opens a Command object.
;
; Usage: $mysql_open_command(%DB)
; Returns: a Command object name.
;
alias -l mysql_open_command {

• ; Create a dynamically-named Command object and open it.
  ;
  var %Command.Object = $+(MySQL.Command.,$ticks)
  
  .comopen %Command.Object ADODB.Command
  
  ; Try to set the ActiveConnection parameter to the supplied $db reference. Close the
  ; Command object on failure.
  ;
  if ($com(%Command.Object, ActiveConnection, 4, dispatch, $$1)) return %Command.Object
  else { .comclose %Command.Object }

}

; MYSQL_SELECT_DB
;
; This alias "copies" the format PHP uses to select a database.
;
; Usage: $mysql_select_db(myDatabaseName, %DB)
; Returns: 0 or 1 (failure or success)
;
alias -l mysql_select_db {

• return $com($$2, Execute, 1, *bstr, USE $$1)

}

; MYSQL_COMMAND
;
; This alias "copies" the format PHP uses, modified for our use in just sending a single
; command through the Command object to the database.
;
; Usage: $mysql_command(%SQL, %CMD)
; Returns: 0 or 1 (failure or success)
;
alias -l mysql_command {

• ; Set the .CommandText property to the SQL string provided in $1.
  ;
  .echo -q $com($$2, CommandText, 4, *bstr, $$1)
  
  ; Return the success code from the operation.
  ;
  return $com($$2, Execute, 1)

}

; MYSQL_QUERY
;
; This alias "copies" the format PHP uses to send a query to the database.
; Use this command when you want the return to be a 0 or 1 (failure or success)
; and the name of the dynamically-named recordset object that is returned,
; if any.
;
; NOTE: This alias is provided, though unused in this example script.
;
; Usage: $mysql_query(%SQL, %DB)
; Returns: 0 or 1 ADODB.Recordset.$ticks
;
alias -l mysql_query {

• ; Create a dynamically-named Recordset object, but don't open it; the query will do that
  ; if it needs to.
  ;
  var %Recordset.Object = ADODB.Recordset. $+ $ticks
  
  ; Capture the success (or failure) to return later. We do this so we can check the
  ; .dispatch property to see if we should be returning the name of the Recordset
  ; object or not.
  ;
  var %Success.Code = $com($$2, Execute, 1, *bstr, $$1, *dispatch %Recordset.Object)
  
  ; If there was a returned Recordset, return the success code (will be 1) and the
  ; name of the Recordset object to call it with. To be parsed in the calling script.
  ; If there was no dispatch, just return the success code.
  ;
  if ($com($$2).dispatch) { return %Success.Code %Recordset.Object }
  else { return %Success.Code }

}
__________________________________________________________

/MySQL.Create.Database

* Opened MySQL connection.
* Using the test database.
* Command object opened and ready for use.
* DROP DATABASE IF EXISTS `myDatabaseName`;
* CREATE DATABASE `myDatabaseName`;
* USE `myDatabaseName`;
* GRANT ALL PRIVILEGES ON `myDatabaseName`.`*` TO `myUsername`@`localhost` IDENTIFIED BY 'MyPassword';
* CREATE TABLE `Nicks` ( `NickID` int(10) unsigned NOT NULL auto_increment, `Nickname` varchar(30) NOT NULL default 'NoNick', `FirstName` varchar(50) default '', `LastName` varchar(50) default '', `Gender` varchar(1) default 'M', PRIMARY KEY (`NickID`));
* INSERT INTO `Nicks` (`Nickname`, `FirstName`, `LastName`, `Gender`) VALUES ('JDoe', 'John', 'Doe', 'M'), ('AussieGal', 'Shiela', '', 'F');
* Command object closed.
* Database closed.
__________________________________________________________

mIRC + $com + MySQL = Fun stuff!