error switching sql for sqlquery, missing "old query" fields...?!?
lazarus / 16-Databases

----------------------------------------------------------------------
m.ardito · 2013-01-29 15:26

hi all, still trying to get on with lazarus dbcontrols...

i have a form with
- dbcomponents: 1 connection, 1 transaction, 1 sqlquery and 1
datasource
- a pagecontrol with a few pages, each page has a DBLookupListBox

i would like to on-the-fly use the datasource and the sqlquery as its
dataset, changing the sql text for the page

at design time:
- sqlquery has no sql text assigned, no fields
- datasource has no dataset assigned
- all the DBLookupListBoxes have no listsource,listfield,keyfield
assigned: i try to assigne everything at runtime.

i implemented the switching code as follows below:

================================================================
procedure sync(cosa: string; pagina:TTabSheet;lista:TDBLookupListBox);
var
  q,lf,kf:string;
  fc:Integer;
begin
    q:='';
    case cosa of
       'tStati':        begin q:='select id, tipo from db_stati order
by tipo';         lf:='tipo';kf:='id'; end;
       'tServerTech':   begin q:='select id, tipo from db_server_tech
order by tipo';   lf:='tipo';kf:='id'; end;
       'tServerOs':     begin q:='select id, tipo from db_server_os
order by tipo';     lf:='tipo';kf:='id'; end;
       'tLinkTypes':    begin q:='select id, tipo from db_link_types
order by tipo';    lf:='tipo';kf:='id'; end;
       'tLinkDSN':      begin q:='select id, nome from db_link_dsn
order by nome';      lf:='nome';kf:='id'; end;
    end;

    if q > '' then
        begin
            with form1 do
              begin
                  with qTabelle do
                      begin
                          active:=false;
                          SQL.Text:=q;
                          active:=true;
                      end;
                  dsTabelle.DataSet:=qTabelle;
                  lista.ListSource:=dsTabelle;
                  lista.ListField:=lf;
                  lista.KeyField:=kf;

                  pTabelle.PageIndex:=pagina.PageIndex;
              end;

        end;
end;
================================================================

it works and keeps working until the sql has the same field names,
as soon as i assign and activate a query (the last one in the example)
that changes field names i get an

"EDatabaseError" with the reason "qTabelle: field not found 'tipo'"
(eg: starting from another page and switching to the last page)
"EDatabaseError" with the reason "qTabelle: field not found 'nome'"
(eg: starting from the last page and switching to another page)

i tried lots of things but can't see what i am missing...

anyone willing to help me to understand ?

Thanks, Marco

----------------------------------------------------------------------
m.ardito · 2013-01-29 22:31

i reply myself 

before reassigning sql text i have to clear keyfield and listfield for
other lists (well at least for the last used, but it's easy do this
for all the lists), like:
==============================
procedure unlist();
var
  i:Integer;
begin
  for i:=0 to form1 .ComponentCount-1 do
      if form1 .Components.ClassName=TDBLookupListBox.ClassName then
      begin
      (form1 .Components as TDBLookupListBox).KeyField:='';
      (form1 .Components as TDBLookupListBox).ListField:='';
      end;
end;
==============================

it apparently solves any edatabase error...

any comment appreciated, anyway :-)

Marco

----------------------------------------------------------------------
BigChimp · 2013-01-30 08:52

Hi Marco,

Just some remarks about your posts: you can surround code by code tags
(the # button on the editor when posting). This makes it easier to
read/copy etc.

Also, please mention your operating system and Lazarus version in case
that has an influence.

The solution you found seems a bit... strange but I understand:
changing the SQL means that in theory all databound controls can no
longer rely on fields being present.
Lazarus/FPC is not keeping track of whether the old field names
actually are the same or not.

----------------------------------------------------------------------
m.ardito · 2013-01-30 15:44

hi, yes, i will use the code tags, from now on... i just saw every
line indented and copy/paste keeps that formatting...
anyway :-)

about the code i am open to all suggestions: what you feel "strange"
about it?

i am trying to play around with laz/fpc, to find a workflow that
mimics what i have in some msaccess apps i am willing to convert to
lza/fpc, so i could make huge mistakes because i'm new here :-)

i have a crud-like form for now, each page enables crud operations on
different simple tables, and would like to use as few components as i
can.

up to now, i use
- 1 sqlquery/datasource pair for all of the "table records" lists,
each on a different "page", from which user could choose a single
record
- 1 sqlquery/datasource pair as dataset which i adapt to the "selected
record fields" of the active page, and this acts as datasource for all
data controls on that page for read,update,delete actions.

i'm trying to keep as simple as possible, but do you feel i could use
a better method?

Thanks, Marco

----------------------------------------------------------------------
BigChimp · 2013-01-30 15:56

Well, you could separate out the queries that have different field
names, or use SQL to rename the fields (select tipo as TYPENAME or
something like that).

The strange part refered really to the fact that your solution
worked/was necessary - so more a reflection on FPC/Lazarus than your
code.

Ok, I understand your situation: your using a master/detail setup
where you use the tabs to select a record then show/edit the related
detail records in a second query.

You could stuff your sqlquery connection transaction objects in a
separate data module... but AFAIU that is really more cosmetic (of
course you can share those across forms then which is not cosmetic at
all).

If you're just playing around, you could do Project=>Publish project
into an empty directory, zip up the resulting sources files and post
together with the DDL for your database (or use a sample database such
as Firebird's EMPLOYEE, Oracle's HR, MS SQL Northwind/Adventureworks
etc)...
I'm certainly willing to have a look to see if things can be improved
but I'm not that much of a GUI guru... at least I worked with MS
Access a (fairly long) time ago so I might have an idea what you're
trying to achieve.
Also, others may well be able to give advice.