RE: What is going on here?! DBI/DBD Sybase question

Never mind, I figured out the problem - I hadn't done an execute after the
prepare at the beginning of the snippet; it doesn't fully explain the
problem (why was I able to do a create table) but after I added the execute
everything started behaving as it should.
 
Michael, any explanation for this odd behaviour?
 
 

  _____  

From: Wechsler, Steven 
Sent: Thursday, May 19, 2005 5:38 PM
To: [email protected]
Subject: What is going on here?! DBI/DBD Sybase question



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