Lookup fields do not return data.
lazarus / 16-Databases

----------------------------------------------------------------------
taazz · 2012-09-11 09:22

Any one else having problems with fklookup fields?

Having create one and place it in a DBGrid does not show any data.

I'm attaching a demo project. It requires firebird and connect it to
the employe database before activating the connection and the queries.

Thank you for any hints.

----------------------------------------------------------------------
ludob · 2012-09-11 11:58

2 problems:
- set stringfield1.LookupCache to True. If not using the LookupCache
then TField.CalcLookupValue uses FLookupDataSet.Lookup which is not
implemented in TBufDataset. fpc 2.7.1 has it implemented but it causes
other problems in this case.
- remove stringfield1.OnGetText. Commenting the contents out still
makes that OnGetText is called and the result is always an empty
string.

----------------------------------------------------------------------
taazz · 2012-09-11 12:40

Quote from: ludob on September 11, 2012, 01:58:47 pm2 problems:
- set stringfield1.LookupCache to True. If not using the LookupCache
then TField.CalcLookupValue uses FLookupDataSet.Lookup which is not
implemented in TBufDataset. fpc 2.7.1 has it implemented but it causes
other problems in this case.

OK. although I would like to know where exactly is the code that
decides to call the CalcLookupValue I've be trying to find almost all
day to day (on and off).

Quote from: ludob on September 11, 2012, 01:58:47 pm- remove
stringfield1.OnGetText. Commenting the contents out still makes that
OnGetText is called and the result is always an empty string.

Yeah that one I found it also, it makes no sense though if there is an
event handler then do not run the required code policy seems more like
a hack to speed things up instead of a rock solid behavior.

Oh well any shortcomings that I should be aware of ? eg refreshing the
lookupdataset would require an explicit refresh of the dblookupfield
cache?

thank you for your time greatly appreciated.

----------------------------------------------------------------------
ludob · 2012-09-11 13:28

QuoteOK. although I would like to know where exactly is the code that
decides to call the CalcLookupValue I've be trying to find almost all
day to day (on and off).Stack trace when entering a value in
SQLQuery1HEAD_DEPT:
Code: [Select]#0 TFIELD__CALCLOOKUPVALUE(<error reading variable>) at
.\fcl-db\src\base\fields.inc:617
#1 TDATASET__CALCULATEFIELDS(0x97508 '', <error reading variable>) at
.\fcl-db\src\base\dataset.inc:150
#2 TDATASET__GETCALCFIELDS(0x26df8c0 #168#248'm'#2#204#250'm'#2#1,
<error reading variable>) at .\fcl-db\src\base\dataset.inc:472
#3 TDATASET__EDIT(<error reading variable>) at .\fcl-
db\src\base\dataset.inc:1698
#4 TDATASET__INTERNALCANCEL(<error reading variable>) at .\fcl-
db\src\base\dataset.inc:934
#5 ?? at :0
#6 TCUSTOMGRID__EDITORKEYPRESS(0xa1a38, 49 '1', <error reading
variable>) at grids.pas:7598
TField.CalcLookupValue is where LookupCache is tested.

QuoteYeah that one I found it also, it makes no sense though if there
is an event handler then do not run the required code policy seems
more like a hack to speed things up instead of a rock solid
behavior.What do you suggest? That aText should contain the string
value as it would be displayed without the handler? The current
behavior is in lin with many event handlers. Assign an
TDBGrid.OnDrawColumnCell or a TForm.OnPaint and enter no code: nothing
will be drawn.
Quoteeg refreshing the lookupdataset would require an explicit refresh
of the dblookupfield cache?Yep. By calling
StringField1.RefreshLookupList;

Another known messy situation is that the lookupdataset is set active
when the binding is done between field and lookupdataset. This causes
some spurious activation of datasets when the form is streamed. Some
bugreports and forum threads discuss this behavior.

----------------------------------------------------------------------
taazz · 2012-09-11 14:23

Quote from: ludob on September 11, 2012, 03:28:52 pm
Stack trace when entering a value in SQLQuery1HEAD_DEPT:
Code: [Select]#0 TFIELD__CALCLOOKUPVALUE(<error reading variable>) at
.\fcl-db\src\base\fields.inc:617
#1 TDATASET__CALCULATEFIELDS(0x97508 '', <error reading variable>) at
.\fcl-db\src\base\dataset.inc:150
#2 TDATASET__GETCALCFIELDS(0x26df8c0 #168#248'm'#2#204#250'm'#2#1,
<error reading variable>) at .\fcl-db\src\base\dataset.inc:472
#3 TDATASET__EDIT(<error reading variable>) at .\fcl-
db\src\base\dataset.inc:1698
#4 TDATASET__INTERNALCANCEL(<error reading variable>) at .\fcl-
db\src\base\dataset.inc:934
#5 ?? at :0
#6 TCUSTOMGRID__EDITORKEYPRESS(0xa1a38, 49 '1', <error reading
variable>) at grids.pas:7598
TField.CalcLookupValue is where LookupCache is tested.

What do you suggest? That aText should contain the string value as it
would be displayed without the handler?

Code: [Select]function TField.GetDisplayText: String;
begin
  ///SetLength(Result, 0);
  GetText(Result, True);
  if Assigned(OnGetText) then
    OnGetText(Self, Result, True);
end;       

Quote from: ludob on September 11, 2012, 03:28:52 pmThe current
behavior is in line with many event handlers.
Assign an TDBGrid.OnDrawColumnCell or a TForm.OnPaint and enter no
code: nothing will be drawn.

although I disagree with this behavior it is understandable up to a
point. Windows solved this problem by allowing the callback procedure
to flag the message handled so it does not paint anything if it is
flagged or paint everything if it is not, but this is not comparable
to the problem at hand a draw procedure has many levels and  can't be
broken down to smaller steps (this is a design flow in my view btw) so
it is understandable to have an all or nothing policy including call
the callback before the default paint, to avoid screen flicker and
speed up the application responsiveness. Of course the paint is called
a few dozen times a second and is a lot slower than a simple
move/convert of a few bytes overall so it makes sense to cut off the
old for the sake of the new.

On data events on the other hand (especially on bufdataset which
already has the data in  memory) it is a design flow not to pass that
data to the user's event.There is no all or nothing and most of the
times some kind of check on the existing data would be required.

As it is now we have created a dangerous re-entrance situation think
of it like this the user writes the event checks the text variable
sees nothing and calls the displaytext property to get the text. That
of course forces its application in to a loop which eventually will
eat up all his stack and raise an exception.

This simple change will probably avoid most problems but hey you know
what Einstein said about stupidity and the universe no guaranties can
be made other than actively guarding against the re-entrance.

I hope I made my self clear of what I mean speed hack instead of
stable I didn't mean to say that the framework is not stable.

Quote from: ludob on September 11, 2012, 03:28:52 pmYep. By calling
StringField1.RefreshLookupList;
Noted for future reference. I'm going to check for a more generic
method first though and I'll report back.

Quote from: ludob on September 11, 2012, 03:28:52 pmAnother known
messy situation is that the lookupdataset is set active when the
binding is done between field and lookupdataset. This causes some
spurious activation of datasets when the form is streamed. Some
bugreports and forum threads discuss this behavior.

Oh right I think I remember one of those it was resent as well.
Noted. first open the lookup datasets then link them to the lookup
fields.

The good think is that everything is dynamic in my application even
field definition and creation so I can just move a line of code or two
up a few steps and this is no issue.

Thank you for your time as always you have been most helpful.

----------------------------------------------------------------------
ludob · 2012-09-11 15:06

QuoteOn data events on the other hand (especially on bufdataset which
already has the data in  memory) it is a design flow not to pass that
data to the user's event.OnGetText has Sender: TField as the first
parameter. So the user has direct access to the field in memory.
QuoteAs it is now we have created a dangerous re-entrance situation
think of it like this the user writes the event checks the text
variable sees nothing and calls the displaytext property to get the
text. That of course forces its application in to a loop which
eventually will eat up all his stack and raise an exception.

