This is a short and simple questions I thought may be interesting to beginners:
I have a query "Select * From PhotoTable, Category Where PhotoTable.FileName IN ('#KeywordSearchResults#') AND CategoryID = Category.ID Order by CategoryPrefix, Filename ASC" I am using a ValueList that is dynamically generated. I need to know how I can define a custom delimiter in valuelist so that the values appear as 'a',b','c'. Right now whatever I do either i get single quote or comma. Please Help.
First off - you know the select * is bad, right? Never, ever, use select *. Always list out the columns. Also, you want to replace the simple #KeywordSearchResults# variable with a cfqueryparam tag. But that's not why you are here.
In case folks don't know, the valueList function gets you all the values from one column of a query. It's a handy little function. To use a custom delimiter, just supply a second argument to the function. So for example, this would use the @ sign:
<cfset values = valueList(myQuery, "@")>
However, the writer wants to wrap each list item with single quotes. You can't do this with valueList. Luckily ColdFusion provides us with listQualify. This function will wrap each list item with a character. So to add single quotes to the code above, you would do this:
<cfset values = valueList(myQuery, "@")>
<cfset niceList = listQualify(values, "'")>
One little thing about valueList. It doesn't allow for dynamic column names. If you want to dynamically get one column, you can treat the column as an array:
<cfset col = "id">
<cfset values = arrayToList(myQuery[col])>
Archived Comments
Or you could just use QuotedValueList()
Duh. Can I blame the coffee for missing that? I like to tell people to read the function/tag list every now and then just to refresh your memory. I need to heed my own advice.
Although, to be fair, that function forces you to use single quotes. If you wanted control over the "wrapper", you would need to use listQualify.
Wow Ray! This entry has unbelievable timing!
Just yesterday I wrote a function that required a string argument for the IN clause.
Now instead of passing "'A','B','C'", I can pass "A,B,C".
QuotedValueList expects a query column, whereas ListQualify can work with a string.
One other thing to keep in mind is it's sometimes necessary to use #preserveSingleQuotes(SQLstr)# instead of #SQLstr# inside a <cfquery>.
Phil, if you use cfqueryparam, you don't need to worry. If X is a list of items like so: a,b,c
Then you can pass it as is to cfqueryparam, and use the list=true attribute.
I use this all the time for IN clauses.
Well............
I rarely use cfquery anymore.
I pass parameters to a stored procedure that
A. Logs the SQL stmt to be run (along with who's attempting to run it).
B. Checks to see if that person has rights for what they're trying to do.
C. If so, it runs the SQL stmt and
D. Logs that the SQL stmt ran successfully (There was no @@Error condition)
E. If it's an INSERT, the stored procedure then does a SELECT on SCOPE_IDENTITY so that the defaults are returned.
I should blog about all this one day (after cfUnited).
@Phillip
Please don't think that I'm being a wisea**, but why are you doing this inside a stored procedure? You are creating additional work for yourself and putting additional load on your server that can be totally avoided by using SQL Profiler. You can filter the data you want to capture using the events and filters tab. Additional you can have the output saved into a table on a seperate SQL server so you can perform queries against it.
Also you can grab the SCOPE_IDENTITY from within cfquery by just putting in on additional line in your statement:
<cfquery name="q" datasource="#dsn#">
INSERT INTO TABLE
(col1)
VALUES('something')
SELECT SCOPE_IDENTITY() AS id
</cfquery>
<cfoutput>#q.id#</cfoutput>
I'm not saying what you're doing is wrong, I'm just saying that if you use the tools that are already avaiable to you, you can avoid a tremendous amount of work and capture more percise information. Readup and play with SQL Profiler, it's an invaluable tool.
BTW, I should also mention that by using SQL Profile it doesn't add any load to the server that you are profiling. It works just like a packet sniffer for SQL Server.
Tony,
Thanks for the advice.
I will look into it, definately.
Tony,
Using "select scope_identity() as" or "SELECT @@Identity AS" can crash if you don't turn no count on. like so:
SET NOCOUNT ON
INSERT INTO...
VALUES (...)
SELECT @@Identity AS newId
SET NOCOUNT OFF
But i'm with you I use it all the time. It's much better than setting a transaction and getting the max(id), or worse having an extra unique ID in your table that you generate before the insert, and re-use to get the new ID.
Tof
i was always taught that if the select list comprises a large amount of the columns, that * would be best to use. if you are selecting 5 out of 30 columns, yes, then naming is best. was i taught wrong?
Yes. I'm no DBA, but I've been told that select * is never a good idea. Now - I wouldn't be too concerned about it. I doubt the performance impact will matter for 99.99% of us, but still.
@tony
It doesn't matter if you use SELECT * or name every column when you are selecting all the columns in a table, they yield the EXACT same execution plan. (if you don't believe me, test it out for yourself.) Thus there is NO PERFORMACE DIFFERENCE between the two. Again, this is ONLY if you are selecting every column in the database.
The bottom line is that SELECT * is a matter of perference. Some like to use it and some don't, I don't. So whoever tells you that using it to select all the columns in a table is a bad idea and causes performance hits, is lying through their teeth to push their opinion.
Tony, interesting. I'd probably still recommend against it for a readability perspective.
i use the list of fields, unless its like 3 columns, and they are the only three, and i need all three.
having them listed out makes it easier to reference them in code if you can see them in the same screen as the screen you are on. i believe ya a 100% that it doesnt matter, and im sure the person was being honest and true, just his opinion.
oddly enough it was a Microsh*t DBA person teaching a class I was in learning SQL and SQL Admin like 4 years ago.
"Phil, if you use cfqueryparam, you don't need to worry. If X is a list of items like so: a,b,c..."
Bingo!
Raymond, just wanted to say thanks. I often get awesome info off your blog.
I wasn't even aware this function existed, and it worked like a charm. It's a very elegant solution.
Wow, this was an old blog entry. :) Glad it helped you, Shannon!