Skip to main content
R
robwilliamswv
Participant
June 7, 2009
Question

DB connection without DSN?

  • June 7, 2009
  • 1 reply
  • 3152 views

I'm using a shared CF 8 environment, but the administrator has been very slow in setting up a DSN for my application.  I've tested locally and need my app live yesterday.  I know the 'C:' path for where the Access DB (MDB) has been stored; is there anyway to create a JDBC connection using CFSCRIPT or CFOBJECT in CF 8 so I can use the DB without a DSN?

I've got this, which works without throwing an error, but how do I use this in, for example, a cfquery statement to get data from the DB?

<cfobject type="COM"
          name="ConnectionObject"
          class="ADODB.Connection"
          action="CREATE">

<cfscript>

  Driver="Microsoft Access Driver (*.mdb)";
  DataBasePath="C:\websites\...\eventcal.mdb";
  Source="Driver=#Driver#;DBQ=#DataBasePath#";

  ConnectionObject.Open("#Source#");

</cfscript>


<!--- Close Object--->   
<cfset ConnectionObject.Close()>

This topic has been closed for replies.

1 reply

C
craigkaminsky
Inspiring
June 7, 2009

Sorry to hear that your host has been so slow to respond. That sucks!

You can connect to any DB without a DSN in CF. This article (with code samples) should do the trick:

http://www.hostmysite.com/support/cfusion/dsnless/

The downside is that you aren't (so far as I know) able to use CFQUERY (as you'll see in the code samples); rather, you need to write and execute the SQL more like is done with PHP and other server-side languages.

Of course you can setup a DSN programmatically via the Administrator API but I'm guessing that your hosting provider doesn't/won't allow that.

A
Anonymous
June 10, 2009

ok thats fine..

            Then how do we implement query of query in this case?

Thanks in advance

C
craigkaminsky
Inspiring
June 10, 2009

The code below returns a CFQUERY object. So, you can use a QofQ just as you would otherwise.

<cfscript>
     classLoader = createObject("java", "java.lang.Class");
     classLoader.forName("macromedia.jdbc.MacromediaDriver");
     dm = createObject("java","java.sql.DriverManager");
        // I don't have Access (I use SQL Server) but here's a sample of the Access Conn String
        // jdbc:sequelink:msaccess://[host]:[19998];serverDatasource=[datasource]
     con = dm.getConnection(
          "jdbc:macromedia:sqlserver://_path.to.your.server_:1433;databaseName=_db_name_;
          SelectMethod=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=1000", 
       "_db_user_", "_db_pass_");
     st = con.createStatement();
     rs = st.ExecuteQuery("Select * FROM _table_");
     q = createObject("java", "coldfusion.sql.QueryTable").init(rs);
</cfscript>
<!--- The Query --->
<cfdump var="#q#">
<!--- The table I selected from has a task field, some of which contain the word 'web' --->
<cfquery name="q2" dbtype="query">
     select * from q where task like '%Web%'
</cfquery>
<!--- The QofQ dump --->
<cfdump var="#q2#">

This code uses a slightly different connection (not using sun.jdbc as the link from my previous post indicates). This was run from ColdFusion 8.0.1 on a Mac connecting to a SQL Server running on Win 2K3.

Adobe Tech Note on Pure Java connections: http://kb2.adobe.com/cps/196/tn_19607.html

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices