How can I connect to a SQL Server in ColdFusion 7 without a DSN?

Question

I'm writing a CFC on a server running Coldfusion MX 7. The CFC needs to be able to query a database the location of which will be determined at runtime. The database is SQL Server 2005. The CFC takes the IP address of the server and the name of the database as arguments, and the built-in Windows Authentication should allow connections from the machine. However, I can't figure out how to code the connection. The <cfquery> tag hasn't supported the connectstring attribute since CF5. Setting up a System DSN isn't dynamic enough. I know there's probably some way to connect to SQL Server without using a DSN, but the method escapes me at the moment. Does anyone know how to do this?

Answer 1

not in CF7, but you can add, modify, and delete ColdFusion data sources in CF8 via Administrator API

Answer 2

Use Web Services to hit the DB.

Answer 3

bypass cfquery, do JDBC in Java?

Answer 4

Google search proposes this solution: http://cfsilence.com/blog/client/index.cfm/2007/11/8/More-cfquery--Now-With-cfqueryparam

Have you tried it?

Don't have CF7 so can't say that all Java stuff from that tag works. At least it does not use ServiceFactory (aka solution for CF8).

Answer 5

You can modify the datasource on CF7 using the datasourceService. Something along these lines might work:

<cfset service = createobject("java","coldfusion.server.ServiceFactory").getDatasourceService()>
<cfset uname = "yourUserName">
<cfset pw = service.encryptPassword("yourPassword")>
<cfdump var="#createobject("java","coldfusion.server.ServiceFactory").getDatasourceService()#">
<cfset ds = service.getDatasources()>
<cfset dsn = "MyDSN">
<cfset thisDS = service.getDatasource(dsn)>
<cfset thisDS.getDatasourceDef().setPassword(pw)><!--- if you need to set the password programmatically --->
<!--- do stuff to set the URL... you can get the appropriate functions from the cfdump of the datasourceservice, above --->
Verifying datasource: #service.verifyDatasource(dsn)# <br>

Answer 6

After looking at everyone's answers and doing some more Google searches, I got a simple solution worked out. This solution uses some Java code, as Henry suggested, but doesn't require compiling a jar. It can be done inline using <cfscript>.

<cfscript>
    classLoader = createObject("java", "java.lang.Class");
    classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
    dm = createObject("java","java.sql.DriverManager");
    con = dm.getConnection("jdbc:odbc:DRIVER={SQL Server};Database=""DATABASENAME"";Server=SERVERNAME;","USERNAME","PASSWORD");
    st = con.createStatement();
    rs = st.ExecuteQuery("SELECT * FROM TABLE");
    q = createObject("java", "coldfusion.sql.QueryTable").init(rs);
</cfscript>

I think this is easier than programatically adding datasources to ColdFusion Administrator, although that seems like a good solution too. I'm guessing the custom tag Sergii linked works similarly, although I didn't see the link until after I tried this solution, so I haven't examined the code in detail.