This simple change will probably avoid most problems but hey you know
what Einstein said about stupidity and the universe no guaranties can
be made other than actively guarding against the re-
entrance.http://www.freepascal.org/docs-
html/fcl/db/tfield.ongettext.html:
QuoteOnGetText is triggered whenever the TField.Text or
TField.DisplayText properties are read.Users accessing
TField.DisplayText in TField.OnGetText deserve to be punished

----------------------------------------------------------------------
codifies · 2012-09-13 11:19

I'm having this issue with dbgrid the drop down happens with the
correct contents but nothing is displayed in the cell

in
DBGrid1DrawColumnCell

column.field.asstring is always blank

I've turned lookup cache on anything else I need to do?

----------------------------------------------------------------------
taazz · 2012-09-13 11:51

As far as I know no there is nothing else you need to do. But be very
careful do not use the fields lookupdataset for anything else, the
code for lookup needs a lot of work it opens and closes the
lookupdataset on demand it assumes to much and its generally not
advised to have any other components than the lookup field depend on
it.

----------------------------------------------------------------------
ludob · 2012-09-13 11:52

Is your keyfields for the fpLookup field set to the correct field? If
the dropdown lists the correct values but column.field.asstring always
returns '' then probably you are looking up the wrong value.

----------------------------------------------------------------------
codifies · 2012-09-13 16:23

I couldn't say it now says can't do operation on a none active data
set when it runs
(doesn't say which one and they are both active...)

I'll have to start from scratch, but its obvious I'm not understanding
something as it all seems very fragile and easy to break inadvertently
- which is a real shame as I can see how flexible it has the potential
to be and how much work must have gone into it...

----------------------------------------------------------------------
Knipfty · 2012-09-13 16:27

chris_c,

post the code from the .lfr file.  In it will be the settings you are
using in the IDE.

----------------------------------------------------------------------
codifies · 2012-09-13 18:35

thanks but I scrapped it in the end...

----------------------------------------------------------------------
codifies · 2012-09-13 21:49

i must be a sucker for punishment I started from scratch and can't
even get as far as I got before...

no doubt something daft, I cant help but think if the look up field
settings were populated with valid entries for that property, people
would have a lot less problems!


did you mean this file
Code: [Select]object Form1: TForm1
  Left = 400
  Height = 360
  Top = 254
  Width = 658
  Caption = 'Form1'
  ClientHeight = 360
  ClientWidth = 658
  LCLVersion = '1.0.1.3'
  object DBGrid1: TDBGrid
    Left = 36
    Height = 214
    Top = 106
    Width = 576
    AutoFillColumns = True
    Color = clWindow
    Columns = <>
    DataSource = peopleDatasource
    TabOrder = 0
  end
  object MySQL51Connection1: TMySQL51Connection
    Connected = True
    LoginPrompt = False
    DatabaseName = 'mydb'
    KeepConnection = False
    Password = 'db123'
    Transaction = SQLTransaction1
    UserName = 'dbuser'
    HostName = 'localhost'
    LogEvents = []
    left = 88
    top = 16
  end
  object SQLTransaction1: TSQLTransaction
    Active = False
    Action = caNone
    Database = MySQL51Connection1
    left = 248
    top = 16
  end
  object peopleQuery: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    FieldDefs = <   
      item
        Name = 'peopleid'
        DataType = ftInteger
        Precision = -1
        Size = 0
      end   
      item
        Name = 'fktitleid'
        DataType = ftInteger
        Precision = -1
        Size = 0
      end   
      item
        Name = 'surname'
        DataType = ftString
        Precision = -1
        Size = 45
      end   
      item
        Name = 'forename'
        DataType = ftString
        Precision = -1
        Size = 45
      end   
      item
        Name = 'address'
        DataType = ftMemo
        Precision = -1
        Size = 0
      end>
    Active = True
    Database = MySQL51Connection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'select * from people;'
    )
    Params = <>
    left = 376
    top = 16
    object peopleQuerypeopleid: TLongintField
      DisplayWidth = 10
      FieldKind = fkData
      FieldName = 'peopleid'
      Index = 0
      LookupCache = False
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
      Visible = False
      OnChange = peopleQuerypeopleidChange
    end
    object peopleQueryfktitleid: TLongintField
      DisplayWidth = 10
      FieldKind = fkLookup
      FieldName = 'fktitleid'
      Index = 1
      KeyFields = 'fktitleid'
      LookupCache = True
      LookupDataSet = titleQuery
      LookupKeyFields = 'titleid'
      LookupResultField = 'title'
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
    end
    object peopleQueryforename: TStringField
      DisplayWidth = 45
      FieldKind = fkData
      FieldName = 'forename'
      Index = 2
      LookupCache = False
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
      Size = 45
    end
    object peopleQuerysurname: TStringField
      DisplayWidth = 45
      FieldKind = fkData
      FieldName = 'surname'
      Index = 3
      LookupCache = False
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
      Size = 45
    end
    object peopleQueryaddress: TMemoField
      DisplayWidth = 10
      FieldKind = fkData
      FieldName = 'address'
      Index = 4
      LookupCache = False
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
      BlobType = ftBlob
      Transliterate = False
    end
  end
  object titleQuery: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    FieldDefs = <>
    Database = MySQL51Connection1
    Transaction = SQLTransaction1
    SQL.Strings = (
      'select * from titles;'
    )
    Params = <>
    left = 483
    top = 16
  end
  object peopleDatasource: TDatasource
    DataSet = peopleQuery
    left = 584
    top = 16
  end
  object titleDatasource: TDatasource
    DataSet = titleQuery
    left = 111
    top = 82
  end
end

----------------------------------------------------------------------
taazz · 2012-09-13 22:45

Careful now, you need to have both the titleID and titleID lookup
field you can't select fktitleid as the key field you need an field
with fieldkind = fkData in the KeyFields property.

----------------------------------------------------------------------
codifies · 2012-09-14 00:57

not sure what you meant there please bare in mind what you see there
is the product of me trying just about every combination I could think
of and failing to understand the feedback from Lazarus.

people table has

peopleid <- AI PK
fktitleid <- foreign key to titles.titleid
other fields

titles table

titleid <- AI PK
title <- string to display instead of foreign key way displaying a
person record

----------------------------------------------------------------------
taazz · 2012-09-14 07:32

How many fields you have in the peopleQuery with the name fktitleid?

I can see 2 references 1 a fielddef reference of data type ftInteger
and 2) a Tfield Object with field kind fklookup. You can't have
1) 2 fields with the same name in any single Query object.
2) a lookup field that has its FieldKeys = Fieldname.
Also the keyfields property must have fields on the dataset that the
lookup field belongs to.

From the form definition you have shown it seems that the keyfields
property is not connected to an existing data field.

----------------------------------------------------------------------
codifies · 2012-09-14 07:41

theres only on fktitleid field, theres a query field def with the same
name but that happens automatically as with the other query (not
record) fields

So I'm really not following you ??

----------------------------------------------------------------------
taazz · 2012-09-14 07:49

I'm sorry my, english fu is not as good as it should be.

You have a field fktitleid, which is of fieldkind fklookup correct?

Which is the keyfield in peopleQuery that fktitleid should update with
data?

----------------------------------------------------------------------
codifies · 2012-09-14 07:57

no worries its better than anyother language of mine that English!

Code: [Select]    object peopleQueryfktitleid: TLongintField
      DisplayWidth = 10
      FieldKind = fkLookup
      FieldName = 'fktitleidISNTTHISTHEQUERYFIELDNAME??'
      Index = 1
      KeyFields = 'AAA'
      LookupCache = True
      LookupDataSet = titleQuery
      LookupKeyFields = 'BBB'
      LookupResultField = 'CCC'
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
    end
people table has

peopleid <- AI PK
fktitleid <- foreign key to titles.titleid
name
address
etcetc

titles table

titleid <- AI PK
title <- string to display instead of foreign key way displaying a
person record

what values should be in AAA, BBB, CCC

----------------------------------------------------------------------
taazz · 2012-09-14 08:29

OK then fkTitleID is a physical field (aka exists in database) which
you can not define as a lookup field.
You need to change it back to fkData and add a new field as the look
up.

----------------------------------------------------------------------
codifies · 2012-09-14 09:03

ah right! (thats, how its *supposed* to work!!!)

how ever when I try to make the people query active I get

peopleQuery : Field not found : "titleid"

which makes no sense at ALL !

