Posted in ColdFusion | Posted on 06-21-2006 | 4,001 views
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:
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:
2<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:
2<cfset values = arrayToList(myQuery[col])>
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>.
Then you can pass it as is to cfqueryparam, and use the list=true attribute.
I use this all the time for IN clauses.
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).
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.
Thanks for the advice.
I will look into it, definately.
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
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.
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.
[Add Comment] [Subscribe to Comments]