Break On

$=SetOption("wrapateol","on")

$DATABASE = "C:\ACCESS\DB1.MDB" ; <--- change this

$DSN = "DATA SOURCE=$DATABASE; PROVIDER=MICROSOFT.JET.OLEDB.4.0;"

$SQL = "CREATE TABLE JTOKACH (NAME CHAR(50) PRIMARY KEY, ADDRESS CHAR(50), PHONE CHAR(20))"

$Connection = CreateObject("ADODB.Connection")

If $Connection
$Connection.ConnectionString = $DSN
$Connection.Open()
$=$Connection.Execute("$SQL")
If @ERROR
?"@SERROR"
Else
?"Table successfully created."
Endif
$Connection.Close()
$Connection = 0
Else
?"ADODB is not installed on this machine"
Endif

Exit 1

Ok, that makes sense. Good place to start.

Sometimes it helps to have an abstract mental picture of how things work. I'll tell you my mental picture in terms of how KiXtart COM works. I think of COM objects as sort of like smart DOS programs. For example, lets say you wanted to add a new user account to your local workstation. There are two ways to do it in kixtart:

1) Shell-out to the NET command
2) Use the ADSI COM object

So looking at the first method, we would code:

shell "%comspec% /c net user john.doe /add"

and what happens here ? net.exe is loaded into memory and run. net.exe then queries the command line for the parameters passed to it then simply creates the account. Then it exits, the process is unloaded from memory and your kixtart script carrys on. Pretty basic stuff eh ? If you wanted to create another user, you would have to shell out again and create another running instance of net.exe and so it goes, again and again. And the only way to communicate with these programs (specify what we want done) is with commandline parameters on the inbound side. And to extract information on the outbound side, use temporary textfiles and read them back into our script. You know the whole story

With COM, things are a little different. Instead of shelling-out and running the program every time you need it, you create an instance of the running program that is always "running" in the background, waiting at all times to recieve (service) our requests. And sending information in and out of the "program" is handled much, much more eloquently. Oh yeah, and instead of calling them programs, we call them objects

So with COM, instead of using the SHELL command, we use a kixtart function called CreateObject("progid"). For example, if I wanted to create a running instance of Internet Explorer, I would code this:

$IE = CreateObject("InternetExplorer.Application")

This one function does a whole lot. Here's the blow-by-blow play (might want to start regedit for this):

1) COM first looks in the registry for a key in HKEY_CLASSES_ROOT called "InternetExplorer.Application" (try it yourself manually to see if you can find it). If you crack open that key, there's a key called a CLSID which is basically an unique identifer for this application. Every COM object has one and they are all differnet from one another. They have to be or else COM couldn't locate your object. The string "InternetExplorer.Application" is just like a shortcut, or a friendly name for the object.

2) If you select this key into your clipboard, then go back up to the top and select HKEY_CLASSES_ROOT again, then do a find on this identifier, it should take you to a very strange place in the registry where all the identifiers are kept. Crack it open and look for LocalSever32 - whats the value there ? Look familiar ?

3) So then COM starts Internet Explorer but it puts it into a mode where it is continually running, in the background. If you look at taskmgr, you will see iexplore.exe running. So like using the SHELL command, CreateObject() just starts the program. But then here's where the fun starts.

So now iexplore.exe is running in the background, along side your kix script and it's just waiting for requests (from your script). How does one talk to the object ? Well if you remember, the CreateObject() function returns a variable, here it is again:

$ie = CreateObject("InternetExplorer.Application")

What is $ie ? $ie is a pointer to your running instance of Internet Explorer that you just created. It's like a channel or a pipe to the process (proper term is an address to an interface). Sometimes for whatever reason, the object your trying to create doesn't get created. We can test for this by querying the value of the pointer like this:

$ie = CreateObject("InternetExplorer.Application")
If $ie = 0
?"Some error occurred"
Endif

CreateObject() will either return the address of the object, or return a 0 (zero) if it fails. Try creating an object that doesn't exist and see what i mean. So this object pointer or interface pointer, in simple terms, consists of the following two things:

1) Properties
2) Methods

Properties are "attributes" of the currently running object. The syntax is like this:

Setting a property:

$objectname.propertyname = 1

Querying a property:

$kixvariable = $objectname.propertyname ; query a property

So we can set these attributes and/or we can query them. For example, IE has a property called "Visible" that tells wether the browser window is actually visible to the user or not (by default, when you create an IE object, it is *not* visible). I tend to think of properties as simply variables maintained by the object, inside the object itself. And in order for us to get at them, we have to specifically ask the object to set or get them for us. So, to make IE visible we must "set the Visible property", in this case to TRUE (1)

