Isql from bash script, how to handle error codes?

Hello, i submit commands from a bash script, is there a way to handle any isql errors at the shell level? I’m trying:

#!/bin/bash
...
/usr/local/virtuoso-opensource/bin/isql 1111 user password VERBOSE=OFF /home/deve
l/dump.sql -i
#exit code of previous
echo $?
...

anyway $? is always 0 even when isql raises SQL errors :frowning:

do i need to catch it at SQL level (exceptions?) and how to propagate (print? to file?) to the caller (bash script)?

Thanks for any advices

Development are looking into possible causes of this. What type of errors are you seeking to catch?

How to check for SQL errors in isql script

The iSQL tool actually has scripting capabilities which OpenLink uses extensively in the Virtuoso testsuite scripts (binsrc/tests/suite/*.sql) to check the engine after each build.

Each script contains a number of SQL statements followed by some script code that performs a check on the previous statement, for example:

select 1;                                                          -- l1
ECHO BOTH $IF $EQU $STATE OK "PASSED" "FAILED";                    -- l2
ECHO BOTH ": example : STATE=" $STATE " MESSAGE=" $MESSAGE "\n\n"; -- l3
SET ARGV[$LIF] $+ $ARGV[$LIF] 1;                                   -- l4

EXIT $ARGV[0]                                                      -- l5

This example runs the SQL command select 1; which is a valid statement that returns a single row consisting of a single integer column that contains 1;

The second line checks if the STATE of the previous statement returned OK. If so, it prints PASSED; else, FAILED.

The third line prints a string that can be used to describe the test, followed by the STATE and MESSAGE that were returned. In case the test cannot be executed due to SQL syntax errors, transactional issues, permissions, etc., this can provide valuable information to identify the problem.

The fourth line uses the index $LIF which indicates whether the previous IF expression evaluated to
1 (PASSED) or 0 (FAILED) and adds one to either counter.

The fifth line sets the exit state of the isql tool to the number of FAILED tests.

The output should look like:

OpenLink Virtuoso Interactive SQL (Virtuoso)
Version 08.03.3314 as of Jul 17 2019
Type HELP; for help and EXIT; to exit.
Connected to OpenLink Virtuoso
Driver: 08.03.3314 OpenLink Virtuoso ODBC Driver

unnamed
INTEGER NOT NULL
_______________________________________________________________________________

1

1 Rows. -- 1 msec.
PASSED: test 1 : STATE=OK MESSAGE=OK

and since this example passes all tests, the isql exit state in $? will be 0.

Sample script with various tests

--
--  Clean counters
--
SET ARGV[0] 0;
SET ARGV[1] 0;


--
--  1. this example should pass
--
select 1;
ECHO BOTH $IF $EQU $STATE OK "PASSED" "FAILED";
ECHO BOTH ": test 1 : STATE=" $STATE " MESSAGE=" $MESSAGE "\n\n";
SET ARGV[$LIF] $+ $ARGV[$LIF] 1;

--
--  2. this should fail
--
select "no_such_table";
ECHO BOTH $IF $EQU $STATE OK "PASSED" "FAILED";
ECHO BOTH ": test 2 : STATE=" $STATE " MESSAGE=" $MESSAGE "\n\n";

SET ARGV[$LIF] $+ $ARGV[$LIF] 1;


--
--  3. this is how to check if syntax errors actually are caught
--
select "no_such_table";
ECHO BOTH $IF $NEQ $STATE OK "PASSED" "FAILED";
ECHO BOTH ": test 3 : STATE=" $STATE " MESSAGE=" $MESSAGE "\n\n";
SET ARGV[$LIF] $+ $ARGV[$LIF] 1;


--
--  4. check to make sure we got a syntax error
--
select "no_such_table";
ECHO BOTH $IF $EQU $STATE "S0022" "PASSED" "FAILED";
ECHO BOTH ": test 4 : STATE=" $STATE " MESSAGE=" $MESSAGE "\n\n";
SET ARGV[$LIF] $+ $ARGV[$LIF] 1;


--
--  5. check if dba account exists (rowcount must be exactly 1)
-- 
select u_id, u_e_mail from sys_users where u_name = 'dba';
ECHO BOTH $IF $EQU $ROWCNT 1 "PASSED" "***FAILED";
ECHO BOTH ": test 5 : STATE=" $STATE " MESSAGE=" $MESSAGE "\n\n";
SET ARGV[$LIF] $+ $ARGV[$LIF] 1;


--
--  6. check if dba account exists (rowcount must be greater than 0)
-- 
select u_id, u_e_mail from sys_users where u_name = 'dba';
ECHO BOTH $IF $GT $ROWCNT 0 "PASSED" "***FAILED";
ECHO BOTH ": test 6 : STATE=" $STATE " MESSAGE=" $MESSAGE "\n\n";
SET ARGV[$LIF] $+ $ARGV[$LIF] 1;


--
--  7. check if dbX account exists (rowcount should be 0)
-- 
select u_id, u_e_mail from sys_users where u_name = 'dbS';
ECHO BOTH $IF $EQU $ROWCNT 0 "PASSED" "***FAILED";
ECHO BOTH ": test 7 : STATE=" $STATE " MESSAGE=" $MESSAGE "\n\n";
SET ARGV[$LIF] $+ $ARGV[$LIF] 1;

--
--  8. check if dbX account exists (rowcount should be less than 1)
-- 
select u_id, u_e_mail from sys_users where u_name = 'dbS';
ECHO BOTH $IF $LT $ROWCNT 1 "PASSED" "***FAILED";
ECHO BOTH ": test 7 : STATE=" $STATE " MESSAGE=" $MESSAGE "\n\n";
SET ARGV[$LIF] $+ $ARGV[$LIF] 1;


--
--  Show how many tests failed and passed
--
ECHO BOTH "COMPLETED WITH " $ARGV[0] " FAILED, " $ARGV[1] " PASSED\n\n";


--
--  Set EXIT state to the total number of FAILED tests
--
EXIT $ARGV[0];

When you save the above example as /tmp/isql_sample_script.sql and run it using:

#!/bin/bash
…
/usr/local/virtuoso-opensource/bin/isql 1111 user password VERBOSE=OFF /tmp/isql_sample_script.sql -i
#exit code of previous
echo $?
…

it should return an exit code of 1 as there is one test that is actually counted as a failed test.

thanks, maybe I’m doing something wrong but it doesn’t behave as expected, here is a simplified bash script :

 [devel@tulipe-test1 ~]$ more dumpvirtuoso.sh
#!/bin/bash
/usr/local/virtuoso-opensource/bin/isql 1111 user password VERBOSE=OFF /home/devel/dump.sql -i
#exit code of previous
echo $?

and the content of /home/devel/dump.sql from your advices :

[devel@tulipe-test1 ~]$ more dump.sql
--dump_one_graph_nt('<http://toto>', '/home/devel/ThomasTestDump/myfile_', 1000000000);
select "no_such_table";
ECHO BOTH $IF $EQU $STATE OK "PASSED" "FAILED";
ECHO BOTH ": example : STATE=" $STATE " MESSAGE=" $MESSAGE "\n\n";
SET ARGV[$LIF] $+ $ARGV[$LIF] 1;
EXIT $ARGV[0]

the execution, see the zero at the end instead of expected 1 (for failure) :

[devel@tulipe-test1 ~]$ ./dumpvirtuoso.sh

*** Error S0022: [Virtuoso Driver][Virtuoso Server]SQ200: No column no_such_table.
at line 2 of Command-Line-Load /home/devel/dump.sql:
select "no_such_table"
FAILEDFAILED: example : STATE=: example : STATE=S0022S0022 MESSAGE= MESSAGE=[Virtuoso Driver][Virtuoso Server]SQ200: No column no_such_table.[Virtuoso Driver][Virtuoso Server]SQ200: No column no_such_table.



0

I found 2 small issues in your test code.

First one is in the dump.sql script. the EXIT line should have a semicolon at the end of the line, i.e.:

EXIT $ARGV[0];

Also do not forget to start the dump.sql script so the counters are properly initialized:

SET ARGV[0]=0;
SET ARGV[1]=0;

The second problem is in your bash script. The $? is reset at each new line. Since you have a comment between the isql statement and the echo, the $? is reset to 0.

I recommend using the following construction:

#!/bin/bash

/usr/local/virtuoso-opensource/bin/isql 1111 user password VERBOSE=OFF /home/devel/dump.sql -i
STATUS=$?

#exit code of previous
echo $STATUS
1 Like

Great ! thank you , indeed the semicolon was tricky ! I’m very thankfull four your help,

Thomas