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

Hi, I am new to perl and am writing a program to connect to sybase using perl DBI.

I need to get the SQL text for a give db object . for. eg. sp_helptext spName which would give the underlying SQL text for the Stored proc. I have pasted the code that am using: 

$dbh = DBI->connect("dbi:Sybase:host=<host>:port=<port>",user,pwd)
my $rv; #holds the return value from sybase stored procedure
eval {
    my $func = $dbh->prepare(q{
        BEGIN
            :rv := jwb_function(
                parameter1_in => :parameter1
            );
        END;
    });

    $func->bind_param(":parameter1", "spname");
    $func->bind_param_inout(":rv", \$rv, 1000);
    $func->execute;
        print $rv;

[download]

I have the folowing doubts:
1. I am not getting any result in $rv
2. can i substitute jwb_function with my stored proc name?

Your help is greatly appreciated

Replies are listed 'Best First'.
Re: Perl db programming
by mje (Curate) on Feb 04, 2009 at 16:25 UTC

    I think you may be rather confused. Although I do not use Sybase or DBD::Sybase I assume sp_helptext is similar in Sybase as to what it is now in SQL Server. Firstly it is a procedure, not a function, secondly it does not return the definition that is used to create an object in multiple rows but a result-set and finally the argument to it is the object you are enquiring about. i.e., you do something like:

    prepare(q/exec sp_helptext(?)/);
execute('my_object_name');
while(@data = fetchrow_array) {
    print join(q{,}, @data) . "\n";
}

    [download]

    sp_helptext creates a result-set which you can retrieve by calling fetch (fetchrow/fetchrow_arrayref etc) on the statement handle. However, you should be aware that SQL Server (which I imagine is similar to Sybase) does not return output parameters until the procedure has completed and that means you must have retrieved all result-sets and usually means calling more_results (see http://search.cpan.org/~mewp/DBD-Sybase-1.09/Sybase.pm#Handling_Multiple_Result_Sets and http://search.cpan.org/~mewp/DBD-Sybase-1.09/Sybase.pm#Calling_Stored_Procedures.

[reply]
[d/l]
      I have changed my code as below: 
       my $sth = $dbh->prepare("exec sp_helptext ?");
        $sth->execute($_[0]) || die ("cannot run sp");
        while(my @data = $sth->fetchrow_array()) {
           print join(q{,}, @data) . "\n".$data[1];
        }
        $sth->finish;
        $sth=undef;

      [download]
      when I print @data it seems to be giving me the number of lines in the SQL text. instead of the text itself. Please advise
[reply]
[d/l]
        when I print @data it seems to be giving me the number of lines in the SQL text. instead of the text itself.

        The code you show looks like it should work to me although I don't see why you added that "$data1" to the end of the print line. If you print an array in scalar context you get the number of elements in the array - that is just the way perl works. If you are saying the code you quote isn't working then show us the output and tell us what you expect.

[reply]

Back to Seekers of Perl Wisdom