Logic_Bomb421 has asked for the wisdom of the Perl Monks concerning the following question:

Alright, so I'm running this on an Ubuntu machine that's hosting my Nagios service. This script uses FreeTDS to connect to our MSSQL database, and then it should execute a query, trim the results, and finally pass it to an if/else statement so Nagios can make sense of it. The problem I'm running into is when I try to run the script, nothing happens. I hit enter and the terminal moves down a line, never returns anything, and just sits there indefinitely until I hit Ctrl+C. I've copied the script below. I'd really appreciate it if someone could lend a hand. Thanks!

#!usr/bin/perl
use strict;
my($tsql, $origresult, $result, $args);

$args = "-H production -U UsernameRemoved -P PasswordRemoved -p 1433 -
+I InstanceRemoved";

$tsql = `tsql $args`;
    
$origresult = `/usr/bin/printf "%b" "select SUM(ocount) TotalCounts fr
+om (\n
        select dbd.DateStart, count(oo.id_orders) As oCount, DATEPART(
+dw, dbd.datestart) as DayNumber\n
        from [dbo].[DaysBetweenDates](dbo.BeginningOfDay(CURRENT_TIMES
+TAMP), dbo.endofday(CURRENT_TIMESTAMP)) dbd\n
        left join orders oo on dbd.DateStart = oo.orderdate\n
        and oo.OrderPlacedBypk = '011CA1AF-0C20-4B87-93CC-11886C530E2D
+'\n
        group by dbd.DateStart\n
        ) As Total\n
        where DayNumber not in (6,7)\n
        GO"`;

$result = substr($origresult, 12, 2);
    
if ("$result" >= 20 ){
        print "OK - Order count is at or above average.\n";
        exit 0; #Nagios OK exit code
    }

elsif ("$result" <= 7)
    {
        print "CRITICAL - Order count is below average.\n Current Aver
+age = $result";
        exit 2; #Nagios CRITICAL exit code
    }
else
    {
        print "UNKNOWN - Unable to determine order average.\n Variable
+s are:";
        exit 3; #Nagios UNKNOWN exit code
    }

[download]
  • Comment on Trying to connect to MSSQL and return result. Script hangs indefinitely.
  • Download Code

Replies are listed 'Best First'.
Re: Trying to connect to MSSQL and return result. Script hangs indefinitely.
by NetWallah (Canon) on Aug 06, 2014 at 19:20 UTC
    I suspect it is the "%b" that you are attempting to pass to "printf", but is getting eaten by perl interpolation.

    Try this to reproduce the issue: 

     perl -e 'print qx|printf "%b" "this is a q\'uot\'ed test|'

    [download]
    UPDATE:

    More likely, each single quote used inside the backticks needs to be double-single quoted replaced by the sequence '"'"' , to escape it.

    UPDATE 2:

    In the previous example, the backslashes are useless. To produce single quotes in the output, the following works (on Linux (SLES). The O.P had hints that although he's using MSSQL, the binaries (e.g. /usr/bin/printf) point to Linux): 

     perl -e 'print qx|echo "%b" "this is a qu'"'"'oted'\'' test"|,"\n"'

    [download]
    Note - that the slightly shorter alternate replacement for a single quote '\'' used above also works.

            Profanity is the one language all programmers know best.

[reply]
[d/l]
[select]
      Maybe I'm missing something: How would incorrect quoting cause Perl to hang? Trying bad quoting on qx I get "sh: Syntax error: Unterminated quoted string". Are there shells that hang on bad quoting?
[reply]
[d/l]
        My bash shell on SLES( 2.6.32.12-0.7-default #1 SMP 2010-05-20 11:14:20 +0200 x86_64 x86_64 x86_64 GNU/Linux) hangs on the first code sample, waiting for the close single quote.

        After one is provided, it complains (and perl does too .. the intent was to show code that appears to hang in the shell). Here is the session: 

        />: perl -e 'print qx|printf "%b" "this is a q\'uot\'ed test|'
>
> .. Hanging here
>  .. until quote..
> '
-bash:

.. Hanging here
 .. until quote..
: command not found
Can't find string terminator "|" anywhere before EOF at -e line 1.
/>:

        [download]

                Profanity is the one language all programmers know best.

[reply]
[d/l]
Re: Trying to connect to MSSQL and return result. Script hangs indefinitely.
by Anonymous Monk on Aug 06, 2014 at 18:36 UTC

    What happens when you run tsql -H production -U UsernameRemoved -P PasswordRemoved -p 1433 -I InstanceRemoved from the command line? (same question for printf, although I suspect that's not the problem)

[reply]
[d/l]
[select]

      When running the normal tsql command, everything connects to MSSQL just fine. I've been reading into this more, and it appears I'd be better off using DBI to connect perl to a database. Does that sounds right to you?

[reply]

        "just fine" is a little too vague, posting a sample command line session would be better.

        If the tsql you're using is this one: http://linux.die.net/man/1/tsql , then the problem is that it enters an interactive shell that you have to exit from, that's why it's hanging there (it's waiting for input). A quick Googling suggests that http://linux.die.net/man/1/bsqlodbc or http://linux.die.net/man/1/bsqldb might be better suited for your use case.

        If you're going to be running multiple queries on the database in one session, then yes, DBI is the way to go. However, for a single SELECT query run once every few minutes that just spits out a value, backticks are often an acceptable solution. A little better might be the capture function from IPC::System::Simple (that has better error handling), and IPC::Run even lets you set timeouts on external commands.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom