Find the DB Names on a Remote Windows Server using VBS

December 12, 2009

The following question appeared in one of the OTN forums a couple months ago:
http://forums.oracle.com/forums/thread.jspa?threadID=958849 

I am new in this database field.
I have a small query that how can i find the number of database running on a particular host in windows environment by query?

 There is no query in Oracle which will show you the database instances on a Windows server. That said, here is a portion (adaptation) of a WMI script from the book “Expert Oracle Practices: Oracle Database Administration from the Oak Table” which will do it for you as long as the database instance is started (even on a remote server if you have administrator privileges on the remote server):

Dim objWMIService
Dim strSQL
Dim strComputer
Dim colItems
Dim objItem

strSQL = "SELECT * FROM Win32_Process Where Name like 'Oracle%'"

strComputer = "."  ' the . indicates the local computer
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\CIMV2")

Set colItems = objWMIService.ExecQuery(strSQL,"WQL",48)

For Each objItem in colItems
  Wscript.Echo "Instance: " & objItem.CommandLine & vbCrLf
Next

To run the above script, save it as a text file with the extension .VBS. Open a Windows command prompt, and type cscript followed by the name of the script:

C:\> cscript c:\databases.vbs

Note that you must be an administrator on the remote computer (or a domain administrator) to remotely query the running processes on another Windows computer. 

Print Oracle Data to a Barcode Label with a Zebra Printer using VBS and Excel

December 12, 2009

Here is the problem: You know how to query the database using a Windows Scripting Host VBS file, but you have no way to print a barcode label using the data to the networked Zebra label printer and there is no printer driver installed for the Zebra printer.  Fortunately, Zebra printers understand ZPL (Zebra Printer Langauge), so we are able to easily work around the issue of not having a driver for the printer installed.  The second problem is how do we send the print job to the printer?  This is where Excel comes to the rescue.  Excel is able to work with the Windows API to start and submit a print job to a printer that is not necessarily installed on the local computer.

First, the Windows Scripting Host VBS file (note that this uses a DLL that I wrote which hides the database name, username, and password, as well as simplifying the process of submitting SQL statements with bind variables to the database):

'PrintLabel.vbs
Dim intParentPartLine
Dim strRawText
Dim strSQL
Dim strWhereUsed
Dim snpData
Dim OracleSQL
Dim objExcel

On Error Resume Next

Set OracleSQL = CreateObject("VMDBOracle.SQLProcessor") 'This is my custom DLL
Set snpData = CreateObject("ADODB.Recordset")
Set objExcel = CreateObject("Excel.Application")

strOut = ""
With objExcel
    'Open the Excel file containing the macro functions
    .Workbooks.Open "C:\ExcelMacroFunction.xls"

    strRawText = "~SD25^XA" 'Set Darkness, Label start
    strRawText = strRawText & "^SZ2" 'Enable ZPL2
    strRawText = strRawText & "^JZ" 'Reprint on error
    strRawText = strRawText & "^PR8,8,8" 'Print speed 8" per second, 8" per sec slew, 8" per sec backfeed
    strRawText = strRawText & "^LH12,30" 'Label home position in pixels
    strRawText = strRawText & "^FO5,0^A0,40,40^FD" & ID & "^FS" 'Proportional font
    strRawText = strRawText & "^FO5,40^A0,20,20^FD" & DESCRIPTION & "^FS" 'Proportional font
    strRawText = strRawText & "^FO5,80^A0,15,15^FD**** WHERE USED ****^FS" ' 'Proportional font

    strSQL = "SELECT DISTINCT" & vbCrLf
    strSQL = strSQL & "  R.WORKORDER_BASE_ID AS TOP_PART_ID," & vbCrLf
    strSQL = strSQL & "  WO.PART_ID AS SUB_PART_ID" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  REQUIREMENT R," & vbCrLf
    strSQL = strSQL & "  WORK_ORDER WO" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  WO.TYPE='M'" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_TYPE='M'" & vbCrLf
    strSQL = strSQL & "  AND R.PART_ID= ?" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_TYPE=WO.TYPE" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_BASE_ID=WO.BASE_ID" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_LOT_ID=WO.LOT_ID" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_SPLIT_ID=WO.SPLIT_ID" & vbCrLf
    strSQL = strSQL & "  AND R.WORKORDER_SUB_ID=WO.SUB_ID" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  R.WORKORDER_BASE_ID," & vbCrLf
    strSQL = strSQL & "  WO.PART_ID"
    OracleSQL.SQL = strSQL
    OracleSQL.SetParameter ID, "VARCHAR"

    Set snpData = OracleSQL.Execute

    If Not (snpData Is Nothing) Then
        If Not (snpData.EOF) Then
            intParentPartLine = 0
            Do While Not (snpData.EOF)
                intParentPartLine = intParentPartLine + 1
                If snpData("top_part_id") <> snpData("sub_part_id") Then
                    strWhereUsed = strWhereUsed & cStr(snpData("top_part_id")) & " (Sub " & cStr(snpData("sub_part_id")) & ")"
                Else
                    strWhereUsed = strWhereUsed & cStr(snpData("top_part_id"))
                End If
                strRawText = strRawText & "^FO10," & cStr(80 + 25 * intParentPartLine) & "^A0,25,25^FD" & strWhereUsed & "^FS" 'Proportional font
                strWhereUsed  = ""

                snpData.MoveNext
            Loop
        End If

        snpData.Close
    End If

    strRawText = strRawText & "^FO20,562^AF^FDMy Company Here^FS"
    strRawText = strRawText & "^XZ"  'End of label indicator

    'Excute a macro located in mdlGlobal that prints a label using API calls
                                 'Subroutine, printer device name, raw ZPL code

    'Print to a shared Zebra printer named ZEBRA on computer named KMMACH98
    'strResult = .Application.Run("ZebraPrintLabel", "\\KMMACH98\ZEBRA", strRawText)

    'Print to a "Local" printer named ZEBRA_M1 with a redirected LTP2 port with NET USE
    strResult = .Application.Run("ZebraPrintLabel", "ZEBRA_M1", strRawText)

    .DisplayAlerts = False
    .ActiveWorkbook.Saved = True
    .Quit
