Stillnet Studios

I had an issue recently when I was working with a couple other developers on a project. We keep our MySQL database in sync by exporting the database as an SQL script and sharing it in a subversion repository (I wish MS SQL could be this easy!). Our table names are saved with mix case, like “userFiles”, but everytime I would export my database the tables would come out all lower case, like “userfiles”.

Turns out this is a simple thing to fix, I had to add a line to MySQL’s config file: my.cnf. Mine was located at [MySQL installed root]\bin\my.cnf. The setting that needs to change is lower_case_table_names, I set the value to 2, like this:

lower_case_table_names=2

If you are interested, here are the different possible values:

On Windows the default is 1.

More information at http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

The other day I had a need to be creating dates in a SQL Server SQL statement, made from a few different text fields. I could not find a function to create a date from these, so I wrote one (with the help of our in house SQL guru).
Continue reading ‘A ‘MakeDate’ function for MS SQL Server’ »

There may be something buggy here, or maybe I’m misunderstanding how this should work.

This code executes fine:

<cfquery name="qryListings" dbtype="query">
SELECT 1 as OrdBy, * FROM qryListings
UNION
SELECT 2 as OrdBy, * FROM qryListingsTmp
ORDER BY OrdBy
</cfquery>

But when I add a WHERE clause to the second part to filter out records that were in the first group:

<cfquery name="qryListings" dbtype="query">
SELECT 1 as OrdBy, * FROM qryListings
UNION
SELECT 2 as OrdBy, * FROM qryListingsTmp WHERE ref_num NOT IN
(#ValueList(qryListings.ref_num)#)
ORDER BY OrdBy
</cfquery>

I get a ColdFusion error about the number of columns being different between the two queries.

Continue reading ‘A bug with Query of Query and * ?’ »

I ran into this issue when adding a new datetime column to an existing table in my MySQL 5 database. I did not get the error in my production environment, only the dev environment on my local machine.

I was puzzled by this error, because I thought the all zeros value basically meant a null/unknown date value. Also I wasn’t using a type TIMESTAMP, I was using a DATETIME column type, so that threw me, too. After digging through the data a little bit, I found that the values for these new columns in the production database were NULL, but the values in my dev database were ‘0000-00-00 00:00:00′. A little googling told me that the all zeros value is what happens when an invalid date is assigned to a datetime field.

I could not see any differences in the schema, so I’m not sure how this happened. But updating my dev data to be NULL where the value was ‘0000-00-00 00:00:00′ solved the problem.

The other option, if you can’t change your data, is to add these items to your connection string value in the ColdFusion Administrator for your datasource:

noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull

This tells MySQL to return a null instead of throwing an exception when one of these values is found. This is how I initially solved the problem, until I figured out that ‘0000-00-00 00:00:00′ was not a proper value.

I’ve never found a great solution for pagination in SQL 2000. I much prefer MySQL’s syntax: SELECT * FROM table LIMIT 100,10. That will get you 10 records starting at the 100th record. Why MS SQL still doesn’t offer an opposite to TOP, I don’t know. Yes, I’ve seen the double TOP method where you sort your data twice to end up with the last X rows. It works, but its not exactly elegant.

But I have come across a new method for pagination using some new 2005 features that is pretty neat. What I really like about this solution is that it gives you a count of the total number of records, which you usually don’t get with other pagination methods.
Continue reading ‘Elegant pagination with MS SQL 2005’ »

My SQL savvy coworker Jason Troy has done some work recently on finding and deleting duplicate records in a database. One of the things he had to do was compare on a text field (that is, over 8000 characters and not a varchar). Very interesting.

http://phillyun.blogspot.com/2008/10/power-of-sql-find-and-delete-duplicates.html

I’m setting up mysql locally on my laptop this weekend, and ran into a snag that cost me at least an hour. To mimic a web app I am running locally, I needed to connect to mysql with a specific username and password. But no matter what I did, I always got this error:

MySQL: access denied for user: username (Using password: YES)

I got this when connecting via the command line client and via ColdFusion. Finally after some googling I came across this blog post suggesting to change the privilege details to allow connecting from ‘localhost’ instead of ‘%’. The % indicates a wildcard and should allow connections from any host. But for some reason this was not working for me. Changing it to ‘localhost’ fixed the problem and allowed me to connect. I think it may have had something to do with the fact that I checked a box when installing that would only allow connections from the localhost.

I used this SQL to fix the host setting after connecting to the ‘mysql’ database:

update user set Host=’localhost’ where User=’myUserName’

I’ve used this trick several times when needing to sort data into groups. For example say you need to sort car classified listings by price. Some of the dealers enter a price of “0″ for their listings. When sorting by lowest price, you want the listings that actually have a low price to come first, rather than than the cars costing $0 (hey, I’d like to buy your free car, thank you).
Continue reading ‘SQL CASE statement with NULLs’ »

I needed to clean out some orphaned records from a MySQL database today. I thought this would be as simple as

DELETE FROM subTable WHERE ID IN (
  SELECT subTable.ID from subTable
  LEFT OUTER JOIN parentTable ON subTable.parentID = parentTable.ID
  WHERE  parentTable.ID IS NULL
  )

But this resulted in an error:
Continue reading ‘Cleaning up orphaned records in MySQL’ »

We had a need to do a case sensitive SQL comparison this week. As part of a login process a user is prompted for an answer to their security question. Now I really don’t think forcing users to enter their employer’s name in the exact case does anything to keep out hackers, but the client wanted it done this way.

Of course we could pull the values into ColdFusion and do a case sensitive comparison with Compare(). But the code was already setup to do the comparison at the SQL level, which is usually a better idea anyway if it can be done.

There are several ways to tell MS SQL to do a case sensitive comparison. Continue reading ‘Case sensitive comparisons in SQL’ »