sqlite3, database views and db.gb.form controls. Does it work?

Ask about the individual Gambas components here.
01McAc
Posts: 34
Joined: Sunday 24th January 2021 8:25pm

sqlite3, database views and db.gb.form controls. Does it work?

Post by 01McAc » Wednesday 27th January 2021 10:22am

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.
Attachments
Lens-app-pre-alpha.jpg
Lens-app-pre-alpha.jpg (191.46 KiB) Viewed 515 times

User avatar
BruceSteers
Posts: 392
Joined: Thursday 23rd July 2020 5:20pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post by BruceSteers » Wednesday 27th January 2021 8:54pm

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

Have you looked all over the wiki?
http://gambaswiki.org/wiki/comp/gb.db.f ... view/table

http://gambaswiki.org/wiki/comp/gb.db.f ... acomboview

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

Sorry I cant help more.
Wishing well
Bruce

If at first you don't succeed , try it differently.

01McAc
Posts: 34
Joined: Sunday 24th January 2021 8:25pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post by 01McAc » Thursday 28th January 2021 4:35pm

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.
Attachments
Lenses.sqlite.tar.gz
(1.85 KiB) Downloaded 20 times
Lens-Register-0.0.7.tar.gz
(17.31 KiB) Downloaded 19 times

User avatar
stevedee
Posts: 378
Joined: Monday 20th March 2017 6:06pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post by stevedee » Thursday 28th January 2021 6:33pm

01McAc wrote:
Thursday 28th January 2021 4:35pm
...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.
Static Public Sub createDBcon()
  
       Try $con.Close()          ' Schließen Sie die Verbindung, damit die folgende Verbindung ohne Fehler erfolgt
            $con.Type = "sqlite3"        ' Definiert den Verbindungstyp
            $con.Host = User.Home & "~/docs" ' Host ist der Pfad, in dem sich die SQLite-Datenbankdatei befindet"
            $con.Name = "Lenses.sqlite"    ' Datenbankname ist der Name der Datenbankdatei
       Try $con.Open()                  ' Wir aktivieren und öffnen die Verbindung, der Versuch ist, einen Fehler zuzulassen

       If Not IsNull($con) Then
...
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?

01McAc
Posts: 34
Joined: Sunday 24th January 2021 8:25pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post by 01McAc » Thursday 28th January 2021 7:05pm

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:

Code: Select all

$con.Host = User.Home & "~/docs"
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

Code: Select all

sqlite3  Lenses.sqlite < Lenses.sqlite.sql
That's it. Sorry for the hassle but it's my first try with Gambas. The project might be a bit challenging.
Attachments
Screenshot_20210128_195054.jpg
Screenshot_20210128_195054.jpg (69.82 KiB) Viewed 486 times

User avatar
stevedee
Posts: 378
Joined: Monday 20th March 2017 6:06pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post by stevedee » Thursday 28th January 2021 7:39pm

01McAc wrote:
Thursday 28th January 2021 7:05pm
...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:-
SqliteProb.png
SqliteProb.png (223.99 KiB) Viewed 482 times

01McAc
Posts: 34
Joined: Sunday 24th January 2021 8:25pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post by 01McAc » Thursday 28th January 2021 7:52pm

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

Code: Select all

sqlite3 Lenses.sqlite < Lenses.sqlite.sql
in a separate directory without any errors. It seems the SQL dump is OK to create a fresh new DB.

User avatar
stevedee
Posts: 378
Joined: Monday 20th March 2017 6:06pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post by stevedee » Thursday 28th January 2021 7:59pm

01McAc wrote:
Thursday 28th January 2021 7:52pm
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.

User avatar
stevedee
Posts: 378
Joined: Monday 20th March 2017 6:06pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post by stevedee » Friday 29th January 2021 12:42pm

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:-

https://lists.gambas-basic.org/cgi-bin/ ... datasource

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

01McAc
Posts: 34
Joined: Sunday 24th January 2021 8:25pm

Re: sqlite3, database views and db.gb.form controls. Does it work?

Post by 01McAc » Friday 29th January 2021 3:24pm

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-basic.org/pipermai ... 67085.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.

Post Reply