Käyttöopas Peruuta

Data Source Management for ColdFusion

Hae
  1. ColdFusion User Guide
  2. Introduction to ColdFusion
    1. About Adobe ColdFusion
    2. Download Adobe ColdFusion
    3. What's new in ColdFusion (2021 release)
    4. ColdFusion (2021 release) Release Notes
    5. Deprecated Features
    6. REST enhancements in ColdFusion (2018 release)
    7. Server Auto-Lockdown
    8. Asynchronous programming
    9. Docker images for ColdFusion
  3. Adobe ColdFusion (2021 release)
    1. Install ColdFusion- Zip Installer
    2. Install ColdFusion- GUI Installer
    3. ColdFusion Licensing and Activation
    4. ColdFusion Package Manager
    5. CFSetup configuration tool
    6. SAML in ColdFusion
    7. ColdFusion and Amazon S3
    8. ColdFusion and DynamoDB
    9. ColdFusion and Amazon SQS
    10. ColdFusion and Amazon SNS
    11. ColdFusion and MongoDB
    12. ColdFusion and Azure Blob
    13. ColdFusion and Azure Service Bus
    14. New and updated language enancements
    15. Multi-cloud storage services
    16. Multi-cloud RDS databases
    17. ColdFusion and Azure Cosmos DB
  4. Install ColdFusion
    1. ColdFusion server profiles
    2. Prepare to install ColdFusion
    3. Install the server configuration
    4. Install the JEE configuration
    5. Install ColdFusion Express
    6. Install integrated technologies
    7. Configure your system
    8. Troubleshoot installation issues
    9. Install ColdFusion silently
    10. Install Adobe ColdFusion (2016 release) hotfix
    11. ColdFusion (2018 release) - Install JEE configuration
  5. Use ColdFusion
    1. Command Line Interface (CLI)
    2. External session storage
    3. Generate Swagger documents
    4. Language enhancements
    5. NTLM support
    6. New and changed functions/tags in Adobe ColdFusion (2016 release)
    7. PDF enhancements
    8. Security enhancements in ColdFusion (2016 release)
  6. Performance Monitoring Toolset
    1. Auto-discovery of ColdFusion nodes and clusters
    2. Code profiler in ColdFusion Performance Monitoring Toolset
    3. Configure ColdFusion Performance Monitoring Toolset settings
    4. Install ColdFusion Performance Monitoring Toolset
    5. Overview of ColdFusion Performance Monitoring Toolset
    6. View cluster and node metrics
    7. View data source metrics
    8. View external services
    9. View incoming services
    10. View list of sites and busy connections
    11. View topology of sites
    12. Datastore Health Monitoring
    13. Performance Monitoring Toolset Update 1
    14. Secure Performance Monitoring Toolset with HTTPS/SSL
    15. Performance Monitoring Toolset deployment guide
  7. Use ColdFusion Builder
    1. About ColdFusion Builder
    2. System requirements | ColdFusion Builder
    3. Install ColdFusion Builder
    4. Edit code in ColdFusion Builder
    5. Manage servers in ColdFusion Builder
    6. Manage projects in ColdFusion Builder
    7. What’s new in Adobe ColdFusion Builder (2018 release)
    8. Frequently Asked Questions (FAQ) | Adobe ColdFusion Builder (2018 release)
    9. Debug applications in ColdFusion Builder
    10. ColdFusion Builder workbench
    11. ColdFusion Builder extensions
    12. Debugging Perspective in ColdFusion Builder
    13. Build mobile applications using ColdFusion Builder
    14. Bundled ColdFusion Server
    15. Debug mobile applications in ColdFusion Builder
    16. Use extensions in ColdFusion Builder
  8. Coldfusion API Manager
    1. Overview of Adobe ColdFusion API Manager
    2. Features in ColdFusion API Manager
    3. Get started with ColdFusion API Manager
    4. Install ColdFusion API Manager
    5. Authentication types
    6. Create and publish APIs
    7. Administrator
    8. Subscriber
    9. Throttling and rate limiting
    10. Notifications
    11. Connectors
    12. Set up cluster support
    13. Integrate ColdFusion and API Manager
    14. Metrics and Logging in API Manager
    15. Generate Swagger documents
    16. Configure SSL
    17. Known issues in this release
    18. Policies in ColdFusion API Manager
    19. Create a Redis cluster
    20. Multitenancy in API Manager
    21. Docker images for ColdFusion API Manager
  9. Configure and administer ColdFusion
    1. Administer ColdFusion
    2. Use the ColdFusion administrator
    3. Data Source Management for ColdFusion
    4. Connect to web servers
    5. Deploy ColdFusion applications
    6. Administer ColdFusion security
    7. Basic Troubleshooting and FAQs
    8. Work with Server Manager
    9. Use multiple server instances
    10. WebSocket Enhancements (ColdFusion 11)
    11. Security Enhancements (ColdFusion 11)
    12. Work with Server Monitor
    13. ColdFusion Administrator API Reference
  10. CFML Reference
    1. Introduction to CFML Reference
      1. New functions in ColdFusion (2018 release)
      2. New and changed functions/tags in Adobe ColdFusion (2016 release)
      3. Script supported tags and functions
      4. New and changed tags/functions in ColdFusion 11
    2. Reserved words and variables
      1. Reserved words and variables
      2. Reserved words
      3. Scope-specific built-in variables
      4. Custom tag variables
      5. ColdFusion tag-specific variables
      6. CGI environment (CGI Scope) variables
    3. ColdFusion tags
      1. ColdFusion tags
      2. Tags in ColdFusion 10
      3. Tag summary
      4. Tags by function
      5. Tag changes since ColdFusion 5
      6. Tags a-b
      7. Tags c
      8. Tags d-e
      9. Tags f
      10. Tags g-h
      11. Tags i
      12. Tags j-l
      13. Tags m-o
      14. Tags p-q
      15. Tags r-s
      16. Tags t
      17. Tags u-z
    4. ColdFusion functions
      1. ColdFusion functions
      2. New functions in ColdFusion 10
      3. ColdFusion functions by category
      4. Function changes since ColdFusion 5
      5. Functions a-b
      6. Functions c-d
      7. Functions e-g
      8. Functions h-im
      9. Functions in-k
      10. Functions l
      11. Functions m-r
      12. Functions s
      13. Functions t-z
      14. BooleanFormat
    5. Ajax JavaScript functions
      1. Ajax JavaScript functions
      2. Function summary Ajax
      3. ColdFusion.Ajax.submitForm
      4. ColdFusion.Autosuggest.getAutosuggestObject
      5. ColdFusion.Layout.enableSourceBind
      6. ColdFusion.MessageBox.getMessageBoxObject
      7. ColdFusion.ProgressBar.getProgressBarObject
      8. ColdFusion.MessageBox.isMessageBoxDefined
      9. JavaScriptFunctionsinColdFusion9Update1
    6. ColdFusion ActionScript functions
      1. ColdFusion ActionScript functions
      2. CF.http
      3. CF.query
    7. ColdFusion mobile functions
      1. ColdFusion Mobile Functions
      2. Accelerometer Functions
      3. Camera Functions
      4. Connection Functions
      5. Contact Functions
      6. Event Functions
      7. File System Functions
      8. Geolocation Functions
      9. Media and Capture Functions
      10. Notification Functions
      11. Splash Screen Functions
      12. Storage Functions
    8. Application.cfc reference
      1. Application.CFC reference
      2. Application variables
      3. Method summary
      4. onAbort
      5. onApplicationEnd
      6. onApplicationStart
      7. onMissingTemplate
      8. onCFCRequest
      9. onError
      10. onRequestEnd
      11. onRequest
      12. onRequestStart
      13. onServerStart
      14. onSessionEnd
      15. onSessionStart
    9. Script functions implemented as CFCs
      1. Script Functions Implemented as CFCs
      2. Accessing the functions
      3. Function summary
      4. ftp
      5. http
      6. mail
      7. pdf
      8. query
      9. Script functions implemented as CFCs in ColdFusion 9 Update 1
      10. storedproc
    10. ColdFusion Flash Form style reference
      1. Styles valid for all controls
      2. Styles for cfform
      3. Styles for cfformgroup with horizontal or vertical type attributes
      4. Styles for box-style cfformgroup elements
      5. Styles for cfformgroup with accordion type attribute
      6. Styles for cfformgroup with tabnavigator type attribute
      7. Styles for cfformitem with hrule or vrule type attributes
      8. Styles for cfinput with radio, checkbox, button, image, or submit type attributes
      9. Styles for cftextarea tag and cfinput with text, password, or hidden type attributes
      10. Styles for cfselect with size attribute value of 1
      11. Styles for cfselect with size attribute value greater than 1
      12. Styles for cfcalendar tag and cfinput with dateField type attribute
      13. Styles for the cfgrid tag
      14. Styles for the cftree tag
      15. ColdFusion Flash Form Style Reference
    11. ColdFusion event gateway reference
      1. ColdFusion Event Gateway reference
      2. addEvent
      3. CFEvent
      4. CFEventclass
      5. Constructor
      6. Gateway development interfaces and classes
      7. getStatus
      8. setCFCPath
      9. setCFCMethod
      10. getOriginatorID
      11. getLogger
      12. getBuddyList
      13. getBuddyInfo
      14. IM gateway message sending commands
      15. IM Gateway GatewayHelper class methods
      16. onIncomingMessage
      17. onIMServerMessage
      18. onBuddyStatus
      19. onAddBuddyResponse
      20. onAddBuddyRequest
      21. IM Gateway CFC incoming message methods
      22. IM gateway methods and commands
      23. CFML CFEvent structure
      24. warn
      25. info
      26. setOriginatorID
      27. data command
      28. submit Multi command
      29. submit command
      30. setGatewayType
      31. setGatewayID
      32. setData
      33. setCFCListeners
      34. outgoingMessage
      35. getStatusTimeStamp
      36. numberOfMessagesReceived
      37. numberOfMessagesSent
      38. removeBuddy
      39. removeDeny
      40. removePermit
      41. setNickName
      42. setPermitMode
      43. setStatus
      44. SMS Gateway CFEvent structure and commands
      45. SMS Gateway incoming message CFEvent structure
      46. getStatusAsString
      47. getProtocolName
      48. getPermitMode
      49. getPermitList
      50. getNickName
      51. getName
      52. getDenyList
      53. getCustomAwayMessage
      54. getQueueSize
      55. getMaxQueueSize
      56. getHelper
      57. getGatewayType
      58. getGatewayServices
      59. getGatewayID_1
      60. getGatewayID
      61. getData
      62. getCFCTimeout
      63. setCFCTimeout
      64. getCFCPath
      65. getCFCMethod
      66. GatewayServices class
      67. Gateway interface
      68. GatewayHelper interface
      69. addPermit
      70. addDeny
      71. addBuddy
      72. error
      73. debug
      74. Logger class
      75. stop
      76. start
      77. CFML event gateway SendGatewayMessage data parameter
      78. restart
      79. fatal
      80. SMS gateway message sending commands
    12. ColdFusion C++ CFX Reference
      1. C++ class overview
      2. Deprecated class methods
      3. CCFXException class
      4. CCFXQuery class
      5. CCFXRequest class
      6. CCFXStringSet class
      7. ColdFusion C++ CFX Reference
    13. ColdFusion Java CFX reference
      1. ColdFusion Java CFX reference
      2. Class libraries overview
      3. Custom tag interface
      4. Query interface
      5. Request interface
      6. Response interface
      7. Debugging classes reference
    14. WDDX JavaScript Objects
      1. WDDX JavaScript objects
      2. JavaScript object overview
      3. WddxRecordset object
      4. WddxSerializer object
  11. Develop ColdFusion applications
    1. Introducing ColdFusion
      1. Introducing ColdFusion
      2. About ColdFusion
      3. About Internet applications and web application servers
      4. About JEE and the ColdFusion architecture
    2. Changes in ColdFusion
      1. Changes in ColdFusion
      2. Replacement of JRun with Tomcat
      3. Security enhancements
      4. ColdFusion WebSocket
      5. Enhanced Java integration
      6. ColdFusion ORM search for indexing and search
      7. Solr enhancements
      8. Scheduler enhancements
      9. Integration with Microsoft Exchange Server 2010
      10. RESTful Web Services in ColdFusion
      11. Lazy loading across client and server in ColdFusion
      12. Web service enhancements
      13. Displaying geolocation
      14. Client-side charting
      15. Caching enhancements
      16. Server update using ColdFusion Administrator
      17. Secure Profile for ColdFusion Administrator
    3. Introduction to application development
      1. Introduction to application development using ColdFusion
      2. Using the Developing ColdFusion Applications guide
      3. About Adobe ColdFusion documentation for Developers
    4. The CFML programming language
      1. The CFML programming language
      2. Elements of CFML
      3. ColdFusion variables
      4. Expressions and number signs
      5. Arrays and structures
      6. Extend ColdFusion pages with CFML scripting
      7. Regular expressions in functions
      8. ColdFusion language enhancements
      9. Built-in functions as first class citizen
      10. Data types- Developing guide
    5. Building blocks of ColdFusion applications
      1. Building blocks of ColdFusion applications
      2. Create ColdFusion elements
      3. Write and call user-defined functions
      4. Build and use ColdFusion Components
      5. Create and use custom CFML tags
      6. Build custom CFXAPI tags
      7. Use the member functions
      8. Object Oriented Programming in ColdFusion
    6. Develop CFML applications
      1. Develop CFML applications
      2. Design and optimize a ColdFusion application
      3. Handle errors
      4. Use persistent data and locking
      5. Use ColdFusion threads
      6. Secure applications
      7. Client-side CFML (for mobile development)
      8. Use the ColdFusion debugger
      9. Debugging and Troubleshooting Applications
      10. Develop globalized applications
      11. REST enhancements in ColdFusion
      12. Authentication through OAuth
      13. Social enhancements
    7. Develop mobile applications
      1. Mobile application development
      2. Build mobile applications
      3. Debug mobile applications
      4. Inspect mobile applications
      5. Package mobile applications
      6. Troubleshoot mobile applications
      7. Device detection
      8. Client-side CFML
      9. Mobile Templates
      10. Code samples to build a mobile application
    8. Access and use data
      1. Access and use data
      2. Introduction to Databases and SQL
      3. Access and retrieve data
      4. Update database
      5. Use Query of Queries
      6. Manage LDAP directories
      7. Solr search support
    9. ColdFusion ORM
      1. ColdFusion ORM
      2. Introducing ColdFusion ORM
      3. ORM architecture
      4. Configure ORM
      5. Define ORM mapping
      6. Work with objects
      7. ORM session management
      8. Transaction and concurrency
      9. Use HQL queries
      10. Autogenerate database schema
      11. Support for multiple data sources for ORM
      12. ColdFusion ORM search
    10. ColdFusion and HTML5
      1. ColdFusion and HTML 5
      2. Use ColdFusion Web Sockets
      3. Media Player enhancements
      4. Client-side charting
      5. Display geolocation data
    11. Flex and AIR integration in ColdFusion
      1. Flex and AIR integration in ColdFusion
      2. Use the Flash Remoting Service
      3. Use Flash Remoting Update
      4. Offline AIR application support
      5. Proxy ActionScript classes for ColdFusion services
      6. Use LiveCycle Data Services ES assembler
      7. Use server-side ActionScript
    12. Request and present information
      1. Request and present information
      2. Retrieve and format data
      3. Build dynamic forms with cfform tags
      4. Validate data
      5. Create forms in Flash
      6. Create skinnable XML forms
      7. Use Ajax data and development features
      8. Use Ajax User Interface components and features
    13. Office file interoperability
      1. Office file interoperability
      2. Using cfdocument
      3. Using cfpresentation
      4. Using cfspreadsheet
      5. Supported Office conversion formats
      6. SharePoint integration
    14. ColdFusion portlets
      1. ColdFusion portlets
      2. Run a ColdFusion portlet on a JBoss portal server
      3. Run a ColdFusion portlet on a WebSphere portal server
      4. Common methods used in portlet.cfc
      5. ColdFusion portlet components
      6. Support for JSR-286
    15. Work with documents, charts, and reports
      1. Work with documents, charts, and reports
      2. Manipulate PDF forms in ColdFusion
      3. Assemble PDF documents
      4. Create and manipulate ColdFusion images
      5. Create charts and graphs
      6. Create reports and documents for printing
      7. Create reports with Report Builder
      8. Create slide presentations
    16. Use web elements and external objects
      1. Use web elements and external objects
      2. Use XML and WDDX
      3. Use web services
      4. Use ColdFusion web services
      5. Integrate JEE and Java elements in CFML applications
      6. Use Microsoft .NET assemblies
      7. Integrate COM and CORBA objects in CFML applications
    17. Use external resources
      1. Send and receive e-mail
      2. Interact with Microsoft Exchange servers
      3. Interact with remote servers
      4. Manage files on the server
      5. Use event gateways
      6. Create custom event gateways
      7. Use the ColdFusion extensions for Eclipse
      8. Use the data services messaging event gateway
      9. Use the data management event gateway
      10. Use the FMS event gateway
      11. Use the instant messaging event gateways
      12. Use the SMS event gateway

 

