... something in the environment?

chexmix has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I'm tearing my hair out over a problem, and I'd appreciate any guidance or pointers.

I worked very hard on an update to a script we use at work. My update involved using stored procedures instead of direct database (Sybase) queries. This way I could call the stored procedures and get back values identified as output params.

The construct looks like this:

    my ( $thing_id, $action );

    my $update_or_insert = $dbh1->prepare( qq/exec stored_procedure /
        . qq/\@p1 = ?, \@p2 = ?, \@p3 = ?, \@p4 = ?, \@p5 = ?, /
        . qq/\@p6 = ?, \@p7 = ?, \@p8 = ?, \@p9 = ?, /
        . qq/\@thing_id = ? output, \@action = ? output/);

    $update_or_insert->bind_param(1, $p1, SQL_INTEGER);
    $update_or_insert->bind_param(2, $p2, SQL_INTEGER);
    $update_or_insert->bind_param(3, $p3, SQL_INTEGER);
    $update_or_insert->bind_param(4, $p4, SQL_VARCHAR);
    $update_or_insert->bind_param(5, $p5, SQL_DATETIME);
    $update_or_insert->bind_param(6, $p6, SQL_VARCHAR);
    $update_or_insert->bind_param(7, $p7, SQL_VARCHAR);
    $update_or_insert->bind_param(8, $p8, SQL_VARCHAR);
    $update_or_insert->bind_param(9, $p9, SQL_VARCHAR);
    $update_or_insert->bind_param(10, $thing_id, SQL_INTEGER);
    $update_or_insert->bind_param(11, $action, SQL_VARCHAR );

    $update_or_insert->execute();

    ( $thing_id, $action ) = $update_or_insert->syb_output_params();
    
    $update_or_insert->finish;
[download]

I got this working flawlessly in testing. But now I am running it in our 'shadow test' environment and am getting uninitialized value complaints for the bind_param statements for $thing_id and $action. Ultimately the script fails.

These were just as unitialized in my tests, but those runs didn't complain and they ran to completion.

To be fair, the input files to the program have changed some and have additional lines -- these new lines are skipped by default anyway, but I added a line of code to do it exolicitly.

I did figure out that our shadow test environment was running a different version of Perl. I fixed this: both are running 5.22.2, which is what I ran in my tests and what we run in production. This didn't help.

I confirmed that the same version of DBD::Sybase is being run in both places.

Is there something that could cause this that I am failing to consider? I asked our dba whether it could be Sybase-related but he said no.

Very tired. I could figure something out after I post this, but ...

Thanks,

Glenn

Comment on ... something in the environment? Download Code

Replies are listed 'Best First'.
Re: ... something in the environment? by Fletch (Bishop) on Feb 27, 2020 at 20:27 UTC
Completely wild guess but maybe try turning tracing on `$update_or_insert->trace( 2 )` and see if that produces anything interesting and/or different between the two platforms. The cake is a lie. The cake is a lie. The cake is a lie.	[reply] [d/l]
Re^2: ... something in the environment? by chexmix (Hermit) on Feb 27, 2020 at 20:41 UTC
Thanks for the reply. I did create tracelogs but don't have one from my test environment yet. We've reverted to using the old code for now. I will return to this after our shadow test is over. This stinks for me. This project, at least in the testing phase, was a big code change and I was more methodical and focused than I've ever been before (hey, I'm only 58!). I was starting to feel more competent at this stuff. Then this.	[reply]
Re: ... something in the environment? by jo37 (Deacon) on Feb 27, 2020 at 21:29 UTC
Maybe the problem hides in the omitted part of your code. There is no declaration shown for `$p1` to `$p9` and you don't have a problem when `bind_param` is called with these variables. Just a guess... -jo	[reply] [d/l] [select]
Re^2: ... something in the environment? by chexmix (Hermit) on Feb 27, 2020 at 21:50 UTC
Thanks. I should have said (and did in a previous incarnation of this post, lost when my work comp temporarily died!) that $p1 - 9 are parameters that are passed in to the subroutine this is in. So they're all initialized. Maybe I should be concerned that I DIDN'T get 'uninitialized value' messages in my testing environment ...? I'm really stumped. I have found what I thought was an obvious solution to this three times or so. Also, if it matters, the full complaint is "Use of uninitialized value in subroutine entry at <script> <line number>".	[reply]
Re^3: ... something in the environment? by bliako (Monsignor) on Feb 27, 2020 at 22:58 UTC
this is beyond my ability but can I assume that this is XS related? That is, it comes from within the db driver? Or simpler explanation: the db stores some illegal values or undef? This is what made me think that: Re: Source of warning message: "Use of uninitialized value in subroutine entry". (that post has cult value too) bw, bliako	[reply]
Re^2: ... something in the environment? by chexmix (Hermit) on Mar 03, 2020 at 16:52 UTC
A big thank you to all who responded. I haven't been able to get to the bottom of this yet -- and have been asked to stop trying until a later date. When I fix the issue I will post the fix. I haven't been on here much of late and had forgotten what a great community it is. Glenn	[reply]
Re: ... something in the environment? by Eily (Monsignor) on Feb 28, 2020 at 13:45 UTC
What's the exact error message? It might have a little more information on what is happening exactly (or where it is happening). The "uninitialized value" warning is actually disabled by default, and enabled either with use warnings, or the -w / -W / -X options, or the $^W variable. So maybe there's some config somewhere or another module that changes warnings activation. You can try this in your test version to see if the warnings appear: `{ use warnings 'uninitialized'; $update_or_insert->bind_param(10, $thing_id, SQL_INTEGER); $update_or_insert->bind_param(11, $action, SQL_VARCHAR ); }` [download] If the warning appears, your code had the issue all along, but just didn't tell you about it. In that case, perl just silently replaces your value by either "" or 0 depending on context, so you can do it explicitly to obtain the same result. You could also try `no warnings 'uninitialized';` in your shadow test version, but it's better to fix the warnings than just hide. Edit: it looks like -X will disable all warnings regardless of whatever else you try to do, so the `use warnings;` wouldn't have any effect in that case.	[reply] [d/l] [select]
Re: ... something in the environment? by hexcoder (Curate) on Feb 28, 2020 at 11:09 UTC
If you are able to run your code with the perl debugger, i suggest you let the debugger stop when the warning appears and then look at the values. See here how to setup the debugger to do this: Re: Debugging a program Hexcoder	[reply]
Re: ... something in the environment? by Veltro (Hermit) on Feb 28, 2020 at 13:12 UTC
What does the stored procedure return in case the update or insert fails?	[reply]
Re^2: ... something in the environment? by chexmix (Hermit) on Feb 28, 2020 at 15:38 UTC
In case of failure, the subroutine returns the nature of the failure (in $action, if I recall correctly -- I'm home sick). In this case the transaction is rolled back, too. The program is supposed to print the failure to an error log and quit.	[reply]
Re^3: ... something in the environment? by Veltro (Hermit) on Feb 28, 2020 at 16:02 UTC
Ok, this is something you may need to verify. The reason why I asked is to be sure that the return values are valid SQL_INTEGER and SQL_VARCHAR. Usually the program writes back the values that it received by the bind instructions which are in this case undef. I'm not sure but I think that when the stored proc doesn't set them then undefs are returned and those may be invalid values for the type specified.	[reply]

Back to Seekers of Perl Wisdom