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

Hi any one can explain below Subroutine. original subroutine has only one argument but i need to pass two argument to below stored procedure. original way of calling Sub is ExecStoredProcedureArg1("pr_CustomForPV", $AutoNumber, "IsCustom") I have changed the sub to like ExecStoredProcedureArg1("pr_CustomForPV", "$VersionID,$AutoNumber", "IsCustom"). it is still working fine. my question is how the sub is working with two parameters and please explain subroutine what is qq.what is fetch row? what i need to learn to understand this subroutine.

if  (0 != ExecStoredProcedureArg1("pr_CustomForPV", "$VersionID,$AutoN
+umber", "IsCustom"))

{
   # Do some thing
}



sub ExecStoredProcedureArg1($$$)
{
    writelog(info,"Entering ExecStoredProcedureArg1() : ====");
    my ($strStoredProc,$strArg, $strResultField) = @_;

    my $strResultValue = "";

    my $mydb = Win32::ODBC->new($DSN);
    if (!$mydb)
    {
        Win32::ODBC::DumpError();
        writelog(logdie,"Failed to connect to $DB_SERVER!");
    }

    my $sql = qq|$strStoredProc $strArg|;

    if ($mydb->Sql($sql))
    {
        Win32::ODBC::DumpError();
    }
    if ($mydb->FetchRow())
    {
        %DataSet = $mydb->DataHash();
        $strResultValue = $DataSet{$strResultField};
    }
    else
    {
        writelog(info,"No row found for $strResultField");
        $mydb->Close();
    }
    $mydb->Close();

    writelog(info,"$strStoredProc $strArg for $strResultField:  value 
+= $strResultValue");

    writelog(info,"Exiting ExecStoredProcedureArg1() : ====");
    return $strResultValue;
}

[download]
stored procedure: In the below procedure i have added @number parameter.original stored procedure has only one parameter 
CREATE PROCEDURE dbo.pr_CustomForPV
(
    @VersionID int,
    @Number int
)
AS



SELECT
CASE
    WHEN U.SourceFileName IS NOT NULL Then 1
    ELSE 0
END AS IsCustom
# some joins

[download]

Replies are listed 'Best First'.
Re: Calling Stored procedure from perl
by desemondo (Hermit) on Mar 02, 2010 at 01:36 UTC
    qq is simply another means of double quoting a string. See perlop for full details on qq and its sibbling operators.

    eg. 
    my $var = 'blue';

my $string1 = "the sky is $var\n";
my $string2 = qq|the sky is $var\n|;

    [download]
    Both string1 and string2 are identical. The qq operator usually gets used when you need the " character within the string itself, and allows you to avoid having to escape it with a \.

    eg. 
    my $var = 'blue';

my $string1 = "the \"sky\" is $var\n";
my $string2 = qq|the "sky" is $var\n|;

    [download]
    Both string1 and string2 are still identical.
[reply]
[d/l]
[select]

      Thank you very much for your reply. can u please tell me what is happening here and what i need to learn to understand below code.

      my $sql = qq|$strStoredProc $strArg|;

    if ($mydb->Sql($sql))
    {
        Win32::ODBC::DumpError();
    }
    if ($mydb->FetchRow())
    {
        %DataSet = $mydb->DataHash();
        $strResultValue = $DataSet{$strResultField};
    }
    else
    {
        writelog(info,"No row found for $strResultField");
        $mydb->Close();
    }
    $mydb->Close();

      [download]
[reply]
[d/l]
        Take a look through perldoc Win32::ODBC this will introduce the concepts you need to follow this code and define the methods used in your snippet.

        Essentially it creates a string of the stored procedure and argument(s) and then makes a single row selection from the results of this procedure which it returns as a hash and checks for a value in the field specified in the variable $strResultField

        In this specific caseExecStoredProcedureArg1("pr_CustomForPV",  "$VersionID,$AutoNumber", "IsCustom") it runs the pr_CustomForPV procedure with the values in $VersionID and $AutoNumber and checks to see if the IsCustom field is set

        print "Good ",qw(night morning afternoon evening)[(localtime)[2]/6]," fellow monks."
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom