DSN or other way to connect?

[oaklanders]

For the past 5 years I been using DSN connections to connect to Oracle in my Cold Fusion. Is there any other way to connect in Cold Fusion MX 7 without using DSN?

<CFQUERY NAME="query_name" DATASOURCE="db_name">

SQL statements

</CFQUERY>

For example in Java, I can do this and not use any DSN:

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@hostname:1526:orcl", "scott", "tiger");
                // @machineName:port:SID,   userid,  password

Please advise.

 

[Sarky78]

found this from google:

<cfscript>

classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
dm = createObject("java","java.sql.DriverManager");

con = dm.getConnection(connection string);

st = con.createStatement();
rs = st.ExecuteQuery("Select * FROM table");
q = createObject("java", "coldfusion.sql.QueryTable").init(rs);

</cfscript>

you should be able to do the same thing without using cfscript if you want to

Hope this helps!

Tony

 

[Glowball]

I've done it before and though it's possible I wouldn't recommend it. ColdFusion just doesn't do it very well. I was using it to grab page content from a database, so there was a very simple, single query per page. There was always about a 4-second delay while it performed the query and got the results.

I don't know what your reasons are for trying this, but ours were to get around an uncooperative IT department (they controlled CF Administrator). In the end, we had to scrap the whole project because the query delay was way too great to use on a public Web site.

If you really want to do this I'd be happy to send you the CFCs I wrote. It seems like it should be faster but it just isn't.

 

[cfSearching]

I agree with Glowball. While you can open your own jdbc connection, it may slower. One reason is it does not take advantage of CF's connection pooling. At least not out of the box. Opening a connection is expensive. That often accounts for the lag Glowball described.

If you still decide to open your own jdbc connection, always close the connection when you are finished! Or you could end up with an even worse problem



----------------------------------

http://cfsearching.blogspot.com/

 

[oaklanders]

Thanks, I will scratch the attempt.

I am used to JDBC connection where I have to close my Connection, ResultSet and Statement.

I assume Cold Fusion closes this in the CFOUTPUT end tag area??

<CFQUERY NAME="query_name" DATASOURCE="db_name">

SQL Statement

</CFQUERY>
<CFOUTPUT query="query_name">
Department: #Qry_Employee.Dept_ID# ID:
#Qry_Employee.Emp_ID# - #FirstName# #LastName#<br>
</CFOUTPUT>

 

[Glowball]

ColdFusion works with databases in a way that is completely unlike all other languages. It separates the connection information from the code so you can safely allow developers to work on the code without having to know the database connection information. That's great if the CF Admin and the developer are two different people, but if it's the same person it's kind of annoying at times (in my opinion). It would be nice if ColdFusion allowed DSN-less connections that were just as speedy.

ColdFusion itself takes care of opening, maintaining persistence, and closing database connections. You never have to concern yourself with database connections at all. If you had a page and all it had on it was one <cfquery> tag and SQL statement, ColdFusion would handle everything with the datasource call.

If you're just getting started with ColdFusion, you might want to research using a <cftry>/<cfcatch> block around <cfquery> tags, so you can safely exit from any issues that might arise from querying a database. Also look into <cfqueryparam> to make sure the hackers can't do bad things.

ColdFusion makes things extremely easy compared to other languages. Enjoy!

 

[oaklanders]

Thanks for all the info.

I assume <cfqueryparam> is the same as Java's PreparedStatement class?

 

[Glowball]

Sorry, I don't know Java well enough to be able to answer your question. I would guess that Java has something very much like <cfqueryparam>, though.