Connect to DBF files lazarus / 16-Databases ---------------------------------------------------------------------- Guy · 2014-02-23 21:09 Hello I have at work a CRM software written in Delphi I guess.it uses DBF files. I need to get data from it. I was able to do that in C sharp and dot net until it stopped working.i guess the driver not good. I was thinking of using Lazarus because it is like Delphi. But there r no examples of how to work with databases. I tried TDBf but there r encoding problems plus u can't write SQL queries there. So my question is : where can I find a fully working example of how to connect to DBF files ? I think odbc connection is better. I'm searching for days and nothing Thanks ---------------------------------------------------------------------- JanRoza · 2014-02-23 23:38 You already have the tools for that, look in your component folder. There is a folder tdbf which contains the dbflaz component. Install that package in Lazarus (if not already there in the Data Access tab) and you're set to go. A tutorial for dbf can be found at http://wiki.freepascal.org/Lazarus_Tdbf_Tutorial ---------------------------------------------------------------------- JanRoza · 2014-02-24 07:16 Sorry, I just see you already tried tdbf. ---------------------------------------------------------------------- mangakissa · 2014-02-24 10:34 Are you really sure it's DBF, because you're talking about DBS. Can you open the file in a spreadsheet program? ---------------------------------------------------------------------- Guy · 2014-02-24 11:00 Hi 1. I've corrected the title to DBF. 2. It's sure is a DBF file. according to one program that i use to watch the file, it's a dBase 3 file type 3. I've already wrote a c sharp program that open the files and retrieve data from it using SQL queries. but,allas, the c sharp program is no longer working. sometimes it works and sometimes no. the error has something to do with the driver. 4. what i'm trying to achieve is this : since the CRM software is written in Delphi' and since Lazarus is Delphi compatible, i wanted to use lazarus to write a program which will query the dbf files. the files contain hebrew chars, so TDBF is not good for me. Is there a way to connect to dbf files using odbc drivers ? but i need a full example since i undestand little in free pascal thank you guy ---------------------------------------------------------------------- howardpc · 2014-02-24 12:22 The attached simple project shows that TDBF can cope with Hebrew characters (utf8 encoded) perfectly well. ---------------------------------------------------------------------- Guy · 2014-02-24 13:13 Hi thank you for your kind reply unfortunately, it may be that the file are not in UTF. I can view the file in another program that i've downloaded from the internet,called Sdbf, but only when i chose view as dos in the menu of the program. even if i could see the hebrew, i still have to run a sql script that gather data from three tables. i send a zip file showing the question marks i get, plus the file itself. don't you guys use odbc ? how do u combine a search from multiple tables ? ---------------------------------------------------------------------- marcov · 2014-02-24 14:54 Iterating over one table with first/next and then doing locate on relevant keys on the other tables. SQL layers layered over DBF were usually quite slow compared to the native access. ---------------------------------------------------------------------- Guy · 2014-02-24 15:34 great...how about an example ? i mean full working example. anyone ? ---------------------------------------------------------------------- howardpc · 2014-02-24 16:01 See if you can open the attached dbf (unzip it) and view your clisohen data using TDBF. It seems to work OK here, except I can't read Hebrew, so I can't tell for sure. ---------------------------------------------------------------------- Guy · 2014-02-25 10:46 Hi thank you...it's hebrew what did you do to the file ? guy ---------------------------------------------------------------------- BigChimp · 2014-02-25 11:05 Put in UTF8 hebrew characters, I suppose!?!? Would be interesting indeed to hear what he did... The fpc trunk dbftool sample program reported QuoteDatabase tablelevel: 4 Database codepage:   1252 for Howardpc's other file... (which is obviously not UTF8 but AFAIU, there is no built in support for UTF8 in any dbf file TDBF can read/write, neither DBase nor (Visual) FoxPro) ---------------------------------------------------------------------- howardpc · 2014-02-25 11:38 Quote from: Guy on February 25, 2014, 11:46:18 amHi thank you...it's hebrew what did you do to the file ? I opened your file in LibreOffice under various encodings until I hit on one that showed recognisable Hebrew characters (although I don't read Hebrew, the characters are quite distinctive, and obviously different from ? or random rubbish, and I was successful on the third encoding I tried). I then created a new .dbf in Lazarus using TDBF with appropriate fieldDefs as per your original, and copied and pasted the now readable data into rows in the new table which I saved and posted. Bigchimp says the encoding is actually codepage 1252. I think I was wrong in suggesting my previous file was utf8. Probably it too is codepage 1252, though I have not checked this. To deal with DOS-encoded dbfs correctly TDBF provides an OnTranslate event(). If I had known the correct endcodings to apply this would have been the better way to deal with 'foreign' dbfs. Lazarus has a ConvertEncoding() function in the lconvencoding unit which you can use in the OnTranslate event of any TDBF to convert strings to and from almost any encoding. It is a little tricky because you have to work with the source and destination strings as PChars, which means taking account of an extra null byte at the end of the strings (which Length() does not give you, so you have to add 1 to the apparent Length()). You also have to set the Transliterate property of every string field that requires conversion to True in order for OnTranslate to get called at the proper time. (Edit) Re-reading Bigchimp's post I see he determined the encoding of my little test file (not the converted file of yours I posted). Anyway, in spite of my misunderstanding above, the same logic applies to any dbf, whatever the encoding of its character data might be. ---------------------------------------------------------------------- tatamata · 2014-02-25 11:42 Quote from: marcov on February 24, 2014, 03:54:55 pmIterating over one table with first/next and then doing locate on relevant keys on the other tables. SQL layers layered over DBF were usually quite slow compared to the native access. once I will have enogh time, I will make Zmsql-like package that is based on tdbf instead of tbufdataset. referential integriry can be enforced to tdbf dataset the same way it is done in zmsql on zmquerydatasets. ---------------------------------------------------------------------- BigChimp · 2014-02-25 11:55 Quote from: tatamata on February 25, 2014, 12:42:52 pmonce I will have enogh time, I will make Zmsql-like package that is based on tdbf instead of tbufdataset. referential integriry can be enforced to tdbf dataset the same way it is done in zmsql on zmquerydatasets. @tatamata: great, but.... - I'll happily accept patches/updates for tdbf that implement referential integrity ---------------------------------------------------------------------- BigChimp · 2014-02-25 11:59 Quote from: howardpc on February 25, 2014, 12:38:08 pmI opened your file in LibreOffice under various encodings until I hit on one that showed recognisable Hebrew characters (although I don't read Hebrew, the characters are quite distinctive, and obviously different from ? or random rubbish, and I was successful on the third encoding I tried). Mmm, looks like the Hebrew UTF8 characters happen to not interfere with whatever encoding codepage 1252 does... Thinking out loud, been a while since I dealt with Unicode stuff: what'll happen if you put in UTF8 characters that match the non-ASCII part of CP 1252 below 255? Or does UTF8 leave those characters undefined? ---------------------------------------------------------------------- Guy · 2014-02-25 14:43 Hi Thank you for your kind efforts I thought it was going to be easy, but it seems that i have to learn a lot. first i have to learn free pascal and lazarus, and databases... Maybe i will give it up and try c sharp anyway  thanks a lot anyway guy