You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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 [CORE4900]
#5193
=== 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.
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
^- 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:
(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
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.
The text was updated successfully, but these errors were encountered:
Submitted by: @pavel-zotov
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.
The text was updated successfully, but these errors were encountered: