Page 1 of 1

Database Example: Master and sub form two related tables

Posted: Mon Oct 14, 2019 8:09 am
by mchael
New and fresh to PB - looking for any examples or code blocks to help get started. I am working on an app 3 tables: Text area form elements, grid (loading table) at top related to record of text area elements, and a second grid related to the top grid selected row.

Are there any examples of loading data from a database to a grid and then in a second grid loading related data to selected row on the master table.

Like in Access master and sub form?

thank you

Re: Database Example: Master and sub form two related tables

Posted: Mon Oct 14, 2019 12:27 pm
by mk-soft

Re: Database Example: Master and sub form two related tables

Posted: Mon Oct 14, 2019 1:11 pm
by spikey

Code: Select all

Declare CheckDatabaseUpdate(Database.I, Query.S)
Declare CreateDemoDB()
Declare OpenWindow0(x = 0, y = 0, width = 600, height = 400)
Declare LoadParentList()
Declare LoadChildList()

UseSQLiteDatabase()

Global DatabaseFile.S = GetTemporaryDirectory() + "Database.sqlite"

Enumeration FormWindow
  #Window0
EndEnumeration

Enumeration FormGadget
  #ParentList
  #ChildList
EndEnumeration

Procedure CheckDatabaseUpdate(Database.I, Query.S)
  
  Result = DatabaseUpdate(Database, Query)
  
  If Result = 0
    Debug DatabaseError()
  EndIf
  
  ProcedureReturn Result
EndProcedure

Procedure CreateDemoDB()
  
  If CreateFile(0, DatabaseFile)
    CloseFile(0)
    
    If OpenDatabase(0, DatabaseFile, "", "")
      
      CheckDatabaseUpdate(0, "CREATE TABLE food (foodid INTEGER, name TEXT)")
      CheckDatabaseUpdate(0, "CREATE TABLE attributes (attribid INTEGER, foodid INTEGER , name TEXT, value TEXT)")
      
      CheckDatabaseUpdate(0, "INSERT INTO food (foodid, name) VALUES (1, 'apple'), (2, 'pear'), (3, 'banana');") 
      
      CheckDatabaseUpdate(0, "INSERT INTO attributes VALUES (1, 1, 'colour', 'red'), (2, 1, 'weight', '10'), (3, 1, 'taste', 'appley');")
      CheckDatabaseUpdate(0, "INSERT INTO attributes VALUES (4, 2, 'colour', 'green'), (5, 2, 'weight', '5'), (6, 2, 'taste', 'peary');")
      CheckDatabaseUpdate(0, "INSERT INTO attributes VALUES (7, 3, 'colour', 'yellow'), (8, 3, 'weight', '20'), (9, 3, 'taste', 'bananary');")
      
    Else
      
      Debug "Can't create the database file !"
      End
      
    EndIf
    
  EndIf
  
EndProcedure

