DBI Error with MSSQL

I am on TS 6.5 sp2 on windows. It is a know error that if we are doing fetchrow_array( ) inside our perl with MSSQL to get row data it fails. Is there a workaround for this. Here is my snippet.

[html]
my $getquery = "SELECT areavpath FROM tbl_products_generic WHERE areavpath='\products\servers'";
$sth = $dbh->prepare($getquery);
$rc = $sth->execute;
die($sth->errstr) if $sth->err;

my $fetch=$sth->fetchrow_array( );
print "Fetch :$fetch\n";
[/html]

Fetch is always empty. Donno why.

Thank You

Find more posts tagged with

Comments

Adam Stoller

I am on TS 6.5 sp2 on windows. It is a know error that if we are doing fetchrow_array( ) inside our perl with MSSQL to get row data it fails. Is there a workaround for this. Here is my snippet.

[html]
my $getquery = "SELECT areavpath FROM tbl_products_generic WHERE areavpath='\products\servers'";
$sth = $dbh->prepare($getquery);
$rc = $sth->execute;
die($sth->errstr) if $sth->err;

my $fetch=$sth->fetchrow_array( );
print "Fetch :$fetch\n";
[/html]

Fetch is always empty. Donno why.

Thank You

What do you mean that "Fetch is always empty"?
Do you mean you see: "Fetch: " or you see "Fetch: ARRAY(0x38220)" (the number would be different - the general form is what I'm asking about)?

If the former - and your script didn't die - then perhaps the query is wrong.
If the latter - then it's because $fetch is a reference to an array, and not a string - you would need to do something like:

...
print "Fetch :" . join("\n", @{$fetch}) . "\n";

Bhargav Coca

thank You adam for the reply. I got past the fetch part,
So I am getting the array as it is supposed to, not that i have it, I am checking for a cetain occurance in that array and updating my table based on that, for some reason I am getting this error now and the update of the table is not happening.

DBD: Smiley Surprised

DBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

Here is my snippet

[html]
if ($new_location2 eq $data_table)
{
print "Am I here\n";
my $sel1 = $dbh->prepare("UPDATE tbl_products_generic SET content='Test123*' WHERE areavpath='$new_location2'");
$sel1->execute();

if ($sel1->errstr)
{
print "Oops There is an error";

}
}

[/html]

p.s. $new_location2="\\"."products"."\\"."servers";

Thank You

Adam Stoller

thank You adam for the reply. I got past the fetch part,
So I am getting the array as it is supposed to, not that i have it, I am checking for a cetain occurance in that array and updating my table based on that, for some reason I am getting this error now and the update of the table is not happening.

DBD:DBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt (SQL-HY000)(DBD: st_execute/SQLExecute err=-1)

Here is my snippet

[html]
if ($new_location2 eq $data_table)
{
print "Am I here\n";
my $sel1 = $dbh->prepare("UPDATE tbl_products_generic SET content='Test123*' WHERE areavpath='$new_location2'");
$sel1->execute();

if ($sel1->errstr)
{
print "Oops There is an error";

}
}

[/html]

p.s. $new_location2="\\"."products"."\\"."servers";

Thank You

You'll probably have to talk to your DBA - but my guess is that there's some lock stuck on the DB from a previous attempt that is preventing new connections and you either have to find and remove that lock or stop/restart the DB (these are guesses - I'm not positive on details for this stuff - I'm not a DBA)

Bhargav Coca

well we do not have any DBA at our place, every one know a little more or less about DBs. So I have to fix my own errors, pity me. But in this regard, I did manage to make a workaround which works fabulously.

All i am doing is writing the update statement in a different perl file and calling this perl file from this perl script. Well this is weird, but it is working fine.

Thank You

Bhargav Coca

I am stumbling at a simple while loop and I could never get my if loop to run even though the condition is met. can you correct me please

[html]
while ( ($new_location) = $sth->fetchrow_array( ) )
{
$new_location2 .=$new_location;
print LOG "Location : $new_location2\n";
}

if ($new_location2 eq $data_table)
{
print LOG "Am I here\n";
}

[/html]

Thank you

Adam Stoller

I am stumbling at a simple while loop and I could never get my if loop to run even though the condition is met. can you correct me please[html] while ( ($new_location) = $sth->fetchrow_array( ) )
{
$new_location2 .=$new_location;
print LOG "Location : $new_location2\n";
}

if ($new_location2 eq $data_table)
{
print LOG "Am I here\n";
}[/html]Thank you

Your code again seems to be assuming that fetchrow_array is returning a string instead of an array - which you are stuffing into a scalar instead of an array (if you want the scalar ref, you need to use fetchrow_arrayref) - and in either case you cannot use the .= operator when dealing with arrays or references to arrays.

Bhargav Coca

Your code again seems to be assuming that fetchrow_array is returning a string instead of an array - which you are stuffing into a scalar instead of an array (if you want the scalar ref, you need to use fetchrow_arrayref) - and in either case you cannot use the .= operator when dealing with arrays or references to arrays.

Thank You Adam, I Totally agree with you. And in this regard I have changed my code so that I can get the array. When the array is being read inside the forloop. But at some point when the variable is read in forloop I am expecting the if statement to be executed but what exactly is happening is the else part is executed fine and the if part always fails, I other words for any value of $data_table, the if loop is stuck in the else part.

Here is the snippet.

Thanks

[html]
while ( ($new_location) = $sth->fetchrow_array( ) )
{
$new_location2 =$new_location;
push(@make, $new_location2);
}

foreach $_(@make)
{
my $new_location3=$_;
print "$_\n";
}

if (($new_location3)eq($data_table))
{
print "Variables are Equal\n";
}
else
{
print "Variables are Not Equal";
}

[/html]

Pawan

Try this.[html]
....
...
foreach $_(@make)
{
my $new_location3=$_;
print "$_\n";

print "||$new_location3||$data_table||\n";
if (($new_location3)eq($data_table))
{
print "Variables are Equal\n";
}
else
{
print "Variables are Not Equal";
}
}
[/html]

Bhargav Coca

Thank You pawan, I tried this longtime back, but what happens here is that there will be one match and then if part will be printed, but for every mis match the else part is printed through out. I do not want that. i want the the condition to be checked and run if part if it is true Just once and execute else part if it is not true Just once.

I even tried using exit 0, but that will not help either. Here is an example which suites my need. I am trying to implement in my case.

This is Example:

[html]

my @urllist = ('http://www.fee.org/','http://www.fie.org/','http://www.foe.org/','http://www.fum.org/');
# equivalent
my $urltofind = 'http://www.fee.org/';

# written another way...
if (grep {m|^$urltofind?$|} @urllist) { print "It's in there!\n" }

[/html]

Bhargav Coca

grep seems to work like a charm, but perl monks out there can any one tell me a way to do this using while and if please.

Thank You

Bhargav Coca

Again it is always stuck in else part. I guess I need to parse the backslashes, even then it doesn't work.
here is my snippet
[html]
my @urllist = ('\html\images','\html\includes','\media\videos','\media\pressrelease');
# equivalent
my $urltofind = '\html\images';

# written another way...
if (grep {m|^$urltofind?$|} @urllist)
{
print "It's in there!\n"
}
else
{
print "It's Not there!\n";
}

[/html]

Thank You

Pawan

Hi Lilley,
Actually, I meant to say this:

Try this.[html]
....
...
my $flag = "0";
foreach $_(@make)
{
my $new_location3=$_;
print "$_\n";

print "||$new_location3||$data_table||\n";
if (($new_location3) eq ($data_table))
{
$flag = "1";
print "Variables are Equal:$flag\n";
}
else
{
print "Variables are Not Equal";
}
}
if ($flag eq "1")
{
print "Variables are Equal\n"; //This executes either once or not
}
else
{
print "Variables are Not Equal"; //This executes either once or not
}
[/html]

HTH

Adam Stoller

Thank You Adam, I Totally agree with you. And in this regard I have changed my code so that I can get the array. When the array is being read inside the forloop. But at some point when the variable is read in forloop I am expecting the if statement to be executed but what exactly is happening is the else part is executed fine and the if part always fails, I other words for any value of $data_table, the if loop is stuck in the else part.

Here is the snippet.

Thanks

[html]
while ( ($new_location) = $sth->fetchrow_array( ) )
{
$new_location2 =$new_location;
push(@make, $new_location2);
}

foreach $_(@make)
{
my $new_location3=$_;
print "$_\n";
}

if (($new_location3)eq($data_table))
{
print "Variables are Equal\n";
}
else
{
print "Variables are Not Equal";
}

[/html]

You keep showing the same code - and the code is logically broken from the get-go:

 while ( ($new_location) = $sth->fetchrow_array( ) )  
  {
   $new_location2 =$new_location;
   push(@make, $new_location2);
  }

$new_location is a scalar, $sth->fetchrow_array returns an array. From my reading of the perldoc for DBI - the code should either be:

while ($new_location = $sth->fetchrow_arrayref()) {
    push(@make, $new_location); # unclear if this is really what you want, but ...
}

while (new_location = $sth->fetchrow_array()) {
    push(@make, new_location);
}

The next bit of code is also logically flawed:

  foreach $_(@make)
  {
                         my $new_location3=$_;
  print "$_\n";
  }

$new_location3 is defined solely for the scope of the foreach loop, and even if it weren't - this is essentially the same as:

my $new_location3 = $make[-1];

i.e. you're looping through the entire array without doing anything, so the variable will end up set to the last entry in the array.
We then come to the end of the code snippet:

    if (($new_location3)eq($data_table)) 
    {
     print "Variables are Equal\n";
    }
    else
    {
     print "Variables are Not Equal";
    }

As pointed out before - $new_location3 is empty at this point as it references a non-declared globally scoped variable which was never populated (if you add use strict; use warnings; to your script you'd already know about this problem).
I have no idea what $data_table is, so I have no way of knowing whether the logic of trying to compare a reference to an array of row-data to the contents of $data_table makes sense - but my guess is it does not - and thus you'll never get into the IF part of the clause, and always end up in the ELSE clause.

You need to spend a bit more time reading the perldocs, reading some good Perl books, and understanding the code that you write.

Bhargav Coca

I am sorry adam, Probably my way of explaining the problem seems vague, I totally agree with what you said, I have referenced through my Perl Books for while loop My way also works. I do not need to assign an array while fetching the row, since the variable keeps taking the values of the rows (It doesn't store it, except for the last value). So I was pusing tha scalar into the array in the while. (I agree that Yours is absolutely right).

I was flipped over defining the scalars globally, since the values are available only in the loop. Once i figured that out everything else was piece of cake. I guess i have spent more time not thinking about that variable, but now everything is good.

Thank You