End With

Set objExcel = Nothing 
Set OracleSQL = Nothing
Set snpData = Nothing
'End of PrintLabel.vbs

The above script expects to find an Excel spreadsheet named C:\ExcelMacroFunction.xls with a public subroutine named  ZebraPrintLabel in a regular Excel code module.

The code in the Excel code module looks like this:

'mdlGlobal in C:\ExcelMacroFunction.xls - a global module
Option Explicit

'Type declaration for Zebra label printing
Public Type DOC_INFO_1
    pDocName As String
    pOutputFile As String
    pDatatype As String
End Type

'Zebra Printing API functions
Public Declare Function ClosePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function EndDocPrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function EndPagePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function OpenPrinter Lib "winspool.drv" Alias "OpenPrinterA" (ByVal pPrinterName As String, phPrinter As Long, ByVal pDefault As Long) As Long
Public Declare Function StartDocPrinter Lib "winspool.drv" Alias "StartDocPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, pDocInfo As DOC_INFO_1) As Long
Public Declare Function StartPagePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
Public Declare Function WritePrinter Lib "winspool.drv" (ByVal hPrinter As Long, pBuf As Any, ByVal cdBuf As Long, pcWritten As Long) As Long

Public Sub ZebraPrintLabel(strPrinter As Variant, strRawText As Variant)
    'Variables for handling printing
    Dim i As Integer
    Dim lngPrinterHandle As Long
    Dim lngResult As Long
    Dim lngWritten As Long
    Dim lngPrinterDocHandle As Long
    Dim bytRawText() As Byte
    Dim MyDocInfo As DOC_INFO_1

    On Error Resume Next

    'In VB6 to see the list of printer device names, enter the following into the Debug window
    'For i = 0 to Printers.Count - 1:Debug.Print Printers(i).DeviceName:Next

    'Sample label for testing using just Excel
    'strPrinter = "\\KMMACH98\ZEBRA"
    'strRawText = "~SD25^XA" 'Set Darkness, Label start
    'strRawText = strRawText & "^SZ2" 'Enable ZPL2
    'strRawText = strRawText & "^JZ" 'Reprint on error
    'strRawText = strRawText & "^PR8,8,8" 'Print speed 8" per second, 8" per sec slew, 8" per sec backfeed
    'strRawText = strRawText & "^LH10,26" 'Label home position in pixels
    'strRawText = strRawText & "^FO2,14^A0R,20,20^FDMy Company Here^FS" 'rotated text in font A
    'strRawText = strRawText & "^FO2,480^A0R,20,20^FDSomwhere, USA^FS"
    'strRawText = strRawText & "^FO180,135^B3R,,105,N^FD" & "ABC111" & "^FS"  'Font 3 of 9 barcode
    'strRawText = strRawText & "^FO180,0^GB0,760,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO335,0^GB0,1218,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO550,0^GB0,1218,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO260,760^GB0,452,3^FS"  'Vertical Line 3 pixels wide
    'strRawText = strRawText & "^FO0,760^GB335,0,3^FS"  'Horizontal Line 3 pixels wide
    'strRawText = strRawText & "^XZ"  'End of label indicator
    'Convert the variant data types to strings
    strPrinter = CStr(strPrinter)
    strRawText = CStr(strRawText)

    'Terminate the string with a CRLF combination (may not be needed)
    If Right(strRawText, 2) <> vbCrLf Then
        strRawText = strRawText & vbCrLf
    End If

    'Convert the strRawText string to a byte stream
    ReDim bytRawText(1 To Len(strRawText))
    For i = 1 To Len(strRawText)
        bytRawText(i) = Asc(Mid(strRawText, i, 1))
    Next i

    'Create a connection to the printer, returns a handle to the printer
    lngResult = OpenPrinter(strPrinter, lngPrinterHandle, 0)

    If lngPrinterHandle = 0 Then
        MsgBox "Could Not Open Printer"
        Exit Sub
    End If

    'Fill in the document header structure
    MyDocInfo.pDocName = "Zebra Label from Excel"
    MyDocInfo.pOutputFile = vbNullString
    MyDocInfo.pDatatype = "RAW"
    lngPrinterDocHandle = StartDocPrinter(lngPrinterHandle, 1, MyDocInfo)

    If lngPrinterDocHandle = 0 Then
        MsgBox "Could Not Start the Document"
        lngResult = ClosePrinter(lngPrinterHandle)
        Exit Sub
    End If

    'Prepare to start the first page
    Call StartPagePrinter(lngPrinterHandle)

    'Write out the contents of the first page
    lngResult = WritePrinter(lngPrinterHandle, bytRawText(1), Len(strRawText), lngWritten)
    If lngResult = 0 Then
        MsgBox "Could Not Write to the Page"
        lngResult = ClosePrinter(lngPrinterHandle)
        Exit Sub
    End If

    'End the first page
    lngResult = EndPagePrinter(lngPrinterHandle)

    'End the document
    lngResult = EndDocPrinter(lngPrinterHandle)

    'Close the connection to the printet
    lngResult = ClosePrinter(lngPrinterHandle)
