Forums Scripts & Popups MySQL

Any ideas how I could access a MySQL database within MIRC? I would like to display some news and other information on user demand.

I believe you need mysql.dll for that. Which can be obtained here.

Thx, works.

You're welcome.

As the following will show, you don't have to use an external DLL to grab data from a MySQL database. Granted, the example below is very simplified from what you might want to do, but it will show that it is entirely possible, and even very feasible, to work with MySQL directly from within mIRC. I have done it from the mIRC command line.

Assuming you have MySQL installed properly (if you don't, get MySQL and OLEDB provider), I will show all the steps required to make this all work.

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

/run cmd

E:\mIRC\> [color:#840017]C:

C:\> cd mysql\bin

C:\mysql\bin\> mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28 to server version: 4.0.23-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE myDatabaseName;
Query OK, 1 row affected (0.01 sec)

mysql> USE myDatabaseName;
Database changed

mysql> GRANT ALL ON myDatabaseName.*
- - - > TO myUsername[/b]@[/b]localhost
- - - > IDENTIFIED BY 'myPassword';
Query OK, 0 rows affected (0.15 sec)

mysql> CREATE TABLE Nicks (
- - - > NickID int unsigned not null auto_increment primary key,
- - - > Nickname varchar(30) not null default 'NoNick',
- - - > FirstName varchar(50) default ' ',
- - - > LastName varchar(50) default ' ',
- - - > Gender varchar(1) default 'M'
- - - > );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO Nicks (Nickname, FirstName, LastName, Gender)
- - - > VALUES ('JDoe', 'John', 'Doe', 'M');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO Nicks (Nickname, FirstName, Gender)
- - - > VALUES('AussieGal', 'Shiela', 'F');
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye

C:\mysql\bin> exit[/color]

[color:000066]2. USING mIRC TO ACCESS THE MySQL DATABASE[/color]

; This is the quick and dirty version - no error trapping, input validation - nothing.
;
alias MySQL.GetNickData {

• [/color]; Open an ADODB Connection object.
  ;
  .comopen MySQL.Database ADODB.Connection
  
  ; For the purposes of this example, I have shortened the Open method declaration by defining a variable
  ; to hold a proper MySQL connection string. Again, if this step does not work for you, then you will need
  ; to install the OLEDB driver from here (picking the closest mirror).
  ;
  var %Connection.String = Provider=MySQLProv;Data Source=myDatabaseName;User ID=myUsername;Password=myPassword
  
  ; Open the connection to the MySQL database you wish to work with, specified in the Data Source
  ; parameter above.
  ;
  .echo -q $com(MySQL.Database, Open, 1, *bstr, %Connection.String)
  
  ; Open an ADODB Recordset object.
  ;
  .comopen RecordsetObject ADODB.Recordset
  
  ; Again, to shorten the Open method line, I have put the SQL I want to use to populate the recordset
  ; inside a variable.
  ;
  var %SQL = SELECT FirstName, LastName, Gender FROM Nicks WHERE Nickname = ' $+ $1 $+ ';
  
  ; Open the recordset object using the previously setup SQL variable on the Connection object.
  ;
  .echo -q $com(RecordsetObject, Open, 1, *bstr, %SQL, dispatch, MySQL.Database, int, 1, int, 3, int 1)
  
  ; The Collect property is an undocumented shortcut way to grab just the value held in the fields
  ; which are zero-based. As you can see from the SQL above, I have specified 3 fields I wish returned:
  ;
  ; * FirstName (field 0)
  ; * LastName (field 1)
  ; * Gender (field 2)
  ;
  ; So, I grab the data from the FirstName field and store the result in the variable %Name.
  ;
  .echo -q $com(RecordsetObject, Collect, 2, i4, 0)
  var %Name = $com(RecordsetObject).result
  
  ; Then I grab the data from the LastName field and add it to the %Name variable.
  ;
  .echo -q $com(RecordsetObject, Collect, 2, i4, 1)
  var %Name = %Name $com(RecordsetObject).result
  
  ; Then I grab the Gender data and put it in its own variable, surrounded by parentheses.
  ;
  .echo -q $com(RecordsetObject, Collect, 2, i4, 2)
  var %Gender = ( $+ $com(RecordsetObject).result $+ )
  
  ; Now I can return or display the results from the database query to the active window.
  ;
  if ($isid) return %Name %Gender
  else echo -gaitc info * $1 is %Name %Gender
  
  ; And finally, (being the neat little programmer that I am) I close the two objects I have used.
  ;
  .comclose RecordsetObject
  .comclose MySQL.Database

}

/MySQL.GetNickData JDoe
5•[color:#6F5B65]15:21:17[/color] * JDoe is John Doe (M)

Haven't seen you around in a while, it's good to see you're back

I pop in from time to time.

Post deleted by Tr0n

Hmm, everytime I run the alias the MyOLEDB Sample Database Creator window shows up. Any ideas how I could disable it?

MySQL Server v4.0
MyOLEDB v3.0

Strange. I had to download the MyOLE provider from the "official" site.

http://old.sw.com.sg/products/myoledb/form/

Now it works. Thx.

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!

Very impressive Hammer, and well explained as always.

After my exams, I'll definitely read up on ADO, it's been itching for a while, remember

Cya

an impressive piece of scripting...yet the dll option still seams simpler...

IIRC the dll also allows access to remote databases

As does ADO. All you have to do is setup your connection string to use a Server=12.34.56.78;Port=3306; which will tell ADO which server and port to go to to connect to the correct database, instead of using localhost.

var %Connection.String = $+( $&

• DRIVER={MySQL ODBC 3.51 Driver};, $&
  SERVER=MySQL.myDomain.com;, $&
  PORT=3306;, $&
  DATABASE=myDatabase;, $&
  USER=myUsername;, $&
  PASSWORD=myPassword;, $&
  OPTION=3;, $&
  )

This method's main advantage, to my way of thinking, is that you don't need to download any dlls which may or may not have "extra" code you might not need or want (or can't get rid of after running the dll). This means one (or more) less file(s) to bundle with your distributed scripts and get setup properly.

great.... i like this script...

but i cant connect to my mysql server (at datacenter).
** but i can connect by using MySQLControlCenter...

here the script :-
alias MySQL.get.login {
.comopen MySQL.Database ADODB.Connection

;==========================
;if i'm use this... i cant connect...
var %Connection.String = $+( $&
DRIVER={MySQL ODBC 3.51 Driver};, $&
SERVER=localhost;, $&
PORT=3306;, $&
DATABASE=mydb;, $&
USER=root;, $&
PASSWORD=pass;, $&
OPTION=3;, $&
)

;==================end========


;===================
;but if i change it to :-

var %Connection.String = Provider=MySQLProv;Data Source=mydb;Server=localhost;User ID=root;Password=pass
;=================end=======
.echo -q aa $com(MySQL.Database, Open, 1, *bstr, %Connection.String)
.comopen RecordsetObject ADODB.Recordset
var %SQL = SELECT account_id FROM login WHERE userid = ' $+ $1 $+ ';
.echo -q $com(RecordsetObject, Open, 1, *bstr, %SQL, dispatch, MySQL.Database)
.echo -q $com(RecordsetObject, Collect, 2, i4, 0)
var %Name = $com(RecordsetObject).result
.echo -q $com(RecordsetObject, Collect, 2, i4, 1)
var %Name = %Name $com(RecordsetObject).result
if ($isid) return %Name %Gender
else {
echo -gaitc info * $1 is %Name
.comclose RecordsetObject
.comclose MySQL.Database
.hadd -m tmp $1 %Name
}
}



any idea?

Notice that you have left Server=localhost, which means that you are trying to connect to a MySQL installation hosted on your own computer, instead of changing that to the IP/hostname of the datacenter server.

Server=12.34.56.78;

or

Server=MySQL.myDomain.com;

i mean, if i use :-

a) var %Connection.String = Provider=MySQLProv;Data Source=mydb;Server=localhost;User ID=root;Password=passwd

i can connect to my mysql server (on datacenter or on localhost). but "MySQL Data Source Name Setup" dialog will appear everytime i'm use (a)

but, the dialog not appear when i'm use (b)
b) var %Connection.String = $+( $&
DRIVER={MySQL ODBC 3.51 Driver};, $&
SERVER=localhost;, $&
PORT=3306;, $&
DATABASE=mydb;, $&
USER=root;, $&
PASSWORD=passwd;, $&
OPTION=3;, $&
)

but the problem is, i cant get any items out.

After you use b), try this:

//echo -ati * State: $com(MySQL.Database, State, 2) => $iif($com(MySQL.Database).result == 0,Closed, Open)

to see if the MySQL.Database object is actually connected or not to the correct server, datacenter or localhost.

its closed.
any idea?

this is the result * State: 1 => Closed

That means you have not connected to MySQL, and that means that your connection string is still wrong.