Re: Microsoft JDBC Driver: Multiple SQL statements
Quite, and using semi-colons did not help in this case. With all the drivers, using the 'result' attribute worked for getting the newly created id. However, I implemented the BEGIN... END syntax as it was the cleanest and quickest to implement without introducing bugs - having no budget for the extensive testing requirement created :~( It's definitely an interesting area as I don't think the choice of driver is arbritary. Certainly not in our case where heap memory usage is dropping from an average 800-1000 Mb with the CF shipped driver to around 300Mb with the jTDS driver. Youch. This is on CF 8.0.1 Dominic On 1 February 2010 20:25, Leigh cfsearch...@yahoo.com wrote: I'm just curious: don't you need to separate the statements with semicolons? I do not think it is technically required with SQL Server. Though it may be in the ANSI specs. In which case, it is probably not a bad idea to use them. ~| 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:330332 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
heap memory usage is dropping from an average 800-1000 Mb with the CF shipped driver to around 300Mb with the jTDS driver. Youch. Very interesting. If it is really not arbitrary, I wonder what causes such a significant difference. I will have to do some more reading on jTDS. Quite, and using semi-colons did not help in this case. I could be wrong, but I think comment about semi-colons was just about standards / best practices , rather than having any impact on this case. MS SQL may be a bit unusual in not requiring them for multiple statements. -Leigh ~| 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:330336 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
Yes, you're right. Just making sure that whoever read this knew it was an explored avenue ;) The issue with the driver is documented: http://www.alagad.com/blog/post.cfm/mssql-driver-issue-with-cf-8-0-1 Using either the MS or the jTDS driver eliminates the issue. Dominic On 2 February 2010 15:15, Leigh cfsearch...@yahoo.com wrote: heap memory usage is dropping from an average 800-1000 Mb with the CF shipped driver to around 300Mb with the jTDS driver. Youch. Very interesting. If it is really not arbitrary, I wonder what causes such a significant difference. I will have to do some more reading on jTDS. Quite, and using semi-colons did not help in this case. I could be wrong, but I think comment about semi-colons was just about standards / best practices , rather than having any impact on this case. MS SQL may be a bit unusual in not requiring them for multiple statements. -Leigh ~| 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:330339 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Microsoft JDBC Driver: Multiple SQL statements
I recently switched to using the latest Microsoft JDBC Driver for MSSQL 2005 and immediately saw a performance increase (over using the standard driver w/ ColdFusion 8.01). However, all our queries that have multiple statements are failing so I've had to switch back. e.g. cfquery datasource=my-datasource name=my-query UPDATE foo SET bar = 1 SELECT bar FROM foo /cfquery With the standard built in driver I can reference my-query.bar, with the MS JDBC Driver I cannot (get a 'bar is undefined in my-query' error). Has anyone experienced this? Is there a way to enable multiple statements using the jdbc driver? I've checked this page out and couldn't see anything that fit the bill: http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=datasources_ADV_MJS_11.html TIA Dominic ~| 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:330292 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
cfquery datasource=my-datasource name=my-query UPDATE foo SET bar = 1 SELECT bar FROM foo /cfquery Any difference if you surround the entire block with a SET NOCOUNT ON/OFF? ~| 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:330293 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
It appears to be a limitation of the M$ jdbc driver. Now using the jTDS driver and all works well and with the same performance improvements (specifically, reduced memory usage). Hoorah for OSS! Dominic On 1 February 2010 13:23, Leigh cfsearch...@yahoo.com wrote: cfquery datasource=my-datasource name=my-query UPDATE foo SET bar = 1 SELECT bar FROM foo /cfquery Any difference if you surround the entire block with a SET NOCOUNT ON/OFF? ~| 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:330294 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
It appears to be a limitation of the M$ jdbc driver. I do not know that it is a limitation of the driver. From everything I have read, I have always felt it was due to CF's handling of multiple statements within cfquery, and the whole one query/resultset limitation. Though I prefer procedures, I have definitely used multiple statements within cfquery + the MS driver in the past (usually with SET NOCOUNT ON/OFF) . ~| 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:330295 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
Right, and thanks for the response btw. The main trouble is that we could not afford the time to be checking through and changing all the possibly affected queries in our rather sprawling legacy application (getting less sprawled by the day) - the jTDC driver gives us the needed performance improvement without having to touch our code. It wouldn't seem right either, given that the SQL syntax was correct, to be editing all those queries to work with the MS driver. Dominic On 1 February 2010 14:27, Leigh _ cfsearch...@yahoo.com wrote: It appears to be a limitation of the M$ jdbc driver. I do not know that it is a limitation of the driver. From everything I have read, I have always felt it was due to CF's handling of multiple statements within cfquery, and the whole one query/resultset limitation. Though I prefer procedures, I have definitely used multiple statements within cfquery + the MS driver in the past (usually with SET NOCOUNT ON/OFF) . ~| 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:330296 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
affected queries in our rather sprawling legacy application (getting less sprawled by the day) I hear you. For a legacy application, it is probably not worth the trouble to make all those changes. You may also want to check for multiple queries with @@IDENTITY and SCOPE_IDENTITY(). I remember seeing very different (and sometimes bizarre) results with CF8 across the different drivers. -Leigh ~| 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:330298 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
I recently switched to using the latest Microsoft JDBC Driver for MSSQL 2005 and immediately saw a performance increase (over using the standard driver w/ ColdFusion 8.01). However, all our queries that have multiple statements are failing so I've had to switch back. e.g. I know with MySQL, you can specify multiple queries right in your DSN setting. http://www.bennadel.com/blog/1542-MySQL-3-4-com-mysql-jdbc-Driver-And-allowMultiQueries-true.htm I don't know if there's an equivalent for the SQL Server driver. Will ~| 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:330299 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
Yup, spot on Leigh, they're not working right with the jDTS driver either :O Wrapping the whole statement with BEGIN .. END, or indeed SET NOCOUNT ON/OFF does make them work though. Looks like I'm going to have to bite the bullet and get coding; the performance issues with the default CF driver are too big to ignore, unfortunately. Thanks Dominic On 1 February 2010 15:03, Leigh cfsearch...@yahoo.com wrote: You may also want to check for multiple queries with @@IDENTITY and SCOPE_IDENTITY(). I remember seeing very different (and sometimes bizarre) results with CF8 across the different drivers. ~| 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:330304 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
Yup, spot on Leigh, they're not working right with the jDTS driver either :O Ugh. Is this with CF8 or CF9? A lot of the weirdness I saw in CF8 had to do with INSERT/VALUES statements in particular, due to the generated keys feature of the result attribute. http://www.mischefamily.com/nathan/index.cfm/2008/1/28/Problems-with-CF-8s-Generated-Keys-Feature I am not sure if anything has changed in CF9. But if you are modifying the code anyway, you might want to experiment a little. Try using the result attribute, cfqueryparam, etcetera and see what works best and is the cleanest code. ~| 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:330312 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
I'm just curious: don't you need to separate the statements with semicolons? I've never seen multiple SQL statements without semicolons before. As others have noted tho', this behavior is driver-specific anyway... Sean On Mon, Feb 1, 2010 at 4:29 AM, Dominic Watson watson.domi...@googlemail.com wrote: I recently switched to using the latest Microsoft JDBC Driver for MSSQL 2005 and immediately saw a performance increase (over using the standard driver w/ ColdFusion 8.01). However, all our queries that have multiple statements are failing so I've had to switch back. e.g. cfquery datasource=my-datasource name=my-query UPDATE foo SET bar = 1 SELECT bar FROM foo /cfquery With the standard built in driver I can reference my-query.bar, with the MS JDBC Driver I cannot (get a 'bar is undefined in my-query' error). Has anyone experienced this? Is there a way to enable multiple statements using the jdbc driver? I've checked this page out and couldn't see anything that fit the bill: http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=datasources_ADV_MJS_11.html TIA Dominic ~| 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:330313 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft JDBC Driver: Multiple SQL statements
I'm just curious: don't you need to separate the statements with semicolons? I do not think it is technically required with SQL Server. Though it may be in the ANSI specs. In which case, it is probably not a bad idea to use them. ~| 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:330314 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
