2

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!!!!

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Chiseled
  • 2,280
  • 8
  • 33
  • 59
  • Slightly related: http://stackoverflow.com/questions/18640737/why-arent-i-picking-up-the-exit-status-from-my-child-process/18641283#18641283 – mob Sep 06 '13 at 17:50

1 Answers1

2

$? 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
ikegami
  • 367,544
  • 15
  • 269
  • 518
mob
  • 117,087
  • 18
  • 149
  • 283
  • hi "mob" and "ikegami" thank you!!! I get why the two codes are different. if i understood you right , if i have value of "$?" i can't get the original hana error code from it. Right ?? – Chiseled Sep 06 '13 at 21:16
  • Not right. Original error code is `$? / 256 + 256` (assuming the hana code is always in the range 257-511) – mob Sep 06 '13 at 21:21
  • Well the hana sql error codes range from 1 to 4105 and its not continuous. – Chiseled Sep 06 '13 at 21:25
  • Then at least you can narrow it down. If `$?` is 768 (`3<<8`), then the hana code is one of 3, 259, 515, 771, 1027, ..., 3587, or 3843. – mob Sep 06 '13 at 21:32
  • Sure but the idea here is to resolve the error code so that we exactly know what the issue is . That way it makes debugging easier. – Chiseled Sep 06 '13 at 21:44
  • If that's the idea, then save the standard error stream of `hbdsql` and read it. – mob Sep 06 '13 at 21:47
  • yupp!! That's an option. – Chiseled Sep 06 '13 at 21:53
  • Wondering If there is a way in perl to capture both output and error (in case of failure) in the same scalar or even 2 different scalars. – Chiseled Sep 09 '13 at 16:17