End Sub
'End of mdlGlobal

Note that while the above uses a custom DLL to connect to the database and submit SQL statements, the Simple VBS Script to Retrieve Data from Oracle blog entry shows how to do the same without using the custom DLL.  Also, it probably is not necessary to convert the Unicode string that is passes into the procedure into a byte stream for the Windows API call.

The printed label appears below:

Simple VBS Script to Retrieve Data from Oracle

December 12, 2009

All recent releases of the Windows operating system include the Windows Scripting Host, which allows executing program commands that look much like the macro language used in Excel, Access, and Word, as well as the original line of Microsoft’s Visual Basic (before the introduction of .Net).  Scripts intended to be excuted by the Windows Scripting Host have an extension of .VBS and are executed either with cscript that outputs to a command line window, or the default wscript that outputs to Windows popup messages.

A simple script that connects to an Oracle database (without using ODBC), queries a table, and then performs a comparison on the values retrieved from the table follows:

'Save as ConnectDB.vbs
Dim strSQL
Dim strUsername
Dim strPassword
Dim snpData
Dim dbMyDBConnection
Dim ORDER_ID         'Note that this variable was omitted originally as it was automatically supplied by an ERP package

Set snpData = CreateObject("ADODB.Recordset")
Set dbMyDBConnection = CreateObject("ADODB.Connection")
strUsername = "MyUser"
strPassword = "MySecret"

dbMyDBConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyDB;User ID=" & strUsername & ";Password=" & strPassword & ";"
dbMyDBConnection.Open

ORDER_ID = "MYORDERID123"  'Note that this variable value was omitted originally as it was automatically supplied by an ERP package
strSQL = "SELECT C1, C2 FROM MY_TABLE WHERE PURC_ORDER_ID='" & ORDER_ID & "'"

snpData.Open strSQL, dbMyDBConnection

If Not(snpData.EOF) Then
    If cInt(snpData("c1")) < cInt(snpData("c2")) Then
        MsgBox "C1 is Less than C2"
    End If
    If cInt(snpData("c1")) > cInt(snpData("c2")) Then
        MsgBox "C1 is Greater than C2"
    End If
    If cInt(snpData("c1")) = cInt(snpData("c2")) Then
        MsgBox "C1 is Equal to C2"
    End If

    MsgBox cInt(snpData("c1")) - cInt(snpData("c2"))
Else
    MsgBox "Ut oh, No Matching Records"
End If

snpData.Close
dbMyDBConnection.Close

Set snpData = Nothing
Set dbMyDBConnection = Nothing

Once the script is saved, double-clicking the script should automatically execute it using wscript.  Alternatively, open a Windows command prompt and type the following (assuming that the script is named ConnectDB.vbs and is saved in the root of the C:\ drive):

cscript c:\ConnectDB.vbs