$ie = CreateObject("InternetExplorer.Application")
$ie.visible = 1

So not only does IE set it's internal variable "Visible" to 1 (on our behalf), it actually went ahead and made itself visible. So now that its visible, we can query the value of this property (read it back in) to see if it really did set it.

If $ie.Visible = 1
?"Yup, it is..."
Endif

or, since the value 1 is "true", we could just as easily code it this way:

If $ie.Visible
?"Yup, it is..."
Endif

Methods calls are simply function calls that you can invoke within the running instance of IE. The syntax of invoking a method call is like this:

$objectname.methodname(parm1,parm2,...)

So for example, continuing our IE script:

$ie = CreateObject("InternetExplorer.Application")

$ie.navigate("http://www.kixtart.org")

so we have just told IE to navigate to the best little KiXtart board on the Internet. There just like any other function call, but they are implemented inside the object.

And since the IE object is always running in the background, we need a way to stop it. We do that in kixtart like this:

$ie.quit() ; another method call - gracefully closes the browser
$ie = 0

Just set the variable to 0 (zero). What that does is to stop the running instance of iexplore.exe and unload it from memory.

So here's our little example script in it's full form:

break on
$ie = CreateObject("InternetExplorer.Application")
$ie.visible=1
$ie.navigate("http://www.kixtart.org")
gets$ ; just pause for now
$ie.quit
$ie = 0

My fingers hurt

-Shawn

[ 18 January 2002: Message edited by: Shawn ]

$SQL1 = "INSERT INTO TBL_USERS (USERNAME,FULLNAME,WORKSTATION,PRIVILEGELEVEL,HOMEDRIVE,LASTUPDATE) VALUES ('@USERID','@FULLNAME','@WKSTA','@PRIV','@HOMESHR','@DATE @TIME');"
$SQL2 = "INSERT INTO TBL_COMPUTERS (WORKSTATION,DOMAIN,IPADDRESS,MACADDRESS,OS,LOGGEDONUSER,LASTUPDATE) VALUES ('@WKSTA','@DOMAIN','@IPADDRESS0','@ADDRESS','@PRODUCTTYPE','@USERID','@DATE @TIME');"

;WMI script to write info to database at logon
;Created Feb 2002 by Neil Moran

Break On
CLS

;Time script execution
;? @TIME

;Query WMI to obtain info not available through standard KiXtart macros
;Many thanks to the authors of the WMIQuery UDF for this - two gents known as Radimus and Kent
FUNCTION WMIQuery($what,$where,)
dim $strQuery, $objEnumerator, $value
$strQuery = "Select $what From $where"
$SystemSet = GetObject("winmgmts:{impersonationLevel=impersonate}!//@WKSTA")
$objEnumerator = $SystemSet.ExecQuery($strQuery)
For Each $objInstance in $objEnumerator
If @Error = 0 and $objInstance <> ""
$x=execute("$$value = $$objInstance.$what")
$WMIQuery="$value"+"|"+"$WMIQuery"
EndIf
Next
$WMIQuery=left($WMIQuery,len($WMIQuery)-1)
exit @error
ENDFUNCTION

;Enumerate all disk info into arrays, then cross-reference for required info
$arrDeviceIDs = Split(WMIQuery("DeviceID","Win32_LogicalDisk"),"|",-1)
$arrFreeSpaces = Split(WMIQuery("FreeSpace","Win32_LogicalDisk"),"|",-1)
$arrTotalSizes = Split(WMIQuery("Size","Win32_LogicalDisk"),"|",-1)
$arrFormats = Split(WMIQuery("FileSystem","Win32_LogicalDisk"),"|",-1)

For $Counter = 0 To UBound($arrDeviceIDs)
If $arrDeviceIDs[$Counter] = "C:"
$C_DriveFormat = $arrFormats[$Counter]
$C_DriveFreeSpace = $arrFreeSpaces[$Counter]
$C_DriveTotalSize = $arrTotalSizes[$Counter]
$Counter = UBound($arrDeviceIDs) + 1
Endif
Next

;Sort the returned disk spaces values into MB
;$C_DriveFreeSpace = Val($C_DriveFreeSpace)/1048576
;$C_DriveTotalSize = Val($C_DriveTotalSize)/1048576

;First, set variables for the connection to the database and other general options
$=SetOption("WrapAtEOL","On")

$DATABASE = "\\corpfs01\rollout$$\logon.mdb"
$DSN="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DATABASE"

