Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: Annoying SQL Statement!

Feb 11, 2003, 2:45 AM

Andy

Veteran / Moderator (18436 posts)

Feb 11, 2003, 2:45 AM

Post #1 of 19

Shortcut

Annoying SQL Statement!

Please...can anyone see why this won't work?

Code:
     my $sth      = $table->select( { SetID => \$ID, Password => "$Password" } );

For some reason, the query always comes out like;

SELECT * FROM links2CatAdverts WHERE SetID = 3 AND Password = magnum

Notice the lack of quotes around the Password = value part. This seems to be causing an error. See below;

GT::SQL::error = Failed to execute query: 'SELECT * FROM links2CatAdverts WHERE SetID = 3 AND Password = magnum ' Reason: Unknown column 'magnum' in 'where clause'

I've tried several versions of the code, such as;

Code:
     my $sth      = $table->select( { SetID => \$ID, Password => \"$Password" } ); 

     my $sth      = $table->select( { SetID => \$ID, Password => \$Password } ); 

     my $sth      = $table->select( { SetID => \$ID, Password => \'$Password' } );

...and thats about it Unsure

Anyone got any ideas? This one really has me stumped.

Cheers

Andy (mod)
andy@ultranerds.co.uk

Feb 11, 2003, 2:53 AM

Paul

Veteran (19537 posts)

Feb 11, 2003, 2:53 AM

Post #2 of 19

Shortcut

Re: [Andy] Annoying SQL Statement! In reply to

Why reference the id?...the following is fine:

Code:
my $sth = $table->select( { SetID => $ID, Password => $Password } );

Feb 11, 2003, 2:54 AM

yogi

Veteran / Moderator (2199 posts)

Feb 11, 2003, 2:54 AM

Post #3 of 19

Shortcut

Re: [Andy] Annoying SQL Statement! In reply to

Andy,

Code:
my $sth = $table->select( { SetID => $ID, Password => $Password } );

This is a case of RTFM, actually. GT::SQL::Table, select.

Ivan
-----
Iyengar Yoga Resources / GT Plugins

Feb 11, 2003, 3:01 AM

Andy

Veteran / Moderator (18436 posts)

Feb 11, 2003, 3:01 AM

Post #4 of 19

Shortcut

Re: [yogi] Annoying SQL Statement! In reply to

Thanks Paul and yogi for the replies. Yogi, that sarcasm wasn't really needed, as I did read the manual..thats why I'm so confused it wont work! I did exactly the same as you just said, but that still gave the following error;

GT::SQL::error = Failed to execute query: 'SELECT * FROM links2CatAdverts WHERE SetID = 3 AND Password = magnum ' Reason: Unknown column 'magnum' in 'where clause'

As you can see, still no joy! Frown

Andy (mod)
andy@ultranerds.co.uk

Feb 11, 2003, 3:03 AM

yogi

Veteran / Moderator (2199 posts)

Feb 11, 2003, 3:03 AM

Post #5 of 19

Shortcut

Re: [Andy] Annoying SQL Statement! In reply to

What does

print Dumper($Password)

say?

Ivan
-----
Iyengar Yoga Resources / GT Plugins

Feb 11, 2003, 3:19 AM

Andy

Veteran / Moderator (18436 posts)

Feb 11, 2003, 3:19 AM

Post #6 of 19

Shortcut

Re: [yogi] Annoying SQL Statement! In reply to

print $Password;

prints out;

magnum

