Gammon Forum

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

<?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>

  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

  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

123.123.123.123:
  Fred
  Grub
  Sam
  Ginger