The WinOps Blog

'#################################
' SQLQuery.vbs
' Use to Query SQL DataBase
' command line:
' cscript SQLQuery.vbs /DataSourceName:%DataSourceName% /ServerName:%ServerName% /Database:%Database% /ADOTable:%ADOTable% /column:%column% /search:%search%
' or
' cscript SQLQuery.vbs /DataSourceName:%DataSourceName% /username:%username% /password:%password% /ServerName:%ServerName% /Database:%Database% /ADOTable:%ADOTable% /column:%column% /search:%search%
'
' or arguments given in the script

Dim DataSourceName,ServerName,Database,column,ADOTable
Dim usercredentials,username,password
Dim strquery,search
Dim logfile,here

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

here = left(WScript.ScriptFullName,len(WScript.ScriptFullName)-len(WScript.ScriptName)-1)

'####################
'SCRIPT INFORMATIONS
'####################
'Logfile:
logfile = here & "\logfile.txt"

dim i, Args, valdata

checkingarguments()

'if you want to assign connector for all action you can assign it here:
'for example to assign then server name and after you only write the command line without assign the server in the arguments
if valdata = "OK" then
    if not valADOTable = "OK" then
    'Table Name:
        ADOTable = ""
    end if
    if not valcolumn = "OK" then
    'column retrieve:
        column = ""
    end if
    if not valDatabase then
    'Database Name:
        Database = ""
    end if
    if not valDataSourceName then
    'ODBC DataSourceName:
        DataSourceName = ""
    end if
    if not valsearch = "OK" then
    'index line condition:
        search = ""
    end if
    if not valServerName = "OK" then
    'Server Name:
        ServerName = ""
    end if
    if not valusername = "OK" then
    'User Credentials:
        username = ""
    end if
    if not valpassword = "OK" then
    'User Credentials:
        password = ""
    end if
else
    'ODBC DataSourceName:
    DataSourceName = ""
    'User Credentials:
    username = ""
    password = ""
    'Server Name:
    ServerName = ""
    'Database Name:
    Database = ""
    'Table Name:
    ADOTable = ""
    'column retrieve:
    column = ""
    'index line condition:
    search = ""
end if

condition = " WHERE id='" & search & "'"

   
dim databaseconnector
    if isnull(username) = TRUE _
    and isnull(password) = TRUE then
    databaseconnector = "DSN=" & DataSourceName & ";" & _
                           "DRIVER=" & "{SQL Server}" & ";" & _
                           "Server=" & ServerName & ";" & _
                           "Database=" & Database & ";"
    else
    databaseconnector = "DSN=" & DataSourceName & ";" & _
                           "Uid=" & username &";" & _
                           "Pwd=" & password & ";" &_
                           "DRIVER=" & "{SQL Server}" & ";" & _
                           "Server=" & ServerName & ";" & _
                           "Database=" & Database & ";"
    end if

'############
'MAIN SCRIPT
'############

'SQL Query:
strquery = "SELECT " & column & " FROM " & ADOTable & condition

'Connexion to Database:
ConnecDatabase()

'Execute SQL Query:
searchbdd(strquery)

'####################################### SUBs ##########################################""

'--------------------------------------------------------------------------------------------------------
' Connect to ServerName, with DataSourceName, to Database, to ADOTable with or without username/password
'
sub ConnecDatabase()
objConnection.Open databaseconnector
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM " & ADOTable , objConnection, adOpenStatic, adLockOptimistic
objRecordset.Close
objConnection.Close
end sub

'-----------------------------------------------------------------
'Search info from the SQLQUERY define into the connected ADOTable
'
function searchbdd(strquery)
dim valfullname
objConnection.Open "DSN=" & DataSourceName & ";"
objRecordset.CursorLocation = adUseClient
objRecordset.Open strquery, objConnection
    If objRecordset.EOF Then
        trace column & " from " & ADOTable & condition & " is NULL : cannot be found."
    Else
        trace column & " from " & ADOTable & condition & " is " & objRecordset.Fields.Item(column)
    End If
objRecordset.Close
objConnection.Close
end function

'echo and log retrieved info
function trace(value)
Dim fso, oLog
Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oLog = fso.OpenTextFile(logfile, ForAppending, True)
        oLog.WriteLine(value)
        oLog.Close
    Wscript.Echo value
end function

sub checkingarguments()
Dim valADOTable,valcolumn,valDatabase,valDataSourceName,valpassword,valsearch,valServerName,valusername
On Error Resume Next
valdata = "KO"

Set args = Wscript.Arguments
for i = 0 to 10
    if instr(args(i),"DataSourceName")>0 then
        DataSourceName = split(args(i),":")(1)
        valDataSourceName = "OK"
    elseif instr(args(i),"ADOTable")>0 then
        ADOTable = split(args(i),":")(1)
        valADOTable = "OK"
    elseif instr(args(i),"column")>0 then
        column = split(args(i),":")(1)
        valcolumn = "OK"
    elseif instr(args(i),"Database")>0 then
        Database = split(args(i),":")(1)
        valDatabase = "OK"
    elseif instr(args(i),"password")>0 then
        password = split(args(i),":")(1)
        valpassword = "OK"
    elseif instr(args(i),"search")>0 then
        search = split(args(i),":")(1)
        valsearch = "OK"
    elseif instr(args(i),"ServerName")>0 then
        ServerName = split(args(i),":")(1)
        valServerName = "OK"
    elseif instr(args(i),"username")>0 then
        username = split(args(i),":")(1)
        valusername = "OK"
    end if
Next

if valADOTable = "OK" _
and valcolumn = "OK" _
and valDatabase = "OK" _
and valDataSourceName = "OK" _
and valsearch = "OK" _
and valServerName = "OK" then
    valdata = "OK"
end if
end sub