...which is the correct value for it when it goes in (I'm sending '3' as the ID, and 'magnum' as the password).

I'm just stumped as to why it won't work. Numeric passwords work fine, but just not alpha ones Unimpressed

Cheers

Andy (mod)
andy@ultranerds.co.uk

Feb 11, 2003, 3:36 AM

Paul

Veteran (19537 posts)

Feb 11, 2003, 3:36 AM

Post #7 of 19

Shortcut

Re: [Andy] Annoying SQL Statement! In reply to

I *think* I know why this seems to happening although not 100%.

The fact that Dumper($Password) prints out magnum tells me $Password is already a scalar reference as I don't think Dumper() will print out anything other than references.

Try using:

Password => $$Password

Feb 11, 2003, 4:47 AM

Andy

Veteran / Moderator (18436 posts)

Feb 11, 2003, 4:47 AM

Post #8 of 19

Shortcut

Re: [Paul] Annoying SQL Statement! In reply to

Dumper($Password) doesn't print out "magnum"...I was using;

print $Dumper (don't see why Dumper() needed to be used...as its only a variable being passed over via POST).

I'll give your idea a go...but otherwise I'm still stuck Frown

Cheers

Andy (mod)
andy@ultranerds.co.uk

Feb 11, 2003, 5:50 AM

Paul

Veteran (19537 posts)

Feb 11, 2003, 5:50 AM

Post #9 of 19

Shortcut

Re: [Andy] Annoying SQL Statement! In reply to

Well if thats the case then my code won't work. However yogi's/my first code examples should.

Feb 11, 2003, 6:45 AM

Andy

Veteran / Moderator (18436 posts)

Feb 11, 2003, 6:45 AM

Post #10 of 19

Shortcut

Re: [Paul] Annoying SQL Statement! In reply to

Man...still no job :( It just doesn't like putting the quotes into the query (around the passwords value)...WHY OH WHY! Pirate

Andy (mod)
andy@ultranerds.co.uk

Feb 11, 2003, 7:11 AM

Andy

Veteran / Moderator (18436 posts)

Feb 11, 2003, 7:11 AM

Post #11 of 19

Shortcut

Re: [Paul] Annoying SQL Statement! In reply to

Finally I got around it (last but not least).

Code:
     my $table    = $DB->table('CatAdverts');  
     my $sth      = $table->select( {SetID => \$ID } ); 
     my $got; 

    # set $got to true if we managed to find an entry matcinhg their details... 
     while (my $hit = $sth->fetchrow_hashref) { 
        if ($Password eq $hit->{Password}) { 
           $got = 1; 
        }
     }

Adding/editing the parts in red seemed to have sorted it now. I'm still lost as to why the code above didn't work Unsure

Andy (mod)
andy@ultranerds.co.uk

Feb 11, 2003, 7:24 AM

Paul

Veteran (19537 posts)

Feb 11, 2003, 7:24 AM

Post #12 of 19

Shortcut

Re: [Andy] Annoying SQL Statement! In reply to

I still don't get why you are using \$ID instead of $ID

Feb 11, 2003, 7:28 AM

Andy

Veteran / Moderator (18436 posts)

Feb 11, 2003, 7:28 AM

Post #13 of 19

Shortcut

Re: [Paul] Annoying SQL Statement! In reply to

Using $ID on its own (without the \) just printed this into the query;

SetID =

...i.e. no value was given. Not sure why...but it did it Tongue

Cheers

Andy (mod)
andy@ultranerds.co.uk

Feb 11, 2003, 7:41 AM

Paul

Veteran (19537 posts)

Feb 11, 2003, 7:41 AM

Post #14 of 19

Shortcut

Re: [Andy] Annoying SQL Statement! In reply to

Sounds like your copy is a bit messed up. All these weirdnesses shouldn't happen.

Feb 11, 2003, 7:47 AM

yogi

Veteran / Moderator (2199 posts)

Feb 11, 2003, 7:47 AM

Post #15 of 19

Shortcut

Re: [Andy] Annoying SQL Statement! In reply to

How are you setting the value of $ID and $Password?

Try this:

Code:
if (ref $Password) { 
    print '$Password is a ' . ref $Password . ' reference. How come?'; 
} 
else { 
    print '$Password is a normal scalar.'; 
}

Ivan
-----
Iyengar Yoga Resources / GT Plugins

Feb 11, 2003, 7:49 AM

Andy

Veteran / Moderator (18436 posts)

Feb 11, 2003, 7:49 AM

Post #16 of 19

Shortcut

Re: [yogi] Annoying SQL Statement! In reply to

That code prints out;

Quote:

magnum is a normal scalar.