Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Format for GT::SQL::Condition

Quote Reply
Format for GT::SQL::Condition
Man, this has had me pissed off for a while now. I really wanna know how to use this function, as it seems to have a lot of power in it! The problem I am having, is that it won't work for me! The documentation doesn't seem that clear (at least not to me), as to how one would go about using it in a $table->select_options call. I tried;

$table->select_options ('ORDER BY ID DESC', GT::SQL::Condition->new ( 'ID', '>=', $start_from));

But, as I'm sure you could imagine, that just gives an error Tongue

Any help/suggestions you can provide would by much appreciated, and save me the last few hairs I have on my head :)

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Format for GT::SQL::Condition In reply to
You are trying to use a condition in select_options which is wrong - the docs explain where to put it. It forms a where clause ie..

$table->select( GT::SQL::Condition->new( 'ID', '>=', $start_from ) );

Which translates to:

SELECT * FROM Table WHERE ID >= some_number
Quote Reply
Re: [Paul] Format for GT::SQL::Condition In reply to
Mmmm...for some reason the ID >= $last_id to work correctly...

Code:
print $last_id;

my $table = $DB->table('Links');
$table->select_options ('ORDER BY ID ASC', "LIMIT $run_at_time");
my $sth = $table->select( GT::SQL::Condition->new( 'ID2', '>=', $last_id ) );

while (my $hit = $sth->fetchrow_hashref) {

...

}

That prints out the right value for $last_id, but for some reason, the SQL query only comes out as;

'SELECT * FROM lsql_Links WHERE ( ID2 >= ? ) ORDER BY ID ASC LIMIT 6'

Note how the part in bold has a ? there, which should be filled with a number Crazy

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Format for GT::SQL::Condition In reply to
Read read read

http://www.perl.com/pub/a/1999/10/DBI.html
Quote Reply
Re: [Paul] Format for GT::SQL::Condition In reply to
Does that really have anything to do with the Links SQL formatting? I know what the ? means, but I can't understand why the number is being left out!

Crazy

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Format for GT::SQL::Condition In reply to
>>
Does that really have anything to do with the Links SQL formatting?
<<

Yes, although it isn't "Links SQL Formatting"

>>
I know what the ? means, but I can't understand why the number is being left out!
<<

That makes no sense, if you knew what the "?" meant then you'd know why the number is left out.

Like I suggested, read the article and you'll understand.
Quote Reply
Re: [Andy] Format for GT::SQL::Condition In reply to
I do not really understand why it substitues with '?'. It should be substitute with the variable value.
Before Paul yields me again as usually, let me express, that I know what the '?' means, and how is used in DBI.

I also used the the select, also the select_options, and had no problems, when I used the correct GT::SQL syntax.
I was not require to use '>=' operator in select_options, but should work correctly.

Some info from the code itself (it at least explains me better usage of select, than the docs):
Quote:
# $obj->select ($condition, \@select_returns);
# --------------------------------------------
# $condition is a Condition or a hash reference.
#
# $obj->select (\%columns, \@select_returns);
# -------------------------------------------
# $col1 = $val1, $col2 = $val2
#
# @select_returns is a list of the fields that you wish returned. If none are
# specified all fields will be returned.

EDIT: you may want to try to print out the condition after WHERE with that code:
print $cond->sql;

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

Last edited by:

webmaster33: Sep 10, 2002, 4:49 AM
Quote Reply
Re: [webmaster33] Format for GT::SQL::Condition In reply to
Doh..worked it out now. I needed to escape the variable, i.e.

my $sth = $table->select( GT::SQL::Condition->new( 'ID', '>=', \$last_id ) );

Cheers for the help guys Smile

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Format for GT::SQL::Condition In reply to
Yep, I remember. You need to use quotes.

"" or ''
If you pass it as reference using \'string', then it will be not quoted in the final statement. Useful for e.g. "field", "IS", \"NULL" type conditions.

Please use quote instead of passing ref.

EDIT: Try out this:
Code:
print $last_id;

my $table = $DB->table('Links');
$table->select_options ('ORDER BY ID ASC', "LIMIT $run_at_time");
my $sth = $table->select( GT::SQL::Condition->new( 'ID2', '>=', "$last_id" ) );

while (my $hit = $sth->fetchrow_hashref) {

...
}
I hope that works.
If not, then your solution to pass as reference is the good solution.

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

Last edited by:

webmaster33: Sep 10, 2002, 7:09 AM
Quote Reply
Re: [webmaster33] Format for GT::SQL::Condition In reply to
...and of course numbers don't need quoting.

>>
"" or ''
<<

"$number" is the same as simply $number

'$number' will _not_ work. It will not be interpolated.

Last edited by:

Paul: Sep 10, 2002, 7:07 AM
Quote Reply
Re: [Paul] Format for GT::SQL::Condition In reply to
I know "" is interpolated and '' is not.

Docs says for select:
Quote:
All arguments in the where clause are automatically quoted. If you don't want quotes, you should pass in a scalar reference as in:


my $sth = $obj->select ( { Col => \"NOW()" } );

which turns into ``SELECT * FROM Table WHERE Col = NOW()''.
So I think my solution should work, as posted (although I did not try it out)

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...

Last edited by:

webmaster33: Sep 10, 2002, 7:19 AM
Quote Reply
Re: [webmaster33] Format for GT::SQL::Condition In reply to
I don't really understand what you are saying. "Quoting" is referring to DBI quoting

Adding " " is exactly the same as Andy's original code.
Quote Reply
Re: [Paul] Format for GT::SQL::Condition In reply to
Paul, yes, it is possible that you are right.
Should be tried out. I do not have time to try out that.

Andy could you try out my suggested solution?

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [Andy] Format for GT::SQL::Condition In reply to
Hi,

$table->select( GT::SQL::Condition->new('ID', '>=', $start_time );

should work fine. Where are you seeing the ? in. Is this in the debug logs? GT::SQL should replace the question mark with the actual value used. The question mark is a DBI placeholder, and is something internal to DBI. However, in any debug output, GT::SQL is meant to replace the question mark with the value used.

You should not need to pass a reference as this could lead to security problems if the value of $start_time is derived from user input.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Format for GT::SQL::Condition In reply to
Hi Alex...using the code you posted in your post is what was giving me the ?

The way I got the error, was to change one of the valid SQL fields (i.e. ID, to ID2"), which then gave an error. That also printed out the MySQL query, which worked great :) Simply placing a \ in front of the $ fixed the problem.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!