ColdFusion’s rapid development tools integrate with the JDBC driver to provide connectivity to SQL Server data. At Bobcares, with our Server Management Services, we can handle your server issues.

Integration of ColdFusion with JDBC for SQL Servers

Building and deploying web and mobile applications are both possible with ColdFusion11, an application server. The most recent protocol drivers are already supported by ColdFusion for a number of relational databases, and they are seamlessly integrated with JDBC drivers.

In this article, we will see how to connect to SQL Server data in ColdFusion as well as query SQL Server tables.

How to integrate ColdFusion with JDBC for SQL Servers?

The JDBC data source allows execution of SQL from standard ColdFusion tags such as cfquery and CFScript such as executeQuery.

• Copy the JAR and.lic files from the installation directory to the ColdFusion classpath. Alternatively, open the ColdFusion Administrator’s Java and JVM page and enter the path to the files in the ColdFusion Class Path box.
• After this step, restart the server.
• Expand the Data & Services node in the ColdFusion administrator interface and then click Data Sources.
• Enter a name for the data source in the Add New Data Source section and choose Other from the Driver menu. This adds a driver as a data source.
• Now we have to fill in the following driver properties:

  In the JDBC URL, enter the connection properties. JDBC URLs start with jdbc:sql: Also, list the connection properties in a semicolon-separated list of name=value pairs.

  In order to establish a connection with Microsoft SQL Server, use the properties:

  Server: The name of the server that is running SQL Server.

  Username: The username used for SQL Server authentication.

  Password: The password of the authenticating user.

  Database: The SQL Server database’s name.

  In order to establish a connection with Azure SQL Server and Azure Data Warehouse:

  Server: The server that runs Azure.

  User: The name of the user who is authenticating to Azure.

  Password: The password to authenticate the user.

  Database: The database name as seen in the Azure portal for SQL databases.

  Use the connection string designer built into the SQL Server JDBC Driver for assistance in creating the JDBC URL. Either double-click the JAR file or run it from the command line.

  java -jar cdata.jdbc.sql.jar

   

  Fill out the connection properties and afterward copy the string to the clipboard.

  A typical JDBC URL is as follows:

  jdbc:sql:User=myUser;Password=myPassword;Database=NorthWind;Server=myServer;Port=1433;

   

  Driver Class: Choose a driver class. cdata.jdbc.sql.SQLDriver is the driver class.

  Driver Name: Input a name for the driver that the user created.

  Username: Input the username that was used to authenticate.

  Password: Input the password used to authenticate.
• Now, enable the SQL Server data source in the Actions column to test the connection. The SQL Server data source is ready for use after reporting an OK status.

How to query SQL Server tables?

In order to pass SQL statements such as INSERT, UPDATE, and DELETE to SQL Server, use the cfquery tag. However, to create parameterized queries and prevent SQL injection via the query string, use the cfqueryparam tag.

<cfquery name="SQLQuery" dataSource="CDataSQL">
  SELECT * FROM Orders WHERE ShipCountry = <cfqueryparam value="#ShipCountry#" cfsqltype="cf_sql_varchar">
</cfquery>
<cfdump var="#SQLQuery#">

The CFScript equivalent is as follows:

<cfscript>
result = queryExecute(
  "SELECT * FROM Orders WHERE ShipCountry = ?", 
  [
    { value="USA", cfsqltype="cf_sql_varchar" }
  ],
  { datasource="CDataSQL" }
);

writeDump( var= result );
</cfscript> 

[Looking for a solution to another query? We are just a click away.]

Conclusion

To summarize, the article explains the steps from our Tech team to integrate ColdFusion’s rapid development tools with JDBC driver to provide connectivity to SQL Server data. The querying of SQL server tables is also discussed.