best way to switch between multiple datasets with dbcontrols

[m.ardito]

hi all!

i'm learning lazarus and freepascal, mostly for personal interest and learning itself, and love this ide/language which is quite new to me, while i have some xperience with other languages/ide.

i would like to develop a small erp-like app much similar to what i can do with, say, msaccess backed by mysql (or others) db servers.

i've done some experience and i can successfully connect and use db enabled controls, both dbgrids and single field ones, but now i have reached a higher complexity degree  and i need some advice in the setup and developement strategy, so i'm here asking! 

my test app is a kind of home expense recorder, bank accounts status, services bills (eg: gas, electricity etc.), and i have build a mysql dedicated db with all the tables (i think are) necessary to record all the infos. i've kinda populated manually it with a few different records of each kind, and reworked its design few time, until now, but it should work.

the lazarus app should allow me to read info from different tables, and let the user (me!) view, edit, insert or delete all needed fields from different kind of records (ie: a bank account record is completely different from  a gas bill, etc), as well as some table that stores generic info like configurations, code labels, etc. the usual stuff.

i've roughly built of a form with
- a combobox which lists the kind of records user can edit
- a dblist which reads from the db all the records of the kind selected in the combobox
- a pagecontrol with a page for every kind of record, with dbcontrols (single fields) fit for that kind of record
- buttons to add a record or edit/delete selected records. (not working yet)

when the user changes the kind of record in the combobox, the list accordingly shows the records of that kind stored in the db, and the pagecontrol shows only the page that keeps the dbcontrols for that kind of record. sounds quite boring, but efficient enough to me... in theory!

i've set up:
- a connection > transaction > sqlquery > datasource
- the dblist is connected to the datasource
- there is a page related to the only kind of record up to now
- the dbcontrols on that page have their datasource/datafield set
- selecting the first (the only up to now) kind of record, i set the sql text of the sqlquery component to read all "that" kind of records an it shows them in the list, no problem, works. i can switch different kinds of records, which are shhown in the list.
- if i select, in the list, one of the records (the only kind i have a page for up to now), the page is shown and the right values are shown in the page dbcontrols, no problem, works.

now the problem came when i added another page, for another kind of records, in the same database. i could add the other page, the controls on it and i could set the same datasource/datafield, planning (at the combo "change value" event) of deactivate/changing SQL text/reactivate the same sqlquery component, and it kinda works, but at runtime i have an error with the "other page" components complaining that "their" datafield is not found in the datasource: of course, i've just completely changed that!

i could think of a connection > transaction > sqlquery > datasource dedicated set for each page, so that i can use many datasets at the same time at runtime, ready for the user switching pages, and kinds of records, but are there any alternatives?

in msaccess i can rely on linked tables/query being "already there", and form controls can "just use them" through their "rowsource" properties, which accept sql: i could build something like that in the lazarus app but i see this strategy as not that efficient, i could end up having dozens (x4) components, in a real world app! but only one being used at a time!

is there a way to "deactivate/reactivate" the dbcomponents so to keep activated only those in sync with a single sqlquery controls which switch its sql (and so, available fieldnames for dbcontrols)?

how can i do this? or have you any other better working approach or suggestions?

Thanks!
Marco

[taazz]

do the following

1) each page has its own DataSource component
2) changing the record kind set the active page datasource.dataset := nil;
3) change the sql as needed reopening the query, at the new page set the
    datasource.dataset := YourSqlQuery;

that should give you the results you are after for now. For a better advice I need to know the database Schema mainly to understand your thinking on the subject.


Regards.

[m.ardito]

oh thanks! 

if i get what you suggest i can:
- set up only one connection, transaction, sqlquery component
- set up a datasource component for each page (different data kind) each one with datasource.dataset:=nil, at start.

i've thought of adding a "neutral" pagecontrol.page (default) to show when there is no selection, perhaps with some instructions/info on how to use that section   

then, switching "data type", ie record list & dbcontrols page,
- if the previous pagecontrol.page has a datasource, set its dataset to nil
- set the active datasource.dataset to the only sqlquery component i've set on the form
- deactivate/set the sql properly/reactivate it

can this be working? i can not try just now, but will soon.

this make me understand that i do not know well the rules that must be followed for sharing all the "sqldb tab" components, like tsqlquery & tsqltransaction. can they be shared by different datasource components switching only (as you suggest) their dataset property (or nil)? as long as i am connecting to a single db, is it safe to use only one connection, transaction, sqlquery component? because they are "related" to each other... connections refers transaction and vice versa, and sqlquery refers both... 

maybe this should be asked in a dedicated thread/topic?

Thanks, Marco

[taazz]

The idea behind the current design of the sqldb components goes like that.
Connection -> 1..N Transactions -> each transaction can have 1..N SqlQueries -> each query can 1..N Datasources.
take a look on the diagram below.

[m.ardito]

thanks for the diagram, it helps, and is roughly what i had in mind but more than before i still don't understand why sqlquery component has a "database" property, even if it has a "transaction" property; the transaction component is _already_ bound to a connection component (which is bound to a database)...
maybe this apparently redundant properties are there for more complicate setups?!?

i think that in most applications with an sql backend you don't need (always) many sql query *** active at the same time *** but it is true that you tend to use the db backend for many many data, if not all, and so you mainly expect to be able to switch from one data query to another with ease... but efficiently

db-bound controls, as in this case, do expect perhaps to be permanently bound to a dataset which should be always available... before i tried to "disable" the control but with no success.

going back to my msaccess apps comparison (i am planning to replace some of them, in my job, with lazarus apps) their form controls do not need a specific connection but each of them rely on the fact that the mdb file already has a "saved" connection/query to a table (internal or external), and you only have to specify the sql which populates them. If the sql is not valid, for whatever reason, the control is shown empty (which does not give you any hint of why, but neither show  any error to the user nor crashes the app)

i am not telling here that lazarus should mimic msaccess, which is much different and much less in many ways, i am just trying to find the best setup for converting my mind to a different world...  in the best way

i will make some more experience based on you suggestions, which i am grateful for, and then perhaps open new topics with new and mor especific questions...

Thanks, Marco

[taazz]

SQLQuery has both database and transaction because you do not need to set both of them to work setting the database property will use the databases default transaction no need to set the transaction component on the other hand each query can start its own transaction which might be needed especially if used to insert or update multiple tables in one go assuring that the data are saved all or none, for example subtracting an amount from your bank account and adding that amount to my account should make sure that both sql commands executed with out problems otherwise amounts will get lost between the cracks.

By removing the transaction property you actually limiting your choices as for the database there are some databases that do not require from the client library to explicitly start a transaction and that choice does not exists in the current library.

Please keep in mind I am not and never was part of the design team of the library or have ever being part of the decision making process so I'm only guessing that  Delphi compatibility played a role on the existing design.