SQL Web Log Entries
Why My Queries Hate Application Service Layers
Posted: June 18, 2009 at 10:02 AM by Ben Nadel
Last week, I was trying to take an old, procedural style ColdFusion application and factor out queries into some sort of Service layer. I wasn't trying to create an Object Oriented ColdFusion application - I was merely trying to centralize some business logic such that it wasn't so distributed throughout the application. At a glance, this seems li... read more »
Comments (63) | Post Comment | Ask Ben | Permalink
Ask Ben: Pulling Unique SQL Records That Match ALL Join Conditions
Posted: April 23, 2009 at 10:00 AM by Ben Nadel
The reader question is far too long to put here, but the jist of it was that the reader had a primary table that joined to several "property" tables in a One-to-Many kind of relationship. These properties could be selected in a search form and the reader wanted to be able to return all primary table records that matched at least every selected pro... read more »
Comments (12) | Post Comment | Ask Ben | Permalink
MySQL 3/4 - com.mysql.jdbc.Driver And allowMultiQueries=true
Posted: March 25, 2009 at 4:20 PM by Ben Nadel
Just a quick note on configuring a MySQL JDBC Driver to allow multiple queries per CFQuery tag when using MySQL 3/4 (pre MySQL 5) on a ColdFusion 8 server. A few months ago, I leaned that you could add "allowMultiQueries=true" to the MySQL 5 connection string . However, today, I had to do some work on a ColdFusion MX 7 box that only had MySQL JDB... read more »
Comments (21) | Post Comment | Ask Ben | Permalink
Data Truncation: Truncated Incorrect DOUBLE Value When Updating Timestamp
Posted: February 18, 2009 at 3:04 PM by Ben Nadel
I just spent the last hour trying to debug the smallest SQL problem! I have a datatable of Contracts and I was building a feature in our client software where we could end all contracts of a certain type (SLA) at the same time (trust me, there is a business reason behind this). To do this, I was executing the following MySQL Cross-Table UPDATE sta... read more »
Comments (15) | Post Comment | Ask Ben | Permalink
Seeding SQL RAND() Method With NEWID() For Per-Row Random Values (Thanks Joshua Cyr)
Posted: January 27, 2009 at 2:54 PM by Ben Nadel
From what I have read and from what I've seen demos of, I thought that MS SQL's RAND() method could only return one random value per connection. As I never looked at the documentation for RAND(), I never realized that it could be seeded. This morning, in my post on selecting random records from a weighted data table , Joshua Cyr pointed me to a... read more »
Comments (3) | Post Comment | Ask Ben | Permalink
Ask Ben: Selecting A Random Row From A Weighted, Filtered Record Set
Posted: January 27, 2009 at 9:56 AM by Ben Nadel
This is the scenario to help explain my question so I hope it makes sense: On a form is a zip code search box where you enter a zip code to see the list of services or shops in your area (e.g. 90210). The query returns 10 businesses in that area. Now, the tricky part is that some of the business should appear at the top (or near the top) more ofte... read more »
Comments (17) | Post Comment | Ask Ben | Permalink
MySQL Cannot Convert Value '0000-00-00 00:00:00' From Column XX To TIMESTAMP
Posted: December 19, 2008 at 4:15 PM by Ben Nadel
When I first started using the MySQL database engine, I would get this error from time to time after doing data transfer: MySQL Cannot Convert Value '0000-00-00 00:00:00' From Column XX To TIMESTAMP For a long time, I just assumed that I was messing something up in the data transfer (which I may very well have been doing). But then, I got it aga... read more »
Comments (7) | Post Comment | Ask Ben | Permalink
MySQL: The Multi-part Identifier "u.id" Could Not Be Bound
Posted: December 18, 2008 at 9:19 AM by Ben Nadel
I had never seen this error before, so I thought I would just take a second to post it in case anyone tries to Google it. Luckily, my query was extremely small and so the error was obvious from a quick review of the SQL; but, in a large query this might not be readily apparent. Here's the kind of query I was running in MySQL: UPDATE [user] SE... read more »
Comments (2) | Post Comment | Ask Ben | Permalink
Getting ColdFusion To Work With MS SQL Server Express 2005 Databases
Posted: December 10, 2008 at 9:38 AM by Ben Nadel
Yesterday, after debating about whether or not to convert my blog to MySQL so that I could have a version of it running locally, I was persuaded by my esteemed Twitter colleagues to try using MS SQL Server Express since I do have SQL Server 2005 running in production. Downloading and installing SQL Server Express took the better part yesterday w... read more »
Comments (30) | Post Comment | Ask Ben | Permalink
Both MySQL and MS SQL Server Use @@Identity To Report Latest Auto-Incrementing Value
Posted: December 9, 2008 at 10:14 AM by Ben Nadel
ahixon2112 on Twitter just rocked my world! I was talking about converting from MS SQL Server to MySQL and theorized that one of the only things I would have to change is my use of @@Identity to get the most recently created auto-ID value. I thought that I would have to convert all those @@Identity statements into LAST_INSERT_ID() statements. Bu... read more »
Comments (28) | Post Comment | Ask Ben | Permalink
Exploring The Cardinality And Selectivity Of SQL Conditions
Posted: December 8, 2008 at 3:14 PM by Ben Nadel
After posting Rick Osborne 's comments about Selectivity in a SQL, I thought I would take my lunch break to sort of wrap my head around the concept a little bit more fully. After some Googling, I came across a good comment on Experts Exchange : the selectivity is what goes with the cardinality concept. the "cardinality" refers to the number of ... read more »
Comments (10) | Post Comment | Ask Ben | Permalink
Rick Osborne On Database Indexing And SQL Best Practices
Posted: December 8, 2008 at 9:01 AM by Ben Nadel
Over Thanksgiving, I wrote a blog post on the importance of database performance in data-driven applications . I had stated that since the database calls were most often the bottleneck in an application, it is absolutely essential that I, as a software developer, have really good SQL skills. I vented that I don't know nearly enough about database... read more »
Comments (9) | Post Comment | Ask Ben | Permalink
Random Thought: The Database Bottleneck And Elegance
Posted: November 29, 2008 at 9:39 AM by Ben Nadel
I forget exactly what I was doing, but last week, I had a random thought about application architecture and the database. From everything that I have been told, people say that the database is the bottleneck in a data-driven application. If that is the case, (which I would definitely agree with) then shouldn't the data layer be the most elegant, g... read more »
Comments (14) | Post Comment | Ask Ben | Permalink
MySQL Does Not Support IF / ELSE Statements In General SQL Work Flow
Posted: September 2, 2008 at 10:14 AM by Ben Nadel
This is just a minor note, but it took me a really long time to debug, so I thought I would put it up here. Apparently, IF statements and ELSE statements are not allowed in the general SQL processing flow of MySQL scripts. They are only allowed in functions and stored procedures. Looking back at my research, that makes sense - you can only ever fi... read more »
Comments (30) | Post Comment | Ask Ben | Permalink
SQL Join Table vs. SQL Entity Table And When To Switch
Posted: June 5, 2008 at 9:20 AM by Ben Nadel
I have to say that I have never been too educated when it comes to Database architecture. I have picked up tips here and there and found patterns that I really like, but overall, no one has ever sat me down and taught me the tried and true methods of database development. Occassionally, this comes to light. About a year ago, I was on the phone wit... read more »
