A

Anonymous

Answered

Connecting to MS SQL 2005 via JDBC Driver

Forum|Forum|19 years ago
January 15, 2007
7 replies
1360 views

I have an application in which I use Java to abstract database elements as objects and handle most of the server-side processing. This is done by creating a Java object via CreateObject(). The Java then connects to a MS SQL 2005 database using the JDBC 1.1 Driver.

The following tutorial explains how to set this up: http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=ded4216b. It instructs you to create a Data Source, much like how you typically would in "regular" ColdFusion applications. Then, using some simple Java, I am successfully able to connect to the database, query it, and get results. The connection code is attached.

As you can see, I am still passing all the connection parameters to the Database class constructor. My question is: how would I go about connecting to the database by simply using the Data Source Name, as you would, again, in "regular" ColdFusion applications. Is this possible at all?

Thanks!

Database access

This topic has been closed for replies.

Correct answer

Ok, this should be all a smart guy like you needs to grab a datasource connection and pass it down to Java...

It works, thanks a lot. Here's my final code:

Application.cfm

<cfset dbConnection = CreateObject("java", "coldfusion.server.ServiceFactory").DataSourceService.GetDataSource(strDataSource).getConnection()>
<cfset REQUEST.db = CreateObject("java", "Database").init(dbConnection)>

Then, I can pass the reference to the database connection while instantianting objects of other classes, so that they may access the database.

For example:

<cfset user = CreateObject("java", "User").init(REQUEST.db, someUserID)>

Again thanks a lot everyone!

BKBK

Community Expert

> If you insist...
MikerRoo is right to sound a caution. Connecting to the database, like take-off and landing, remains a precarious operation at the best of times. To connect Java to the database through Coldfusion means adding one more interface between Java and the database. The risks of a crash would increase. And all that just to avoid passing the username and password?

It is not clear whether you, Nottaken959595952, are aware that Coldfusion, too, actually has to pass the username and password. Just like Java. However, in Coldfusion you can configure those settings in the Coldfusion Administrator. That is more or less equivalent to caching a Java connection to the database, something you might probably be doing already.

A

Anonymous

Thanks for the input guys.

Perhaps I did not explain my intent very clearly. What I am trying to accomplish is have a vast majority of the server-side code done in Java. With that, I would be able to do things like:

<cfset myUser = CreateObject("java", "User").init(5)>
<cfoutput>#myUser.getFirstName()#</cfoutput>

However, unless I am able to connect to the database in Java (which I currently AM able to do, but just not quite the way I want to), I would have to run all the queries in CF and then pass them to the different objects so that they can then process the query results. This isn't a very smart thing to do, seeing that it binds me to a lot of CF while I am trying use it as little as possible (session management and user interface "tier" only).

So no, I am not going all out with Java just to avoid passing username/password, but instead to use true OOP instead of CF Components. I realize ColdFusion too passes this info, but it does not show up in the source code, which is precisely what I need to do, but with Java.

Basically, I have everything working the way I need now, except I need to be able to connect using a data source name rather than passing the parameters, if at all possible.

A

Anonymous

Ok, this should be all a smart guy like you needs to grab a datasource connection and pass it down to Java...

BKBK

Community Expert

Here, we're into "regular" Coldfusion, as you yourself put it. You should go to a Java forum or have a look at a JDBC tutorial

BKBK

Community Expert

> ... is there a Java command that will allow me to connect to the Database
> using ONLY the Data Source NAME, rather than having to set all connection
> the parameters?]

Here then is the beauty of Coldfusion's interaction with the database

<cfquery name="myQuery" datasource="mssqlDSN">
select *
from myTable
</cfquery>

A

Anonymous

Thanks, again, but I need to be able to connect using Java so that I can run all the queries there, rather than through ColdFusion.

A

Anonymous

Far better to use java from CF rather than the other way around.

If you insist, then you must compile with cfusion.jar.
Then there are undocumented ways to access the datasources or you can call a CFC, via the CFCProxy API, and return the appropriate context (again all undocumented).

BKBK

Community Expert

Remove the dot at the end of the link. It doesn't work otherwise.

I, too, have installed MS SQL 2005 and the JDBC 1.1 driver for use with Coldfusion MX7.0.2. I followed the steps in the link you provided.

I went to the Coldfusion Administrator and put C:\Program Files\Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.1\enu\sqljdbc.jar in the classpath. I then created a datasource using the following settings

CF Data Source Name: mssqlDSN
JDBC URL : jdbc:sqlserver://localhost:1433;DatabaseName=myMSSQLDB
Driver Class : com.microsoft.sqlserver.jdbc.SQLServerDriver
Driver Name : mssql2005
User Name : sa
Password : fgT5nUK7

mssqlDSN and mssql2005 are user-defined, and can be any name you like. sa is the standard administrator name for MS SQL. I had configured MS SQL 2005 with the recommended SQL Server Authentication, not Windows Authentication. However, for security purposes, for example, on a production server, you should not use the admin sa account. You should then create an account with less privileges for Coldfusion.

A

Anonymous

Thanks, but I'm afraid that still does not answer the question. Once the Data Source is setup in the ColdFusion Admin, is there a Java command that will allow me to connect to the Database using ONLY the Data Source NAME, rather than having to set all connection the parameters? I need this so that I don't have my username and password in the code, which is fine for development, but not an option for production.

S

SpiderFromMars

Inspiring

It would be interesting to see if this is possible.

Please post the result of your research. This is a very useful concept.

A

Anonymous

I could, but I would like to do it "inside" the Java, which works now, but it would be nicer/better if I didn't have to script in the connection parameters, seeing that there already is a data source name defined with such parameters.

S

SpiderFromMars

Inspiring

Can you simply perform the query outside of the Java object and pass the query structure into your object?

Cheers,
Peter

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded