ODBC problem

[bdee2]

i am trying to connect to the database for our company's Human Resources software (they want some custom reporting not supported in the app). i am pretty sure the database is foxpro. the data folder contains a bunch of dbf and cdx files.
if i create a system DSN using ODBC on my office computer, then i can connect to it using Crystal Reports and run reports with no problem. however when i create the same system DSN on the coldfusion server and then add the DSN in CF administrator as an ODBC socket, i run into problems. the DSN adds and verifies in CF administrator, but when i try and run a query against it it returns the followign error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Visual FoxPro Driver]File 'syemploy.dbf' does not exist.

however i KNOW that syemploy.dbf DOES exist.

can someone please help me out here?

[Guest]

Try to follow this technote:

http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=2d2a7a19

It describes an Access ODBC, so in your case just do some substitution. Instead of an Access ODBC, type your FoxPro ODBC as the DSNname.

HTH.

[bdee2]

hey thanks for the suggestion but i am basically getting the same error:
[Microsoft][ODBC Visual FoxPro Driver]File 'syemploy.dbf' does not exist.

[bdee2]

i still can't seem to get this to work, I actually have several foxpro databases that I am trying to connect to and having the same problem. and now i also have a Relativity database that wont even verify in CF administrator.

ANY ideas woudl be appreciated.

[dbunting]

Use a real jdbc driver instead of a JDBC/ODBC bridge -- http://www.hxtt.com/dbf.html
I've used this one successfully on CFMX 6.1 and 7. I'ts not free, but how much is your time worth?

[bdee2]

ok i will take a look - do they have a free trial so that i can be sure it will work for me before i spend the money?

[bdee2]

ok so i found the trial (duh - right at the top of the page you sent). i put the DBF_JDBC30.jar file in C:\CFusionMX7\wwwroot\WEB-INF\classes\

then i went into CF admin and into JAVA and JVM, and i already had some paths in the "Coldfusion Class Path field" so i added the following:
,C:/CFusionMX/wwwroot/WEB-INF/classes/DBF_JDBC30.jar

then i restarted the CF service.

then i went into data sources in CF Admin, and added a new datasource with driver type of Other.
for JDBC URL i am not 100% clear on how to format this but i put the following:
jdbc:dbf://servername\Best Software\Abra Suite\Programs\Data

the path to the data i am trying to his is \\servername\Best Software\Abra Suite\Programs\Data

and it did not verify - i get the following error:
Connection verification failed for data source: Abra
java.sql.SQLException: No suitable driver available for Abra, please check the driver setting in resources file, error: null
The root cause was that: java.sql.SQLException: No suitable driver available for Abra, please check the driver setting in resources file, error: null

i assume its because of a problem in my JDBC URL.

can you help me out here?

[bdee2]

anyone??????

[bdee2]

anyone??? what am i doing wrong here?

[Guest]

First, place DBF_JDBC30.jar in \cfusionmx7\lib and restart CFMX. The jar file will be picked up automatically in this location. I leave WEB-INF and its subdirectories for coldfusion's own use and do not think it wise to place your jar files there. Testing with DBF_JDBC30.jar in WEB-INF/classes, I do not find it shows up in my classpath even if I put it into the classpath inside jvm.config. This is verified when I look at the systeminfo page and settings summary report in CFAdmin. So, place the jar in cfusionmx7/lib and it is picked up automatically. Or put it somewhere else (outside of WEB-INF) and specify it in the java.classpath.

Next from the driver's documentation the class is:
com.hxtt.sql.dbf.DBFDriver
The URL takes the form:
jdbc:dbf:///c:/data

So, I have a foxpro sample database in c:\foxpro\Samples\Northwind. So in CFAdmin I created a DSN as:
name: foxTest
type: other
URL: jdbc:dbf:///C:/foxpro/Samples/Northwind ( Note, I am using java style forward slashes not backslashes per the docs)
driver class: com.hxtt.sql.dbf.DBFDriver

This datasource points to the directory containing the DBF files. It verifies. I can view the data in the tables.

HTH.

[bdee2]

thanks for getting back to me and sorry for taking so long to get back to this - my boss had me sidetracked with another project.

anyway - i am just getting back to this and i moved the jar file into the lib folder and restarted CF service. now i am workign on adding my datasource. but my question is this: my database doe snot live on a local drive - it is on another server that is accessible via a UNC path (\\dataServer\Best Software\Abra Suite\Programs\Data) how woudl i get that into the correct format for the JDBC URL?

i tried jdbc:dbf:///\\dataServer\Best Software\Abra Suite\Programs\Data and jdbc:dbf://///svh1/Best Software/Abra Suite/Programs/Data

when i try to verify either of those it says \\dataServer\Best Software\Abra Suite\Programs\Data does not exist.

I even tried mapping a drive (Y) to the unc path and then for the JDBC URL i put jdbc:dbf:///Y:/ but when i verify it says
onnection verification failed for data source: Abra
java.sql.SQLException: Y:/ doesn't exist!
The root cause was that: java.sql.SQLException: Y:/ doesn't exist!

[Guest]

jdbc:dbf://///svh1/Best Software/Abra Suite/Programs/Data looks like the correct URL. If cfmx is running as installed the service is running under a localsystem account. Localsystem will not be able to use shared drives, unc paths, etc. It has privileges to the local box only. This is a known issue.

The solution is to run coldfusion as a specific user that you create. See the technote, " Running ColdFusion as a specific user".

You can initially confirm this is the issue by stopping the CFMX application server service. Then start it from the commandline using the cfstart.bat file in cfusionmx7\bin. CF will be running under your login. You should find the URL with the UNC path works.

[bdee2]

yea when i look at the service , the log on as field says "Local System". so if i were to create a user called ColdFusionUser and add him to the administrators group on our domain, then i just change the service to run as that user and i should be good to go?

[bdee2]

ok so i finally got around to trying this. I created a local user on sunweb and gave him permissions to all the correct folders according to the link you supplied and still when i try and verify the datasource i get the following:
Connection verification failed for data source: Abra
java.sql.SQLException: No suitable driver
The root cause was that: java.sql.SQLException: No suitable driver

does the user need to be an AD user in our networks active directory instead of a local user?

[bdee2]

i also tried using cfstart.bat to start the service and still same error - no suitable driver.


why cant i get this to work???

[Guest]

It looks like you are back to your original error. That means you have either created your datasource incorrectly or the jar is no longer in the classpath.

[bdee2]

i cant figure out why that would be - i double checked and DBF_JDBC30.jar is still in \cfusionmx7\lib. as for the datasource - this is what i entered:

CF Data Source Name: Abra
JDBC URL: dbc:dbf://///svh1/Best Software/Abra Suite/Programs/Data
Driver Class: com.hxtt.sql.dbf.DBFDriver
Driver Name: DBF
User Name: empty
Password: empty
Description: empty

and when I submit it or verify it, it says:
Connection verification failed for data source: Abra
java.sql.SQLException: No suitable driver
The root cause was that: java.sql.SQLException: No suitable driver

does the trial version of this driver time out after a certain number of days? i have been working on this for a while so maybe it timed out? i don't see any mention of that on their website. any ideas?

[bdee2]

i just tried this on a secondary test server with CF installed. i downloaded the jar file again and put it in the cfusionmx7/lib folder and restarted the service. recreated the DSN in CF admin and got the same error on this server too. so i guess its not due to a trial version expiring.

[Guest]

First, you show the URL as:
JDBC URL: dbc:dbf://///svh1/Best Software/Abra Suite/Programs/Data
it needs to be jdbc:dbf://///svh1/Best Software/Abra Suite/Programs/Data, starting with a j. Perhaps that was just a typo into the forum though.

I would recommend creating a new DSN against a local foxpro database. Once you have that working, check the differences against your original DSN.

If you still cannot get things running it is time to open a support incident.

[usmc-ret]

ksmith

I have been trying to get this to work for a couple of hours. The suggestion of placing sqljdbc.jar in \coldfusion\lib did the trick. THANKS!