Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

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";
$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
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
    print $bcp_cmd if $debug;
    $rc1 = open (BCP, "|$bcp_cmd");
    if ($rc1) {
        print BCP "$password\n";
        $rc2 = close BCP;
#    $sql = "drop view $view";
    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
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 ./ 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?