It is currently Tue Oct 20, 2020 1:39 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Database Example: Master and sub form two related tables
PostPosted: Mon Oct 14, 2019 8:09 am 
Offline
User
User

Joined: Mon Oct 14, 2019 7:31 am
Posts: 15
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Example: Master and sub form two related tables
PostPosted: Mon Oct 14, 2019 12:27 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 2681
Location: Germany
Link: viewtopic.php?f=13&t=67817#p502477

_________________
My Projects ThreadToGUI / OOP-BaseClass / OOP-BaseClassDispatch / EventDesigner V3
PB v3.30 / v5.70 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Example: Master and sub form two related tables
PostPosted: Mon Oct 14, 2019 1:11 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Wed Sep 22, 2010 1:17 pm
Posts: 352
Location: United Kingdom
Code:
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Example: Master and sub form two related tables
PostPosted: Mon Oct 14, 2019 8:57 pm 
Offline
User
User
User avatar

Joined: Mon Jul 17, 2017 7:22 am
Posts: 88
Location: Cotswolds England
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.

_________________
If it's falling over......just remember the computer is never wrong!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Example: Master and sub form two related tables
PostPosted: Tue Oct 15, 2019 6:01 am 
Offline
User
User

Joined: Mon Oct 14, 2019 7:31 am
Posts: 15
Thank you so Much - works great, great example to build on.

spikey wrote:
Code:
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


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Example: Master and sub form two related tables
PostPosted: Tue Oct 15, 2019 6:17 am 
Offline
User
User

Joined: Mon Oct 14, 2019 7:31 am
Posts: 15
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: Database Example: Master and sub form two related tables
PostPosted: Tue Oct 15, 2019 1:39 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Wed Sep 22, 2010 1:17 pm
Posts: 352
Location: United Kingdom
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...


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 48 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye