Making a Database.

Posted by Xyborg on Sat 04 Oct 2003 08:52 PM — 29 posts, 89,941 views.

Xyborg Canada Sat 04 Oct 2003 08:52 PM #0

Ok. The mud I play/work on is prone to jackasses coming back continuously to crash the game and stuff. What I want to do, is make a database of the IPs connecting to the mud. I don't have MySQL or anything like that, so I was hoping it would be possible to do just with the game notepad or somthing.

This is how I was thinking it could be done.

Trigger goes off.
script checks to see if it (the ip and player name (Xyborg@66.139.45.45)) exists in the notepad document.
If it isn't in the document, it adds the line and saves the document. (Saving isn't really needed.)
Now if the exact line (Xyborg@66.139.45.45) exists in the document, it isn't added. But if the IP varies at all, it IS added.

If this is possible, how do I go about appending and retriving information from notepad?

Thanks for any help you can give me.

Nick Gammon Australia Forum Administrator Sun 05 Oct 2003 05:28 AM #1

The example databases given in a couple of plugins just use the Access database format which normally comes with most versions of Windows (the ODBC driver). However, you can do what you want pretty easily by simply storing the names and IPs in a variable, separated by commas, and using Split to break them up into an array. There was a recent post about doing that.

Xyborg Canada Mon 06 Oct 2003 05:13 AM #2

wow. I didn't understand anything you just said. This is what I think you said.

"Go look at some of the other plugins."

Sorry for being retarded, but I'm not a smart one. I just do half assed stuff.

Flannel USA Mon 06 Oct 2003 06:17 AM #3

He said pretty much what you got out of it.

You dont need SQL or anything, windows innately has some database functions on normal installs.

Plugins that use this can be found on the plugins page.

He also went on to say that you dont really need to make a database, you can just use a variable to store the IPs with commas seperating them, and then use the Split command (in the scripting engines) to seperate the variable data ("ip1,ip2,ip3") into an array which will be more usable by the scripting engines to do what you want.

Xyborg Canada Mon 06 Oct 2003 06:28 AM #4

Hmm. So there isn't really a way I could have it in the format I want (Xyborg@65.139.45.45) right?

Hrm. Well at least I don't need MySQL.

Ok. Instead of the way I wanted, could I do it in this type of format?

65.139.45.45
Xyborg
Shinjirou

Cause that would be really useful.

Flannel USA Mon 06 Oct 2003 08:34 AM #5

Why couldnt you have it in the [name]@[ip] format?

Xyborg Canada Mon 06 Oct 2003 03:39 PM #6

I have no idea how to store letters in a variable lol. I'm still new to this stuff. Only did one plugin a couple months ago and then I just gave up.

It was just a PL tracker for a DBZ mud I play. Pretty simple. Nick did it with just a trigger which I used and modified to suit my needs. Then I had a hard time trying to re-insert commas into the totalled number.

Basically, what I know of VBscript is in that one plugin I made. If you want to check it out, you can get it at

http://www.badtrip-designs.com/dloads/PLTL16.zip

Dubthach Mon 06 Oct 2003 05:03 PM #7

You could do this very easily in PerlScript. If VBScript has associative arrays/hashes, you could use this concept for it as well.

Trigger fires, you get user@domain.

split the variable on your comma delimiters.

load up a hash with the keys being members of the split list. set the values to 1 or some other arbitrary value.

add the user@domain to the hash. If the user already exists in the hash, it will not be duplicated because hashes are guaranteed to have unique keys.

join the keys from the hash again by your comma delimiter and shove it back into the variable.

In perl the code would like something like:


@users = split(/,/, $mushvar);

foreach $x (@users)
{
    $hash_users{$x} = 1;
}

$hash_users{$new_user} = 1;

$mushvar = join(',', sort(keys(%hash_users)));

You'll have to figure out how to get and set the $mushvar.

Shadowfyr USA Mon 06 Oct 2003 09:19 PM #8

Well Dubthach, I am sure he is now completely confused.

Ok. Xyborg. This is a stripped down and reorganized database that should do what you need:



<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE muclient>
<muclient>
<plugin
   name="IPDatabase"
   author="Kagehi Kossori"
   id="5dfbc176dad925866871c1dc"
   language="VBscript"
   purpose="Maintains a database of IPs and player names."
   date_written="10-06-2003"
   date_modified="10-06-2003"
   requires="3.24"
   save_state="y"
   version="1.0"
   >
<description trim="y">
<![CDATA[
  This plugin manages a basic database of players and IPs.

  Commands are:

  ip:add <Player> <IP Address>   - Adds a player and IP to the database.

  ip:remove <Player> - Removes an item from the database, by Player name.

  ip:clear <IP Address {optional}> - Will clear all names with a single IP or the entire database.

  ip:list <IP or Player {optional)> - This will show an entire like id you leave off the name or IP.
  If you use a name or an IP, then it will list that name and IP or all names associated with that IP.

  ip:help - This help page.

]]>
</description>

</plugin>

<aliases>
  <alias script="Add"
   match="ip:add * *"
   enabled="y">
  </alias>
  
  <alias script="List"
   match="ip:list"
   enabled="y">
  </alias>

  <alias script="List"
   match="ip:list *"
   enabled="y">
  </alias>

  <alias script="Remove"
   match="ip:remove *"
   enabled="y">
  </alias>

  <alias script="ClearList"
   match="ip:clear"
   enabled="y">
  </alias>
</aliases>

<script>
<![CDATA[
dim DBHandle

sub OnPluginInstall
'  timer: enabled, one-shot, active-if-not-connected

  world.addtimer "", 0, 0, 5, "", 1 + 4 + 32, "OnSetup"
end sub

sub Onsetup(tName)
  dim Link
  Link = StartDB
  select case Link
    case 0
      world.ColourNote ""lightblue", "midnightblue", "IP list plugin started."
    case 1
      world.ColourNote "white", "red", "ADOX Catalog missing. You may need to download the latest MDAC from Microsoft."
    case 2
      world.ColourNote "white", "red", "Table creation failed."
  end select    

function GetProvider
  'Uses the "version independent" provider name, to hopefully simplify matters.
  GetProvider = "Provider=Microsoft.Jet.OLEDB;Data Source=IPList.mdb;Jet OLEDB:Engine Type=5;"
end function

function StartDB
  Dim FSO, Exists, CatHandle, oTable
  
  On Error Resume Next
  'Connect to the ADOX functions.
  Set CatHandle = CreateObject ("ADOX.Catalog")
  
  If Err.Number <> 0 Then
    StartDB = 1
    Exit Function 
  End If

  On Error Goto 0

  'Lets first see if it exists.
  Set FSO = CreateObject("Scripting.FileSystemObject")
  Exists = FSO.FileExists ("IPList.mdb")
  Set FSO = Nothing

  if not Exists then
    CatHandle.Create GetProvider
    world.ColourNote "lightblue", "midnightblue", "IPList database created."
  end if

  'Open database.
  Set DBHandle = CreateObject ("ADODB.Connection")
  DBHandle.Open GetProvider

  'Check to see if Table (the actually storage place) exists.
  Exists = vbFalse
  For Each oTable In db.Tables
    If UCase(oTable.Name) = UCase("IPData") Then
      Exists = vbTrue
      Exit For
    End If
  Next

  if not Exists then
    'Create the table.
    if DoSQL ("CREATE TABLE IPData (" & _
      "  ip_address varchar(15) NOT NULL," & _
      "  player_name varchar(64) NOT NULL)") Then
      world.ColourNote "lightblue", "midnightblue", "IPList database created."
    else
      StartDB = 2
      Exit Function 
    end if
  end if

  Set CatHandle = Nothing
  StartDB = 0
end function

'
'  Execute some arbitrary SQL
'
Function DoSQL (sSQL)
  DoSQL = vbTrue	' error return

  On Error Resume Next
  
' Execute it
  DBHandle.Execute sSQL

  If Err.Number <> 0 Then
    world.ColourNote "white", "red", Err.Description
    Exit Function 
  End If

  On Error GoTo 0

  DoSQL = vbFalse	' OK return
end Function

sub Add (aName, Output, Wilds)
  Dim ip_address, player_name

  player_name = Wilds(1)
  ip_address = Wilds(2)

  If DoSQL _
    ("INSERT INTO IPList (player_name, ip_address)" & _
          " VALUES (" & _
          """" & Player & """, " & _
          """" & ip_address & """);") Then Exit Sub         
 
  world.ColourNote "white", "green", "Player '" & Player & _
     "' added to the database"  
end sub

sub Remove (aName, Output, Wilds)
  If DoSQL _
    ("DELETE FROM IPList WHERE player_name = " & _
          """" & Wilds(1) & """ ") Then Exit Sub
 
  world.ColourNote "white", "green", "Player '" & Wilds(1) & _
     "' deleted from the database"
end sub

sub List (aName, Output, Wilds)
  Dim ip_address, player_name
  Dim rst, count, SQuery
  Dim LastIP

  if Wilds(1) = "" then
    sQuery = "SELECT * FROM IPData ORDER BY ip_address"
  else
    sQuery = "SELECT * FROM IPData WHERE " & _
         "player_name like ""%" & wildcards (1) & "%"" " & _
         "OR ip_address like ""%" & wildcards (1) & "%"" "
         "ORDER BY ip_address"
  end if

  On Error Resume Next
  
  Set rst = CreateObject ("ADODB.Recordset")
  rst.Open sQuery, DBHandle

  If Err.Number <> 0 Then
    world.ColourNote "white", "red", Err.Description
    Set rst = Nothing
    Exit Function
  End If

  count = 0

  ' display each record
  Do Until rst.EOF

    count = count + 1

    ip_address   = rst.Fields ("ip_address").Value
    player_name  = rst.Fields ("port").Value

    If LastIP <> ip_address then
      world.note " "
      world.ColourNote "white", "darkred", ip_name & ":"
      LastIP = ip_address
    End If
    
    world.ColourNote "white", "darkred", player_name

    rst.MoveNext

  Loop

  Set rst = Nothing

end sub

sub ClearList (aName, Output, Wilds)
  if Wilds(1) <> "" then
    If DoSQL _
    ("DELETE FROM IPList WHERE ip_address = " & _
          """" & Wilds(1) & """ ") Then Exit Sub
    world.ColourNote "white", "green", "IP '" & Wilds(1) & _
     "' deleted from the database"
  else
    If DoSQL("DELETE FROM IPList") Then Exit Sub
    world.ColourNote "white", "green", "All data erased."
  end if
end sub
]]>
</script> 

<!--  Plugin help  -->

<aliases>
  <alias
   script="OnHelp"
   match="ip:help"
   enabled="y"
  >
  </alias>
</aliases>

<script>
<![CDATA[
Sub OnHelp (sName, sLine, wildcards)
  World.Note World.GetPluginInfo (World.GetPluginID, 3)
End Sub
]]>
</script> 

</muclient>

Hopefully this all works. I haven't tested it though.

I left out some things, like the extra queries before an Add or Delete. These either work or don't, it doesn't make sense to execute an INSERT, which doesn't replace existing records anyway. It 'would' be important if we where using a database with extra data that we didn't want to get overwritten and we used the UPDATE command, but in this case it is redundant.

I also leave the Database connection open. There isn't any harm in doing this and it likely saves at least some time. Again, this 'might' be an issue if the database was remote or it was possible that the engine could disconnect when in use, but that is very unlikely here. Nick's version is nicely modularized, but is hard to fumble through and figure things out. ;)

Xyborg Canada Tue 07 Oct 2003 01:21 AM #9

Ok. I got a couple errors but I fixed what I could. The following one I don't know how to fix.

Error number: -2146827286
Event: Execution of line 21 column 1
Description: Syntax error
Line in error:
function GetProvider
Called by: Immediate execution


function GetProvider
  'Uses the "version independent" provider name, to hopefully simplify matters.
  GetProvider = "Provider=Microsoft.Jet.OLEDB;Data Source=IPList.mdb;Jet OLEDB:Engine Type=5;"
end function

Then I think it will work.

Shadowfyr USA Tue 07 Oct 2003 06:53 PM #10

Ah.. Look at the Onsetup sub. First off, that sub should be

sub OnSetup (tName)

The timer won't work if the name isn't exactly the same. However, this is also the source for your error. If you look carefully at the error, it doesn't say anything about the contents of the function at all. It is complaining about its existance. The reason is fairly obvious, you need to add:

End Sub

to the end of the OnSetup sub, right after the 'End Select' statement. I accidently left that out, so it is complaining about a new function being defined 'before' the last sub or function has been finished. One can hope that these are the only errors, but... I am not going to hold my breath. lol

Xyborg Canada Tue 07 Oct 2003 10:54 PM #11

Ah! I totally missed that. Anywho. I added the 'end sub' part and it went a little further. Got some error about expecting and end statement but that was an easy fix. Just had an extra " somewhere. But then I got this.

Error number: -2146827264
Event: Execution of line 137 column 10
Description: Expected statement
Line in error:
"ORDER BY ip_address"
Called by: Immediate execution

which I found in the List sub. Tried a couple things to try and fix it but none of them worked, so again I have to get help.

Shadowfyr USA Tue 07 Oct 2003 11:36 PM #12

Previous line in missing the '& _'. This mean basically & - "Append another string" and _ - "The rest of this statement is on the next line of the script". So with those missing it assumes that you have finished the command, but then have a string that doesn't belong to anyone on line that produces the error.

Xyborg Canada Wed 08 Oct 2003 03:39 AM #13

Fixed that and now I'm screwed again.

Error number: -2146827272
Event: Execution of line 148 column 10
Description: Expected 'Sub'
Line in error:
Exit Function
Called by: Immediate execution

Don't know which line is 148 since I use Notepad. Of course I'm always open to suggestions about good programs for this stuff. If you know any, lemme know.

Anyways. Like I said, I can't tell which line is 148 but I did look at all the lines containing Exit Funtion and around that, but I still couldn't find anything wrong.

Thanks for all the help so far guys.

Nick Gammon Australia Forum Administrator Wed 08 Oct 2003 04:42 AM #14

To find the line number, copy the script part of the plugin, and paste into a MUSHclient notepad window. Then use Ctrl+G (go to line) to go to line 148 (or whatever).

You may need to uncheck "word wrap" in the notepad preferences (File -> Global Preferences -> Notepad) or the wrapping at the window edge might throw out the line count.

Xyborg Canada Wed 08 Oct 2003 06:42 AM #15

Thansk Nick. That's a great help. Love learning more about MUSHClient.

Even now that I know what line I'm looking at, I can't see anything wrong with it. I mean... God I hate being a newb. Even after a month or so I'm still clueless to VBscript. *sigh* Now I see why I'm the only one on the muds I play to ever toy with it.

[EDIT]

Nevermind. I fixed it, but now I got another error I don't even know how to deal with.

Error number: -2147221164
Event: Execution of line 47 column 5
Description: Class not registered
Called by: Function/Sub: OnSetup called by timer
Reason: processing timer ""

Comepletely lost here.

Shadowfyr USA Wed 08 Oct 2003 07:26 PM #16

Hmm. It seems to be saying that the Microsoft Jet ODBC driver is missing. This seems quite odd, since you should have Microsoft.Jet.OLEDB.3.51 installed as standard on Windows 98 and Microsoft.Jet.OLEDB.4.0 on anything newer. Unless it is complaining about using the version independant name, which makes no sense either.

Hmm.. Jusr tested this on mine.. Apparently it won't work, which makes me seriously wonder about the stupidity of even having a version independant ID for such things. It would mean that the program to use the driver would have to search the registry for the short version, then back track to get the real provider. Sigh...

It will probably work if you change it to Microsoft.Jet.OLEDB.4.0, unless you are on Win98, in which case you may need to upgrade. I'll look around and see if I can find why the simplified version doesn't work, in spite of the fact that it is supposed to. :(

Shadowfyr USA Wed 08 Oct 2003 08:10 PM #17

Hmm. I just nosed around a bit. Seems that 'some' servers that use ODBC can be accessed through the version independent ID. An example is MSDASQL, which you can access using either the version like 'MSDASQL.1' or the independent ID 'MSDASQL'. It seesm that like any good Micro$loth product, the one people use the most often is broken and can't be accessed through an non-version specific name, thus dooming all of use to having half our programs stop working the moment they upgrade from 4.0 to whatever the next version will be...

The only solution seems to be to check the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet for the 'current' installed version. Of course then you have to look for the highest version number key under that in order to get the correct version.

Of course, assuming that .jod is 'standard' across version you can look in HKEY_CLASES_ROOT\.jod and the full name is in there. This may be specific to 4.0 though.

If the stupid installer worked right it 'should' have installed a key at HKEY_CLASS_ROOT\Microsoft.Jet.OLEDB\CLSID that is the same as the 'latest' version. This is probably where adodb.connection is looking to find the driver and since there isn't one there for it to find.... If you look in there you find entries for both MSDASQL and MSDASQL.1, so this is 99.9% certain to be the heart of why the Jet engine doesn't bloody work right when you try to use it without a version number.

In any case.. Changing the name to Microsoft.Jet.OLEDB.4.0 'should' fix it. It doesn't mean it will work 2-3 years from now on a system using 5.0 or something, but the only 'real' solution right now is to either add a key to the registry yourself to 'fix' the issue or search the registry every bloody time you start the plugin to figure out what the version is you need to use. Either solution is a stupid waste of the programmers time imho.

Xyborg Canada Wed 08 Oct 2003 11:28 PM #18

Ok. Changed the thing to Microsoft.Jet.OLEDB.4.0 which in turn made the script compile for the most part. I did get one error.

Error number: -2146827864
Event: Execution of line 57 column 3
Description: Object required: 'db'
Called by: Function/Sub: OnSetup called by timer
Reason: processing timer ""

which is located in the StartDB function.


  Exists = vbFalse
  For Each oTable In db.Tables
    If UCase(oTable.Name) = UCase("IPData") Then
      Exists = vbTrue
      Exit For
    End If
  Next

Since I don't know anything about this database stuff and Microshaft crap, I don't know what I'm doing. But even though I got that error, the script still runs. I can't add/list/clear anything because I get the following error:

ip:clear gave me this.
The Microsoft Jet database engine cannot find the input table or query 'IPList'. Make sure it exists and that its name is spelled correctly.
ip:list gave me this.
The Microsoft Jet database engine cannot find the input table or query 'IPData'. Make sure it exists and that its name is spelled correctly.
ip:add gave me this.
Could not find output table 'IPList'.

and the rest of the errors are pretty much the same.

But I do get the "IPList database created." when I load the plugin.

Shadowfyr USA Thu 09 Oct 2003 12:10 AM #19

Hmm. OK. db.Tables should be CatHandle.Tables. I missed this one. Basically it works like this:

object.item

Where object is a 'handle' that points to or sort of contains the item, in this case the ADOX.Catalog. Item is either 1) a property (something you read/set) or 2) a function/sub that gets called.

Hmm.. looking at my code I did this wrong anyway. The code should be:


  if not Exists then
    CatHandle.Create GetProvider
    world.ColourNote "lightblue", "midnightblue", "IPList database created."
  end if

  'Get a connection to the provider to check tables.
  CatHandle.ActiveConnection = GetProvider

  'Check to see if Table (the actually storage place) exists.
  Exists = vbFalse
  For Each oTable In CatHandle.Tables
    If UCase(oTable.Name) = UCase("IPData") Then
      Exists = vbTrue
      Exit For
    End If
  Next

  if not Exists then
    'Create the table.
    if DoSQL ("CREATE TABLE IPData (" & _
      "  ip_address varchar(15) NOT NULL," & _
      "  player_name varchar(64) NOT NULL)") Then
      world.ColourNote "lightblue", "midnightblue", "IPList database created."
    else
      StartDB = 2
      Exit Function 
    end if
  end if

  'Close the ADOX connection.
  Set CatHandle = Nothing

  'Open our database.
  Set DBHandle = CreateObject ("ADODB.Connection")
  DBHandle.Open GetProvider

Now for the other issues:

The code for ip:clear, ip:add and ip:remove is flat out wrong. Replace IPList with IPData in the subs 'Add', 'Remove' and 'ClearList'. The code in 'List' should be right, but I suspect that the mistakes in StartDB resulted in the database not actually being created. I have no idea why it gave you the 'IPList database created.' message, since it obviously didn't do so.

Xyborg Canada Thu 09 Oct 2003 06:25 AM #20

Yay. No compile errors or nothing. Now I just can't get the commands to work and stuff. Gonna give you a list of problem messages I get when I use the ip: commands.

ip:add/ip:remove/ip:clear
Object required

ip:list
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Tried to make a couple changes like adding the 'StartDB = 0' line back in at the end of what you had in your last post and that didn't work.

Anyways. Back to working on my newbish script that will just track the highest number recieved. *sigh* Having a hard time doing that, but I think I'm on the right track.

Shadowfyr USA Thu 09 Oct 2003 09:01 AM #21

Ok... Here is the fix:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE muclient>
<muclient>
<plugin
   name="IPDatabase"
   author="Kagehi Kossori"
   id="5dfbc176dad925866871c1dc"
   language="VBscript"
   purpose="Maintains a database of IPs and player names."
   date_written="2003-10-06"
   date_modified="2003-10-06"
   requires="3.24"
   save_state="y"
   version="1.0"
   >
<description trim="y">
<![CDATA[
  This plugin manages a basic database of players and IPs.
  
  Commands are:
  
  ip:add <Player> <IP Address>   - Adds a player and IP to the database.
  
  ip:remove <Player> - Removes an item from the database, by Player name.
  
  ip:clear <IP Address {optional}> - Will clear all names with a single IP or the entire database.
  
  ip:list <IP or Player {optional)> - This will show an entire like id you leave off the name or IP.
  If you use a name or an IP, then it will list that name and IP or all names associated with that IP.
  
  ip:help - This help page.
  
]]>
</description>
</plugin>
<aliases>
  <alias script="Add"
   match="ip:add * *"
   enabled="y"
   group="commands">
  </alias>
  
  <alias script="List"
   match="ip:list"
   enabled="y"
   group="commands">
  </alias>
  
  <alias script="List"
   match="ip:list *"
   enabled="y"
   group="commands">
  </alias>
  
  <alias script="Remove"
   match="ip:remove *"
   enabled="y"
   group="commands">
  </alias>

  <alias script="ClearList"
   match="ip:clear
   group="commands""
   enabled="y">
  </alias>
