mIRC Homepage
Posted By: Enknown SQL - 18/03/04 01:15 PM
Hey,

Why not implent SQL? So you can retrive info from a SQL-server directly to mIRC in script?
Anyway this is something I really would like to see in a future version.
Posted By: Hammer Re: SQL - 19/03/04 04:32 PM
WARNING: Long, semi-technical post follows. It is intended for those wanting to use SQL to access a database directly through mIRC. It will also be of interest for those learning how to use $com in mIRC scripts, as well.

You can already do this through $com (and ADODB, for one). You can do it the same way you would do it in VB, or in VBScript on an ASP page. Here's how you can do it in a fairly simple way (for the relative complexity of the subject) using nothing except mIRC scripting and a database. Obviously, you'll also need to have ADO installed for this example to work. The database itself can take many forms, though. It doesn't need to be an Access database at all...it can even be a text file or a database located somewhere on the web, using an MSXML2.XMLHTTP30 object that uses XML to model the recordset in both directions.

-----------------------------------------

alias ShowTheseFields {

; Create a Connection object.
.comopen objConnection ADODB.Connection

; Set up the connection string in a variable (easier to change for different projects).
var %ConnectionString = Provider=Microsoft.JET.OLEDB.4.0;Data Source=E:\MyDatabase.mdb

; Open a connection to the datase you want to connect to.
!.echo -q $com(objConnection, Open, 1, *bstr, %ConnectionString)

; Set up the (ANSI) SQL query string you want to use in a variable.
var %SQL = SELECT Nick, [Last Name], [First Name], Country $&
FROM [DALnet Friends] $&
WHERE Nick LIKE 'Ham%";

; Create a Recordset object.
.comopen objRecordset ADODB.Recordset

; Use a client-side cursor. (That's database talk for "I'll hold the recordset here.")
!.echo -q $com(objRecordset, CursorLocation, 4, i4, 3)

; Open the recordset using the SQL you created and using the connection you opened.
!.echo -q $com(objRecordset, Open, 1, *bstr, %SQL, dispatch, objConnection)

; Make sure the SQL query string generated records in the recordset (resultset).
!.echo -q $com(objRecordset, BOF, 2)
var %BOF = $com(objRecordset).result
!.echo -q $com(objRecordset, EOF, 2)
var %EOF = $com(objRecordset).result

; If Beginning of File and End of File are both true, there are no records.
if %BOF && %EOF {
  • .comclose objRecordset
    .comclose objConnection
    echo -ablirtc ctcp * No records found.
    return
}

; Otherwise, find out how many records were returned by going to the end of the recordset
; to find out the RecordCount and save it in a variable.

!.echo -q $com(objRecordset, MoveLast, 1)
!.echo -q $com(objRecordset, RecordCount, 2)
var %RecordCount = $com(objRecordset).result
!.echo -q $com(objRecordset, MoveFirst, 1)

; Loop through each record, showing the fields named in the SQL statement (in this case, they're named).
while !%EOF {
  • ; First, get the Fields collection for this record.
    !.echo -q $com(objRecordset, Fields, 2, dispatch* collFields)

    ; Now we can access each field, using the Fields.Item("Fieldname") property to create individual Field
    ; objects we can play with.

    !.echo -q $com(collFields, Item, 2, *bstr, Nick, dispatch* fldNick)
    !.echo -q $com(collFields, Item, 2, *bstr, Last Name, dispatch* fldLastName)
    !.echo -q $com(collFields, Item, 2, *bstr, First Name, dispatch* fldFirstName)
    !.echo -q $com(collFields, Item, 2, *bstr, Country, dispatch* fldCountry)

    ; Now we get the .Value property of each of these objects.
    !.echo -q $com(fldNick, Value, 2)
    !.echo -q $com(fldLastName, Value, 2)
    !.echo -q $com(fldFirstName, Value, 2)
    !.echo -q $com(fldCountry, Value, 2)

    ; Next, display this record's values.
    echo -ablirtc info * Nick: $com(fldNick).result
    echo -ablirtc info * Name: $com(fldFirstName).result $com(fldLastName).result
    echo -ablirtc info * Country: $com(fldCountry).result
    linesep

    ; Done using these objects, destroy them so we can reuse the same object names next time through the loop.
    .comclose fldCountry
    .comclose fldFirstName
    .comclose fldLastName
    .comclose fldNick
    .comclose collFields

    ; And move to the next record.
    !.echo -q $com(objRecordset, MoveNext, 1)
    !.echo -q $com(objRecordset, EOF, 2)
    %EOF = $com(objRecordset).result
}

; Once we're done with all the records we have retrieved, close the recordset and the connection.
.comclose objRecordset
.comclose objConnection

}

-----------------------------------------

See? Simple and straight-forward, if you know how to use ADO already.

If you don't know the names of the fields to use in the Item property call, you can go through ADOX to get them easily enough. ADOX let's you use a numeric iterator like objCollection.Item(1) rather than requiring objCollection.Item("Fieldname") like the recordset object does.

Since it is likely that you might want to use SELECT * FROM TableName someday, here is how you would go about gathering the field names and their data types (which will come in very handy when you go to start using .AddNew or even just modifying values and updating them). In the following alias, called $GetFieldNamesTypes(TableName), we return a string of field names, separated by $chr(158), and a string of data types, also separated by $chr(158). The two strings, themselves, are separated by a $chr(159). These values can be anything you wish - the tab character $chr(9) and the space character $chr(32) would be good choices as well.

-----------------------------------------

alias GetFieldNamesTypes {

; Set up a the object variable names.
var %objConnection = objConnection $+ $ticks
var %objCatalog = ojbCatalog $+ $ticks
var %collTables = collTables $+ $ticks
var %objTable = objTable $+ $ticks
var %collColumns = collColumns $+ $ticks
var %objColumn = objColumn $+ $ticks

; Set $1- into a local variable as the table name to look for (this is purely documentary here).
var %TableName = $1-

; Create local variables to hold the field names and types so we can return them at the end.
var %FieldNames
var %FieldTypes

; Notice that this connection string must be exactly the same as you used in the previous example.
var %ConnectionString = Provider=Microsoft.JET.OLEDB.4.0;Data Source=E:\MyDatabase.mdb

; Open the two main objects, the Connection object to the database and an ADOX Catalog object.
.comopen %objConnection ADODB.Connection
.comopen %objCatalog ADOX.Catalog

; Open the connection to the database, as before.
; Note: If you already have an open connection object, you can use it instead of opening a new one.

!.echo -q $com(%objConnection, Open, 1, *bstr, %ConnectionString)

; Set the ActiveConnection property to the database connection.
!.echo -q $com(%objCatalog, ActiveConnection, 4, dispatch, %objConnection)

; Get the Tables collection.
!.echo -q $com(%objCatalog, Tables, 2, dispatch* %collTables)

; Using the Item property, get the table name we want. This can either be hard-coded or passed in as
; $1- (as I've used %TableName in this example).

!.echo -q $com(%collTables, Item, 2, *bstr, %TableName, dispatch* %objTable)

; Get the the Columns collection for our table.
!.echo -q $com(%objTable, Columns, 2, dispatch* %collColumns)

; Find out how many fields we're going to get so we can loop through them all.
!.echo -q $com(%collColumns, Count, 2) :: $com(%collColumns).result
var %FieldCount = $com(%collColumns).result

; Loop through each field in turn, gather its Name and Type. Remember that the fields are 0-based, not 1-based. That's why %i starts at 0.
var %i = 0
while %i < %FieldCount {
  • ; Get the column we're pointing at with %i.
    !.echo -q $com(%collColumns, Item, 2, i4, %i, dispatch* %objColumn)

    ; Get the Name for this column and save it.
    !.echo -q $com(%objColumn, Name, 2)
    %FieldNames = $+(%FieldNames,$chr(158),$com(%objColumn).result)

    ; Get the type for each field. Since each type returned is numeric, I've included a shortened
    ; alias below to help translate this number into something usable, called [color:#840017]$FieldType()
    .[/color]
    !.echo -q $com(%objColumn, Type, 2)
    %FieldTypes = $+(%FieldTypes, $chr(158), $FieldType($com(%objColumn).result))

    ; Close the object to make it ready to reuse on the next column.
    .comclose %objColumn

    ; Increment the loop counter and go back through again.
    inc %i
}

; Give ourselves a clean way to close the objects in case there is an error somewhere, such as not finding the table named exactly right.
:error

; Close all the objects we've used.
if $com(%collColumns) { .comclose $ifmatch }
if $com(%objTable) { .comclose $ifmatch }
if $com(%collTables) { .comclose $ifmatch }
if $com(%objCatalog) { .comclose $ifmatch }
if $com(%objConnection) { .comclose $ifmatch }

; And finally, return the values to the calling script. The calling script will use save the return in a variable and
; then split them into two more variables:
;
; var %returned = $GetFieldNames(MyTableName)
; var %Fields = $gettok(%returned, 1, 159)
; var %Types = $gettok(%returned, 2, 159)
;
; Then it can simply loop through %Fields to get the names to use in the Item property.

return $+($right(%FieldNames,-1),$chr(159),$right(%FieldTypes,-1))

}

alias FieldType {
  • ; This is a Switch/Case statement emulator.
    var %a = $iif($1, $1, default)
    goto %a

    :2 | return i2 | ; 2-byte signed integer value.
    :3 | return i4 | ; 4-byte signed integer value.
    :4 | return r4 | ; Single-precision floating-point value.
    :5 | return r8 | ; Double-precision floating-point value.
    :6 | return cy | ; Currency value.
    :7 | return date | ; Date value.
    :8 | return *bstr | ; Null-terminate string value (Unicode).
    :9 | return dispatch | ; Not supported.
    :10 | return error | ; 32-bit error code value.
    :11 | return bool | ; Boolean value.
    :12 | return variant | ; Not supported.
    :13 | return unknown | ; Not supported.
    :14 | return decimal | ; Exact numeric value with a fixed precision and scale.
    :16 | return i1 | ; 1-byte signed integer value.
    :17 | return ui1 | ; 1-byte unsigned integer value.
    :18 | return ui2 | ; 2-byte unsigned integer value.
    :19 | return ui4 | ; 4-byte unsigned integer value.
    :21 | return ui8 | ; 8-byte unsigned integer value.
    :129 | return *string | ; String value (STR).
    :130 | return *string | ; Null-terminated Unicode character string value (WSTR).
    :200 | return *string | ; String value.
    :201 | return *string | ; Long string value.
    :202 | return *string | ; Null-terminated Unicode character string value.
    :203 | return *string | ; Null-terminated Unicode string value.
    :default | return variant | ; Value not covered here.
}

-----------------------------------------

Each of the objects, methods, properties and collections I've used here have links to the appropriate MSDN page that describes them. If you are confused about what one of the members does, following that link should clear things up for you, both in terms of syntax and usage. By doing so, you should be able to see clearly how I did this and therefore be able to duplicate the process later in your own $com scripts without too much difficulty.

The key to doing anything with $com is knowing the object model for the object(s) you wish to use. After that, it's simply a matter of checking the documentation on the objects, methods, properties and collections, and then converting what they require into mIRC script. The above examples show exactly how to do most of the kinds of things you will need to do when you're writing your own $com scripts.

Now let's more or less translate these concepts into a roughly more real world application, something someone might actually do. In the next example, I'm not going to comment quite as heavily, other than to show how the object model I'm going to be using is translated into mIRC script directly. To accomplish this, I've used variable names to hold the common $com values to make them more human-readable. I've used the [[/b]code][/[/b]code] tags in this example so that the spacing I've used to make it more readable shows better.

Code:
[color:#008000];  Creating a new database, two tables and their fields.
;  mIRC.mdb
;
;    Friends
;      FriendsID  (integer  4)      Primary key
;      Nick       (string  30) 
;      Networks   (string 100) 
;      City       (string  50) 
;      Country    (string  50)      
;      FirstName  (string  50) 
;      LastName   (string  50) 
;      SeenTime   (string  10)      $ctime
;      SeenAction (string   1)      J|P|Q = Join|Part|Quit
;
;    Countries
;      CountryID  (integer  4)      Primary key
;      Name       (string  50)      United States
;      Capital    (string  50)      Washington, D.C.
;      Code       (string   2)      US = .us
;      Languages  (string 100)      English, Spanish, French
;[/color]
alias SetupMyDatabase {

  var %database = mIRC
  var %path     = E:\mIRC\databases\
  var %database = $+(%path,%database,.mdb)
  if $isfile(%database) { remove %database }
  [color:#008000];[/color]
  var %ConnectionString = Provider=Microsoft.JET.OLEDB.4.0;Data Source= $+ %database
  [color:#008000];
  ;  A few variables to make the $com commands look a bit more human readable. Once you
  ;  get more familiar with how these are used, you can just use the values straight in
  ;  the $com( ).
  ;[/color]
  var %Method       =        1                       | ; Method
  var %PropertyGet  =        2                       | ; Property Get
  var %PropertySet  =        4                       | ; Property Set
  var %String       =    *bstr                       | ; String data type
  var %Long         =       i4                       | ; 4-byte signed integer
  var %Boolean      =     bool                       | ; True or False only
  var %NewObject    = dispatch*                      | ; Creates a new object
  var %Object       = dispatch                       | ; Passes an object as a parameter
  [color:#008000];
  ;  Now a few variables to match what's in the MSDN documentation.
  ;[/color]
  var %adInteger    =        3                       | ; Long integer (%long)
  var %adVarWChar   =      202                       | ; String       (string)
  [color:#008000];
  ;  Set up the SQL necessary to add auto-incrementing, primary key fields for the tables.
  ;[/color]
  var %SQL.FriendsAuto   = ALTER TABLE Friends   ADD COLUMN     FriendID  counter;
  var %SQL.CountriesAuto = ALTER TABLE Countries ADD COLUMN     CountryID counter;
  var %SQL.FriendsPK     = ALTER TABLE Friends   ADD CONSTRAINT PrimaryKey_Friends   PRIMARY KEY (FriendID);
  var %SQL.CountriesPK   = ALTER TABLE Countries ADD CONSTRAINT PrimaryKey_Countries PRIMARY KEY (CountryID);
  [color:#008000];
  ;  Create the database from scratch, and close it to release it.
  ;[/color]
  .comopen objCatalog ADOX.Catalog
  !.echo -q $com(objCatalog   , Create, %Method, %String, %ConnectionString, %NewObject objConnection)
  !.echo -q $com(objConnection, Close , %Method)
  !.comclose objConnection
  !.comclose objCatalog
  [color:#008000];
  ;  Now open it back up using a Connection object (used later to specify auto-incrementing primary key fields).
  ;  Also open up the two table objects we are going to add.
  ;[/color]
  !.comopen objConnection  ADODB.Connection
  !.comopen objCatalog      ADOX.Catalog
  !.comopen tblFriends      ADOX.Table
  !.comopen tblCountries    ADOX.Table
  [color:#008000];
  ;  Reopen the database using the Connection object and a Catalog object.
  ;[/color]
  !.echo -q $com(objConnection, Open            , %Method     , %String,   %ConnectionString )
  !.echo -q $com(objCatalog   , ActiveConnection, %PropertySET, %Object,   objConnection     )
  [color:#008000];
  ;  objCatalog.Tables
  ;[/color]
  !.echo -q $com(objCatalog   , Tables          , %PropertyGET, %NewObject colTables)
  [color:#008000];
  ;  Set up the Friends and Countries tables and append them to the Tables collection.
  ;[/color]
  !.echo -q $com(tblFriends   , Name            , %PropertySET, %String,   Friends           )
  !.echo -q $com(tblCountries , Name            , %PropertySET, %String,   Countries         )
  !.echo -q $com(colTables    , Append          , %Method     , %Object,   tblFriends        )
  !.echo -q $com(colTables    , Append          , %Method     , %Object,   tblCountries      )
  [color:#008000];
  ;  Create the auto-incrementing primary keys for both tables.
  ;[/color]
  !.echo -q $com(objConnection, Execute         , %Method     , %String,   %SQL.FriendsAuto  )
  !.echo -q $com(objConnection, Execute         , %Method     , %String,   %SQL.FriendsPK    )
  !.echo -q $com(objConnection, Execute         , %Method     , %String,   %SQL.CountriesAuto)
  !.echo -q $com(objConnection, Execute         , %Method     , %String,   %SQL.CountriesPK  )
  [color:#008000];
  ;  Now, create the rest of the fields for the Friends table.
  ;
  ;  objCatalog.Tables("Friends").Columns
  ;[/color]
  !.echo -q $com(tblFriends   , Columns         , %PropertyGET, %NewObject friColumns)
  [color:#008000];
  ;  objCatalog.Tables("Friends").Columns.Append                           Nick      ,         adVarWChar,         30
  ;  objCatalog.Tables("Friends").Columns.Append                           Networks  ,         adVarWChar,        100
  ;  objCatalog.Tables("Friends").Columns.Append                           City      ,         adVarWChar,         50
  ;  objCatalog.Tables("Friends").Columns.Append                           Country   ,         adVarWChar,         50
  ;  objCatalog.Tables("Friends").Columns.Append                           FirstName ,         adVarWChar,         50
  ;  objCatalog.Tables("Friends").Columns.Append                           LastName  ,         adVarWChar,         50
  ;  objCatalog.Tables("Friends").Columns.Append                           SeenTime  ,         adVarWChar,         10
  ;  objCatalog.Tables("Friends").Columns.Append                           SeenAction,         adVarWChar,          1
  ;[/color]
  !.echo -q $com(friColumns   , Append          , %Method     , %String,   Nick      , %Long, %adVarWChar, %Long,  30)
  !.echo -q $com(friColumns   , Append          , %Method     , %String,   Networks  , %Long, %adVarWChar, %Long, 100)
  !.echo -q $com(friColumns   , Append          , %Method     , %String,   City      , %Long, %adVarWChar, %Long,  50)
  !.echo -q $com(friColumns   , Append          , %Method     , %String,   Country   , %Long, %adVarWChar, %Long,  50)
  !.echo -q $com(friColumns   , Append          , %Method     , %String,   FirstName , %Long, %adVarWChar, %Long,  50)
  !.echo -q $com(friColumns   , Append          , %Method     , %String,   LastName  , %Long, %adVarWChar, %Long,  50)
  !.echo -q $com(friColumns   , Append          , %Method     , %String,   SeenTime  , %Long, %adVarWChar, %Long,  10)
  !.echo -q $com(friColumns   , Append          , %Method     , %String,   SeenAction, %Long, %adVarWChar, %Long,   1)
  [color:#008000];
  ;  And create the rest of the fields for the Countries table.
  ;
  ;  objCatalog.Tables("Countries").Columns
  ;[/color]
  !.echo -q $com(tblCountries , Columns         , %PropertyGET, %NewObject couColumns)
  [color:#008000];
  ;  objCatalog.Tables("Countries").Columns.Append                         Name      ,         adVarWChar,         50
  ;  objCatalog.Tables("Countries").Columns.Append                         Capital   ,         adVarWChar,         50
  ;  objCatalog.Tables("Countries").Columns.Append                         Code      ,         adVarWChar,          2
  ;  objCatalog.Tables("Countries").Columns.Append                         Languages ,         adVarWChar,        100
  ;[/color]
  !.echo -q $com(couColumns   , Append          , %Method     , %String,   Name      , %long, %adVarWChar, %long,  50)
  !.echo -q $com(couColumns   , Append          , %Method     , %String,   Capital   , %long, %adVarWChar, %long,  50)
  !.echo -q $com(couColumns   , Append          , %Method     , %String,   Code      , %long, %adVarWChar, %long,   2)
  !.echo -q $com(couColumns   , Append          , %Method     , %String,   Languages , %long, %adVarWChar, %long, 100)
  [color:#008000];
  ;  Set up a nice place to come to if there is an error so that the objects all get closed properly.
  ;[/color]
  :error
  if $error { echo -ac action $error }
  [color:#008000];
  ;  Now free up all the objects we've used.
  ;[/color]
  if $com(couColumns  ) { !.comclose $ifmatch }
  if $com(friColumns  ) { !.comclose $ifmatch }
  if $com(colTables   ) { !.comclose $ifmatch }
  if $com(tblCountries) { !.comclose $ifmatch }
  if $com(tblFriends  ) { !.comclose $ifmatch }
  if $com(objCatalog  ) { !.comclose $ifmatch }
  [color:#008000];
  ;  Finally, close the connection to the database and release that object too.
  ;[/color]
  if $com(objConnection) { !.echo -q $com(objConnection, Close, 2) | !.comclose objConnection }

}

Posted By: CyPliX Re: SQL - 25/03/04 09:46 PM
I've been using SQL in mirc for over 2 years, theres an easier way if you happen to run a MySQL server on your comp.

Theres a dll called mIRC MySQL v1.6.1

It includes how to use it within mirc, for more info about using dll in mirc /help dll and /help $dll
.. should be in the same section of the help file anyways.

have fun.
Posted By: CyPliX Re: SQL - 25/03/04 09:59 PM
Theres also a socket version called MySQL Interface v1.2
Posted By: Hammer Re: SQL - 09/07/04 12:02 AM
While this way is somewhat complex, it is ALSO done completely from within mIRC itself, accessing no 3rd-party DLLs, other than the ADO ones which most installations of Windows already have (or can get from Microsoft easily enough). It's also very easy to switch database types by simply switching the connection string to something else.

You can even access recordsets across the internet on remote databases if you set things up right. I've done it with SQL Server 7 + ADO + ASP serverside. I just persisted the recordset straight to the output object as XML and retrieved it in my mIRC script the same way. It might even be possible to just call set up the IP as part of the connection string for some database types.
Posted By: CyPliX Re: SQL - 12/07/04 02:00 AM
I found this one several months ago thats even better than the MySQL dll if your only looking for somthing small.. and don't intend to have a large database..

SQLiteDll by Klops
© mIRC Discussion Forums