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

I am recieving this error from the code below.
ct_cmd_alloc failed at ./multi_copy_segmap.pl line 209. 01/28/2003 12:52 | FATAL | Can't execute SQL statement execute sp_dbsize D_CHRIS :: at ./multi_copy_segmap.pl line 209
sub get_stripe_count() {

my ( $dbh, $DB ) = @_;
my $stripes = 0;
my $size = 0;
my $stripe_size = 1000;
my $max_stripes = 32;

my $sql = "execute sp_dbsize $DB";

    my $sth = $$dbh->prepare($sql)
       or $app->error($FATAL, "Can't do SQL statement [ $sql ] :: $DBI
+::errstr");
    $sth->execute()
       or $app->error($FATAL,"Can't execute SQL statement [ $sql ] :: 
+$DBI::errstr");

$size = $sth->fetchrow_array();
$sth->finish();
$$dbh->disconnect();

[download]

Replies are listed 'Best First'.
Re: Can't execute sql
by mpeppler (Vicar) on Jan 28, 2003 at 21:26 UTC
    ct_cmd_alloc() failure is an internal failure in the Sybase libraries. It's a this shouldn't happen error that DBD::Sybase can't really do anything about.

    The Sybase manual states that the most common reason for failure of ct_cmd_alloc() is lack of memory, however I've seen this in situations where the connection structure is invalid.

    My guess is that your internal connection value (which is referenced by $$dbh) is somehow invalid, or has been corrupted. What does the script do before it gets to this point?

    Michael

[reply]
Re: Can't execute sql
by ibanix (Hermit) on Jan 28, 2003 at 19:18 UTC
    I am not well versed in DBI, but I'll take some guesses:

    * Do you really mean to say $$dbh->prepare($sql)? That would deference $dbh, I believe -- maybe you just want $dbh->prepare($sql)?

    * What's on line 209? Is it the $sth->execute() statement?

    * Have you checked that DB-specific driver (DBD::whatever) for the ct_cmd_alloc error? Or the DBI docs?

    Sorry I can't be more helpful,
    ibanix

    $ echo '$0 & $0 &' > foo; chmod a+x foo; foo;
[reply]
[d/l]
[select]
      yes line 209 is $sth->execute()
      I do execute similar code in a different sub routine. The only difference is the sql statement.
      In this code I am executing a stored proc in the other code I am executing a select statement that fetches the database name.
      As you can see I do successfully get the database name D_CHRIS
      The $$dbh is correct. We use our own module that passes back references from $app.
[reply]
[d/l]
        Check the the user running the script has correct rights to run the stored proc?

        Try some test cases against the stored proc?

        ibanix $ echo '$0 & $0 &' > foo; chmod a+x foo; foo;
[reply]
[d/l]

        Knowing the database still would help. How SP's are executed differs across databases. In Oracle, for example, I need to surround the SP to be executed with BEGIN/END statements like this: 

        $sth = $dbh->prepare(" BEGIN :1:=util.end_of_day(:2); END; ");

        [download]
[reply]
[d/l]
Re: Can't execute sql
by poj (Abbot) on Jan 28, 2003 at 20:11 UTC
    From your earlier posts I guess you are using Sybase. "Programming the Perl DBI" suggests when using stored procedures you need to use
    $sth->prepare("exec my_proc '$foo'"); # single quotes added

    [download]
    I would see if this works
    $sth->prepare("exec my_proc 'D_CHRIS'");

    [download]
    poj
[reply]
[d/l]
[select]
Re: Can't execute sql
by steves (Curate) on Jan 28, 2003 at 19:37 UTC

    As already noted, you're dereferencing $dbh, which may be valid if we could see what you're passing in to get_stripe_count(). Show that code too for more help.

    What you're passing as SQL: execute sp_dbsize D_CHRIS :: is not SQL in any database I'm aware of, but I may just not have used enough databases. What database is it you're using?

[reply]
Re: Can't execute sql
by rdfield (Priest) on Jan 29, 2003 at 09:53 UTC
    Is this a multi-threaded (or forked, even) application, by any chance? (e.g. using Apache) Are you sharing the database connection between threads (siblings)? Each thread(process) needs it's own database handle, otherwise it makes things very difficult.

    rdfield

[reply]

Back to Seekers of Perl Wisdom