does it mean the peopleQuery is failing because it can't find titleid
in the foreign table ???

the titles query IS active and when I run its query I DO get a titleid
field, the titleQuery does have a titleid field defined...

----------------------------------------------------------------------
codifies · 2012-09-14 09:18

Finally I'm back to the none working but not crashing and allowing the
query to be active condition of the previous project that I scrapped
PHEW!

please see the attachment

this will show a drop down with the correct selection of fields
but without a value in the cells

if I turn cache on I get an exception with a similar error to the one
mentioned earlier and the IDE exits....

----------------------------------------------------------------------
taazz · 2012-09-14 09:27

I have impression that you need to use persistent fields in both
queries but I need some time to test it later today.

----------------------------------------------------------------------
codifies · 2012-09-15 02:19

Quote from: taazz on September 14, 2012, 11:27:22 amI have impression
that you need to use persistent fields in both queries but I need some
time to test it later today.
if by persistent fields you mean that both queries have fielddefs then
yes they both do!

----------------------------------------------------------------------
taazz · 2012-09-15 02:55

Quote from: chris_c on September 15, 2012, 04:19:18 amQuote from:
taazz on September 14, 2012, 11:27:22 amI have impression that you
need to use persistent fields in both queries but I need some time to
test it later today.
if by persistent fields you mean that both queries have fielddefs then
yes they both do!

Sorry Chris it was a long night tonight didn't had the time to take a
closer look on your form.
Is it possible to send me an sql query to recreate the tables and a
few lines of data for testing?
As always my test will be done with firebird but I do not expect any
differences between the 2 databases.
I just need to have your fields in those 2 queries so you can just
replace the IBConnection with a mysql connection and see how things
work for you. Saturday to today I'll have more time to spend on the
issue.

----------------------------------------------------------------------
codifies · 2012-09-15 05:25

I've taken the liberty of attaching the whole project so you can see
the the problem, its just a single form with a single grid. (a mysql
dump is included)

Please don't misunderstand I'm not demanding you fix my project,
rather I have created (and recreated a number of times) this project
so I can understand DBGrid better, it is my intention to create a
tutorial but without complete understanding of this issue this isn't
possible!

I think there are a great number of people find this issue troublesome
and would greatly appreciate some kind of basic tutorial ...

really no .xz attachments ??

----------------------------------------------------------------------
taazz · 2012-09-15 11:35

I'm working on this as I type this. I have created the database and
I'm trying to identify the problem.
At first I thought that your problem was the
keyfield/listfield/listResultfield combination which was wrong then I
realized that they ended up that way after you tried to identify the
problem your self.

I am at the point where I see the exception in design time and trying
to find why it behaves like this, so hopefully it want be long now.

----------------------------------------------------------------------
codifies · 2012-09-15 14:17

oh I do hope you don't end up with the same blood stains on the wall
as mine....

It would be really nice to get to the bottom of this though and know
specifically what causes it and why, so we can work out what to do and
just as importantly what NOT to do!

----------------------------------------------------------------------
taazz · 2012-09-15 14:44

I am almost there (seen blood stains that is) there are a number of
really weird checks in all the wrong places inside the Bufdataset. In
one hand getting an error is better than silently failing to show data
on the other hand it shouldn't fail while connecting the dataset to
the database unless there is some kind of network or library error. I
am still trying to figure out where this error originates and not
being able to step in to sqldb code is a major pain in my back.
Building a debug ide as we speak so that might help to get things
going a bit faster.
NEVER MIND that failed miserably I need to re install everything.

----------------------------------------------------------------------
codifies · 2012-09-15 14:52

Thank for taking the time, I for one certainly appreciate it, I'm
determined to make good use of your work to make a really good wiki
tutorial so your effort will not be wasted!

----------------------------------------------------------------------
BigChimp · 2012-09-15 14:55

You perhaps only built a debug ide? The bufdataset unit is fpc so that
won't be included in a debug build.
You'll need to run something like make --OPT="-gw -gl" (not sure of
exact option name etc)

