MySQL - mIRC Discussion Forums

mIRC Homepage

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.

Hammer: that's some pretty impressive code, I like the mysql.dll style much better though (easier :P), but I am probably forced to use this as my mIRC crashes with the dll's

Quote:

Faulting application mirc.exe, version 6.1.6.0, faulting module ntdll.dll, version 5.1.2600.2180, fault address 0x00011404.

I've tried like 4 diff mysql installations and about 3 versions of the mysql.dll/lib_mysql.dll for mIRC.
It doesn't immediately crash, just after 5-30 minutes... also when scripts do a lot MYSQL commands.
Any idea(s)?

Also, does the com connection to the database stay alive till you close it or does it close after some time like with the dll's.

Thanks in advance,

Remco

Sorry, I have no idea about the dlls .. I don't use any dlls at all.

As for your other question, your $com object stays open until you comclose it (or you close mIRC). This means that if you're going to be hitting the database with any regularity whatsoever, it's best to leave it open until you don't need it anymore, then close it (and any other objects you've created that use it). If you wanted to, you could open the connection on START and close it on EXIT. Creating the objects and getting everything connected is fairly slow, whereas using them once they are open/connected is not anywhere near as slow.

In one of your examples you used the com thingy "Collect" that collected 1 field is there also a command for a whole row? like FetchRow with the usual mysq.dll...

Is there any readme for using com and MYSQL? I'm quite lost in all the commands :tongue:

I've joined Dalnet btw, if you got some time on IRC it might be easier heh

it also seems I have to open a new Object for every SELECT or other sql query I do.. is it supposed to be like that?

Yes, you have to have a different recordset object for each recordset returned to you; how else would you differentiate between them?

The Collect method is a simple method of retrieving the fields numerically when you know how many fields you want and in what order they are going to appear. It is 0-based, so your first field will be 0, your last field will be N-1, where N is the number of fields you requested. This is like PHP's if ($row = mysql_fetch_row($rs)), after which you would use $row[0] to reference the row.

The ADODB.Recordset object's .MoveNext method functions the same way performing another mysql_fetch_row() would; it moves the recordset pointer to the next record.

VBScript:

While Not rs.EOF

' Do something with the current record, like retrieving its values, changing them, updating them, etc.
rs.MoveNext

WEnd

PHP:

while ($row = mysql_fetch_row($rs)) {

/* Do something with the current record, like retrieving its values, changing them, updating them, etc.

}

mIRC:

while (!$com(rs, EOF, 2)) {

; Do something with the current record, like retrieving its values, changing them, updating them, etc.
!.echo -q $com(rs, MoveNext, 1)

}

Well I don't really know mySQL, PHP, com objects, or ADO, but I hacked together a !seen script anyways based on Hammer's work. I'm not sure why it works, but it works pretty well!

While I was trying to figure out how Hammer's code worked, I made some more goodies that might help others.

Here's a snippet...

Code:

  var %obj
  var %SQL = SELECT FirstName, LastName, Gender FROM Nicks WHERE nickname = ' $+ $1 $+ ';
  var %result = $mysql_query(%SQL,%DB), %success = $gettok(%result,1,32), %obj = $gettok(%result,2,32)
  if (%success) {
    echo %Success.Color -a * Opened MySQL QUERY connection: %SQL
    echo -a Returned: $mysql_fetch_field(%obj,1) $mysql_fetch_field(%obj,2) $mysql_fetch_field(%obj,3) 
    SQLcomclose %obj
  }
  else {
    echo %Failure.Color -a * Couldn't connect to MySQL for QUERY: %SQL
  }

That came from rewriting his 1st, simpler code, with his revised code. %DB is also the same as MySQL.Database or MySQL.Database.$ticks depending on where you copy paste.

And custom alias:

Code:

 
; NOT zero based, 1st column is column 1
ALIAS mysql_fetch_field {
  if ($com($$1, Collect, 2, i4, $calc($$2 - 1))) { return $com($$1).result  }
  else {
    ; ERROR: NO FIELD--OUT OF BOUNDS?
    return
  }
}

Now I'm trying to figure out how to get it to return stuff in a format I like. It's been returning integer data like "15" as "15.000000"

I tried different variable types: i1, i2, i4, ui1, ui2, ui4, int, uint, r4, r8, cy, date, decimal, bool, bstr, variant, dispatch, unknown, error (found in mIRC help for com objects) but nothing yet.

I know you didn't want to write a tutorial, but I've been trying to find documentation, especially on the syntax, with little luck.

[edit]I've found the MSDN reference, but it's just help me learn a lot more, but not how why it returns funny numbers.

Upon working further with my code, there seems to be a memory leak.

I traced it to $mysql_command and to a smaller degree... $mysql_query

I'm not sure if I'm just not using it right, but mIRC's memory usage swells to 700 megs in a day.

To test it, I wrote an alias that called portions of the code inside large loops. Everything else I tested, including other mysql and com object aliases worked fine.

hey im having problems in how to use this mysql connection and add to db.

My intention is to save all text from certain nick in to db on my mysql and add ctime next to it.

Example in db how to be stored:

ctime - The1LineFromTheNick
ctime - The2LineFromTheNick
ctime - The3LineFromTheNick

This is how far i got: (not much i know

)

Code:

 
menu channel {
  Relayer
  .On: set %relay on | echo -as RELAYER Turned 09ON
  .Off: unset %relay | echo -as RELAYER Turned 04OFF
}

on *:text:*:#ChannelToListenOn: {
  if ($network == NetworkToListenOn &amp;&amp; $nick == NameToListenOn &amp;&amp; %relay == on) {
    if ( $chr(91) isin $1 &amp;&amp; $chr(93) isin $3) {
      if (SEARCH !isin $2 &amp;&amp; GROUP !isin $2) {
        if (FIRST !isin $2 &amp;&amp; LAST !isin $2) {
          [color:green] ;Need to remove color from %text [/color]
          var %text = $1-
          var %Connection.String = (DRIVER={MySQL ODBC 3.51 Driver};, SERVER=MySQL.MySite.com;, $&amp;
            PORT=80;, DATABASE=myDatabase;, USER=myUsername;, PASSWORD=myPassword;, OPTION=3;)
        }
      }
    }
  }
}

i know the remote bit trigger just fine, its more about getting $ctime - %text (with stripped color) & mysql part to work

Any help would be appreciated