in reply to Perl db programming

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.

Replies are listed 'Best First'.
Re^2: Perl db programming
by rmatil (Initiate) on Feb 05, 2009 at 08:46 UTC
    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]

In Section Seekers of Perl Wisdom