asked on

Coldfusion query a DBF file

I'm trying to query a .dbf file (what kind I dont know), using Coldfusion. I read that this would be the command for a dynamic connection:

<CFQUERY NAME="qContact"
DBTYPE="dynamic"
CONNECTSTRING="Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\file.dbf;">
SELECT *
</CFQUERY>

But that doesn't work, I get this error:

The tag does not have an attribute called CONNECTSTRING. The valid attribute(s) are name, datasource, dbtype, sql, username, password, maxrows, blockfactor, timeout, cachedafter, cachedwithin, debug.

I have set up ODBC on the server so I could use that or this dynamic way, whichever works. Any ideas?

cheekycj

are you using ColdFusion 5.0? This tag is supported in CF 5.0 and later.

CJ

jyokum

actually CONNECTSTRING is no longer supported in CFMX.

cheekycj

Even 5.0?

I thought it was a part of 5.0.

CJ

jyokum

you can use it in 5.0 but not MX

anandkp

OOPS - DSN Less connections [using connection string] are obsolete in CFMX :(

substand

is there a reason you cannot set up a datasource?

BuMp

ASKER

Hi, sorry I'm just responding, my email wasn't working. I have no reason to use DSNless connection. The problem is I couldnt find out how to do it the normal way. I think the problem may be the dbf file now that I have gotten a little further. I set up ODBC System DSN to connect via FoxPro driver to the folder where the dbf file is. Then my query is like this:

<cfquery name="qContact" datasource="DSN">
SELECT *
FROM dbf_file
</cfquery>

I think thats the way to do it, because dbf's dont have table names, so the FROM is the actual file name. I'm not sure of this however, because the query errors out saying:

[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Visual FoxPro Driver]Not a table

I've also tried ODBC connecting to Microsoft dbase file and get same problem. I am able to open the dbf file in a dbf viewer and everything looks fine.

substand

interesting... i can't think of anything but the file name either...

BuMp

ASKER

OK I tested this last way with a smaller dbf and it works fine. So the problem must be my dbf file, which again opens fine in a dbf editor. Its 30mb, with 600 fields and about 3,000 records. So maybe thats a problem, too big or some sort of timeout setting?

ASKER CERTIFIED SOLUTION

cheekycj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cheekycj

check out:
https://www.experts-exchange.com/questions/20123866/Visual-FoxPro-Cold-Fusion-and-ODBC.html

CJ

BuMp

ASKER

OK I have tried many other dbf files from the same program and they all connect and query fine in CF. So i'm assuming i have it all set up right, but that the main dbf I want is just too big. This program is our main accounting app so needs to stay in dbf. I tried importing into Access and SQL and both said it was too many fields. I can, however connect to it with Corel Paradox without that error. So is there a setting in CF regarding table size or timeout?

cheekycj

I don't think so. We have run queries where CF executes a query against a Table that had millions of rows and the DB itself was around 30GB

CJ

cheekycj

Though the driver could limit it as well :-/

CJ

BuMp

ASKER

I could post a blank dbf file of the one I'm using to see if you can get it to connect. If you can get it to work I will increase to 500 points. If thats ok let me know.

cheekycj

a blank dbf file? aren't dbf files binary.

CJ

BuMp

ASKER

I meant a file with not records in it. BUT I have solved the problem. Like you said, it was a driver issue. There's a few different dbf drivers and this one happened to be Microsoft dbase version III. I opened the dbf with a different app and it said exactly what it was. It opens fine now! So it was the ODBC all along. Thanks for your help!

cheekycj

Great! Good to hear you got it working. Thanx for the "A"

CJ