Re: MySQL question
select day(`timestamp`) as dayOfMonth, count(`timestamp`) as searchesPerDay from mytable where `timestamp` between '2012-05-01 00:00:00' and '2012-05-31 23:59:59') group by dayOfMonth order by dayOfMonth asc iirc, timestamp is a reserved word in MySQL - hence it's enclosed in ticks above (note that ticks are NOT single quotes). MySQL also allows you to use column aliases in group by and order by statements. On Tue, May 8, 2012 at 3:57 AM, Che Vilnonis ch...@asitv.com wrote: Should be a simple one for a mysql guru. What is the mysql equivalent of the following? select datePart(dd,timestamp) as dayOfMonth, count(timestamp) as searchesPerDay from mytable where (timestamp between '5/1/2012 00:00:00' and '5/31/2012 23:59:59') group by datePart(dd,timestamp) order by datePart(dd,timestamp) asc Thanks, Che ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:351035 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL date problem
Nevermind. Stupid mistake. I was looping over a number of records and it turns out that one of the records had a null value set which was what caused the error. Had nothing to do with MySQL dates at all. Ugh. I have an app that I have created using CF 9.01, MySQL 5.1.41 and ORM. One of the properties of my object is a start date. Everything inserts and updates to the MySQL database without a problem. This issue that I am having is when I try to perform any CF date functions on the stored date. If I try to do a datecompare or datediff function on the start date, I get a Date value passed to date function DateCompare is unspecified or invalid error message. However, if I try either isValid(date, Deal.getStartDate()) or isDate(Deal.getStartDate()), both return true. Any thoughts? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349601 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL date problem
can you perform any date functions on it at all ? Try running it through CreateDate() or CreateODBCDate() first and see if that helps On Sun, Jan 22, 2012 at 8:48 PM, Dean Lawrence dean...@gmail.com wrote: I have an app that I have created using CF 9.01, MySQL 5.1.41 and ORM. One of the properties of my object is a start date. Everything inserts and updates to the MySQL database without a problem. This issue that I am having is when I try to perform any CF date functions on the stored date. If I try to do a datecompare or datediff function on the start date, I get a Date value passed to date function DateCompare is unspecified or invalid error message. However, if I try either isValid(date,Deal.getStartDate()) or isDate(Deal.getStartDate()), both return true. Any thoughts? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:349602 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL install
You should real the notes on the various install modes/settings to make sure you install it configured with the best options for your needs, it isn't really as straight forward as MSSQL. I would suggest you block remote access to mysql in your firewall and use phpmyadmin to access it or just login to the server and use the tools from there. To setup a DataSource, in the dropdown list for database type, instead of selecting MSSQL or Access you choose mysql 4/5 On Mon, May 16, 2011 at 6:02 PM, Ken Hammond khamm...@saleminc.com wrote: As suggested by Raymond, I am going to install MySQL server. Is there anything special I need to know security wise? It's going onto a win2k8 box. How do I setup a datasource in CFAdmin for MySQL? I've only ever dealt with MS SQL, Access, Excel, ODBC connections, etc... ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344551 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL install
Not sure I'd recommend phpmyadmin - wouldn't that also imply installing PHP? MySQL has free tools for management - why not just use them? (And yeah - they kinda suck in terms of UX but you can deal with it. :) On Mon, May 16, 2011 at 12:34 PM, Russ Michaels r...@michaels.me.uk wrote: You should real the notes on the various install modes/settings to make sure you install it configured with the best options for your needs, it isn't really as straight forward as MSSQL. I would suggest you block remote access to mysql in your firewall and use phpmyadmin to access it or just login to the server and use the tools from there. To setup a DataSource, in the dropdown list for database type, instead of selecting MSSQL or Access you choose mysql 4/5 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344552 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL install
that would depend if you want to open up remote access. If you do then at least restrict it by IP or use a VPN. MySQL ports are regularly scanned for and attacked if found open. On Mon, May 16, 2011 at 8:01 PM, Raymond Camden rcam...@gmail.com wrote: Not sure I'd recommend phpmyadmin - wouldn't that also imply installing PHP? MySQL has free tools for management - why not just use them? (And yeah - they kinda suck in terms of UX but you can deal with it. :) On Mon, May 16, 2011 at 12:34 PM, Russ Michaels r...@michaels.me.uk wrote: You should real the notes on the various install modes/settings to make sure you install it configured with the best options for your needs, it isn't really as straight forward as MSSQL. I would suggest you block remote access to mysql in your firewall and use phpmyadmin to access it or just login to the server and use the tools from there. To setup a DataSource, in the dropdown list for database type, instead of selecting MSSQL or Access you choose mysql 4/5 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344560 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL install
I have no need for remote access. I RDP into the machine over VPN already. -Original Message- From: Russ Michaels r...@michaels.me.uk To: cf-talk cf-talk@houseoffusion.com Date: Mon, 16 May 2011 22:17:39 +0100 Subject: Re: MySQL install that would depend if you want to open up remote access. If you do then at least restrict it by IP or use a VPN. MySQL ports are regularly scanned for and attacked if found open. On Mon, May 16, 2011 at 8:01 PM, Raymond Camden rcam...@gmail.com wrote: Not sure I'd recommend phpmyadmin - wouldn't that also imply installing PHP? MySQL has free tools for management - why not just use them? (And yeah - they kinda suck in terms of UX but you can deal with it. :) On Mon, May 16, 2011 at 12:34 PM, Russ Michaels r...@michaels.me.uk wrote: You should real the notes on the various install modes/settings to make sure you install it configured with the best options for your needs, it isn't really as straight forward as MSSQL. I would suggest you block remote access to mysql in your firewall and use phpmyadmin to access it or just login to the server and use the tools from there. To setup a DataSource, in the dropdown list for database type, instead of selecting MSSQL or Access you choose mysql 4/5 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344563 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL Conversion
If you have no gaps in the autoid field in Access, you can create am additional field in MySQL for your Access ID, and order the Access table in ID order, then import it and the MYSQL autonumber field should match the Access ID field. if that is the case, you can then delete the extra field. If you have gaps and id is an essential data element that has relational children, it's a much more complex problem. If that is the case, let me know and I'll try to explain how to do it. On Tue, Apr 19, 2011 at 10:46 PM, Scott Williams myscottwilli...@yahoo.com wrote: Oy -- I shouldn't have started this one until the weekend. I'm converting from MS Access to MySQL on one of my ColdFusion sites, and having problems converting my Autonumbered Access tables to MySQL format. They just won't import at all. If I remove the primary key and change the format to Integer instead of Autonumber, the tables import. The real problem is when I try to add data to these tables later. It will take one additional record (numbering it 0), then won't take anymore because then there would be two 0s. How can I get a MySQL table to autonumber like Access? Scott ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343862 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL Conversion
how are you migrating your db? mysql used to have a migration toolkit as part of mysql gui tools bundle which handled access-mysql migration very well. the gui tools have now been replaced by mysql workbench, but you can still download them from mysql website. gui tools: http://dev.mysql.com/downloads/gui-tools/5.0.html workbench: http://dev.mysql.com/downloads/workbench/5.2.html mysql equivalent of access's autonumber type is an Integer column with auto_increment attribute. http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html (bottom of page for auto_increment attr info) you can also set/change the next auto_increment value to use on mysql column: ALTER TABLE t2 AUTO_INCREMENT = value (from http://dev.mysql.com/doc/refman/5.5/en/alter-table.html) all this and much much more is covered in mysql reference manual on mysql website: http://dev.mysql.com/doc/ another point to keep in mind: if you cfqueries used any access-specific syntax you will have to re-write them to use proper sql to work with mysql db. Azadi On 20/04/2011 13:46 , Scott Williams wrote: Oy -- I shouldn't have started this one until the weekend. I'm converting from MS Access to MySQL on one of my ColdFusion sites, and having problems converting my Autonumbered Access tables to MySQL format. They just won't import at all. If I remove the primary key and change the format to Integer instead of Autonumber, the tables import. The real problem is when I try to add data to these tables later. It will take one additional record (numbering it 0), then won't take anymore because then there would be two 0s. How can I get a MySQL table to autonumber like Access? Scott ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343863 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL Conversion
You would probably find it easier to upgrade to MSSQL, which is better anyway in so many ways than MySQL esp if you are on windows (speed, performance, reliability, scheduled backups, automated maintenance, no table corrupting issues, no horrid SQL dumps to name a few). Presumably if your using Access you must be on a windows host, so MSSQL is probably available to you. If you are choosing MySQL only because it is free, as many do, then remember that MSSQL Express is also FREE. You have an upgrade tool built in to MSACCESS, and Microsoft also provide free migration tools as well. just something to consider. On Wed, Apr 20, 2011 at 7:22 AM, Azadi Saryev azadi.sar...@gmail.comwrote: how are you migrating your db? mysql used to have a migration toolkit as part of mysql gui tools bundle which handled access-mysql migration very well. the gui tools have now been replaced by mysql workbench, but you can still download them from mysql website. gui tools: http://dev.mysql.com/downloads/gui-tools/5.0.html workbench: http://dev.mysql.com/downloads/workbench/5.2.html mysql equivalent of access's autonumber type is an Integer column with auto_increment attribute. http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html (bottom of page for auto_increment attr info) you can also set/change the next auto_increment value to use on mysql column: ALTER TABLE t2 AUTO_INCREMENT = value (from http://dev.mysql.com/doc/refman/5.5/en/alter-table.html) all this and much much more is covered in mysql reference manual on mysql website: http://dev.mysql.com/doc/ another point to keep in mind: if you cfqueries used any access-specific syntax you will have to re-write them to use proper sql to work with mysql db. Azadi On 20/04/2011 13:46 , Scott Williams wrote: Oy -- I shouldn't have started this one until the weekend. I'm converting from MS Access to MySQL on one of my ColdFusion sites, and having problems converting my Autonumbered Access tables to MySQL format. They just won't import at all. If I remove the primary key and change the format to Integer instead of Autonumber, the tables import. The real problem is when I try to add data to these tables later. It will take one additional record (numbering it 0), then won't take anymore because then there would be two 0s. How can I get a MySQL table to autonumber like Access? Scott ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343867 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: MySQL Conversion
then remember that MSSQL Express is also FREE limited to 4GB in size. if you definitely want to end up with MySQL then it would be easier to use the built in wizard to upsize from MSAccess to MSSQL then (if you still want to) ... convert from MSSQL to MySQL i would bother trying to use the MySQL Migration Tool Kit. ive never had much luck with this. I've converted many DBs from MS to My. So many that i bought some third party software to do it for me. takes about 30 seconds. Let me know if i can help out. zac Regards, Zac Wingfield Operations Manager For and on behalf of, Allied Facilities.com Limited 01903 723999 7 Wickham Business Centre Harwood Road Littlehampton West Sussex BN17 7AU For more information about Allied please visit: http://www.allied-facilities.com -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 20 April 2011 11:20 To: cf-talk Subject: Re: MySQL Conversion You would probably find it easier to upgrade to MSSQL, which is better anyway in so many ways than MySQL esp if you are on windows (speed, performance, reliability, scheduled backups, automated maintenance, no table corrupting issues, no horrid SQL dumps to name a few). Presumably if your using Access you must be on a windows host, so MSSQL is probably available to you. If you are choosing MySQL only because it is free, as many do, then remember that MSSQL Express is also FREE. You have an upgrade tool built in to MSACCESS, and Microsoft also provide free migration tools as well. just something to consider. On Wed, Apr 20, 2011 at 7:22 AM, Azadi Saryev azadi.sar...@gmail.comwrote: how are you migrating your db? mysql used to have a migration toolkit as part of mysql gui tools bundle which handled access-mysql migration very well. the gui tools have now been replaced by mysql workbench, but you can still download them from mysql website. gui tools: http://dev.mysql.com/downloads/gui-tools/5.0.html workbench: http://dev.mysql.com/downloads/workbench/5.2.html mysql equivalent of access's autonumber type is an Integer column with auto_increment attribute. http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html (bottom of page for auto_increment attr info) you can also set/change the next auto_increment value to use on mysql column: ALTER TABLE t2 AUTO_INCREMENT = value (from http://dev.mysql.com/doc/refman/5.5/en/alter-table.html) all this and much much more is covered in mysql reference manual on mysql website: http://dev.mysql.com/doc/ another point to keep in mind: if you cfqueries used any access-specific syntax you will have to re-write them to use proper sql to work with mysql db. Azadi On 20/04/2011 13:46 , Scott Williams wrote: Oy -- I shouldn't have started this one until the weekend. I'm converting from MS Access to MySQL on one of my ColdFusion sites, and having problems converting my Autonumbered Access tables to MySQL format. They just won't import at all. If I remove the primary key and change the format to Integer instead of Autonumber, the tables import. The real problem is when I try to add data to these tables later. It will take one additional record (numbering it 0), then won't take anymore because then there would be two 0s. How can I get a MySQL table to autonumber like Access? Scott ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343868 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL Conversion
Yes mysql has a 4gb limit, for MSSQL there is also a limit of 10GB, please see this url http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx which is more than enough for most people. In fact we have been hosting SQL Server express since 2005, which did have a 4GB limit also, and no-one EVER came close to exceeding this. In fact the average databases are only a few hundred mb in size, so it really is not a problem. MySQL and MySQL also has limitations which require you to purchase the enterprise editions. But as with MSSQL express, these limitations do not affect your average user with a website and a database. If you need an enterprise solution, expect to pay for it. The very fact that you are upgrading from MSACCESS should really tell you that your database requirements are quite simple, so the limitations are unlikely to affects you. What I will say is that we have been hosting MSSQL for 10+ years (since MSSQL 2000) without any major issues and is pretty trouble free. We have been hosting mysql since v4 and some customers regularly have issues with tables getting corrupted (common issue), performance issues where 1 database can cause mysql to consume 100% cpu, row locking issues etc and certainly requires a lot more support, tweaking and trouble shooting. Clearly I have a preference for MSSQL, but issues like the above also lead me to generally recommend MSSQL over MySQL for anyone on windows, especially when both are free. You also need to make sure you understand the difference with the MySQL storage engines (MyISAM, INNODB) and use the appropriate one, otherwise this can cause many performance issues as well. You don't have any such issue with MSSQL, there are no different storage engines to learn, MSSQL pretty much just handles everything for you. The GUI tools that come with MSSQL also give you a lot more control out of the box, most people resort to using PHPMyAdmin instead for mysql, which is a web based tool. There are other free mysql GUI's. but in my experience to get the same level of features or control you really need to buy a copy of a commercial product like Navicat or similar. I think the overall biggest thing that makes a difference for me is backups. With MSSQL it is easy, setup a scheduled maintenance plan via the GUI to backup your database and also perform various tasks, such as optimise imdexes, shrink database, and much more. The backups whether done manually or scheduled are created very quick and are a single file which can be restored in seconds. In case of disaster a raw MSSQL database can also be copied to another server and attached. With MySQL (up to v5) you have to create a DUMP file of the entire database as SQL statements which re-insert the data. It is pretty clunky to say the least and can take hours to create or restore a dump if you have a large database. However you can at least do it via PHPMyAdmin or remote tools and save the dump locally. This is however the only way you can restore a MySQL database, if you backup the raw files, you cannot do much with them without a lot of hassle. On Wed, Apr 20, 2011 at 11:26 AM, Zac Wingfield z...@allied-facilities.comwrote: then remember that MSSQL Express is also FREE limited to 4GB in size. if you definitely want to end up with MySQL then it would be easier to use the built in wizard to upsize from MSAccess to MSSQL then (if you still want to) ... convert from MSSQL to MySQL i would bother trying to use the MySQL Migration Tool Kit. ive never had much luck with this. I've converted many DBs from MS to My. So many that i bought some third party software to do it for me. takes about 30 seconds. Let me know if i can help out. zac Regards, Zac Wingfield Operations Manager For and on behalf of, Allied Facilities.com Limited 01903 723999 7 Wickham Business Centre Harwood Road Littlehampton West Sussex BN17 7AU For more information about Allied please visit: http://www.allied-facilities.com -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 20 April 2011 11:20 To: cf-talk Subject: Re: MySQL Conversion You would probably find it easier to upgrade to MSSQL, which is better anyway in so many ways than MySQL esp if you are on windows (speed, performance, reliability, scheduled backups, automated maintenance, no table corrupting issues, no horrid SQL dumps to name a few). Presumably if your using Access you must be on a windows host, so MSSQL is probably available to you. If you are choosing MySQL only because it is free, as many do, then remember that MSSQL Express is also FREE. You have an upgrade tool built in to MSACCESS, and Microsoft also provide free migration tools as well. just something to consider. On Wed, Apr 20, 2011 at 7:22 AM, Azadi Saryev azadi.sar...@gmail.comwrote: how are you migrating your db? mysql used to have
Re: mySQL Error
I tried DECIMAL(2,1) and still get the same thing. - Original Message - From: Azadi Saryev To: cf-talk Sent: Monday, December 27, 2010 9:25 PM Subject: Re: mySQL Error DECIMAL(4,4) is a bit strange - the (4,4) part means your column can store a value with 4 digits, 4 of which are decimal points - effectively means you can only store a range of values between -. and . in your column. the first 4 in (4,4) denotes total number of digits allowed - integers and decimals - not the length of integer part of column value. Azadi On 28/12/2010 08:46 , Monique Boea wrote: I have created a table that needed decimal columns. Here is my script: CREATE TABLE `goals` ( `enrollments` DECIMAL(4,4) DEFAULT NULL , `followups` DECIMAL(4,4) DEFAULT NULL , `outcomes` DECIMAL(4,4) DEFAULT NULL , `productivityPoints` DECIMAL(4,4) DEFAULT NULL , `hoursWorked` DECIMAL(4,4) DEFAULT NULL , `extrabreakACW` DECIMAL(4,4) DEFAULT NULL , `productiveHours` DECIMAL(4,4) DEFAULT NULL , `totalCalls` DECIMAL(4,4) DEFAULT NULL , `callsPerhour` DECIMAL(4,4) DEFAULT NULL , `datelastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP, `lastUpdatedBy` varchar(200)); When I try to insert a record I get the following error: 1264: Out of range value adjusted for column 'enrollments' at row 1i've googled the error and most resources say that the occurs when INSERT query makes try to insert an empty value into a NOT NULL field, but I know this is not the case here.Any suggestions?Thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340293 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL Error
I used UNSIGNED DECIMAL (10,2) as suggested by this page http://forums.digitalpoint.com/showthread.php?t=274332 (see very last comment) and it worked! - Original Message - From: Azadi Saryev To: cf-talk Sent: Monday, December 27, 2010 9:25 PM Subject: Re: mySQL Error DECIMAL(4,4) is a bit strange - the (4,4) part means your column can store a value with 4 digits, 4 of which are decimal points - effectively means you can only store a range of values between -. and . in your column. the first 4 in (4,4) denotes total number of digits allowed - integers and decimals - not the length of integer part of column value. Azadi On 28/12/2010 08:46 , Monique Boea wrote: I have created a table that needed decimal columns. Here is my script: CREATE TABLE `goals` ( `enrollments` DECIMAL(4,4) DEFAULT NULL , `followups` DECIMAL(4,4) DEFAULT NULL , `outcomes` DECIMAL(4,4) DEFAULT NULL , `productivityPoints` DECIMAL(4,4) DEFAULT NULL , `hoursWorked` DECIMAL(4,4) DEFAULT NULL , `extrabreakACW` DECIMAL(4,4) DEFAULT NULL , `productiveHours` DECIMAL(4,4) DEFAULT NULL , `totalCalls` DECIMAL(4,4) DEFAULT NULL , `callsPerhour` DECIMAL(4,4) DEFAULT NULL , `datelastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP, `lastUpdatedBy` varchar(200)); When I try to insert a record I get the following error: 1264: Out of range value adjusted for column 'enrollments' at row 1i've googled the error and most resources say that the occurs when INSERT query makes try to insert an empty value into a NOT NULL field, but I know this is not the case here.Any suggestions?Thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340294 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL Error
enrollments - 33 followup - 25 outcomes - 21.3 None of these values would work since they are outside the range of -. to +.. If you don't need four decimal places use decimal(4,1) which would work for all the above values. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340295 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL Error
This is even smaller so it makes sense it wouldn't work. This would only allow -9.9 to +9.9. The psuedo explanation for DECIMAL(2,1) is: Create a decimal field, that stores two digits [the first value] with an accuracy of one decimal place [the second value] On Tue, Dec 28, 2010 at 5:33 AM, Monique Boea moniqueb...@gmail.com wrote: I tried DECIMAL(2,1) and still get the same thing. - Original Message - From: Azadi Saryev To: cf-talk Sent: Monday, December 27, 2010 9:25 PM Subject: Re: mySQL Error DECIMAL(4,4) is a bit strange - the (4,4) part means your column can store a value with 4 digits, 4 of which are decimal points - effectively means you can only store a range of values between -. and . in your column. the first 4 in (4,4) denotes total number of digits allowed - integers and decimals - not the length of integer part of column value. Azadi On 28/12/2010 08:46 , Monique Boea wrote: I have created a table that needed decimal columns. Here is my script: CREATE TABLE `goals` ( `enrollments` DECIMAL(4,4) DEFAULT NULL , `followups` DECIMAL(4,4) DEFAULT NULL , `outcomes` DECIMAL(4,4) DEFAULT NULL , `productivityPoints` DECIMAL(4,4) DEFAULT NULL , `hoursWorked` DECIMAL(4,4) DEFAULT NULL , `extrabreakACW` DECIMAL(4,4) DEFAULT NULL , `productiveHours` DECIMAL(4,4) DEFAULT NULL , `totalCalls` DECIMAL(4,4) DEFAULT NULL , `callsPerhour` DECIMAL(4,4) DEFAULT NULL , `datelastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP, `lastUpdatedBy` varchar(200)); When I try to insert a record I get the following error: 1264: Out of range value adjusted for column 'enrollments' at row 1i've googled the error and most resources say that the occurs when INSERT query makes try to insert an empty value into a NOT NULL field, but I know this is not the case here.Any suggestions?Thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340296 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: mySQL Error
When I try to insert a record I get the following error: What's the insert query look like? -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340288 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL Error
I am inserting it on the back end. - Original Message - From: Justin Scott To: cf-talk Sent: Monday, December 27, 2010 8:09 PM Subject: RE: mySQL Error When I try to insert a record I get the following error: What's the insert query look like? -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340289 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: mySQL Error
I am inserting it on the back end. Ok, what is the value you're trying to insert? The more info you can volunteer up front, the easier it will be to help you. :) -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340290 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL Error
DECIMAL(4,4) is a bit strange - the (4,4) part means your column can store a value with 4 digits, 4 of which are decimal points - effectively means you can only store a range of values between -. and . in your column. the first 4 in (4,4) denotes total number of digits allowed - integers and decimals - not the length of integer part of column value. Azadi On 28/12/2010 08:46 , Monique Boea wrote: I have created a table that needed decimal columns. Here is my script: CREATE TABLE `goals` ( `enrollments` DECIMAL(4,4) DEFAULT NULL , `followups` DECIMAL(4,4) DEFAULT NULL , `outcomes` DECIMAL(4,4) DEFAULT NULL , `productivityPoints` DECIMAL(4,4) DEFAULT NULL , `hoursWorked` DECIMAL(4,4) DEFAULT NULL , `extrabreakACW` DECIMAL(4,4) DEFAULT NULL , `productiveHours` DECIMAL(4,4) DEFAULT NULL , `totalCalls` DECIMAL(4,4) DEFAULT NULL , `callsPerhour` DECIMAL(4,4) DEFAULT NULL , `datelastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP, `lastUpdatedBy` varchar(200)); When I try to insert a record I get the following error: 1264: Out of range value adjusted for column 'enrollments' at row 1i've googled the error and most resources say that the occurs when INSERT query makes try to insert an empty value into a NOT NULL field, but I know this is not the case here.Any suggestions?Thanks ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340291 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL Error
Oh sorry :) just simple number and decimals: `enrollments` DECIMAL(4,4) DEFAULT NULL , `followups` DECIMAL(4,4) DEFAULT NULL , `outcomes` DECIMAL(4,4) DEFAULT NULL , `productivityPoints` DECIMAL(4,4) DEFAULT NULL , `hoursWorked` DECIMAL(4,4) DEFAULT NULL , `extrabreakACW` DECIMAL(4,4) DEFAULT NULL , `productiveHours` DECIMAL(4,4) DEFAULT NULL , `totalCalls` DECIMAL(4,4) DEFAULT NULL , `callsPerhour` DECIMAL(4,4) DEFAULT NULL , `datelastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP, `lastUpdatedBy` varchar(200)); here are the values I was inserting enrollments - 33 followup - 25 outcomes - 21.3 and so on. Some were decimals some whole numbers. - Original Message - From: Justin Scott To: cf-talk Sent: Monday, December 27, 2010 8:33 PM Subject: RE: mySQL Error I am inserting it on the back end. Ok, what is the value you're trying to insert? The more info you can volunteer up front, the easier it will be to help you. :) -Justin ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340292 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL administration
Well you can of course us the free mysql admin tools provided by mysql, or there arenumerous alternatives. Navicat is very good Sqlyog is very popular but quite basic Numerous others if you google the subject Russ On 10 Dec 2010 03:53, Rob Voyle robvo...@voyle.com wrote: Hi Folks I have mySQL running on my website and want to run it on Windows Vista desktop computer to pilot and test web pages. Any recommendations on a way to create and administer databases without having to use the command line. On my website I have phpMyAdmin. Can that be run on a local computer? Rob Robert J. Voyle, Psy.D. Director, Clergy Leadership Institute For Coaching and Training in Appreciative Inquiry Author: Restoring Hope: Appreciative Strategies to Resolve Grief and Resentment http://www.appreciativeway.com/ 503-647-2378 or 503-647-2382 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340113 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL administration
just google any of the following: Heidisql, mySQLFront Best Regrads, Chuka I.W. Anene Chief Software Eng./CEO Quorium Solutions www.quorium.org +2347032696113 +23418812777 +2347029609185 From: Rob Voyle robvo...@voyle.com To: cf-talk cf-talk@houseoffusion.com Sent: Fri, December 10, 2010 3:52:37 AM Subject: mySQL administration Hi Folks I have mySQL running on my website and want to run it on Windows Vista desktop computer to pilot and test web pages. Any recommendations on a way to create and administer databases without having to use the command line. On my website I have phpMyAdmin. Can that be run on a local computer? Rob Robert J. Voyle, Psy.D. Director, Clergy Leadership Institute For Coaching and Training in Appreciative Inquiry Author: Restoring Hope: Appreciative Strategies to Resolve Grief and Resentment http://www.appreciativeway.com/ 503-647-2378 or 503-647-2382 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339964 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL administration
+1 for Navicat. Well worth it. That reminds me - I bought a Windows copy of Navicat for MySQL and then moved back to Mac... It's for the MySQL (Windows) Non-Commercial Edition version 9 so if you decide to purchase drop me a line. Stefan On 10 Dec 2010, at 04:27, Azadi Saryev wrote: SQLYog (http://www.webyog.com/en/) and Navicat (http://navicat.com/) both are great MySQL administration tools and both have very feature-rich free editions. MySQL GUI Tools bundle (http://dev.mysql.com/downloads/gui-tools/5.0.html) is another alternative. Azadi ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339965 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL administration
+1 for SqlYog and Heidi SQL SqlYog has a free community version BTW: http://code.google.com/p/sqlyog/ I am also partial to Toad for MySQL. It is free and full featured. http://www.quest.com/toad-for-mysql/ EMS SQL Manager for MySQL is worth mentioning. And I keep the Lite version on my thumb drive http://www.sqlmanager.net/en/products/mysql/manager HTH G! On Thu, Dec 9, 2010 at 10:52 PM, Rob Voyle robvo...@voyle.com wrote: Hi Folks I have mySQL running on my website and want to run it on Windows Vista desktop computer to pilot and test web pages. Any recommendations on a way to create and administer databases without having to use the command line. On my website I have phpMyAdmin. Can that be run on a local computer? Rob Robert J. Voyle, Psy.D. Director, Clergy Leadership Institute For Coaching and Training in Appreciative Inquiry Author: Restoring Hope: Appreciative Strategies to Resolve Grief and Resentment http://www.appreciativeway.com/ 503-647-2378 or 503-647-2382 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339972 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL administration
mySQL Workbench is good for a freebie. mySQL Yog is good but costs a few bucks. On Thu, Dec 9, 2010 at 10:52 PM, Rob Voyle robvo...@voyle.com wrote: Hi Folks I have mySQL running on my website and want to run it on Windows Vista desktop computer to pilot and test web pages. Any recommendations on a way to create and administer databases without having to use the command line. On my website I have phpMyAdmin. Can that be run on a local computer? Rob Robert J. Voyle, Psy.D. Director, Clergy Leadership Institute For Coaching and Training in Appreciative Inquiry Author: Restoring Hope: Appreciative Strategies to Resolve Grief and Resentment http://www.appreciativeway.com/ 503-647-2378 or 503-647-2382 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339961 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL administration
You can run it on Vista if you want to install PHP. I would suggest installing the Mysql GUI tools which are available on the Mysql website. Sent with my Droid On Dec 9, 2010 8:53 PM, Rob Voyle robvo...@voyle.com wrote: Hi Folks I have mySQL running on my website and want to run it on Windows Vista desktop computer to pilot and test web pages. Any recommendations on a way to create and administer databases without having to use the command line. On my website I have phpMyAdmin. Can that be run on a local computer? Rob Robert J. Voyle, Psy.D. Director, Clergy Leadership Institute For Coaching and Training in Appreciative Inquiry Author: Restoring Hope: Appreciative Strategies to Resolve Grief and Resentment http://www.appreciativeway.com/ 503-647-2378 or 503-647-2382 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339962 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL administration
SQLYog (http://www.webyog.com/en/) and Navicat (http://navicat.com/) both are great MySQL administration tools and both have very feature-rich free editions. MySQL GUI Tools bundle (http://dev.mysql.com/downloads/gui-tools/5.0.html) is another alternative. Azadi On 10/12/2010 12:07 , Michael Grant wrote: mySQL Workbench is good for a freebie. mySQL Yog is good but costs a few bucks. On Thu, Dec 9, 2010 at 10:52 PM, Rob Voylerobvo...@voyle.com wrote: Hi Folks I have mySQL running on my website and want to run it on Windows Vista desktop computer to pilot and test web pages. Any recommendations on a way to create and administer databases without having to use the command line. On my website I have phpMyAdmin. Can that be run on a local computer? Rob Robert J. Voyle, Psy.D. Director, Clergy Leadership Institute For Coaching and Training in Appreciative Inquiry Author: Restoring Hope: Appreciative Strategies to Resolve Grief and Resentment http://www.appreciativeway.com/ 503-647-2378 or 503-647-2382 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339963 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL data types - possible db bloat with text type? yes or no?
*bump* No one has any insight into this? Please please please. On Thu, Sep 2, 2010 at 11:47 AM, Michael Grant mgr...@modus.bz wrote: Normally I use MSSQL but the shop I'm at uses mySQL. I've always built my db's so that the field best matches the data going into it. As an example if I was storing some text data that was max 1000 chars I would use varchar(1000) and not a blob type. I've always thought that this prevented bloating. However I've just been told something that contradicts this and I'm wondering what you experts say. Is using the TEXT datatype completely variable how long it it? So if I insert a single character into a tinytext, text, mediumtext or longtext field it will only take up that much room in the db? Is that correct? ~| 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:336808 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL data types - possible db bloat with text type? yes or no?
With MSSQL you are limited to about 8000 bytes per row and anything beyond this will be chopped, so you need to be careful how much data you are storing. Using BLOBS will overcome this as a pointer to the blog is the only thing stored in the row, and the blog is actually stored separately. Using datatypes of specific lengths does indeed only take up the space used, but other factors affect thr storage such as page sizes. This paging applies to mysql as well, but I have never looked too deeply into it as I find MySQL very flaky and poor in performance compared to MSSQL so only use it when I have to, such as when I am using PHP apps.. I think this site will help you though., here is a post specifically about page sizes, but check the rest of the site too. http://www.mysqlperformanceblog.com/2006/06/04/innodb-page-size/ You also need to make sure you are using the correct table type (MYISAM or INNODB) -- Russ Michaels www.cfmldeveloper.com - Supporting the CF community since 1999 FREE ColdFusion/Railo hosting for developers. blog: www.michaels.me.uk ~| 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:336809 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL data types - possible db bloat with text type? yes or no?
On Fri, Sep 3, 2010 at 12:04 PM, Michael Grant mgr...@modus.bz wrote: *bump* No one has any insight into this? Please please please. On Thu, Sep 2, 2010 at 11:47 AM, Michael Grant mgr...@modus.bz wrote: Normally I use MSSQL but the shop I'm at uses mySQL. I've always built my db's so that the field best matches the data going into it. As an example if I was storing some text data that was max 1000 chars I would use varchar(1000) and not a blob type. I've always thought that this prevented bloating. However I've just been told something that contradicts this and I'm wondering what you experts say. Is using the TEXT datatype completely variable how long it it? So if I insert a single character into a tinytext, text, mediumtext or longtext field it will only take up that much room in the db? Is that correct? It's not as simple as you think it is. I don't know the answer for sure but this is what I know. Assuming mySQL...How much space something takes is dependent on the type of engine you select. The common ones are innodb, myISAM, and the perconaDB. As far I remember the innodb takes more space because it has more features and can't be compressed. Looking at myISAM, which by the way is probably not the engine you want for any transaction based db, you would use one (1) more bytes over a varchar(1000). So if storing i piece of data as a varchar(1000) that was 299 cahr longs it would take 300 bytes it would take 301 bytes if you had typed is as a text. That is true up to 65536 bytes. then the storage requirements changes. recap: in your case if you were using myISAM the text type would use 1 more byte than the varchar(1000). Things like char encoding like UTF or latin will impact it as well. ~| 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:336810 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL data types - possible db bloat with text type? yes or no?
Thanks. Other than the 1 extra byte thing... if I'm using MyISAM and assumming I have a string that's 150 characters is there any advantage to using varchar(150) over say TEXT or any of the other text type fields? that's what I'm trying to get at. So is a table that's got 20 TEXT datatype fields going to be the same as a table that has 20 varchar(x) fields? ~| 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:336811 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL data types - possible db bloat with text type? yes or no?
On Fri, Sep 3, 2010 at 12:23 PM, Michael Grant mgr...@modus.bz wrote: Thanks. Other than the 1 extra byte thing... if I'm using MyISAM and assumming I have a string that's 150 characters is there any advantage to using varchar(150) over say TEXT or any of the other text type fields? that's what I'm trying to get at. So is a table that's got 20 TEXT datatype fields going to be the same as a table that has 20 varchar(x) fields? There is an advantage in using varchar over text and it is performance. Because text type is written in a different area of the memory that the row buffer there may be a slight performance hit because it needs to do a harddrive read/write. I guess like your does ## impact performance thread it depends on the data you are storing. But let me say that you are much more likely to see this performance hit. I also believe that varchar can be indexed while text can't. I'm a little hazy on remembering everything right now cause I moved to an Oracle shop. ~| 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:336812 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL data types - possible db bloat with text type? yes or no?
thank you sir. On Fri, Sep 3, 2010 at 12:34 PM, Won Lee won...@gmail.com wrote: On Fri, Sep 3, 2010 at 12:23 PM, Michael Grant mgr...@modus.bz wrote: Thanks. Other than the 1 extra byte thing... if I'm using MyISAM and assumming I have a string that's 150 characters is there any advantage to using varchar(150) over say TEXT or any of the other text type fields? that's what I'm trying to get at. So is a table that's got 20 TEXT datatype fields going to be the same as a table that has 20 varchar(x) fields? There is an advantage in using varchar over text and it is performance. Because text type is written in a different area of the memory that the row buffer there may be a slight performance hit because it needs to do a harddrive read/write. I guess like your does ## impact performance thread it depends on the data you are storing. But let me say that you are much more likely to see this performance hit. I also believe that varchar can be indexed while text can't. I'm a little hazy on remembering everything right now cause I moved to an Oracle shop. ~| 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:336813 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL, datetime, and ColdFusion date objects
Well, I guess that's the problem. I was certain there was a cf_sql_datetime (and pretty certain I've been using it for years :) ). I guess I'd expect CF to throw an error for an invalid sql type. Thanks! Scott On Mon, May 17, 2010 at 7:46 PM, Leigh cfsearch...@yahoo.com wrote: Well, a timestamp in mySQL is a bit different from a datetime, so it seems a little weird using that sql type in the param Oh .. that. The thing to remember is cfsqltypes are based on the generic java.sql.Types used in JDBC (for all databases). Not the names of the data types used by your database. Obviously the two are connected, and most of the names do correspond pretty closely. But there are a few exceptions. Timestamp is one of them. http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Types.html For whatever reason they chose not to have a type called DATETIME type, and instead used the name TIMESTAMP. I am not sure why they chose to break with convention there .. but they did. So Timestamp is the correct type to use for date/time columns. (If you want some real fun, try and figure out the correct types to use for the approximate numeric 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:333771 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL, datetime, and ColdFusion date objects
Well, I guess that's the problem. I was certain there was a cf_sql_datetime (and pretty certain I've been using it for years :) ). I guess I'd expect CF to throw an error for an invalid sql type. Yes, that was my expectation too. But I _think_ CF does not not validate the types. IIRC, it just uses the default type CHAR when an invalid cfsqltype is passed. -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:333785 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL, datetime, and ColdFusion date objects
That makes sense. It also explains why it was warning me of data truncation. Scott On Tue, May 18, 2010 at 10:35 AM, Leigh cfsearch...@yahoo.com wrote: Well, I guess that's the problem. I was certain there was a cf_sql_datetime (and pretty certain I've been using it for years :) ). I guess I'd expect CF to throw an error for an invalid sql type. Yes, that was my expectation too. But I _think_ CF does not not validate the types. IIRC, it just uses the default type CHAR when an invalid cfsqltype is passed. -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:333807 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL, datetime, and ColdFusion date objects
Well, it looks like I can leave the field as a datetime but just use
cf_sql_timetamp in the queryparams.
I'd still like to know if there's a better way, but it's a lot easier to
search and replace that than to change every field in the database.
Scott
On Mon, May 17, 2010 at 11:48 AM, Scott Brady dsbr...@gmail.com wrote:
I'm running into an issue I'm hoping others have seen.
We have a number of fields that need to be date times, and I'm getting this
type of error when trying to enter them into the database:
Data truncation: Incorrect datetime value: '{ts '2010-05-17 11:40:23'}' for
column 'modified' at row 1
If I change them to a time stamp (and set the sql type in the param to
cf_sql_timestamp) it works -- though, I'm not looking forward to changing
every single table that has these fields in them. If I just leave the field
alone and change it to cf_sql_date, it works (though, as expected, doesn't
put the time part of the date time in there).
Is there some weirdness with ColdFusion date time objects and mySQL
datetime fields? (this is ColdFusion 9 and mySQL 5)
I could go through and change all of the fields to timestamps (except for
things like birthdate, but that can be just date, since I believe
timestamp won't work for dates before 1970), but was hoping for an easier
option.
Thanks!
Scott
--
-
Scott Brady
http://www.scottbrady.net/
--
-
Scott Brady
http://www.scottbrady.net/
~|
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:333758
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL, datetime, and ColdFusion date objects
I'd still like to know if there's a better way I am not sure I understand the question. Using cf_sql_timestamp for a date/time column is perfectly valid. So better than what ..? ;) ~| 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:333764 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL, datetime, and ColdFusion date objects
I always use the following function when inserting dates/times into sql.
CreateODBCDateTime()
CreateODBCDate()
CreateODBCTime()
--
Shaun Webster
480 Broadway Suite 328, Saratoga Springs NY 12866
office: 518.632.4333 | mobile: 518.269.2132 | fax: 518.490.1236
sh...@webvalve.com | www.webvalve.com
--
This email, and any files transmitted with it, is confidential and intended
solely for the use of the individual or entity to whom it is addressed.
--
On May 17, 2010, at 1:56 PM, Scott Brady wrote:
Well, it looks like I can leave the field as a datetime but just use
cf_sql_timetamp in the queryparams.
I'd still like to know if there's a better way, but it's a lot easier to
search and replace that than to change every field in the database.
Scott
On Mon, May 17, 2010 at 11:48 AM, Scott Brady dsbr...@gmail.com wrote:
I'm running into an issue I'm hoping others have seen.
We have a number of fields that need to be date times, and I'm getting this
type of error when trying to enter them into the database:
Data truncation: Incorrect datetime value: '{ts '2010-05-17 11:40:23'}' for
column 'modified' at row 1
If I change them to a time stamp (and set the sql type in the param to
cf_sql_timestamp) it works -- though, I'm not looking forward to changing
every single table that has these fields in them. If I just leave the field
alone and change it to cf_sql_date, it works (though, as expected, doesn't
put the time part of the date time in there).
Is there some weirdness with ColdFusion date time objects and mySQL
datetime fields? (this is ColdFusion 9 and mySQL 5)
I could go through and change all of the fields to timestamps (except for
things like birthdate, but that can be just date, since I believe
timestamp won't work for dates before 1970), but was hoping for an easier
option.
Thanks!
Scott
--
-
Scott Brady
http://www.scottbrady.net/
--
-
Scott Brady
http://www.scottbrady.net/
~|
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:333767
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL, datetime, and ColdFusion date objects
CreateODBCDateTime() CreateODBCDate() .. I used to use those as well. But now prefer cfqueryparam since provides some additional benefits over CreateODBC...(), like the usage of bind variables. ~| 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:333768 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL, datetime, and ColdFusion date objects
On Mon, May 17, 2010 at 1:58 PM, Leigh cfsearch...@yahoo.com wrote: I'd still like to know if there's a better way I am not sure I understand the question. Using cf_sql_timestamp for a date/time column is perfectly valid. So better than what ..? ;) Well, a timestamp in mySQL is a bit different from a datetime, so it seems a little weird using that sql type in the param (plus, I used a code generator to generate by CFCs, which uses the field's datatype to determine what sql type to use -- I guess I can change that code to conditionally use timestamp for date times). I guess the real question is if I was doing something wrong for date time to not be working for some reason. (It turns out that the global search and replace in CF Eclipse on the Mac isn't working very well for me. At least regards to switching out cf_sql_datetime with cf_sql_timestamp -- I keep coming across instances it's missing.) Scott -- - Scott Brady http://www.scottbrady.net/ ~| 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:333769 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: mySQL, datetime, and ColdFusion date objects
Well, a timestamp in mySQL is a bit different from a datetime, so it seems a little weird using that sql type in the param Oh .. that. The thing to remember is cfsqltypes are based on the generic java.sql.Types used in JDBC (for all databases). Not the names of the data types used by your database. Obviously the two are connected, and most of the names do correspond pretty closely. But there are a few exceptions. Timestamp is one of them. http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Types.html For whatever reason they chose not to have a type called DATETIME type, and instead used the name TIMESTAMP. I am not sure why they chose to break with convention there .. but they did. So Timestamp is the correct type to use for date/time columns. (If you want some real fun, try and figure out the correct types to use for the approximate numeric 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:333770 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: MySQL TimeStamp error
Which month is the zero'th month? mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2009/12/21 Stefan Richter ste...@flashcomguru.com: Some of you may have encountered the following error with date/time fields in MySQL: Error Executing Database Query. Cannot convert value '-00-00 00:00:00' from column 7 to TIMESTAMP. This is usually resolved by adding the following to the advanced Datasource settings under 'Connection String' in CF Admin: noDatetimeStringSync=truezeroDateTimeBehavior=convertToNull My questions is: does anyone have a more elegant solution to this? Is there a way to avoid this error without having to remember to edit new datasources first? And should this error even be thrown - after all the value is not an invalid date/time value so why does CF have issues with it? Cheers Stefan ~| 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:329287 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL TimeStamp error
Some of you may have encountered the following error with date/time fields in MySQL: Error Executing Database Query. Cannot convert value '-00-00 00:00:00' from column 7 to TIMESTAMP. This is usually resolved by adding the following to the advanced Datasource settings under 'Connection String' in CF Admin: noDatetimeStringSync=truezeroDateTimeBehavior=convertToNull My questions is: does anyone have a more elegant solution to this? Is there a way to avoid this error without having to remember to edit new datasources first? And should this error even be thrown - after all the value is not an invalid date/time value so why does CF have issues with it? First, that is in fact an invalid date/time value. Valid date/time values resolve to an actual date/time. Second, the general approach to handling this sort of problem is to explicitly pass NULLs to the database if you don't have a corresponding actual value. So, if someone leaves a field blank, you send NULL instead of an empty string, etc. If you're using CFQUERYPARAM (as you certainly should be unless you're using stored procedures) you can use the NULL attribute with a Boolean expression that resolves to true if the field is empty: cfqueryparam ... null=yesNoFormat(not len(trim(form.datefield))) 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 information! ~| 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:329289 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL Limit
I will add that you can achieve the 101-200 by using the OFFSET keyword, eg: SELECT resourceId FROM resources LIMIT 100 OFFSET 100 -- Pete Freitag http://foundeo.com/ - ColdFusion Consulting Products http://petefreitag.com/ - My Blog http://hackmycf.com - Is your ColdFusion Server Secure? On Mon, Nov 16, 2009 at 7:56 PM, Barney Boisvert bboisv...@gmail.com wrote: select r.*, v.* from (select resourceId from resources limit 100) r inner join resourcesattributesvalues v on r.resourceId = v.resourceId cheers, barneyb On Mon, Nov 16, 2009 at 4:49 PM, Agha Mehdi aghaime...@gmail.com wrote: I have three tables tableA Rresources (resourceID) tableB Attributes (attributeID, attributeName) tableC ResourcesAttributesValues (ravID, resourceID, attributeID, value) I need to apply MySQL Limit clause to get paginated dataset from row 1 - 100/101-200/201-300 I'd like to run a single query to get 1-100... resources with all of their attributes and values. What is the most efficient way to do it? Thanks ~| 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:328475 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL Limit
select r.*, v.* from (select resourceId from resources limit 100) r inner join resourcesattributesvalues v on r.resourceId = v.resourceId cheers, barneyb On Mon, Nov 16, 2009 at 4:49 PM, Agha Mehdi aghaime...@gmail.com wrote: I have three tables tableA Rresources (resourceID) tableB Attributes (attributeID, attributeName) tableC ResourcesAttributesValues (ravID, resourceID, attributeID, value) I need to apply MySQL Limit clause to get paginated dataset from row 1 - 100/101-200/201-300 I'd like to run a single query to get 1-100... resources with all of their attributes and values. What is the most efficient way to do it? Thanks ~| 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:328448 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL Limit
Awesome. thanks On Mon, Nov 16, 2009 at 4:56 PM, Barney Boisvert bboisv...@gmail.comwrote: select r.*, v.* from (select resourceId from resources limit 100) r inner join resourcesattributesvalues v on r.resourceId = v.resourceId cheers, barneyb On Mon, Nov 16, 2009 at 4:49 PM, Agha Mehdi aghaime...@gmail.com wrote: I have three tables tableA Rresources (resourceID) tableB Attributes (attributeID, attributeName) tableC ResourcesAttributesValues (ravID, resourceID, attributeID, value) I need to apply MySQL Limit clause to get paginated dataset from row 1 - 100/101-200/201-300 I'd like to run a single query to get 1-100... resources with all of their attributes and values. What is the most efficient way to do it? Thanks ~| 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:328449 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: mySQL view coming up table not found?
It's OS X. The view was named properly, but I went back into the CFQUERY tag and found a field in the query that was not in the view. Fixing that fixed the error, though why that was causing it to say table not found I am not sure. ~| 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:326799 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL Auto Increment Field
The article did mention trouble coming from the database itself if ones insert caused a trigger to be fired. Then the @@identity could return the generated key from what ever the trigger created. Yes, and it does. Having had that unpleasant experience once, with an older application, convinced me to switch to using SCOPE_IDENTITY(). Granted, not everyone uses triggers, or ones that involve an identity column on another table, but better safe than sorry. -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:326786 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: mySQL view coming up table not found?
On Tue, Sep 29, 2009 at 1:01 PM, Rob Barthle r...@barthle.com wrote: I have a simple view that I created using Navicat and saved. When I try to call the view with CFQUERY though, I get the following error: Table 'bettybarthle_com.vListingsSale' doesn't exist Is MySQL on Windows or Linux? On Linux MySQL table/view names are case sensitive because they correspond to files on the file system - that's caught me out once or twice (or maybe a few more times :) -- Sean A Corfield -- (904) 302-SEAN Railo Technologies US -- http://getrailo.com/ An Architect's View -- http://corfield.org/ If you're not annoying somebody, you're not really alive. -- Margaret Atwood ~| 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:326788 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL Auto Increment Field
On Tue, Sep 29, 2009 at 12:22 PM, Azadi Saryev az...@sabai-dee.com wrote: if you are on CF8, use RESULT attribute of cfquery tag and then get GENERATED_KEY from that result: cfquery name=myquery datasource=... result=qResult INSERT ... /cfquery cfset newID = qResult.GENERATED_KEY As an aside, the generated key is placed into a different element of the result struct for each different database in CF8 (why??!?!) but that is fixed in CF9 which also adds GENERATEDKEY for *all* DB types so you can write portable code (assuming you stick to standard SQL of course and not TSQL - sorry Robert, couldn't resist! :) Railo recently implemented this same feature (both adding the various DB-specific generated key elements as well as the generic GENERATEDKEY element). I don't know whether Open BlueDragon supports this feature yet. Anyone? -- Sean A Corfield -- (904) 302-SEAN Railo Technologies US -- http://getrailo.com/ An Architect's View -- http://corfield.org/ If you're not annoying somebody, you're not really alive. -- Margaret Atwood ~| 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:326789 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL Auto Increment Field
Just out of curiosity Sean, do you know if the value is lazy loaded, or do you have to pay the cost of retrieving the value on every query (or at least introspecting to determine whether a value is available) regardless? I know you're closest to Railo, but I'm interested in both (all three?) platforms if you've got the info. cheers, barneyb On Wed, Sep 30, 2009 at 10:19 PM, Sean Corfield seancorfi...@gmail.com wrote: On Tue, Sep 29, 2009 at 12:22 PM, Azadi Saryev az...@sabai-dee.com wrote: if you are on CF8, use RESULT attribute of cfquery tag and then get GENERATED_KEY from that result: cfquery name=myquery datasource=... result=qResult INSERT ... /cfquery cfset newID = qResult.GENERATED_KEY As an aside, the generated key is placed into a different element of the result struct for each different database in CF8 (why??!?!) but that is fixed in CF9 which also adds GENERATEDKEY for *all* DB types so you can write portable code (assuming you stick to standard SQL of course and not TSQL - sorry Robert, couldn't resist! :) Railo recently implemented this same feature (both adding the various DB-specific generated key elements as well as the generic GENERATEDKEY element). I don't know whether Open BlueDragon supports this feature yet. Anyone? -- Sean A Corfield -- (904) 302-SEAN Railo Technologies US -- http://getrailo.com/ An Architect's View -- http://corfield.org/ -- Barney Boisvert bboisv...@gmail.com http://www.barneyb.com/ ~| 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:326791 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL Auto Increment Field
if you are on CF8, use RESULT attribute of cfquery tag and then get GENERATED_KEY from that result: cfquery name=myquery datasource=... result=qResult INSERT ... /cfquery cfset newID = qResult.GENERATED_KEY Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ On 30/09/2009 03:16, Agha Mehdi wrote: All, How do I get the ID value back in CF from MySQL after doing an insert with auto_inc data type in the table? Thanks for help Agha ~| 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:326748 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL Auto Increment Field
select last_insert_id() as id http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id cheers, barneyb On Tue, Sep 29, 2009 at 12:16 PM, Agha Mehdi aghaime...@gmail.com wrote: All, How do I get the ID value back in CF from MySQL after doing an insert with auto_inc data type in the table? Thanks for help Agha ~| 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:326749 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: MySQL Auto Increment Field
Nice Azadi, I didn't know you could do that. Will -Original Message- From: Azadi Saryev [mailto:az...@sabai-dee.com] Sent: 29 September 2009 20:23 To: cf-talk Subject: Re: MySQL Auto Increment Field if you are on CF8, use RESULT attribute of cfquery tag and then get GENERATED_KEY from that result: cfquery name=myquery datasource=... result=qResult INSERT ... /cfquery cfset newID = qResult.GENERATED_KEY Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ On 30/09/2009 03:16, Agha Mehdi wrote: All, How do I get the ID value back in CF from MySQL after doing an insert with auto_inc data type in the table? Thanks for help Agha ~| 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:326750 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL Auto Increment Field
Thanks Azadi and Barney. Which approach is better in performance? I'm assuming result.generated_key? Thanks again for help Agha On Sep 29, 2009, at 12:20 PM, Barney Boisvert bboisv...@gmail.com wrote: select last_insert_id() as id http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id cheers, barneyb On Tue, Sep 29, 2009 at 12:16 PM, Agha Mehdi aghaime...@gmail.com wrote: All, How do I get the ID value back in CF from MySQL after doing an insert with auto_inc data type in the table? Thanks for help Agha ~| 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:326751 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: MySQL Auto Increment Field
You can also do this using SQL. Sure this works with MS SQL, not 100% sure
about mySQL but it's standard SQL, so it should work.
cfquery name=putData datasource=#dsn#
SET NOCOUNT ON
INSERT INTO my_table (my_field1, my_field2)
VALUES ('#trim(value_field1)#', ('#trim(value_field2)#')
SELECT @@Identity AS newId
SET NOCOUNT OFF ;
/cfquery
cfoutputThe Generated ID is: #putData.newId#/cfoutput
Robert B. Harrison
Director of Interactive Services
Austin Williams
125 Kennedy Drive, Suite 100
Hauppauge NY 11788
P : 631.231.6600 Ext. 119
F : 631.434.7022
http://www.austin-williams.com
Great advertising can't be either/or. It must be .
Plug in to our blog: AW Unplugged
http://www.austin-williams.com/unplugged
On 30/09/2009 03:16, Agha Mehdi wrote:
All,
How do I get the ID value back in CF from MySQL after doing an insert with
auto_inc data type in the table?
Thanks for help
Agha
~|
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:326752
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL Auto Increment Field
You can also do this using SQL. Sure this works with MS SQL, not 100% sure
about mySQL but it's standard SQL, so it should work.
cfquery name=putData datasource=#dsn#
SET NOCOUNT ON
INSERT INTO my_table (my_field1, my_field2)
VALUES ('#trim(value_field1)#', ('#trim(value_field2)#')
SELECT @@Identity AS newId
SET NOCOUNT OFF ;
/cfquery
cfoutputThe Generated ID is: #putData.newId#/cfoutput
The @@IDENTITY global variable is not standard SQL. It's specific to
T-SQL. So is SET NOCOUNT ON|OFF. Of the four SQL statements you've
listed, one is standard SQL.
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
~|
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:326753
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL Auto Increment Field
Thanks Azadi and Barney. Which approach is better in performance? I'm assuming result.generated_key? I would assume that there wouldn't be a significant performance difference either way. 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 information! ~| 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:326754 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: MySQL Auto Increment Field
Yes. You're correct. My Bad. TSQL.
That's probably why I should just turn off email when I'm busy on something
else.
Robert B. Harrison
Director of Interactive Services
Austin Williams
125 Kennedy Drive, Suite 100
Hauppauge NY 11788
P : 631.231.6600 Ext. 119
F : 631.434.7022
http://www.austin-williams.com
Great advertising can't be either/or. It must be .
Plug in to our blog: AW Unplugged
http://www.austin-williams.com/unplugged
-Original Message-
From: Dave Watts [mailto:dwa...@figleaf.com]
Sent: Tuesday, September 29, 2009 4:00 PM
To: cf-talk
Subject: Re: MySQL Auto Increment Field
You can also do this using SQL. Sure this works with MS SQL, not 100% sure
about mySQL but it's standard SQL, so it should work.
cfquery name=putData datasource=#dsn#
SET NOCOUNT ON
INSERT INTO my_table (my_field1, my_field2)
VALUES ('#trim(value_field1)#', ('#trim(value_field2)#')
SELECT @@Identity AS newId
SET NOCOUNT OFF ;
/cfquery
cfoutputThe Generated ID is: #putData.newId#/cfoutput
The @@IDENTITY global variable is not standard SQL. It's specific to
T-SQL. So is SET NOCOUNT ON|OFF. Of the four SQL statements you've
listed, one is standard SQL.
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
~|
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:326757
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL Auto Increment Field
And for those folks using MSSQL, please note that @@IDENTITY is
probably not what you want either. @@IDENTITY returns the last
identity produced by a connection to the database, regardless of the
table. So in some circumstances you can end up with an identity
returned that is not from the table you just inserted into. In most
cases you'll instead want to use SCOPE_IDENTITY() which returns the
identity produced by the current connection and current statement
combination.
For more, you can read up here: http://bit.ly/rhkvf
Cheers,
Judah
On Tue, Sep 29, 2009 at 12:59 PM, Dave Watts dwa...@figleaf.com wrote:
You can also do this using SQL. Sure this works with MS SQL, not 100% sure
about mySQL but it's standard SQL, so it should work.
cfquery name=putData datasource=#dsn#
SET NOCOUNT ON
INSERT INTO my_table (my_field1, my_field2)
VALUES ('#trim(value_field1)#', ('#trim(value_field2)#')
SELECT @@Identity AS newId
SET NOCOUNT OFF ;
/cfquery
cfoutputThe Generated ID is: #putData.newId#/cfoutput
The @@IDENTITY global variable is not standard SQL. It's specific to
T-SQL. So is SET NOCOUNT ON|OFF. Of the four SQL statements you've
listed, one is standard SQL.
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
~|
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:326758
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: MySQL Auto Increment Field
Man. Now I'm glad I posted that. Just went through and changed all: SELECT @@Identity AS newId To SELECT SCOPE_IDENTITY() AS newId Looks like it worked. I've been using that statement for six or seven years. Never failed, but I could see where if it did I'd have had no idea what happened. Thanks for clearing that up. Robert B. Harrison Director of Interactive Services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or. It must be . Plug in to our blog: AW Unplugged http://www.austin-williams.com/unplugged __ Information from ESET Smart Security, version of virus signature database 4468 (20090929) __ The message was checked by ESET Smart Security. http://www.eset.com ~| 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:326760 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL Auto Increment Field
I had to go and make that very same change a couple years ago, so I can totally understand missing that. Glad to be of help. Cheers, Judah On Tue, Sep 29, 2009 at 1:29 PM, Robert Harrison rob...@austin-williams.com wrote: Man. Now I'm glad I posted that. Just went through and changed all: SELECT @@Identity AS newId To SELECT SCOPE_IDENTITY() AS newId Looks like it worked. I've been using that statement for six or seven years. Never failed, but I could see where if it did I'd have had no idea what happened. Thanks for clearing that up. Robert B. Harrison Director of Interactive Services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or. It must be . Plug in to our blog: AW Unplugged http://www.austin-williams.com/unplugged __ Information from ESET Smart Security, version of virus signature database 4468 (20090929) __ The message was checked by ESET Smart Security. http://www.eset.com ~| 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:326762 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL Auto Increment Field
While I agree that SCOPE_IDENTITY is the preferred syntax, it's
unlikely that you'll encounter a problem with @@IDENTITY. CF will use
a single connection for all queries within a page that use the same
datasource, username and password, and the queries will be executed
sequentially unless wrapped in CFTHREAD. So to run into this problem,
you'd have to have a single query doing multiple inserts in a nested
transaction or stored procedure call.
In summary, I would write new T-SQL with SCOPE_IDENTITY, but I
wouldn't bother rewriting existing code.
Dave Watts, CTO, Fig Leaf Software
On 2009-09-29, Judah McAuley ju...@wiredotter.com wrote:
And for those folks using MSSQL, please note that @@IDENTITY is
probably not what you want either. @@IDENTITY returns the last
identity produced by a connection to the database, regardless of the
table. So in some circumstances you can end up with an identity
returned that is not from the table you just inserted into. In most
cases you'll instead want to use SCOPE_IDENTITY() which returns the
identity produced by the current connection and current statement
combination.
For more, you can read up here: http://bit.ly/rhkvf
Cheers,
Judah
On Tue, Sep 29, 2009 at 12:59 PM, Dave Watts dwa...@figleaf.com wrote:
You can also do this using SQL. Sure this works with MS SQL, not 100%
sure
about mySQL but it's standard SQL, so it should work.
cfquery name=putData datasource=#dsn#
SET NOCOUNT ON
INSERT INTO my_table (my_field1, my_field2)
VALUES ('#trim(value_field1)#', ('#trim(value_field2)#')
SELECT @@Identity AS newId
SET NOCOUNT OFF ;
/cfquery
cfoutputThe Generated ID is: #putData.newId#/cfoutput
The @@IDENTITY global variable is not standard SQL. It's specific to
T-SQL. So is SET NOCOUNT ON|OFF. Of the four SQL statements you've
listed, one is standard SQL.
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
~|
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:326763
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL Auto Increment Field
Thank you all for the great inputs. On Tue, Sep 29, 2009 at 1:48 PM, Judah McAuley ju...@wiredotter.com wrote: I had to go and make that very same change a couple years ago, so I can totally understand missing that. Glad to be of help. Cheers, Judah On Tue, Sep 29, 2009 at 1:29 PM, Robert Harrison rob...@austin-williams.com wrote: Man. Now I'm glad I posted that. Just went through and changed all: SELECT @@Identity AS newId To SELECT SCOPE_IDENTITY() AS newId Looks like it worked. I've been using that statement for six or seven years. Never failed, but I could see where if it did I'd have had no idea what happened. Thanks for clearing that up. Robert B. Harrison Director of Interactive Services Austin Williams 125 Kennedy Drive, Suite 100 Hauppauge NY 11788 P : 631.231.6600 Ext. 119 F : 631.434.7022 http://www.austin-williams.com Great advertising can't be either/or. It must be . Plug in to our blog: AW Unplugged http://www.austin-williams.com/unplugged __ Information from ESET Smart Security, version of virus signature database 4468 (20090929) __ The message was checked by ESET Smart Security. http://www.eset.com ~| 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:326764 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL Auto Increment Field
Dave Watts wrote: In summary, I would write new T-SQL with SCOPE_IDENTITY, but I wouldn't bother rewriting existing code. The article did mention trouble coming from the database itself if ones insert caused a trigger to be fired. Then the @@identity could return the generated key from what ever the trigger created. I would presume ColdFusion would provide no protection form this event. ~| 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:326765 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - allowMultiQueries=true not working on developer edition...
Oh, I fixed it! I just deleted the DSN and created a new one with the new connection setting. Seems to have worked. Thanks, 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:324960 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: mysql and order by datetime
Here you go Alan. DROP TABLE IF EXISTS `profilepicts`; CREATE TABLE `profilepicts` ( `ProfilePictsID` int(10) NOT NULL auto_increment, `ProfileID` int(10) default NULL, `ProfileScrapbookID` int(10) default NULL, `ImageName` varchar(255) default NULL, `Title` varchar(255) default NULL, `Caption` varchar(255) default NULL, `Rating` int(10) default NULL, `VoteTotal` int(10) default NULL, `Votes` int(10) default NULL, `DateAdded` datetime default NULL, `Winner` tinyint(1) default NULL, `WinnerDate` datetime default NULL, `Live` tinyint(1) NOT NULL default '0', PRIMARY KEY (`ProfilePictsID`), KEY `ProfileID` (`ProfileID`) ) ENGINE=MyISAM AUTO_INCREMENT=61 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC; INSERT INTO `profilepicts` VALUES (28,2,3,'Niagara Falls 161.jpg','scrapbooktest2','test2',15,30,2,'2009-05-28 00:00:00',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (32,2,4,'Mom and Dads HT speakers 003.jpg','aaaddd','sd',11,44,4,'2009-05-28 16:07:09',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (34,2,3,'Mom and Dads HT speakers 001.jpg',NULL,NULL,18,317,18,NULL,1,'2009-06-09 16:00:55',1); INSERT INTO `profilepicts` VALUES (36,2,0,'Tommy 011.jpg','asdfsafsad','adsfdsafs',11,32,3,'2009-05-29 15:04:15',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (37,2,0,'Tommy 010.jpg','adg','ggg',14,159,11,'2009-05-29 15:05:44',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (38,2,0,'Tommy 008.jpg','h','hhh',NULL,NULL,NULL,'2009-05-29 15:09:24',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (39,2,6,'Tommy 009.jpg','','q',19,19,1,'2009-05-29 15:10:53',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (44,1,9,'12060004.JPG','asfdsaf','sadfsadfa',NULL,NULL,NULL,'2009-06-01 10:19:58',1,'2009-06-08 15:53:46',1); INSERT INTO `profilepicts` VALUES (45,2,6,'120600041.JPG','asfsad','fsadf',NULL,NULL,NULL,'2009-06-01 12:17:24',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (46,2,6,'120600061.JPG','aewrewrw','ewqrewqrw',NULL,NULL,NULL,'2009-06-01 12:17:33',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (47,6,10,'2ndary-page.jpg','asdfas','dfsadf',NULL,NULL,NULL,'2009-06-01 13:32:04',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (48,6,10,'fashion-hot-spots.jpg','adsfsa','fdsaf',NULL,NULL,NULL,'2009-06-01 13:32:12',1,'2009-06-07 15:53:46',1); INSERT INTO `profilepicts` VALUES (49,3,11,'fmf.jpg','adsf','asdf',14,222,16,'2009-06-01 13:33:22',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (50,5,12,'2ndary-pageAlt.jpg','adsfsa','dfsadfs',15,106,7,'2009-06-01 13:34:07',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (51,1,13,'Epcot 2.JPG','test','test',10,10,1,'2009-06-04 09:38:41',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (52,2,3,'Western Dinner 5.JPG','m good','asfdadsfds',NULL,NULL,NULL,'2009-06-08 13:46:03',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (57,2,5,'C6664A4D-F9A1-27CD-57CF0B4879648111.jpg','test','test',NULL,NULL,NULL,'2009-06-09 15:02:21',1,'2009-06-04 15:53:46',1); INSERT INTO `profilepicts` VALUES (58,2,5,'C66690B6-97AD-2367-59D50409E1DEFD9F.jpg','asdf','asdf',NULL,NULL,NULL,'2009-06-09 15:02:39',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (59,2,4,'CBC4042D-C5CA-9147-D58B03C59ED55B39.jpg','test','test',NULL,NULL,NULL,'2009-06-10 16:02:49',NULL,NULL,1); INSERT INTO `profilepicts` VALUES (60,3,14,'CBE190BC-F941-5BBB-7988A145B999530C.jpg','test','test',NULL,NULL,NULL,'2009-06-10 16:35:06',NULL,NULL,1); Here are the queries I was questioning: SELECT PP.ProfileID FROM profilepicts PP WHERE PP.Live = 1 ORDER BY PP.DateAdded DESC RESULTS: 3 2 2 2 1 5 3 6 I add a distinct and I get SELECT DISTINCT(PP.ProfileID) FROM profilepicts PP WHERE PP.Live = 1 ORDER BY PP.DateAdded DESC RESULTS: 5 3 6 1 2 Why did 5 come first after I added the DISTINCT? -Original Message- From: Alan Rother [mailto:alan.rot...@gmail.com] Sent: Wednesday, June 10, 2009 6:02 PM To: cf-talk Subject: Re: mysql and order by datetime Hey Chad, Out of curiosity, can you share those date/time values with us for the first query? I'd be interested in playing with it to see why it's doing what it's doing. =] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| 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:323390 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: mysql and order by datetime
The only way I found to fix this is to return the first query via MySQL then use a Query of a Query in CF to do a group by on the data and ORDER it by the DateTime field. Strange. SELECT PP.ProfileID FROM profilepicts PP WHERE PP.Live = 1 ORDER BY PP.DateAdded DESC cfquery dbtype=query name=profiles SELECT ProfileID FROM profiles GROUP BY ProfileID ORDER BY DateAdded DESC /cfquery RESULT: 3 2 1 5 6 -Original Message- From: Chad Gray [mailto:cg...@careyweb.com] Sent: Wednesday, June 10, 2009 5:13 PM To: cf-talk Subject: mysql and order by datetime This is strange. I run this query and I get proper results (DateAdded is a DateTime data type): SELECT PP.ProfileID FROM profilepicts PP WHERE PP.Live = 1 ORDER BY PP.DateAdded DESC RESULTS: 3 2 2 2 1 5 3 6 I add a distinct and I get SELECT DISTINCT(PP.ProfileID) FROM profilepicts PP WHERE PP.Live = 1 ORDER BY PP.DateAdded DESC RESULTS: 5 3 6 1 2 Why did 5 come first after I added the DISTINCT? ~| 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:323376 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: mysql and order by datetime
Hey Chad, Out of curiosity, can you share those date/time values with us for the first query? I'd be interested in playing with it to see why it's doing what it's doing. =] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| 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:323378 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL IDE on OS X
Can anyone recommend a good MySQL IDE on OS X? So far I've been using Navicat but it's kind of a joke. Anyone have one they actually like? ~| 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:322952 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL IDE on OS X
Personally I went with Querious, defiantly worth the small cost of it, granted I got in on it at the start but more features that sequal pro and still only $25 http://www.araelium.com/querious/ http://theappleblog.com/2009/02/27/mysql-showdown-querious-vs-sequel-pro/ some others http://theappleblog.com/2009/03/31/eight-more-mysql-apps-for-os-x/ ~| 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:322953 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL IDE on OS X
On Tuesday 26 May 2009, David McGuigan wrote: Anyone have one they actually like? Aqua DataStudio. -- Helping to economically foster proactive partnerships as part of the IT team of the year, '09 and '08 Tom Chiverton Developer Tel: +44 0161 618 5032 Fax: +44 0161 618 5099 tom.chiver...@halliwells.com 3 Hardman Square, Manchester, M3 3EB www.Halliwells.com This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office together with a list of those non members who are referred to as partners. We use the word partner to refer to a member of the LLP, or an employee or consultant with equivalent standing and qualifications. Regulated by the Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.Halliwells.com. ~| 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:322818 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL IDE on OS X
From MySQL's web site http://dev.mysql.com/downloads/gui-tools/5.0.html Wil Genovese On Tue, May 26, 2009 at 2:54 PM, David McGuigan davidmcgui...@gmail.comwrote: Can anyone recommend a good MySQL IDE on OS X? So far I've been using Navicat but it's kind of a joke. Anyone have one they actually like? ~| 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:322798 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL IDE on OS X
You know, I use the MySQL Query Browser on Vista a lot and actually love it. On OS X however, that app literally crashes on me within 30 seconds of launching it each and every time, and I've got a completely fresh install of OS X as of Saturday night with just CS4, Adium, some browsers, ColdFusion and MySQL installed. Is it stable for you? Thanks. On Tue, May 26, 2009 at 2:32 PM, Wil Genovese jugg...@visi.com wrote: From MySQL's web site http://dev.mysql.com/downloads/gui-tools/5.0.html Wil Genovese On Tue, May 26, 2009 at 2:54 PM, David McGuigan davidmcgui...@gmail.com wrote: Can anyone recommend a good MySQL IDE on OS X? So far I've been using Navicat but it's kind of a joke. Anyone have one they actually like? ~| 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:322799 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL IDE on OS X
On Tue, May 26, 2009 at 12:54 PM, David McGuigan davidmcgui...@gmail.comwrote: Can anyone recommend a good MySQL IDE on OS X? So far I've been using Navicat but it's kind of a joke. Anyone have one they actually like? I'm a relatively recent MBP user, so haven't really delved too deeply into it yet, but have heard good things about Sequel Pro ( http://www.sequelpro.com/). Used to be CocoaSQL. -- I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. ~| 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:322800 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL IDE on OS X
Charlie! That app is AWESOME. EXACTLY what I was looking for. Better windowing, much more attractive, smart shortcuts, inline quick querier. So slick. Command 1 2 3. So simple and clever. There's even a keyboard shortcut to execute your inline query ( though it's a weird one: Fn + Enter ). Seriously thanks a million. On Tue, May 26, 2009 at 2:46 PM, Charlie Griefer charlie.grie...@gmail.comwrote: On Tue, May 26, 2009 at 12:54 PM, David McGuigan davidmcgui...@gmail.com wrote: Can anyone recommend a good MySQL IDE on OS X? So far I've been using Navicat but it's kind of a joke. Anyone have one they actually like? I'm a relatively recent MBP user, so haven't really delved too deeply into it yet, but have heard good things about Sequel Pro ( http://www.sequelpro.com/). Used to be CocoaSQL. -- I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. ~| 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:322801 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL IDE on OS X
DBVisualizer! it rocks for multiple database server types like MSSQL and MySQL and many more... as long as you have the JDBC drivers for it! (it's paid though, but fairly priced!) http://www.dbvis.com/products/dbvis/ 2009/5/26 Wil Genovese jugg...@visi.com From MySQL's web site http://dev.mysql.com/downloads/gui-tools/5.0.html Wil Genovese On Tue, May 26, 2009 at 2:54 PM, David McGuigan davidmcgui...@gmail.com wrote: Can anyone recommend a good MySQL IDE on OS X? So far I've been using Navicat but it's kind of a joke. Anyone have one they actually like? ~| 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:322804 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL IDE on OS X
I use Sequelpro aswell its really good. Paul On 26/05/2009, at 4:46 PM, Charlie Griefer wrote: On Tue, May 26, 2009 at 12:54 PM, David McGuigan davidmcgui...@gmail.comwrote: Can anyone recommend a good MySQL IDE on OS X? So far I've been using Navicat but it's kind of a joke. Anyone have one they actually like? I'm a relatively recent MBP user, so haven't really delved too deeply into it yet, but have heard good things about Sequel Pro ( http://www.sequelpro.com/). Used to be CocoaSQL. -- I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success. ~| 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:322811 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - How to create a category tree in a store
I think I'm going to use this tool: http://nstree.riaforge.org/ I've been playing around with it. Seems fairly straightforward and does everything I need. I'm just working on what the admin interface would look like to display the entire tree so you could select multiple categories for a given product. Thanks for everyone's input. 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:322755 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - How to create a category tree in a store
@will Good deal. Haven't used below but it seems the author has written a nice book about it on his site. Good luck. -- Ryan On Sat, May 23, 2009 at 2:27 AM, Will Tomlinson w...@wtomlinson.com wrote: I think I'm going to use this tool: http://nstree.riaforge.org/ I've been playing around with it. Seems fairly straightforward and does everything I need. I'm just working on what the admin interface would look like to display the entire tree so you could select multiple categories for a given product. Thanks for everyone's input. 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:322756 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL - How to create a category tree in a store
This might be useful: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Cheers, Dave I built a store application that has a simple product category schema. Right now, the user can select one or more categories to associate to a given product. I have a linking table in between those two tables that resolves the many-to-many. Now, I need the ability to let the user create their own categories and nested categories/subcategories, then associate a product to one or more of them. Much like a category tree. i.e. A shoe needs to be associated to the footwear category, and the shoes category located within the footwear category. Make sense? :) I've looked at recursion, but I'm on MySQL 5.1, and I don't think it supports recursive queries. Does anyone have any suggestions on the best way to go about this? Also, what would you implement for the admin tool that creates the category tree? Thanks, 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:322708 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - How to create a category tree in a store
This might be useful: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Cheers, Dave Yes, there's a lot of great info there. But it looks like a nightmare if I want to add a node under the parent. Products -baseball --gloves --bats --shin guards -golf --gloves --bags ---nike ---titleist ---ping --shoes If I want to add lacrosse between baseball and golf, that'd be a pain. Wish I could find something easier for this. Thanks, 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:322709 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL - How to create a category tree in a store
@willr u ok with setting a maximum number of levels? -- Ryan On Fri, May 22, 2009 at 10:14 AM, Will Tomlinson w...@wtomlinson.comwrote: This might be useful: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Cheers, Dave Yes, there's a lot of great info there. But it looks like a nightmare if I want to add a node under the parent. Products -baseball --gloves --bats --shin guards -golf --gloves --bags ---nike ---titleist ---ping --shoes If I want to add lacrosse between baseball and golf, that'd be a pain. Wish I could find something easier for this. Thanks, 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:322710 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - How to create a category tree in a store
@willr u ok with setting a maximum number of levels? -- Ryan Yep. I could max it out at 4 levels. I had thought about building related tables, 4 of them. Thanks, 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:322711 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - How to create a category tree in a store
I am assuming you at least have product_id and category_id in your linking table. I think if you added a parent_id accounting for the root level you could build out your tree. Not sure of how you want to go about it but thinking out loud: 1. Query all categories at root level 2. Loop through root level and pull children (all those with a parent as that category) 3. Repeat this for the max levels 4. Pull products at each level (1,2,3,4) Basically you are only assigning one sub/category but you have the hierarchical relationship stored in the db. You would have to account for that when searching on the front end. Front end I would suggest jQuery for an accordion like effect(slidedown/up and/or fadein/out) and pull the products using the ajax or load method. -- Ryan On Fri, May 22, 2009 at 10:41 AM, Will Tomlinson w...@wtomlinson.comwrote: @willr u ok with setting a maximum number of levels? -- Ryan Yep. I could max it out at 4 levels. I had thought about building related tables, 4 of them. Thanks, 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:322714 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - How to create a category tree in a store
Also depending on your traffic versus how often the products will get updated versus your hosting plan etc you may want to output the entire tree at the time of it being maintained on the back end rather than every time someone visits the site. Don't know your situation, just an idea if you need to limit hits on the database. -- Ryan On Fri, May 22, 2009 at 11:11 AM, Ryan Letulle bayous...@gmail.com wrote: I am assuming you at least have product_id and category_id in your linking table. I think if you added a parent_id accounting for the root level you could build out your tree. Not sure of how you want to go about it but thinking out loud: 1. Query all categories at root level 2. Loop through root level and pull children (all those with a parent as that category) 3. Repeat this for the max levels 4. Pull products at each level (1,2,3,4) Basically you are only assigning one sub/category but you have the hierarchical relationship stored in the db. You would have to account for that when searching on the front end. Front end I would suggest jQuery for an accordion like effect(slidedown/up and/or fadein/out) and pull the products using the ajax or load method. -- Ryan On Fri, May 22, 2009 at 10:41 AM, Will Tomlinson w...@wtomlinson.comwrote: @willr u ok with setting a maximum number of levels? -- Ryan Yep. I could max it out at 4 levels. I had thought about building related tables, 4 of them. Thanks, 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:322715 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL and CF LAST_INSERT_ID()
It doesn't work because you can't run multiple statements in a single
query. If you use two CFQUERY tags (wrapped in a CFTRANSACTION to
ensure connection affinity) it'll work fine. MySQL Front is splitting
the single query into two distinct queries on the semicolon, sending
each to the server independently, and then giving you back the two
results. I.e. it's a client-side optimization, not server
functionality.
cheers,
barneyb
On Tue, Mar 17, 2009 at 10:42 AM, Chad Gray cg...@careyweb.com wrote:
I am trying to use the MySQL function LAST_INSERT_ID() in a CFquery tag and
get this error.
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '; SELECT
LAST_INSERT_ID() AS UserID' at line 4
INSERT users (Foo, Foo1, Foo2)
VALUES ('test', 'test', 'test');
SELECT LAST_INSERT_ID() AS UserID;
If I run this query in MySQL Front it works fine. It returns the last
UserID. If I run this code inside of CFQuery I get the error.
Any ideas why it is not working in a CFQUery tag?
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320588
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL and CF LAST_INSERT_ID()
You actually *can* run multiple statements in a cfquery if you change a
setting in the datasource. By default MySQL does not let you, this is a
security precaution to protect against SQL injection. This may help:
http://www.petefreitag.com/item/357.cfm
Also note that you can only use CFTRANSACTION with innodb tables, not
MyISAM tables. I don't know if it will throw an error or not, last time
I tried it did not, but there is no transaction going on when you use
these tags with MyISAM tables.
-Ryan
Barney Boisvert wrote:
It doesn't work because you can't run multiple statements in a single
query. If you use two CFQUERY tags (wrapped in a CFTRANSACTION to
ensure connection affinity) it'll work fine. MySQL Front is splitting
the single query into two distinct queries on the semicolon, sending
each to the server independently, and then giving you back the two
results. I.e. it's a client-side optimization, not server
functionality.
cheers,
barneyb
On Tue, Mar 17, 2009 at 10:42 AM, Chad Gray cg...@careyweb.com wrote:
I am trying to use the MySQL function LAST_INSERT_ID() in a CFquery tag and
get this error.
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '; SELECT
LAST_INSERT_ID() AS UserID' at line 4
INSERT users (Foo, Foo1, Foo2)
VALUES ('test', 'test', 'test');
SELECT LAST_INSERT_ID() AS UserID;
If I run this query in MySQL Front it works fine. It returns the last
UserID. If I run this code inside of CFQuery I get the error.
Any ideas why it is not working in a CFQUery tag?
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320589
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL and CF LAST_INSERT_ID()
well...
first, the proper syntax for an INSERT query starts with INSERT INTO,
not just INSERT ...
second, by default, multiple statements are NOT supported in mysql db -
do you have multiple statements enabled for this dsn in mysql?
third: cf8 has a cfquery attribute RESULT, which, when utilised
properly, can return the new generated PK. check the cfml reference for
details. if you do not have one - download a free pdf from adobe.com.
for mysql db the variable is GENERATED_KEY...
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Chad Gray wrote:
I am trying to use the MySQL function LAST_INSERT_ID() in a CFquery tag and
get this error.
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '; SELECT
LAST_INSERT_ID() AS UserID' at line 4
INSERT users (Foo, Foo1, Foo2)
VALUES ('test', 'test', 'test');
SELECT LAST_INSERT_ID() AS UserID;
If I run this query in MySQL Front it works fine. It returns the last
UserID. If I run this code inside of CFQuery I get the error.
Any ideas why it is not working in a CFQUery tag?
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320590
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL and CF LAST_INSERT_ID()
If your on CF8 there are inbuilt features to take advantage of see
here: http://tutorial9.learncf.com/
Paul.
On Tue, Mar 17, 2009 at 1:42 PM, Chad Gray cg...@careyweb.com wrote:
I am trying to use the MySQL function LAST_INSERT_ID() in a CFquery tag and
get this error.
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '; SELECT
LAST_INSERT_ID() AS UserID' at line 4
INSERT users (Foo, Foo1, Foo2)
VALUES ('test', 'test', 'test');
SELECT LAST_INSERT_ID() AS UserID;
If I run this query in MySQL Front it works fine. It returns the last
UserID. If I run this code inside of CFQuery I get the error.
Any ideas why it is not working in a CFQUery tag?
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320591
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL and CF LAST_INSERT_ID()
@ Barney: you CAN run multiple queries in one cfquery tag as long as you db supports it. by default, mysql db does not, but one can easily change that using mysql admin or another mysql db administration tool... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Barney Boisvert wrote: It doesn't work because you can't run multiple statements in a single query. If you use two CFQUERY tags (wrapped in a CFTRANSACTION to ensure connection affinity) it'll work fine. MySQL Front is splitting the single query into two distinct queries on the semicolon, sending each to the server independently, and then giving you back the two results. I.e. it's a client-side optimization, not server functionality. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320592 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL and CF LAST_INSERT_ID()
InnoDB are the only transactional tables in MySQL, but CF doesn't know
about the backing table, and the latter is what we care about. In
order for transactions to work correctly, the client has to ensure
that all queries within the transaction execute on a single DB
connection, and that no other queries are executed on that same
connection. Since connections are managed by CF's connection pool,
using the CFTRANSACTION tag ensures that i own the connection
behaviour, regardless of whether the backing DB is actually
transaction-aware. That's the part that is essential for a
LAST_INSERT_ID() query to execute correctly, not an actual
transaction.
And I know about the MySQL server setting for multiple statement
support, but I'm of the opinion that you should never use it. :)
Aside from the potential SQL injection vulnerabilities, I find that
grouping statements in CFQUERY tags (as you can do with MSSQL out of
the box), usually ends up in code that is harder to follow, because
the eye naturally uses the CFQUERY tags to delimit blocks, not CFQUERY
tags AND internally contained semicolons.
cheers,
barneyb
On Tue, Mar 17, 2009 at 10:57 AM, Ryan Stille r...@cfwebtools.com wrote:
You actually *can* run multiple statements in a cfquery if you change a
setting in the datasource. By default MySQL does not let you, this is a
security precaution to protect against SQL injection. This may help:
http://www.petefreitag.com/item/357.cfm
Also note that you can only use CFTRANSACTION with innodb tables, not
MyISAM tables. I don't know if it will throw an error or not, last time
I tried it did not, but there is no transaction going on when you use
these tags with MyISAM tables.
-Ryan
Barney Boisvert wrote:
It doesn't work because you can't run multiple statements in a single
query. If you use two CFQUERY tags (wrapped in a CFTRANSACTION to
ensure connection affinity) it'll work fine. MySQL Front is splitting
the single query into two distinct queries on the semicolon, sending
each to the server independently, and then giving you back the two
results. I.e. it's a client-side optimization, not server
functionality.
cheers,
barneyb
On Tue, Mar 17, 2009 at 10:42 AM, Chad Gray cg...@careyweb.com wrote:
I am trying to use the MySQL function LAST_INSERT_ID() in a CFquery tag and
get this error.
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '; SELECT
LAST_INSERT_ID() AS UserID' at line 4
INSERT users (Foo, Foo1, Foo2)
VALUES ('test', 'test', 'test');
SELECT LAST_INSERT_ID() AS UserID;
If I run this query in MySQL Front it works fine. It returns the last
UserID. If I run this code inside of CFQuery I get the error.
Any ideas why it is not working in a CFQUery tag?
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320593
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: MySQL and CF LAST_INSERT_ID()
Thanks everyone!
I remember having problems with some database with INSERT INTO. Access maybe?
I can't remember.
I will check out the CF8 result structure... I did not know is existed!
Thanks!
-Original Message-
From: Azadi Saryev [mailto:az...@sabai-dee.com]
Sent: Tuesday, March 17, 2009 2:02 PM
To: cf-talk
Subject: Re: MySQL and CF LAST_INSERT_ID()
well...
first, the proper syntax for an INSERT query starts with INSERT INTO,
not just INSERT ...
second, by default, multiple statements are NOT supported in mysql db -
do you have multiple statements enabled for this dsn in mysql?
third: cf8 has a cfquery attribute RESULT, which, when utilised
properly, can return the new generated PK. check the cfml reference for
details. if you do not have one - download a free pdf from adobe.com.
for mysql db the variable is GENERATED_KEY...
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Chad Gray wrote:
I am trying to use the MySQL function LAST_INSERT_ID() in a CFquery tag
and get this error.
Error Executing Database Query.
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '; SELECT
LAST_INSERT_ID() AS UserID' at line 4
INSERT users (Foo, Foo1, Foo2)
VALUES ('test', 'test', 'test');
SELECT LAST_INSERT_ID() AS UserID;
If I run this query in MySQL Front it works fine. It returns the last
UserID. If I run this code inside of CFQuery I get the error.
Any ideas why it is not working in a CFQUery tag?
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320595
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL - Trying to query x days back
I would use the DATEDIFF() function: SELECT DATE(orderdate) AS thedate, SUM(ordertotal) AS ordersubtotal FROM tblOrders WHERE DATEDIFF(current_date(), orderdate) = 30 GROUP BY DATE(orderdate) (I think this should work; I use MSSQL, so can't test this...) Seb Seb Duggan Web ColdFusion Developer e: s...@sebduggan.com t: 07786 333184 w: http://sebduggan.com On 28 Dec 2008, at 22:32, Will Tomlinson wrote: I have an orders table with a datetime field. I'm trying to query orders that are x number of days back from today. Ex. Show me all orders 30 days back from now. SELECT date(orderdate) as thedate, sum(ordertotal) AS ordersubtotal FROM tblOrders where orderdate = current_date() - 30 group by date(orderdate) The query works, but it's returning orders all the way back to 2006 - Not correct! What's weird is, if I change the value from 30, to 7, It shows the correct orders. I can change it all the way up to 28, and it's correct. If you change it to 29, which runs into last month, it pulls EVERYthing. Very strange! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317190 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - Trying to query x days back
(I think this should work; I use MSSQL, so can't test this...) Seb Yep, that works perfectly! I really appreciate it dude! I still wonder why what I was using wasn't working. Thanks, Will ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317196 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - Trying to query x days back
Yeah DATE_SUB() would work too select * from table WHERE date_column BETWEEN CURDATE() and DATE_SUB(CURDATE(),INTERVAL 30 DAY) Eric On Sun, Dec 28, 2008 at 7:00 PM, Will Tomlinson w...@wtomlinson.com wrote: (I think this should work; I use MSSQL, so can't test this...) Seb Yep, that works perfectly! I really appreciate it dude! I still wonder why what I was using wasn't working. Thanks, Will ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317197 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL 5 TIME columns reported as dates in ColdFusion 8
On Wednesday 30 Jul 2008, Barney Boisvert wrote: CF doesn't have the concept of a time data type. Ahh, of course, that makes *sort* of sense now, cheers. -- Tom Chiverton This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310002 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL 5 TIME columns reported as dates in ColdFusion 8
CF doesn't have the concept of a time data type. When I need
durations, I always store a number of seconds (or milliseconds). I've
used TIME columns a couple times, usually for external reasons, but
all my queries that feed CF convert the time to a number of seconds.
cheers,
barneyb
On Wed, Jul 30, 2008 at 8:06 AM, Tom Chiverton
[EMAIL PROTECTED] wrote:
Does anyone know of a way around the problem that ColdFusion 8 (on SuSE, if it
makes any odds) reports MySQL's TIME columns (or values) as having a date ?
The problem is that
cfquery name=q datasource=fraud
select str_to_date('23:45','%k:%i') as foo
/cfquery
cfoutput#q.foo#/cfoutput
outputs
{ts '1970-01-01 23:45:00'}
instead of the
23:35:00
that MySQL gives when the SQL is run directly
The problem is compounded because
cfquery name=q datasource=fraud
select extract( year from str_to_date('23:45','%k:%i') ) as foo
/cfquery
cfoutput#q.foo#/cfoutput
outputs
0
when obviously using ColdFusion's year() on the previous q.foo result will
give 1970 !
I've looked through the MySQL connectors JDBC reference, and can't see
anything obvious.
Any hints ?
--
Tom Chiverton
--
Barney Boisvert
[EMAIL PROTECTED]
http://www.barneyb.com/
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:309969
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
