Nested Select terminates primary select

I am reading data from an Oracle table and updating an MS Access table
using WIN32:ODBC. I have nested a select statement. The inner
select seems to be prematurely terminateing my outside select
statement. How can I write the nested select so that
it does not affect the "while($db->FetchRow())" of the outer select
statement?

Here is the code:

sub getStatus{

$sqlStatement ="select sku_code, " .
"SKU_PARENT_ID," .
"SKU_STATUS_CD " .
"from bluem_main.sku " .
"where version = $version" .
"order by sku_code";

if ($db->Sql($sqlStatement))
{
print "$sqlStatement\n";
print "SQL failed 2.\n";
print "*** Error: " .
Win32::ODBC::DumpError() .
"\n";
$db->Close();
print "SQL failed. \n";
exit;
}
else
{
while($db->FetchRow())
{
undef %Data;
%Data = $db->DataHash();
$skuCode = "$Data{SKU_CODE}";
$statVal = "$Data{SKU_STATUS_CD}";
$sku_parent_id = "$Data{SKU_PARENT_ID}";
print "ttt 1 $skuCode\n";
updateStatus();
} # end while


} # end else
} # END getStatus


sub updateStatus{
if ($statVal eq "A")
{
$status = "Active";
getCategory();
$sqlStatement = "UPDATE [SKUs] " .
"SET Status=\'$status\', " .
"Category=\'$catVal\' " .
"WHERE SKU_Code = \'$skuCode\'";
}
else {
$status = "Inactive";
$sqlStatement = "UPDATE [SKUs] " .
"SET Status=\'$status\' " .
"WHERE SKU_Code = \'$skuCode\'";
}
$db2->Sql($sqlStatement);
$db2->Transact( $db2->SQL_COMMIT );
} # END updateStatus


sub getCategory{
$sqlStatement = "SELECT s.sku_code,o.oba_int_val " .
"FROM bluem_main.sku s, bluem_main.object_attribute o " .
"WHERE o.oba_atr_id = $attIdCat " .
"AND s.SKU_CODE = $skuCode " .
"AND s.SKU_PARENT_ID= $sku_parent_id " .
"AND o.oba_obj_id= $sku_parent_id " .
"AND S.version = $version " .
"AND S.SKU_STATUS_CD='A'";

if ($db->Sql($sqlStatement))
{
print "$sqlStatement\n";
print "SQL failed 3.\n";
print "*** Error: " .
Win32::ODBC::DumpError() .
"\n";
$db->Close();
print "SQL failed. \n";
exit;
}
else
{
$db->FetchRow();
%Data = $db->DataHash();
$catVal = "$Data{OBA_INT_VAL}";
print LOGFILE "ttt2 Parent: $sku_parent_id, cat: $catVal\n";
}
} #END getCategory

Re: Nested Select terminates primary select

"hightone" wrote in message
news:1124140335.184434.325220@g14g2000cwa.googlegroups.com.. .
>I am reading data from an Oracle table and updating an MS Access table
> using WIN32:ODBC. I have nested a select statement. The inner
> select seems to be prematurely terminateing my outside select
> statement. How can I write the nested select so that
> it does not affect the "while($db->FetchRow())" of the outer select
> statement?
>

Your problem is too vaguely worded. Please explain what you expected to
happen and what did happen, including the relevant error message(s)
returned.

The only recommendation I would make at this point is that you switch from
Win32::ODBC to the DBI interface and DBD-ODBC driver, for which there is
more active support.

Matt

Re: Nested Select terminates primary select

Thank you for the reply Matt, I will try to explain the problem more
explicitly.

** WHAT I EXPECT TO HAPPEN **
I do a select, and then I loop while rows still exist. In the loop is
another select with while rows still exist. When the second loop exits
(no more rows) I would like it to continue through the resultset in the
first loop.

** WHAT DID HAPPEN **
I do a select, and then I loop while rows still exist. In the loop is
another select with while rows still exist. When the second loop
exits, (ie while rows exist becomes no longer true) it terminates the
loop, but not only the inner loop, the outer loop is also terminated.

