Forum > Databases

Using DBlookup to modify a SQLite table column

(1/4) > >>

MickB:
Good day all,

By way of disclaimer I have to say that I have not done any serious programming for 20 years (using Delphi/Paradox), I have not used Lazarus much and I have never used SQLite. I want to be able to use Lazarus Pascal and SQLite for a project I am working on. Before starting on the project I am playing around with some very simple things to get familiar with the software. That being said, the question is: how can I use a lookup to alter a value in another table's column. Let me explain.

I have two tables: (1) Countries and (2) Cities defined as:


--- Code: MySQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TABLE "Countries" ("CountryID"INTEGER NOT NULL UNIQUE,"Country"NVARCHAR(30),"CapitalID"INTEGER,PRIMARY KEY("COuntryID" AUTOINCREMENT),FOREIGN KEY("CapitalID") REFERENCES "Cities"("CityID")) CREATE TABLE "Cities" ("CityID"INTEGER NOT NULL UNIQUE,"City"NVARCHAR(30),PRIMARY KEY("CityID" AUTOINCREMENT)) 
The idea is that for each Country in the Countries table there will be one entry (in the CapitalID column) for the corresponding city in the Cities table.

I am using Zeos DBO but that is not locked in. I'm happy to use standard DB components.

In the main form I have a DBgrid. The DBgrid displays the columns for the Countries table plus an extra column to display the name of the capital city. The SQL is:


--- Code: MySQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---select CountryId, country, CapitalID, cityfrom countriesleft  join citieson countries.CapitalID = cities.cityid;     
This displays as expected (see attached screenshot).

If I don't have the join in the SQL, I can update the CapitalID column with a dblookup. However, with the join, the DBlookup will not update the column. I get a message:

--- Quote ---Cannot update a complex query with more than one table.
--- End quote ---

Obviously I am doing something wrong - but what?.  I would like to have the actual city displayed in the DBgrid but still be able to update it.

Any suggestions would be greatly appreciated.

rvk:
How does your update SQL look like?
(You need to write those manual for join selects)

And you can only update one table. You can't update the CapitalID in Countries at the same time as the capitolname in Cities. For that you need to think about redesigning your screens and use separate entry dialogs.

BTW. Why is the table called Cities?
Shouldn't it be called Capitols?

If Cities can have non-capitol cities, then that table also needs to have another field CountryID as foreign key pointing back to a country. Make sure to think about your DB design beforehand. Or don't you need to know what country a city is in???

Zvoni:
First off:
Since you want to use SQLite, you should get familiar with it.
1) Don't use AutoIncrement for Integer Primary Keys in SQLite. You don't need it. SQLite will still count up automatically.
2) Don't use Datatypes like VARCHAR, DateTime etc. Use the real and correct DataTypes.
3) Your Table-Setup is Nonsense. It should be the other way around: Countries is the Master-Table to Cities. the Cities-Table needs a Foreign Key to Countries' Primary Key.
If you're interested in the Capitol of a Country, just add a Column "Capitol" to "Cities with DataType Integer defaulting to 0. If a City is the Capitol, set it to 1
(I'd probably implement an AFTER INSERT and AFTER UPDATE Trigger setting all other "Capitol"-Values to 0, if a City gets the "1" for Capitol).

Then the Query to get the Countries with its Capitol is straightforward.
Untested

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TABLE "Countries" (  "ID" INTEGER PRIMARY KEY,  "CountryName" TEXT UNIQUE NOT NULL); CREATE TABLE "Cities" (  "ID" INTEGER PRIMARY KEY,  "CityName" TEXT NOT NULL,  "Capitol" INTEGER DEFAULT 0,  "Country_ID" INTEGER,  FOREIGN KEY ("Country_ID") REFERENCES "Countries"("ID") ON UPDATE CASCADE ON DELETE CASCADE); 

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT CO.ID AS CountryID, CHAR(CO.CountryName) AS CountryName, CI.ID AS CityID, CHAR(CI.CityName) AS CityNameFROM Countries AS CO LEFT JOIN Cities AS CI ON CI.Country_ID=CO.ID AND CI.Capitol=1

rvk:

--- Quote from: Zvoni on February 22, 2024, 08:43:54 am ---If you're interested in the Capitol of a Country, just add a Column "Capitol" to "Cities with DataType Integer defaulting to 0. If a City is the Capitol, set it to 1
(I'd probably implement an AFTER INSERT and AFTER UPDATE Trigger setting all other "Capitol"-Values to 0, if a City gets the "1" for Capitol).

--- End quote ---
In that case you have a field which is seldomly used (only once per country).

So you could also have a capitalID field in Country pointing back at the cities (like it is now).

But you do need to make constraints in that case not to select a city which isn't in it's country.
So there are two ways to look at that design.

The problem will still be the input-design.
I think this calls for separate dialogs or inputfields instead of just being able to edit the TDBGrid.

MickB:
Thanks for your replies rvk and zvoni.
rvk: The names of the tables don't matter. They are some simple tables I am using to get familiar with SQLite. They could be anything. For example the Cities table could be a Mark-Ups table where each line is a mark-up percent, (10%, 20%) and the Countries table could be a Products table so each product would have only one mark-up applied to it. I used Countries and Cities just to play around with. The cities table does not have countries associated with it so I could select London as the capital of the USA. While this does not reflect the real world, I am trying to get the updates working as, e.g., if I was doing markups and products, the markup would not belong to only one product. 

zvoni: Please see my first comment to rvk. I am trying to get familiar with it and with Lazarus. I can only go on examples I see and many of them used autoincrement. However, if SQLite does not need them I can remove them and I can change the datatypes but those don't get to the issue. That is, I want to be able to update (in this example) the capitalId column in the countries table using the id of the value I select in a dblookup. I'm not sure why I need a foreign key in the Cities table as there will only ever be one capital city. Surely it should be possible to use the tables as they are (ignoring autoincrement columns) Again, this is just a simple example for me to play with SQLite. Each item in the countries table will only ever have one item from the cities table. The tables could be anything as I mentioned to rvk above.

Cheers

Navigation

[0] Message Index

[#] Next page

Go to full version