I'm trying to setup a 3rd party JDBC driver as a datasource, but it appears to not matter what I put in the Connection String field in CFAdmin, it doesn't seem to use it at all.
Errors out with
Login failed: You must provide a user and password to login.
I tried putting random text into the Connection String field expecting it to error out, but nothing happened either.
I was wondering if I was entering the data wrong (I also tried the entire connection string with the jdbc:, but that did not work either) or if there was some way to verify the entire connection string that ColdFusion was trying to use.
I'm not trying anything special inside of my cfquery tag:
<cfquery name="output" datasource="mySource"> so that should not be the issue. I can get around the user/password issue by hard-coding these values in the cfquery tag, but there is a 3rd key that I need to provide as a connection value as well.
The issue shouldn't be with my connection string, because the following worked with pure Java:
conn = DriverManager.getConnection("jdbc:mydriver:User=user;Password=password;AccessToken=accessToken;");
An interesting addition...ColdFusion appears to not care at ALL what is at the end of my URL as long as the beginning is correct...
gives the username/password error. Whereas:
gives me an "unable to find driver" error.
On your CFAdmin page, click the database and check for this.
JDBC URL : jdbc:oracle:thin:@#Ip address of the database#:#port#:#username#
There is a colon in between the database and the port and port and the username.
Also, your driver class should be oracle.jdbc.OracleDriver
I m assuming you are using Oracle.
As I said, I'm using a 3rd party jdbc driver, not Oracle.
And the connection string that I used in Java (shown above) works just fine. There is no database to connect to persay. It is also a local data source.
What datasource driver are you selecting in cfadmin?
When you say that you got the connection working using pure java was that from the same server?
Are you sure that the drivers are installed and available to the cf server? Are the jar files associated with your 3rd party driver listed in the java class path on your cf server's system information page (from the cfadmin)?
We have used 3rd party drivers with cf before and although I have seen similar situations as you mention (settings being ignored) we have always gotten them to work eventually.
I'm selecting other.
Yes, the same machine got the connection working using pure Java
The driver is definitely installed and available, because if I move them out of the lib folder I get a different error. I don't see the driver in the java class path on the system information page though.
I can't remember for sure if the drivers need to be listed under CF's classpath or not. It has been a while since we configured ours. Typically we can take the connection string used for a java connection directly over to the CF datasource page and plug that in. So, in your case, I would think that including your parameters with the jdbc url would work. What error does that give you?
if I set the JDBC URL to jdbc:mydriver:User=username;Password=password;
|Login failed: You must provide a user and password to login.|
Which isn't that surprising considering this works as a connection string (see first post):
When you say the mydriverrrrrrrrrrrrr... works as a connection string what do you mean? The connection actually works with that or it allows you to enter that in the datasource dialog without error? I don't think CF cares what you put in the connection string because it has no way of knowing what your specific driver wants/needs. So it just passes whatever you put in there at connection time and lets the driver deal with it (I think).
Are you including the url to your database in the jdbc url string? It should be something like:
What happens when you put the username and password in the supplied fields of the datasource in cfadmin (not in the connection string box)?
Where are you telling it the dbserver to connect to (if not in the jdbc url)?
It allows me to enter it in the datasource without an error, but will give me a missing username/password, whereas if I have an incorrect character before t he end it will not.
There is no database URL, the connection string I showed you is exact.
If I put the username and password into the supplied fields, it will attempt to connect, but it fails because the accesstoken is not set anywhere, so those fields do work.
How does it know where to connect with no db server specified? Defaults to local or something?
Anyway, if the username and password fields seem to work have you tried using those and supplying only the accesstoken in the connection string box? Or the accesstoken as part of the jdbc url?
The db server is specified inside of the JAR if I'm not mistaken...this is just to connect to the JAR persay.
Yes I have, and neither works, which isn't that surprising since CF appears to be ignoring both of those fields regardless of what I put in them
I think you are right about CF ignoring what you put in those fields but it should be passing the values you enter to your driver (jar). Does your driver have any logging? What do those logs show?
From what you have described I think CF is communicating with your driver. You said that if you put the username and password in the CF datasource fields then it attempts to connect but fails because of the accesstoken. So how is this accesstoken supposed to work with your database? Is it set per query, user, connection? Is it dynamic or static? Where do you get the accesstoken (from the db server)?
If you can specify the database server to connect to within the JAR then perhaps you can also specify the accesstoken there? At this point I would start looking at the driver you are using for logging/debugging options.
Unfortunately, there is no logging
It is static, and set per user. I got the accesstoken from the provider if I'm not mistaken.
The driver is a middleman to salesforce, that I don't necessarily have direct access to modify
Yep, tried "AccessToken", "Access Token", and "Access_Token"
I feel like the root of my issue is CF basically ignoring any of my input, but I have no idea how to fix this, and I don't have another driver I could test this scenario with.
I guess I'm on the opposite side of you. The connection string option is there to provide specific parameters that are needed by the driver you are using. ColdFusion does not know what those parameters may be so it allows you to enter whatever you want and just passes whatever you enter to the driver. It is up to the driver to interpret the string. Does that make sense?
I don't understand how your jdbc url is missing the database server either. That does not make sense to me?
Oh I agree, but why would this:
work as a jdbc URL? Would that be an issue with the driver?
It seems like the URL/connection string that worked in Java should just work in CF if it was being passed directly to the driver.
I'm not 100% certain, though it may just be a local DB, because that's the string I used in Java
I guess that works because the first part, jdbc:mydriver, happens to match your driver. Like you said earlier, jdbc:mydrivrerrrrrrrr does not work. I'm guessing though.
I agree the same jdbc url that you use in java should also work in CF. The odd thing to me is the missing database server in your jdbc url. I have always needed to supply that in ours (after the driver name and before any additional parameters). For what it's worth, you should be able to define the datasource using your java server (jrun if you are using that) and then reference that in CF. I forget the exact scenario for that setup but we did it before as a proof of concept. If it works in java it will work in CF I promise.
Also, you used to be able to define the connection within the cfquery tag itself but I'm not sure that is allowed anymore. (Can't look it up at the moment.)
Thanks for that, but interestingly enough, it behaves similarly to CFAdmin.
Login failed: You must provide a user and password to login.
It is ignoring anything after it ends, but I can spell it incorrectly and have it not work.