SQL Server 2008 and ColdFusion 9: no problems, right?

Sure, once it's set up. lol.

ColdFusion and SQL Server talk to each other very well, except when they don't. In a development environment, like, say, your laptop, they don't talk at all by design: CF wants to connect via TCP/IP and SQL Server ships with TCP/IP disabled by default.

So, thanks to Adobe help, Ben Forta's post, and Marc Esher's post, I got it working ...

• Follow the instructions in Esher's post above to open TCP/IP services in your SQL Server instance (enable TCP/IP, click on Properties, IP Addresses, delete all Dynamic Port values, and enter 1433 as the TCP Port, assuming you do have it running on 1433).
• Follow the instructions in Forta's post as well (enable Windows and SQL Server authentication, create a SQL Server account for ColdFusion[1], and give it access to the database in question).
• In the CF Administrator, enter the username and password for the SQL Server account you just created[2], leave the server name blank, and enter the name of the database to which you want to connect.

That's everything, I think, although I probably skipped over some of the painful details. Anyway, it's more complicated than it should be, and it's not all on Microsoft: there are plenty of JDBC connectors that don't have this issue. Adobe just hasn't improved their built-in connector.

---

[1] Unless, of course, you already have ColdFusion running under a separate user account, which is good, in which case you simply let CF log in using Windows Authentication. (return)

[2] Username and password are required only for SQL Server authentication; for Windows authentication, neither are required (because the user is already logged in). (return)

When is SQL Server not SQL Server?

When it's MySQL, of course.

(note: programming-heavy content. Normal sports, gaming, and random content resumes tomorrow.)

You see, it goes like this. We're using ColdFusion 8 and want to use Windows Authentication to login to SQL Server databases (2005, for the most part). Of course that's not directly supported, so we have to set up the data sources as Other and use Microsoft's JDBC driver to connect.

Now for the most part, that's not that big a deal (once you remember that you have to grant access to that user to the database itself - oops on my part). But when a sequence is involved ... (tangent: SQL Server is absolutely stupid when it comes to sequences. I have no idea why they don't separate them from tables like Oracle does)

So when I add a record to a table, either I have to use janky code that runs multiple queries in a single cfquery tag, or I can take advantage of the new feature in CF 8 that returns the identity (SQL Server's name for sequence) value that was just used. Duh.

Of course Adobe "helpfully" set it up so that each database returns a different key-value pair for the same purpose, rather than using a single key for any database. That would have been entirely logical and reasonable. And of course Microsoft's driver prevents CF from realizing that it's connected to a SQL Server database for these purposes, so as a result, it returns the key for a MySQL database (GENERATED_KEYS) instead of the one for a SQL Server database (IDENTITYCOL).

not. helpful.

+1 to me. -1 to Adobe. times 2.

A helpful tip for saving a date and time in Access using ColdFusion

two steps ...

1. Use odbccreatedatetime() so that you have an ODBC object with both date and time, otherwise all Access gets is a date.
2. Use cf_sql_timestamp (you are using cfqueryparam, right?), so that the ODBC driver understands it's getting a date-time parameter.

Ta da! Date and time in your Access database.

Follow-up

Today, I start by discovering a solution and a problem.

Solution: IE 7 and Firefox 3 do not treat <button> elements the same. If IE sees a button of type submit, it will assume that what you want to do when someone clicks this button is to submit the form; if there is any JavaScript in the onclick event, it will run that in addition to submitting the form. Firefox sees the JavaScript and runs it instead of submitting the form.

This is kind of a problem if your JavaScript calls the submit() function. :)

So I changed the type from submit to button and everything was fine.

Then I found my problem. We have passwords in a database, I want to check the user's password against the database password. Done: I use <cfqueryparam> in the query to ensure they don't put funny little things in there.

But you can also change your password. If you do that, and you log out, when you log in again, I want to check for your new password. I can't do that if the query is cached. The problem is that I can't make the query un-cache (we don't have CF 8 on this server, although I'm not sure that would help much) because you can't use cacheafter or cachewithin when you use cfqueryparams.

So I need a solution for that.

After a sub-optimal day, I return home and find poop in different places, of course. Here's one spot, right by the box I want to use to return my defective headphones. And look, there's something sticking out of it, like a twig. (Um, these are indoor cats ...)

And I pick it up, but it's light, and I drop it on my hand ... oh, at least it's dry ... wait a minute.

That is kind of a twig. This isn't poop. It's a leaf.

d'oh.