HTML5 SQL DB vs localStorage

databases web I've now built some mobile applications using both HTML5's localStorage and the HTML5 Embedded SQLite Database (aka Web SQL Database).

This entry was:

Use varchar(max) instead of text in SQL Server

databases As you may know on SQL Server a varchar column can hold up to 8000 characters (each row can hold up to 8K, so it depends on the size of other columns), when you need to store more it is common to use the text or ntext datatypes.

This entry was:

Creating a Derby Datasource with ColdFusion Admin API

coldfusion databases I am working on some example code for some CFUG managers who are demoing our ColdFusion WAF product at their groups. I wanted the demo to be very easy to setup, so I decided to use Apache Derby for the database, since it is embedded with CF8.

This entry was:

Cheat Sheet for SQL Server

databases I have been writing a lot of T-SQL Scripts for SQL Server for a client to migrate to a new database schema. Whenever I get deeply involved in something like that I find that it is handy to create a cheatsheet for all of the things you have to lookup in the docs.

This entry was:

DateFormat for SQL Server

databases Have you ever tried to find a DateFormat or date_format function in SQL Server? Chances are you probably ended up concatenating a bunch of DatePart strings to get the format you are looking for.

This entry was:

Top 10 Reserved SQL Keywords

databases You may know that I created a SQL Reserved Keywords Checker a few years ago. I was just noticing today that a lot of people are searching for the same keywords. Here's a list of the top 10 keywords people searched for using the tool in the past year:.

This entry was:

SQL Case Statement

databases Here's a simple example of a SQL CASE statement for Microsoft SQL Server:

This entry was:

Dear SQL Server Enterprise Manager Developer

databases Please add a button to make a column an identity.

You have a button to set as primary key, why do we have to scroll down through 3/4 of the properties, click the plus under "Identity", and then choose "Yes" for such a common operation?

Thank You

This entry was:


coldfusion If you haven't been using the cfqueryparam tag, chances are you had a baptism by fire this week. As you may have heard, lots of ColdFusion powered sites were targeted by hackers using SQL Injection this week.

This entry was:

Getting ColdFusion SQL Statements from SQL Server Trace

coldfusion databases Running a SQL Trace in the SQL Server Profiler can be a great way to track down performance problems in your ColdFusion application.

This entry was:

Try Catch for SQLServer T-SQL

databases Error handling in SQL Server T-SQL scripts is not always the most graceful thing to deal with. With the release of Microsoft SQL Server 2005 you can now use TRY / CATCH statements.

This entry was:


databases I've known about the INFORMATION_SCHEMA views (or system tables) in SQL Server for a while, but I just leared recently that they are actually part of the SQL-92 standard and supported on other database platforms.

This entry was:

Use Char instead of Varchar to Store UUID's

databases I know a lot of developers like using UUID's for primary keys instead of integers. But one thing I see alot is the use of the varchar datatype to store these strings.

This entry was:

Calculating Distance in Miles from Latitude and Longitude

databases The amount of data out there via API's is increadible these days. For instance you can take an address, and get the latitude and longitude using Google's GeoCoding API.

I am using this API along with some others to build a pretty some interesting stuff (more on that when its public).

This entry was:

Backwards LIKE Statements

databases Sometimes you need to think backwards.

Here was the problem. I needed to match up some IP address ranges to the company that owns them.

This entry was:

Toggle a Bit with SQL

databases web I am working on a management dashboard for a client, which uses some AJAX here and there.

This entry was:

Naming Tables

misc I am working on a project that has a database table called people. Now I am pretty sure that I was the one to name this table so I will take the blame, but it has been kind of hard to work with that name.

This entry was:

Sphinx - Open Source SQL Full Text Search Engine

databases I came across Sphinx today via the MySQL Performance Blog (which has some good entries you might want to check out). It is an Open Source Full Text SQL Search Engine.

This entry was:

Updated SQL Reserved Words Checker

databases I just updated my SQL reserved words checker to include a few new words from MySQL 5 that it wasn't picking up.