;Create SQL statements to check/write/update database
$CHECK_ENTRY_USERS = "SELECT * FROM TBL_USERS WHERE USERNAME='@USERID';"
$CHECK_ENTRY_COMPUTERS = "SELECT * FROM TBL_COMPUTERS WHERE WORKSTATION='@WKSTA';"

$Connection = CreateObject("ADODB.Connection")
$Command = CreateObject("ADODB.Command")
$Recordset = CreateObject("ADODB.Recordset")

;Check for connection object
if $Connection

;Open connection to database
$Connection.ConnectionString = $DSN
$Connection.Open()

;Check for existing records for the current workstation/user
;Add new record or update existing as required

;Check for User details first
$Command.ActiveConnection = $Connection
$Recordset.CursorType = 3
$Recordset.LockType = 3
$Recordset.ActiveCommand = $Command
$Command.CommandText = $CHECK_ENTRY_USERS
$Recordset.Open($Command)

;Create new record if none exists to update
If $Recordset.RecordCount < 1
$Recordset.AddNew
Endif

;Write user values into database field by field
$Recordset.Fields("UserName").Value = @USERID
$Recordset.Fields("FullName").Value = @FULLNAME
$Recordset.Fields("Workstation").Value = @WKSTA
$Recordset.Fields("PrivilegeLevel").Value = @PRIV
$Recordset.Fields("HomeDrive").Value = @HOMESHR
$Recordset.Fields("LastUpdate").Value = @DATE + " " + @TIME

;Update the new record and close the recordset object
$Recordset.Update
$Recordset.Close()

;Check Computer details next - refresh $Recordset object with new query
$Command.CommandText = $CHECK_ENTRY_COMPUTERS
$Recordset.Open($Command)

;Check for existing record to update
If $Recordset.RecordCount < 1
$Recordset.AddNew
Endif

;Write values into table
$Recordset.Fields("Workstation").Value = @WKSTA
$Recordset.Fields("NTDomain").Value = @DOMAIN
$Recordset.Fields("SystemManufacturer").Value = $SystemManufacturer
$Recordset.Fields("SystemModel").Value = $SystemModel
$Recordset.Fields("SerialNo").Value = $SerialNo
$Recordset.Fields("IPAddress").Value = @IPADDRESS0
$Recordset.Fields("MACAddress").Value = @ADDRESS
$Recordset.Fields("OS").Value = @PRODUCTTYPE
$Recordset.Fields("ServicePack").Value = @CSD
$Recordset.Fields("PhysicalMemory").Value = $PhysicalMemory
$Recordset.Fields("ProcessorSpeed").Value = $ProcessorSpeed
$Recordset.Fields("PagefileSpace").Value = $PagefileSpace
$Recordset.Fields("C riveFormat").Value = $C_DriveFormat
$Recordset.Fields("C riveFreeSpace").Value = $C_DriveFreeSpace
$Recordset.Fields("C riveTotalSize").Value = $C_DriveTotalSize
$Recordset.Fields("LoggedOnUser").Value = @USERID
$Recordset.Fields("LastUpdate").Value = @DATE + " " + @TIME

;Update and close recordset object
$Recordset.Update
$Recordset.Close()

;Close connection to database
$Connection.Close()

;Tidy up by releasing COM objects from memory
$Connection = 0
$Recordset = 0
$Command = 0
else
Goto error
endif

:end
;? @TIME
exit 321

:error
exit

$Recordset.Fields("UserName").Value = @USERID
$Recordset.Fields("FullName").Value = @FULLNAME
$Recordset.Fields("Workstation").Value = @WKSTA
$Recordset.Fields("PrivilegeLevel").Value = @PRIV
$Recordset.Fields("HomeDrive").Value = @HOMESHR
$Recordset.Fields("LastUpdate").Value = @DATE + " " + @TIME

Break On

