I need to create a DSN Less connection in ColdFusion 10 to an Oracle 10g database

Need to create a DSN less connection from ColdFusion 10 to an Oracle 10 or 11g database.

Create a connection to the an Oracle 10g database without using ColdFusion Administrator.

I have searched the internert and there is reference to a CFQuery attribute called dbType dynamic with another called connectstring but I can't get these working.

We already have an application module created that we need to create a connection to an Oracle 10g database without using the ColdFusion Admin to make the connection.

Please Assist
demergAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
I have searched the internert and there is reference to a CFQuery attribute called dbType dynamic with another called connectstring but I can't get these working.

DSN-less queries were only supported in older versions of CF. They discontinued support for them a while back. So that's why it doesn't work in CF10.

It's possible to connect to a database without a DSN, but it's a LOT more complicated, and less performant.  So I wouldn't recommend it.. Can you explain more about why you can't set up  a dsn?

In any case, if you really must do this, here's a simplified connection example for SQL server, but the concept is the same for Oracle, just change the driver/connection settings:
http://www.experts-exchange.com/Software/Server_Software/Web_Servers/ColdFusion/Q_26371168.html#a33337078
0
Brijesh ChauhanStaff IT EngineerCommented:
Here is what you can try, it worked for me on CF 10, explanation and code here,
http://www.neiland.net/blog/article/how-to-create-a-dsn-less-database-connection-in-coldfusion/

<cfset factory = createObject("java", "coldfusion.server.ServiceFactory") />
<!---<cfdump var="#factory.dataSourceService.getDrivers()#">--->

<!--- Load the database driver using java class loader --->
<cfset classLoader = createObject("java", "java.lang.Class")>
<cfset classLoader.forName("com.mysql.jdbc.Driver")>

<!--- Create a driver manager instance --->
<cfset driverManager = createObject("java","java.sql.DriverManager")>

<!--- Create the connection to the DB --->
<cfset connection = driverManager.getConnection(
"jdbc:macromedia:oracle://[host]:[port];SID=[sid];AuthenticationMethod=userIDPassword;sendStringParametersAsUnicode=[sendStringParametersAsUnicode];querytimeout=[qTimeout];[args]", 'UserID', 'Password')>
 

 
<!--- <!---Create a connection statement and run our query string --->
<cfset statement = connection.createStatement()>      
<cfset resultSet = statement.ExecuteQuery("SELECT * FROM yourTableName")>--->

<cfdump var="#connection#">

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brijesh ChauhanStaff IT EngineerCommented:
Also, just read the question again, this will NOT work with CFQUERY, simply because doing this we are bypassing CFQUERY Tags, and this CFQUERYPARAM will also not work.....
0
_agx_Commented:
brijeshchauhan - FYI that's exactly what I described above, just with the oracle driver.

this will NOT work with CFQUERY, simply because doing this we are bypassing CFQUERY Tags, and this CFQUERYPARAM will also not work.....

Right, but still possible. Since you're using low level jdbc stuff, you have to use bind variables instead of cfqueryparam.  Definitely a lot more work than cfquery. That's why I said I don't recommend it.
0
demergAuthor Commented:
Tnx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.