mIRC Home    About    Download    Register    News    Help

Print Thread
Accessing mysql using mIRCs COM features #110115 04/02/05 06:44 PM
Joined: Dec 2004
Posts: 81
N
nycdiesel Offline OP
Babel fish
OP Offline
Babel fish
N
Joined: Dec 2004
Posts: 81
I took this from a post made by Hammer.

Code:
alias mysql.GetNickData {
  ; 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=mIRC;User ID=root;Password=<edited>

  ; 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 * FROM mp3s;

  ; 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
}


note: I changed a few things around but I tried the example shown in Hammers post, even created the table, installed the driver, etc and still got an error.

-
* Invalid parameters: $com (line 60, mysqldb.mrc)
-
Line 60:
Code:
.echo -q $com(RecordsetObject, Open, 1, *bstr, %SQL, dispatch, mysql.Database, int, 1, int, 3, int 1)

%SQL:
Code:
var %SQL = SELECT * FROM mp3s;


Whenever typing the alias, the MySQL Name Data Source dialog appears.

Any ideas why this is encountering an error?


When issuing the /mysql.getnickdata

Re: Accessing mysql using mIRCs COM features #110116 05/02/05 02:37 AM
Joined: Mar 2004
Posts: 457
D
Danthemandoo Offline
Fjord artisan
Offline
Fjord artisan
D
Joined: Mar 2004
Posts: 457
u tried or thought about using one of the mysql dll's on www.mircscripts.org ?

Re: Accessing mysql using mIRCs COM features #110117 05/02/05 06:05 AM
Joined: Dec 2004
Posts: 81
N
nycdiesel Offline OP
Babel fish
OP Offline
Babel fish
N
Joined: Dec 2004
Posts: 81
Yeah but I'm using the newest version of mySQL.

Re: Accessing mysql using mIRCs COM features #110118 05/02/05 11:56 AM
Joined: Dec 2002
Posts: 1,321
H
Hammer Offline
Hoopy frood
Offline
Hoopy frood
H
Joined: Dec 2002
Posts: 1,321
There are several troubleshooting techniques you'll need to use to get to the root of the problem.

[*] Instead of using .echo -q on each $com() call, save the results in a variable, such as %Successful. Then you can check its value at each stage and do some error reporting (which I did not include - for brevity - in the original example).

%Successful = $com(mysql.Database, Open, 1, *bstr, %Connection.String)
if (!%Successful) {
  • echo -ti * ERROR: Database didn't open. | comclose mysql.Database | halt
}

[*] You should check to see if a $com() object is opened before attempting to create it.

if ($com(mysql.Database)) .comclose mysql.Database
.comopen mysql.Database ADODB.Connection

[*] You can check the .State proper of a Command, Connection, Record, Recordset or Stream object.