A data source is a complete database configuration that uses a JDBC driver to communicate with a specific database. In Adobe ColdFusion, you configure a data source for each database that you want to use. After you configure a data source, ColdFusion can then communicate with that data source through JDBC.

For basic information on data sources and connecting to databases, click Resources in the ColdFusion Administrator, and then select Getting Started Experience.

About JDBC

The following table describes the four types of JDBC drivers:

Type

Name

Description

1

JDBC-ODBC bridge

Translates JDBC calls to ODBC calls, and sends them to the ODBC driver. Advantages: Allows access to many different databases.Disadvantages: The ODBC driver, and possibly the client database libraries, must reside on the ColdFusion server computer. Performance is slower than other JDBC driver types.Adobe does not recommend this driver type unless your application requires DBMS-specific features.

2

Native-API/partly Java driver

Converts JDBC calls to database-specific calls.Advantages: Better performance than Type 1 driver.Disadvantages: The client database libraries of the vendor must reside on the same computer as ColdFusion. ColdFusion includes a Type 2 driver for use with Microsoft Access Unicode databases.

3

JDBC-Net pure Java driver

Translates JDBC calls to the middle-tier server, which then translates the request to the database-specific native-connectivity interface.Advantages: The database libraries of vendors are not required client computer. Can be tailored for small size (faster loading).Disadvantages: Database-specific code must be executed in the middle tier. ColdFusion includes an ODBC socket Type 3 driver for use with Microsoft Access databases and ODBC data sources.

4

Native-protocol/all-Java driver

Converts JDBC calls to the network protocol used directly by the database.Advantages: Fast performance. No special software needed on the computer on which you run ColdFusion.Disadvantages: Many of these protocols are proprietary, requiring a different driver for each database. ColdFusion includes Type 4 drivers for many DBMSs; however, not all DBMSs are supported in ColdFusion Standard Edition. 

JDBC drivers are stored in JAR files. For example, the JDBC drivers that are supplied with ColdFusion are in the drivers.jar file. If you are using another JDBC driver, you must store it in the ColdFusion classpath. For example, _cf_root/cfusion/lib (server configuration) or cf_webapp_root/WEB-INF/cfusion/lib (JEE configuration).

Supplied drivers

The following table lists the database drivers supplied with ColdFusion and where you can find more information about them:

Driver

Type

For more information

Apache Derby Client

 

Connecting to Apache Derby Client

Apache Derby Embedded

 

Connecting to Apache Derby Embedded

DB2 Universal Database

4

Connecting to DB2 Universal Database

DB2 OS/390

4

Connecting to other data sources

Microsoft Access

3

Connecting to Microsoft Access

Microsoft Access with Unicode support

2

Connecting to Microsoft Access with Unicode

Microsoft SQL Server

4

Connecting to Microsoft SQL Server

MySQL

4

Connecting to MySQL

ODBC Socket

3

Connecting to ODBC Socket

Oracle

4

Connecting to Oracle

Other

 

Connecting to other data sources

Sybase

4

Connecting to Sybase

To see a list of database versions that ColdFusion supports, go to https://helpx.adobe.com/pdf/coldfusion2018-support-matrix.pdf

When running in the JEE configuration, the ColdFusion Administrator also lets you configure a data source that connects to a JNDI data source. A Java Naming and Directory Interface (JNDI) data source is equivalent to a ColdFusion data source, except you define it by using your J2EE application server. After it's defined, ColdFusion applications use it as they would any data source. For information on defining a JNDI data source, see Connecting to JNDI data sources.

Adding data sources

The ColdFusion Administrator organizes information about all ColdFusion server database connections in a single location. In addition to adding data sources, you can use the Administrator to specify changes to your database configuration, such as relocation, renaming, or changes in security permissions.

Adding data sources in the Administrator

You use the ColdFusion Administrator to quickly add a data source for use in your ColdFusion applications. When you add a data source, you assign it a data source name (DSN) and set all information required to establish a connection.

ColdFusion includes data sources that are configured by default. You do not need the following procedure to work with these data sources.
  1. In the ColdFusion Administrator, select Data & Services > Data Sources.
  2. Under Add New Data Source, enter a data source name; for example, MyTestDSN. The following names are reserved; you cannot use them for data source names:
    • service
    • jms_provider
    • comp
    • jms
  3. Select a driver from the drop-down list; for example, Microsoft SQL Server.
  4. Click Add.A form for additional DSN information appears. The available fields in this form depend on the driver that you selected.
  5. In the Database field, enter the name of the database.
  6. In the Server field, enter the network name or IP address of the server that hosts the database, and enter any required Port value. For example, the bullwinkle server on the default port.

  7. If your database requires login information, enter your user name and password.

    The omission of required user name and password information is a common reason why a data source fails to verify.

     

  8. (Optional) Enter a Description.
  9. (Optional) Click Show Advanced Settings to specify any ColdFusion specific settings; for example, to configure which SQL commands can interact with this data source.
  10. Click Submit to create the data source. ColdFusion automatically verifies that it can connect to the data source.

  11. (Optional) To verify this data source later, click the verify icon in the Actions column.

    To check the status of all data sources available to ColdFusion, click Verify All Connections.

Specifying connection string arguments

The ColdFusion Administrator lets you specify connection-string arguments for data sources. In the Advanced Settings page, use the Connection String field to enter name-value pairs separated by a semicolon. For more information, see the documentation for your database driver.

The cfquery connectstring attribute is no longer supported.

Guidelines for data sources

When you add data sources to ColdFusion, keep in mind the following guidelines:

  • Data source names must be all one word.
  • Data source names can contain only letters, numbers, hyphens, and the underscore character (_).
  • Data source names must not contain special characters or spaces.
  • Although data source names are not case sensitive, use a consistent capitalization scheme.
  • Depending on the JDBC driver, connection strings and JDBC URLs might be case sensitive.
  • Use the Administrator to verify that ColdFusion can connect to the data source.
  • A data source must exist in the ColdFusion Administrator before you use it on an application page to retrieve data.

Connection Issues

Provide validationQuery (to validate the connection) before executing the query in the Advanced Settings page.

validationQuery has to be used with caution as it can result in performance issues.

Connecting to Apache Derby Client

Use the settings in the following table to connect ColdFusion to Apache Derby Client:

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

Database

The name of the database.

Server

The name of the server that hosts the database that you want to use. If the database is local, enclose the word local in parentheses.

User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a user name (for example, in a cfquery tag). The user name must have CREATE PACKAGE privileges for the database, or the database administrator must create a package. Consult the database administrator when configuring this type of data source.

Password

The password that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Description

(Optional) A description for this connection.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Restrict Connections To

Specifies the maximum number of database connections for the data source. To use this restriction, enable the Limit Connections option.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the attempt to log in to the data source connection.

CLOB

Returns the entire contents of any CLOB/ Text columns in the database for this data source. If deselected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting. For UDB 7.1 and 7.2, a 32K limit on CLOBs exists.

BLOB

Returns the entire contents of any BLOB/Image columns in the database for this data source. If deselected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting. BLOBs are not supported on UDB 7.1 and 7.2.

LongText Buffer (chr)

The default buffer size, used if the CLOB option is not selected. The default value is 64000 bytes.

BLOB Buffer (bytes)

The default buffer size, used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

Validation Query

Called when a connection from the pool is resued. This can slow query response time because an additional query is generated. Specify the validation query just before restarting the database to verify all connections, but remove the validation query after restarting the database to avoid any performance loss.

Connecting to Apache Derby Embedded

Use the settings in the following table to connect ColdFusion to Apache Derby Embedded:

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

Database folder

The folder where the database is located.

Create Database

Select this option to create a database. The new database exists in the path specified in the Database Folder. If the database exists, an SQL warning is generated, and a connection to the existing database is established.

Description

(Optional) A description for this connection.

ColdFusion user name

The user name you use to log in to the ColdFusion Administrator.

ColdFusion Password

The password you use to log in to the ColdFusion Administrator.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

Max Pooled Statements

Select this option to reuse prepared statements (that is, stored procedures and queries that use the cfqueryparam tag). Although you tune this setting based on your application, start by setting it to the sum of the following:

·         Unique cfquery tags that use the cfqueryparam tag

·         Unique cfstoredproc tags

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the attempt to log in to the data source connection.

CLOB

Returns the entire contents of any CLOB/ Text columns in the database for this data source. If deselected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting. For UDB 7.1 and 7.2, a 32K limit on CLOBs exits.

BLOB

Returns the entire contents of any BLOB/Image columns in the database for this data source. If deselected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting. BLOBs are not supported on UDB 7.1 and 7.2.

LongText Buffer (chr)

The default buffer size, used if the CLOB option is not selected. The default value is 64000 bytes.

BLOB Buffer (bytes)

The default buffer size, used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

Validation query

Called when a connection from the pool is resued. This can slow query response time because an additional query is generated.Specify the validation query just before restarting the database to verify all connections, but remove the validation query after restarting the database to avoid any performance loss.

When you add an Apache Derby Embedded data source, ensure that the specified directory does not exist

Connecting to DB2 Universal Database

For information on defining data sources that work with DB2 for OS/390 or iSeries, see Connecting to other data sources. To see a list of DB2 versions that ColdFusion supports, go to https://helpx.adobe.com/pdf/coldfusion2018-support-matrix.pdf.

DB2 Universal Database (UDB) refers to all versions of DB2 running on Windows, UNIX, and Linux/s390 platforms.

Use the settings in the following table to connect ColdFusion to DB2:

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

Database

The name of the database.

Server

The name of the server that hosts the database that you want to use. If the database is local, enclose the word local in parentheses.

Port

The number of the TCP/IP port that the server monitors for connections.

User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a user name (for example, in a cfquery tag). The user name must have CREATE PACKAGE privileges for the database, or the database administrator must create a package. Consult the database administrator when configuring this type of data source.

Password

The password that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Description

(Optional) A description for this connection.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Restrict Connections To

Specifies the maximum number of database connections for the data source. To use this restriction, enable the Limit Connections option.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

Max Pooled Statements

Enables reuse of prepared statements (that is, stored procedures and queries that use the cfqueryparam tag). Although you tune this setting based on your application, start by setting it to the sum of the following:

·         Unique cfquery tags that use the cfqueryparam tag

·         Unique cfstoredproc tags

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the attempt to log in to the data source connection.

CLOB

Returns the entire contents of any CLOB/Text columns in the database for this data source. If deselected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting. For UDB 7.1 and 7.2, a 32K limit on CLOBs exits.

BLOB

Returns the entire contents of any BLOB/Image columns in the database for this data source. If deselected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting. BLOBs are not supported on UDB 7.1 and 7.2.

LongText Buffer (chr)

The default buffer size, used if the CLOB option is not selected. The default value is 64000 bytes.

BLOB Buffer (bytes)

The default buffer size, used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

Validation query

Called when a connection from the pool is resued. This can slow query response time because an additional query is generated. Specify the validation query just before restarting the database to verify all connections, but remove the validation query after restarting the database to avoid any performance loss.

Client Hostname

The host name from where the query is executed.

Client Username

The user name if the user is logged in using the <cflogin> tag.

Application Name

The application name specified in the application.cfc.

Prefix

If specified, the value is prefixed with the application name specified in application.cfc.

Enable connection validation

Check if to validate the connection.

Connecting to Microsoft Access

Use the settings in the following table to connect ColdFusion to Microsoft Access data sources:

Setting

Description

CF Data Source Name

The data source name (DSN) used by ColdFusion to connect to the data source.

Database File

The file that contains the database.

System Database File

To secure access to the specified database file, click Browse Server to locate and enter a database that contains database security information. By default, the system database is located in the same directory as the MDB file or in the windows\system32\system.mdw directory.

Use Default User name

If selected, ColdFusion does not pass a user name or password when requesting a connection. The Microsoft Access driver uses the default user name and password.

ColdFusion User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a user name (for example, in a cfquery tag).

ColdFusion Password

The password that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Description

(Optional) A description for this connection.

Page Timeout

The number of milliseconds before a request for a ColdFusion page times out. The default is 600. If you observe excessive network activity when using this driver, increase the page time-out value.

Max Buffer Size

The size of the internal buffer, in kilobytes, that Access uses to transfer data to and from the disk. The default buffer size is 2048 KB. Specify an integer value divisible by 256.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Default User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a user name (for example, in a cfquery tag).

Default Password

The password that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Return Timestamp as String

Enable this setting if your application retrieves Date/Time data and then reuses it in SQL statements without applying formatting (using functions such as DateFormat, TimeFormat, and CreateODBCDateTime).

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Restrict Connections To

Specifies the maximum number of database connections for the data source. To use this restriction, enable the Limit Connections option.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the data source connection login attempt.

CLOB

Returns the entire contents of any CLOB/ Text columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting.

BLOB

Returns the entire contents of any BLOB/ Image columns in the database for this data source. If deselected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting.

LongText Buffer

The default buffer size, used if the CLOB option is not selected. The default value is 64000 bytes.

BLOB Buffer

The default buffer size, used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

Validation query

Called when a connection from the pool is resued. This can slow query response time because an additional query is generated. Specify the validation query before restarting the database to verify all connections, but remove the validation query after restarting the database to avoid any performance loss.

Connecting to Microsoft Access with Unicode

Use the settings in the following table to connect ColdFusion to Microsoft Access with Unicode data sources (this is a Type 2 driver):

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

Database File

The file that contains the database.

Description

(Optional) A description for this connection.

ColdFusion User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a user name (for example, in a cfquery tag).

ColdFusion Password

The password that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Page Timeout

The time (in tenths of a second) before a request for a ColdFusion page times out.

Max Buffer Size

The size of the internal buffer, in kilobytes, used by Microsoft Access to transfer data to and from the disk. Can be any integer value divisible by 256.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Restrict Connections To

Specifies the maximum number of database connections for the data source. To use this restriction, enable the Limit Connections option.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the data source connection login attempt.

CLOB

Select to return the entire contents of any CLOB/Text columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting.

BLOB

Select to return the entire contents of any BLOB/Image columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting.

LongText Buffer

The default buffer size, used if the CLOB option is not selected. The default value is 64000 bytes.

BLOB Buffer

The default buffer size, used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

Validation query

Called when a connection from the pool is resued. This can slow query response time because an additional query is generated. Specify the validation query just before restarting the database to verify all connections, but remove the validation query after restarting the database to avoid any performance loss.

This driver uses the Microsoft Jet list of reserved words, including the word Last. For a complete list, see http://support.microsoft.com/?kbid=248738.

Connecting to Microsoft SQL Server

To see a list of SQL Server versions that ColdFusion supports, go to https://helpx.adobe.com/pdf/coldfusion2018-support-matrix.pdf. Use the settings in the following table to connect ColdFusion to SQL Server:

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

Database

The database to which this data source connects.

Server

The name of the server that hosts the database that you want to use. If the database is local, enclose the word local in parentheses. If you are running SQL Server locally (or using MSDE), specify 127.0.0.1 for theserver name instead of the actual instance name.

Port

The number of the TCP/IP port that the server monitors for connections.

User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a user name (for example, in a cfquery tag).

Password

The password that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Description

(Optional) A description for this connection.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Select Method

Determines whether server cursors are used for SQL queries.

·         The Direct method provides more efficient retrieval of data when you retrieve recordsets in a forward-only direction and you limit your SQL Server connection to a single open SQL statement at a time. This is typical for ColdFusion applications.

·         The Cursor method lets you have multiple open SQL statements on a connection. This is not typical for ColdFusion applications, unless you use pooled statements.

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Restrict Connections To

Specifies the maximum number of database connections for the data source. To use this restriction, enable the Limit Connections option.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

String Format

Enable this option if your application uses Unicode data in DBMS-specific Unicode data types, such as National Character or nchar.

Max Pooled Statements

Enables reuse of prepared statements (that is, stored procedures and queries that use the cfqueryparam tag). Although you tune this setting based on your application, start by setting it to the sum of the following:

·         Unique cfquery tags that use the cfqueryparam tag

·         Unique cfstoredproc tags

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.
Query Timeout (seconds) The timeout (in seconds) for all the query statements created by the connection. 
ApplicationIntemt Specifies whether the driver connects to read-write databases or requests read-only routing to connect to read-only database replicas.
Log Activity Enable this option to create logs for all database activities. You can also specify a location for the log file.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the data source connection login attempt.

CLOB

Select to return the entire contents of any CLOB/Text columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting.

BLOB

Select to return the entire contents of any BLOB/ Image columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting.

LongText Buffer

The default buffer size, used if Enable Long Text Retrieval (CLOB) is not selected. The default value is 64000 bytes.

BLOB Buffer

The default buffer size, used if the BLOB option is not selected. The default value is 64000 bytes.
Disable Autogenerated Keys Enable or disable the option. Auto-generated keys feature provides a way to retrieve values from columns that are part of an index or have a default value assigned. 

Allowed SQL

The SQL operations that can interact with the current data source.

Validation Connection

Called when a connection from the pool is resued. This can slow query response time because an additional query is generated. Configure the validation query just before restarting the database to verify all connections, but remove the validation query after restarting the database to avoid any performance loss.

Client Hostname

The host name from where the query is executed.

Client Username

The user name if the user is logged in using the <cflogin> tag.

Application Name

The application name specified in the application.cfc.

Prefix

If specified, the value is prefixed with the application name specified in application.cfc.

Enable connection validation

Check if to validate the connection.

Set up the database in the SQL Server Enterprise manager

  1. Expand the server group.
  2. Expand the server.
  3. Under the Security folder, right-click on Logins.
  4. Select New Login.
  5. Select Windows Authentication or SQL Server Authentication settings.
  6. Select the database, and specify the language.
  7. Ensure that the database server is using mixed authentication. While in Enterprise Manager, right-click the server, select Properties > Security and then select the Security tab. Ensure that the SQL Server and Windows options are clicked.
  8. Click OK.

Troubleshooting SQL Server connections

If you are having trouble establishing a connection to SQL Server, review the following considerations:

  • If you installed SQL Server using a server name other than the default, use your chosen domain\servername wherever there is reference to (local). The following situations can cause a Connection Refused error:
    • If you specified authentication information in SQL Server, ensure that you have not defined a user name and password in the ColdFusion data source.
    • You are running a connection-limited version of SQL Server and the request exceeds the limit for TCP/IP connections.
      You can prevent this exception by setting the Limit Connections and Restrict Connections To options in ColdFusion Administrator on the Advanced Settings page for the data sources, and specifying a number less than the SQL Server maximum.
  • SQL Server does not enable the TCP/IP protocol. This problem can happen when SQL Server is on the same computer as ColdFusion. To fix this problem, perform the following steps:
    1. In SQL Server Enterprise Manager, right-click on the name of your SQL Server and click Properties.
    2. Click Network Configuration and the General Tab.
    3. Move TCP/IP from the Disabled Protocols section to the Enabled Protocols section.
    4. Click OK.
    5. Restart the SQL Server services.
    6. Verify your data source.
  • If you have are having trouble connecting, consider using mixed-mode authentication for SQL Server (Windows and SQL) and removing the user name and password from the ColdFusion data source.

Connecting to MySQL

To see a list of MySQL versions that ColdFusion supports, go to https://helpx.adobe.com/pdf/coldfusion2018-support-matrix.pdf

By default, queries to MySQL data sources return isCaseSensitive = NO for each column in the return structure from the GetMetaData function. Set the system property -Dcoldfusion.mysql.enableiscasesensitive=true to turn on the calls to isCaseSensitive.

Use the settings in the following table to connect ColdFusion to MySQL data sources:

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

Database

The database to which this data source connects.

Server

The name of the server that hosts the database that you want to use. If the database is local, enclose the word local in parentheses.

Port

The number of the TCP/IP port that the server monitors for connections.

User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source, if a ColdFusion application does not supply a user name (for example, in a cfquery tag).

Password

The password that ColdFusion passes to the JDBC driver to connect to the data source, if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Description

(Optional) A description for this connection.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Restrict Connections To

Specifies the maximum number of database connections for the data source. To use this restriction, you must enable the Limit Connections option.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Query Timeout (seconds)

 The default query timeout values for a DSN. This settings is only applicable for MySQL DataDirect driver. A new argument qtimeout has been added to the following methods in the Administrator API:
  • setDB2()
  • setMySQL_DD()
  • setOracle()
  • setSybase()
  • setInformix()
  • setMSSQL

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the data source connection login attempt.

CLOB

Select to return the entire contents of any CLOB/ Text columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting.

BLOB

Select to return the entire contents of any BLOB/ Image columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting.

LongText Buffer

The default buffer size; used if Enable Long Text Retrieval (CLOB) is not selected. The default value is 64000 bytes.

BLOB Buffer

The default buffer size; used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

Validation query

Called when a connection from the pool is resued. This can slow query response time because an additional query is generated. You should specify this just before restarting the database to verify all connections, but remove the validation query after restarting the database to avoid any performance loss.

DataDirect Connect JDBC Support

ColdFusion supports the latest version of DataDirect drivers for database operations. These drivers improve performance and provide support for additional databases.

DataDirect driver features

ColdFusion supports DataDirect driver version 4.2, which provides the following features to enhance database operations:

  • Support for MySQL (Enterprise and Commercial), Oracle11g, DB2v9.5, Informix 11, SQL Server 2008

    For Oracle databases, if you want to filter the retrieval of column names or indexes for a particular schema, then the schema name must be provided along with the table name in the table attribute. In this case, the format of the table attribute value is: schemaname.tablename.
  • Improved performance
  • IPv6 Address Support
  • Querytimeout connection option to set default query timeout value. For details, see New querytimeout connection option.
Note: Timestamp Milliseconds are rounded off during insert on SQL Server 2008 and using Connect for JDBC driver version 4.2. This is a known issue. Read this article. As a workaround, specify the parameter DateTimeInputParameterType=dateTime in the connection string.

Creating a data source in MySQL

To create a MySQL Enterprise ColdFusion data source, select the driver type as MySQL(Datadirect) from the drivers pop up menu in ColdFusion Administrator.

The MySQL5 Enterprise database sometimes requires access permission for all users who try to connect to it, if the access permission is not specified during configuration.

To grant the required permission, you can use the command: grant all on . to 'root'@'%' identified by 'admin';.
Replace root and admin by MYSQL username and MYSQL password, respectively.
A new admin API method, setMySQL_DD has been added, which lets you create a MySQL DataDirect data source.

New querytimeout connection option

Following is the description of querytimeout and pagetimeout from the DataDirect documentation: 
"When the page time-out is much higher and the query times out, an exception is thrown to indicate that the query has timed out. Similarly, if the query timeout is much higher and page times out, an exception is thrown to indicate the page has timed out. However, when a page times out while a query is executing, the page times out only after the query's execution is complete."
A new argument qtimeout has been added to the following methods in the Administrator API:

  • setDB2()
  • setMySQL_DD()
  • setOracle()
  • setSybase()
  • setInformix()

  • setMSSQL()

    The qtimeout option is not supported by all databases.

    For more information about DataDirect JDBC Connect, see DataDirect Connect for JDBC documentation

Enabling SSL Connection

Do the following to enable SSL connection:

  1. In the ColdFusion Administrator, go to Data & Services > Data Sources.
  2. Select the data source to enable SSL Connection.
  3. In the data source page, click Show Advanced Settings.
  4. In the Connection String text box, specify the connection properties as per the SSL requirements.

Connection properties

The following table provides details of the connection properties and specifies which database the properties apply to:

Property

Relevance

Description

Applies to

KeyStore

Applies only if client authentication is enabled on database server

The directory of the keystore file to be used

DB2, Oracle

KeyStorePassword

Applies only if client authentication is enabled on database server

The password to access the keystore file

DB2, Oracle

KeyPassword

OptionalUsed if keys in keystore file have a different password than the keystore file

The password to access the individual keys in the keystore file

DB2, Oracle

EncryptionMethod

Required. Values are noEncryption and SSL. If EncryptionMethod is set to SSL, the data is encrypted using the SSL encryption method.

Determines if SSL encryption encrypts and decrypts data transmitted between the driver and the database server.

DB2, Microsoft SQL Server, Oracle, Sybase, and MySQL.

TrustStore

Ignored if ValidateServerCertificate=false

The directory of the truststore file

DB2, Microsoft SQL Server, Oracle, Sybase

TrustStorePassword

Ignored if ValidateServerCertificate=false

The password to access the truststore file

DB2, Microsoft SQL Server, Oracle, Sybase

ValidateServerCertificate

Optional

true or false Determines whether the driver validates the certificate sent by the database server

DB2, Microsoft SQL Server, Oracle, Sybase

HostNameInCertificate

OptionalApplies if EncryptionMethod=SSL and ValidateServerCertificate=true

{{host_name | #SERVERNAME#}}Host name for certificate validation

DB2, Microsoft SQL Server, Oracle, Sybase

useSSL

Required for enabling SSL

true or false Use SSL when communicating with the server

MySQL

requireSSL

Optional

true or false Require SSL connection if useSSL=true

MySQL

verifyServerCertificate

Optional

true or false Determines whether to validates the certificate sent by the database server

MySQL

clientCertificateKeyStoreUrl

Applies only if client authentication is enabled on the database server

URL to the client certificate KeyStore. If not specified, use defaults.

MySQL

clientCertificateKeyStoreType

OptionalDepends on the keystore type supported by your JVM

KeyStore type for client certificates. NULL or empty means use default. Standard keystore types supported by the JVM are "JKS" and "PKCS12". Your environment might have more types available depending on the security products available to the JVM.

MySQL

clientCertificateKeyStorePassword

Applies only if client authentication is enabled on database server

Password for the client certificate KeyStore

MySQL (4/5)

trustCertificateKeyStoreUrl

Applies only if verifyServerCertificate=true

URL to the trusted root certificate KeyStore. If not specified, use defaults.

MySQL (4/5)

trustCertificateKeyStoreType

OptionalDepends on the keystore type supported by your JVM

KeyStore type for trusted root certificates. NULL or empty means use default. Standard keystore types supported by the JVM are "JKS" and "PKCS12". Your environment might have more types available depending on the security products available to the JVM.

MySQL (4/5)

trustCertificateKeyStorePassword

Required if verifyServerCertificate=true

Password for the trusted root certificate KeyStore

MySQL (4/5)

Specifying connection properties

The following table details the connection properties that you must specify for each database driver to enable SSL connection. The table provides all possible values for each driver. Specify the optional values (see the table in the section Enabling SSL Connection) as per your requirements.

Database

Database Driver

Connection Property

DB2

DB2 Universal Database

EncryptionMethod=SSL; KeyStore=path to keystore; KeyStorePassword=
keystore Password; KeyPassword=
key Password; TrustStore=path to keystore; TrustStorePassword=trustStorePassword;
ValidateServerCertificate=
true|false; HostNameInCertificate=host_name|#SERVERNAME#};

Microsoft SQL Server

Microsoft SQL Server

EncryptionMethod=SSL; TrustStore=path to keystore; TrustStorePassword=
trustStorePassword;
ValidateServerCertificate=true|false; HostNameInCertificate=host_name|
#SERVERNAME#;

Oracle

Oracle

EncryptionMethod=SSL; KeyStore=path to keystore; KeyStorePassword=
keystore Password; KeyPassword=
key Password; TrustStore=path to keystore; TrustStorePassword=
trustStorePassword; ValidateServerCertificate=
true|false; HostNameInCertificate=host_name|#SERVERNAME#};

Sybase

Sybase

EncryptionMethod=SSL;TrustStore=path to keystore; TrustStorePassword=
trustStorePassword;
ValidateServerCertificate=true|false; HostNameInCertificate=host_name|
#SERVERNAME#;

MySQL

MySQL (4/5)

useSSL=true&requireSSL=true|false& verifyServerCertificate=true|false&clientCertificateKeyStoreUrl=
URLToClientCertificate&clientCertificateKeyStoreType=
KeyStoreType&clientCertificateKeyStorePassword=
keystorePassword&trustCertificateKeyStoreUrl=
URLToRootCertificate&trustCertificateKeyStoreType= KeyStoreType&trustCertificateKeyStorePassword=trustedRootCertificatePassword

Not all MySQL (4/5) properties listed are supported by all MySQL versions. See MySQL documentation for details of the supported properties for your version.

If the database driver attempts to connect to a database server that does not support SSL, connection might hang. You can avoid issues when connecting to a server that does not support SSL by setting a login timeout.

For more information on enabling SSL for DB2, Microsoft SQL Server, Oracle, and Sybase, see the DataDirect documentation
For more information on enabling SSL for MySQL, see MySQL documentation available at the following URL:
https://dev.mysql.com/doc/connector-j/en/connector-j-reference.html

Connecting to ODBC Socket

Use the settings in the following table to connect ColdFusion to ODBC Socket data sources (this is a Type 3 driver):

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

ODBC DSN

Select the ODBC DSN to connect to ColdFusion.

Trusted Connection

Specifies whether to use domain user account access to the database. Only valid for SQL Server.

User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a user name (for example, in a cfquery tag).

Password

The password that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Description

(Optional) A description for this connection.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Return Timestamp as String

Enable this option if your application retrieves Date/Time data and then reuses it in SQL statements without applying formatting (using functions such as DateFormat, TimeFormat, and CreateODBCDateTime).

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Restrict Connections To

Specifies the maximum number of database connections for the data source. To use this restriction, enable the Limit Connections option.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the attempt to log in to the data source connection.

CLOB

Select to return the entire contents of any CLOB/Text columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting.

BLOB

Select to return the entire contents of any BLOB /Image columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting.

LongText Buffer

The default buffer size; used if Enable Long Text Retrieval (CLOB) is not selected. The default value is 64000 bytes.

BLOB Buffer

The default buffer size; used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

Validation query

Called when a connection from the pool is resued. This can slow query response time because an additional query is generated. Configure this just before restarting the database to verify all connections, but remove the validation query after restarting the database to avoid any performance loss.

Connecting to Oracle

To see a list of Oracle versions that ColdFusion supports, go to https://helpx.adobe.com/pdf/coldfusion2018-support-matrix.pdf. Use the settings in the following table to connect ColdFusion to Oracle data sources:

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

SID Name

The Oracle System Identifier (SID) that refers to the instance of the Oracle database software running on the server. The default value is ORCL.

Server

The name of the server that hosts the database that you want to use. If the database is local, enclose the word local in parentheses.

Port

The number of the TCP/IP port that the server monitors for connections.

User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a user name (for example, in a cfquery tag).

Password

The password that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Description

(Optional) A description for this connection.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Restrict Connections To

Specifies the maximum number of database connections for the data source. To use this restriction, enable the Limit Connections option.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

Max Pooled Statements

Enables reuse of prepared statements (that is, stored procedures and queries that use the cfqueryparam tag). Although you tune this setting based on your application, start by setting it to the sum of the following:

·         Unique cfquery tags that use the cfqueryparam tag

·         Unique cfstoredproc tags
The default value is 300.

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the attempt to log in to the data source connection.

CLOB

Select to return the entire contents of any CLOB/Text columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting.

BLOB

Select to return the entire contents of any BLOB/ Image columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting.

LongText Buffer

The default buffer size; used if Enable Long Text Retrieval (CLOB) is not selected. The default value is 64000 bytes.

BLOB Buffer

The default buffer size; used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

Validation query

Called when a connection from the pool is resued. This can slow query response time because an additional query is generated. Specify the validation query before restarting the database to verify all connections, but remove the validation query after restarting the database to avoid any performance loss.

Client Hostname

The host name from where the query is executed.

Client Username

The user name if the user is logged in using the <cflogin> tag.

Application Name

The application name specified in the application.cfc.

Prefix

If specified, the value is prefixed with the application name specified in application.cfc.

Enable connection validation

Check if to validate the connection.

You can also use this option to create a datasource for Oracle Real Application Cluster (RAC). Specify the following settings:

  • JDBC URL - jdbc:macromedia:oracle://hostname:port;ServiceName=servicename;AlternateServers=(alternatehostname:port);ConnectionRetryCount=2;ConnectionRetryDelay=5 (servicename,a name that is typically composed of the database name and domain name, is used to specify connections to Oracle RAC because the nodes in a RAC system share a common service name. AlternateServers is a comma separated list of servers and port names.)
  • Driver class - macromedia.jdbc.MacromediaDriver
  • Username - A user defined to the database
  • Password - The password for the username

Connecting to other data sources

Use the settings in the following table to connect ColdFusion to data sources through JDBC drivers that do not appear in the drop-down list of drivers:

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

JDBC URL

The JDBC connection URL for this data source.

Driver Class

The fully qualified class name of the driver. For example, com.inet.tds.TdsDriver. The JAR file that contains this class must be in a directory defined in the ColdFusion classpath.

Driver Name

(Optional) The name of the driver.

User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a user name (for example, in a cfquery tag).

Password

The password that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Description

(Optional) A description for this connection.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Restrict Connections To

Specifies the maximum number of database connections for the data source. To use this restriction, enable the Limit Connections option.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the attempt to log in to the data source connection.

CLOB

Select to return the entire contents of any CLOB/Text columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting.

BLOB

Select to return the entire contents of any BLOB/ Image columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting.

LongText Buffer

The default buffer size; used if Enable Long Text Retrieval (CLOB) is not selected. The default value is 64000 bytes.

BLOB Buffer

The default buffer size; used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

For example, you can use the Other Data Sources option to define a data source for DB2 OS/390 or iSeries, using the following settings:

  • JDBC URL jdbc:datadirect:db2://dbserver:portnumber
  • Driver class .jdbc.Driver
  • Driver name DB2
  • User name A user defined to the database
  • Password The password for the user name
  • Connection stringSpecify one connection string for the first connection, and then modify it for use in subsequent connections, as follows:

    1. On the initial connection, specify LocationName, CollectionId, CreateDefaultPackage, and sendStringParametersAsUnicode (with no spaces) as the following example shows:

      sendStringParametersAs
      Unicode=false

      If the database uses Unicode, specify true for the sendStringParametersAsUnicode_ parameter._

    2. On subsequent connections, specify LocationName, CollectionId, and sendStringParametersAsUnicode, as the following example shows:

      ParametersAsUnicode
      =false

      DB2 OS/390 refers to all supported versions of DB2 on OS/390 and z/OS platforms. DB2 iSeries refers to all supported versions of DB2 on iSeries and AS/400.

      For more information on DB2, see Connecting to DB2 Universal Database.

Connecting to PostgreSQL

To see a list of PostgreSQL versions that ColdFusion supports, go to www.adobe.com. Use the settings in the following table to connect ColdFusion to PostgreSQL data sources:

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

Database

The database to which this data source connects.

Server

The name of the server that hosts the database that you want to use. If the database is local, enclose the word local in parentheses. This name must be either a fully qualified domain name (resolvable through DNS) or an IP address. It cannot be a netbios name (even if you are running NBT), or an alias you set up using the client connectivity wizard (both of these approaches worked in earlier ColdFusion versions).

Port

The number of the TCP/IP port that the server monitors for connections.

User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a user name (for example, in a cfquery tag).

Password

The password that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Description

(Optional) A description for this connection.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Restrict Connections To

Specifies the maximum number of database connections for the data source. To use this restriction, enable the Limit Connections option.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the attempt to log in to the data source connection.

CLOB

Select to return the entire contents of any CLOB/ Text columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting.

BLOB

Select to return the entire contents of any BLOB/ Image columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting.

LongText Buffer

The default buffer size; used if Enable Long Text Retrieval (CLOB) is not selected. The default value is 64000 bytes.

BLOB Buffer

The default buffer size; used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

Validation query

Called when a connection from the pool is resued. This can slow query response time because an additional query is generated. Specify the validation query before restarting the database to verify all connections, but remove the validation query after restarting the database to avoid any performance loss.

Connecting to Sybase

To see a list of Sybase versions that ColdFusion supports, go to https://helpx.adobe.com/pdf/coldfusion2018-support-matrix.pdf. Use the settings in the following table to connect ColdFusion to Sybase data sources:

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

Database

The database to which this data source connects.

Server

The name of the server that hosts the database that you want to use. If the database is local, enclose the word local in parentheses. This name must be either a fully qualified domain name (resolvable through DNS) or an IP address. It cannot be a netbios name (even if you are running NBT), or an alias you set up using the client connectivity wizard (both of these approaches worked in earlier ColdFusion versions).

Port

The number of the TCP/IP port that the server monitors for connections.

User name

The user name that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a user name (for example, in a cfquery tag).

Password

The password that ColdFusion passes to the JDBC driver to connect to the data source if a ColdFusion application does not supply a password (for example, in a cfquery tag).

Description

(Optional) A description for this connection.

Connection String

A field that passes database-specific parameters, such as login credentials, to the data source.

Select Method

Determines whether server cursors are used for SQL queries.

·         The Direct method provides more efficient retrieval of data when you retrieve recordsets in a forward-only direction and you limit your Sybase connection to a single open SQL statement at a time. This is typical for ColdFusion applications.

·         The Cursor method lets you have multiple open SQL statements on a connection. This is not typical for ColdFusion applications, unless you use pooled statements.

Limit Connections

Specifies whether ColdFusion limits the number of database connections for the data source. If you enable this option, use the Restrict Connections To field to specify the maximum.

Restrict Connections To

Specifies the maximum number of database connections for the data source. To use this restriction, enable the Limit Connections option.

Maintain Connections

ColdFusion establishes a connection to a data source for every operation that requires one. Enable this option to improve performance by caching the data source connection.

Max Pooled Statements

Enables reuse of prepared statements (that is, stored procedures and queries that use the cfqueryparam tag). Although you tune this setting based on your application, start by setting it to the sum of the following:

·         Unique cfquery tags that use the cfqueryparam tag

·         Unique cfstoredproc tags

Timeout (min)

The number of minutes that ColdFusion maintains an unused connection before destroying it.

Interval (min)

The time (in minutes) that the server waits between cycles to check for expired data source connections to close.

Disable Connections

If selected, suspends all client connections.

Login Timeout (sec)

The number of seconds before ColdFusion times out the attempt to log in to the data source connection.

CLOB

Select to return the entire contents of any CLOB/ Text columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting.

BLOB

Select to return the entire contents of any BLOB/ Image columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting.

LongText Buffer

The default buffer size; used if Enable Long Text Retrieval (CLOB) is not selected. The default value is 64000 bytes.

BLOB Buffer

The default buffer size; used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

Validation query

Called when a connection from the pool is resued. This can slow query response time because an additional query is generated. Specify the validation query before restarting the database to verify all connections, but remove the validation query after restarting the database to avoid any performance loss.

Client Hostname

The host name from where the query is executed.

Client Username

The user name if the user is logged in using the <cflogin> tag.

Application Name

The application name specified in the application.cfc.

Prefix

If specified, the value is prefixed with the application name specified in application.cfc.

Enable connection validation

Check if to validate the connection.

Connecting to JNDI data sources

Use the settings in the following table to connect ColdFusion to JNDI data sources that are defined for a JEE application server (J2EE configurations only):

Setting

Description

CF Data Source Name

The data source name (DSN) that ColdFusion uses to connect to the data source.

JNDI Name

The JNDI location in which the J2EE application server stores the data source.

User name

The user name that ColdFusion passes to JNDI to connect to JNDI if a ColdFusion application does not supply a user name (for example, in a  cfquery  tag).

Password

The password that ColdFusion passes to JNDI to connect to the data source if a ColdFusion application does not supply a password (for example, in a  cfquery  tag).

Description

(Optional) A description for this connection.

JNDI Environment Settings

Specifies additional JNDI environment settings, if necessary by the JNDI data source. Use comma-separated list of name-value pair. For example if you must specify a user name and password to connect to JNDI, specify the following: SECURITY_PRINCIPAL=" myusername ",SECURITY_CREDENTIALS=" mypassword "

CLOB

Select to return the entire contents of any CLOB/ Text columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the Long Text Buffer setting.

BLOB

Select to return the entire contents of any BLOB/ Image columns in the database for this data source. If not selected, ColdFusion retrieves the number of characters specified in the BLOB Buffer setting.

LongText Buffer

The default buffer size; used if Enable Long Text Retrieval (CLOB) is not selected. The default value is 64000 bytes.

BLOB Buffer

The default buffer size; used if the BLOB option is not selected. The default value is 64000 bytes.

Allowed SQL

The SQL operations that can interact with the current data source.

The ColdFusion Administrator does not display the JNDI data source option when running in the server configuration.

Connecting to an external JDBC Type 4 data source

To use a JDBC driver that is not included with ColdFusion (such as SQLAnywhere) configure the JDBC driver and add a data source for it.

Connect to an external JDBC data source:

  1. Copy the database driver .jar file to one of the following directories:
    • (server configuration only) cf_root/lib
    • (JEE configuration) cf_webapp_root/WEB-INF/cfusion/lib

  2. Restart ColdFusion.

    In Windows, ensure that you restart all of the ColdFusion services.
  3. In the ColdFusion Administrator, add the other JDBC Type 4 data source, selecting Other from the Driver drop-down list.For more information, see the chapter on data source management in Configuring and Administering ColdFusion.
    You can now connect to an external JDBC Type 4 data source.
Adobe-logo

Kirjaudu sisään tiliisi

Pikalinkit

Näytä kaikki sopimuksesi Sopimusten hallinta

Legal Notices    |    Online Privacy Policy