Re: cfinsert/cfupdate
> Ok, so I found out I WAS correct. Either that or yet another article I > found on prepared statements is wrong. Here's a quote from the article: > "The PreparedStatement object contains not just an SQL statement, but an SQL > statement that has been precompiled. This means that when the > PreparedStatement is executed, the DBMS can just run the > PreparedStatementSQL statement without having to compile it first." > > So according to this, the SQL statement IS precompiled, as I had asserted. > Here's the article: > http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html I hate to disagree, but it's not "precompiled" in the traditional sense of the word. When you compile a program, the compiler converts it from the code you wrote to a different form (binary, bytecode) that can be directly executed by the computer or a VM like the JVM or Flash Player. This isn't what happens with SQL. "Compiled" in this case means that the database server has created a corresponding execution plan. The database server can then store and reuse this execution plan if it receives the same SQL statement later. The execution plan is not a direct conversion from the original SQL, either - it will depend on the underlying structure of the database (indexes, etc). So, you could take the same SQL statement and get different execution plans. The execution plan itself is the "program" that is run. But there is no actual compilation of the SQL statement, and the client will continue to send "raw" SQL statements to the database server. And, by default, this is the behavior you see whether you use prepared statements or not. If you send this query to the database twice: SELECT * FROM mytable WHERE myfield = 1 AND myotherfield = 1 the second execution will reuse the execution plan created by the first execution. Of course, if you were to then execute this query: SELECT * FROM mytable WHERE myfield = 2 AND myotherfield = 1 the execution plan from the first query would not be reused. A prepared statement changes this behavior by letting you specify placeholders within your query. In a prepared statement, you'll have two sections - the query and the values to plug into the placeholders. So, the database server would receive this: SELECT * FROM mytable WHERE myfield = ? AND myotherfield = 1 followed by the value to plug into "?". On the first execution, it would build an execution plan for the query above, without plugging in the value. On subsequent executions, it would reuse that plan. Usually, the plan will be optimal for all values that could be placed in the query, and that's where we see performance gains. If the plan isn't optimal for all values, though, you may see decreased performance: if the suboptimal execution plan takes longer to execute than the optimal execution plan would take to create and execute. If "myotherfield" could change, you'd want to put a placeholder there too. If you don't, the database server would need to create a new execution plan for this query: SELECT * FROM mytable WHERE myfield = ? AND myotherfield = 2 But if the value of "myotherfield" never changes from 1, you don't have to store that value in a placeholder, and won't see any performance benefit if you do store it in a placeholder. Now, within the database world, you will see the use of the words "compile", "recompile", etc - but again, they don't mean what they do in other environments. For example, in MS SQL Server, you can use "WITH RECOMPILE" when building a query or stored procedure, and that tells the database server not to reuse any existing execution plan. Fortunately, you can easily see all this at work. I tend to work with MS SQL Server more than other platforms, so that's the one I'm most familiar with, but it provides all sorts of tools that let you see the underlying things going on. For example, SQL Profiler lets you see the raw queries being sent from the client. SHOWPLAN lets you view the execution plan from Query Analyzer (or whatever they call that in 2005+). And having taken the MSDBA exams, all this stuff is covered in detail there. But Oracle has similar (and probably better) tools, as Oracle guys have shown me more than once. I agree that this is a subtle difference, and not that important. But what is important is the set of conclusions that you're drawing from this concept of "compilation" - those conclusions aren't correct. In summary: - all queries, whether "prepared" or not, are "compiled" (they have a corresponding execution plan) - you don't have to have placeholders for all values in a statement to make it a prepared statement, although to get the maximum reuse from a prepared statement, you should have placeholders for all values that might change. Those two assertions address the points you raised in your original response, and the whole "compile" debate is really irrelevant to that. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software
RE: cfinsert/cfupdate
> I think you are referring to SCOPE_IDENTITY() No, I did mean @@IDENTITY. Though I am going off of much older threads where it was used within a cftransaction, and separate cfquery, to retrieve the Autonumber value from an Access table. Rick's post contained the basic concept, but with cfinsert instead of cfquery. Again, I have no idea how reliable it is. > However I seem to recall it did not work last time I tried > it, but that was > ages ago. I think it requires latest version of access and > jet drivers. You might be right. I have not used it in a while. So it is entirely possible newer versions support SCOPE_IDENTITY() for Access, not just MS SQL. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337536 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
> > This isn't really correct. The query isn't compiled in any case. > Ok, so I found out I WAS correct. Either that or yet another article I found on prepared statements is wrong. Here's a quote from the article: "The PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatementSQL statement without having to compile it first." So according to this, the SQL statement IS precompiled, as I had asserted. Here's the article: http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337532 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
And plenty or sarcastic ones at that :-) It's because Dave doesn't actually do any work, he clearly spends all day on discussion lists LOL On Sat, Sep 25, 2010 at 12:52 AM, Michael Grant wrote: > > Meh. I'm keeping my Dave Watts shrine right where it is. He's a smart guy > that seems to have an answer to everything. > > (Well except my question about cfinvoke and webservices.) > /me walks away grumbling. > > On Fri, Sep 24, 2010 at 3:06 PM, Jacob Munson wrote: > > > > > Wow, calm down there partner! I clearly stated in my post that I could > be > > wrong about this, and I invited people to correct me if I am wrong. > > > > As far as "Dave is of course correct as usual". Dave has been known to > be > > wrong before. Feel free to dismantle your Dave Watts shrine. ;) > > > > I KNOW for a fact that I read an article a couple of years ago that > > explained things the way I explained them earlier. I learned that using > > prepared statements causes CF to send a compiled (yes, compiled) chunk of > > code to SQL Server, lightening the DB's load. Also, I learned that > leaving > > pieces of your "search" (the right side of Where/And) out of the bind > would > > cause the whole thing to be sent as plain SQL instead of compiled. That > is > > NOT to say that the security benefits would break down, just the > > performance. > > > > AGAIN, I will say that I could be wrong here. But I will defend myself > and > > say that whoever wrote that article a couple of years ago was wrong...not > > me. > > > > On Fri, Sep 24, 2010 at 8:17 AM, Russ Michaels > > wrote: > > > > > > > > It is a complex topic for many, and Dave is of course correct as usual. > > > It would be wise to a bit of research on the topic before giving out > > > incorrect advise and also for yourself to make sure you understand the > > > process correctly to aid in your own query design. > > > There is a lot of info regarding this on Microsofts SQL server MSDN and > > > technet pages. > > > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337529 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
Meh. I'm keeping my Dave Watts shrine right where it is. He's a smart guy that seems to have an answer to everything. (Well except my question about cfinvoke and webservices.) /me walks away grumbling. On Fri, Sep 24, 2010 at 3:06 PM, Jacob Munson wrote: > > Wow, calm down there partner! I clearly stated in my post that I could be > wrong about this, and I invited people to correct me if I am wrong. > > As far as "Dave is of course correct as usual". Dave has been known to be > wrong before. Feel free to dismantle your Dave Watts shrine. ;) > > I KNOW for a fact that I read an article a couple of years ago that > explained things the way I explained them earlier. I learned that using > prepared statements causes CF to send a compiled (yes, compiled) chunk of > code to SQL Server, lightening the DB's load. Also, I learned that leaving > pieces of your "search" (the right side of Where/And) out of the bind would > cause the whole thing to be sent as plain SQL instead of compiled. That is > NOT to say that the security benefits would break down, just the > performance. > > AGAIN, I will say that I could be wrong here. But I will defend myself and > say that whoever wrote that article a couple of years ago was wrong...not > me. > > On Fri, Sep 24, 2010 at 8:17 AM, Russ Michaels > wrote: > > > > > It is a complex topic for many, and Dave is of course correct as usual. > > It would be wise to a bit of research on the topic before giving out > > incorrect advise and also for yourself to make sure you understand the > > process correctly to aid in your own query design. > > There is a lot of info regarding this on Microsofts SQL server MSDN and > > technet pages. > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337528 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
wow, toys back in the pram dude. Don't take things so personally, you just got corrected is all. :-) On Fri, Sep 24, 2010 at 8:06 PM, Jacob Munson wrote: > > Wow, calm down there partner! I clearly stated in my post that I could be > wrong about this, and I invited people to correct me if I am wrong. > > As far as "Dave is of course correct as usual". Dave has been known to be > wrong before. Feel free to dismantle your Dave Watts shrine. ;) > > I KNOW for a fact that I read an article a couple of years ago that > explained things the way I explained them earlier. I learned that using > prepared statements causes CF to send a compiled (yes, compiled) chunk of > code to SQL Server, lightening the DB's load. Also, I learned that leaving > pieces of your "search" (the right side of Where/And) out of the bind would > cause the whole thing to be sent as plain SQL instead of compiled. That is > NOT to say that the security benefits would break down, just the > performance. > > AGAIN, I will say that I could be wrong here. But I will defend myself and > say that whoever wrote that article a couple of years ago was wrong...not > me. > > On Fri, Sep 24, 2010 at 8:17 AM, Russ Michaels > wrote: > > > > > It is a complex topic for many, and Dave is of course correct as usual. > > It would be wise to a bit of research on the topic before giving out > > incorrect advise and also for yourself to make sure you understand the > > process correctly to aid in your own query design. > > There is a lot of info regarding this on Microsofts SQL server MSDN and > > technet pages. > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337522 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
Wow, calm down there partner! I clearly stated in my post that I could be wrong about this, and I invited people to correct me if I am wrong. As far as "Dave is of course correct as usual". Dave has been known to be wrong before. Feel free to dismantle your Dave Watts shrine. ;) I KNOW for a fact that I read an article a couple of years ago that explained things the way I explained them earlier. I learned that using prepared statements causes CF to send a compiled (yes, compiled) chunk of code to SQL Server, lightening the DB's load. Also, I learned that leaving pieces of your "search" (the right side of Where/And) out of the bind would cause the whole thing to be sent as plain SQL instead of compiled. That is NOT to say that the security benefits would break down, just the performance. AGAIN, I will say that I could be wrong here. But I will defend myself and say that whoever wrote that article a couple of years ago was wrong...not me. On Fri, Sep 24, 2010 at 8:17 AM, Russ Michaels wrote: > > It is a complex topic for many, and Dave is of course correct as usual. > It would be wise to a bit of research on the topic before giving out > incorrect advise and also for yourself to make sure you understand the > process correctly to aid in your own query design. > There is a lot of info regarding this on Microsofts SQL server MSDN and > technet pages. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337520 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
I think you are referring to SCOPE_IDENTITY() http://msdn.microsoft.com/en-us/library/ks9f57t0%28VS.71%29.aspx However I seem to recall it did not work last time I tried it, but that was ages ago. I think it requires latest version of access and jet drivers. Regards -- Russ Michaels www.cfmldeveloper.com - free CFML hosting for developers my blog: http://russ.michaels.me.uk/ skype: russmichaels -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: 24 September 2010 18:16 To: cf-talk Subject: RE: cfinsert/cfupdate > There is a built in method of getting that last records > primarykey for most > databases, but not for MSACCESS. I cannot speak for its reliability, but I believe MS Access does have @@identity. However as shown in one of the previous posts, retrieving the value is a bit more convoluted than in other database types. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337512 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
On Fri, Sep 24, 2010 at 11:51 AM, Russ Michaels wrote: > > After all, you can only really discuss the possibilities if you actually > know what is and is not possible, otherwise it's the blind leading the blind > isn't it ? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337511 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
> There is a built in method of getting that last records > primarykey for most > databases, but not for MSACCESS. I cannot speak for its reliability, but I believe MS Access does have @@identity. However as shown in one of the previous posts, retrieving the value is a bit more convoluted than in other database types. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337508 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
Getting back to the OP. I can totally see the how using cfinsert and cfupdate are attractive options for RAD I use Steve Bryant's DataMgr quite a bit during development and prototyping. http://datamgr.riaforge.org/ I actually use a service layer but the end result is that I can use one line of code for simple update and insert queries using the contents of a form post. Which is not all the different than using cfupdate/cfinsert. So if you are in get 'er done development mode you don't have to worry about making changes to your queries or regenerating your database CFC's every time you make a change to the database. I just reload the datamgr CFC and I am good to go. This can shave quite a bit off development time when prototyping. When things stabilize and we are getting ready to go into production I can massage my queries, create stored procedures or even swap out my entire database layer to use ORM, Illudium CFC's or what ever and the rest of my app is none the wiser. So as an option that does not require cfinsert and cfupdate and maintains brevity and speed of coding you can use a service layer as a front end to your favorite/preferred database abstraction layer to accomplish the same thing. Tastes great AND less filling. FWEIW, G! On Fri, Sep 24, 2010 at 12:46 PM, Russ Michaels wrote: > > There is a built in method of getting that last records primarykey for most > databases, but not for MSACCESS. > You need to get the MAX() number to do that, and u should do it in a locked > transaction block so that you don't get the ID of another record that has > been added since. > Or, get the max() id, +1, turn off auto increment, and insert with explicit > ID. > > > Regards > -- > Russ Michaels > my blog: http://russ.michaels.me.uk/ > www.cfmldeveloper.com - free CFML hosting for developers > skype: russmichaels > > > > > -Original Message- > From: Leigh [mailto:cfsearch...@yahoo.com] > Sent: 24 September 2010 17:38 > To: cf-talk > Subject: Re: cfinsert/cfupdate > > > > Can you do that by running it inside cftransaction? > > I remember seeing something similar in old threads about retrieving an > Autonumber value for an Access database. I have not really thought it > through, but I suppose in theory it might work. Though it does seem like > stretching the intent of transactions a bit. > > I just wanted to confirm there was not a built-in option I was overlooking. > Though it is certainly possible to implement your own handling (ie uuid > column, etcetera...) having it done 'auto-magically', like with cfquery, > would be preferable. > > -Leigh > > > > > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337507 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
There is a built in method of getting that last records primarykey for most databases, but not for MSACCESS. You need to get the MAX() number to do that, and u should do it in a locked transaction block so that you don't get the ID of another record that has been added since. Or, get the max() id, +1, turn off auto increment, and insert with explicit ID. Regards -- Russ Michaels my blog: http://russ.michaels.me.uk/ www.cfmldeveloper.com - free CFML hosting for developers skype: russmichaels -Original Message- From: Leigh [mailto:cfsearch...@yahoo.com] Sent: 24 September 2010 17:38 To: cf-talk Subject: Re: cfinsert/cfupdate > Can you do that by running it inside cftransaction? I remember seeing something similar in old threads about retrieving an Autonumber value for an Access database. I have not really thought it through, but I suppose in theory it might work. Though it does seem like stretching the intent of transactions a bit. I just wanted to confirm there was not a built-in option I was overlooking. Though it is certainly possible to implement your own handling (ie uuid column, etcetera...) having it done 'auto-magically', like with cfquery, would be preferable. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337505 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
> Can you do that by running it inside cftransaction? I remember seeing something similar in old threads about retrieving an Autonumber value for an Access database. I have not really thought it through, but I suppose in theory it might work. Though it does seem like stretching the intent of transactions a bit. I just wanted to confirm there was not a built-in option I was overlooking. Though it is certainly possible to implement your own handling (ie uuid column, etcetera...) having it done 'auto-magically', like with cfquery, would be preferable. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337503 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
Russ Michaels wrote: > It is also easier from a self containment and portability point of view to > keep SQL within the application. Stored Procs written for MSSQL clearly wont > run on MySQL. I hear this sometime and wonder how many times you have to port something to another DBMS. I have never had to do so. I guess I have had to assure that code ran properly when we got a new version of MS-SQL Server though. I wondered about it since DBMS portability is given as a reason to code certain ways. I usually have the database transactions in specific modules so I can go back to the code easily. I don't abstract the SQL so it works in a SQL ISO standard. That seems limiting and not worth the trouble. Is it common for systems to be ported between different DBMSs? -- LinkedIn: http://www.linkedin.com/pub/roger-austin/8/a4/60 Twitter: http://twitter.com/RogerTheGeek Blog: http://rogerthegeek.wordpress.com/ http://www.misshunt.com/ Home of the Clean/Dirty Magnet ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337494 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
OK, but Given the amount incorrect assumptions made so far it would seem like a fair suggestion to make. After all, you can only really discuss the possibilities if you actually know what is and is not possible, otherwise it's the blind leading the blind isn't it ? Russ Michaels www.cfmldeveloper.com FREE CFML developer hosting -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: 24 September 2010 16:34 To: cf-talk Subject: Re: cfinsert/cfupdate On Fri, Sep 24, 2010 at 11:13 AM, Russ Michaels wrote: > > This is covered in the CF docs actually, so if your not sure you can RTFM. We're having a discussion on the possibilities. Someone else can RTFM if they're interested in actually trying what I'm suggesting. =) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337492 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
On Fri, Sep 24, 2010 at 11:13 AM, Russ Michaels wrote: > > This is covered in the CF docs actually, so if your not sure you can RTFM. We're having a discussion on the possibilities. Someone else can RTFM if they're interested in actually trying what I'm suggesting. =) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337491 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
Stored procs is not always the best solution either. There is plenty of good reason to keep your queries inside your CFML templates. If you have the luxury of a DBA then he will probably be doing everything for you in stored procs, or if you are a lone developer then you can make the choice. However people working in teams or on contracts may not have direct access to the Database server, so adding or editing stored procs can be a real problem. Unless the SP is really adding a significant performance boost or achieving something you cannot do in plain TSQL in your CFQUERY then you are probably not benefitting from it. It is also easier from a self containment and portability point of view to keep SQL within the application. Stored Procs written for MSSQL clearly wont run on MySQL. So remember, it's the right tool for the job at the right time. Regards -- Russ Michaels my blog: http://russ.michaels.me.uk/ skype: russmichaels MSM: r...@michaels.me.uk Russ -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: 24 September 2010 15:29 To: cf-talk Subject: Re: cfinsert/cfupdate Just for fun, here's some examples of what SQL Server receives from coldfusion when you run a pretty simple query using cfqueryparam: declare @p1 int set @p1=748592 exec sp_prepexec @p1 output,N'@P1 int,@P2 bit',N'select gallery_link_id, gallery_link_name from gallery_links where class_id = @P1 and active = @P2 order by sort',8806,1 select @p1 This exec sp_prepexec shit is one reason why, if you're writing an app that needs to scale, you probably ought to actually write your own stored procedures rather than letting CF do this kind of thing what's interesting is that I've found cases where I'd run my "query" in SQL Server Management studio, and compare the execution plan to a query like the one that SQL Server actually executes (above) and find that they're different sometimes. So writing your OWN stored procedure is the only way to be sure that the database server is doign exactly what you think its doing (or what you want it to do). rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337487 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
Any database transaction can go inside a transaction block. The purpose of this is to basically treat everything inside as a single transaction so that you can do a ROLLback if there is any error. This is covered in the CF docs actually, so if your not sure you can RTFM. Russ -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: 24 September 2010 16:01 To: cf-talk Subject: Re: cfinsert/cfupdate On Fri, Sep 24, 2010 at 10:55 AM, Leigh wrote: > > Sounds good. It would certainly come in handy. Especially the ability to retrieve identity/autoincrement values. Can you do that by running it inside cftransaction? select @@identity ... or whatever your db uses I'm not sure if putting this kind of thing in cftransaction does anything for you at all or even if you can put cfinsert inside a transaction... Ric ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337486 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
On Fri, Sep 24, 2010 at 10:55 AM, Leigh wrote: > > Sounds good. It would certainly come in handy. Especially the ability to > retrieve identity/autoincrement values. Can you do that by running it inside cftransaction? select @@identity ... or whatever your db uses I'm not sure if putting this kind of thing in cftransaction does anything for you at all or even if you can put cfinsert inside a transaction... Ric ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337479 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
Hi Aaron, Sounds good. It would certainly come in handy. Especially the ability to retrieve identity/autoincrement values. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337478 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
Just for fun, here's some examples of what SQL Server receives from coldfusion when you run a pretty simple query using cfqueryparam: declare @p1 int set @p1=748592 exec sp_prepexec @p1 output,N'@P1 int,@P2 bit',N'select gallery_link_id, gallery_link_name from gallery_links where class_id = @P1 and active = @P2 order by sort',8806,1 select @p1 This exec sp_prepexec shit is one reason why, if you're writing an app that needs to scale, you probably ought to actually write your own stored procedures rather than letting CF do this kind of thing what's interesting is that I've found cases where I'd run my "query" in SQL Server Management studio, and compare the execution plan to a query like the one that SQL Server actually executes (above) and find that they're different sometimes. So writing your OWN stored procedure is the only way to be sure that the database server is doign exactly what you think its doing (or what you want it to do). rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337473 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
It is a complex topic for many, and Dave is of course correct as usual. It would be wise to a bit of research on the topic before giving out incorrect advise and also for yourself to make sure you understand the process correctly to aid in your own query design. There is a lot of info regarding this on Microsofts SQL server MSDN and technet pages. Russ -Original Message- From: Dave Watts [mailto:dwa...@figleaf.com] Sent: 24 September 2010 15:09 To: cf-talk Subject: Re: cfinsert/cfupdate > I don't know the deep technical details, but I do know that many people > argue that using query params actually gives you a performance benefit over > plain SQL (although a lot of people disagree). This isn't really a matter for debate. In some cases, parameterized SQL is faster. In other cases, it's slower. So, you really have to examine the specifics of your case to determine whether it'll be faster or slower. Fortunately, this is pretty easy to do. But in any case, the primary reason for building prepared statements isn't to make things faster, it's to make them more secure. So, the appropriate default behavior is to parameterize your SQL to prevent SQL injection attacks, and if it's a little slower because of this, so be it. > Regardless of the debate over which method is faster, the way it works is if you query > param ALL of your variables (including values in the SQL that don't come from CF), > ColdFusion will create what is called a prepared statement (sometimes called > a parameterized statement, or bind parameter). From what I understand, the > way this works is that ColdFusion "compiles" your query down to machine code > that the DB just executes. If you don't query param every value, the DB has > to compile the statement, which includes syntax checking and all that jazz. This isn't really correct. The query isn't compiled in any case. And a prepared statement doesn't require that every value be represented by a parameter. When you send an SQL statement to the database, that database builds an execution plan. Building an execution plan can be an expensive operation - it can actually take longer than executing the plan once it's built. The database can then cache the execution plan so that if the query is run again, the execution plan can be reused. Prepared statements simply include placeholders where values would go. The database can build a single execution plan, and use it no matter what the values in the placeholders turn out to be for one query or another. If the same execution plan works well for all possible values that you'd plug into those placeholders, your queries will run faster. If, however, you have a set of values that would be better served by a different execution plan, that query may run slower (or may not, depending on how unoptimal the existing execution plan is and whether that is balanced by not having to build the execution plan in the first place). > That said, this does not work if you don't use cfqueryparams for > everything. For example, this query would NOT be a prepared statement (from > what I understand): > > > select birthDate > from familyGroups > where child = cfsqltype="cf_sql_varchar"> > and stillMinor = 1 > > > The reason that it would not be a prepared statement is because of the > "stillMinor = 1" part. Even though that is a static value that never > changes, you still want to cfqueryparam that if you want your sql to be > compiled to a prepared statement. Otherwise the DB server will still have > to do the work when it receives the SQL query from ColdFusion. That's incorrect. The "stillMinor = 1" would not be represented by a placeholder, but "child" would be, and all you need is one placeholder to have a prepared statement. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, onlin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337471 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
> I don't know the deep technical details, but I do know that many people > argue that using query params actually gives you a performance benefit over > plain SQL (although a lot of people disagree). This isn't really a matter for debate. In some cases, parameterized SQL is faster. In other cases, it's slower. So, you really have to examine the specifics of your case to determine whether it'll be faster or slower. Fortunately, this is pretty easy to do. But in any case, the primary reason for building prepared statements isn't to make things faster, it's to make them more secure. So, the appropriate default behavior is to parameterize your SQL to prevent SQL injection attacks, and if it's a little slower because of this, so be it. > Regardless of the debate over which method is faster, the way it works is if > you query > param ALL of your variables (including values in the SQL that don't come from > CF), > ColdFusion will create what is called a prepared statement (sometimes called > a parameterized statement, or bind parameter). From what I understand, the > way this works is that ColdFusion "compiles" your query down to machine code > that the DB just executes. If you don't query param every value, the DB has > to compile the statement, which includes syntax checking and all that jazz. This isn't really correct. The query isn't compiled in any case. And a prepared statement doesn't require that every value be represented by a parameter. When you send an SQL statement to the database, that database builds an execution plan. Building an execution plan can be an expensive operation - it can actually take longer than executing the plan once it's built. The database can then cache the execution plan so that if the query is run again, the execution plan can be reused. Prepared statements simply include placeholders where values would go. The database can build a single execution plan, and use it no matter what the values in the placeholders turn out to be for one query or another. If the same execution plan works well for all possible values that you'd plug into those placeholders, your queries will run faster. If, however, you have a set of values that would be better served by a different execution plan, that query may run slower (or may not, depending on how unoptimal the existing execution plan is and whether that is balanced by not having to build the execution plan in the first place). > That said, this does not work if you don't use cfqueryparams for > everything. For example, this query would NOT be a prepared statement (from > what I understand): > > > select birthDate > from familyGroups > where child = cfsqltype="cf_sql_varchar"> > and stillMinor = 1 > > > The reason that it would not be a prepared statement is because of the > "stillMinor = 1" part. Even though that is a static value that never > changes, you still want to cfqueryparam that if you want your sql to be > compiled to a prepared statement. Otherwise the DB server will still have > to do the work when it receives the SQL query from ColdFusion. That's incorrect. The "stillMinor = 1" would not be represented by a placeholder, but "child" would be, and all you need is one placeholder to have a prepared statement. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, onlin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337470 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
On Thu, Sep 23, 2010 at 12:10 PM, Jacob Munson wrote: > While I won't tell ORM fans that they shouldn't use ORM, I have done my fair > share of "railing" against ORM. As you have against OO and various other "modern" ways of thinking about software - and as Rick points out you are wrong about the prepared SQL statement (as you are about OO, IMO). It's perfectly fine to decide not to leverage these shortcuts and strategies but please make sure you're doing it from an informed position rather a position of ignorance... -- Sean A Corfield -- (904) 302-SEAN Railo Technologies, Inc. -- http://getrailo.com/ An Architect's View -- http://corfield.org/ "If you're not annoying somebody, you're not really alive." -- Margaret Atwood ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337452 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
Hi Leigh, Good points you make here. Not sure why cfinsert|update don't accept a "result" attribute and return a result struct. I'll probably have to re-file, since I don't see ER #71025 (filed in 2007) in the tracker anymore. Thanks, -Aaron Neff > 1) Retrieve an identity/autoincrement value from an INSERT > 2) Return the number of records affected by an UPDATE > 3) (Also, from a recent question on the adobe forums..) Do you need to > handle/escape invalid column names? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337451 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
On Thu, Sep 23, 2010 at 3:02 PM, Jacob Munson wrote: > > > That said, this does not work if you don't use cfqueryparams for > everything. For example, this query would NOT be a prepared statement (from > what I understand): > > > select birthDate > from familyGroups > where child = cfsqltype="cf_sql_varchar"> > and stillMinor = 1 > Actually that is most definatley NOT true. If you run the same query again with a different value for #form.children#, it will use the prepared statement ... but if you were to run it with a different value for stillMinor, it would create a NEW prepared statement. So if you're always running the query with "stillMinor=1" then you don't need to put the "1" in a cfqueryparam, you still get all the benefits of the behind the scenes stuff that CF does. BTW if you ever really want to see what coldfusion is sending to the database, and you're using SQL Server, turn on SQL Profiler. It's some crazy shit ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337439 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
> The thing is that it's not really about "sides" it's about > using the right > tools at the right times. The remark about "sides" was intended as sarcasm ;) ie Conversations about cfinsert/cfupdate tend to lean towards the passionate side. I was totally serious when I said some good points were made on both sides. Having said that, personally, I do not use those tags. Nor do I see myself using them in the future. They just do not provide enough flexibility for most (if not all) of my applications. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337422 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
> Some good points were made, on both sides. I am almost > thinking about switching sides ... not. The thing is that it's not really about "sides" it's about using the right tools at the right times. If you have a basic update/insert query that doesn't have any special needs, I suppose using those tags could save some time (esp. if you have a lot of those basic queries to write). For queries that need special care, rolling your own would likely be a better approach. If you're CFC-heavy already then ORM might be a good choice. Being a good programmer shouldn't be about "I always write my own SQL" it should be about using the most effective tool for the job at hand. One of the things I love about CF is that it gives you these options to choose from. -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337418 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
> > While I generally agree with that sentiment, ORM does the same thing on a > more grand scale, and I haven't seen anyone railing against using that > technology. > While I won't tell ORM fans that they shouldn't use ORM, I have done my fair share of "railing" against ORM. As others have said in this thread, I don't like losing control over my SQL. I used to think that ORM was just a crutch for people that hate writing SQL (because they never took the time to properly learn SQL). While I still believe that is the case for some ORM users (I won't name names, but I personally know a few developers that would fit this description), I have since come to realize that there are many SQL gurus out there that like to use ORM because of it's convenience, as well as how tightly it can integrate with OO techniques and frameworks. Personally, I am not one of those people, but I won't say that ORM is bad and nobody should use it. To each his own. :) ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337416 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
On Thu, Sep 23, 2010 at 9:50 AM, Michael Grant wrote: > > +1 > > I feel exactly the same way and still write my SQL statements myself. IMO > it's necessary when you start getting into multiple advanced joins, computed > columns and inline equations. This is actually where ORM shines. You can do *crazy awesome* stuff with Hibernate. You can model your model in a variety of ways, that can make things really easy from a programming perspective. It's like learning a new language tho, and a lot of it can still end up being DB specific, so... eh. I bet very few people are using HBM files, where a lot of the magic happens. :Den -- Through shallow intellect, the mind becomes shallow, and one eats the fly, along with the sweets. Guru Nanak ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337409 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
> > How does Coldfusion manufacturer the queryparams? It must inspect the > database to determine field types... I wonder if that's a performance > hit... > I don't know the deep technical details, but I do know that many people argue that using query params actually gives you a performance benefit over plain SQL (although a lot of people disagree). Regardless of the debate over which method is faster, the way it works is if you query param ALL of your variables (including values in the SQL that don't come from CF), ColdFusion will create what is called a prepared statement (sometimes called a parameterized statement, or bind parameter). From what I understand, the way this works is that ColdFusion "compiles" your query down to machine code that the DB just executes. If you don't query param every value, the DB has to compile the statement, which includes syntax checking and all that jazz. That said, this does not work if you don't use cfqueryparams for everything. For example, this query would NOT be a prepared statement (from what I understand): select birthDate from familyGroups where child = and stillMinor = 1 The reason that it would not be a prepared statement is because of the "stillMinor = 1" part. Even though that is a static value that never changes, you still want to cfqueryparam that if you want your sql to be compiled to a prepared statement. Otherwise the DB server will still have to do the work when it receives the SQL query from ColdFusion. At least, that is how I understand things. If anybody sees a flaw in my explanation, feel free to jump in and correct me. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337408 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
Some good points were made, on both sides. I am almost thinking about switching sides ... not. But on a more neutral note, there are a few small considerations that might also affect the decision to use one or the other. ie Do you need any of the following functionality 1) Retrieve an identity/autoincrement value from an INSERT 2) Return the number of records affected by an UPDATE 3) (Also, from a recent question on the adobe forums..) Do you need to handle/escape invalid column names? My guess would be these features are "not supported" with cfinsert/cfupdate. But I do not know for certain. -Leigh ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337407 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
> No, it certainly doesn't. If you write the SQL, they can't post > additional form fields that you're not expecting and have them get > into your SQL statement. If I recall correctly, you can limit the form fields used by CFINSERT/CFUPDATE using the FORMFIELDS attribute. So that's not a big deal as long as those fields are explicitly specified. In general, unless you have control over coding standards in your organization, I suspect you're not going to win this battle. Personally, I don't like them and haven't used them outside of a classroom - and even then, that was many years ago - but I don't think there's a significant difference in performance or security. There may be a bit of a performance hit for CF to identify SQL data types, but I can't imagine that's significant. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337404 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
+1 I feel exactly the same way and still write my SQL statements myself. IMO it's necessary when you start getting into multiple advanced joins, computed columns and inline equations. On Thu, Sep 23, 2010 at 11:38 AM, Jason Fisher wrote: > > Actually, I don't use ORM for much the same reason I don't use cfinsert / > cfupdate. I still like to craft my SQL and I often have complex > relationships that are easy to write in SQL and a PITA to model in an ORM. > > - Jason > "Smokey the Bears rules for fire safety also apply to government: Keep it > small, keep it in a confined area, and keep an eye on it." > > > > > From: "Russ Michaels" > Sent: Thursday, September 23, 2010 11:30 AM > To: "cf-talk" > Subject: RE: cfinsert/cfupdate > > That's a good point I didn't consider that ORM is just doing the same > thing. > And I bet you are all happily using Transfer and Reactor in the same way. > > Russ > > -Original Message- > From: Justin Scott [mailto:jscott-li...@gravityfree.com] > Sent: 23 September 2010 16:27 > To: cf-talk > Subject: RE: cfinsert/cfupdate > > > For me I like to be able to have as much control over my > > query statements as possible. It makes it easier to read/ > > understand and to see EXACTLY what is being sent to the db. > > While I generally agree with that sentiment, ORM does the same thing on a > more grand scale, and I haven't seen anyone railing against using that > technology. Personally it's been many years since I last looked at > cfinsert/cfupdate, but if the queries that they generate are 'safe' then it > might be worth taking another look at as an option to use where > appropriate. > > -Justin > > > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337398 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
Actually, I don't use ORM for much the same reason I don't use cfinsert / cfupdate. I still like to craft my SQL and I often have complex relationships that are easy to write in SQL and a PITA to model in an ORM. - Jason "Smokey the Bearâs rules for fire safety also apply to government: Keep it small, keep it in a confined area, and keep an eye on it." From: "Russ Michaels" Sent: Thursday, September 23, 2010 11:30 AM To: "cf-talk" Subject: RE: cfinsert/cfupdate That's a good point I didn't consider that ORM is just doing the same thing. And I bet you are all happily using Transfer and Reactor in the same way. Russ -Original Message- From: Justin Scott [mailto:jscott-li...@gravityfree.com] Sent: 23 September 2010 16:27 To: cf-talk Subject: RE: cfinsert/cfupdate > For me I like to be able to have as much control over my > query statements as possible. It makes it easier to read/ > understand and to see EXACTLY what is being sent to the db. While I generally agree with that sentiment, ORM does the same thing on a more grand scale, and I haven't seen anyone railing against using that technology. Personally it's been many years since I last looked at cfinsert/cfupdate, but if the queries that they generate are 'safe' then it might be worth taking another look at as an option to use where appropriate. -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337397 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
Rick, Saying those tags are ok to use is not promoting people to write insecure applications and I certainly would not imply that. Protecting your application against SQL/XSS attacks is something you should do regardless, so I would not say it defeats the point at all, otherwise you could say the same about ORM or any other framework because you still have to write code of your own to make it do what you want. These things are there to aid in your development and speed things up, not to be some magic bullet that you can rely on to do everything for you. However in order for that to happen the developer has to actually know what these things are and that he has to protect against them, and the typical newbie is not going to know this, so it is really a moot point. Russ -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: 23 September 2010 16:28 To: cf-talk Subject: Re: cfinsert/cfupdate On Thu, Sep 23, 2010 at 11:24 AM, Russ Michaels wrote: > > That applies across the board Rick, to any sql in any code on any site. If No, it certainly doesn't. If you write the SQL, they can't post additional form fields that you're not expecting and have them get into your SQL statement. > You can SCAN the FORM scope and simply remove anything that shouldn't be > there or simply do not execute the SQL code if you think the request did not > come form the original form. Well sure but that kinda defeats the purpose of the simplicity of these tags. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337395 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
That's a good point I didn't consider that ORM is just doing the same thing. And I bet you are all happily using Transfer and Reactor in the same way. Russ -Original Message- From: Justin Scott [mailto:jscott-li...@gravityfree.com] Sent: 23 September 2010 16:27 To: cf-talk Subject: RE: cfinsert/cfupdate > For me I like to be able to have as much control over my > query statements as possible. It makes it easier to read/ > understand and to see EXACTLY what is being sent to the db. While I generally agree with that sentiment, ORM does the same thing on a more grand scale, and I haven't seen anyone railing against using that technology. Personally it's been many years since I last looked at cfinsert/cfupdate, but if the queries that they generate are 'safe' then it might be worth taking another look at as an option to use where appropriate. -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337394 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
On Thu, Sep 23, 2010 at 11:24 AM, Russ Michaels wrote: > > That applies across the board Rick, to any sql in any code on any site. If No, it certainly doesn't. If you write the SQL, they can't post additional form fields that you're not expecting and have them get into your SQL statement. > You can SCAN the FORM scope and simply remove anything that shouldn't be > there or simply do not execute the SQL code if you think the request did not > come form the original form. Well sure but that kinda defeats the purpose of the simplicity of these tags. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337393 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
Well if you turn on full debugging then u can see the sql statements, you could also enable SQL profiling on the sql server to see what was going on if you really wanted to know. Yes it introspects the database to get the metadata it needs, I did do some performance testing once and did not find any significant performance issues. Russ -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: 23 September 2010 16:25 To: cf-talk Subject: Re: cfinsert/cfupdate How does Coldfusion manufacturer the queryparams? It must inspect the database to determine field types... I wonder if that's a performance hit... I just hate not knowing what the hell its doing. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337392 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
> For me I like to be able to have as much control over my > query statements as possible. It makes it easier to read/ > understand and to see EXACTLY what is being sent to the db. While I generally agree with that sentiment, ORM does the same thing on a more grand scale, and I haven't seen anyone railing against using that technology. Personally it's been many years since I last looked at cfinsert/cfupdate, but if the queries that they generate are 'safe' then it might be worth taking another look at as an option to use where appropriate. -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337391 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
I wasn't saying the tags are no good for veterans, just who they are predominantly aimed at. The average developer I have found quite sucks at SQL and database design. -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: 23 September 2010 16:24 To: cf-talk Subject: Re: cfinsert/cfupdate On Thu, Sep 23, 2010 at 11:19 AM, Russ Michaels wrote: > > So I'd say he is right in the broadest sense, and these tags are especially > good for newbies and developers who just learn a small set of CFML tags or Sadly, he's a 10 year veteran CF programmer. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337390 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
How does Coldfusion manufacturer the queryparams? It must inspect the database to determine field types... I wonder if that's a performance hit... I just hate not knowing what the hell its doing. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337389 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
That applies across the board Rick, to any sql in any code on any site. If you have not taken measures to stop that kind of thing then you are vulnerable regardless. But it is just as easy to put a stop to that if your using cfinsert and cfupdate. You can SCAN the FORM scope and simply remove anything that shouldn't be there or simply do not execute the SQL code if you think the request did not come form the original form. Russ -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: 23 September 2010 16:21 To: cf-talk Subject: Re: cfinsert/cfupdate It seems to me that using cfinsert and cfupdate is a security risk. I mean, what if I wrote a script to post the form with additional form fields? I mean, people don't always know your db structure but they can guess at things sometimes. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337388 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
On Thu, Sep 23, 2010 at 11:19 AM, Russ Michaels wrote: > > So I'd say he is right in the broadest sense, and these tags are especially > good for newbies and developers who just learn a small set of CFML tags or Sadly, he's a 10 year veteran CF programmer. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337387 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
It seems to me that using cfinsert and cfupdate is a security risk. I mean, what if I wrote a script to post the form with additional form fields? I mean, people don't always know your db structure but they can guess at things sometimes. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337386 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfinsert/cfupdate
I also used to think that cfinsert and cfupdate were rubbish, then when I actually used them properly one day I came to a similar conclusion as your friend. They certainly can be very handy and time saving tags when working with simple update/insert from forms as they directly convert form field names into column names and do everything for you. Where they obviously fall down is where you need to use conditional logic in your query to determine what values may or may not be used or where you may be using values that didn't come from the form. So I'd say he is right in the broadest sense, and these tags are especially good for newbies and developers who just learn a small set of CFML tags or use the dreamweaver builders and tools to generate code and probably never going to get into OOP or ORM. Regards -- Russ Michaels my blog: http://russ.michaels.me.uk/ skype: russmichaels MSM: r...@michaels.me.uk -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: 23 September 2010 16:09 To: cf-talk Subject: cfinsert/cfupdate I've been trying to convince one of my friends that using cfinsert and cfupdate is a bad idea. I recently told him that real CF programmers don't use cfinsert and cfupdate :) He responded with this: CF8 uses cfqueryparam inside all cfinsert/cfupdate statements, making it just as safe as a regular cfquery, but minus all the clunky code needed to create the query. Actually, scratch that, both queries, since without them you have to maintain both the insert and update statements separately if you add a new form field to a form. cfinsert can do in one line of code what otherwise could be hundreds, not to mention saving you all the ridiculous potential errors from not having commas in the correct place, accidentally using or not using quotes, or mis-matching insert values since the syntax of inserts is different from updates and the two lines you need to add can often be quite far apart. Call me proud to not be a real cf programmer. I just don't know how to respond to this. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337385 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
re: cfinsert/cfupdate
Well, he's completely right, of course. Personally, I really like to have the control over my SQL statements, just like I do over my other code, so I write them out. Allows me to test for NULLs (empty integer fields, for example) or to build computed fields or wrap sequences of queries in cftransaction or whatever. But, that being said, it seems like cfinsert / cfupdate have gotten better since the bad old days where they were truly scary. I don't use the tags personally and I don't plan to, but your friend's arguments seem sound. - Jason "Smokey the Bearâs rules for fire safety also apply to government: Keep it small, keep it in a confined area, and keep an eye on it." From: "Rick Root" Sent: Thursday, September 23, 2010 11:09 AM To: "cf-talk" Subject: cfinsert/cfupdate I've been trying to convince one of my friends that using cfinsert and cfupdate is a bad idea. I recently told him that real CF programmers don't use cfinsert and cfupdate :) He responded with this: CF8 uses cfqueryparam inside all cfinsert/cfupdate statements, making it just as safe as a regular cfquery, but minus all the clunky code needed to create the query. Actually, scratch that, both queries, since without them you have to maintain both the insert and update statements separately if you add a new form field to a form. cfinsert can do in one line of code what otherwise could be hundreds, not to mention saving you all the ridiculous potential errors from not having commas in the correct place, accidentally using or not using quotes, or mis-matching insert values since the syntax of inserts is different from updates and the two lines you need to add can often be quite far apart. Call me proud to not be a real cf programmer. I just don't know how to respond to this. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337384 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfinsert/cfupdate
Direct him to Microsoft Front Page or Dreamweaver if he feels like giving up all his granular control. For me I like to be able to have as much control over my query statements as possible. It makes it easier to read/understand and to see EXACTLY what is being sent to the db. Sounds to me like your friend is just lazy. On Thu, Sep 23, 2010 at 11:08 AM, Rick Root wrote: > > I've been trying to convince one of my friends that using cfinsert and > cfupdate is a bad idea. > > I recently told him that real CF programmers don't use cfinsert and > cfupdate :) He responded with this: > > CF8 uses cfqueryparam inside all cfinsert/cfupdate statements, making > it just as safe as a regular cfquery, but minus all the clunky code > needed to create the query. Actually, scratch that, both queries, > since without them you have to maintain both the insert and update > statements separately if you add a new form field to a form. cfinsert > can do in one line of code what otherwise could be hundreds, not to > mention saving you all the ridiculous potential errors from not having > commas in the correct place, accidentally using or not using quotes, > or mis-matching insert values since the syntax of inserts is different > from updates and the two lines you need to add can often be quite far > apart. Call me proud to not be a real cf programmer. > > I just don't know how to respond to this. > > Rick > > ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337383 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfinsert/cfupdate
I've been trying to convince one of my friends that using cfinsert and cfupdate is a bad idea. I recently told him that real CF programmers don't use cfinsert and cfupdate :) He responded with this: CF8 uses cfqueryparam inside all cfinsert/cfupdate statements, making it just as safe as a regular cfquery, but minus all the clunky code needed to create the query. Actually, scratch that, both queries, since without them you have to maintain both the insert and update statements separately if you add a new form field to a form. cfinsert can do in one line of code what otherwise could be hundreds, not to mention saving you all the ridiculous potential errors from not having commas in the correct place, accidentally using or not using quotes, or mis-matching insert values since the syntax of inserts is different from updates and the two lines you need to add can often be quite far apart. Call me proud to not be a real cf programmer. I just don't know how to respond to this. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337382 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: GoDaddy, Access DBs, and cfinsert/cfupdate
> I'm porting a website for a client over to GoDaddy from another > server. Using cfinsert/cfupdate tags to manipulate Access databases. > > When trying to insert/edit more than a few sentences of data into a > memo field, it causes an error on the GoDaddy server: > > "Error Executing Database Query. > Application uses a value of the wrong type for the current operation." > > GoDaddy, of course, will not enable Robust Exception Information so > that's all I get. > > What works perfectly on the other server fails on GoDaddy. Both are > CF7. Any ideas? I suggest you use CF7's logging and error handling to capture the error information yourself. If I had to guess, I'd say it's probably a datasource setting controlling the length and use of large object fields (CLOB, BLOB). Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more informati ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324013 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
GoDaddy, Access DBs, and cfinsert/cfupdate
I'm porting a website for a client over to GoDaddy from another server. Using cfinsert/cfupdate tags to manipulate Access databases. When trying to insert/edit more than a few sentences of data into a memo field, it causes an error on the GoDaddy server: "Error Executing Database Query. Application uses a value of the wrong type for the current operation." GoDaddy, of course, will not enable Robust Exception Information so that's all I get. What works perfectly on the other server fails on GoDaddy. Both are CF7. Any ideas? Thanks in advance, Les ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324012 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CFINSERT, CFUPDATE, Ben Forta, SQL injection and more...
I've inherited a large site chock full of cfupdate/cfinsert. Rewriting all of them is probably outside the scope of the budget given me for other updates, but now I'm curious... Ben Forta says: "And what's more, and actually help avoid common pitfalls and problems. Do variables need single quotes around them or not? Not an issue. Dates need to be handled specially? Nope. The dangers of a malformed WHERE clause (too many beginners have mistakenly typed WHERE id=id or WHERE #id#=#id# when they meant WHERE id=#id#) are diminished. SQL injection risks? Not an issue. These are real benefits not to be discounted." Why are SQL injection risks considered "not an issue"? I can find little information elsewhere in this. The action pages are coded using cfparam before the query tags like: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:288910 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: cfinsert/cfupdate and double quotes
>It's just not *displaying* when looking at the above. > >So >value="#htmleditformat(getapp.ID_app_owner)#" > > >Or is there a better way? That is what I do. This also helps with security. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270674 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfinsert/cfupdate and double quotes
Steve Bryant wrote: > Are you sure that the problem isn't that you are > using instead of ? After looking at it a little closer - my bad. The value Bob "Bubba" Jones *is* getting recorded correctly on insert/update. It's just not *displaying* when looking at the above. So value="#htmleditformat(getapp.ID_app_owner)#" Or is there a better way? ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270673 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: cfinsert/cfupdate and double quotes
Are you sure that the problem isn't that you are using instead of ? I just ran a test on cfinsert with quoted values in CF7 against SQL Server 2000 and it inserted the quoted values without any problem. If you load values with quotes into an input box, then the value will be loaded into an attribute of the tag and the browser will interpret the value as the end of the attribute value. You might use HTMLEditFormat() or replace the quote with ". Steve Bryant 918-449-9440 Bryant Web Consulting LLC http://www.BryantWebConsulting.com/ http://steve.coldfusionjournal.com/ >I thought cfinsert and cfupdate escaped double quotes? > >SQL Server vchar fields - seems the answer is NOPE. >CFINSERT will not accept Bob "Bubba" Jones as input into SQL Server >vchar field. No problem with an nTEXT field though. > >Any way around this without rewriting all my queries to use SQL inserts >instead of cfinsert/cfupdate? ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270671 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
cfinsert/cfupdate and double quotes
I thought cfinsert and cfupdate escaped double quotes? SQL Server vchar fields - seems the answer is NOPE. CFINSERT will not accept Bob "Bubba" Jones as input into SQL Server vchar field. No problem with an nTEXT field though. Any way around this without rewriting all my queries to use SQL inserts instead of cfinsert/cfupdate? ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2 Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270670 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: cfinsert cfupdate and Oracle 10g - bugs
Change subject line to see if someone might respond. Shelley -Original Message- From: Shelley Browning [mailto:[EMAIL PROTECTED] Sent: Thursday, November 02, 2006 11:08 AM To: CF-Talk Subject: cfinsert cfupdate and Oracle 10g I'm migrating a CF 4/Oracle 8 application that uses and tags to CFMX 7.0.2 and Oracle 10g. The tags are erroring on the table name property. The way this database access is set up I must prefix the table name with the schema name in order to access the data. When I use SCHEMA.TABLE_NAME, both tags error on the table name. Does anyone know of specific issues between CFMX 7 and Oracle 10g? The tags are used in third party custom tags that generate dynamic forms and therefore don't want to change to . CF Exception Text: This error may be caused by not specifying the table name in the format that the database server expects. (For example. Oracle Server table name must be upper case or the Server table name must be qualified in the format of "schema.table_name". For the SQL Server, the format is "databasename.username.tablename", such as in customers.dbo.orders) There are no columns found for the given table name: "SCHEMA.TABLE_NAME" Shelley Browning ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:258939 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
cfinsert cfupdate and Oracle 10g
I'm migrating a CF 4/Oracle 8 application that uses and tags to CFMX 7.0.2 and Oracle 10g. The tags are erroring on the table name property. The way this database access is set up I must prefix the table name with the schema name in order to access the data. When I use SCHEMA.TABLE_NAME, both tags error on the table name. Does anyone know of specific issues between CFMX 7 and Oracle 10g? The tags are used in third party custom tags that generate dynamic forms and therefore don't want to change to . CF Exception Text: This error may be caused by not specifying the table name in the format that the database server expects. (For example. Oracle Server table name must be upper case or the Server table name must be qualified in the format of "schema.table_name". For the SQL Server, the format is "databasename.username.tablename", such as in customers.dbo.orders) There are no columns found for the given table name: "SCHEMA.TABLE_NAME" Shelley Browning ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:258887 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
