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