How to Access a SQL Anywhere Database with ColdFusion
In this article, you will learn about connecting from the ColdFusion MX 6.1 Server to a SQL Anywhere 9 database. This technique can be useful in a variety of applications, for example when a traveling salesman keeps some data from a database on a PDA or a laptop computer and periodically reconnects to the main database for updates.
How to Access a SQL Anywhere Database with ColdFusion - Create and verify a test query in SQL Anywhere (Page 4 of 4 )
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>
Display of query Output
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.
DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.