To store client variables in a database, your ColdFusion Server must be specially configured to do so. By default, client variables are stored in the Registry (or an equivalent if ColdFusion is not running on a Windows system).

In ColdFusion MX:

1. Open the ColdFusion Administrator.
   
2. Under Server Settings click Client Variables.
    
3. Under "Select Data Source to Add as Client Store" choose the already set-up datasource that you want to make a client variable repository (you can use more than one datasource. See below). Once you have found and selected the desired datasource, click 'Add' (If you have not yet set up the datasource you must do so under Data & Services in the Data Sources area).
    
4. You will be taken to the client data source creation screen. Type in a description of the data source. Leave the onscreen defaults alone (although you may want to change the default purge interval to a much shorter number until you are familiar with how your db will grow, and know for sure what your needs are) and click 'Submit Changes'.
    
5. Check your data source. Are there newly created tables named CDATA and CGLOBAL in it? There may or may not be. in mySQL, for instance, ColdFusion does not create the tables. You may have to create them yourself. Here are the ColdFusion queries that will construct a datasource in mySQL:

<cfquery
     datasource="mydatasource">
     CREATE TABLE cdata (
          cfid varchar(64) NOT NULL default '',
          app varchar(64) NOT NULL default '',
          data longtext NOT NULL,
          PRIMARY KEY (cfid,app)
     );
</cfquery>

<cfquery
     datasource="mydatasource">
     CREATE TABLE cglobal (
          cfid varchar(64) NOT NULL default '',
          data longtext NOT NULL,
          lvisit timestamp(14) NOT NULL,
          KEY cfid (cfid),
          KEY lvisit (lvisit)
     );
</cfquery>

 

Note the composite primary key in the CDATA table. The primary key is the contents of TWO fields: The CFID AND the application name. If you create a key based only on CFID then the user will have the same client variables for multiple web sites hosted on the same server, which can be catastrophic depending on your application. Also, note that there is NO primary key in the cglobal table.

 

Note that this is not the way that ColdFusion would have set up these tables had it built them itself. In an Access database, the CDATA table will not have the cfid and app fields indexed at all, nor will they be set as a composite primary key.

6. Set the default client data storage repository.  The area entitled "Select Default Storage Mechanism for Client Sessions" now includes the datasource you just created. Select it as the default and click 'Apply'. It is not necessary to restart the ColdFusion services. The new choice will immediately be put into use in place of whatever was in use before.

Should I have more than one datasource?
As soon as you do this once you'll see advantages to having one datasource per web site: neatly and tidily keeping each client's data separate from everyone else's. However, over time you may find the exercise is more trouble than its worth. ColdFusion manages client data sources on its own quite well, and leaving the default alone can be regarded as one less thing you'll have to worry about when setting up a new site. One area where it is very handy is if you have a particular site where you want a purge interval that is different from the default client data source. In that case, using a specific datasource for a given site becomes essential.