datatype from SQL

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

this seems simple, but i'm running into confusion: i have the following very simple SQL statement:

my $sql = "SELECT UID FROM news";
[download]

and then the following loop to match the requested UID with the results of the query, and identify the previous and next records:

while (my $newUID = $sth->fetchrow)
{
if ($in{'requestID'} == $newUID)
  {
    $currID = $newUID;
  }
else
  {
    $prevID = $newUID;
  }
... #there's a counter to generate one more loop after the currID is f
+ound, to capture "nextID"
$nextID = $newUID;
}
print p(["Previous = |$prevID|","Current = |$currID|","Next = |$nextID
+|"]);
[download]

so, if i submit the value 2 as 'requestID', the results for $prevID is correctly listed as 1, $currID is undefined, and $nextID gets assigned as 2. this is what it looks like:

0 #this is a counter value to escape the while loop after finding the 
+value of $nextID

Previous = |1|

Current = ||

Next = ||
1

Previous = |1|

Current = ||

Next = |2|
[download]

it seems to me that the condition ($in{'requestID'} == $newUID) is not getting evaluated correctly, and i'm wondering if one of the other value is not recognized as an integer? is that the problem? how do i fix it? the database file is MySQL, and the UID field is defined as an integer field.

TIA, janaki

Comment on datatype from SQL Select or Download Code

Replies are listed 'Best First'.
Re: datatype from SQL by VSarkiss (Monsignor) on Jun 23, 2006 at 17:23 UTC
The `fetch` method on a DBI statement handle object returns a reference to an array containing the value, not the value itself (it's an alias for `fetchrow_arrayref`). You need to do something like: `if ($in{'requestID'} == $newUID->[0])` [download] to get the value in the row. Update I just realized your code snippet says `fetchrow`, not `fetch`. To my knowledge, there's no `fetchrow` method in DBI. If you're not using DBI, ignore this note (what are you using?); if you are, what method are you actually calling? Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l] [select]
Re^2: datatype from SQL by jck (Scribe) on Jun 23, 2006 at 17:36 UTC
well, i tried your suggestion, changing $newUID to $newUID->[0], but it gave me the following error: `Can't use string ("1") as an ARRAY ref while "strict refs" in use at n +ewsedit.pl line 61.` [download] so i think that $newUID must be the scalar ref, not an array ref, right?	[reply] [d/l]
Re^3: datatype from SQL by VSarkiss (Monsignor) on Jun 23, 2006 at 17:42 UTC
Something doesn't add up. If you're calling `fetchrow_arrayref`, then your `print` example should show `ARRAY(0x123456)` or some such. That error message you're getting indicates the actual value (not a scalar ref or an array ref) is contained in `$newUID`, which is not consistent with the return value from that method. Stop, take a deep breath, make sure your code says what you think, and then go through brian's Guide to Solving Any Perl Problem. Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l] [select]
Re^3: datatype from SQL by derby (Abbot) on Jun 23, 2006 at 17:45 UTC
What version of DBI are you using and which DBD driver? You're use of $in{param} leads me to believe you're also using cgi-lib (instead of CGI) and may have a very old installation. -derby Update: And is this an XY Problem ... are you just trying to ~~get~~ create the next UID ... if so, most DB's have an auto-incrementing field feature.	[reply]
Re^2: datatype from SQL by jck (Scribe) on Jun 23, 2006 at 17:29 UTC
i thought that might be the problem, but then, why does `print $newUID` [download] give me the value "2"?? (or "1", or whatever)??	[reply] [d/l]
Re^2: datatype from SQL by jck (Scribe) on Jun 23, 2006 at 17:32 UTC
i think i may have had the whole fetchrow_arrayref, now it's fetch...i may have cut & pasted an intermediate incorrect version. and, yes, i am using DBI	[reply]
Re: datatype from SQL by jck (Scribe) on Jun 23, 2006 at 17:49 UTC
OK, well, it was really stupid!!! so sorry to take up anyone's time (and thank you VSarkiss for tryine!!)....it turned out that my loops were poorly constructed, and the condition that i thought was failing was never getting tested at all. it works fine now just had to work through it a little	[reply]