To the index

Databases

Beginner

How can I query an Excel spreadsheet as if it were a database?

<cfquery datasource="XLS" name="test">
   SELECT *
   FROM `INFO$`
</cfquery>

<cfoutput query="test">
   #test.Name#
</cfoutput>

to question

How can I remotely alter the table structure of an Access database?

You can manipulate a database's structure uring DDL (Data Definition Language). Put the SQL you want to run in a cfquery tag, upload it and call it from a browser. First, we'll add a column to the Employees table.

ALTER TABLE Employees
 ADD COLUMN StartedWork datetime NULL

When you add columns, you must always make them nullable by using the NULL keyword. Otherwise, the column will have no initial value and the alter table statement will fail.

This query adds a boolean column to the Accounts table.

ALTER TABLE Accounts
  ADD COLUMN ChangePassword yesno

This query deletes a column from a table.

ALTER TABLE Employees
DROP COLUMN Vegetarian

to question

How can I remove the Access lockfile?

Ever made a connection to a database that refuses to disconnect? That's because ColdFusion Server locks a table by default and may produce an error such as the following when attempting to edit a table

User dbo has exclusive use of database

To change this default setting, first log in to ColdFusion Administrator and select the ODBC link under the Datasources section. Then, select the relevant ODBC datasource and deselect the Maintain Database Connections option (you may have to choose Advanced Settings) and select Update. Restarting the ColdFusion server should put that change into effect and avoid locking the database in the future.

To do this programmatically, use

<cfset rc=cfusion_disable_dbconnections("cfmysource","1")>

to disable a specific datasource, and

<cfset rc=cfusion_disable_dbconnections("cfmysource","0")>

to re-enable it.

<cfset CFUSION_DBCONNECTIONS_FLUSH()>

The invalid query worked in ColdFusion Server 4.0 and previous versions. It was a bug that was fixed in 4.5. The way to keep Access databases from being locked is to uncheck the Maintain database connections checkbox in the ColdFusion Administrator ODBC settings. This way Access databases will never lock, even if they are being accessed. Of course you then need to be careful about updating the database. You need to make sure no one can update the database on the web site while you are updating. It's also reccomended that you keep the buffer size at 0 for Access databases.

to question

How can I store CFML in a database record and get ColdFusion server to parse it after retreival?

Write a temporary file containing the CFML retreived from the database. Then cfinclude it in the required template.

Unfortunately this may incur a big performance penalty, but it's the only way to achieve it.

to question

How can I use an Excel spreadsheet as a datasource?

The ODBC source points to the file. The "table name" is the sheet name with a $ on the end, but always put square brackets around it; [Sheet1$]

Row 1 MUST, I repeat, MUST be the column names

Once a column is decided upon (setting in the ODBC) for it's data type, it's GOT to be that datatype, other types will be ignored (i.e. putting some text in a column that starts as numeric will return a NULL). Formatting for visual needs means nothing to ODBC, it's the actual data which determines it's type - you must FORCE it to be text if you want text

On this point, unless you want to release the ODBC before you upload, then use an Access database and Link Table to point to it, but the same ODBC limitations apply

How to force a cell to be text;
You need a "copy" column beside it and put the following formula into it;
=Mid("'"&a2,2,Len(a2))
Assuming that A2 is the cell that contains the original data

Also, Sometimes you get blank rows at the bottom of the table, these DO count as rows - so be very careful about this.

to question

I'm having problems accessing a SQL Server database using Enterprise Manager. What might be going wrong?

Ensure that you have enabled the TCP/IP network protocol on the server (the default is Named Pipes) and that you are trying to connect to that database using TCP/IP. You can determine what protocol you are using and add protocols by running the SQL Server Setup utility.

There is a step-by-step guide at
http://www.shanje.com/sqlentman.shtml

to question

What are the differences between Access and SQL Server?

AutoNumbers are Int fields with an Index
Indexes can be applied at any time, not just on a new table
InStr and Mid become CharIndex and SubStr
You can't refer to a date within # anymore, SQL Server uses '
Lists for IN() can't end in a , anymore
Data isn't written immediately, it's queued
SQL Server can return the Index field within the SQL
You can set-up Stored Procedures to do complex SQL commands and just call them within SQL
You can by-pass the record locking to improve speed
You can alter a table definition while it's still in use

to question

Advanced

Can ColdFusion connect to the Windows version of MySql?

Yes.

to question

Can ColdFusion query a FileMakerPro database?

FileMaker Web Companion uses Blue World's Lasso technology
http://www.blueworld.com/

You can use CDML (Claris Dynamic Markup Language) with out-of-the-box FileMaker Pro. It is possible to query a FileMaker Pro database using their standard ODBC drivers. If the database is going to be accessed via the Internet, you will have to use the FileMakter Pro 5 Unlimited version as the standard FileMaker Pro 5 has a limit of 10 IPs per 12 hour period.

You will find some info going to Technical Support on FileMaker's web site and searching their knowledge base on 'ODBC' and 'SQL' under FileMaker Pro 5
http://www.filemaker.com/

to question

How can I add Access DSNs programatically in CFMX?

to question

How can I migrate an existing Access database to SQL Server?

Microsoft SQL Server is the database solution of choice for small- and large-scale sites alike. Many developers often start out with Access as their database solution, but quickly realize the advantages of the more robust and scalable SQL Server. As a result, they spend time migrating their Access database to SQL Server. If you're planning on doing just that, the Microsoft Access Upsizing Tool will help. This tool provides a clear path to SQL Server 7. (Or so I hear, anyway.)

http://www.microsoft.com/accessdev/prodinfo/aut97dat.htm Microsoft Access Upsizing Tool
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=6133

When you upsize make sure SQL doesn't change any fields to ntext as Cold Fusion can't deal with them. Also make sure that all date fields are datetime and not small date time as when you import the data you may ge a data overload for some of the rows. I had this problem when transfering from Paradox and Interbase db's. One piece of advice I would give is to use the data import within SQL instead of the upsizing wizard in Access. Let the SQL engine do the work.

to question

How can I obtain a list of datasources on a server?

It specifically looks for Access, but you get the idea

<cfsetting enablecfoutputonly="Yes">

<cfset Results = QueryNew( "Datasource,Server,Path,Description" )>