;Retrieve info by calls to WMIQuery() function
$SystemManufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")
$SystemModel = WMIQuery("Model","Win32_ComputerSystem")
$SerialNo = WMIQuery("SerialNumber","Win32_BIOS")
$PhysicalMemory = val(WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration"))/1024
$ProcessorSpeed = WMIQuery("CurrentClockSpeed","Win32_Processor") + " Mhz"
$PagefileSpace = val(WMIQuery("TotalPageFileSpace","Win32_LogicalMemoryConfiguration"))/1024
;Enumerate all disk info into arrays, then cross-reference for required info
$arrDeviceIDs = Split(WMIQuery("DeviceID","Win32_LogicalDisk"),"|",-1)
$arrFreeSpaces = Split(WMIQuery("FreeSpace","Win32_LogicalDisk"),"|",-1)
$arrTotalSizes = Split(WMIQuery("Size","Win32_LogicalDisk"),"|",-1)
$arrFormats = Split(WMIQuery("FileSystem","Win32_LogicalDisk"),"|",-1)
For $Counter = 0 To UBound($arrDeviceIDs)
If $arrDeviceIDs[$Counter] = "C:"
$C_DriveFormat = $arrFormats[$Counter]
$C_DriveFreeSpace = $arrFreeSpaces[$Counter]
$C_DriveTotalSize = $arrTotalSizes[$Counter]
$Counter = UBound($arrDeviceIDs) + 1
Endif
Next
;Sort the returned disk spaces values into MB
;$C_DriveFreeSpace = Val($C_DriveFreeSpace)/1048576
;$C_DriveTotalSize = Val($C_DriveTotalSize)/1048576
;First, set variables for the connection to the database and other general options
$=SetOption("WrapAtEOL","On")
$DATABASE = "$logon\inventory\inventory.mdb"
$DSN="Driver={Microsoft Access Driver (*.mdb)}; DBQ=$DATABASE"
;Create SQL statements to check/write/update database
$CHECK_ENTRY_USERS = "SELECT * FROM TBL_USERS WHERE USERNAME='@USERID';"
$CHECK_ENTRY_COMPUTERS = "SELECT * FROM TBL_COMPUTERS WHERE WORKSTATION='@WKSTA';"
$Connection = CreateObject("ADODB.Connection")
$Command = CreateObject("ADODB.Command")
$Recordset = CreateObject("ADODB.Recordset")
;Check for connection object
if $Connection
? "i'm in there"
;Open connection to database
$Connection.ConnectionString = $DSN
$Connection.Open()
;Check for existing records for the current workstation/user
;Add new record or update existing as required
;Check for User details first
$Command.ActiveConnection = $Connection
$Recordset.CursorType = 3
$Recordset.LockType = 3
$Recordset.ActiveCommand = $Command
$Command.CommandText = $CHECK_ENTRY_USERS
$Recordset.Open($Command)
;Create new record if none exists to update
If $Recordset.RecordCount < 1
? "adding new user record"
$Recordset.AddNew
Endif
;Write user values into database field by field
$Recordset.Fields("UserName").Value = @USERID
$Recordset.Fields("FullName").Value = @FULLNAME
$Recordset.Fields("Workstation").Value = @WKSTA
$Recordset.Fields("PrivilegeLevel").Value = @PRIV
$Recordset.Fields("HomeDrive").Value = @HOMESHR
$Recordset.Fields("LastUpdate").Value = @DATE + " " + @TIME
;Update the new record and close the recordset object
$Recordset.Update
$Recordset.Close()
;Check Computer details next - refresh $Recordset object with new query
$Command.CommandText = $CHECK_ENTRY_COMPUTERS
$Recordset.Open($Command)
;Check for existing record to update
If $Recordset.RecordCount < 1
? "adding new computer record"
$Recordset.AddNew
Endif
;Write values into table
$Recordset.Fields("Workstation").Value = @WKSTA
$Recordset.Fields("NTDomain").Value = @DOMAIN
$Recordset.Fields("SystemManufacturer").Value = $SystemManufacturer
$Recordset.Fields("SystemModel").Value = $SystemModel
$Recordset.Fields("SerialNo").Value = $SerialNo
$Recordset.Fields("IPAddress").Value = @IPADDRESS0
$Recordset.Fields("MACAddress").Value = @ADDRESS
$Recordset.Fields("OS").Value = @PRODUCTTYPE
$Recordset.Fields("ServicePack").Value = @CSD
$Recordset.Fields("PhysicalMemory").Value = $PhysicalMemory
$Recordset.Fields("ProcessorSpeed").Value = $ProcessorSpeed
$Recordset.Fields("PagefileSpace").Value = $PagefileSpace
$Recordset.Fields("C_DriveFormat").Value = $C_DriveFormat
$Recordset.Fields("C_DriveFreeSpace").Value = $C_DriveFreeSpace
$Recordset.Fields("C_DriveTotalSize").Value = $C_DriveTotalSize
$Recordset.Fields("LoggedOnUser").Value = @USERID
$Recordset.Fields("LastUpdate").Value = @DATE + " " + @TIME
;Update and close recordset object
$Recordset.Update
$Recordset.Close()
$Connection.Close()
$Connection = 0
$Recordset = 0
$Command = 0
else
Goto error
endif
:end

exit 321
:error
exit\