Search the Site

Sponsors

bottom corner

Building an AIM buddy list from your database employee table with VBScript

This article is a sample of how you can use a SQL statement against a database table to generate an AOL Instant Messenger buddy list.

This page is filed under keyword(s): vbscript, oracle, sqlserver, mysql.

The code below can be used against Oracle, SQL Server, or MySQL database table to automatically create an AIM buddy list. The SQL statement should select out a "group_name" field (ideas: a department name such as "Accounting" or "Purchasing", an office location such as "Taipei" or "New York", etc.) and an "aim_name" field. The "group_name" will be used as AIM groups, while "aim_name" are the users' actual registered AIM names. The output file is a flat text file in a format that can be imported into AIM.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' AIM Buddy List Builder                                        '
'                                                               '
' Description: Builds an AIM buddy list from your database      '
'      employee table.                                          '
' Author: C. Peter Chen                                         '
' Version Tracker:                                              '
'       1.0   20081021   Base version                           '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "hostName"              ' Hostname of the database server
dbName = "dbName"                ' Name of the database/SID
dbUser = "user"                  ' Name of the user
dbPass = "password"              ' Password of the above-named user
outputFile = "c:\buddylist.blt"  ' Path and file name of the output CSV file

' SQL statement below; be sure to select out "group_name" and "aim_name" in your SQL statement.
sqlStr = "select department_name as group_name, aim_name from employees_table where aim_name is not null order by department_name, aim_name"
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''

dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
	conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
	conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
	conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if

dim a, showList, prevGroup

set a = fso.createtextfile(outputFile)
a.writeline ("Config {")
a.writeline (" version 1")
a.writeline ("}")
a.writeline ("User {")
a.writeline (" screenName dummyAimName")
a.writeline ("}")
a.writeline ("Buddy {")
a.writeline (" list {")

set showList = conn.execute(sqlstr)

prevGroup = "placeholder"
do while not showList.eof
	if (showList("group_name") <> prevGroup) then
		if (prevGroup <> "placeholder") then
			a.writeline ("  }")
		end if
		a.writeline ("  """ + showList("group_name") + """ {")
	end if
	a.writeline ("   " + showList("aim_name"))
	prevGroup = showList("group_name")
	showList.movenext
loop
showList.close
set showList = nothing

a.writeline ("  }")

a.writeline (" }")
a.writeline ("}")

' Close
set a = nothing
set fso = nothing
conn.close
set conn = nothing

'You're all done!!  Enjoy the file created.
msgbox("AIM Buddy List Created!")

Interested in obtaining a generic AIM buddy list import file format? Please see below for an illustration with fictional data.

Config {
 version 1
}
User {
 screenName dummyAimName
}
Buddy {
 list {
  "Accounting" {
   MrCFO_fictionalUser
   BobAtAccounting_fictionalUser
   JaneDoe_fictionalUser
  }
  "Purchasing" {
   LewisTheBuyer_fictionalUser
  }
  "Useless Employees" {
   PaulJohnson_fictionalUser
  }
 }
}

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): vbscript, oracle, sqlserver, mysql.
Author: C. Peter Chen
Last updated: 12 Nov 2008

bottom corner