perl and sql trigger 1

[roadrash]

greetings,
i have been working on an insert statement that inserts data into an sql 2000 table, coming from a linux 7.3 running some perl scripts that are net telnet to serial devices.

the inserts run great until somebody put a trigger on one of the tables. once that happened the first insert happens, then it appears that the transaction does not close. i have even updated the trigger to do nothing but print the date with the same result.

is there something that i need to do in perl to end a transaction with a trigger on the table? (trigger waits for an insert then updates 2 other tables with the data inserted). or is perl expecting something back?? with other languages i have utilized i have never had this happen!!

i am just utilizing a basice do->

$sth = $dbh->do($sql);
unless ($sth) {
mysyslog('err',$sql);
mysyslog('err',"Unable to INSERT: $DBI::errstr");
return undef;
}
return 1;

any information is appreciated!
sean

 

[mbrooks]

$sth->finish;

M. Brooks

http://mbrooks.info

 

[roadrash]

thanx m.brooks

was a great suggestion but it threw this error:
Bareword "finish" not allowed while "strict subs" in use

never seen this before.

sean

 

[roadrash]

commented out use strict; will see if this gets it going.

 

[mbrooks]

$sth = $dbh->do($sql);
unless ($sth) {
	mysyslog('err',$sql);
	mysyslog('err',"Unable to INSERT: $DBI::errstr");
	return undef;
}
[b]$sth->finish();[/b]
return 1;

M. Brooks

http://mbrooks.info

 

[mbrooks]

You should never comment out use strict. The example above should work without errors.

M. Brooks

http://mbrooks.info

 

[roadrash]

thanx m.brooks, i uncommented the use strick and once i corrected my error, it did run clean, but still hangs on the trigger on the first insert. hhhhhmmmmmmm.........

sub RLGSDScale {
    my (@datalist) = @_;
    mysyslog('notice',"Insert Start  @datalist"); #Un-comment to see the data
#    mysyslog('notice',"Got this  @datalist[0]"); #Un-comment to see the data
    my ($sql,$sth);

    $sql="INSERT INTO RLGSDScale ".
            "(ScaleID,SampleID,TotalSampleWt,Pct0To2oz,".
            "Pct2To4oz,Pct4To6oz,Pct6To8oz,Pct8To10oz,".
            "Pct10To12oz,Pct12To14oz,Pct14To16oz,PctGT16oz,".
            "Cnt0To2oz,Cnt2To4oz,Cnt4To6oz,Cnt6To8oz,".
            "Cnt8To10oz,Cnt10To12oz,Cnt12To14oz,Cnt14To16oz,".
            "CntGT16oz)".
            "VALUES ".
            "('$datalist[0]',$datalist[1],$datalist[2],$datalist[3],".
            "$datalist[4],$datalist[5],$datalist[6],$datalist[7],".
            "$datalist[8],$datalist[9],$datalist[10],$datalist[11],".
            "$datalist[12],$datalist[13],$datalist[14],$datalist[15],".
            "$datalist[16],$datalist[17],$datalist[18],$datalist[19],".
            "$datalist[20])";

    mysyslog('notice',"Statement $sql"); #Un-comment to see the data

    $sth = $dbh->do($sql); #HANGING HERE!!! they will send data but it will not even start the next insert
# above is where the problem is i have even tried $sth->finish($sql); with no luck
    mysyslog('notice',"inserted $datalist[1]");
    unless ($sth) {
         mysyslog('err',$sql);
         mysyslog('err',"Unable to INSERT: $DBI::errstr");
         return undef;
    }
    $sth->finish();
    return 1;
}

great suggestions, just no luck yet.
sean

 

[roadrash]

update:
i wrote this little insert script with a trigger on the table. it worked just fine. i think when the person i am working with gets back on teusday, i am going to change the statement to a commit and execute style for testing.

use DBI ;
$user = 'nw' ;
$passwd = 'nw' ;


$dbh = DBI->connect('DBI:Sybase:server=mysqlserver',
$user, $passwd);
$dbh->do("use thisDB");
$sql = "insert into testdata (data) values ('hello11')";
$action = $dbh->prepare($sql) ;
$action->execute ;
$action->finish ;
$sql = "insert into testdata (data) values ('hello3')";
$action = $dbh->prepare($sql) ;
$action->execute ;
$action->finish ;
$dbh->disconnect() ;
exit(0);

any comments are always greatly appreciated.
sean

 

[MikeLacey]

Hello Sean,

Looks, obviously, as if the the problem is with MySQL rather than Perl. Maybe anyway.

If you do this operation using MySQL's SQL tool does it still hang?

Mike

I am not inscrutable.

Want great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[roadrash]

thanx for the reply mike,
everything runs great doing raw inserts from analyzer or even T-Sql.
we just did the testing with the new code:

$sql = "insert into testdata (data) values ('hello11')";
$action = $dbh->prepare($sql) ;
$action->execute ;
$action->finish ;

and it kicked right along!
thanx all for the great suggestions
sean