Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
firebird-automations opened this issue Aug 5, 2015 · 1 comment

Comments

@firebird-automations
Copy link
Collaborator

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.

@mrotteveel
Copy link
Member

Closing as Firebird 2.5 is end-of-life

@mrotteveel mrotteveel closed this as not planned Won't fix, can't repro, duplicate, stale May 3, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants