How can I open DBF files?

DARKGuy · Post by **DARKGuy** » Thu Aug 26, 2004 10:48 pm

Greetings!

Hehe how's everyone here? Heh, I got sorta lost I know, you know, some nice vacations and new PC going on my way. Anyways, I'm continuing to teach my cousing PureBASIC, but he needs to work with DBF files. I tried using the Database.pb example, but I can't get to open the DBF file we need to work on. I tried making a lot of DSN files for working with, using various versions of DBase that it had in the properties, but nothing worked. I got a lot frustrated when that SQL always said: "Bad Query!". We just need to open the DBF file in a way we can edit it in PB code, though I can't use the console. The problem is that no matter what I write, it always, ALWAYS returns a "Bad Query!" error. Is there any way we can open DBF files in PB code, without that SQL thing? I mean, don't know, some external DLLs of some sort, who knows...

I also tried to search in this forum, and foiund a TESTDB.ZIP that had a nice example with MDB files. I tried to open my DBF and convert it to MDB but didn't worked either. The result was ever 1, different than opening the TESTDB.MDB that the ZIP file had.

So well if some of you can help me I would be very glad

Sorry I've been away for some time, though I'll try to come more frequently

Bye bye, and thanks in advance

Max.Â² · Post by **Max.Â²** » Fri Aug 27, 2004 5:30 am

If my memory serves me correctly, then DBF is a common extension for DBase and Foxpro databases. So the first task would be to identify which database it is, then to install the appropriate ODBC driver in case you don't have it on yor system.

Num3 · Post by **Num3** » Fri Aug 27, 2004 11:03 am

If you have MS Access try to import that dbf database, that way you'll be able to know which ODBC driver it uses (it's displayed when you're importing).

Next thing would be to create a DNS link to that database using the correct ODBC driver.

DARKGuy · Post by **DARKGuy** » Sat Aug 28, 2004 3:32 am

I appreciate all your help, but what about the SQL commands....what are those....and how do I use them without a console....and edit the DBF file? let's say I got the DSN, then I use it, and it works but I don't know the SQL commands and I also searched in google about them and no one of them works. Now, what if doesn't work with the ODBC? is there any other way to do it?

Max.Â² · Post by **Max.Â²** » Sat Aug 28, 2004 9:59 am

Have a look at the Database topic in the PureBasic help file. The sample allows you to select a ODBC database and then to execute SQL commands, so you can test the syntax.

In the help file there are also 2 links to webpages dealing with SQL.

In short: SQL stands for Structured Query Language and is a standard across a lot of databases, making it possible to use different DBMS systems without much pain.

One of the most used queries is:

select * from table_you_want_to_query;

Select: return from database
*: choose all fields in the table

Another variant would be:

select surname,firstname from table_you_want_to_query where city like 'Amsterdam'

Now only two fields are returned, surname and firstname and only rows are returned who meet the condition that the city is Amsterdam.

Other keywords:
update
insert into

Num3 · Post by **Num3** » Sat Aug 28, 2004 10:07 am

Check out the tutorial page in www.PureArea.net !

There a nice ODBC in PureBasic tutorial there

Here's a quick link http://www.purearea.net/pb/download/tut ... C_odbc.zip

DARKGuy · Post by **DARKGuy** » Mon Aug 30, 2004 5:46 am

*sighs* Greetings again

Well I've been trying to make it work but nothing works. I have Visual Fox Pro and I'm trying to open a dbf file and I can't. I tried following all the steps in the tutorial but didn't worked, it always says a "Bad Query!" error. I wonder if someone could make me an example with a DBF file (clipper 5.0 format) for accessing with PureBasic. I would be really glad and it will help me a lot. I tried making a DBF file with Visual Fox Pro and didn't worked either, and about the ODBC, I tried...3 about DBF's I think and no one worked...if someone can help me I would be very glad.

Also, I did a ZIP file with the DBF's I'm working on to see if one of you can access it

-EDIT: Corrected the link, sorry

http://galeon.com/thor100/dbf.zip

Max.Â² · Post by **Max.Â²** » Mon Aug 30, 2004 8:27 am

The link doesn't work for me.

Edwin Knoppert · Post by **Edwin Knoppert** » Mon Aug 30, 2004 11:05 am

Several tools supporting DBF might have a different 'DBF-id', i believe fox pro does have a different one.
So not to open with Excel or other since they verify the DBF file this way..

If you write a custom reader you can test for these as well.
In most cases the DBF filestructure will be the same.
I ever encountered a DBF file having a shifted filestructure, iow custom made.

Paul · Post by **Paul** » Tue Aug 31, 2004 4:24 pm

Hi DARKGuy,

Using DBF files are quite diffferent than using Access (MDB) databases. When you set up an MDB database, you point to a single MDB file that contains all your tables. With DBF files, you point to a Directory that contains many DBF files, each one being a seperate table.

Keeping this in mind (and using your zip file containing 3 DBF files), place then all in a folder... example: place them all in C:\myDBF
Next open your ODBC Data Source Administrator panel (found in the control panel).
Select the System DSN tab and press the ADD button.
Select Microsoft FoxPro VDF Driver (*.dbf) and press Finish button
Next enter a Datasource name and description. Example: myDB (we'll enter this in both fields)
Select Free Table directory
Enter the path of your database (in our example it is C:\myDBF)
Press OK

Now that the driver is set up... write some PB code

Code: Select all

If InitDatabase()
  If OpenDatabase(0,"myDB","","")  ;the name we called our database connection
    qry.s="Select * from dbf4"  ;one of your tables is called dbf4 so we will use that name to view that table
    If DatabaseQuery(qry)
      While NextDatabaseRow()
        Debug GetDatabaseString(0) ;Lets just view the 1st column in the table
      Wend
      Else
      Debug DatabaseError()
    EndIf
  
    CloseDatabase(0)
    Else
    MessageRequester("Error","Could not open database",0)
  EndIf
EndIf

Hope this helps and makes sense

Max.Â² · Post by **Max.Â²** » Tue Aug 31, 2004 4:46 pm

The link still doesn't work.

Paul · Post by **Paul** » Tue Aug 31, 2004 5:18 pm

It's one of those crappy hosting sites... I had to copy/paste his link into address box.

DARKGuy · Post by **DARKGuy** » Wed Sep 01, 2004 4:45 am

Yeah I had to upload it in that webhosting...sorry

Anyways, Paul, thanks for all your help, it worked wonderfully!!

thanks to all of you too for all your help!!

I knew it was possible, thanks!!!!!!!!!!