SQL Web Log Entries
Turning On Multiple Statements In ColdFusion 8 MySQL 4/5 Datasource
Posted: April 24, 2008 at 2:31 PM by Ben Nadel
Lately, I have been doing a lot of work in ColdFusion 8 and MySQL 5. One of the things that I noticed immediately was that MySQL didn't seem to allow multiple statements within one CFQuery tag. I read that this is apparently done to prevent SQL injection attacks, which as a ColdFusion developer seems kind of silly. Anyway, it took just a little bi... read more »
Comments (29) | Post Comment | Ask Ben | Permalink
Update To MSSQL To MySQL Translation (II)
Posted: April 17, 2008 at 8:36 AM by Ben Nadel
I have updated my MS SQL to MySQL translation post to include the following functions: Update a table using JOINs - MSSQL Delete from a table using JOINs - MSSQL Creating and populating table variables - MSSQL ... read more »
Comments (0) | Post Comment | Ask Ben | Permalink
Update To MSSQL To MySQL Translation
Posted: April 16, 2008 at 9:33 AM by Ben Nadel
I have started building out my MS SQL to MySQL translation post . I have decided that I am going to tell the whole "story" in MS SQL 2005 before I even try to attempt any of it in MySQL. I feel like this will help me get it done faster - not having to stop all the time to figure out how this is done in MySQL at ever step of the journey. Here is w... read more »
Comments (17) | Post Comment | Ask Ben | Permalink
Translate Microsoft SQL (MSSQL) To MySQL
Posted: April 15, 2008 at 2:43 PM by Ben Nadel
This is going to be a post that I update from time to time and I just wanted to kick if off right now. For years, I have been working on Microsoft SQL Server and I have reached what I think is a descent level of competence in it. As of late, however, I have been doing a lot of work in MySQL. While MySQL is a good database server (from what I have ... read more »
Comments (21) | Post Comment | Ask Ben | Permalink
Thoughts On Storing Duplicate / Calculated Data In The Database
Posted: March 28, 2008 at 10:02 AM by Ben Nadel
The other day, I was talking to this girl, Franziska, and all I could think about was how insanely cute she was. Then, as I was walking home later that day, I kept thinking about how cute she was. As I was doing this, I started to think about database design (is it odd that hot girls and databases are easily swapped in my head??). And, it occurred... read more »
Comments (31) | Post Comment | Ask Ben | Permalink
Ben Nadel's Easy Tips For Writing Better, More Optimized SQL
Posted: March 27, 2008 at 12:56 PM by Ben Nadel
I was just reading over on Andrew Powell's ColdFusion blog about the problem that server-side developers have with writing SQL. I don't quite agree with what Andy was saying; I think since SQL is such an integral part of web development, all server-side developers should have at least a decent understanding of SQL. They don't need to be experts,... read more »
Comments (47) | Post Comment | Ask Ben | Permalink
Odd MySql CFQueryParam Truncation Issue (Help)?
Posted: February 27, 2008 at 11:49 AM by Ben Nadel
Can anyone help me out with this? I am used to using Microsoft SQL Server, which I love; recently, however, I have been working on some MySQL 5.0 databases and they seem to have some odd rules. Right now, I am getting a really strange truncation error when using CFQueryParam in a calculated column. Running this code: SELECT <cfqueryparam v... read more »
Comments (13) | Post Comment | Ask Ben | Permalink
Ask Ben: Querying Groups With Conditional Elements
Posted: January 14, 2008 at 7:00 AM by Ben Nadel
I hav a situation in my academic project, and was struggling to get a proper approach towards the solution, then i decided to ask 'kind-hearted' ppl for help ... while browsing net for forums wid posts with similar situation, i 'stumbled' upon you site. it's a nice and great job u r doin here ben. thanks a ton... do consider to direct me towards t... read more »
Comments (2) | Post Comment | Ask Ben | Permalink
Selecting Top X From Each Group
Posted: January 7, 2008 at 8:57 AM by Ben Nadel
Over the weekend, I was talking to my friend who is building a fantasy sports site. In this site, he has different leagues, and within each league, many members. He asked me how to build a query that selects the top 5 performing members from each league. This is one of those queries that always FEELS like it should be really easy, but the moment y... read more »
Comments (17) | Post Comment | Ask Ben | Permalink
Getting One Record Per Group From A One-to-Many Join
Posted: December 19, 2007 at 8:58 AM by Ben Nadel
Often times in an application, I have wanted to output a simple list of items that have a one-to-many relationship with other items in the database. One scenario that I come across very often involves attorneys and offices; many attorneys will work out of more than one office, but in an attorney directory, they only want to be listed with their pr... read more »
Comments (9) | Post Comment | Ask Ben | Permalink
ColdFusion CFQueryParam Binding vs. SQL Execution
Posted: December 18, 2007 at 8:29 AM by Ben Nadel
I ran into a really frustrating "bug" last night that took me over an hour to debug! I was trying to run this massive ColdFusion query and kept getting an error about a data type not casting property in a CFQueryParam tag. The thing that was frustrating me so much, and making the error so hard to debug, was that the CFQueryParam tag was not even i... read more »
Comments (37) | Post Comment | Ask Ben | Permalink
Grouping JOIN Clauses In SQL
Posted: November 28, 2007 at 7:06 PM by Ben Nadel
Here's something that I have touched on in my blog via examples, but I have never talked about it explicitly. It is the idea of grouping SQL JOIN clauses. Normally, when you join multiple tables together, you simply have one JOIN after another. In some situations, this is not always possible to do in a way that will return accurate data. In rare c... read more »
Comments (10) | Post Comment | Ask Ben | Permalink
Getting @@RowCount After SQL INSERT INTO Statement
Posted: November 28, 2007 at 2:57 PM by Ben Nadel
I just made a happy little discovery about SQL's INSERT INTO statement. After you run the INSERT INTO statement on a temporary data table, the SQL variable, @@RowCount, contains the number of records that were inserted into the temp table. Take a look at this demo: <!--- Create, populate, and query the temp ID table. ---> <cfquery nam... read more »
Comments (21) | Post Comment | Ask Ben | Permalink
Alf Pedersen On NULL Value Usage In Database Design
Posted: November 20, 2007 at 9:05 AM by Ben Nadel
As you know, I am not a huge fan of NULL values in databases. There are times, when I do like them (such as with undefined date/time fields), but for the most part, I think they are horrible, cause confusion, and create much more overhead than they are worth from any sort of business requirements standpoint. I have gotten a lot of heat for this,... read more »
Comments (23) | Post Comment | Ask Ben | Permalink
Ask Ben: Selecting From The Union Of Two SQL Tables
Posted: October 23, 2007 at 7:57 AM by Ben Nadel
Hi Ben, it's me again... I have a MYSQL 5.X question for you: I have two tables - Table A: pru_properties and Table B: mls_properties. They have the exact same columns names, but different content inside of them since they get feed by different companies. The only matching column is MLSID. how can I do a CFQUERY for both tables using a GROUP BY ML... read more »
