Porting from Borland Delphi BDE TQuery, TTable, TDatabase, TSession, etc lazarus / 16-Databases ---------------------------------------------------------------------- PStechPaul · 2017-05-07 05:17 I'm trying to port the database units of my Ortmaster project (created with Delphi 4 Pro) to Lazarus, and it needs equivalents for TQuery (which I changed to TSQLQuery) and TTable (for which I found no equivalent, and possibly others. I am using dBase format (III, III+, IV?). I can change the references in the PAS and DFM files so they will appear in the form designer, and then I can change or add Lazarus database components. I'm not especially clear on some database concepts, but I'm not doing anything very exotic. Mostly adding and editing records and extracting data for analysis and reports. Thanks! ---------------------------------------------------------------------- Thaddy · 2017-05-07 07:13 TDbf is a TTable component for dBase files. ---------------------------------------------------------------------- Zath · 2017-05-07 11:07 Did the TSQLQuery work instead of TQuery ? What else did you need to change for this to work ? I have the same problem moving an old legacy D7/D2007 project into a newer version. ---------------------------------------------------------------------- avra · 2017-05-07 11:43 In order to have a reliable Paradox or DBF application, you need to flush after each write, and if you use indexes then to avoid corruption you need to detect if your application has ended properly, and if not (there was reset or power down) then you need to reindex on application initialization. All these can be avoided if you move to transactional database, for example SQLite or Firebird embedded, but that needs significant changes in code and the way of thinking. ---------------------------------------------------------------------- Thaddy · 2017-05-07 12:15 @Avra: TOdbcConnection makes the lot transactable (requires an odbc driver for dBase but these are common.) ---------------------------------------------------------------------- Thaddy · 2017-05-07 12:18 Quote from: Zath on May 07, 2017, 01:07:37 pmDid the TSQLQuery work instead of TQuery ? What else did you need to change for this to work ? I have the same problem moving an old legacy D7/D2007 project into a newer version. No. TSqlQuery is NOT going to work. These older dBase's have no concept of SQL. TDbf on the other hand can work as either/or/and ttable and tquery. The other option is to use ODBC with a dBase driver. ---------------------------------------------------------------------- korba812 · 2017-05-07 12:25 Advantage Database can work with dbf and have query component http://wiki.freepascal.org/Advantage_Database_Server ---------------------------------------------------------------------- avra · 2017-05-07 13:06 Quote from: Thaddy on May 07, 2017, 02:15:50 pmTOdbcConnection makes the lot transactable (requires an odbc driver for dBase but these are common.) Good point. ODBC was not on my mind when I wrote that. ---------------------------------------------------------------------- PatBayford · 2017-05-07 20:38 Sorry Thaddy, but your comment on SQL is not strictly accurate - older databases, like dBase and Paradox certainly do "talk" art least a limited subset of SQL. In paradox you can even get to see the SQL used to communicate with the database engine, and even write your own. However, they DO hide this from the user, sometimes too well! I have no direct experience with TsqlQuery, but suspect it will work provided you stick to using basic, unenhanced SQL. On the ODBC front can I urge you all strongly to avoid the Microsoft offerings for dBase and Paradox, as personal experience suggests these are fraught with problems. (no doubt the long running feud between MS and Borland is in no way responsible for this!!!) ---------------------------------------------------------------------- PStechPaul · 2017-05-07 20:42 I found this, which will probably be helpful: http://wiki.freepascal.org/Lazarus_Tdbf_Tutorial#Creating_the_table I was able to get three of the forms to show, for editing. There is also a form for reports, using Quick Reports. I am installing LzReports  - which hopefully will be compatible. Attached are the three forms I will be working with: ---------------------------------------------------------------------- Thaddy · 2017-05-08 04:41 Nope not compatible. The reports need a rewrite. But the principles are the same. Many of your issues are very doable, but you seem to have lack of experience in porting based on some of your choices. I'll see if I can make some time. ---------------------------------------------------------------------- mangakissa · 2017-05-08 07:14 If you're very font of your data, use TDBF. Otherwise turn your application on with TBufdataset / tMemdataset and use XML ---------------------------------------------------------------------- Zath · 2017-05-08 09:22 Quote from: Thaddy on May 07, 2017, 02:18:33 pmQuote from: Zath on May 07, 2017, 01:07:37 pmDid the TSQLQuery work instead of TQuery ? What else did you need to change for this to work ? I have the same problem moving an old legacy D7/D2007 project into a newer version. No. TSqlQuery is NOT going to work. These older dBase's have no concept of SQL. TDbf on the other hand can work as either/or/and ttable and tquery. The other option is to use ODBC with a dBase driver. Thank you. ---------------------------------------------------------------------- PStechPaul · 2017-05-08 17:01 I will probably need to keep the dBase III+ format, because there are a lot of these systems installed in the field, and changing the files to another format may be problematic. The dBase format works well enough and is simple. I'm not even using indexes. At this point I'm going to go through the Lazarus Tdbf tutorial (http://wiki.freepascal.org/Lazarus_Tdbf_Tutorial) and create a simple application that uses those components with one (or all) of my dBase files. Then I may try to learn LazReport (http://wiki.freepascal.org/LazReport_Documentation). Thanks for the suggestions. If anyone is interested in putting some time into this project for porting it from Delphi to Lazarus, I'd be willing to pay a reasonable amount. This is an industrial product that needs a fair degree of reliability, as well as a simple and attractive GUI. I have transferred the responsibility for the product to an associate who will be marketing, building, and servicing the product. It does not necessarily have to be ported to Lazarus. My immediate problem is getting the serial port communication working with Win10, and it seems like the TLazSerial component will do that. I think it should work with D4. But I am impressed with Lazarus and it seems to be actively supported, whereas Delphi has moved far past D4 and even D7, and future maintenance will probably be easier (and much cheaper) than the newer versions of Delphi. But that's another discussion... ---------------------------------------------------------------------- mangakissa · 2017-05-08 17:31 QuoteThis is an industrial product that needs a fair degree of reliabilityOkay...... ---------------------------------------------------------------------- jc99 · 2017-05-08 18:08 By the way, I had the same problem, having to port something like a TTable to lazarus, so I made my own TSQLTable- Component that is based on the TSQLQuery have a look at my github-repository under Components\Source\SQLTable ---------------------------------------------------------------------- Thaddy · 2017-05-08 18:44 His problem is that the tables are dbf.... Anything SQL won't work. Except odbc to some extend. dBase has its own query language. ---------------------------------------------------------------------- jc99 · 2017-05-08 19:03 So you can bind The TSqlTable to a TODBCConnection [edit]... i think i learned SQL with dbase in school back in the 80's ---------------------------------------------------------------------- Thaddy · 2017-05-08 19:17 Then you are mistaken. In the 80's there was no sql with dBase. ---------------------------------------------------------------------- PStechPaul · 2017-05-08 19:41 dBase III+ might not have had the ability to use SQL for queries, but my application uses the BDE and SQL works quite well, at least for simple expressions. For instance: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQL.Strings = (      'SELECT TECHID FROM ORTTECHS ORDER BY TECHID'    ) I also have a memo where the SQL is displayed, and can be edited by the user. It is partially automated, where the user can select a field from a list to be added to the SQL statement, as shown in the following screenshot from my original application, and a sample report: ---------------------------------------------------------------------- PStechPaul · 2017-05-08 21:21 More information on Borland Delphi, dBase, BDE, and SQL: http://edn.embarcadero.com/article/28688 http://www.dataweb.de/en/products/delphi_database.html http://torry.net/pages.php?id=564 http://www.delphigroups.info/2/32/378979.html https://www.thoughtco.com/sql-in-delphi-4092571 http://edn.embarcadero.com/article/25639 http://www.delphipages.com/forum/showthread.php?t=128379 ---------------------------------------------------------------------- Thaddy · 2017-05-09 04:45 Yes, I know all that... At the time I was rather involved with Borland because I did some research for them. I also worked on a product called PerfectView that is basically a dBase format based database design software that did not use the BDE. But you want to drop the use of the BDE since it is deprecated and in Lazarus there's no direct interface for it. This can all be mitigated, but I understand you want to keep the dBase format and that will take more work than you think. We can discuss this directly. ---------------------------------------------------------------------- PStechPaul · 2017-05-09 05:43 As long as the old dBase files can be converted to another format, there is no reason to keep the dBase files for the new application. It could be done as part of the install process. I found several utilities that can do this. http://www.dbf2002.com/dbf-converter/convert-dbf-to-sql.html http://download.cnet.com/DBF-to-MySQL/3000-2065_4-10534440.html https://dbf-to-sql-converter.en.softonic.com/ ---------------------------------------------------------------------- jc99 · 2017-05-09 07:48 (off topic:) @Thaddy: See Wikipedia: https://en.wikipedia.org/wiki/DBase#dBase_.2F_xBase_programming_langua ge Quote... Over time, Ashton-Tate's competitors introduced so-called clone products and compilers that had more robust programming features such as user-defined functions (UDFs) to supplement the built-in function set, scoped variables for writing routines and functions that were less likely to be affected by external processes, arrays for complex data handling, packaging features for delivering applications as executable files without external runtime interpreters, object- oriented syntax, and interfaces for accessing data in remote database management systems. Ashton-Tate also implemented many of these features with varying degrees of success. Ashton-Tate and its competitors also began to incorporate SQL, the ANSI/ISO standard language for creating, modifying, and retrieving data stored in relational database management systems. ... (on Topic:) Any comments on TSQLTable helping to convert Delphi programs to Lazarus ? ---------------------------------------------------------------------- Thaddy · 2017-05-09 07:57 Quote from: PStechPaul on May 09, 2017, 07:43:36 amAs long as the old dBase files can be converted to another format, there is no reason to keep the dBase files for the new application. It could be done as part of the install process. I found several utilities that can do this. http://www.dbf2002.com/dbf-converter/convert-dbf-to-sql.html http://download.cnet.com/DBF-to-MySQL/3000-2065_4-10534440.html https://dbf-to-sql-converter.en.softonic.com/ This can be done straight from lazarus with default components, btw. There's a lot of noise here. I am probably the only one in this discussion that actually worked for Borland. On dBase format  related things. So the links aren't very helpful if you don't know it can be done already within the Lazarus provided infrastructure. ---------------------------------------------------------------------- avra · 2017-05-09 11:17 Quote from: Thaddy on May 08, 2017, 09:17:14 pmThen you are mistaken. In the 80's there was no sql with dBase.He was close. In 1991 Fox Pro 2.0 for DOS had SQL queries over DBF tables. ---------------------------------------------------------------------- Thaddy · 2017-05-09 15:14 Quote from: avra on May 09, 2017, 01:17:58 pmQuote from: Thaddy on May 08, 2017, 09:17:14 pmThen you are mistaken. In the 80's there was no sql with dBase.He was close. In 1991 Fox Pro 2.0 for DOS had SQL queries over DBF tables. Correct. But the tables and indexes aren't compatible. Fox Pro and dBase were like fire and water if you mixed them. ---------------------------------------------------------------------- mangakissa · 2017-05-09 15:35 I've tried to connect DBF with ODBC and started a query on it. I was not not very font of it ---------------------------------------------------------------------- jc99 · 2017-05-09 16:33 (still off topic) It was late '89 maybe begin of '90 and it was a very early release of dBase. We didn't call it SQL those days, we just learned dBase. But when i later had a look at SQL i realized the similarities. I still know after the query you had to call the browse command to get a data table, btw. it was DOS, so no graphic ..., but EGA so 80x43