... or using fpcup to get a debug everything build for Laz SVN, with
FPC fixes 2.6.1:
Quotefpcup --fpcopt="-gw -gl" --lazopt="-gw -gl"Make sure you have
nothing in c:\development and that you have write permissions there.
However, it will happily exist side-by-side with existing Lazarus/FPC
installs

----------------------------------------------------------------------
taazz · 2012-09-15 15:05

ok, ok, downloaded fcup created the directory C:\developing I just
copy it there and run it?

I already have turtoisesvn installed is there something I should know
before sarting the process?

My main idea is use svn version for bugs/enhancements lazarus/fpc
specific tasks and keep a clean release installation too for my
current projects.

----------------------------------------------------------------------
BigChimp · 2012-09-15 15:21

c:\development
unless you want to fiddle around with options (see fpcup --help)
Yes, just put it there.

It's best if tortoisesvn's directory with svn.exe is in your path.
Otherwise no problem, it will download its own svn.exe but you won't
get the SVN revision number in your Lazarus/About screen, which can be
handy.

Good idea to keep 2 separate installs. That's the way I use it too.

----------------------------------------------------------------------
ludob · 2012-09-15 15:26

Had a look at the sample program and the KeyFields and LookupKeyFields
are mixed up. Here is the part of the lfm file relative to
StringField1 that is changed. Works with laz 1.1 fpc 2.7.1.

Code: [Select]    object StringField1: TStringField
      DisplayWidth = 45
      FieldKind = fkLookup
      FieldName = 'title'
      Index = 3
      KeyFields = 'fktitleid'
      LookupCache = True
      LookupDataSet = titleQuery
      LookupKeyFields = 'titleid'
      LookupResultField = 'title'
      ProviderFlags = [pfInUpdate, pfInWhere]
      ReadOnly = False
      Required = False
      Size = 45
    end

----------------------------------------------------------------------
codifies · 2012-09-15 15:36

I've already tried that way round as mentioned the example posted is
the result of trying multiple combinations of possibilities and is in
a known to be incorrect state

(notice the screen shot of the dialog I posted shows the same settings
you suggest...)

trying your settings when making the query active I see a dialog
saying

peopleQuery : Field not found : "titleid"

which is no surprise being as titleid is in the titles table....

If you're saying you have the project working can you do a DIFF
against the posted archive and tell us what else you have changed.

Its at least two of us are also seeing serious exceptions and IDE
crashes, as well as confusing error messages

----------------------------------------------------------------------
taazz · 2012-09-15 15:49

Quote from: BigChimp on September 15, 2012, 04:55:08 pmYou perhaps
only built a debug ide? The bufdataset unit is fpc so that won't be
included in a debug build.
You'll need to run something like make --OPT="-gw -gl" (not sure of
exact option name etc)

... or using fpcup to get a debug everything build for Laz SVN, with
FPC fixes 2.6.1:
Quotefpcup --fpcopt="-gw -gl" --lazopt="-gw -gl"Make sure you have
nothing in c:\development and that you have write permissions there.
However, it will happily exist side-by-side with existing Lazarus/FPC
installs

I S**T i didn't see your reply and started the download with out any
options typed in. Should I stop and start over or it will rebuild from
local data if I run it again?

----------------------------------------------------------------------
BigChimp · 2012-09-15 15:55

No prob. Wait for it to finish, and run it again with the options. It
will still take a while but as you've got the latest svn checkout, at
least you won't get any (or many) updated files as it does svn update
for both fpc+laz.

OT: I'd suggest laying off the coffee and switching to alcohol in
order to increase the relaxation factor

----------------------------------------------------------------------
ludob · 2012-09-15 16:02

Attached the project. I changed to TMysql50Connection since that is
the client I'm using. That doesn't impact dblookup. The other thing I
added is the FormOnCreate with the following 2 lines:
Code: [Select]  titleQuery.Open;
  peopleQuery.Open;
The order of opening queries when streaming a form is undetermined (it
is the order in the lfm but that can change when modifying a
property). Here I force the order. The other way around gives an error
message about an inactive dataset. I know, the auto opening of
lookupdatasets is a mess. There are several bug reports about that and
the maintainer has promised to get rid if that.

----------------------------------------------------------------------
codifies · 2012-09-15 16:14

