Effortless workspace delivery
In this one, I want to share a script that helps me to deliver Siebel workspaces. This time I'll start with the source code and then I'll tell you how to use it.
Source code
- Parameters.vbs ( Download | Snippet ) - file with all parameters.
- WSDelivery.wsf ( Download | Snippet ) - file to run on 32-bit system.
- WSDelivery.bat ( Download | Snippet ) - file to run on 64-bit system.
- WSDelivery.zip ( Download ) - all at once in an archive.
Const sODBC = "SBL DEV Server DSN" ' ODBC32 name should be using OraClient. When using "Siebel Oracle" driver there'll be an annoying copyright message.
Const sUsername = "approver" ' DB user name
Const sPassword = "pass123" ' DB password
Const sTools = """C:\Siebel\Tools\BIN\siebdev.exe"" /c ""C:\Siebel\Tools\bin\enu\tools_dev.cfg"" /d ServerDataSrc" 'Siebel tools path along with some params, be careful with quote escaping
Const sLog = "C:\Siebel\Tools\LOG" ' Logs directory if you want it to be opened delivery failed
Const sDefWS = "dev_vbabkin_*" ' Default workspace name prefix to ease typing
Const sWSComment = "Delivered by VB" ' Delivery comment - usually your name/username if using a common system user for delivery <job id = "WSDelivery">
<script language="VBScript" src="Parameters.vbs"/>
<script language="VBScript">
'@desc Siebel workspaces delivery "automation"
'@version 1.7 (20190914)
'@author VB (http://xapuk.com)
'@example For the sake of using x32 ODBC drivers, should be ran through x32 bit version of VBS engine ...windows\sysWOW64\cscript
Dim sSQL1, sSQL2, sSQL3
Dim sConnection, dbConnection, snpData
Dim objShell
Dim sWS
Dim sId
Dim sMsg
Sub Deliver
Set objShell = WScript.CreateObject("WScript.Shell")
' create objects first, if failed , there is no point in moving forward
Set snpData = CreateObject("ADODB.Recordset")
Set dbConnection = CreateObject("ADODB.Connection")
Set objShell = WScript.CreateObject("WScript.Shell")
' establish DB connection
sConnection = "Data Source=" & sODBC & "; User ID=" & sUsername & "; Password=" & sPassword & ";"
Err.Clear
On Error Resume Next
dbConnection.ConnectionString = sConnection
dbConnection.Open
' to handle DB connection errors
If Err.Number <> 0 Then
sMsg = Err.Description & chr(10) & chr(10) & sConnection & chr(10) & chr(10) & "Please open Parameters.vbs file to change connection parameters."
MsgBox sMsg, 0, "Siebel workspace delivery"
Else
On Error GoTo 0
' Input WS name
sWS = LCase(InputBox("Please enter a WS name", "Siebel workspace delivery", sDefWS))
If sWS > "" Then
' if WS name has * search with like and only Submitted For Delivery
If InStr(1, sWS, "*", 1) > 0 Then
sSQL2 = "select name, row_id from siebel.S_WORKSPACE where name like '" & Replace(sWS, "*", "%") & "' and status_cd = 'Submitted for Delivery'"
snpData.Open sSQL2, dbConnection
If Not(snpData.EOF) Then
sId = snpData("row_id")
sWS = snpData("name") ' Get a full name of the workspace
sSQL1 = "select row_id, status_cd from siebel.S_WORKSPACE where name = '" & sWS & "'"
snpData.MoveNext() ' Check if there are more than one match
If Not(snpData.EOF) Then
MsgBox "Multiple records found. Please specify the query.", 0, sWS
snpData.Close
Exit Sub
End If
End If
snpData.Close
Else ' otherwise look for exact name match
sSQL1 = "select row_id, status_cd from siebel.S_WORKSPACE where name = '" & sWS & "'"
snpData.Open sSQL1, dbConnection
' verify the WS
If Not(snpData.EOF) Then
sStatus = snpData("status_cd")
sId = snpData("row_id")
snpData.Close
If sStatus <> "Submitted for Delivery" Then
MsgBox "Wrong status (" + sStatus + ")", 0, sWS
Exit Sub
End If
End If
End If
' if WS is found, delivering
If sId > "" Then
' this is a basic SQL with a list of objects in WS. Feel free to enchance it with information of importance
'sSQL3 = "select v.VERSION_NUM, o.OBJ_TYPE, o.OBJ_NAME from siebel.S_REPOSITORY r join siebel.S_WORKSPACE w on r.ROW_ID = w.REPOSITORY_ID join siebel.S_WS_VERSION v on w.ROW_ID = v.WS_ID and r.ROW_ID = v.REPOSITORY_ID join siebel.S_RTC_MOD_OBJ o on o.WS_VER_ID = v.ROW_ID and r.ROW_ID = o.REPOSITORY_ID where r.name = 'Siebel Repository' and w.row_id = '" & sId & "' order by 1,2,3;"
' I'm only curious about scripts :)
sSQL3 = "select v.VERSION_NUM, o.OBJ_TYPE, o.OBJ_NAME, GREATEST(nvl(max(s1.name),'-'), nvl(max(s2.name),'-'), nvl(max(s3.name),'-')) SCRIPT from siebel.S_REPOSITORY r join siebel.S_WORKSPACE w on r.ROW_ID = w.REPOSITORY_ID join siebel.S_WS_VERSION v on w.ROW_ID = v.WS_ID and r.ROW_ID = v.REPOSITORY_ID join siebel.S_RTC_MOD_OBJ o on o.WS_VER_ID = v.ROW_ID and r.ROW_ID = o.REPOSITORY_ID left join siebel.S_APPLICATION o1 on o1.name = o.OBJ_NAME and r.row_id = o1.REPOSITORY_ID and o.OBJ_TYPE = 'Application' left join siebel.S_APPL_SCRIPT s1 on s1.APPLICATION_ID = o1.ROW_ID and s1.WS_ID = w.row_id left join siebel.S_APPLET o2 on o2.name = o.OBJ_NAME and r.row_id = o2.REPOSITORY_ID and o.OBJ_TYPE = 'Applet' left join siebel.S_APPL_WEBSCRPT s2 on s2.APPLET_ID = o2.row_id and s2.WS_ID = w.row_id left join siebel.S_BUSCOMP o3 on o3.name = o.OBJ_NAME and r.row_id = o3.REPOSITORY_ID and o.OBJ_TYPE = 'Business Component' left join siebel.S_BUSCOMP_SCRIPT s3 on s3.BUSCOMP_ID = o3.row_id and s3.WS_ID = w.row_id where r.name = 'Siebel Repository' and w.row_id = '" & sId & "' group by v.VERSION_NUM, o.OBJ_TYPE, o.OBJ_NAME order by 1, 2, 3;"
snpData.Open sSQL3, dbConnection
'fetching workspace content
sMsg = "Object modified in the WS:"
Do While Not(snpData.EOF)
sMsg = sMsg & chr(10) & snpData("VERSION_NUM") & " / " & snpData("OBJ_TYPE") & " = " & snpData("OBJ_NAME")
If snpData("SCRIPT") > "-" Then ' special alarm if there is a new applet, application or BS script
sMsg = sMsg & " / <<<<<[SCRIPT]>>>>>"
End If
snpData.MoveNext
Loop
snpData.Close
' Are you sure?
sMsg = sMsg + chr(10)+ chr(10) + "Are you sure you want to deliver the WS?"
If MsgBox(sMsg, 1, sWS) = 1 Then
' DELIVER
sMsg = sTools + " /u " & sUsername & " /p " & sPassword & " /Deliverworkspace " & sWS & " """ & sWSComment & ""
objShell.Run sMsg, 0, true
' check if delivery was successful
snpData.Open sSQL1, dbConnection
If Not(snpData.EOF) Then
sStatus = snpData("status_cd")
If sStatus = "Delivered" Then
MsgBox "WS is delivered!", 0, sWS
Else
' If failed open log folder
If sLog > "" Then
sMsg = """C:\Windows\explorer.exe"" """ & sLog & """"
objShell.Run sMsg, 8, true
End If
MsgBox "Not delivered with status = " & sStatus & chr(10) & "Please, check a log file.", 0, sWS
End If
Else
MsgBox "WS not found", 0, sWS
End If
End If
Else
MsgBox "WS not found", 0, sWS
End If
End If
dbConnection.Close
End If
Set snpData = Nothing
Set dbConnection = Nothing
Set objShell = Nothing
End Sub
</script>
<script language="VBScript">
Call Deliver
</script>
</job> ECHO OFF
REM The only purpose of that shell script is to run VBScript in 32bit mode, same as ODBC driver
REM If you are using ODBC64 data source, simply run the .vbs file itself
time /t
C:\windows\sysWOW64\cscript WSDelivery.wsf
time /t
timeout /t 5 How to use
Download all files and put them into the same folder. Open "Parameters.vbs" and change each parameter according to your environment.
If you are using 32-bit ODBC driver on 64-bit OS run the script through "WSDelivery.bat", otherwise just run "WSDelivery.wsf" directly.
Step 1.
Here you put the name of the workspace you want to be delivered.
There are 3 scenarios I've tuned the script for:
- Delivering your own WS - just click Ok.
- Delivering someone else's WS - copy & paste the full name.
- Delivering a WS when you don't know the exact name - use an asterisk (*). For example, to deliver John's WS with user story 123 type in "dev_john*us123*".
If the script finds more then one match or if the WS is in the wrong status, it will tell you that.
Step 2.
At this stage, you will be able to check the details of the workspace.
First, make sure the name in the title is correct.
Then, go through the list of changed objects. If objects require a detailed look, you can always Cancel.
Step 3.
Script will run Siebel Tools in WSDelivery mode and will wait for it to complete.
Once delivery is completed it will tell you if it was success or failure.
I've tried my best to make the script straight forward. Feel free to adjust it to your needs.
I hope you'll find the script or at least the idea useful. In my case, it saves me a couple of weeks annually.