sybperl-l Archive

From: "Wechsler, Steven" <Steven dot Wechsler at mtvstaff dot com>
Subject: What is going on here?! DBI/DBD Sybase question
Date: May 19 2005 9:38PM

I must be missing something obvious:

$temptable = "tempdb..ureg_delete";
$dbh->do("use staging") || die "Unable to use staging db: $dbh->errstr";
$temp_create = "select $key_column into $temptable from $root_table where
$search_column < $delete_parameter";
$dbh->do($temp_create);
$key_query = "select * from $temptable";
if (!($sth = $dbh->prepare($key_query))) {
    die "Unable to prepare sql statement $key_query: $dbh->errstr";
}

$allrows = $sth->fetchall_arrayref();
$view = "archive_$$";
$sql = "if object_id('$view') is not null drop view $view"; # an unlikely
occurrence
for ($i = @tables - 1; $i >= 0; $i--) {
    $table = "$databases[$i].$owners[$i].$tables[$i]";
    $sql = "create view $view as select a.* from $table a, $temptable b
where a.$key_column = b.$key_column";
    if (undef eq $dbh->do($sql)) {
        die "Unable to '$sql':\n" . $dbh->errstr;
    }

    $file = "$table";
    $bcp_cmd = "$bcp staging..$view out $tempdir/$file -U$login -S$server
-c";
    print $bcp_cmd if $debug;
    $rc1 = open (BCP, "|$bcp_cmd");
    if ($rc1) {
        print BCP "$password\n";
        $rc2 = close BCP;
    }
#    $sql = "drop view $view";
    $dbh->do($sql);
    die "Error on BCP out" if (!$rc1 || !$rc2);
    $cmd = "wc -l $tempdir/$file | awk '{print \$1}'";
    $bcp_rows[$i] = `$cmd`;
}

When I run the code I get this (I've taken the debug statements out of the
code snipped to make it more readable):

select UserID into tempdb..ureg_delete from ureg.dbo.UregUser where
DateLastLogin < '9/1/99'
create view archive_12073 as select a.* from ureg.dbo.UregUserLanguage a,
tempdb..ureg_delete b where a.UserID = b.UserID
/apps/sybase/OCS-12_5/bin/bcp staging..archive_12073 out
/dba/steve/ureg_test/200505191722/ureg.dbo.UregUserLanguage -Usa -Sdevsyb2
-c
Password: 
Server Message: devsyb2 - Msg 208, Level 16, State 1:
staging..archive_12073 not found. Specify owner.objectname or use sp_help to
check whether the object exists (sp_help may produce lots of output).

Unexpected result type returned.
Retrieving table format failed.
DBD::Sybase::db do failed: Server message number=2714 severity=16 state=1
line=1 server=devsyb2text=There is already an object named 'archive_12073'
in the database.
Error on BCP out at ./ureg_delete.pl line 223.

So notice that BCP doesn't find the view that I just created, but I've
confirmed that it exists by trying to recreate it after the failed BCP,
which fails because it exists. If I change the create view to a create
table, everything works as expected. If I go into the server I cannot find
the view.

Any clues?

Thanks,

Steve