Re^6: in, ands, ors in query (qq)

Sorry, it's me again. I hope I'm not bugging you with the questions or get the idea I don't want to figure this out on my own but I am really stuck on this one.

I took your suggestion and printed the statement and recieved


C:\Documents and Settings\Administrator\Desktop\scripts>perl form.pl
SELECT engine, lable1, lable2, lable3, lable4, settings1, settings2, s
+ettings3, settings4 FROM special_fields WHERE engine IN (".join(",",m
+ap(->quote (), )).") AND (settings1="public" OR settings2="public" OR
+ settings3="public")
C:\Documents and Settings\Administrator\Desktop\scripts>
[download]

I've reread it quite a few times now and I don't see anything misplaced or missing.

Of course that's assuming you meant for me to do

print qq(SELECT engine, lable4, settings1, settings2 FROM special_fiel
+ds WHERE engine IN (".join(",",map($dbh->quote($_), @choices)).") AND
+ 
(settings1="public" OR settings2="public" OR settings3="public"));
# removed some of the columns just to clean it up a bit
[download]

I carefully counted the open and close parens around the JOIN and all of them were there, including the last paren after public").

I also went ahead and tried again after creating the array and something looks weird about it printing out every element at once, I sort of expected it to only print one.


C:\Documents and Settings\Administrator\Desktop\scripts>perl form.pl
SELECT engine, lable1,settings3, settings4 FROM special_fields WHERE e
+ngine IN (".join(",",map(->quote
(), red blue orange green)).") AND
(settings1="public" OR settings2="public" OR settings3="public" )
C:\Documents and Settings\Administrator\Desktop\scripts>
[download]

Comment on Re^6: in, ands, ors in query (qq) Select or Download Code

Replies are listed 'Best First'.
Re^7: in, ands, ors in query (qq) by tye (Sage) on Dec 13, 2006 at 04:57 UTC
What you printed is what the DB is seeing. The DB doesn't know Perl so this query will return records where special_fields is either the string `".join("` or is the string `",map(->quote(), red blue orange green))."`. You aren't getting any records returned because you don't have any records with either of those two strings in that column (not surprisingly). You need to have that Perl code executed, so you have to take that code outside of the quotes, that is, outside of the `qq( ... )`. The quote (") in this code: `engine IN (".join(` [download] you appear to think is ending the quoted string such that Perl executes the concatenation operator (.) and calls the join function. But, as I said way up there, a quote (") doesn't end a quoted string that was started with `qq(`. I'm going to stop right there because I think you can figure out the rest. You appear to just have had a hard time seeing this. No, I'll give you one more hint. You might want to change from `qq( ... )` to something like `qq{ ... }`, `qq< ... >`, or `qq[ ... ]`, because ending a `qq( ... )` string having unmatched parens (which is what the first part of your statement needs to be) is a bit tricky. Though you might want to experiment with the trickier syntax after you get it working just for your own enlightenment. Good luck. - tye	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^7: in, ands, ors in query (qq)
by tye (Sage) on Dec 13, 2006 at 04:57 UTC

What you printed is what the DB is seeing. The DB doesn't know Perl so this query will return records where special_fields is either the string ".join(" or is the string ",map(->quote(), red blue orange green)).". You aren't getting any records returned because you don't have any records with either of those two strings in that column (not surprisingly).

You need to have that Perl code executed, so you have to take that code outside of the quotes, that is, outside of the qq( ... ). The quote (") in this code:

engine IN (".join(
[download]

you appear to think is ending the quoted string such that Perl executes the concatenation operator (.) and calls the join function. But, as I said way up there, a quote (") doesn't end a quoted string that was started with qq(.

I'm going to stop right there because I think you can figure out the rest. You appear to just have had a hard time seeing this.

No, I'll give you one more hint. You might want to change from qq( ... ) to something like qq{ ... }, qq< ... >, or qq[ ... ], because ending a qq( ... ) string having unmatched parens (which is what the first part of your statement needs to be) is a bit tricky. Though you might want to experiment with the trickier syntax after you get it working just for your own enlightenment.

Good luck.

- tye

[reply]
[d/l]
[select]