Data & Services - Data Sources

// Application.cfc
component {
  this.name = "MyApplication";

  // Define datasource inline
  this.datasources["mydb"] = {
    driver: "MySQL",
    host: "localhost",
    port: 3306,
    database: "mydbname",
    username: "dbuser",
    password: "dbpassword",

    // Connection pool settings
    connectionLimit: 50,
    connectionTimeout: 30,
    loginTimeout: 30,

    // Validation
    validate: true,
    validationQuery: "SELECT 1",

    // Advanced settings
    blob: true,
    clob: true,
    storage: true
  };

  // Set default datasource
  this.datasource = "mydb";
}

<!--- Application.cfc --->
<cfcomponent>
  <cfset this.name = "MyApplication">

  <!--- Define datasource inline --->
  <cfset this.datasources["mydb"] = {
    driver: "MySQL",
    host: "localhost",
    port: 3306,
    database: "mydbname",
    username: "dbuser",
    password: "dbpassword",

    connectionLimit: 50,
    connectionTimeout: 30,
    loginTimeout: 30,

    validate: true,
    validationQuery: "SELECT 1",

    blob: true,
    clob: true,
    storage: true
  }>

  <!--- Set default datasource --->
  <cfset this.datasource = "mydb">
</cfcomponent>

// Using default datasource (if set in Application.cfc)
users = queryExecute("
  SELECT id, firstName, lastName, email
  FROM users
  WHERE active = :active
", {
  active: { value: 1, cfsqltype: "cf_sql_bit" }
});

// Or specify datasource explicitly
users = queryExecute("
  SELECT id, firstName, lastName, email
  FROM users
  WHERE active = :active
", {
  active: { value: 1, cfsqltype: "cf_sql_bit" }
}, {
  datasource: "mydb"
});

// Loop through results
for (user in users) {
  writeOutput("#user.firstName# #user.lastName#<br>");
}

<!--- Using default datasource --->
<cfquery name="users">
  SELECT id, firstName, lastName, email
  FROM users
  WHERE active = <cfqueryparam value="1" cfsqltype="cf_sql_bit">
</cfquery>

<!--- Or specify datasource explicitly --->
<cfquery name="users" datasource="mydb">
  SELECT id, firstName, lastName, email
  FROM users
  WHERE active = <cfqueryparam value="1" cfsqltype="cf_sql_bit">
</cfquery>

<!--- Loop through results --->
<cfoutput query="users">
  #firstName# #lastName#<br>
</cfoutput>

// Test datasource connection
try {
  queryExecute("SELECT 1", {}, { datasource: "mydb" });
  writeOutput("Datasource connection successful!");
} catch (any e) {
  writeOutput("Datasource connection failed: #e.message#");
  writeLog(
    file: "datasource-errors",
    type: "error",
    text: "Connection test failed: #e.message# | Detail: #e.detail#"
  );
}

<!--- Test datasource connection --->
<cftry>
  <cfquery datasource="mydb">
    SELECT 1
  </cfquery>
  <cfoutput>Datasource connection successful!</cfoutput>

  <cfcatch type="any">
    <cfoutput>Datasource connection failed: #cfcatch.message#</cfoutput>
    <cflog
      file="datasource-errors"
      type="error"
      text="Connection test failed: #cfcatch.message# | Detail: #cfcatch.detail#">
  </cfcatch>
</cftry>