Database

DOS specific questions.
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Database

Post by Gablea »

Hi All,

I would like some advise please on what would be the best database to use in DOS

As you all know I am working on a DOS Version of my Point of sale software and I am at the moment using a CSV file to hold my data and I am using the Type Function to load that into

But I am sure there Must be better ways to create and use a Database in DOS (for example ISAM Database)

If so what would you all Recommend I switch to

My Linux version is using MySQL Connection (but I am still having Random Crashes with my App) I know in a perfect world Someone would have ported the MySQL functions to DOS but I know that is a LOT of work for little to no reward.

Thank you in advise for any Advise
caseih
Posts: 2157
Joined: Feb 26, 2007 5:32

Re: Database

Post by caseih »

What's wrong with the CSV solution?

Because DOS is a single-task operating system, there's no way to run a database server like MySQL that accepts connections via sockets.

Whatever you find, it will have to be integrated into your EXE as a library. Back in the day there were various commercial database engines you could integrate into your application (dBase, Paradox, etc), but I doubt any of them are available still, and doubt they would be compatible with FreeBASIC.

SQLite is unsupported on MS-DOS, and MS-DOS lacks certain facilities that SQLite relies on for data integrity in the event of a crash.

I cannot think of any acceptable solutions for you, short of building your own database engine (which you've essentially done).
RockTheSchock
Posts: 252
Joined: Mar 12, 2006 16:25

Re: Database

Post by RockTheSchock »

Maybe you could provide a sample CSV file with at least 20 data rows and the header row?
caseih
Posts: 2157
Joined: Feb 26, 2007 5:32

Re: Database

Post by caseih »

At this point we're revisiting territory that you've already tried before with limited success. About the only solution I can think of is to use a TCP/IP stack in your POS program, and connect to an external database server, running on Windows or Linux. That keeps the simplicity and single-tasked nature of your DOS-based POS design, keeps existing hardware and operating system, but allows a real database that can service multiple POS stations simultaneously. But the catch here is getting libmysql to work on MS-DOS---which we've explored without success in the past. Another possibility is to write some kind of HTTP-based API wrapper that runs on a web server on the SQL server that makes all the SQL calls on behalf of of the client. Then the client just uses TCP/IP to make http requests. But that also has significant drawbacks including the need to implement your own access controls and use SSL to encrypt the traffic. Getting an SSL library to run under DOS is certainly possibly but I know of no libraries that actively support it.

MS-DOS really limits what you can do, in large part because nearly no one still supports it as far as open source libraries go.
Coolman
Posts: 294
Joined: Nov 05, 2010 15:09

Re: Database

Post by Coolman »

under DOS, there was the excellent :

Microsoft BASIC Professional Development System 7.1
Integrated ISAM : The Indexed Sequential Access

it is an abandoned development tool. the license should be checked...
marcov
Posts: 3455
Joined: Jun 16, 2005 9:45
Location: Netherlands
Contact:

Re: Database

Post by marcov »

I wonder how doable even that (Caseih's) option is, to avoid that any network (database or own protocol) is blocking and stops servicing the POS ? No threads, no asynchronous I/O etc etc.

Sure, if I were really desperate, I'd try what Caseih says, create an app server on the database machine, and let the dos machine communicate with it. (that would at least make mysql versioning a bit less dangerous)
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Database

Post by Gablea »

RockTheSchock wrote: Jul 04, 2022 15:00 Maybe you could provide a sample CSV file with at least 20 data rows and the header row?
Sorry for the delay in posting this I had to re create the data file as I could not remeber the layout lol


so the format is as follows

Code: Select all

barcodenumber, posdescription, salelocation, agerestricted, agelimit, pricetype, retailprice, vatcode, print_guarantee_message, print_guarantee_code, displaymessage, messagenumber, sendtoppr, requestserial, itemnotallowed, itemnotallowed_reason, itemnotallowed_reason, restrict_product_qty, product_qty_allowed, discount_not_allowed, no_refund_allowed, ask_for_qty_before_selling, healthy_start_voucher_ok
So I would get a output into Products.upd

Code: Select all

011210115255|PEPPER SAUCE TABASCO 57ML|0008|0||S|2.20|A|N|-|N|-|N|N|N|-|N|-|N|N|Y|N
01501513|ROCK SALT|0056|0||S|7.50|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
018341751024|BAREFOOT SAUVIGNON BLANC 75CL|0047|1|9|S|6.99|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
018341751062|BAREFOOT WHITE ZINFANDEL 75CL|0047|1|9|S|6.99|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
018341751109|BAREFOOT MERLOT 75CL|0047|1|9|S|6.99|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
024000001966|DELMONTE PINAPPLE SLICES 435G|0004|0||S|1.29|Z|N|-|N|-|N|N|N|-|N|-|N|N|N|N
024000025993|DELMONTE PEACH SLICES 420G|0004|0||S|1.19|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
024000080947|DELMONTE MANDARIN SEGMENTS|0004|0||S|1.29|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
024000124023|DELMONTE FRUIT COCKTAIL|0004|0||S|1.19|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
024000124962|DELMONTE PEAR HALVES|0004|0||S|1.19|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
031200168963|OCEAN SPRAY CRANBERRY CLASSIC|0013|0||S|1.39|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
031200452009|OCEAN SPRAY CRANBERRY CLASSIC|0013|0||S|1.89|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
031200468956|OCEAN SPRAY CRANBERRY & MANGO|0013|0||S|1.39|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
037600785020|SPAM 340G|0009|0||S|2.79|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
043647010018|BLACKCURRENT CONSERVE 340G|0025|0||S|2.59|Z|N|-|N|-|N|N|N|-|N|-|N|N|N|N
043647030016|MORELLO CHERRY PERSERVE 340G|0025|0||S|2.59|Z|N|-|N|-|N|N|N|-|N|-|N|N|N|N
043647130013|'SWEET TIP' RASPBERRY 340G|0025|0||S|2.59|Z|N|-|N|-|N|N|N|-|N|-|N|N|N|N
043647440020|'OLD TIMES' ORANGE MARMALADE|0025|0||S|2.59|Z|N|-|N|-|N|N|N|-|N|-|N|N|N|N
048256219456|ASTONISH MULTI-PURPOSE CLEANER|0034|0||S|1.49|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
048256291063|ASTONISH ANTIBACTERIAL SURFACE|0034|0||S|1.49|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
048256299557|ASTONISH MOULD & MILDEW SPRAY|0034|0||S|1.49|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
051141347042|2 CLEAR PLASTIC HOOKS|0065|0||S|1.49|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
071106785030|CLASSIC CHILI CON CARNE MEDIUM|0009|0||S|2.29|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
0711766737832|SCOTCH EGG |0062|0||S|1.29|Z|N|-|N|-|N|N|N|-|N|-|N|N|N|N
0716053002037|PREPS READY MADE MEAL 380G|0062|0||S|2.99|Z|N|-|N|-|N|N|N|-|N|-|N|N|N|N
071662083281|8 CRAYOLA WASHABLE MARKERS|0063|0||S|3.69|A|N|-|N|-|N|N|N|-|N|-|N|N|N|N
The PoS would then Read in the file at Boot up once it has been downloaded from a shared network drive and create the local version of the Product Database


So for the DOS version would it be best to stick to the format I have (download delete exiting file and recreate it) or would it be worth the move to the ISAM database?
caseih
Posts: 2157
Joined: Feb 26, 2007 5:32

Re: Database

Post by caseih »

Well even if you used an ISAM engine, you still have a huge problem of keeping the database in sync between your different POS stations. The only good solution to that is a dedicated application server that your POS clients all talk to which does the database interactions for them.

As for CSV vs ISAM, if your whole database can fit in memory when your program loads, I don't see a huge advantage to ISAM. But if your database is large enough that you need to leave it on the disk and search a file for a record, then ISAM is clearly superior. Searching through a CSV file on disk is quite slow compared to ISAM which uses fixed records lengths and indexes.
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Database

Post by Gablea »

How would I load the database into memory?
caseih
Posts: 2157
Joined: Feb 26, 2007 5:32

Re: Database

Post by caseih »

Isn't that what you are doing now? Reading and parsing the CSV into memory? That's what I meant.
Gablea
Posts: 1104
Joined: Apr 06, 2010 0:05
Location: Northampton, United Kingdom
Contact:

Re: Database

Post by Gablea »

So o write it to a file on the hard drive defined by a

Type end type

Example

Type Products
Dim barcodeNumber as string * 13
End type
angros47
Posts: 2321
Joined: Jun 21, 2005 19:04

Re: Database

Post by angros47 »

If all you need is to write a record defined with Type, the easiest thing to do is to use PUT, and to read it using the GET command

https://www.freebasic.net/wiki/KeyPgRandom
coderJeff
Site Admin
Posts: 4313
Joined: Nov 04, 2005 14:23
Location: Ontario, Canada
Contact:

Re: Database

Post by coderJeff »

Gablea wrote: Jul 02, 2022 22:20 If so what would you all Recommend I switch to
If you have something that works and you can maintain it, why switch to something else? What problem are you trying to solve?

In the past I have written systems that work with 100K records at a time, in a custom format; and for finding information quickly: custom index files with custom tools to check the validity / integrity of the database. This was much more efficient than writing dBaseIV files (which was popular ^ available at the time) and working dBase automation. i.e. a custom implemented database met all the required needs of the information tracked.

The eventual move to a MSSQL database was the desire to use features of the database engine - like joined tables and relational constraints to query on information between the custom (QB generated data files) and MSSQL tables populated by a separate system. However, in this case, day to day running data was managed by the custom software, and then imported in to MSSQL for historical trending / reporting and analysis.
rabbit71
Posts: 1
Joined: Jul 22, 2022 14:57
Location: Hungarya
Contact:

Re: Database

Post by rabbit71 »

Hi! I did not tryed it out still year 2000, but dBase allways was the best. And it's still alive:
https://www.dbase.com

:P
exagonx
Posts: 314
Joined: Mar 20, 2009 17:03
Location: Italy
Contact:

Re: Database

Post by exagonx »

Hi

If you want to create a database you have to consider a few things:
You must create an index where to keep the list of tables, of which one of these tables must contain references as the number of tables current index last index created, DOS being single task you cannot manage a multi-user database so to avoid problems it is necessary create a lock where you go to check if the database is currently open, technically it is convenient to separate the tables by file and a file to manage index and information on the database, in this way you can manage a considerable amount of information, also through an index file exceeding the physical limit of the file allows you to add more files so that you can store a lot of information without limits (Partition permitting)

Using GET and PUT allows you to access the file in random mode but implies more code to protect the data entered in fact if the file is corrupted this will generate too many problems irreversibly, so I recommend using line by line with a format similar to CSV but with a character other than comma so that the same comma can be used in texts or to separate numeric values.

Some time ago I created a library that once included allowed me to create and manage data maybe you can do the same too so that once you have created your database system you can implement it on all your software using simple objects defined previously.
Post Reply