ah right at least there is a known issue with auto opening lookups !
I'm not going insane (it just feels like it!)

alas after trying the open commands in form create I'm seeing....

and its not just me is it, that error message *is* confusing!

----------------------------------------------------------------------
ludob · 2012-09-15 16:18

Is that using the KeyFields and LookupKeyFields as I use them?

----------------------------------------------------------------------
codifies · 2012-09-15 16:37

I think it is the connector I couldn't get the 5.0 connector to work (
ubuntu's libmysqlclient15off_5.0.96-0ubuntu3_amd64.deb package is from
a distribution about a year and a half ago )

It only seemed to hang the compiled app when trying to use it

I changed to a 5.1 connector and changed the record names to lower
case (are they really case sensitive now a days ! eek! )

and guess what I get exactly the *same* exception

I can only conclude that the issue is either with the 5.1 connector or
the mysql driver itself

I'm going to try with postgress and see if I see the same issues....

----------------------------------------------------------------------
taazz · 2012-09-15 16:40

Quote from: BigChimp on September 15, 2012, 05:55:49 pmNo prob. Wait
for it to finish, and run it again with the options. It will still
take a while but as you've got the latest svn checkout, at least you
won't get any (or many) updated files as it does svn update for both
fpc+laz.

OT: I'd suggest laying off the coffee and switching to alcohol in
order to increase the relaxation factor

Well I didn't got a chance to react after I posted that message laptop
shutdown from heat.I just started over again its nice to know that it
will continue and not do everything again.

Coffee?? I don't drink coffee I have it pluged as an IV, leaves my
hands free for hair pooling.

----------------------------------------------------------------------
codifies · 2012-09-15 17:53

damn - same error message with postgres too.....

----------------------------------------------------------------------
taazz · 2012-09-15 23:41

After a day of building breaking installing uninstalling and generally
instability of lazarus building process I think I have finally managed
to make a breakthrough on the subject. I'm at a point where I can at
least show an assembly based call stack and get a meaningful message
out of this mess and the message leads to some the variants unit and
some kind of conversion process failing.

I'm still trying to get fpc to compile with debug info so I can get a
more meaningful call stack and see what is going on but I'm in a good
path so far. I don't know this builting from source process gives me
the creeps I'm uneasy all day long I'm never gonna get use to it. oh
well, the battle must go on, back to my building process.

----------------------------------------------------------------------
codifies · 2012-09-16 02:09

Stick with it!

I'm so desperate I'm considering booting windows for the first time in
months to see if its any different there! 

For me evething works even an uncached lookup just doesn't show cell
contents but does drop down and edit. Its only as soon as I enable
look up cache

I'm seeing this with 64bit Laz 1.0 and 1.1 with fpc 2.6
on Ubuntu 12.04 using MySQL 5.1 connector or postgres connector....

----------------------------------------------------------------------
taazz · 2012-09-16 09:12

Quote from: chris_c on September 16, 2012, 04:09:53 amStick with it!

I'm so desperate I'm considering booting windows for the first time in
months to see if its any different there! 

err, debugging it in windows 7 32bit so I would say no need to boot in
windows.

Quote from: chris_c on September 16, 2012, 04:09:53 amFor me evething
works even an uncached lookup just doesn't show cell contents but does
drop down and edit. Its only as soon as I enable look up cache

I'm seeing this with 64bit Laz 1.0 and 1.1 with fpc 2.6
on Ubuntu 12.04 using MySQL 5.1 connector or postgres connector....

I have a demo application that works with out a glitch with firebird
and my schema.
At first I thought that something is wrong with the database and the
message you get isn't related to the field at all, so I spend time
making sure every field has the correct data type, that no constrain
was bothering the process, etc. but with no lack.

After I gave up on setting up a correct debug environment yesterday I
copied everything (DB, SQLDB, etc) in the applications directory and
started debugging today. Didn't take much to find the following
backward line.

Code: [Select]procedure TField.RefreshLookupList;
var
  tmpActive: Boolean;
begin
  if not Assigned(FLookupDataSet) or (Length(FLookupKeyfields) = 0)
  or (Length(FLookupresultField) = 0) or (Length(FKeyFields) = 0) then
    Exit;
   
  tmpActive := FLookupDataSet.Active;
  try
    FLookupDataSet.Active := True;
    FFields.CheckFieldNames(FLookupKeyfields);//<-- this one makes no
