Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: Format for GT::SQL::Condition

Veteran / Moderator (17366 posts)

Sep 9, 2002, 6:13 AM

Post #1 of 15

Shortcut

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

IMPORTANT: I've now moved to ultranerds.co.uk, and the .com will no longer work!
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package (plugins total "value" $3,325 & rising, for just $350)| GLinks ULTRA Package PRO (plugins total "value" $5,625 & rising, for just $500)
Support Forum | Links SQL Plugins | DMOZ Dumps | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Compare our different Plugin packages *new* Free CSS Templates

Sep 9, 2002, 6:57 AM

Veteran (19537 posts)

Sep 9, 2002, 6:57 AM

Post #2 of 15

Shortcut

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

Sep 9, 2002, 8:46 AM

Veteran / Moderator (17366 posts)

Sep 9, 2002, 8:46 AM

Post #3 of 15

Shortcut

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

Sep 9, 2002, 8:57 AM

Veteran (19537 posts)

Sep 9, 2002, 8:57 AM

Post #4 of 15

Shortcut

Re: [Andy] Format for GT::SQL::Condition In reply to

Read read read

http://www.perl.com/pub/a/1999/10/DBI.html

Sep 9, 2002, 9:16 AM

Veteran / Moderator (17366 posts)

Sep 9, 2002, 9:16 AM

Post #5 of 15

Shortcut

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

Sep 9, 2002, 9:31 AM

Veteran (19537 posts)

Sep 9, 2002, 9:31 AM

Post #6 of 15

Shortcut

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.

Sep 10, 2002, 4:37 AM

Veteran (2312 posts)

Sep 10, 2002, 4:37 AM

Post #7 of 15

Shortcut

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

Sep 10, 2002, 6:55 AM

Veteran / Moderator (17366 posts)

Sep 10, 2002, 6:55 AM

Post #8 of 15

Shortcut

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

Sep 10, 2002, 7:05 AM

Veteran (2312 posts)

Sep 10, 2002, 7:05 AM

Post #9 of 15

Shortcut

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

Last edited by:

webmaster33: Sep 10, 2002, 7:09 AM

Sep 10, 2002, 7:06 AM

Veteran (19537 posts)

Sep 10, 2002, 7:06 AM

Post #10 of 15

Shortcut

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

Sep 10, 2002, 7:16 AM

Veteran (2312 posts)

Sep 10, 2002, 7:16 AM

Post #11 of 15

Shortcut

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

Last edited by:

webmaster33: Sep 10, 2002, 7:19 AM

Sep 10, 2002, 7:18 AM

Veteran (19537 posts)

Sep 10, 2002, 7:18 AM

Post #12 of 15

Shortcut

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.

Sep 10, 2002, 7:22 AM

Veteran (2312 posts)

Sep 10, 2002, 7:22 AM

Post #13 of 15

Shortcut

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

Sep 10, 2002, 4:25 PM

Alex

Administrator (9387 posts)

Sep 10, 2002, 4:25 PM

Post #14 of 15

Shortcut

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.

Sep 10, 2002, 11:40 PM