Evaluate all new entries in SQL table

[eddymicro]

Hello Alex,

I have a error log table in an SQL database. I want to monitor all the records in this table as soon as they are added. The table has columns

autonumber
errorID
error description
error date time stamp

I want to evaluate each new entry since the last time I ran the test. For example I run the test every 30 seconds, during that time 5 new records are added, I need to check each of the 5 records and have all 5 added to the log of that test. When I run the test again, I only want to look at records that are newly created, so the next time I run they test there might be 10 new records, plus the five from the first time, but I only want to look at the 10 new records and log them all.

Do you have a suggestion on how I could do this?

Regards,

Ed

[KS-Soft Europe]

It sounds like you need to copy records from SQL table to log file from time to time.
The only possible solution in this case is to use custom made script.
That script should:
1. read last old record ID from file (e.g. from text file)
2. read SQL table, starting from last old record ID
3. write found records to log file
4. save last new record ID to text file

Shell Script, Active Script or External test methods can be used to run custom made scripts.

[KS-Soft Europe]

Custom made VBScript for Shell Scritp test method may look like the following:

Code: Select all

Option Explicit

const RecordIDFile	= "D:\SQL\RecordID.txt"
const LogFile		= "D:\SQL\LOG.txt"
const SQLConnectionString = "Provider=SQLOLEDB;Data Source=DELL\SQLEXPRESS;Trusted_Connection=Yes;Initial Catalog=hmlog3;"

const statusAlive       = "scriptRes:Host is alive:"
const statusDead        = "scriptRes:No answer:"
const statusUnknown     = "scriptRes:Unknown:"
const statusNotResolved = "scriptRes:Unknown host:"
const statusOk          = "scriptRes:Ok:"
const statusBad         = "scriptRes:Bad:"
const statusBadContents = "scriptRes:Bad contents:"
Const fsoForReading = 1
Const fsoForWriting = 2
Const fsoForAppending = 8

'---- entry point ----

dim i, k, objArgs, mConnection, objRecordset, r, LID

Set objArgs = WScript.Arguments


Sub LogRecords(filename, text)
  Dim fso, f
  Set fso = CreateObject("Scripting.FileSystemObject")

  If fso.FileExists(filename) = false then
    fso.CreateTextFile(filename)
  end if

  Set f = fso.OpenTextFile(filename, fsoForAppending)
  f.Write text

End Sub

function getrecords(fromID, filename)

Set mConnection = CreateObject("ADODB.Connection")
mConnection.Open SQLConnectionString
Set objRecordset = CreateObject("ADODB.Recordset")
objRecordset.CursorLocation = 3
objRecordset.Open "SELECT autonumber, errorID, errordescription FROM errorlog WHERE autonumber>"&fromID, mConnection

i=0
k=0

while (not objRecordset.EOF)
  r = r & objrecordset("autonumber") & vbTab & objrecordset("errorID") & vbTab &  objrecordset("errordescription") & vbCrLf
  if objrecordset("autonumber")>i then i=objrecordset("autonumber") End IF
  k = k + 1
  objRecordset.movenext
wend

if i>0 then
  LogRecords filename, r
  WScript.StdOut.Write statusBad & k
else
  WScript.StdOut.Write statusOk & k
End If

objRecordset.Close
mConnection.Close

Set mConnection = Nothing
Set objRecordset = Nothing

getrecords = i
End Function

Function LoadIDFromFile(filename)
  Dim fso, f
  Set fso = CreateObject("Scripting.FileSystemObject")
  If fso.FileExists (filename) then
    Set f = fso.OpenTextFile(filename, fsoForReading)
    LoadIDFromFile = f.ReadALL
  else
    fso.CreateTextFile(filename)
    LoadIDFromFile = 0
  end if
End Function

Sub SaveIDToFile(filename, text)
  Dim fso, f
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set f = fso.OpenTextFile(filename, fsoForWriting)
  f.Write text
End Sub

LID = LoadIDFromFile(RecordIDFile)
IF LID<0 then LID=0 END IF

LID = getrecords(LID,LogFile)
IF LID>0 then
  SaveIDToFile RecordIDFile,LID
End IF

Start CMD: cmd /c cscript /B /E:VBScript %Script% %Params%

Please note: you should define the following constants, before using this script sample: RecordIDFile, LogFile and SQLConnectionString.
Also, script uses hardcoded table name and fieldnames, you may change them, according to your table structure.

[eddymicro]

Hello,

Thank you for that great example. I will try it and post back on how it works.

Regards

Ed