One more question. The subject points hopefully into the direction:
In sqllite3 I created three tables and saved a view in the database. How can I bind the view to a db-control (DataSource, DataComboView or DataBrowser)? All these controls have a property "Table" and/or "Field", but unfortunately it is not editable. When I push the button with the three dots a window pops up with a selection of the three tables- but no views, neither any SQL-Statements are allowed.
The red circles in the attachments are DataComboView-controls. They are really stubborn when foreign keys comes into play. Any help or code snippets would be great.

(Click to enlarge)

Looks like the Table field should be a string that matches your db source name/key.

Have you looked all over the wiki?
/comp/gb.db.form/datacomboview/table - Gambas Documentation

/comp/gb.db.form/datacomboview - Gambas Documentation

You should click all the properties you need to know about.

Sorry I cant help more.

Cheers Bruce.
I attached the sqlite3 database including some sample records and the project "Lens-Register".

If someone is interested to install it- here you go:
1. Fist of all install the database into a directory of your choice. The DB-file name must be "Lenses.sqlite"
2. Install the attached Gambas project.

Before you start:
The connection "Connection1" is bound to the my home directory. No idea how to make it dynamic. Start the IDE, you need to change the directory according to #1 (see above).

The general form based search works just for DataControls but unfortunately not for controls DataComboView. In the FMain form you'll find two controls form (Manufacturer and Mount). When you push the button "Set Filter" you are not able to search for these attributes.

The code is far from being perfect. So any feedback would be appreciated.

01McAc said

…Before you start:
The connection "Connection1" is bound to the my home directory. No idea how to make it dynamic. Start the IDE, you need to change the directory according to #1 (see above)…

I'm struggling with this.


This indicates that the connection is not bound to your home directory, so I've just created a directory: /home/steve/docs
and put in it the file: Lenses.sqlite

However, I get a message when I try to run in IDE: Unable to locate database Lenses.sqlite in ~/docs

…OR have you declared your connection somewhere else? I think this is possible because even when I change $con.Host to: "/home/steve/docs
I get the same message.

Also note that your sql file appears on my system as a simple text file and my Sqlite DB viewer program wont open it for the same reason.

Has the sql db become corrupted during upload/download?

Steve,

thanks for taking the time. Indeed there are two connections to the DB: first one is the "Connection1". The path needs adjustment (see attachment) according to your location of the DB Lenses.sqlite.
Second connection is in Global.class. This is just another open connection by code to get some information about field types. I forgot to mention that the path in the code needs adjustments too:
The file (attachment "Lenses.sqlite.tar.gz" from earlier post) is a dump of the original database. Extract the file and create a new database with the command line
That's it. Sorry for the hassle but it's my first try with Gambas. The project might be a bit challenging.

(Click to enlarge)

01McAc said

…Sorry for the hassle but it's my first try with Gambas. The project might be a bit challenging.

Don't worry about the hassle. I haven't written an sqlite Gambas project since 2009, so I may be a bit out of practice!

I tried the command without success:-

(Click to enlarge)

hmm. Is there already a file in your docs directory named Lenses.sqlite? If so it nees to deleted or renamed.
I just recreated the DB with in a separate directory without any errors. It seems the SQL dump is OK to create a fresh new DB.

01McAc said

hmm. Is there already a file in your docs directory named Lenses.sqlite? If so it nees to deleted or renamed…

Yes that's it, thanks.

I'm sorry 01McAc,  I've spent a few hours on this but have not been able to solve your problem.

What you are trying to do is so fundamental, that it should be easy to implement.

My only suggestion now is that you either trawl through the Gambas mailing list by typing:-

Gambas Mailinglists Search: datasource

into your web browser (or use My Little Gambas Helper) or simply post a question on the Gambas mailing list.

The link to the user mailinglist is very helpful. Thanks for that. I found the answer of my question "Is the db.gb control able to manage DB-views?".
The answer from Benoît Minisini in 2019 was "no" and I think it is still valid in 2021. https://lists.gambas-b…user/2019-May/067085.html.

Obviously DataComboViews controls have not widely been used as there are just a few hits in the mailinglist. So I got stuck with the little challenging Lens-Register database GUI.
To be honest I have no experience in user mailinglists. Didn't know that they still exists these time where forums are popular. But thanks for your help.

I had a look at the database schema and there were a few things that were not needed.

A database view is normally a complex(?) series of select statements using multiple tables.
Having a view that is a single select statement is a bit pointless.

I took your original data and built a quick project to show using sqlite without any data bound controls.

The Makers and Mounts maintenance forms are working, but I only did a partial on the lenses form as that
was a LOT of work but it has the basics to show you.
I also added a 'Country' table for Makers and the ability to store the maker Logo.
The txt file with the country names and codes is located in Lenses/Data
The upload to database is in Module AG but commented out.

