Perl DBI and binding parameters

I have a bit of an uncommon situation. I'm trying to write to an Oracle database by using TPL's which contain Perl DBI code. I assume that most people would use DataDeploy to handle this, but DD was a bit too inflexible and I needed to have the ability to manipulate the data prior to it going to the database.

I have one TPL for each database that we deploy to. The databases are Oracle 9i. Perl DBI works great with any hard coded values or variables included in the SQL string. The problem occurs when I try to use the bind param functionality of Perl DBI to prevent having to prepare SQL statements for several pieces of data that are going to be using the same SQL code.

Here's an example: I have 3 rows to insert of an ID and a Date. Same insert SQL for each (INSERT into data_table ( id, date) VALUES ($ID,$date)). However, the date is different for each of the 3 inserts, so I'd rather use the SQL - INSERT into data_table ( id, date) VALUES ($ID,?). Then do one prepare and execute multiple times passing in a different value for the date each time it's executed. This is where the problem lies.

Outside of TeamSite templating, this works flawlessly. Once inside templating, using the built-in generate or preview functionality, it fails with odd Oracle errors: ORA-03113: end-of-file on communication channel. After much debugging, I finally realized that by hard coding the values and not using bind param, I could get past this. But it's very inefficient and so I struggled for another solution.

The solution I came up with is this: I run iwpt_compile.ipl by passing in the DCR and TPL information. I also use the -ocode param to export the perl code to an .ipl file. Then I execute that .ipl file. This seems to bypass the problem. It's also a little bit inefficient since I have to execute 2 system commands.

My question is why is it that executing iwpt_compile.ipl directly on these TPLs with bind param fails, but executing the ocode that is created succeeds? I have to admit that after finding a reasonable workaround, I haven't had much time to delve into the reasons further.

Hoping that this saves some folks some frustration that I went through and perhaps leads to reasons and better work arounds. Please let me know if this posting has helped you or if you have any answers to my question.

Thanks,

Jason

Find more posts tagged with

Comments

chuckles

My best guess is that it is related to environment variables. Make sure that ORACLE_HOME and LD_LIBRARY_PATH are set in the TPL code and that might solve it for you.

Migrateduser

My best guess is that it is related to environment variables. Make sure that ORACLE_HOME and LD_LIBRARY_PATH are set in the TPL code and that might solve it for you.

We also thought of this and I captured the ENV variables. The ones that mattered were the same. Also, we have code for setting the ORACLE_HOME and LD_LIBRARY_PATH variables built in. Also, the DBI has no problems connecting to the DB. It only has problems with bind params. Any other data interaction with the database works great. Seems unlikely to me that those ENV Variables would have an impact on whether bind params work when everything else about the database interaction does.

- Jason

Adam Stoller

We also thought of this and I captured the ENV variables. The ones that mattered were the same. Also, we have code for setting the ORACLE_HOME and LD_LIBRARY_PATH variables built in. Also, the DBI has no problems connecting to the DB. It only has problems with bind params. Any other data interaction with the database works great. Seems unlikely to me that those ENV Variables would have an impact on whether bind params work when everything else about the database interaction does.

- Jason

Can you show the actual code where you are trying to perform this operation?
It might be a syntactic issue.

Migrateduser

It's not a coding issue. I've spent about 16 hours on this single issue and I have read through a good portion of the O'Reilly Book: Programming the Perl DBI. Also, I've got another Perl developer working with me and he has also looked over my code and has run into this issue.

Here's the pertinent code:

my $InsertSQL = "INSERT INTO article_audience ( article_id, audience_id) VALUES ($newID,?)";
my $st = $dbh->prepare($InsertSQL);
foreach my $audience (@audiences)
{
$st->bind_param(1,$audience+0);
$st->execute() or $retVal=0;
}

Nothing too complicated.

- Jason

chuckles

How are you setting $newID? Is that just a sequence.nextval?

Migrateduser

How are you setting $newID? Is that just a sequence.nextval?

I'm not sure what that has to do with the problem I'm having, but the value is set by a sequence which get's read into Perl from the database. We then create an article record with that ID and then associate that article record with one or more audiences in the table that is being manipulated in the above code.

There's nothing wrong with the Perl. The $newID value is set correctly and we have used debug statements to verify all values. We've outputted the values that are being attached with the bind_param also. They are correct, but the result is the ORA error. I've spoken to a DBA and he says that the problem is on our side prior to getting to the database. I agree with him. Something is definitely going wrong with the DBI. One key point that you may have overlooked in the original post is that this works perfectly if I use the -ocode option with iwpt_compile.ipl and then execute the generated perl. Therefore, I know that all variables are being set correctly and that the syntax is all right. The only thing that I could figure was an environment variable. But they all check out.

I understand you're just trying to help, and I appreciate it. The thing is, I've spent hours looking at this code and researching. The simple problems have been ruled out by 2 of us here. I'm convinced that it's something to do with the inner working of iwpt_compile.ipl and how those differ when run with the -ocode option.

- Jason

Adam Stoller

I'm convinced that it's something to do with the inner working of iwpt_compile.ipl and how those differ when run with the -ocode option.

For clarification purposes, if nothing else (since I don't have access to the environment in which you are working and have not experienced the problem you are having)... Are you saying that if you run:

iwpt_compile.ipl .... -ocode x.ipl; iwperl x.ipl

it works, but if you run, from the same command line:

iwpt_compile.ipl .... (withouth -ocode)

it fails?

Or is it that it fails when run through a workflow or from the GUI?

If it's the first two situations - then it sounds like a bug that should be reported to Interwoven Support (along with all the files necessary to reproduce the problem).

If it's not the first two situations - then the most significant difference is the environment in which the command is run. You say you checked all the environment variables - you "captured the ENV variables. The ones that mattered were the same." - that last sentence has me wondering if you overlooked something that you didn't think mattered, but which in fact did.

Migrateduser

Ghoti,

Yes, the first scenario is exactly right. If I run it as iwpt_compile.ipl -ocode x.ipl; iwperl x.ipl, it works. If I just try to run iwpt_compile.ipl without the ocode, it fails with the error described in my first posting.

I agree, it's likely a bug, but there are so many more obvious bugs that I doubt it would ever get fixed. My original intention of the posting was to inform others so that they don't spend the hours upon hours trying to fix an unsolvable problem, and to find other potential work-arounds that I didn't think of. I suppose the most obvious is to not use bind_param and just create a perl loop that prepares and executes the SQL over and over. Is very inefficient, though.

- Jason