Informix DB-Access Usage Enhancer for Windows-2000.

Informix DB-Access Usage Enhancer for Windows-2000.

This batch (script) file will help you to run SQL commands from the windows command prompt.

Salient Features of this Run utility are:

. Simple script file; easy to use at prompt level.
. Searches DML statements and traps it under transaction.
. Prompts for commit or rollback transaction.
. Traps delete statement with missing where clause.
. Writes all the committed statements in a flat dump file.

Listing of R.CMD

@echo off
rem IBM Informix DB-Access Usage Enhancer for Windows-2000.
rem V.P. Shriyan 10/10/2002.

rem Script has been tested for vers. 7.31, 9.2 and 9.3

rem This script assumes that the following environment
rem are set with valid paths / values.
rem
rem INFORMIXDIR INFORMIXSERVER ONCONFIG
rem
rem INFORMIXSQLHOSTS CLIENT_LOCALE DB_LOCALE
rem SERVER_LOCALE DBLANG

rem change these two parameters as required
set DB=testdb
set LOG=%TEMP%\%DB%.log

set WINROOT=%SystemRoot%
if "x%WINROOT%" == "x" SET WINROOT=C:\WINNT
set GREP=%WINROOT%\System32\findstr.exe
set DBACCESS=%INFORMIXDIR%\bin\dbaccess.exe
set CMD=%*
set CMD1=%CMD%

ver

if "x%INFORMIXDIR%" == "x" ( echo INFORMIXDIR need to be set.
goto :end )

if not exist %DBACCESS% ( echo %DBACCESS% is required.
goto :end )

if not exist %GREP% ( echo %GREP% is required.
goto :end )

echo \%1 | %GREP% /I "? \/? -- help -help" > nul
if %ERRORLEVEL% == 0 ( goto :help )

if "x%CMD%" == "x" ( echo Pass SQL statement to execute.
goto :end )

%INFORMIXDIR%\bin\onstat - | %GREP% " not " > nul
if %ERRORLEVEL%==0 (echo Database server %INFORMIXSERVER% not up
goto :end )

%DBACCESS% -V
echo Current Database: %DB%

echo %CMD% | %GREP% /I "insert update delete " > nul
if %ERRORLEVEL% == 0 ( goto :trans
) else (
goto :execute
)

:trans
echo %CMD%|%GREP% /I "delete "
if %ERRORLEVEL% == 0 ( goto :ckwhere
) else (
goto :bcr
)

:ckwhere
echo %CMD%|%GREP% /I "where" >nul
if %ERRORLEVEL% == 1 ( echo.
echo Warning:
echo WHERE clause is missing. Please be careful.
echo If you are sure what you are doing invoke as
echo %*% where 1=1
goto :end )

:bcr
echo %CMD% | %GREP% /I "begin commit rollback" > nul
if %ERRORLEVEL% == 1 ( goto :dopad
) else goto :ckall

:dopad
echo.
choice /? > nul 2>&1
if %ERRORLEVEL% == 9009 ( echo choice.com file is required.
goto :end )
choice /c:cr "[C]ommit / [R]ollback ?"
if %ERRORLEVEL% == 1 ( goto :commit
) else goto :rollback

:rollback
set CMD1=begin; %CMD% ; rollback;
goto :execute

:commit
set CMD1=begin; %CMD% ; commit;
goto :execute

:ckall
echo %CMD% | %GREP% /I "begin" > nul
if %ERRORLEVEL% == 1 set CMD1=begin ; %CMD%

echo %CMD% | %GREP% /I "commit rollback" > nul
if %ERRORLEVEL% == 1 set CMD1= %CMD% ; rollback;

:execute
echo %CMD1% | %DBACCESS% -e %DB%
if %ERRORLEVEL% == 0 ( goto :logcmd
) else goto :end

:logcmd
echo %CMD1% | %GREP% /I "commit" > nul
if %ERRORLEVEL%==0 echo %date% %time%:%USERNAME%: %CMD1% >>%LOG%
goto :end

:help
echo.
echo IBM Informix DB-Access Usage Enhancer.
echo by V.P. Shriyan 10/10/2002.
echo.
echo Salient Features:
echo.
echo . Simple script file; easy to use at prompt level.
echo . Searches DML statements and traps it under transaction.
echo . Prompts for commit or rollback transaction.
echo . Traps delete statement with missing where clause.
echo . Writes all the committed statements in a flat dump file.

:end
set DB=
set WINROOT=
set GREP=
set DBACCESS=
set CMD=
set CMD1=
set LOG=