<cfset RootKey = "HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI">
<cfregistry action="GetAll" branch="#RootKey#" name="Sources" type="Any" sort="Entry ASC">
<cfloop query="Sources">
    <cfset Foo=QueryAddRow( Results )>

    <!--- stash the source name --->
    <cfset Source=Sources.Entry>
    <cfset Foo=QuerySetCell( Results, "Datasource", Source )>

    <!--- Note that on the path and decsription fields, we have to --->
    <!--- manually clear them before calling CFREGISTRY, because they --->
    <!--- will not be cleared if the registry entry doesn't exist, and --->
    <!--- CFREGISTRY doesn't provide an error --->

    <!--- Stash the description --->
    <cfset Desc="">
    <cfregistry action="Get" branch="#RootKey#\#Source#" entry="Description" variable="Desc">
    <cfset Foo=QuerySetCell( Results, "Description", Desc )>

    <!--- stash the path --->
    <cfset Path="">
    <cfregistry action="Get" branch="#RootKey#\#Source#" entry="DBQ" variable="Path">
    <cfset Foo=QuerySetCell( Results, "Path", Path )>

    <cfset LeadingSlashes = SpanIncluding( Path, "\\" )>
    <cfset StartPos = Len( LeadingSlashes ) + 1>
    <cfset NextSlash = Find( "\", Path, StartPos )>
    <cfif NextSlash EQ 0>
        <cfset Server = "(Not a disk file)">
    <cfelse>
        <cfset Server = Left( Path, NextSlash )>
    </cfif>
    <cfset Foo=QuerySetCell( Results, "Server", Server )>
</cfloop>

<cfset Caller.CF_Datasources = Results>

<cfsetting enablecfoutputonly="No">

to question

How can I remotely add a table to an Access database?

The following is a series of SQL DDL statements. With Access, you must include each group of statements in its own cfquery block as you can't execute multiple SQL statements by separating them with a semicolon as you can in many other RDBMSs. First, we create a table

CREATE TABLE UnitsOfMeasure (
   UnitOfMeasureID counter NOT NULL,
   UnitOfMeasure text(50) NOT NULL,
   Abbreviation text(10) NOT NULL)

specify the primary key

ALTER TABLE UnitsOfMeasure ADD CONSTRAINT
      PK_UnitsOfMeasure PRIMARY KEY (UnitOfMeasureID)

add a few values into the table

INSERT INTO UnitsOfMeasure(
          UnitOfMeasure,
          Abbreviation)
   VALUES('Litres',
          'ltrs')

   INSERT INTO UnitsOfMeasure(
          UnitOfMeasure,
          Abbreviation)
   VALUES('UK Gallons',
          'UK gals')

   INSERT INTO UnitsOfMeasure(
          UnitOfMeasure,
          Abbreviation)
   VALUES('US Gallons',
          'US gals')

add a foreign key column in another table

ALTER TABLE Sites ADD
      UnitOfMeasureID integer

give all the records a default value of 1 for the foreign key

UPDATE Sites
      SET UnitOfMeasureID = 1

and then specify the relationship between the lookup table and the foreign key in Sites

ALTER TABLE Sites ADD CONSTRAINT
      FK_Sites_UnitsOfMeasure FOREIGN KEY (UnitOfMeasureID)
      REFERENCES UnitsOfMeasure (UnitOfMeasureID)

to question

How can I store images in a database using the BLOB data type and then output them again?

There are two custom tags out there to help you out: cfx_putimage and cfx_getimage.

Create a ColdFusion page called "showImage.cfm" and pass it paramaters to uniquely identify an image. The CF page then sets the content type to, for example, "image/gif", then return the binary data.

<\img src="ShowImage.cfm?ImageID=3456">

Microsoft also has an article about serving images from a BLOB field using SQL server. The example is in ASP, but the theory is the same for ColdFusion
http://support.microsoft.com/support/kb/articles/Q173/3/08.asp

This content type reference might also be useful
http://www.utoronto.ca/ian/books/html4ed/appb/mimetype.html

to question

How can I upsize the Siteminder Acess database to SQL Server?

http://www.allaire.com/Handlers/index.cfm?ID=14566&method=Full

to question

How can I use a text file as a datasource?

Text ODBC datasources are actually set to directories, not the file, then in the query you use

SELECT Field1,
       Field2
  FROM MyFile.txt

where "MyFile.txt" is the name of the file.

to question

How do I get the unique identifier value of a record inserted into an Autonumber field?

It depends what DBMS you are using.

The simplest way is to write an ON INSERT trigger which returns the value of @@IDENTITY when you do the insert. This is only possible in SQL Server. You may want to use a stored procedure instead (see below).

If you are using another DBMS which supports stored procedures, eg Oracle, you can wrap the insert into a stored procedure and return the ID from that.

If the DBMS does not support stored procedures (eg Access), you have to make another query to the database immediately after the INSERT in order to retrieve the ID. One way is to SELECT MAX(ID). The problem here is that if another user has added a record between the two SQL operations, you will get the wrong ID back. This can be avoided by wrapping the two operations inside a database transaction. Alternatively, you can SELECT ID ...WHERE (all the field values match). This is less efficient but could be necessary if the DBMS does not support transactions.

An alternative to all these is to avoid using autonumbered fields at all, and to manufacture a unique ID in the CFML template that does the INSERT - for example, by using CreateUUID().

to question

How do I uninstall MDAC?

There's a utility available on Microsoft's site to uninstall MDAC Components and ensure that you're completely removed them. The utility's name is clsidvw.exe and it can be downloaded from support.microsoft.com. The following knowledge base articles should help you track it down:

HOWTO: Uninstall MDAC 2.0 Components
http://support.microsoft.com/support/kb/articles/q192/0/13.asp

Clsidvw.exe OLE/Createable Objects Registry Diagnostic
http://support.microsoft.com/support/kb/articles/q176/5/62.asp

If you uninstall Option Pack 4, Service Pack 4, or manually uninstall whatever version of MDAC that you're running I believe that the OS does not completely remove the MDAC components. This utility will tell you what components are installed on your system (Win95/98/NT), what version they are, and what components are missing from that version.

to question

How do you configure ColdFusion to access a SQL Server database?

1. Create SQL Server 7 database
2. Create a login account for the application that's going to read from/write to that database
3. Give that account the required permissions and access to that database
4. Go to the box that the site is going to be hosted on
5. Set up a SQL Server DSN from that machine to the SQL box
6. In the ODBC DSN settings, specify that is uses a SQL Server login (not NT) and provide the username/password combo for the account you previously created.
7. Also, click on Client Configuration and make sure it's using TCP/IP and not Named Pipes (the default, I think)
8. Test the datasource connects successfully
9. Go into the ColdFusion Administrator on the same box
10. Click on ODBC
11. Click on the DSN you've just created
12. Click on CF Settings
13. Enter the username and password into the username and password fields and update
14. Your queries should now by just referring to datasource="#YourDSNVarHere#"
15. You now only have to pass the DSN in the CFQUERY tag.

to question

Is it possible for ColdFusion to query a FoxPro database?

Yes. FoxPro database structures are derived from DBase. The way you set up the datasource is that the datasource does not point to a file (like an Access MDB) but rather to a directory. Then, any database files in that directory can be used in a query (note: you can also use files that are not in the specified directory as long as you use paths). One weird thing about DBase (and maybe FoxPro as well) is that the default in the ODBC setup is for ODBC to return even deleted rows. If FoxPro is the same as DBase then you need to disable this manually from the ODBC setup (not from CFA).

FoxPro ODBC drivers, like DBase, are single threaded (and blocking) which means that only one query can be running at one given moment. If you are doing lengthy queries you will notice a degradation in other simultaneous queries.

to question

Is it possible for ColdFusion to use an Access database on a different server?

ColdFusion has to be configured to login as a user with rights to the access table on the other server. By default, ColdFusion logs in as the System account which does not have rights to other server resources.

to question

Is it possible to call a DTS package on command?

cfexecute dtsrun.exe "MyPackageName"

or you can run a stored porcedure which will do this.

to question

Is it true that the Access ODBC driver creates a memory leak?

Apparently so. There is an article on the MS KB - MS have confirmed the leak in the MS Access ODBC driver, and even provide some sample code to show it off. The leak occurs when a connection is made, so if you've got an xSAPI app (CF, iHTML, ASP, etc.) that uses ODBC to MS Access then it will gradually consume memory after each connection until the system dies. If you use something like CGI where the process doesn't run within WebSite then memory appears to get cleared up OK, but I can't confirm it as my ASP pages us the OLE DB provider for Access (guess what, no visible leaks! - my ASP site's been up for ages).