Run the Administration menu option as soon as you start the application and then set the 'Logo Path'
which should be [Project Area]/Lenses/Images
Set the Save path to [Project Area]/Lenses or your Documents folder.

The save path is where any Extracted Documents will be saved.
You can extract data from any table in HTML, CSV, JSon and XML
(The extraction process is quite generic and should work with any sqlite database. )

In the maintenance forms you will see a 'stamp' button on the top tool bar.
This is to allow cloning of any valid record and should clear any specific data on the form.

You will also see an 'Add another' button on the input form.
This is to allow you to add another record without having to click ok and open a new record
if you are entering multiple records in a session.

NOTE # I did not test all functionality in the application so there may be a few bugs here and there.
There is an Error management System that allows you to provide better feedback on error location & data.
All located in Module AE

Any questions, let me know.

Project

Quincunxian said

I had a look at the database schema and there were a few things that were not needed.

A database view is normally a complex(?) series of select statements using multiple tables.
Having a view that is a single select statement is a bit pointless.

I took your original data and built a quick project to show using sqlite without any data bound controls.

The Makers and Mounts maintenance forms are working, but I only did a partial on the lenses form as that
was a LOT of work but it has the basics to show you.
I also added a 'Country' table for Makers and the ability to store the maker Logo.
The txt file with the country names and codes is located in Lenses/Data
The upload to database is in Module AG but commented out.

Run the Administration menu option as soon as you start the application and then set the 'Logo Path'
which should be [Project Area]/Lenses/Images
Set the Save path to [Project Area]/Lenses or your Documents folder.

The save path is where any Extracted Documents will be saved.
You can extract data from any table in HTML, CSV, JSon and XML
(The extraction process is quite generic and should work with any sqlite database. )

In the maintenance forms you will see a 'stamp' button on the top tool bar.
This is to allow cloning of any valid record and should clear any specific data on the form.

You will also see an 'Add another' button on the input form.
This is to allow you to add another record without having to click ok and open a new record
if you are entering multiple records in a session.

NOTE # I did not test all functionality in the application so there may be a few bugs here and there.
There is an Error management System that allows you to provide better feedback on error location & data.
All located in Module AE

Any questions, let me know.

Stunning! Thank you Quincunxian for all your time and effort. It is actually a new application and it shows what is Gambas capable of. The idea how to filter  lenses is much better and a completely different approach. A lot easier for users and more intuitive. Well done.
The application runs in the "Projects" directory in my $Home. I am going to have a closer look into the code in the next days and try to understand how things can be done. A steep learning curve for me.  I am afraid I'll screw up the whole application when I try to add another feature.

Just one word re the database view. The view in the my DB was just an example to find out if gb.db supports views in general. The answer is afaik no, unfortunately. The original DB scheme is very basic and there is probably no reason to use a database rather than a CSV file.

Quincunxian said

Any questions, let me know.

I still have a closer look into code, was cranking up the debugger and set a lot of breaking points. At some point I was wondering what happens when I double click a row in the Frm_Lenses. The panel "Pan_Tools" disables, the form seems to hang but still responses to mouse clicks. In debugging mode the code points to some  other actions but I cannot find out what the purpose would be.
Another question I have is regarding the value boxes (Vbx_MaxAperture, Vbx_MinAperture, ..) in the same form. Do they have any functionality or is this unfinished yet? I am happy to take over your great job but it's not very clear to me why these valueboxes are behind the panel "Pan_Maint".
Cheers
01McAc

Double clicking on a row in the grid view opens that row for maintenance.
The tool panel is disabled at that point so you cannot inadvertently:
Add a new record while halfway through changing and existing record.

The way in which the form works is that the Pan_Maint with the IO controls is hidden.
Either the Gridview or the Treeview selection screens allow you to either change, clone or delete an existing record.
Pan_Maint is made visible; covering up the selection screen so that you can't inadventantly select another record
halfway though a valid maintenance process.
Adding a new record can be accessed from the Tool panel and additional records can be added
by the "Add_Another" button. This is disabled when changing a record as you are not 'adding another' and you must
Select 'Ok' to update the changed record.

The value box controls are used as they only allow numerical input. Storing numbers as text fields limits your capabilities
in doing specialised criteria  SELECT statements. Ie:
"SELECT * FROM Lenses WHERE( Max_Aperture BETWEEN 1 AND 2.9)"
"SELECT * FROM Lenses WHERE( Max_Aperture <= 3.9 OR Max_Aperture >= 2.0)"

You could use Textboxes for this input then convert it to a number before updating the record but this
saves a validation step to ensure that someone entered a number and not some text.

Quincunxian said

Double clicking on a row in the grid view opens that row for maintenance.

