Sample Database Application for Programmers New to PureBasic

[RobertSF]

I put this together while learning PureBasic and figured it might be useful to others also learning PureBasic or evaluating PureBasic. The project is under 600 lines, so it runs fine in the evaluation version of PureBasic. The application itself is trivial, but the code demonstrates how to navigate through a database and add, delete, and modify records. It includes an Access database that is accessed through ODBC.


Download: http://www.clicketyhome.com/purebasicdatabaseapp.zip

I appreciate any and all comments and suggestions for improvements.

[idle]

looks like a well thought out example thanks.

[Joris]

That migth become of use. Thanks.

[Kwai chang caine]

Hello RobertSF

Thanks for your sharing but i have an error on W10 X86

[Microsoft driver ODBC]Data source not found and name of driver no specified

[Marc56us]

Good work, and thanks for the documentation.

A Sample Database Application for Programmers New to PureBasic.pdf

Getting Started:
The zip file contains a blank database called contacts.mdb. Drag this file to the location of your choice,
and then use the ODBC control panel to create a System DSN or a User DSN called dsContacts that
points to the database.

I think it would be interesting to modify the example so that it is completely autonomous using for example SQLite rather than ODBC access on an .mdb database (access?)

Not all users know how to install a DSN or even the correct version of the ODBC driver (32-bit and 64-bit versions are not compatible)

[RobertSF]

Hello RobertSF

Thanks for your sharing but i have an error on W10 X86

[Microsoft driver ODBC]Data source not found and name of driver no specified

Hi, Kwai chang caine, thanks for checking it out.

I have W10 and also had lots of problems with ODBC. It sounds like you were able to create the DSN, but when you ran the program, you got an error. That is probably the 32/64-bit issue. In your Windows directory, there is a directory called SysWOW64. I thought that was for amazing features, but no, WOW64 means "Windows (32-bit) On Windows 64-bit." This directory has the 32-bit versions of some Windows utilities.

Open SysWOW64 and look for odbcad32.exe. Use this program to create the DSN. When you go through Control Panel, the 64-bit version is executed. To run the 32-bit version, you have to run it directly. I hope this fixes your problem.

Also, there's a typo in the documentation that I will fix as soon as I get home!
The DSN name should be "dbContacts" not "dsContacts."

[RobertSF]

Good work, and thanks for the documentation.

A Sample Database Application for Programmers New to PureBasic.pdf

Getting Started:
The zip file contains a blank database called contacts.mdb. Drag this file to the location of your choice,
and then use the ODBC control panel to create a System DSN or a User DSN called dsContacts that
points to the database.

I think it would be interesting to modify the example so that it is completely autonomous using for example SQLite rather than ODBC access on an .mdb database (access?)

Not all users know how to install a DSN or even the correct version of the ODBC driver (32-bit and 64-bit versions are not compatible)

Thank you, and yes, I went around and around about what database to use. Of course I know about SQLite, but I have never worked with it. I know Access is not designed as a multi-user database, but it does do row and table locking, so as long as the volume is not high and the application is not mission-critical, you can put an Access database on a shared drive and use it within departments and small offices. Can this be done with SQLite?

Fortunately, PureBasic makes switching very easy. It looks like all we have to do is change the UseODBCDatabase() statement to UseSQLiteDatabase(). All the rest of the code should work the same. Perhaps I should include this in the documentation?

[Kwai chang caine]

Thanks a lot for your full explanation RobertSF

[C87]

Hello RobertSF,
Methinks you are wrong regarding your note about MS Access.
It is a multi-user database, well insofar as 80+ users can be considered mutil-user. I successfully used it for many years as a shared database at hundreds of different sites, over networks and remote desktops.
Whilst it has table locking it doesn't have record locking, as it locks blocks of data. I could never quite see the point in locking a table in a normalised database of tables. If you attempt a lock on a record, depending upon the record size it may lock 2 or ten or whatever, of what you see as individual records. It doesn't store data as specific records as you may see in see an old DBF table, so one record cannot be locked. If you are using an Access table in a shared system then do not lock anything. Turn all locks off. It is unlikely that the same person will be editing the same record at the same micro second and it cannot be a problem if new records are added. If it is likely then create a good errorCheck Function to help. But if you have an edit on, Access itself will prevent another user from saving an edit or changing the same data anyway. I never had a problem in Access with not using locks.
The biggest issue with ACcess was a flaky network that can corrupt the table or the database but from XP onwards that tended to be cured. If someone is backing up the database whilst table(s) are open by another user, then major problems. Any tables open are not backed up, resulting in an incomplete backup! ALso illegal shutdowns can corrupt a very large database and you then see what could be called record slip and unreadable records. YOu always need an auto-repair to filter and remove any of these if the database is huge. ( tried to get clients to have databases less than 1GB but some went to over 2GB, which I refused to support!) If handled correctly Access is fine, just takes more than a little knowledge. Often the main issue with Access was speed and to get around this you must open a form and link it to an unused table at the start and these are to remain open all the time. Usually with the form hidden. There are several settings to make to a database tables as well but this forum isn't about Access so I'll stop here!

Regards, Will

[RobertSF]

Hi, C87. Thank you for the info on Access, and I agree that it can be used as a multi-user database, even though some purists insist that only server-based SQL is really multi-user.

You seem to have a lot of experience with database development. If you have any opinions about the code I posted for learning purposes, I would love to hear them.

[Paul]

If you have any opinions about the code I posted for learning purposes, I would love to hear them.

You may want to do more error checking within your program.
1. If you start an empty database you are presented with the "enter new contact window". If you press any of the arrow buttons you will crash the program because there are no existing records yet.
2. If you press the "-" button to delete a record and answer yes, you will receive an empty error box and the program quits.
3. If you add a record and then exit to the main window, then double click to edit the record... press the '-' to delete the record and answer yes, instead of deleting the record and going back to the main screen it stays on the edit window allowing you to press '-' again and if you answer yes again, it will give an empty error box and program quits.
4. Do #3 again but instead of pressing '-' a second time press any of the arrows, program will crash (because no more records exist)
5. When you double click to edit a record, sometimes you can update a record by pressing the check button and other times it creates a new record instead.
6. There are also issues with adding and deleting records and the wrong rows are deleted or changed in the main list, forcing you to press the programs 'refresh' button to clear and repopulate the list to view the correct information

[C87]

Hello RobertSF,
Your application is excellent and will be very helpful to anyone grappling with PureBasic. I'm sure it will also be helpful to many others as your own design method gives another aspect on analysis and design that they may not have used. I think that many will be able adapt and expand your software into their own applications. It really is a good starting point. I do think that different developers use a language in their own way and in a way that suits them. For instance there will be some built-in functions that some developers may never use, whilst others use them regularly and visa versa. Which is one reason to have this Forum for discussion. I have changed your example to use SQLite and it works just fine in 64Bit. Having said all that, your naming system for Forms, Tables etc will not be helpful as time goes on but even that gives the new starter room for thought on naming. Further to that, I have actually written out my recommendation for a general naming system that I used until I retired and hopefully, will be uploading it in the next week.

Quickly going back to Access, when you create a table and after adding the fields, open the Design Screen and rightclick on the area to the right of the fields list. Choose Properties and if <SubDataSheet Name> is [Auto] change it to [None]. That change will improve speed on a largish database but is better set on development at the start. Later versions of Access do tend to default this to [None] but it is always worth checking.

Regards, C87

[RobertSF]

If you have any opinions about the code I posted for learning purposes, I would love to hear them.

You may want to do more error checking within your program.
1. If you start an empty database you are presented with the "enter new contact window". If you press any of the arrow buttons you will crash the program because

Wow, thank you so much. That's more than I expected. I'll start addressing those bugs right away.

[RobertSF]

I have changed your example to use SQLite and it works just fine in 64Bit. Having said all that, your naming system for Forms, Tables etc will not be helpful as time goes on but even that gives the new starter room for thought on naming. Further to that, I have actually written out my recommendation for a general naming system that I used until I retired and hopefully, will be uploading it in the next week.

I'm looking forward to reading your naming system. I don't have strong opinions, like camel case over Pascal case, but it's hard to come up with a good consistent naming convention.

Thanks for saying the app was a good starting point. I've noticed there seem to be fewer starting points than there used to. In the days of PC magazines, readers would submit for publication programs they had written in their 64k machines. You don't have that much today, and the three-line code snippets you see in documentation may explain a function but don't give you a big-picture idea. Like you say, we read other people's code and get ideas about how to develop our own code.

[RobertSF]

Thanks especially to Paul for pointing out some things that needed to be worked on. I have addressed those things, added a couple of things, and updated the documentation.

The application now uses modeless forms and includes form management routines to illustrate the difference between modal and modeless forms.

In the future, I'd like to add modules and a formal form management library.

As before, download the package from http://www.clicketyhome.com/purebasicdatabaseapp.zip
I appreciate any comments or suggestions you have.