Search code examples
perldebuggingerror-codehanabackticks

Resolving Perls "$?" return code: Why is it different from Hana sql error code ??

I have written a perl script that connects to hdbsql a commandline tool on SAP HANA. Hdbsql to SAP Hana is what sqlplus is to Oracle.

I am using backtick to fire a sql query on hdbsql and i am capturing the result of the query in an array. Following is the piece of code that performs this operation:

my $sql_statement = 'SELECT some_column FROM some_table WHERE  
                     some_condition';

my $hdb_sql = "/PROGRA~1/sap/hdbclient/hdbsql.exe";

my $connection = "-i 11 -n 100.450.10.20:31115 -u myUser -p myPwd -j -F   
                  \"|\"";

my @queryResults = `$hdb_sql $connection $sql_statement`;

Here is where i am facing issues. When the query executes successfully the hdbsql return code is 0 and the return code stored in perl variable "$?" is also zero.But in cases of error this is what i have observed:

When the query has some syntax issues i get the following is the error message:

* 257: sql syntax error: incorrect syntax near "SELECTS": line 1 col 1 (at 
pos 1) SQLSTATE: HY000

We see that the Hana sql error code is 257 from the above message. But if i printout the perl variable "$?". It gives 256 as the error/return code.

Similarly,

  • For invalid column name the hana sql error code is 260 but the perl return code is 1024.
  • For missing aggregation or grouping the hana sql error code is 276 but the perl return code is 5120.
  • For wrong number of arguments in a function the hana sql error code is 316 but the perl return code is 15360.
  • For invalid table name the hana sql error code is 259 but the perl return code is 768.

and so on...

Why is it that the error codes that "$?" give are different from the actual hana sql return codes? In my script i want to print out the actual hana error code.How do i do it in perl. Is there a mathematical relation between the two codes

I have to state this that i am keen on using backtics. I dont want to switch to system() , perl pipe , use perl DBI or any other module to fire the hdbsql query.

I have come across some similar questions asked in this forum but for my case they have been of very little help.

I appreciate your comments/answers. Thank you!!!!

Solution

  • $? is usually a multiple of 256. See perldoc -f system (though this applies to assignment of $? after backticks and wait as well).

    The exit (and POSIX::_exit) functions expect input on the range 0 to 255 -- this is an operating system convention, not a limitation of Perl. If you pass an argument outside that range, Perl or the operating system will treat it as if you called exit($arg % 256).

    So I think what is happening is that hana sql makes the system call to exit with the sql error code. This error code is typically greater than 255, so the exit code that gets propogated back to the operating system is hana error code % 256.

    When Perl receives this error code and assigns a value to $?, it multiplies that value by 256 (as described in perldoc -f system), so the relationship between the hana sql code and $? is probably something like:

     $? = (<hana-sql-code> % 256) * 256

    or more pedantically

     $? = (<hana-sql-code> & 255) << 8