Oh yes, thanks. My fault. I was curious and moved the panel into the background to see what's behind. So, I reverted it back and the behaviour of the form is exactly as described it. But … I get an error when I double click a row in the lens maintenance form, edit a record and when I save it  (button "OK"). The red error message (see screen capture attached) actually saying .
I suspect the KeyClass didn't populate the data into KeyClass-attributes (?). How can I make sure, that the data is populated before the data is validated/updated?

(Click to enlarge)

My bad.

When I built the lenses maintenance form, I used the existing Maker Form as a template.
I forgot to change the remaining functions over to Lenses.
Because I build my forms in the same way, you can use ( more or less) any form as a template
to build a new form. To build the Mounts Form, which was very simple took around 15 minutes, as an example.

Here is the missing code that should make the form workable so that you can follow the logic in the SQL methods.
I would recommend using the Mounts form as it is far simpler and will give you the same information as the SQL
processes are exactly the same.

Please Note # These are NOT 'the best way' to manage these functions , they are just MY way
There are a lot of sub routines & functions in some of the classes that you will never need but I have other applications that do,
and I just copy them over. I keep a library of 'master' classes so that I don't have to maintain (too many) multiple versions.
 

Thanks, fixed. I noticed that I have to extend the database scheme at first. There are a bunch of fields needed for lenses . Secondly, I'll create some valid/real test data before I can have deep dive into your forms and code. The use of classes and inheritance is quite new for me - so it's not straight forward.
Here you go, you'll get an icy snowman from the northern hemisphere attached  

(Click to enlarge)

 It was 38 degrees here today in Perth Australia.
Your snowman is truly appreciated. !

In the meantime I added a few more attributes to the table "Lenses" which lead me to the question how to get and load the data into the Form "frm_Lenses". There are now 43 fields in table "Lenses". I think I found an option to replace the code in by a more generic function. The following (beta version) code loads the data into the form regardless of the appropriate control exists or not.


clsFields is a separate class defining some variables:

I borrowed parts of the code from the DB-diff example (Software-Farm). Actually, my intention was to code a function which returns a collection with field name and field type. But I'm not that experienced and couldn't find any specific code examples. I think the code above should do the trick. What do you think?

I'm glad that you are making changes to the way I do things as it's the best way to learn.
It was only ever meant to be an example to show all the aspects of data management without any dataview controls.

One of the reasons I abstract the data Get/Add/Change/Delete to a class, is that I can call that class anywhere in the application
to get access to that data. For example, on the Main (start up) form, you can set up a quick view of say:
All the Mount Types you have available.

Add a ListBox control to your main form.
Create the following subroutine.
Call the subroutine from your Form_Open event.



Lens Form
It's a busy form. Lots of variables in the lens data set. I did toy with the idea of using a TabStrip control, with each tab containing
lens elements controls that 'belonged' together but I don't know enough about lenses to sort out the relevant
information into any sensible sets/families.

One thing with using any tabbed control - TabPanel or TabStrip,  when you are validating data input for the user,
make sure that you display the panel with the error. There is nothing worse that getting a validation error and
having to hunt through various tabs to try and find it.

 I am happy to provide a first beta release of The Lens Register. If you are an photography aficionado the application enables you to manage lenses in general, your purchases, sales and clean-lube-adjust jobs. There are a lot of information about lenses available so I organised them into different tabs within an icon panel. Thanks to Quincunxian who helped me a lot and pointed me into right direction with Gambas and the database backend engine sqlite.
The app allows new entries for lenses, mounts and makers (manufacturer). If there are images of the lenses available you can drag and drop them into the appropriate field. Images are stored as jpg in the Image-folder. The entire data about lenses is stored in the database.
The software is still beta ( and probably will be for the next years. I haven't got the time to document the code as I should have been done it. Since I disabled the excellent error management in most of the modules and classes from Quincunxian it is easier for me to find bugs in case the compiler throws an error and it comes to a full stop in the IDE. If you find any errors don't keep them for yourself- instead give me a quick hint where to look at.
I still have a couple of awkward procedures in the code. One of them is the Sub "SelectedLoadGridView" in class "CLS_Lenses". I changed the original procedure in order to get a general solution to load the gridview with different columns due to different DB sources. I haven't got a clue how to keep this clean and tidy.

The app could be extraced in any directory of your choice. Feel free to use it, enhance it or just to browse through the code.

(Click to enlarge)

 I am just experimenting with the Full-Text Search engine (FTS) in sqlite3. FTS allows to search in all columns of all tables w/o a full table scan in the DB. The  performance is breathtaking (with a lot of non-sense test data). This is quite usefull if one is looking for specific information in a related table.
I know, it doesn't make sense with just a handful lenses in the database - but the journey is the reward. If anyone is interested I'm happy to share a next preliminary/beta release in the next days.