</aliases>

<script>
<![CDATA[
dim DBHandle

sub OnPluginInstall
'  timer: enabled, one-shot, active-if-not-connected
  world.addtimer "", 0, 0, 5, "", 1 + 4 + 32, "OnSetup"
end sub

sub Onsetup(tName)
  dim Link
  Link = StartDB
  select case Link
    case 0
      world.ColourNote "lightblue", "midnightblue", "IP list plugin started."
    case 1
      world.ColourNote "white", "red", "ADOX Catalog missing. You may need to download the latest MDAC from Microsoft."
    case 2
      world.ColourNote "white", "red", "Table creation failed."
  end select    
end sub

function GetProvider
  'Uses the "version independent" provider name, to hopefully simplify matters.
  GetProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=IPList.mdb;Jet OLEDB:Engine Type=5;"
end function

function StartDB
  Dim FSO, Exists, CatHandle, oTable
  
  On Error Resume Next

  'Connect to the ADOX functions.
  Set CatHandle = CreateObject ("ADOX.Catalog")
  
  If Err.Number <> 0 Then
    StartDB = 1
    Exit Function 
  End If
  
  On Error Goto 0
  
  'Lets first see if it exists.
  Set FSO = CreateObject("Scripting.FileSystemObject")
  Exists = FSO.FileExists ("IPList.mdb")
  Set FSO = Nothing
  
  if not Exists then
    CatHandle.Create GetProvider
    world.ColourNote "lightblue", "midnightblue", "IPList database created."
  end if
  
  'Get a connection to the provider to check tables.
  CatHandle.ActiveConnection = GetProvider
  
  'Check to see if Table (the actually storage place) exists.
  Exists = vbFalse
  For Each oTable In CatHandle.Tables
    If UCase(oTable.Name) = UCase("IPData") Then
      Exists = vbTrue
      Exit For
    End If
  Next
  
  'Close the ADOX connection.
  Set CatHandle = Nothing

  'Open our database.
  Set DBHandle = CreateObject ("ADODB.Connection")
  DBHandle.Open GetProvider

  if not Exists then
    'Create the table.
    if DoSQL ("CREATE TABLE IPData (" & _
      "  ip_address varchar(15) NOT NULL," & _
      "  player_name varchar(64) NOT NULL)") Then
      StartDB = 2
      Exit Function 
    else
      world.ColourNote "lightblue", "midnightblue", "IPList database created."
    end if
  end if
  
  StartDB = 0 'Return with a code indicating everything was a success (hopefully).
end function

'
'  Execute some arbitrary SQL
'
Function DoSQL (sSQL)
  DoSQL = vbTrue' error return
  On Error Resume Next
  
' Execute it
  DBHandle.Execute sSQL
  If Err.Number <> 0 Then
    world.ColourNote "white", "red", Err.Description
    Exit Function 
  End If

  On Error GoTo 0
  
  DoSQL = vbFalse' OK return
end Function

sub Add (aName, Output, Wilds)
  Dim ip_address, player_name
  player_name = Wilds(1)
  ip_address = Wilds(2)
  note player_name
  note ip_address
  If DoSQL _
    ("INSERT INTO IPData (player_name, ip_address)" & _
          " VALUES (" & _
          """" & player_name & """, " & _
          """" & ip_address & """);") Then Exit Sub         
 
  world.ColourNote "white", "green", "Player '" & player_name & _
     "' added to the database"  
end sub

sub Remove (aName, Output, Wilds)
  If DoSQL _
    ("DELETE FROM IPData WHERE player_name = " & _
          """" & Wilds(1) & """ ") Then Exit Sub
 
  world.ColourNote "white", "green", "Player '" & Wilds(1) & _
     "' deleted from the database"
end sub

sub List (aName, Output, Wilds)
  Dim ip_address, player_name
  Dim rst, count, SQuery
  Dim LastIP
  if Wilds(1) = "" then
    sQuery = "SELECT * FROM IPData ORDER BY ip_address"
  else
    sQuery = "SELECT * FROM IPData WHERE " & _
         "player_name like ""%" & wildcards (1) & "%"" " & _
         "OR ip_address like ""%" & wildcards (1) & "%"" " & _
         "ORDER BY ip_address"
  end if
  
  On Error Resume Next
  
  Set rst = CreateObject ("ADODB.Recordset")
  rst.Open sQuery, DBHandle
  
  If Err.Number <> 0 Then
    world.ColourNote "white", "red", Err.Description
    Set rst = Nothing
    Exit Sub
  End If
  
  count = 0
  ' display each record
  Do Until rst.EOF
    count = count + 1
    ip_address   = rst.Fields ("ip_address").Value
    player_name  = rst.Fields ("player_name").Value
    If LastIP <> ip_address then
      world.note " "
      world.ColourNote "white", "darkred", ip_address & ":"
      LastIP = ip_address
    End If
    
    world.ColourNote "white", "darkred", player_name
    rst.MoveNext
  Loop
  
  Set rst = Nothing
end sub

sub ClearList (aName, Output, Wilds)
  if Wilds(1) <> "" then
    If DoSQL _
    ("DELETE FROM IPData WHERE ip_address = " & _
          """" & Wilds(1) & """ ") Then Exit Sub
    world.ColourNote "white", "green", "IP '" & Wilds(1) & _
     "' deleted from the database"
  else
    If DoSQL("DELETE FROM IPData") Then Exit Sub
    world.ColourNote "white", "green", "All data erased."
  end if
end sub
]]>
</script> 

<!--  Plugin help  -->
<aliases>
  <alias
   script="OnHelp"
   match="ip:help"
   enabled="y"
  >
  </alias>
</aliases>

<script>
<![CDATA[
Sub OnHelp (sName, sLine, wildcards)
  World.Note World.GetPluginInfo (World.GetPluginID, 3)
End Sub
]]>
</script> 
</muclient>

Issues I corrected:

1. I had the part that opened the database in the wrong place. I forgot that DOSQL opened it in Nick's version. I also needed to release the ADOX first, since having that connection in place caused the table creation to fail, even after attempting to open the database.

2. The SQL bit that created the table was producing the success message when something went wrong, but had it worked, would instead have insisted that the database creation procedure had in fact failed. Oops!

3. The otehr error was a result of the connection to the database being open, but the table not being available. I assume this is the case though, but I am not sure. It did happen to me once, with a connection to ADODB open, but no database opened with it (it hadn't been created, so couldn't really be opened).

Everything should now work.

Xyborg Canada Thu 09 Oct 2003 09:12 AM #22

Thanks a bunch. I'll check it out and I'll edit this post with my results. Though I really gotta stop letting you all do everything for me lol. Not learning too much, but I am learning a little bit. I'll be all leet like you as I slowly learn all this.

[EDIT]
Getting a few errors. I had to make a couple changes to an area where you misplaced a couple quotes, but other than that, the script loaded fine. I didn't getting any errors until I tried using some commands.

"ip:add Aburame 65.139.45.45" Displayed:
[b]Aburame
65.139.45.45
Object Required[/b]

"ip:list" Displayed:
[b]Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.[/b]

"ip:list Aburame" Displayed:
Error number: -2146828275
Event: Execution of line 136 column 5
Description: Type mismatch: 'wildcards'
Called by: Function/Sub: List called by alias
Reason: processing alias ""

and the other commands just gave me the "Object required" thing.

Other than those, I think it's working fine. But what do I know -_-

Shadowfyr USA Thu 09 Oct 2003 08:56 PM #23

Hmm. I don't have a clue why this is happening. A good bet is that the original database has been corrupted by the prior mistakes and no connection object ever gets created. I ran the script 'as posted' and tried ip:add, ip:remove, ip:list and ip:clear, all of them worked on my computer. I didn't try ip:list <name> however.

Try adding the line:

note typename(DBHandle)

to each of the command subs. You will need to comment these lines out later when you don't need them anymore, but the result *should* be 'Connection'. If it says anything else, especially 'Empty', then something is definitely going wrong in StartDB and you may have to delete the existing IPList.mdb file and try loading the plugin again so it can create a working version.

As for the quotes you 'fixed', there shouldn't have been any that are wrong. Since you haven't posted what you changed I can't comment on how or if you messed something up changing them. :p

I just did a comparison, the only thing I changed/added to the version posted here was a comment I added. This has no effect on the code itself. Odds are your IPList.mdb got corrupted or something similar and that is what is preventing it from working. If that isn't it, then I have no idea why it won't run on your machine.

Hmm.. This is wrong though in list:


  if Wilds(1) = "" then
    sQuery = "SELECT * FROM IPData ORDER BY ip_address"
  else
    sQuery = "SELECT * FROM IPData WHERE " & _
         "player_name like ""%" & wildcards (1) & "%"" " & _
         "OR ip_address like ""%" & wildcards (1) & "%"" " & _
         "ORDER BY ip_address"
  end if

it needs to be:

  if Wilds(1) = "" then
    sQuery = "SELECT * FROM IPData ORDER BY ip_address"
  else
    sQuery = "SELECT * FROM IPData WHERE " & _
         "player_name like ""%" & Wilds (1) & "%"" " & _
         "OR ip_address like ""%" & Wilds (1) & "%"" " & _
         "ORDER BY ip_address"
  end if

And these lines in Add:

  note player_name
  note ip_address

where for testing. They should really be commented out like:

  'note player_name
  'note ip_address

However, these are the 'only' errors I can find. Everything else works exactly as intended for me. Including ip:list <name>, once the above mistake is fixed.

Shadowfyr USA Thu 09 Oct 2003 09:08 PM #24

Hmm. Better yet, instead of adding the 'note typename(DBHandle)' lines, instead change the end of the 'StartDB' sub to this:



  if typename(DBHandle) <> "Connection" then
    world.ColourNote "white", "red", "Unable to connect to table."
    StartDB = -1
    exit function
  end if

  StartDB = 0 'Return with a code indicating everything was a success (hopefully).
end function

In other words, just add those additional lines. This won't fix the problem with the database that I suspect you are having, but it will display an error if the problem is in fact what I suspect. If it does display it, then odds are very good that your current IPList.mdb is bad and you needed to delete it, then try loading the plugin again to create a new one that works.

Xyborg Canada Fri 10 Oct 2003 12:35 AM #25

Deleting the IPList.mdb and then restarting the plugin worked perfectly. Didn't think about doing that. Thanks for everything. As for the problem with the quotes, it was in the clearlist alias. Other than that, it's working perfectly.

*bows respectfully*

Thank you again for your help.

Xyborg Canada Fri 10 Oct 2003 12:45 AM #26

Found a small problem. I get an error when I use ip:list (name) for some reason. I think it's because of the way the list is organized. Like, it's listed buy IP and the players under that IP.

[EDIT]
I'm an idiot. You posted the solution. Just ignore this post -_-

[EDIT2]
Although I can add the exact same IP to the database. Dunno if there is a way to stop that or not. So that I don't have duplicate entries.

Shadowfyr USA Fri 10 Oct 2003 01:04 AM #27

I intentionally allowed this. That is so that if you have say 4 people with the same IP, then doing 'ip:list 123.123.123.123' would list them like this:

123.123.123.123:
  Fred
  Grub
  Sam
  Ginger

It makes it easier to spot multiple attempts to use the same IP to log in, but with different names. Some people may have legitimate reasons for this, like multple computers on a network, but someone that gets banned may be dumb enough to simply try a new name from the same ISP. I assumed this was what you would need. You could instead go back and create the database over again making the ip_address as UNIQUE, don't remember the exact syntax for that though.

Xyborg Canada Sat 11 Oct 2003 07:48 PM #28

Yeah. That layout is great. Just I have things like:

123.123.123.123
Xyborg
Xyborg
Xyborg

That's the only thing I dislike about the script. Other than that, it's awsome. It does the job, with a little clutter, but still does the job.