** RELEVANT ERROR MESSAGE(S) **
There were no error messages. The program terminates normally. This
is a logic problem.

Thanks Matt for the suggestion to try DBI. I will try it.

Re: Nested Select terminates primary select

hightone wrote:
> I am reading data from an Oracle table and updating an MS Access table
> using WIN32:ODBC. I have nested a select statement. The inner
> select seems to be prematurely terminateing my outside select
> statement. How can I write the nested select so that
> it does not affect the "while($db->FetchRow())" of the outer select
> statement?

Disclaimer: I have never used WIN32::ODBC. Everything I'm about to
say is based soley on my looking at the code below and a cursory
examination of the docs as found on CPAN.

> Here is the code:
>
> sub getStatus{
>
> $sqlStatement ="select sku_code, " .
> "SKU_PARENT_ID," .
> "SKU_STATUS_CD " .
> "from bluem_main.sku " .
> "where version = $version" .
> "order by sku_code";
>
> if ($db->Sql($sqlStatement))

Here you execute a statement on the connection $db.



> }
> else
> {
> while($db->FetchRow())

Here you begin fetching rows from the current "keyset" (which the docs
I looked at did not adaquately explain, IMHO).

> updateStatus();

Within the while loop, you have a function call that eventually takes
us to...

> sub getCategory{
> $sqlStatement = "SELECT s.sku_code,o.oba_int_val " .
> "FROM bluem_main.sku s, bluem_main.object_attribute o " .
> "WHERE o.oba_atr_id = $attIdCat " .
> "AND s.SKU_CODE = $skuCode " .
> "AND s.SKU_PARENT_ID= $sku_parent_id " .
> "AND o.oba_obj_id= $sku_parent_id " .
> "AND S.version = $version " .
> "AND S.SKU_STATUS_CD='A'";
>
> if ($db->Sql($sqlStatement))

.... this function. Here, you're executing a brand new statement on the
existing connection. It seems to me that this would over-ride the
previous statement you were in the middle of fetching. From the docs,
it does not look as though a connection can handle more than one
executing statement at once.

A couple suggestions you could try:

1) Create another connection to the DB for this nested SQL. I believe
elsewhere in the code, you have $db2. Try yet another...

2) Undo the nesting. Retrieve all the results from the first SQL
statement and store them in memory or in a file, the loop through the
stored results to process the 2nd SQL.

3) Switch to the DBI module. This API supports multiple statements on
a single connection, via statement handlers. You would create and
prepare two handlers, execute the first, begin fetching, and within
the loop, execute and fetch the second handler.

Hope this helps,
Paul Lalli

Re: Nested Select terminates primary select

Hi Paul,

Thank you for your investigation and suggestions.
I did try your first suggestion, I got a database error, because I
tried to open two connections to the same database.

I had initially written it like your second suggestion but it took 16
hours to run, so I was trying to avoid the file I/O.

I have yet to try DBI. That is something I will have to do.

Thanks,
Tony

Re: Nested Select terminates primary select

"hightone" wrote in message
news:1124208869.739385.251560@z14g2000cwz.googlegroups.com.. .
> Thank you for the reply Matt, I will try to explain the problem more
> explicitly.
>

> When the second loop
> exits, (ie while rows exist becomes no longer true) it terminates the
> loop, but not only the inner loop, the outer loop is also terminated.
>

I meant a more thorough explanation of this part. That it exits the loop is
not at issue, but why, and I can no more tell you that now with the
information you've provided than I could before.

Have you tried running the code commenting out the inner select? Have you
verified that there are more rows in the result set? (Not that you think
there should be more rows, but that there actually are.) How do you know
that there aren't any errors? (I don't see where you're checking in the
second statement.) Are you sure you're correctly escaping the values you're
using in your sql statement? (Another advantage to DBI being that it
supports a (not always perfect) quote function and placeholders.)

Matt