I'm about to pull my hair out

[fergmj]

ok. I am a decent perl programmer (I think) and I have finished my application and everything works except for ONE issue and I am about to rip my hair out over it so I am asking for some help. I've been staring at this for a long time so maybe I'm just too close to the problem and I can't see it.

So I have a block of code and it executes as it should

elsif ($beginning eq "edit")
    {
    my $edititem = $q->param("id");
    if (my $row = select_item ($dbh, $edititem))
        {
        display_edit_form ($row);
        }
    else
        {
        print "<FONT FACE='Arial' SIZE=2>No record with that ID was found";
        display_current_items_print ($dbh);
        }
    }

I get to the select_item subroutine just fine but in this subroutine, it is getting locked up on the $sth->execute($id). In fact if I just hard code in an id and make

my $sql = qq{ SELECT * FROM export WHERE id = 36};

it still doesn't work
it just gets stuck and never does anything

any ideas -- I am open to any comments -- THANK YOU in advance!

sub select_item
	{
        my ($dbh, $id) = @_;
        my ($sth, $row);
        my $sql = qq{ SELECT * FROM export WHERE id = ?};
        $sth = $dbh->prepare ( $sql ) or die $dbh->errstr;
        $sth->execute($id) or die $dbh->errstr;
	$row = $sth->fetchrow_hashref();
	$sth->finish();
        return ($row);
	}

 

[fergmj]

As a follow-up note:

This is a very small table I am pulling data from.
MySQL is the database I am using.

only 40 rows in the table and hardly any data in my test database

Again, thank you in advance -- I'd love to be able to sleep when this is resolved

 

[mikrom]

Try other way

@row = $sth->fetchrow_array;
print "$row[0]\n";
...

 

[PaulTEG]

you're returning a hashref, can you dump it to see if there's any details.

In the display edit, the hashref, could that be referred to as a scalar instead of the hashref.

Joining the array as advised by mikrom with perhaps a '%' character might allow you to pass the scalar and then split inside your display_edit_form stub

Just a thought

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[fergmj]

the execute line is where it fails

$sth->execute();

The query returns in mysql in sub second but I get nothing when excited via code. It just hangs and so I don't get a fetchrow_array

 

[fergmj]

The execute works if the where clause isn't in the command.

So select * from export will return results but of course the first id not the one I actually want so I use where id=? And pass the $id in the execute

Even if I hardcode it to be where id=36 it always fails

If I remove the where clause it works. But I need the where clause

In mysql the query returns sub-second from the command line

Thanks again

 

[PaulTEG]

try

$sth->execute() or die $dbh->errstr."\n$!\n";

and see if that sheds any light on it, are there other machines you can test this on?

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[fergmj]

Hey Paul,

So I tried adding your extras to my execute line and that still has my execute just locking up and doing nothing.

I also tried from a different machine and that didn't work either

Same bad result from both machines

 

[PaulTEG]

write your sql string to a file and look for extra characters, newlines and carriage returns for example, chomp may be all that's required

Write a simple test script to query the DB and let us know how that goes


Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[fergmj]

Well this isn't the correct way to do this but I must say it works -- kind of

sub select_item
	{
        my ($dbh, $id) = @_;
        my ($sth, @row, $row, $count);
        my $sql = qq{ SELECT * FROM export};
        $sth = $dbh->prepare ( $sql ) or die $dbh->errstr;
        $sth->execute($id) or die $dbh->errstr."\n$!\n";
        while (my $row = $sth->fetchrow_hashref ())
            {
            if ($row->{id} = $id)
                {
                print "$row";
                return ($row);
                }
             ++$count;
            }
        $sth->finish();
        }

Now the problem seems to be that I am pulling the correct $id from the table but all of the other columns of the row are returning as the first row in the table....

Hmmmmm. any others ideas Paul? Your ideas got me thinking and at least i am on the way to a possible solution.

Thanks again

 

[fergmj]

So it seems to be a problem with how I created the mysql table so I'll take this to a different forum unless someone has any experience on why this wouldn't work.

I am doing my select with a where clause on the id column

i built my table with such that the id column is a primary key, int, unsigned, not null, auto-increment

however if I change the where clause (just to see if this works), then the query itself returns quickly and perfectly.

It seems to be a problem with the id column itself.

 

[PaulTEG]

are you sure you're passing an int and not a string [1\n]

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[rvBasic]

if the column is auto-incremented, then how do you know for sure that the number exists? are you sure that you did not delete the record previuously and then re-added it to the table?

searching on an auto-incremented id is generally a bad idea.

_________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]

 

[WinblowsME]

Try removing the bind variable.

sub select_item
    {
        my ($dbh, $id) = @_;
        my ($sth, @row, $row, $count);
        my $sql = qq{ SELECT * FROM export};
        $sth = $dbh->prepare ( $sql ) or die $dbh->errstr;
        $sth->execute([COLOR=red]$id[/color]) or die $dbh->errstr."\n$!\n";
        while (my $row = $sth->fetchrow_hashref ())
            {
            if ($row->{id} = $id)
                {
                print "$row";
                return ($row);
                }
             ++$count;
            }
        $sth->finish();
        }

 

[ChrisHunt]

searching on an auto-incremented id is generally a bad idea

Not much point having a key if you can't search by it. Presumably, elsewhere in his system, fergmj is offering users a list of items to choose from, and capturing the id they choose.

Dunno what's going wrong though. Bet it's something really stupid - it always is when you've been looking at something for days!

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[fergmj]

so there is an error in my posting and WinblowsME caught it. It was only an error in pasting it into this forum because I was originally passing a placeholder in the SELECT statement and needed the $id in the execute but when I was doing testing, I failed to remove the $id from the execute call when posting the code to this forum.

I totally agree with ChrisHunt, there is no point in having a key if you can't search by it. I'm not sure why rvBasic feels that auto-incrementing is a bad idea. I disagree.

In any case, I solved the problem. Unfortunately I'm not sure how. I still feel it was a mysql issue with the id column on the table because once I dropped the entire table and re-created it, all fo the perl code worked fine and as expected.

 

[PinkeyNBrain]

I don't think I completely appreciate everything that $sth->execute($id) is doing, however I'll toss in a thought:

One of the reoccurring issues I've had with various database mediums is that even though everything I can cross reference says that a given field is of type X (ie: 'id' is an integer so id = 36 should work), somewhere between the source data and when it reaches me, it's changed. While I know that perl is graceful enough to make type changes for you when it can, if your 'id' is stored as a string, then 'WHERE id = 36' wont fail as much as it will just return zero lines because the table is looking at it as '36 ' or ' 36' or who knows what. Could you perhaps try WHERE id like '*36*' (or whatever wildcard is applicable here)

MSAccess has been a constant reminder to me of what happens when a program tries to help me out and I don't want their help. I'll define a column as text, but if the first value I happen to import accidentally looks like a number or a date, access will change the column type and then refuse to import anything that doesn't fit the type it just changed it to.

 

[fergmj]

Interesting. Yes I'll give it a try and report back

 

[mikrom]

fergmj,
Last week I had a problem with a DB2-table with an auto-increment key-field too. I tried to write into this table and I got everytime duplicate key issue.
The problem was, that the table was copied from our production environment into the development environment some days ago by our admin.
I got an advice, that when you copy such table the auto-incrementation goes mad (because the autoincrementation counter tries to count again from the beginning). So we saved the data from the table, re-created the table and now all works fine.
So I want to say, that it was probably not a MySQL issue, but an general issue with auto-increment table.
Did you or your admin simply copied the table from one schema into another before you started to work on your perl script?

 

[PaulTEG]

you can specify the auto increment start value when restoring a table in MySQL ...

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)