Monday, November 21, 2011

On SQL Server and romance

Summary: Quick comment on SQL Server and PostgreSQL.
Quote of the day comes from Rob Sullivan:
"The SQL Server install is born from a truck stop romance between TFS and Sharepoint that someone found in a garbage bag in a dumpster and burned to a DVD."
Ha-ha... so true.

On a separate note, Rob's post raised my interest in PostgreSQL. I wonder, what challenges PostgreSQL presents to user, admins and developers (especially to those coming from the SQL Server camp).

Thursday, November 3, 2011

Windows batch scripting simplified

Summary: A few tips for Windows batch script developers.
One of my recent projects called me to write a couple of batch scripts (something I haven't done in a while). This post is mostly a reference for myself, but I hope it helps some unfortunate soul stuck in the last century's technology.

If you find yourself working on a batch script, chances are you will need to implement command-line parsing. You can obviously use simple position-based parameters (e.g. %1, %2, etc), but if you need anything more sophisticated, you may find the _mtplib.bat library more rewarding.

"What is the the _mtplib.bat library?" you may ask. It's a batch file implementing a few general-purpose functions (such as command-line parsing), which you can call from your batch script. [In case you wonder: yes, your batch script can call code from another batch script.] The _mtplib.bat library [mtp stands for MacMillan Technical Publishing] was originally released by Tim Hill, the author of Windows NT® Shell Scripting, a book I cannot overpraise.

Unfortunately, the link to the source code in the book does not work. After extensive search, I managed to find a version of _mtplib.bat at CodeProject, but some of the characters in the source code appear to be corrupted (I tried them in different browsers and using different character sets to no avail).

I cleaned up the file and made a couple of minor changes (e.g. added a helper function to imitate a delay). I also, cleaned up another sample published by Tim Hill: skeleton.bat (an outline that can be used to create a batch script). I made one change to the skeleton file: all of the calls to the procedures implemented in _mtplib.bat from skeleton.bat invoke them via full path (instead of just the name of the file). This change assumes that the library is located in the same folder (this way you do not need to put _mtplib.bat in your path or working directory when you execute your scripts). And I extended skeleton.bat to a more illustrative sample.bat which shows how to parse command line, call procedures, handle errors, etc. You can download the source code from: You will find the listings of the source code at the end of this post, but before, let me recommend a few excellent resources for Windows batch script developers: And now, the source code (if you encounter any issues, please let me know):

_mtplib.bat
@echo OFF

@if not "%ECHO%"=="" echo %ECHO%
@if not "%OS%"=="Windows_NT" goto DOSEXIT

rem $Workfile: _mtplib.bat $ 
rem $Revision: 2 $ 
rem $Date: 12/04/97 9:51a $
rem $Archive: .../_mtplib.bat $
rem UPDATED BY ALEK DAVIS on 11/02/2011

rem If no arguments, show version information and exit
if "%1"=="" (
 (echo Script MTP Script Library [%0] $Revision: 2 $)
 (goto :EOF)
)

rem At least one argument, so dispatch to procedure
set _PROC=%1
shift /1
goto %_PROC%

rem ------------------------------------------------------------------
rem INIT procedure
rem Must be called in local state before other procs are used
rem
:INIT
if defined TRACE %TRACE% [proc %0 %*]

goto :EOF

rem ------------------------------------------------------------------
rem VARDEL procedure
rem Delete multiple variables by prefix
rem
rem Arguments:
rem %1=variable name prefix
rem
:VARDEL
if defined TRACE %TRACE% [proc %0 %*]
 for /f "tokens=1 delims==" %%I in ('set %1 2^>nul') do set %%I=
goto :EOF

rem ------------------------------------------------------------------
rem PARSECMDLINE procedure
rem Parse a command line into switches and args
rem
rem Arguments:
rem CMDLINE=command text to parse
rem %1=0 for new parse (def) or 1 to append to existing
rem
rem Returns:
rem CMDARG_n=arguments, CMDSW_n=switches
rem CMDARGCOUNT=arg count, CMDSWCOUNT=switch count
rem RET=total number of args processed
rem
:PARSECMDLINE
if defined TRACE %TRACE% [proc %0 %*]
 if not {%1}=={1} (
  (call :VARDEL CMDARG_)
  (call :VARDEL CMDSW_)
  (set /a CMDARGCOUNT=0)
  (set /a CMDSWCOUNT=0)
 )
 set /a RET=0
 call :PARSECMDLINE1 %CMDLINE%
 set _MTPLIB_T1=
goto :EOF

:PARSECMDLINE1
if {%1}=={} goto :EOF
set _MTPLIB_T1=%1
set _MTPLIB_T1=%_MTPLIB_T1:"=%
set /a RET+=1
shift /1
if "%_MTPLIB_T1:~0,1%"=="/" goto :PARSECMDLINESW
if "%_MTPLIB_T1:~0,1%"=="-" goto :PARSECMDLINESW
set /a CMDARGCOUNT+=1
set CMDARG_%CMDARGCOUNT%=%_MTPLIB_T1%
goto :PARSECMDLINE1

:PARSECMDLINESW
set /a CMDSWCOUNT+=1
set CMDSW_%CMDSWCOUNT%=%_MTPLIB_T1%
goto :PARSECMDLINE1
goto :EOF

rem ------------------------------------------------------------------
rem GETARG procedure
rem Get a parsed argument by index
rem
rem Arguments:
rem %1=argument index (1st arg has index 1)
rem
rem Returns:
rem RET=argument text or empty if no argument
rem
:GETARG
if defined TRACE %TRACE% [proc %0 %*]
 set RET=
 if %1 GTR %CMDARGCOUNT% goto :EOF
 if %1 EQU 0 goto :EOF
 if not defined CMDARG_%1 goto :EOF
 set RET=%%CMDARG_%1%%
 call :RESOLVE
goto :EOF

rem ------------------------------------------------------------------
rem GETSWITCH procedure
rem Get a switch argument by index
rem
rem Arguments:
rem %1=switch index (1st switch has index 1)
rem
rem Returns:
rem RET=switch text or empty if none
rem RETV=switch value (after colon char) or empty
rem
:GETSWITCH
if defined TRACE %TRACE% [proc %0 %*]
 (set RET=) & (set RETV=)
 if %1 GTR %CMDSWCOUNT% goto :EOF
 if %1 EQU 0 goto :EOF
 if not defined CMDSW_%1 goto :EOF
 set RET=%%CMDSW_%1%%
 call :RESOLVE
 for /f "tokens=1* delims=:" %%I in ("%RET%") do (set RET=%%I) & (set RETV=%%J)
goto :EOF

rem ------------------------------------------------------------------
rem FINDSWITCH procedure
rem Finds the index of the named switch
rem
rem Arguments:
rem %1=switch name
rem %2=search start index (def: 1)
rem
rem Returns:
rem RET=index (0 if not found)
rem RETV=switch value (text after colon)
rem
:FINDSWITCH
if defined TRACE %TRACE% [proc %0 %*]
 if {%2}=={} (set /a _MTPLIB_T4=1) else (set /a _MTPLIB_T4=%2)

 :FINDSWITCHLOOP
 call :GETSWITCH %_MTPLIB_T4%
 if "%RET%"=="" (set RET=0) & (goto :FINDSWITCHEND)
 if /i "%RET%"=="%1" (set RET=%_MTPLIB_T4%) & (goto :FINDSWITCHEND)
 set /a _MTPLIB_T4+=1
 goto :FINDSWITCHLOOP

 :FINDSWITCHEND
 set _MTPLIB_T4=
goto :EOF

rem ------------------------------------------------------------------
rem REGSETM and REGSETU procedures
rem Set registry values from variables
rem
rem Arguments:
rem %1=reg context (usually script name)
rem %2=variable to save (or prefix to save set of vars)
rem
:REGSETM
if defined TRACE %TRACE% [proc %0 %*]
 for /f "tokens=1* delims==" %%I in ("set %2 2^>nul") do call :REGSET1 HKLM %1 %%I "%%J"
goto :EOF

:REGSETU
if defined TRACE %TRACE% [proc %0 %*]
 for /f "tokens=1* delims==" %%I in ("set %2 2^>nul") do call :REGSET1 HKCU %1 %%I "%%J"
goto :EOF

:REGSET1
set _MTPLIB_T10=%4
set _MTPLIB_T10=%_MTPLIB_T10:\=\\%
reg add %1\Software\MTPScriptContexts\%2\%3=%_MTPLIB_T10% >nul
reg update %1\Software\MTPScriptContexts\%2\%3=%_MTPLIB_T10% >nul
goto :EOF

rem ------------------------------------------------------------------
rem REGGETM and REGGETU procedures
rem Get registry value or values to variables
rem
rem Arguments:
rem %1=reg context (usually script name)
rem %2=variable to restore (def: restore entire context)
rem
rem Returns:
rem RET=value of last variable loaded
rem
rem WARNING: 
rem The "delims" value in the FOR commands below is a TAB  
rem character, followed by a space. If this file is edited by
rem an editor which converts tabs to spaces, this procedure
rem will break!!!
rem
:REGGETM
if defined TRACE %TRACE% [proc %0 %*]
 for /f "delims=     tokens=2*" %%I in ("reg query HKLM\Software\MTPScriptContexts\%1\%2 ^|find "REG_SZ"") do call :REGGETM1 %%I "%%J"
goto :EOF

:REGGETU
if defined TRACE %TRACE% [proc %0 %*]
 for /f "delims=     tokens=2*" %%I in ("reg query HKCU\Software\MTPScriptContexts\%1\%2 ^|find "REG_SZ"") do call :REGGETM1 %%I "%%J"
goto :EOF

:REGGETM1
set _MTPLIB_T10=%2
set _MTPLIB_T10=%_MTPLIB_T10:\\=\%
set _MTPLIB_T10=%_MTPLIB_T10:"=%
set %1=%_MTPLIB_T10%
set RET=%_MTPLIB_T10%
goto :EOF

rem ------------------------------------------------------------------
rem REGDELM and REGDELU procedures
rem Delete registry values
rem
rem Arguments:
rem %1=reg context (usually script name)
rem %2=variable to delete (def: delete entire context)
rem
:REGDELM
if defined TRACE %TRACE% [proc %0 %*]
 call :GETTEMPNAME
 echo y >%RET%
 reg delete HKLM\Software\MTPScriptContexts\%1\%2 <%RET% >nul
 del %RET%
goto :EOF

:REGDELU
if defined TRACE %TRACE% [proc %0 %*]
 call :GETTEMPNAME
 echo y >%RET%
 reg delete HKCU\Software\MTPScriptContexts\%1\%2 <%RET% >nul
 del %RET%
goto :EOF


rem ------------------------------------------------------------------
rem SRAND procedure
rem Seed the random number generator
rem
rem Arguments:
rem %1=new seed value
rem
:SRAND
if defined TRACE %TRACE% [proc %0 %*]
 set /a _MTPLIB_NEXTRAND=%1
goto :EOF

rem ------------------------------------------------------------------
rem RAND procedure
rem Get next random number (0 to 32767)
rem
rem Returns:
rem RET=next random number
rem
:RAND
if defined TRACE %TRACE% [proc %0 %*]
 if not defined _MTPLIB_NEXTRAND set /a _MTPLIB_NEXTRAND=1
 set /a _MTPLIB_NEXTRAND=_MTPLIB_NEXTRAND * 214013 + 2531011
 set /a RET=_MTPLIB_NEXTRAND ^>^> 16 ^& 0x7FFF
goto :EOF

rem ------------------------------------------------------------------
rem RESOLVE procedure
rem Fully resolve all indirect variable references in RET variable
rem
rem Arguments:
rem RET=value to resolve
rem
rem Returns:
rem RET=as passed in, with references resolved
rem
:RESOLVE
if defined TRACE %TRACE% [proc %0 %*]
 :RESOLVELOOP
 if "%RET%"=="" goto :EOF
 set RET1=%RET%
 for /f "tokens=*" %%I in ('echo %RET%') do set RET=%%I
 if not "%RET%"=="%RET1%" goto :RESOLVELOOP
goto :EOF

rem ------------------------------------------------------------------
rem GETINPUTLINE procedure
rem Get a single line of keyboard input
rem
rem Returns:
rem RET=Entered line
rem
:GETINPUTLINE
if defined TRACE %TRACE% [proc %0 %*]
 call :GETTEMPNAME
 set _MTPLIB_T1=%RET%
 copy con "%_MTPLIB_T1%" >nul
 for /f "tokens=*" %%I in ("type '%_MTPLIB_T1%'") do set RET=%%I
 if exist "%_MTPLIB_T1%" del "%_MTPLIB_T1%"
 set _MTPLIB_T1=
goto :EOF

rem ------------------------------------------------------------------
rem GETSYNCFILE procedure
rem Get a sync file name (file will not exist)
rem
rem Returns:
rem RET=Name of sync file to use
rem
:GETSYNCFILE
if defined TRACE %TRACE% [proc %0 %*]
 call :GETTEMPNAME
goto :EOF

rem ------------------------------------------------------------------
rem SETSYNCFILE procedure
rem Flag sync event (creates the file)
rem
rem Arguments:
rem %1=sync filename to flag
rem
:SETSYNCFILE
if defined TRACE %TRACE% [proc %0 %*]
 echo . >%1
goto :EOF

rem ------------------------------------------------------------------
rem DELSYNCFILE procedure
rem Delete sync file
rem
rem Arguments: 
rem %1=sync filename
rem
:DELSYNCFILE
if defined TRACE %TRACE% [proc %0 %*]
 if exist %1 del %1
goto :EOF

rem ------------------------------------------------------------------
rem WAITSYNCFILE procedure
rem Wait for sync file to flag
rem
rem Arguments:
rem %1=sync filename
rem %2=timeout in seconds (def: 60)
rem
rem Returns:    
rem RET=Timeout remaining, or 0 if timeout
rem
:WAITSYNCFILE
if defined TRACE %TRACE% [proc %0 %*]
 if {%2}=={} (set /a RET=60) else (set /a RET=%2)
 if exist %1 goto :EOF
 
 :WAITSYNCFILELOOP
 sleep 1
 set /a RET-=1
 if %RET% GTR 0 if not exist %1 goto :WAITSYNCFILELOOP
goto :EOF

rem ------------------------------------------------------------------
rem GETTEMPNAME procedure
rem Create a temporary file name
rem
rem Returns:
rem RET=Temporary file name
rem
:GETTEMPNAME
if defined TRACE %TRACE% [proc %0 %*]
 if not defined _MTPLIB_NEXTTEMP set /a _MTPLIB_NEXTTEMP=1
 if defined TEMP (
  (set RET=%TEMP%)
 ) else if defined TMP (
  (set RET=%TMP%)
 ) else (set RET=%SystemRoot%)
 
 :GETTEMPNAMELOOP
 set /a _MTPLIB_NEXTTEMP=_MTPLIB_NEXTTEMP * 214013 + 2531011
 set /a _MTPLIB_T1=_MTPLIB_NEXTTEMP ^>^> 16 ^& 0x7FFF
 set RET=%RET%\~SH%_MTPLIB_T1%.tmp
 if exist "%RET%" goto :GETTEMPNAMELOOP
 set _MTPLIB_T1=
goto :EOF

rem ------------------------------------------------------------------
rem DELAY procedure
rem Sleep for the specified number of seconds
rem
rem PARAMETERS
rem %1=Number of seconds to sleep
:DELAY
if defined TRACE %TRACE% [proc %0 %*]
 if {%1}=={} goto :EOF
 @ping 127.0.0.1 -n 2 -w 1000 >nul
 @ping 127.0.0.1 -n %1% -w 1000 >nul
goto :EOF

rem These must be the FINAL LINES in the script
:DOSEXIT
echo This script requires Windows NT
rem ------------------------------------------------------------------

skeleton.bat
@echo OFF

@if not "%ECHO%"=="" echo %ECHO%
@if not "%OS%"=="Windows_NT" goto DOSEXIT

rem $Workfile: skeleton.bat $ 
rem $Revision: 2 $ 
rem $Date: 12/04/97 9:51a $
rem $Archive: .../skeleton.bat $
rem UPDATED BY ALEK DAVIS

rem Set local scope and call MAIN procedure
setlocal & pushd & set RET=
set SCRIPTNAME=%~n0
set SCRIPTPATH=%~f0
set SCRIPTDIR=%~dp0

if "%DEBUG%=="1" (set TRACE=echo) else (set TRACE=rem)
call %SCRIPTDIR%_mtplib :INIT %SCRIPTPATH%
if /i {%1}=={/help} (call :HELP %2) & (goto :HELPEXIT)
if /i {%1}=={/?} (call :HELP %2) & (goto :HELPEXIT)
if /i {%1}=={/h} (call :HELP %2) & (goto :HELPEXIT)
call :MAIN %*

:HELPEXIT
popd & endlocal & set RET=%RET%
goto :EOF

rem ------------------------------------------------------------------
rem HELP procedure
rem Display usage information
rem
:HELP
if defined TRACE %TRACE% [proc %0 %*]
rem Put help message here
goto :EOF

rem ------------------------------------------------------------------
rem MAIN procedure
rem 
:MAIN
if defined TRACE %TRACE% [proc %0 %*]
rem Put main script code here
goto :EOF

rem ------------------------------------------------------------------
rem Additional procedure go here

rem These must be the FINAL LINES in the script
:DOSEXIT
echo This script requires Windows NT
rem ------------------------------------------------------------------

sample.bat
@echo OFF

rem Sample batch script
rem Requires _mtplib.bat to be in the same folder.

@if not "%ECHO%"=="" echo %ECHO%
@if not "%OS%"=="Windows_NT" goto DOSEXIT

rem Set local scope and call MAIN procedure
setlocal & pushd & set RET=
set SCRIPTNAME=%~n0
set SCRIPTPATH=%~f0
set SCRIPTDIR=%~dp0
set SCRIPTEXT=%~x0

if "%DEBUG%"=="1" (set TRACE=echo) else (set TRACE=rem)
call %SCRIPTDIR%_mtplib :INIT %SCRIPTPATH%
if /i {%1}=={/help} (call :HELP %2) & (goto :HELPEXIT)
if /i {%1}=={/?} (call :HELP %2) & (goto :HELPEXIT)
if /i {%1}=={/h} (call :HELP %2) & (goto :HELPEXIT)
call :MAIN %*

:HELPEXIT
popd & endlocal & set RET=%RET%
goto :EOF

rem ------------------------------------------------------------------
rem HELP procedure
rem Display usage information
rem
rem ------------------------------------------------------------------
rem HELP procedure
rem Display usage information
rem
:HELP
if defined TRACE %TRACE% [proc %0 %*]
echo DESCRIPTION
echo.
echo Does this or that on the specified server.
echo.
echo SYNTAX
echo.
echo %ScriptName% [this^|that] [switches]
echo.
echo ARGUMENTS
echo.
echo this
echo    Does this.
echo.
echo that
echo    Does that.
echo.
echo /s:server
echo.
echo    Name of server affected by this or that.
echo    If not specified, local server will be used.
echo.
echo /u:user
echo.
echo    Name of the user performing this or that.
echo    If not specified, active Windows user will be used.
echo.
echo /p:password
echo.
echo    Password of the the user identified by switch /u.
echo.
echo EXAMPLES
echo.
echo    %ScriptName% this /s:XYZ /u:BillieJean /p:IsNotMyLover
echo.
echo        Does this on server XYZ as user BillieJean.
echo.
echo    %ScriptName% that /s:XYZ
echo.
echo        Does that on server XYZ as active Windows user.
goto :EOF

rem ------------------------------------------------------------------
rem MAIN procedure
rem 
:MAIN
if defined TRACE %TRACE% [proc %0 %*]
rem If no arguments, display help
if /i {%1}=={/h} (call :HELP %2) & (goto :HELPEXIT)
if /i {%1}=={} (call :HELP %2) & (goto :HELPEXIT)

rem Initialize variables
set SERVER=
set USER=
set PASSWORD=

rem Process command line and set up variables
set CMDLINE=%*
call %SCRIPTDIR%_mtplib :PARSECMDLINE 0

rem Process positional arguments
if %CMDARGCOUNT% LSS 1 (call :HELP) & (goto :EOF)
call %SCRIPTDIR%_mtplib :GETARG 1
set OPERATION=%RET%
if /i "%OPERATION%"=="this" set OPERATION=this
if /i "%OPERATION%"=="that" set OPERATION=that

rem Process command-line switches
set /a IX=1
:GETSWITCHLOOP
 call %SCRIPTDIR%_mtplib :GETSWITCH %IX%
 if "%RET%"=="" goto :GETSWITCHLOOPEND
 set /a IX+=1
 if /i "%RET%"=="/s" set SERVER=%RETV%
 if /i "%RET%"=="/u" set USER=%RETV%
 if /i "%RET%"=="/p" set PASSWORD=%RETV%
goto :GETSWITCHLOOP
:GETSWITCHLOOPEND

rem Use defaults for non-specified variables
if "%SERVER%"=="" set SERVER=%COMPUTERNAME%
if "%USER%"=="" set USER=%USERDOMAIN%\%USERNAME%

rem Check the operation type and perform this or that
if /i "%OPERATION%"=="this" (
 call :DOTHIS
) else if "%OPERATION%"=="that" (
    call :DOTHAT
) else (
    goto :HELP
)
goto :EOF

rem ------------------------------------------------------------------
rem DOTHIS procedure
rem Print parameter values and sleep for 5 seconds.
rem
:DOTHIS
if defined TRACE %TRACE% [proc %0 %*]
 echo Started this on %DATE% at %TIME%

 call :PRINTPARAMS

 echo Please wait 5 seconds...
 call %SCRIPTDIR%_mtplib :DELAY 5
 if %ERRORLEVEL% neq 0 goto :EOF

 echo Ended this on %DATE% at %TIME%
goto :EOF

rem ------------------------------------------------------------------
rem DOTHAT procedure
rem Print parameter values and sleep for 10 seconds.
rem
:DOTHAT
if defined TRACE %TRACE% [proc %0 %*]
 echo Started that on %DATE% at %TIME%

 call :PRINTPARAMS
 
 echo Please wait 10 seconds...
 call %SCRIPTDIR%_mtplib :DELAY 10
 if %ERRORLEVEL% neq 0 goto :EOF

 echo Ended that on %DATE% at %TIME%
goto :EOF

:PRINTPARAMS
if defined TRACE %TRACE% [proc %0 %*]
 echo Server  =%SERVER%
 echo User    =%USER%
 echo Password=%PASSWORD%
goto :EOF

rem These must be the FINAL LINES in the script
:DOSEXIT
echo This script requires Windows NT
rem ------------------------------------------------------------------

See also:
DOS - Script Snippets (library of various DOS snippets)
Using parameter qualifiers in Windows shell scripts