Unify behaviour of ISQL in 2.5.x and 3.0 when: 1) it get commands through OS
pipe mechanism, 2) some of commands are SHELL <...> and 3) some OS-command
fails with ERRORLEVEL<>0
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4900
URL: http://tracker.firebirdsql.org/browse/CORE-4900
Project: Firebird Core
Issue Type: Improvement
Components: Documentation, ISQL
Affects Versions: 3.0 RC 1, 2.5.5
Environment: Windows XP
Reporter: Pavel Zotov
Priority: Minor
Consider following
SCRIPT.
######
=== begin of script ===
connect 'localhost/3249:e25' user 'SYSDBA' password 'masterkey';
set echo on;
show version;
SET BAIL ON; -------------------- [ ### ]
commit;
set list on;
select current_timestamp || ' - point-1' as msg from rdb$database;
shell ping -n 3 127.0.0.1 >nul; ------------------------------- [ ALWAYS
FINISHES "OK" and set in OS env. ERRORLEVEL = 0 ]
select current_timestamp || ' - point-2' as msg from rdb$database;
shell ping -n 1 -w 2000 1.1.1.1 >nul; ---------------------- [ ALWAYS FINISHES
"POOR" and set in OS env. ERRORLEVEL = 1 ]
select current_timestamp || ' - point-3' as msg from rdb$database;
shell ping -n 1 -w 2000 1.1.1.1 >nul;
select current_timestamp || ' - point-4' as msg from rdb$database;
shell ping -n 1 -w 2000 1.1.1.1 >nul;
select current_timestamp || ' - point-5' as msg from rdb$database;
shell ping -n 3 127.0.0.1 >nul;
select current_timestamp || ' - point-6' as msg from rdb$database;
=== end of script ===
NOTE: this script uses two **DIFFERENT** commands to achieve pause during its
execution:
*******
1) PING -n 3 127.0.0.1 >nul; -- this will silently wait ~ (3-1) = 2 seconds
and always set ERRORLEVEL to 0, because host 127.0.0.1 is always available for
ping;
2) PING -n 1 -w 2000 1.1.1.1 >nul; -- this will also silently wait ~ 2
seconds, but is will always set ERRORLEVEL TO 1, because host 1.1.1.1 can be
never reached.
--------------------------------------------------
So, let us run this script on 2.5.5:
C:\MIX\firebird\QA\fbt-repo\tmp>C:\MIX\firebird\fb25Cs\bin\isql -q -i
tmp_worker.tmp.cs25.sql
show version;
ISQL Version: WI-V2.5.5.26916 Firebird 2.5
Server version:
Firebird/x86/Windows NT (access method), version "WI-V2.5.5.26916 Firebird 2.5"
Firebird/x86/Windows NT (remote server), version "WI-V2.5.5.26916 Firebird
2.5/tcp (csprog)/P12"
Firebird/x86/Windows NT (remote interface), version "WI-V2.5.5.26916 Firebird
2.5/tcp (csprog)/P12"
on disk structure version 11.2
set bail on;
commit;
set list on;
select current_timestamp || ' - point-1' as msg from rdb$database;
MSG 2015-08-05 16:10:56.9840 - point-1
shell ping -n 3 127.0.0.1 >nul;
select current_timestamp || ' - point-2' as msg from rdb$database;
MSG 2015-08-05 16:10:59.0150 - point-2
shell ping -n 1 -w 2000 1.1.1.1 >nul;
^- And thats all. No more messages, script was terminated.
I think this is because of last SHELL command set ERRORLEVEL environment to 1.
If we change 'shell' commands to these:
===
select current_timestamp || ' - point-1' as msg from rdb$database;
shell ping -n 3 127.0.0.1 >nul;
select current_timestamp || ' - point-2' as msg from rdb$database;
shell ping -n 1 -w 2000 1.1.1.1 >nul || time /t >nul >nul; -------------- [
will finish always with errorlevel = 0 ]
select current_timestamp || ' - point-3' as msg from rdb$database;
shell ping -n 1 -w 2000 1.1.1.1 >nul || time /t >nul >nul; -------------- [
will finish always with errorlevel = 0 ]
select current_timestamp || ' - point-4' as msg from rdb$database;
shell ping -n 1 -w 2000 1.1.1.1 >nul || time /t >nul >nul; -------------- [
will finish always with errorlevel = 0 ]
select current_timestamp || ' - point-5' as msg from rdb$database;
shell ping -n 3 127.0.0.1 >nul;
select current_timestamp || ' - point-6' as msg from rdb$database;
===
-- than result will be OK: all six points will be displayed. And this is
because we change final ErrorLevel to 0 by adding 'TIME /T' (which finishes
always OK).
Results in FB-3 (tested WI-V3.0.0.31981) are similar.
So, result of SHELL command, being reflected in OS runtime ErrorLevel variable,
can be taken in account inside SQL script.
It's good (may be :)) though I did not find any mention about it into
documentation.
-----------------------------------------------
Now bad news -- consider the
BATCH
######
-- which does exactly the same as script shown above, but using PIPE mechanism.
Let its name will be 'tmp_worker.NN.bat' (NN=25 or 30)
=== start of batch ===
@echo off
echo connect 'localhost/3259:e25' user 'SYSDBA' password 'masterkey';
echo set echo on;
echo show version;
echo set bail on;
echo set list on;
echo select current_timestamp ^|^| ' - point-1' as msg from rdb$database;
echo shell ping -n 3 127.0.0.1 ^>nul;
echo select current_timestamp ^|^| ' - point-2' as msg from rdb$database;
echo shell ping -n 1 -w 2000 1.1.1.1 ^>nul;
echo select current_timestamp ^|^| ' - point-3' as msg from rdb$database;
echo shell ping -n 1 -w 2000 1.1.1.1 ^>nul;
echo select current_timestamp ^|^| ' - point-4' as msg from rdb$database;
echo shell ping -n 1 -w 2000 1.1.1.1 ^>nul;
echo select current_timestamp ^|^| ' - point-5' as msg from rdb$database;
echo shell ping -n 3 127.0.0.1 ^>nul;
echo select current_timestamp ^|^| ' - point-6' as msg from rdb$database;
=== end of batch ===
If this batch contains CONNECT string to database of FB 2.5 then we have to run
this batch like this:
C:\...\> tmp_worker.25.bat | C:\MIX\firebird\fb25sS\bin\isql -bail -q
(note on PIPE operator that is used to redirect STDOUT of batch to STDIN for
ISQL; note also on switch '-BAIL').
Output in 2.5.5:
~~~~~~~~~~~
C:\...> tmp_worker.25.bat | C:\MIX\firebird\fb25sS\bin\isql -b -q
SQL> Database: 'localhost/3259:e25', User: SYSDBA
SQL> SQL> show version;
ISQL Version: WI-V2.5.5.26916 Firebird 2.5
Server version:
Firebird/x86/Windows NT (access method), version "WI-V2.5.5.26916 Firebird 2.5"
Firebird/x86/Windows NT (remote server), version "WI-V2.5.5.26916 Firebird
2.5/tcp (csprog)/P12"
Firebird/x86/Windows NT (remote interface), version "WI-V2.5.5.26916 Firebird
2.5/tcp (csprog)/P12"
on disk structure version 11.2
SQL> set bail on;
SQL> set list on;
SQL> select current_timestamp || ' - point-1' as msg from rdb$database;
MSG 2015-08-05 16:36:05.1710 - point-1
SQL> shell ping -n 3 127.0.0.1 >nul;
SQL> select current_timestamp || ' - point-2' as msg from rdb$database;
MSG 2015-08-05 16:36:07.2030 - point-2
SQL> shell ping -n 1 -w 2000 1.1.1.1 >nul;
SQL> select current_timestamp || ' - point-3' as msg from rdb$database;
MSG 2015-08-05 16:36:09.2960 - point-3
SQL> shell ping -n 1 -w 2000 1.1.1.1 >nul;
SQL> select current_timestamp || ' - point-4' as msg from rdb$database;
MSG 2015-08-05 16:36:11.7810 - point-4
SQL> shell ping -n 1 -w 2000 1.1.1.1 >nul;
SQL> select current_timestamp || ' - point-5' as msg from rdb$database;
MSG 2015-08-05 16:36:14.2810 - point-5
SQL> shell ping -n 3 127.0.0.1 >nul;
SQL> select current_timestamp || ' - point-6' as msg from rdb$database;
MSG 2015-08-05 16:36:16.3120 - point-6
--- end of output in 2.5.5 ---
Output in 3.0:
~~~~~~~~~~
C:\...> tmp_worker.30.bat | C:\MIX\firebird\fb30\isql -b -q
show version;
ISQL Version: WI-V3.0.0.31981 Firebird 3.0 Release Candidate 1
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-V3.0.0.31981 Firebird
3.0 Release Candidate 1"
Firebird/Windows/Intel/i386 (remote server), version "WI-V3.0.0.31981 Firebird
3.0 Release Candidate 1/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-V3.0.0.31981
Firebird 3.0 Release Candidate 1/tcp (csprog)/P13"
on disk structure version 12.0
set list on;
select current_timestamp || ' - point-1' as msg from rdb$database;
MSG 2015-08-05 16:37:12.2500 - point-1
shell ping -n 3 127.0.0.1 >nul;
select current_timestamp || ' - point-2' as msg from rdb$database;
MSG 2015-08-05 16:37:14.2810 - point-2
shell ping -n 1 -w 2000 1.1.1.1 >nul;
--- end of output in 3.0 ---
These results DIFFER.
If 3.0 behaves as expected, i.e. takes in account failed result of outer SHELL
command, than it should be mentioned somewhere in doc. But in this case it will
be nice if similar feature will be backported in 2.5.5.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