sense.
..........
end;
FFields is the fields collection that the field belongs to. it is set
automatically when you call Dataset.fields.add(Myfield);
From a first look it should always link to the fields collection that
this field is part of, aka PeopleQuery in this occasion. 

It makes no sense to try to find any kind of lookup fields in that
collection. The problem is that my demo works and this makes no sense
at all. I made sure that by changing the above line to
Code: [Select]   
FLookupDataSet.Fields.CheckFieldNames(FLookupKeyfields);
everything works as expected.

The search goes on. I need to find out why my demo works now.

----------------------------------------------------------------------
ludob · 2012-09-16 09:21

Just tried your code on ubuntu 10.04 with the mysql 5.1 driver and
your code runs fine with laz1.1 and fpc 2.7.1 after
- setting KeyFields and LookupKeyFields as I specified before
- adding Code: [Select]  titleQuery.Open;
  peopleQuery.Open;
 to the FormCreate. Queries are not active in the OI!
- changing the table names to uppercase because of a bug introduced in
sqldb mysql a while ago

----------------------------------------------------------------------
taazz · 2012-09-16 10:33

Quote from: ludob on September 16, 2012, 11:21:40 amJust tried your
code on ubuntu 10.04 with the mysql 5.1 driver and your code runs fine
with laz1.1 and fpc 2.7.1.

Let me get this straight, the bug has been corrected in a newer
version of fpc and we should upgrade to fpc 2.7.1? Do we have any time
frame on when a new fpc will be released?

----------------------------------------------------------------------
taazz · 2012-09-16 11:19

And I found why my demo works. It seems that I had used a database
that comes with the server to show case my problem and in both the
queries I used there was a field name DEPT_NO had nothing to do with
the lookup but that was enough to avoid the problem and work as
expected.

Try to add a calculated field named TitleID in the peopleQuery that
does nothing and you will be able to see results too.

Checked the SVN too, this one has been corrected there.

----------------------------------------------------------------------
ludob · 2012-09-16 12:19

QuoteLet me get this straight, the bug has been corrected in a newer
version of fpc and we should upgrade to fpc 2.7.1? 2.6.1 is working
also. Not sure when this was solved.
QuoteChecked the SVN too, this one has been corrected there.Was that
the svn for lazarus or fpc? Where did you find the bug?

----------------------------------------------------------------------
taazz · 2012-09-16 12:31

Quote from: ludob on September 16, 2012, 02:19:10 pmQuoteChecked the
SVN too, this one has been corrected there.Was that the svn for
lazarus or fpc? Where did you find the bug?
I found the bug on a clean lazarus 1.0 installation.
I Spend all day yesterday familiarizing my self with fpcup downloading
and building from SVN. I checked my fpc\packages\fcl-db\src\base
folders that fpcup downloaded and it was fixed. I assume that it
hasn't re surfaced since.

I'm trying hard to stick with the released version so I can have some
kind of control over my development, so far it has been proved
pointless.

----------------------------------------------------------------------
codifies · 2012-09-16 17:54

Wow guys thanks for all your Sterling work! Esp.. to taazz, above and
beyond the call of duty!

Assuming I wish to keep as standard as possible and near to stable
release as possible how should I best upgrade...

OR should I just work round the issue for the time being with a dummy
field as taazz suggested and be patient and wait for stable...

Can't wait to get back to my laptop now....

----------------------------------------------------------------------
codifies · 2012-09-16 18:23

Didn't take me long to get to a laptop !

I've got it working now with taazz's dummy field work-a-round
note also that it is necessary to keep both queries active properties
to false and to
open them foreign table first order in the form create callback

thanks everyone! and taazz (*)

----------------------------------------------------------------------
taazz · 2012-09-16 20:56

Personally I have moved to a snapshot version that comes with fpc
2.6.1 for my daily work it has less bugs and a shorter release cycle.
I've updated to Lazarus-1.1-38674-fpc-2.6.1-20120916 today as well.
This will take care the problem for you.