Perl script hangs performing DBI execute statement of stored procedure

Environment[indent]TS 7.3.1 on W2K8 64-bit[/indent]
Issue Summary[indent]Perl script hangs performing DBI execute statement of stored procedure[/indent]
Issue Details[indent]The basic code flow is this:

fix it so that the Script will run completely through all subroutines / stored procedures from a clean state.

Find more posts tagged with

Comments

Adam Stoller

A few more pieces of information:

ISCBorisB

What is the DB behind all that?

Can you modify Disconnect to something like this:
$dbh->disconnect || print "$DBI::errstr $DBI::err\n";

Do you get anything? If you do, what is it?

Adam Stoller

What is the DB behind all that?

Ah yes, forgot to mention that - It's Microsoft Sql Server - I believe 2008, though it might be 2005.

Can you modify Disconnect to something like this:
$dbh->disconnect || print "$DBI::errstr $DBI::err\n";

Do you get anything? If you do, what is it?

Made the modification, nothing shows up from it. I have debug lines both before and after the statement too.:

debug("Disconnecting from database");
$dbh->disconnect() || debug("DB Disconnect: " . $dbh->errstr);
debug("Disconnected from database");

All I see in the log file is:[indent]Disconnecting from database
Disconnected from database[/indent]So nothing new there.

Again - the thing that's really annoying me is that the hanging is occurring in the DBI execute() statement but only if we do file creation after disconnecting from the previous database interaction. Well, that and the fact that it's hanging and not producing an error.

I cannot figure out how the previous post-database-interaction code could be affecting the next database-interaction process.

Adam Stoller

I didn't mention this before, but the script is destined to be an external task script in a workflow.
The code, at a high level was:

I use:my $cmd = qq($iwhome/bin/iwaddtaskfile $taskid "$relpath" "$comment");my $results = qx($cmd 2>&1);my $status = $? >> 8;if ($status) { … }it actually works just fine!So it's not the task, it's not the adding the files to the task, it's the adding the files to the task using the TeamSite::WFtask interface that somehow causes DBI to hang.Sheesh!There may be something deeper here, but at this point I don't really have the time, patience, or inclination to investigate it further.I'll leave the thread open for a bit in case anyone wants to add some useful insights.

ISCBorisB

I'll leave the thread open for a bit in case anyone wants to add some useful insights

Not sure how useful that might be but here is what I found...

WFtask::AddFile does pretty much what you did - it executes iwaddtaskfile CLT on your behalf.
The difference is that it actually calls it through TeamSite::WFutils::call_clt Method.
This baby in turn executes Win32: Smiley Tongue

rocess::Create method in Windows Environment.

This is the main suspect. There are several reputable sources claiming a bug in this module
that causes "already established Sockets to stop working", see for example this old one.
(There are newer articles, this one is uncommonly clear)

Goes without saying, I'm not 100% sure if it is indeed the case and if it is, what are OS/Perl/DBI
permutations to expose the issue. Discussions that I found on the topic mostly pontificate on how
to work around the problem.

Adam Stoller

Interesting - it would certainly seem to fit the symptoms - and makes me feel a bit better about the work-around (i.e. explaining why it works and the WFtask method might not)

I guess I can chalk this up to yet-another-reason-to-dislike-Windows ;-)

Thanks for the research!

ISCBorisB

Interesting - it would certainly seem to fit the symptoms - and makes me feel a bit better about the work-around (i.e. explaining why it works and the WFtask method might not)

I guess I can chalk this up to yet-another-reason-to-dislike-Windows ;-)

Thanks for the research!

No problem, I was curious too...

Adam Stoller

No problem, I was curious too...

Well, on that note, I now declare this bridged - uhm thread - closed.
Thanks again.