ColdFusion Tips and Tutorials

I. Comments:

Hope your mother's day went well. I am ready for summer. If you have some topics you would like used please send them in. We may not be able to get to them right away but it sure helps us to know what your looking for.

NathanS<at>nsnd.com

P.S.
A New List for those looking for Jobs, Employees or just watching the market.

To send a message: web_jobs<at>topica.com
To subscribe: web_jobs-subscribe<at>topica.com
To unsubscribe: See the Unsubscribe link at the bottom of any message.

Keep Coding,
Nathan Stanford
http://www.cftipsplus.com

Macromedia Team Member
http://www.macromedia.com/support/forums/team_macromedia/team_members/112.html

If you have suggestions for articles send them to us.
If you would like to write for cftipsplus.com
send us an email to:

NathanS<at>nsnd.com

IF YOU WANT TO BE AN AUTHOR SEND IN YOUR COLDFUSION TIPS.

Remember this is a great way to get your name known in the
ColdFusion Community.

*Fusebox 4 released, more details at CFUN-03*
Fusebox version 4 was release in Atlanta GA today 5/7/03. Fusebox 4 will add new features using XML and compilation of the switch file. It also includes many ideas from FuseQ, the version of Fusebox written by John Quarto-vonTivadar that allow for multiple fuseactions and streamed output into CSS and Flash front ends. If you can't make it to Atlanta there will be four talks on Fusebox 4 at CFUN-03. The speakers include Hal Helms, Jeff Peters, John Quarto-vonTivadar and Sandra Clark. For more information see http://www.cfconf.org/cfun-03/

Suppose you want to build a demo based on a full application that runs in a large database engine such as Oracle or Microsoft SQL. Perhaps the demo needs to go on a laptop that will handle Microsoft Access but not anything larger. Perhaps the entire demo and its data must fit on a floppy. This tip helps you move data structures and content into Access for situations such as this.

Start with Access Datatypes

Major database engines can generate data description language: scripts which could be used to create the tables in their databases in the first place. The challenge is in modifying these "create table" scripts to produce comparable structures in Microsoft Access. Just as with any maze, the easiest way to solve it is to start with the end and work backward. Here are some common data types that can be interpreted directly by Access if you paste them into a query. See what's available and reasonable to use in Microsoft Access and then determine how these datatypes compare with the source of the data.

• 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

Now that you know what's available, here are some conversions to consider. Note that the script that the original database would have used to create its tables will probably enclose the datatype in square brackets, and you'll have to at least remove the brackets to make Access happy. Bearing that in mind, here are suggested 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 a copy of the script in Microsoft Word or a word processor of comparable power so you can replace all "\[numeric\] \(*\)" in wildcard mode with "single". (The backslash escapes special characters that you want treated as normal characters during the wildcard search and replace.) Make the remaining conversions above in a similar manner. If you see other things that Access won't like (such as "[dbo]." prefixes and " ON [PRIMARY]
GO"), get rid of them while you're at it.

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 SQL, if you poke around long enough in this program or its supporting programs, you'll find an option that lets you export data to an Excel spreadsheet. Do NOT use any Excel version lower than version 5. Version 5 will place each table in a separate page of the workbook and will let you export the entire database at once (if tables don't exceed Excel's limitations). However, version 4 and below will try to use named ranges in a single page and will usually fail unless you export one table at a time, a tedious process.

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

Most of your application's queries will now run as is. As you're tweaking the ones that don't, here are a few things to keep in mind:

- 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=