alias State {
  • [color:#006600]; If no valid COM object is supplied, simply return $null.
    ;
    if (!$com($1)) return

    ; Make sure we're checking on an ADODB object.
    ;

    if ($gettok($com($1).progid,1,46) != ADODB) return

    ; Make sure we're checking the right kinds of objects.
    ;

    if (!$istok(Command Connect Record Recordset Stream, $gettok($com($1).progid,2,46),32)) return

    ; Otherwise, get the state of the object.
    ;

    .echo -q $com($1, State, 2)

    ; Check for a specifically named state property
    ;

    if (($prop == Closed) && ($com($1).result == 0)) return 1
    if (($prop == Open) && ($com($1).result == 1)) return 1
    if (($prop == Connecting) && ($com($1).result == 2)) return 1
    if (($prop == Executing) && ($com($1).result == 4)) return 1
    if (($prop == Fetching) && ($com($1).result == 8)) return 1

    ; If some other state is requested, return 0 rather than $null, meaning Unknown state.
    ;

    if ($prop) return 0

    ; If we make it this far, then no property was specified on a valid COM object. This means
    ; we will want to grab the entire list of states which are ANDed together.
    ;

    var %State.List
    if ($com($1).result == 0) return Closed
    if ($com($1).result & 1) %State.List = Open
    if ($com($1).result & 2) %State.List = %State.List Connecting
    if ($com($1).result & 4) %State.List = %State.List Executing
    if ($com($1).result & 8) %State.List = %State.List Fetching

    ; And return them.
    ;

    return %State.List
}
[/color]
Examples:

if ($State(mysql.Database).Open) {
  • ; then the database connection is open and active
}

or

if (Open Executing == $State(mysqlDatabase)) {
  • ; wait for a bit, still executing a statement
}

The same can be done for the recordset object at any time.

[*] Make as full an error reporting mechanism as you require to troubleshoot each step along the way. You might want to echo out every dynamic variable after it gets set (such as a dynamically-generated SQL statement) and/or halt after each step, till you find the problem step.
  • As you can see, there are a lot of ways for you to troubleshoot exactly where the problem lies. Once you localize that, then you get to figure out what broke when you changed something.

    For instance, if your connection string isn't quite right, then you can change it to something else until you get it working exactly as you need it to. I use 2 different ones for MySQL, both work for me.

    DRIVER={MySQL ODBC 3.51 Driver};Port=3306;Option=16384;Stmt=;Database=[color:#840017]dbname
    ;Server=12.34.56.78;User ID=username;Password=passWORD
    [/color]
    or

    Provider=MySQLProv;Data Source=[color:#840017]dbname
    ;Server=localhost;User ID=username;Password=passWORD
    [/color]
    You can use the IP for the server or (if it's on your machine) localhost, depending on how you have your permissions set. And sometimes, you have to fiddle with those settings to get it working exactly right, as I said.


  • DALnet: #HelpDesk and #m[color:#FF0000]IR[color:#EEEE00]C
    Re: Accessing mysql using mIRCs COM features #110119 30/04/05 10:35 PM
    Joined: Apr 2005
    Posts: 6
    N
    notnice Offline
    Nutrimatic drinks dispenser
    Offline
    Nutrimatic drinks dispenser
    N
    Joined: Apr 2005
    Posts: 6
    IM HAVE PUT THIS LINE IN TO (*a) OR ELSE IM GET THIS ERROR -> * Com 'MySQL.Database' already open

    But Im get the same error like / nycdiesel / -> * Invalid parameters: $com (line 19, sql.mrc)

    The line 19 -> (*b)

    Hope som one can help! smile

    // NN from Denmark out.
    sorry my english! crazy


    alias MySQL.GetNickData {

    (*a) .comclose RecordsetObject
    (*a) .comclose MySQL.Database

    .comopen MySQL.Database ADODB.Connection

    var %Connection.String = $+( $&
    DRIVER={MySQL ODBC 3.51 Driver};, $&
    SERVER=SERVERIP;, $&
    PORT=3306;, $&
    DATABASE=mydatabasename;, $&
    USER=USERNAME;, $&
    PASSWORD=PASSWORD;, $&
    OPTION=3;, $&
    )

    .echo -q $com(MySQL.Database, Open, 1, *bstr, %Connection.String)
    .comopen RecordsetObject ADODB.Recordset
    var %SQL = SELECT FirstName, LastName, Gender FROM Nicks WHERE Nickname = ' $+ $1 $+ ';

    (*b) .echo -q $com(RecordsetObject, Open, 1, *bstr, %SQL, dispatch, MySQL.Database, int, 1, int, 3, int 1)

    .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
    .echo -q $com(RecordsetObject, Collect, 2, i4, 2)
    var %Gender = ( $+ $com(RecordsetObject).result $+ )
    if ($isid) return %Name %Gender
    else echo -gaitc info * $1 is %Name %Gender
    .comclose RecordsetObject
    .comclose MySQL.Database
    }

    Re: Accessing mysql using mIRCs COM features #110120 02/05/05 04:16 PM
    Joined: Dec 2002
    Posts: 1,321
    H
    Hammer Offline
    Hoopy frood
    Offline
    Hoopy frood
    H
    Joined: Dec 2002
    Posts: 1,321
    Original:
    (*b) .echo -q $com(RecordsetObject, Open, 1, *bstr, %SQL, dispatch, MySQL.Database, int, 1, int, 3, int 1)

    Missing the comma after the final "int" and before the final " 1".

    Corrected:
    (*b) .echo -q $com(RecordsetObject, Open, 1, *bstr, %SQL, dispatch, MySQL.Database, int, 1, int, 3, int, 1)


    DALnet: #HelpDesk and #m[color:#FF0000]IR[color:#EEEE00]C
    Re: Accessing mysql using mIRCs COM features #110121 07/05/05 09:03 PM
    Joined: Apr 2005
    Posts: 6
    N
    notnice Offline
    Nutrimatic drinks dispenser
    Offline
    Nutrimatic drinks dispenser
    N
    Joined: Apr 2005
    Posts: 6
    Dahm! tanks.. it works. cool

    U`are n1 Hammer laugh

    Re: Accessing mysql using mIRCs COM features #110122 07/05/05 09:15 PM
    Joined: Apr 2005
    Posts: 6
    N
    notnice Offline
    Nutrimatic drinks dispenser
    Offline
    Nutrimatic drinks dispenser
    N
    Joined: Apr 2005
    Posts: 6
    Ups! btw...

    How do im make this script to take the last new "row" ?


    Code:
      
    .echo -q $com(mysql.Database, Open, 1, *bstr, %Connection.String)
    .comopen RecordsetObject ADODB.Recordset
    
    var %SQL = SELECT * FROM news;
    
    .echo -q $com(RecordsetObject, Open, 1, *bstr, %SQL, dispatch, mysql.Database, int, 2, int, 3, int, 1) 
    
    .echo -q $com(RecordsetObject, Collect, 2, i4, 0)
    .echo -q $com(RecordsetObject, Collect, 2, i4, 1)
    
    var %date = %date $com(RecordsetObject).result
    .echo -q $com(RecordsetObject, Collect, 2, i4, 2)
    
    var %by = %by $com(RecordsetObject).result
    .echo -q $com(RecordsetObject, Collect, 2, i4, 3)
    
    var %news = %news $com(RecordsetObject).result
    .echo -q $com(RecordsetObject, Collect, 2, i4, 4)
    
    else echo -gaitc info * 
    /say 15•14•7•14  $1 Sidste nyt! 15•14•7•14
    /say 15•14•7•14 Dato:-> %date 15•14•7•14
    /say 15•14•7•14 Skrevet af:-> %by   15•14•7•14
    /say 15•14•7•14 Nyheden:-> %news 15•14•7•14
    
    .comclose RecordsetObject
    .comclose mysql.Database
    } 
    


    Have also try:
    Code:
      
    var %SQL = SELECT * FROM news ORDER BY id DESC LIMIT 1 ; 
    


    confused

    Re: Accessing mysql using mIRCs COM features #110123 09/05/05 01:26 PM
    Joined: Dec 2002
    Posts: 1,321
    H
    Hammer Offline
    Hoopy frood
    Offline
    Hoopy frood
    H
    Joined: Dec 2002
    Posts: 1,321
    If you just want to pull the last record added (assuming your ID field is an auto_increment), then you can do it two ways:

    var %SQL = SELECT * FROM news;

    and then use the $com(RecordsetObject, MoveLast, 1) method and retrieve the fields you want with Recordset.Collect method like you did before. Or, you should be able to use the SQL statement you posted...

    var %SQL = SELECT * FROM news ORDER BY id DESC LIMIT 1;

    and then use the Collect method as normal.


    DALnet: #HelpDesk and #m[color:#FF0000]IR[color:#EEEE00]C
    Re: Accessing mysql using mIRCs COM features #110124 10/05/05 10:27 PM
    Joined: Apr 2005
    Posts: 6
    N
    notnice Offline
    Nutrimatic drinks dispenser
    Offline
    Nutrimatic drinks dispenser
    N
    Joined: Apr 2005
    Posts: 6
    oki, tanks hammer - im try laugh

    Re: Accessing mysql using mIRCs COM features #110125 10/05/05 10:54 PM
    Joined: Apr 2005
    Posts: 6
    N
    notnice Offline
    Nutrimatic drinks dispenser
    Offline
    Nutrimatic drinks dispenser
    N
    Joined: Apr 2005
    Posts: 6
    Ey again crazy

    Not working,

    If I’m using this one here:

    Code:
    var %SQL = SELECT * FROM news ORDER BY id DESC LIMIT 1; 
    


    Then there come nothing from the database?
    It wills se like this:

    ••• loader script, vent venligst - [a]NotNice •••
    ••• Her er sidste nyt: •••
    ••• Dato:-> •••
    ••• Skrevet af:-> •••
    ••• Nyheden:-> •••
    ••• Lζs mere pε:-> •••

    Here is it only the texts I’m writhing in the script there are to see?

    The full script look like this:

    Code:
    on *:text:!news*:#: {
    
    /MySQL.Getnewsdata
    } 
    
    alias mysql.getnewsData {
    
    notice $nick   15• 14• 7• 14 loader script, vent venligst - $nick      15• 14• 7• 1
    
    .comopen mysql.Database ADODB.Connection
    
    var %Connection.String = $+( $&
    DRIVER={mysql ODBC 3.51 Driver};, $& 
    SERVER=SERVERIP;, $& 
    PORT=3306;, $& 
    DATABASE=mydatabasename;, $& 
    USER=MYUSER;, $& 
    PASSWORD=MYPASS;, $& 
    OPTION=3;, $& 
    )
    
    .echo -q $com(mysql.Database, Open, 1, *bstr, %Connection.String)
    .comopen RecordsetObject ADODB.Recordset
    
    var %SQL = SELECT * FROM news;
    
    .echo -q $com(RecordsetObject, Open, 1, *bstr, %SQL, dispatch, mysql.Database, int, 2, int, 3, int, 1) 
    .echo -q $com(RecordsetObject, Collect, 2, i4, 0)
    .echo -q $com(RecordsetObject, Collect, 2, i4, 1)
    
    var %date = %date $com(RecordsetObject).result
    .echo -q $com(RecordsetObject, Collect, 2, i4, 2)
    
    var %by = %by $com(RecordsetObject).result
    .echo -q $com(RecordsetObject, Collect, 2, i4, 3)
    
    var %news = %news $com(RecordsetObject).result
    .echo -q $com(RecordsetObject, Collect, 2, i4, 4)
    
    else echo -gaitc info * 
    notice $nick   15• 14• 7• 14    Her er sidste nyt:    15• 14• 7• 14
    notice $nick   15• 14• 7• 14  Dato:->  %date    15• 14• 7• 14
    notice $nick   15• 14• 7• 14  Skrevet af:->  %by      15• 14• 7• 14
    notice $nick   15• 14• 7• 14  Nyheden:->  %news    15• 14• 7• 14
    notice $nick   15• 14• 7• 14  Lζs mere pε:->  %Link     15• 14• 7• 14
    
    .comclose RecordsetObject
    .comclose mysql.Database
    }  
    
    


    I’m only try the code were:
    // var %SQL = SELECT * FROM news ORDER BY id DESC LIMIT 1; \\

    Because I’m not sure I’m understand how to do on the other way, were you use:
    // $com(RecordsetObject, MoveLast, 1) \\


    IS there some where I’m cut read more about “using” script code/sql to IRC or??

    Taks /notnice
    laugh

    Re: Accessing mysql using mIRCs COM features #110126 12/05/05 10:08 PM
    Joined: Dec 2002
    Posts: 1,321
    H
    Hammer Offline
    Hoopy frood
    Offline
    Hoopy frood
    H
    Joined: Dec 2002
    Posts: 1,321
    The MoveLast method call comes immediately after the Open method call. It instructs ADO to go to the last record in the returned recordset.


    DALnet: #HelpDesk and #m[color:#FF0000]IR[color:#EEEE00]C
    Re: Accessing mysql using mIRCs COM features #110127 29/05/05 08:37 AM
    Joined: Apr 2005
    Posts: 6
    N
    notnice Offline
    Nutrimatic drinks dispenser
    Offline
    Nutrimatic drinks dispenser
    N
    Joined: Apr 2005
    Posts: 6
    oki - but im dont now how to use it on the script! confused

    Re: Accessing mysql using mIRCs COM features #110128 30/05/05 01:36 PM
    Joined: Dec 2002
    Posts: 1,321
    H
    Hammer Offline
    Hoopy frood
    Offline
    Hoopy frood
    H
    Joined: Dec 2002
    Posts: 1,321
    You are going to have to learn about ADO before you can work with it. It is not going to just mysteriously work all of the sudden. You will need to go through the same learning process everyone else has that uses ADO, what its methods and properties are and how and when to use them. I can't give you this knowledge without writing up an extended ADO tutorial. That is quite beyond the scope of this forum; at best, only a handful of scripters might be interested in such a tutorial if it were written.

    Besides, there are many, many tutorials on ADO already available. Combining the knowledge gleaned from them with the numerous examples I've already provided in this forum, you should be able to work out easily what to change in the script examples already given once you know what needs to be changed and why.

    $com is just the way we access the ADO objects within mIRC scripts; however, you already have to know ADO in order to use it properly.


    DALnet: #HelpDesk and #m[color:#FF0000]IR[color:#EEEE00]C
    Re: Accessing mysql using mIRCs COM features #110129 16/07/05 04:20 AM
    Joined: Dec 2002
    Posts: 14
    N
    namnori Offline
    Pikka bird
    Offline
    Pikka bird
    N
    Joined: Dec 2002
    Posts: 14
    There is also SELECT LAST_INSERT_ID()

    Quote:
    If you just want to pull the last record added (assuming your ID field is an auto_increment), then you can do it two ways:

    var %SQL = SELECT * FROM news;

    and then use the $com(RecordsetObject, MoveLast, 1) method and retrieve the fields you want with Recordset.Collect method like you did before. Or, you should be able to use the SQL statement you posted...

    var %SQL = SELECT * FROM news ORDER BY id DESC LIMIT 1;

    and then use the Collect method as normal.