Procedure OpenWindow0(x = 0, y = 0, width = 600, height = 400)
  OpenWindow(#Window0, x, y, width, height, "", #PB_Window_SystemMenu)
  
  ListIconGadget(#ParentList, 10, 10, 580, 180, "Food", 100)
  
  ListIconGadget(#ChildList, 10, 200, 580, 180, "Attribute", 100)
  AddGadgetColumn(#ChildList, 1, "Value", 100)
  
  BindGadgetEvent(#ParentList, @LoadChildList(), #PB_EventType_Change) 
EndProcedure

Procedure LoadParentList()
  
  ClearGadgetItems(#ParentList)
  
  Query.S = "SELECT * FROM food;"
  Index = 0
  
  If DatabaseQuery(0, Query)
    ClearGadgetItems(#ParentList)
    
    While NextDatabaseRow(0)
      
      ; Add the parent item to the list.
      AddGadgetItem(#ParentList, Index, GetDatabaseString(0, 1))
      
      ; Store the parent row id in the gadget for later.
      SetGadgetItemData(#ParentList, Index, GetDatabaseLong(0, 0))
      
      Index + 1
      
    Wend
      
  EndIf
  FinishDatabaseQuery(0)
  
EndProcedure

Procedure LoadChildList()
  
  ParentRow = GetGadgetState(#ParentList)
  If ParentRow = -1
    ; Stop if no parent value is selected.
    ProcedureReturn 
  EndIf
  
  ; Retrieve the parent id from the parent gadget.
  ParentId = GetGadgetItemData(#ParentList, ParentRow)
  
  Query.S = "SELECT * FROM attributes WHERE foodid = " + StrU(ParentId) + ";"
  
  If DatabaseQuery(0, Query)
    ClearGadgetItems(#ChildList)
    
    While NextDatabaseRow(0)
      
      ; Add the child item to the list.
      AddGadgetItem(#ChildList, Index, GetDatabaseString(0, 2) + #LF$ + GetDatabaseString(0, 3))
      
    Wend
      
  EndIf
  FinishDatabaseQuery(0)
  
EndProcedure

; Main
If FileSize(DatabaseFile) = -1
  CreateDemoDB()
EndIf
OpenDatabase(0, DatabaseFile, "", "")
  
OpenWindow0(50, 50)
LoadParentList()

Repeat 
Until WaitWindowEvent(10) = #PB_Event_CloseWindow

Re: Database Example: Master and sub form two related tables

Posted: Mon Oct 14, 2019 8:57 pm
by C87
All these are searches in 'Topic Titles Only' that you may find helpful

Indexed files
YADA - Yet another database application
Search and replace in multiple files

PB.Ex GridGadget (Windows)
[Module] GridExModule.pbi
GridGadget
Canvas based Grid gadget

Rather than the grid code you may well find that ListIconGadget is an easier solution. If you search for ListIconGadget, again in 'Topic Titles Only', you will see something to help you. Don't do a general search. It will give you far too many, hundreds to look through and you have to open each one to see if it is of any use. Mind you I have seen a brilliant grid written in PureBasic with many subscreens linked and activated from the main one.....but I couldn't locate it!...if I do I'll post the link.

You will find it a major change from Access to a PureBasic solution, a paradigm shift in fact. The terminology for a start, you may wonder what on earth is a gadget? Gadgets, Modules and code structure differences are hurdles to overcome. It will take a few weeks to stop using an apostrophy for a Rem, instead of a semi-colon. But the Basic is similar. However, no Else in Case and no Then in If-EndIf, no space in EndIf, no IIF(). Plenty of built-in fuctions in PureBasic though but you have to write code to do what Access does easily with very little code. Mind you all that visual stuff needs a huge program and Access RunTime chugging away in the background. You can't write a name and address program in Access that is only 1500Kb in size. (The YADA, noted above is about that)

If you are using an Access database in PureB you will need to have a compact and repair because of the Access bloat, in order to avoid corruption. I have written large and reliable software with 60 or so network users, in several versions of Access since 1999. I would often have a database system with three linked forms for three related tables all on main form containing the three subforms. Each of the three SubForms would have several relational lookups. Easy to do in Access but more work to do the same here. Have to say that Properties in Access are a huge time saver. It should be worth the effort to switch to PureBasic, well as far as I'm concerned, I do hope so!

I do have a naming system in database applications that I used since in the 1980s using Clipper and keep meaning to post it on the forum. It is very structured and means that picking up a program
from 10, 20 or 30 years ago you can easily follow the code and the database design, regardless of the language it is written in. I used it it separate tables as well as the Access or other SQL container type database and it was always fine. Much easier to follow relationships in the software. I'll look into posting it, which may help anyone creating database systems.

Reading through the HELP file is always a good idea. One big benefit of PureBasic is that it is well supported and regularly updated. I don't think you will get any online support for Access that is remotely anything like as good as PureBasic.

Re: Database Example: Master and sub form two related tables

Posted: Tue Oct 15, 2019 6:01 am
by mchael
Thank you so Much - works great, great example to build on.
spikey wrote:

Code: Select all

Declare CheckDatabaseUpdate(Database.I, Query.S)
Declare CreateDemoDB()
Declare OpenWindow0(x = 0, y = 0, width = 600, height = 400)
Declare LoadParentList()
Declare LoadChildList()

UseSQLiteDatabase()

Global DatabaseFile.S = GetTemporaryDirectory() + "Database.sqlite"

Enumeration FormWindow
  #Window0
EndEnumeration

Enumeration FormGadget
  #ParentList
  #ChildList
EndEnumeration

Procedure CheckDatabaseUpdate(Database.I, Query.S)
  
  Result = DatabaseUpdate(Database, Query)
  
  If Result = 0
    Debug DatabaseError()
  EndIf
  
  ProcedureReturn Result
EndProcedure

Procedure CreateDemoDB()
  
  If CreateFile(0, DatabaseFile)
    CloseFile(0)
    
    If OpenDatabase(0, DatabaseFile, "", "")
      
      CheckDatabaseUpdate(0, "CREATE TABLE food (foodid INTEGER, name TEXT)")
      CheckDatabaseUpdate(0, "CREATE TABLE attributes (attribid INTEGER, foodid INTEGER , name TEXT, value TEXT)")
      
      CheckDatabaseUpdate(0, "INSERT INTO food (foodid, name) VALUES (1, 'apple'), (2, 'pear'), (3, 'banana');") 
      
      CheckDatabaseUpdate(0, "INSERT INTO attributes VALUES (1, 1, 'colour', 'red'), (2, 1, 'weight', '10'), (3, 1, 'taste', 'appley');")
      CheckDatabaseUpdate(0, "INSERT INTO attributes VALUES (4, 2, 'colour', 'green'), (5, 2, 'weight', '5'), (6, 2, 'taste', 'peary');")
      CheckDatabaseUpdate(0, "INSERT INTO attributes VALUES (7, 3, 'colour', 'yellow'), (8, 3, 'weight', '20'), (9, 3, 'taste', 'bananary');")
      
    Else
      
      Debug "Can't create the database file !"
      End
      
    EndIf
    
  EndIf
  
EndProcedure

Procedure OpenWindow0(x = 0, y = 0, width = 600, height = 400)
  OpenWindow(#Window0, x, y, width, height, "", #PB_Window_SystemMenu)
  
  ListIconGadget(#ParentList, 10, 10, 580, 180, "Food", 100)
  
  ListIconGadget(#ChildList, 10, 200, 580, 180, "Attribute", 100)
  AddGadgetColumn(#ChildList, 1, "Value", 100)
  
  BindGadgetEvent(#ParentList, @LoadChildList(), #PB_EventType_Change) 
EndProcedure

Procedure LoadParentList()
  
  ClearGadgetItems(#ParentList)
  
  Query.S = "SELECT * FROM food;"
  Index = 0
  
  If DatabaseQuery(0, Query)
    ClearGadgetItems(#ParentList)
    
    While NextDatabaseRow(0)
      
      ; Add the parent item to the list.
      AddGadgetItem(#ParentList, Index, GetDatabaseString(0, 1))
      
      ; Store the parent row id in the gadget for later.
      SetGadgetItemData(#ParentList, Index, GetDatabaseLong(0, 0))
      
      Index + 1
      
    Wend
      
  EndIf
  FinishDatabaseQuery(0)
  
EndProcedure

Procedure LoadChildList()
  
  ParentRow = GetGadgetState(#ParentList)
  If ParentRow = -1
    ; Stop if no parent value is selected.
    ProcedureReturn 
  EndIf
  
  ; Retrieve the parent id from the parent gadget.
  ParentId = GetGadgetItemData(#ParentList, ParentRow)
  
  Query.S = "SELECT * FROM attributes WHERE foodid = " + StrU(ParentId) + ";"
  
  If DatabaseQuery(0, Query)
    ClearGadgetItems(#ChildList)
    
    While NextDatabaseRow(0)
      
      ; Add the child item to the list.
      AddGadgetItem(#ChildList, Index, GetDatabaseString(0, 2) + #LF$ + GetDatabaseString(0, 3))
      
    Wend
      
  EndIf
  FinishDatabaseQuery(0)
  
EndProcedure

; Main
If FileSize(DatabaseFile) = -1
  CreateDemoDB()
EndIf
OpenDatabase(0, DatabaseFile, "", "")
  
OpenWindow0(50, 50)
LoadParentList()

Repeat 
Until WaitWindowEvent(10) = #PB_Event_CloseWindow

Re: Database Example: Master and sub form two related tables

Posted: Tue Oct 15, 2019 6:17 am
by mchael
The quality and care of the PB community is incredible. PB is a great world to be in.

I am using sqllite. We use it extensively mainly in a c# app which we are moving away from back to C and Fortran and looking at PB for RAD prototyping or handling the UI level. PB with the right gadgets etc means people can put Access in the bin. I will be sure to learn the particulars you mention of PB and those Posts you pointed out along with the example above is what was needed to get me going and inspired. Thank you.

Would love to see your database naming system, we as a world need structured programming back as the main paradigm for software engineering and standardize from the get go so things are engineered. OOP allows too much code and data structures and methods to go into never never abstract that is a security problem and costly for maintenance, not to mention to wasting resources. Your Database naming system is of value as people forget the DB is the data structure that needs to carry through into the data structures in the code.
C87 wrote:
I do have a naming system in database applications that I used since in the 1980s using Clipper and keep meaning to post it on the forum. It is very structured and means that picking up a program
from 10, 20 or 30 years ago you can easily follow the code and the database design, regardless of the language it is written in. I used it it separate tables as well as the Access or other SQL container type database and it was always fine. Much easier to follow relationships in the software. I'll look into posting it, which may help anyone creating database systems.

Re: Database Example: Master and sub form two related tables

Posted: Tue Oct 15, 2019 1:39 pm
by spikey
mchael wrote:The quality and care of the PB community is incredible. PB is a great world to be in.
You're welcome. :D

I see that I forgot to put in a foreign key reference between the parent and child tables - I'd strongly recommend you do this in your real database, if you haven't already...