I use the tool when I'm designing a database table, or column. It can tell you if a value is a reserved word in MySQL, PostgreSQL, Oracle, or SQL Server.

This entry was:

MySQL FULLTEXT Indexing and Searching

databases MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL.

To get started you need to define the FULLTEXT index on some columns.

This entry was:

SQL to Select a random row from a database table

databases There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.

This entry was:

Cheat Sheet for PostgreSQL

databases When I created my list of cheat sheets for web development last week, I left out PostgreSQL. Frank Reiser, asked why it was left out - and the reason was simply that I couldn't find one.

Being a PostgreSQL fan, I decided to create a cheat sheet for postgresql myself.

This entry was:

Temporary Stored Procedures on SQL Server

databases I posted about doing fast bulk inserts with PostgreSQL last week, and with MySQL a while back. Now its time for Microsoft SQL Server.

I'm using a technique similar to what I used for PostgreSQL for SQL server.

This entry was:

Cheat Sheet Roundup - Over 30 Cheatsheets for developers

apple coldfusion databases java linux web Lets face it, unless you have a photographic memory, no developer can remember all the different functions, options, tags, etc. that exist. Documentation can be cumbersome at times, thats why I like cheat sheets.

This entry was:

Faster Inserts with PostgreSQL

databases As I mentioned yesterday, I'm working on optimizing lots of inserts into a database, and I need solutions for different DB servers. Today I have been working with PostgreSQL. Although the PostgreSQL COPY command is recommended, I can't seam to get it to work with ColdFusion, and cfquery.

This entry was:

Multiple Dynamic Inserts with SQL

databases If you have ever had to do lots of SQL INSERT statements you will know that they can get pretty slow. When looking for ways to speed up some inserts, I noticed that you can use a SELECT subquery to provide the values.

This entry was:

LIMIT and OFFSET SQL Pagination

databases Both MySQL and PostgreSQL support a really cool feature called OFFSET that is usually used with a LIMIT clause.

The LIMIT clause is used to limit the number of results returned in a SQL statement.

This entry was:

Insert Delayed with MySQL

databases I just discovered that MySQL has some handy optimization hints, and commands that you can pass in your SQL statements. Since there is a lot to cover with each tip, I will be posting them in separate blog entries. I have already written about the MySQL Query Cache optimization hints.

This entry was:

Performance of database tag schemas

databases Philipp Keller has done some performance test on 4 different database schemas for storing tagged items. Although his article refers to tagging bookmarks, you can tag pretty much anything.

This entry was:

Multiple Inserts with MySQL

databases Andy Jarrett posted this technique for insterting multiple rows (bulk insert) in one SQL statement a few months ago.

This entry was:

SQL: Distinct values from two tables

databases Today must be database day for me...

A question on my local CFUG mailing list asks how to remove duplicate values from two different tables:

I have 2 tables that store email addresses. One table is for newsletters and

the other is for registration to our site.

This entry was:

Related Posts

coldfusion databases As you may have noticed I recently added tags to my blog. The main reason I implemented tags was so that I could find related posts based on mutual tags. So if two posts have similar tags, they will show up in as related.

This entry was:

SQL Reserved Key Words Checker Tool

databases I just whipped together a tool for checking for reserved sql words. The tool checks against SQL Server 2000, MySQL 5.0, PostgreSQL 8.0, Oracle 10g, ODBC, and SQL Server Future Keywords list.

This entry was:

Finding Duplicates with SQL

databases Here's a handy query for finding duplicates in a table.

This entry was:

Client Variables unnecessary overhead?

coldfusion databases I was curious to know how much overhead client variables incur in an application.

This entry was:

Top 3 differences between PostgreSQL and MS SQL

databases I recently switched a database server from Microsoft SQL Server over to PostgreSQL.

This entry was:

Returning TOP N Records

databases Returning only the first N records in a SQL query differs quite a bit between database platforms.

This entry was:


did you hack my cf?