The MS articles are at:

PRB: Memory leaks with Desktop Database Drivers
http://support.microsoft.com/support/kb/articles/q132/4/93.asp

BUG: Memory Leak in Microsoft Access ODBC Driver
http://support.microsoft.com/support/kb/articles/q195/0/17.asp

to question

Is there a Personal Oracle Server that can be used for development?

Yes, there's Personal Oracle 8i and Oracle Lite. For development purposes, you'd probably want the first one. Also, you can run the full-blown Oracle 8i on your machine; it's available for development purposes for free, essentially
http://technet.oracle.com/

to question

Is there a way to alias several columns in an Excel spreadsheet and consolidate data into one column name?

in Excel the formula

=A1&B1&C1

will merge all the Data in those three columns together. Alternatively you can use

=Concatenate(A1," ",B1," ",C1)

which will merge all the Data in the three columns together, separated by a space.

to question

Should you index foreign keys in a database table?

Yes, you'll generally want your foreign keys to be indexed. Any fields commonly used for filters or joins should be indexed, if those fields will benefit from an index.

Every table should have a primary key, and within a linking or intersection table, the natural primary key consists of the two foreign keys. However, in some cases, you may want to use a surrogate primary key instead; for example, if you want to track historical information about previous intersections, you could do this by using a surrogate key, and a field to track the persistence of the intersection. Here's an illustration:

Item_Category intersection table

Item_ID
Category_ID

Item_Category intersection table, modified for historical data

Item_Category_ID <- surrogate primary key
Item_ID
Category_ID
Date_Created
Date_Removed

With the second table, to find current intersections, you'd look for entries in the intersection table with a NULL Date_Removed value.

There are other ways of dealing with historical data as well.

to question

What are the benefits of SQL Server 2000 over version 7?

Functions (a true lifesaver)
XML Support (both into and out of the database)
Improved stability

Also, although it's not a major reason to upgrade, the client tools that come with SS2K contain some nice enhancements. If you're building complex stored procedures, Query Analyzer's built in SP debugger is an excellent tool.

One thing to keep in mind - SS7 performs better on NT than SS2K. So if you're on NT, you may want to stick with SS7, but if you're on or moving to W2K, you'll get a performance boost from SS2K.

to question