jTDS and CFMX 6.1 Installation?

[redtopia]

I'm wondering if someone can help me install jTDS into my CFMX 6.1 world.

I copied the jtds-1.2.jar file to the C:\CFusionMX\lib directory.

In the Administrator, I created a new "other" datasource using this JDBC URL:

jdbc:jtds:sqlserver://127.0.0.1:2195\SQLEXPRESS/TVRCampSQL;useLOBs=false

I set the driver class to: (**** I think this is the problem ****)

net.sourceforge.jtds.jdbc.Driver

I set the driver name to:

jtds-1.2.jar

And I entered the username/password for the database in the username and
password fields of the driver dialog.

However, I get the following error:

Connection verification failed for data source: test2
[]java.sql.SQLException: No suitable driver available for test2, please check
the driver setting in resources file, error: net.sourceforge.jtds.jdbc.Driver
The root cause was that: java.sql.SQLException: No suitable driver available
for test2, please check the driver setting in resources file, error:
net.sourceforge.jtds.jdbc.Driver

[PaulH **AdobeCommunityExpert**]

redtopia wrote:

> jdbc:jtds:sqlserver://127.0.0.1:2195\SQLEXPRESS/TVRCampSQL;useLOBs=false

jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]

wrong slash, what's SQLEXPRESS, an instance? if so you have to split that off as
a property (instance=SQLEXPRESS). also i *think* the user & password are required.

> I set the driver class to: (**** I think this is the problem ****)
>
> net.sourceforge.jtds.jdbc.Driver

that looks right.

> I set the driver name to:
>
> jtds-1.2.jar

that looks wrong (though it might not matter), try
net.sourceforge.jtds.jdbc.Driver & see what happens.

finally, did you stop/restart the cf server after you copied in the driver jar?

[redtopia]

Thank you so much! I restarted the CFMX service and was able to connect to the
driver.

My connection string is now:


jdbc:jtds:sqlserver://REDWING:1433/TVRCampSQL;user=tvrcamp;password=test123;useL
OBs=false;

Initially, I was getting network connection errors, but after tweeking the
connection string, I was finally able to get the driver to attempt to login,
which of course is failing. I've got the username and password correct, but the
driver still tells me that the login is failing:

Connection verification failed for data source: test
[]java.sql.SQLException: Login failed for user 'tvrcamp'.
The root cause was that: java.sql.SQLException: Login failed for user
'tvrcamp'.

Could this still be related to the network connection? Or what else could be
giving me this error?

Thanks in advance!

[PaulH *ACE*]

redtopia wrote:

> which of course is failing. I've got the username and password correct, but the
> driver still tells me that the login is failing:
>
> Connection verification failed for data source: test
> []java.sql.SQLException: Login failed for user 'tvrcamp'.
> The root cause was that: java.sql.SQLException: Login failed for user
> 'tvrcamp'.

that user an sql server user which has permissions to that db? jTDS can
do windows authentication but i don't recall how.

[redtopia]

I'm using SQL authentication, not NT.

I have another DSN using the ODBC Socket, that is going to the same
db/username/password, and it works fine. But I found a bug in that driver where
ntext fields are not being returned unless they are the last rows in the table,
or you explicity ask for them by name in the query. So I am trying to replace
my connections using the jtds drivers.

I have a feeling the login error is not really telling the full story. I know
the username/password is correct, and I know that I can connect to the db using
the ODBC Socket, though I have not been successful at connecting to the db
using the built-in SQL Server driver. It probably has something to do with the
port/instance. It seems like I should somehow set the instance name
"SQLEXPRESS," but when I do this, I get another error:

# Connection verification failed for data source: test
[]java.sql.SQLException: Server 127.0.0.1 has no instance named SQLEXPRESS.
The root cause was that: java.sql.SQLException: Server 127.0.0.1has no
instance named SQLEXPRESS.

[redtopia]

OK, I tweeked the settings in the SQL Server Configuration program and turned
on the TCP/IP connection in the Protocols for SQLEXPRESS. I setup the localhost
IP address (127.0.01) to listen to port 2195. I changed my connection string to:


jdbc:jtds:sqlserver://127.0.0.1:2195/TVRCampSQL;user=tvrcamp;password=testing123
;useLOBs=false;instance=SQLEXPRESS;

and I'm now getting the error:

Connection verification failed for data source: test
[]java.sql.SQLException: Cannot open database "TVRCampSQL" requested by the
login. The login failed.
The root cause was that: java.sql.SQLException: Cannot open database
"TVRCampSQL" requested by the login. The login failed.

I wonder why it can't open the database? FYI: That login (in SQL Server) is
setup to open TVRCampSQL by default.

[redtopia]

Whoops... I had the database name wrong.

I got it working now. Thanks so much for your help!