gb.db - database component, simple search form?

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

gb.db - database component, simple search form?

Post by 01McAc »

Hi all,

I am new here and the very first post regarding the database component in Gambas gb.db. In the past I was using MS-Access every now and then for simple DB-driven apps. Now I try to get the first project in Gambas with a simple sqlite3 database for photo lenses. There are only 3 tables at the moment. I managed to get a form with the lens data (name, aperture, date of purchase etc). In two more forms I can add, delete or update the manufactorer and the mount (e.g. Canon, M42, ...).
Now the question: how can I manage to create a search form based on the lenses form? Do I have to create it manually? Is there any option to use another component?
I know the DataBrower object as part of gb.db. It already has controls for next/previous/new record/etc - but the DataBrowser is not able to show the appropiate foreign keys in table lens. So this seems no option. Any ideas? Thanks.

The simple schema of the DB:
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Makers" (
"ID_Makers" INTEGER NOT NULL,
"Maker" TEXT NOT NULL,
"Notes" TEXT,
PRIMARY KEY("ID_Makers" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Mounts" (
"ID_mounts" INTEGER NOT NULL,
"Mount" TEXT NOT NULL,
"Notes" TEXT,
PRIMARY KEY("ID_mounts" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "lenses" (
"ID_lenses" INTEGER NOT NULL,
"Make" INTEGER NOT NULL,
"DateBuy" TEXT NOT NULL,
"Mount" INTEGER NOT NULL,
"MaxAperture" TEXT NOT NULL,
"MinAperture" TEXT NOT NULL,
"FocalLength" TEXT NOT NULL,
"Type" TEXT,
"Colour" TEXT,
"SerialNr" TEXT,
"Label" TEXT,
"Comments" TEXT,
PRIMARY KEY("ID_lenses" AUTOINCREMENT)
);
CREATE UNIQUE INDEX IF NOT EXISTS "ID_lenses" ON "lenses" (
"ID_lenses" ASC
);
CREATE VIEW "vwLenses" AS Select * from lenses;
User avatar
cogier
Site Admin
Posts: 1118
Joined: Wednesday 21st September 2016 2:22pm
Location: Guernsey, Channel Islands

Re: gb.db - database component, simple search form?

Post by cogier »

Welcome to the forum.

I am not up to speed with databases, however, there is a sample program on the 'Gambas Farm' or here that might help. My favoured way to deal with data is to use CSV files. If you post a spreadsheet of some of your data I can show you another way to search for items.
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: gb.db - database component, simple search form?

Post by 01McAc »

Thank you. Before I started with Gambas I had a look into all database examples I could find. What I am actually looking for is a general solution for a form based search.
After thinking a while about this problem I try to code a general approach in order to re-use the code in other applications. Simple use-cases look like as follows:
- push a button "Search/Find". Execute the click-event
- the click-event creates new textboxes for each DataControl (same x/y-position, width and height) as a new layer above the existing DataControl controls at runtime. This is pretty easy with a loop through all Form.controls
- each of the textboxes get a Tag referring to the original DataControl
- the new textboxes can be edited for filter criteria
- Push a button "Apply search" and run the on-click-event
- the on-click-event procedure checks the content of the newly created textboxes, builds the WHERE-Statement and fuels DB.Subst and finally filters the datasource-control

This is already done (apart from the last point) with only a few lines of code. Thanks to Gambas. I hope you can follow my ideas.

So far so good. I am struggling at the moment with a simple problem:
How can I read the columns and rows of the control DataComboView provided by gb.db.form ?? I have to columns in one of my DataComboView with a couple of rows:
Maker ID_Maker (primary key)
------------------------------
Canon 1
Tamron 2

How can I go through the columns and rows of DataComboView and print/debug them into the debug window? I couldn't find any code snippet on the internet, so I have no idea how to do it. Any ideas?
User avatar
BruceSteers
Posts: 1521
Joined: Thursday 23rd July 2020 5:20pm
Location: Isle of Wight
Contact:

Re: gb.db - database component, simple search form?

Post by BruceSteers »

01McAc wrote: Tuesday 26th January 2021 12:58pm So far so good. I am struggling at the moment with a simple problem:
How can I read the columns and rows of the control DataComboView provided by gb.db.form ?? I have to columns in one of my DataComboView with a couple of rows:
Maker ID_Maker (primary key)
------------------------------
Canon 1
Tamron 2

How can I go through the columns and rows of DataComboView and print/debug them into the debug window? I couldn't find any code snippet on the internet, so I have no idea how to do it. Any ideas?
Hi. for a GridView you would use this method with angle brackets..

For iRow As Integer = 0 To GridView1.Rows.Max

   Debug GridView1[iRow, 0].Text  ' 0 for column 0

Next

it's possibly the same or similar for your object. (not used it myself)
but hit the left angle bracket [ and see what it says in the ide :)
Bruce
Last edited by BruceSteers on Tuesday 26th January 2021 2:43pm, edited 1 time in total.
If at first you don't succeed , try doing something differently.
BruceS
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: gb.db - database component, simple search form?

Post by 01McAc »

Appreciate your suggestions. I checked immediately but unfortunately, it doesn't work as DataComboView controls do not have a .Max method like GridView. The compiler throws an error message.
Other ideas?
User avatar
BruceSteers
Posts: 1521
Joined: Thursday 23rd July 2020 5:20pm
Location: Isle of Wight
Contact:

Re: gb.db - database component, simple search form?

Post by BruceSteers »

sure Max is a synonym for Count-1

So if the object lacks .Max use .Count - 1 :)
If at first you don't succeed , try doing something differently.
BruceS
User avatar
BruceSteers
Posts: 1521
Joined: Thursday 23rd July 2020 5:20pm
Location: Isle of Wight
Contact:

Re: gb.db - database component, simple search form?

Post by BruceSteers »

BruceSteers wrote: Tuesday 26th January 2021 2:38pm sure Max is a synonym for Count-1

So if the object lacks .Max use .Count - 1 :)
oops

Maybe try GridView1.Rows.Max

(I forgot the Rows part sorry , I'll adjust the example above)
If at first you don't succeed , try doing something differently.
BruceS
User avatar
BruceSteers
Posts: 1521
Joined: Thursday 23rd July 2020 5:20pm
Location: Isle of Wight
Contact:

Re: gb.db - database component, simple search form?

Post by BruceSteers »

okay sorry i have breifly looked at the wiki now.

the object does not have Rows so it's done a different way
using the associated DataView or DataSoiurce object the combobox is attached to.

DataView.Count
or
DataSource.Count

It will be a similar to retrieve the info as it was to enter it.

I feel i can't help any more though and may only miss-advise as it's not something i've used before and seems to work in a way you probably know more about than me.

There's always a way though :)
Bruce
If at first you don't succeed , try doing something differently.
BruceS
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: gb.db - database component, simple search form?

Post by 01McAc »

Bruce,

thanks for your help. I need to think about the problem with the DataComboView-controls.
In the meantime I finished a rough solution re the use-cases I mentioned above in one of my posts. In detail: a form based DB-search in sqllite3 works for the dabase bound controls DataControl. When I start the search DB.Subst looks like

Code: Select all

ID_lenses = 1 AND FocalLength = '35' AND Colour = 'Black'
.
Before I am able to assemble the filter in a function I need to lookup each field in the database what type ID_lenses (INTEGER) , FocalLength (TEXT) and Colour (TEXT) is. As sqlite3 is not fully SQL compatible it requires a weird

Code: Select all

pragma table_info("lenses)"
to describe the table "lenses".
If someone is interested in the code I'll post it once it is tidied up.
Post Reply