ColdFusion in Context: Downsizing Data to Access

Start with Access Datatypes

• bit

  Also known as yes/no, true/false, or boolean. A "no" or "false" is zero and a "yes" is something else. If you leave this as "bit", then yes appears to be 1 and no to be 0, and you can test that way in both the database and the code. However, if you convert this a bit field to a numeric field, then the database and code will perceive yes to be -1!

• byte

  Handles an integer from 0 to positive 225.

• single

  Handles a positive or negative floating-point number from roughly 10 to the minus 38th to 10 to the 38th. (The actual ranges are a bit larger than shown here, but these approximations are close enough to help you choose a datatype.)

• double

  Handles a positive or negative floating-point number from roughly 10 to the minus 324th to 10 to the 324th.

• short

  Handles an integer from roughly negative 32 thousand to positive 32 thousand.

• autoincrement

  Holds an integer that increases by one whenever a record is added (up to roughly 2 trillion). This is the same underlying datatype as long, and any field that must act as a foreign key for an field of type autoincrement must be type long. (See long.)

• long

  Handles an integer from roughly negative 2 trillion to positive 2 trillion.

• datetime

  Holds a value that the database engine converts to a date and time.

• text (with an explicit length)

  Holds a string up to the specified length, not to exceed 255 characters.

• memo

  Holds a string as long as you're likely to need. However, you can't index it or readily do SQL searches on its contents.

• currency

  Holds a positive or negative fixed-point number with fifteen digits to the left of the decimal point and four digits to the right of the decimal point. Although this datatype holds exact values, fractional computations with them will quickly accumulate inaccuracy. If you were viewing numbers of this datatype through Access pages, they would display as dollars and cents (or equivalent) with the remaining accuracy hidden, but because you're not, using this datatype doesn't convey even this advantage in a Web environment. Consider using the datatype of single instead.

Consider Conversions

• Replace [bit] with bit.
• Replace [byte] with byte.
• Replace [numeric] with single; remove the information that follows it in parentheses.
• Replace [decimal] with single; remove the information that follows it in parentheses.
• Replace [money] with single.
• Replace [single] with single if you find it.
• Replace [real] with double.
• Replace [double] with double if you find it.
• Replace "[int] IDENTITY (1, {some number})" with datatype autoincrement; remove the original datatype, IDENTITY, and the information in parentheses. This datatype is useful when the actual number is NOT being checked by the application.
• Replace "[int] IDENTITY ({not 1}, {some number})" with long; remove the original datatype, IDENTITY, and the information in parentheses. The problem here is that someone has specified that the autonumbering must begin with a value other than one and therefore is probably assigning a special meaning to the numbers themselves in the application: looking for a "3", for example. Where this is the case, database autonumbering is really not appropriate. You'll need the values in this column to exactly match the values from the original database. Accept the original values into a column of long datatype; then modify the table by hand (or the code) to make things right.
• Replace remaining [int] with long. Some of these columns will be used as foreign keys for autonumbered columns in other tables. Since the autonumbered columns are long integers, the corresponding foreign key columns must also be datatype long. Once you get the data moved over and you're sure certain columns won't be used as foreign keys, you could change the datatypes of those specific columns manually to short later as desired.
• Replace [datetime] with datetime.
• Replace [text] not followed by a size in parentheses, or followed by a size greater than 255, with memo.
• If you encounter [text] followed by a size no larger than 255, replace it with text and the given size.
• Replace [char] with text and the given size.
• Replace [varchar] with text and the given size.

Use the Scripts

Open Microsoft Access. Get to the page that lets you enter an SQL query. Then paste the script for one table into this page, execute it, take care of any highlighted problems, and repeat until you've created all the tables you need. This is much faster than doing the task manually. You can easily add ten tables per minute this way.

Copy the Data

When using Microsoft Access, import the spreadsheet data one worksheet at a time to the tables you've created. This is also a fast process.

If you have to use text files for transfer, you may have to load dates converted to text into text fields, then convert them back to dates. The spreadsheet route is faster if it's available to you.

Hints

- If you want to use mostly the same code for both database engines, one way to make sure the changes you make for an Access demo aren't run during production is to limit their execution to the local host (127.0.0.1). For example:

<cfif left(cgi.server_name, 3) is "127">....

- You can't take a field being produced by the query you're trying to build and put it within a ColdFusion function; because, the field doesn't exist yet! This means that you need to figure out the Access way of handling dates (for example) or handle the conversion completely outside the database.

- A common problem that comes up is how to compare the current date/time with a date in the database:

-- The MS SQL way uses native database functions to get the current datetime and make the comparison to see if an hour has elapsed; the database handles both processes:

where (DateDiff(hour,SubmitDate,getdate()) > 1)

where SubmitDate < #dateAdd("h",-1,now())#

- Access is said to be less restrictive than other engines. However, in this context, you'll find it to be more restrictive.

-- When you use the scripts above to define a field as not null, then you must supply a value for the fields when you insert a new record. MS SQL lets you leave them undefined; Access doesn't.

-- If the code uses variables for column names, note that Access doesn't want you to alias a field with its own name. For example, using variables that evaluate to a pair like this this is a no-no:

<--- bad code ---> select SubmitDate as SubmitDate

- Access handles inexact matches differently from other database engines. For example, you'll probably replace a construct like "charindex('#ucase(attributes.VendorEmail)#', upper(Email)) <> 0" with "instr(Email, '#attributes.VendorEmail#') <> 0" when moving from another database engine to Access. (The "like" function syntax varies and would be slower than a solution like this.)

- Generally, when you're having trouble with a difficult construct, the problem is usually something simple such as the differences above. Before you give up on a difficult construct, re-check the basics (such as data types).

- Access does handle joins differently from other database engines. Fortunately, you can use Access to construct the join you need by dropping and dragging fields and then can copy the resulting SQL to your application.

Just getting the data into Access is a worthy first step. Once you've done this, enough of your application will work that you can figure out the rest. This will give you a floppy-size demo that can make your point on the road. =Marty=