Introduction

 SQL Anywhere is an easy to use, enterprise quality, relational database with a small foot print that can be used in a variety of applications including embedded and mobile. My first experience with SQL Anywhere was in an application with Siebel mobile clients, who kept a portion of the database related to their role in the mobile client, and synchronized with either Oracle or MS SQL servers occasionally. I believe it was the excellent synchronization, SQL support, and the small foot print that made this choice natural.

Persisting a small database that can be embedded into an application is required in some cases. Consider the situation of a traveling sales man who keeps the data and occasionally reconnects either by a pull or a push type of replication. In fact the Siebel client mentioned above did have this functionality. The mobile client had the SQL Anywhere database.

ColdFusion provides excellent support for connecting to a variety of data sources through an easy to use administrative panel. ColdFusion easily connects to the SQL Anywhere database through an ODBC Socket. This tutorial is about connecting from ColdFusion MX 6.1 Server to a SQL Anywhere 9 database. Both servers are on an Windows XP Professional with ColdFusion MX 6.1 working off IIS 5.1 web server.

The backend database in this tutorial is the mysorian.db on the mysorian Server shown in Fig.1. I refer to my previous articles,  you can review the steps needed for this to happen. Please also look for the following tutorial to appear on ASP Free: MS Access to SQL Anywhere With Migration Wizard. Basically you use a command line statement with some arguments and then connect to the back end using Sybase Central. You need to specify the HTTP port as an argument. Port=8082 has been used, as other ports were used for IIS, Apache Server, and so on. Three tables imported from a 'pubs' database from SQL 2000 Server are shown here. If you want to carry out a similar table transfer you could use the DTS, but then you may have to make some transformations because of the data type involved.

Fig. 1

In this step you will create a ODBC datasource, more specifically a System DSN. Adaptive Server Anywhere  9.0  has drivers that install on your hard drive when you install SQL Anywhere 9. These will be available through the windows ODBC Data Source Administrator wizard shown in Fig.2 that can be accessed from Start-->Control Panel-->Administrative Tools-->Data Sources (ODBC). You may also use the menu item in SQL Anywhere studio to create this data source which is shown in Fig.3 that follows. This will in turn kick up the windows ODBC Data Source Administrator (the same one shown in Fig.2). The ColdAny ODBC connection is shown in the administrative panel in Fig.2.

Fig. 2


Fig. 3

ColdFusion MX 6.1 supports a variety of drivers to connect to databases. Quite a few drivers are available, and with the ODBC driver it is possible to connect to databases not in the list. Fig.4 shows the drivers available for connection. Although a driver for Sybase is shown, an ODBC Socket connection will be used in this tutorial as it is much easier to configure. We will be using the ODBC System DSN ColdAny created earlier.

Fig. 4

Configure a ColdFusion Data Source

To configure the ColdFusion data source you need to log in to the Administrative Panel of ColdFusion MX 6.1. On the left-hand navigational frame click on Data Sources under Data and Services to kick up the Data Sources page on the right-hand side as in Fig.5. It will be assumed that you have started the server (if not, you will go to Services in the Control Panel and start the ColdFusion related service, ColdFusion ODBC Server). Again it is recommended that you read up on the previous tutorials. It is in this frame that you will create the data source. In the upper form with the title Add New Data Source  you enter the ODBC data source name you created earlier - ColdAny. For the driver you use the pull down menu to pick up ODBC Socket and click on Add.

This brings up the screen shown in Fig.6 where you need to add the authentication information. Since the DSN was created by the computer administrator, a trusted connection is used. The system is the username. You do not need a password.

In addition to the above you will be able to show additional default settings as shown in Fig.7 by clicking on the Show Advanced Settings button. If needed you may make the choices here appropriately. In this tutorial the defaults are taken. You can even grant and revoke privileges and use all the DDL and DML commands.

When you are finally satisfied with your choices you may hit the Submit button, which will take you to the screen shown in Fig.8.  The screen shows that you successfully created a ColdFusion usable source to interact with the data on the SQL Anywhere backend database, mysorian.db. If you come back to this screen at a later stage it will be necessary to make sure that the connection status is OK by hitting the Verify All Connections button shown in Fig.8

Fig. 8

In order to test the connectivity in a realistic manner, a query will be run on the database in SQL Anywhere server, and the same query will be run using ColdFusion with the ODBC Socket created earlier. In order to run such a query on SQL Anywhere, you need to use the Interactive SQL tool described in an earlier tutorial. Basically you should be able to access this from the shortcut on the desktop created when you installed SQL Anywhere 9, or by right clicking the database in the Sybase Central Database management utility shown earlier (Fig.1). The screen in Fig.9 shows the query as implemented using Interactive SQL together with the results returned from the query.

Fig. 9

The same query in ColdFusion

Now the same query will be implemented in ColdFusion. Some additional CSS styling  is added to pretty up the display. The code for the ColdFusion query, ColdAny.cfm, saved to the virtual directory is shown below:

<STYLE TYPE="TEXT/CSS">
body      {font-family: arial}
table {width:60%;}
td{ width:50;color:navy; font-size=12;background-color:#FFAEAE;}
</STYLE>
<cfquery name="GetAll" datasource="ColdAny">
select * from "DBA"."authors" where state='UT' or state='CA' and Zip >90000
</cfquery>
<table border="1">
<tr>
<!--Column headers-->
<td>Author ID</td>
<td>Last Name</td>
<td>First Name</td>
<td>Telephone</td>
<td>City</td>
<td>Zip Code</td>
</tr>
<!--output of query-->
<cfoutput query="GetAll">
<tr>
<td>#au_id#</td>
<td>#au_lname#</td>
<td>#au_fname#</td>
<td>#phone#</td>
<td>#city#</td>
<td>#zip#</td>
</tr>
</cfoutput>

This display in Fig.10 shows the rows returned by the query when the CFM file is browsed. You may notice that not all columns were returned because only six columns were included in the query output.

Fig.10

Summary

ColdFusion offers a very convenient interface to connect with an SQL Anywhere database. It provides out of the box support for many other databases as well, and through ODBC to other ODBC supported databases such as the one described in this tutorial. Please understand that you need to be running a number of programs/services in order to obtain the results shown. Some of the services may have other dependencies, and those too must be running.