I have a table structured like this:

ref (integer) (value generated by a sequence) (also Primary Key) par_ref (integer)
name (varchar)
<other fields>

I am using it as a two level look-up list of values. Top level entries have a par_ref of 0, next level entries have a par_ref that points to the ref of a top_level entry. The app does not allow further levels (at this stage).

The master SQLQuery that has something like:

select * from mytable where par_ref = 0 order by name;

Works fine, (including updating with my own update queries)

The slave query has:

select * from  mytable where par_ref = :PRef order by name;

I have an on_scroll event on the master table that sets the value of the PRef parameter. (I asked a question about this previously)

This also works ... except that it returns rubbish values for 'ref' - 9-digit numbers instead of numbers like 6 or 8 that they should be. (Absolutely no way they are real numbers from wrong records). This means, of course, that in the update query, (update ... where ref = :ref) nothing happens, because no records match the ref parameter value. However, if I replace the :PRef by a real number (eg 5), I get sensible ref values, so it seems to be associated with the parameter. Yet the parameter value DOES select the correct records, so it must be basically working.

I have tried tracing this through (thanks for the help on getting that to work) but can't find anything wrong. (Most of the values I really want to check are properties, so I can't get their values). Does anyone have any ideas ?

Another question (sort of related)

The only way I have been able to get the query to 'read' the new parameter value is to close and reopen the query. Should this be so ? This means it unprepares and then re-prepares the (same) query, reallocates all the fields... Is there not some way of just re-executing the same prepared query with the new bound value(s) ?

(I guess it means I could work around my problem by just modifying the sql text each time, as I would have no additional overhead, but it seems the "wrong way" to go about it!)

thanks,
John

On Thursday 12 July 2007 16.18, John wrote:
[...]

MSEgui has the component "tfieldparamlink" to implement master detail relationship. In SVN trunk I just modified the tmsesqldb.refresh procedure not to unprepare the SQL statement.
If you need fast access to the lookup values you can use a "tdblookupbuffer".

Martin

Maybe a stupid question, but:

SQLDB has this already.
So, why don't you help fixing SQLDB instead, and use that in MSEgui ? This way everyone benefits.

This is a lot of silly code duplication for no good reason...

Michael.

Ups, SQLDB has a lookupbuffer component like MSEgui and does not unprepare the SQL statement on tdataset.refresh? Sorry I didn't know that. Are you sure that TSQLQuery implements internalrefresh? I spent about 150-200 hours to find, report and fix bugs in the FPC DB area BTW. :-)

Martin

No, I am not. This is the whole point: sqldb and msesqldb - which is a copy, after all - would better be merged and maintained as one. We would double the expertise.

I spent about 150-200 hours to find, report and fix bugs in the FPC DB area BTW. :-)

Which I appreciate: There are indeed 15 bugs in Mantis about this. (spending on average 10-13 hours per bug is a lot)

But it doesn't explain why you decided to fork the code, when I see no good reason for this:
- The user base for each set of components gets smaller.
- People don't know what to use.
- The maintainers are divided over too much code.
So, once more, I propose to 'merge' whatever you need so we can develop a decent set of components, suitable for all.

What do you think ?

Michael.

Yes it was a hard time. :-)
The worst (a spurious SIGSEGV in TBufDataset) I hunted a whole week. Because MSEgui must work with the latest stable FPC version I had to find workarounds for every bug, a very time consuming process.
If I count right, I posted 114 FPC reports up to now, some of them in the pre Mantis era. Contrary to Borland where I reported many bugs too and none of them was fixed at the time I abandoned Delphi/Kylix, the FPC team fixed most of the bugs in reasonable time. The fastest fix was in 15 minutes!

The main reason is time. I found out that I need less development time if I write most of the library my self from scratch instead to use existing base components for my "exotic" needs.
Another big problem is the fast release cycle of MSEide+MSEgui compared with FPC. I need to copy up to date FCL files into the MSEgui distribution anyway to build a working system, so the step to do a fork is small. The smaller user base is no problem for me, I don't need to sell MSEide+MSEgui and the user base of FPC-FCL is so much bigger than the user base of MSEgui that the move of users from FCL to MSEgui should be no problem for FPC.

So, once more, I propose to 'merge' whatever you need so we can develop a decent set of components, suitable for all.

The most questionable component in FCL-DB is TBufDataset <-> tmsebufdataset. I fear the needs of FCL and MSEgui are too different to let a merge be possible. Some needs of MSEgui are out-of-scope of the ordinary FPC user and the FPC developers (fast widestrings on all platforms comes into mind).

What is the result of your discussion with Bram and Joost of the matter to merge FCL-DB and MSEgui ?

Martin

45 in Mantis, to be exact. 15 of which are database bugs.

Contrary to Borland where I reported many bugs too and none of them was fixed at the time I abandoned Delphi/Kylix, the FPC team fixed most of the bugs in reasonable time. The fastest fix was in 15 minutes!

We do our best :)

As for 'reasonable time': this always depends on the needs of the user (you) and the available time/estimated seriousness of the FPC maintainers: it is perfectly normal that there is a difference between the appreciation of the 2 involved parties :-)

To a soldier, his own death or well-being is a serious matter, but the general needs to consider all soldiers :-)

Yes and no. MSEGui users are FPC users, obviously, but we would of course appreciate it if the benefits of some of your work are available to more people.

As for the time cycle: Like I said:

After 2.2, we plan to start using the package system. At that time, the release cycles of the compiler & rtl are independent of the release cycles of the packages and FCL. That should solve your release problem: I have no problem with discussing with you the needs of the FCL release cycles.

I am aware of the widestrings difficulty. However, I am sure this can be solved.

I discussed this with Joost, and we decided that the issue of WideStrings in TBufDataset and descendents can be solved very easily with 1 extra property, which would make it transparant for MSEGui users and other users. No need to change any existing code.

What is the result of your discussion with Bram and Joost of the matter to merge FCL-DB and MSEgui ?

Well, 4 things were identified. As you correctly state, the biggest issue is TBufDataset and it's partner tmsebufdataset:

- The widestring issue. We do not see this as a problem, as it can be solved very easily.

- The structure of the memory buffer is another matter. You do not store the string fields in the buffer itself, rather the pointer is stored in the buffer. Joost tried something similar once, but dropped the issue, but in principle he likes the idea. I looked at the matter now, and I am not convinced that this is the optimal solution. According to me, this way of doing will use a lot more memory and will be slower due to additional memory manager calls. So we decided that Joost will do some benchmarking before we make any proposals.

- Blobs are handled differently, but a lot has changed in sqldb since you forked the code. We don't think this will be a problem.

- The use of RECNO as the bookmark for the cursor. This issue can be handled on the index level.

In the process, I noticed you have a sqlite3conn descendent of TSQLConnection, so I changed the sqlite3 unit so it can be loaded dynamically, and then I made a copy of the sqlite3connection component and completely reworked it so it is independent of msegui, and so it conforms more to the coding style of the rest of the FCL, and which has also been tested by Joost. It's in subversion; Obviously, your name is duly referenced as the original implementor.

All in all, I'm fairly confident that we can merge most if not all of the code to everyone's satisfaction.

Does all this sound reasonable to you ?

Michael.

On Saturday 14 July 2007 16.26, Michael Van Canneyt wrote: [...]

[...]

Sounds good. :-)
I am curious about the new packages system and the buffer benchmark tests of Joost.

Martin

Even if you do TDataset.Close; TDataset.Open, it doesn't do an unprepare and a prepare.

Are you sure that TSQLQuery implements internalrefresh?

No it doesn't. It doesn't need it. Since the calls to the connections are splitted up in several parts. Only those that are really necessary are called. So even on a normal refresh, prepare and unprepare aren't called.

I spent about 150-200 hours to find, report and fix bugs in the FPC DB area BTW. :-)

I remember there were some difficult ones, yes.

Joost

Can you specify those? Maybe we can work something out.

I have a question about the widestrings. I thought that tmsebufdataset handles all strings as widestrings. But I can't find any reference to that in the code. It would be silly anyways: from the db-engine you'll get 8-bytes based strings. And a ftString fields's .asString method will return a normal ansistring. The same holds for your code.

Is this right? I mean:
1) tmsebufdataset doesn't store all the string-types in it's internal buffers as widestrings?
2) No conversion from/to widestring takes place?

Only if a field is defined as a nvarchar on the db-engine it is handled as a widestring? (ftWideString)

Because, then there's actually no difference?

Joost.

Joost van der Sluis wrote

Can you specify those? Maybe we can work something out.

MSEgui needs an reference counted widestring type on win32 to be as fast as possible.
Another old wish: GetInterface should work for corba style interfaces (Mantis 6036).

I needed to use my own TField descendants because I can not modify db.pp . See lib/common/msedb.pas, especially tmsestringfield, TField.GetClassDesc does not workfor them BTW.
The strings are converted from utf8 or current locale encoding to UCS2 after fetching the data from database and to utf8 or current locale encoding before sending data to the database in order the string encoding needs not to be converted while displaying and editing data in MSEgui. I can say that this concept and the whole MSEide+MSEgui system turned out to be very successful in commercial database applications.

Martin

This is not something decided on the TDataset level, that's an RTL issue.

Another old wish: GetInterface should work for corba style interfaces (Mantis 6036).

Again, an RTL issue.

Since Delphi does not have it, and is equally succesful, I doubt this is the reason ? The reason is IMHO that it 'simply works' :-)

Michael.

It's very difficult to see what you do in msedb.pas. That's because it's (guess) about 80% fpc code. But then renamed and refactored. With some minor changes. And a few additions, offcourse. But it's diffucult to see what you really do, without digging in the code for hours.

The strings are converted from utf8 or current locale encoding to UCS2 after fetching the data from database and to utf8 or current locale encoding before sending data to the database in order the string encoding needs not to be converted while displaying and editing data in MSEgui.

Ok, but a normal 'select * from table' generates a TDataset with regular TStringFields. And then no concersion takes place? Only if you specificly decide to use TMseStringField?

Thus if we add a poperty to a TSQLQuery to map all strings to a ftWideString. And then add a conversion-option so that the strings are converted when they are fetched you're done. And you can remove your bufdataset and a lot of other code-duplication?

I can say that this concept and the whole MSEide+MSEgui system turned out to be very successful in commercial database applications.

And it could be even better, of we cooperate more, and stop duplicating code. I've seen already a few bugs in your code, which are already solved in fpc. And the other way around. That's madness. So I want to find out what it is that you really need... See if we can implement that in the db-unit. That could be of use for others also.

Joost.

tmsebufdataset overrides getfieldclass:

  function  getfieldclass(fieldtype: tfieldtype): tfieldclass; override;

If we merge things, Martin can (and should) keep TMSESQLQuery as a descendent of TSQLQuery where he overrides getfieldclass so his own field types are used. That's the beauty of OOP :-)

Michael.

Martin Schreiber wrote

I needed to use my own TField descendants because I can not modify db.pp .

Huh? Why can't you? You did change bufdataset and sqldb. Which are also part of the same db-package?

Also for Delphi that's not a problem.

There's no difference between changing db and changing the parts you did?

And there should never be a need to change them, if they are designed well. That holds for the db unit and the rest of fcl-db. And if you find something that is impossible using the fcl-db package, you should mention that and we can look how we can fix fcl-db so that it can do what you want.

The db-unit, and in fpc the whole fcl-db package is mean to be a general interface to all sorts of applications...

Ask the people with non Latin character sets.
Suggestion: use MSEide+MSEgui and you will experience the difference! ;-)

Martin

Because of binary compatibility with other TDataset descendants. You are right, it would be an good idea to flush the whole Delphi ballast and start from scratch.

Martin

I would try, if I could get it to compile. It does not compile on 64-bit nor on 32-bit.

Michael.

The problem with that is that there are millions of components out there which would no longer work if we consequently did that...

Michael.

Binary compitibility? At the TDataset level? That doesn't exist. What do you mean?

You are right, it would be an good idea to flush the whole Delphi ballast and start from scratch.

That could be an good idea, yes. But that's not what you did with sqldb. You copied the code, changed is somewhat, but your code has no advantage over the original. There is only the disadvantage of the maintainability now. Because you and me are doing double work while fixing bugs and adding new features.

Joost.

With a modified db.pp it is difficult to combine a TDBF and a tmsesqlquery without recompiling the RTL. MSEgui must work with the default FPC 2.0.4 installation.

Martin

Here is the complete list:

1 0005085: Error in libc header transcription.
2 0005084: Error in pwidechar pointerarithmetic
3 0005094: AV in dynamic array assignment
4 0005087: Errors in libc header transcription.
5 0005095: Error in windows unit
6 0005096: Streaming of widestrings and widechars does not work. 7 0005113: {$if false} uncorrect
8 0005112: tobject.afterconstruction not called
9 0005154: Stack error in with statement
10 0005188: Abstract error in private section
11 0005193: VMT error in private section (related to 3179) 12 0005194: Error in set procedure parameter
13 0005199: Typeinfo() does not work with classes
14 0005206: Error in reference counting
15 0005228: Long strings as out proc parameters not finalized 16 0005331: Wrong type definition for wchar_t
17 0005332: Memory leak in copy function
18 0005341: No error message in interface conversion 19 0005346: Checkpointer error in open array
20 0005347: Function called twice in open array
21 0005371: Streaming of widestrings
22 0005561: Internal error 9999
23 0005563: Error in TBinaryObjectReader.SkipValue 24 0005578: Objecttext format of wide strings
25 0005565: Local directive CHECKPOINTER not documented 26 0005562: AV in copy function
27 0005564: Checkpointer error by string -> pchar conversion 28 0005636: Inline not supported in objecttext
29 0005643: No check for data and bss in CheckPointer procedure 30 0005655: csSubComponent not supported
31 0005716: Memory leak in dynamic array
32 0005727: Object text format of widestrings
33 0005772: AV in copy function
34 0005780: Missed cdecl in libc.sigaction
35 0005824: Error in win32 header translation
36 0005825: Accessing protected procedures in with statement 37 0006378: AV in TDatabase
38 0006035: AV by interface access
39 0006036: getinterface for corba style interfaces 40 0006034: Missing win32 procedure header
41 0006037: RTTI for interfaces
42 0006690: RTTI for inherited interfaces
43 0006060: Widestrings on Windows
44 0006065: AV in with statement
45 0006123: Overloaded functions
46 0006127: Error in widestring constant
47 0006168: SysTryResizeMem and heaptrace
48 0006205: Typeinfos are not unique
49 0006214: Widestring default procedure parameters 50 0006217: Missing Win32 procedure header
51 0006280: pthread_cond_t for NPTL
52 0006336: Multi threading on win32
53 0006346: Memory leak in registerinitcomponenthandler 54 0006706: Properties with nodefault and stored true options 55 0006427: FloatToStr with 1e100
56 0006426: ObjectBinaryToText with empty strings
57 0006468: Win32CompareWideString on windows98
58 0006865: Compiler crash with -Fcutf8
59 0006873: cwstring and threads
60 0006881: Dataevents in TDataset.MoveBy
61 0006882: Record insert at eof in TSQLQuery
62 0006883: Compile error without -B
63 0006650: Optimization and signed/unsigned comparison 64 0006889: AV in TSQLQuery.Cancel
65 0006892: AV in TSQLQuery.RecNo
66 0006893: Invalid TDatalink.FirstRecord
67 0006897: TSQLQuery.First returns wrong data
68 0006898: AV in TSQLQuery
69 0006899: TDataset.Insert shows record at wrong position 70 0006909: Property option stored false
71 0006919: AV in TBufDataset
72 0006931: Error in Libc header
73 0006938: AV inTBufDataset.CancelUpdates
74 0006988: Twriter.WriteDescendent with component properties 75 0007006: AV in fpc_widestr_inc_ref
76 0007007: Wrong TDataLink.ActiveRecord after append-cancel 77 0007048: AV in DisposeWideString
78 0007049: Memory error with wide strings
79 0007050: Error 204 in heaptrc.pp
80 0007052: Widechars are not streamed
81 0007144: AV in TSQLQuery
82 0007157: InternalCalcFields are not implemented 83 0007266: TBufDataset crash with empty table
84 0007267: TBufDataset crash with one row table
85 0007269: TField.as* properties do not raise exception 86 0007294: TBufDataset does not fire OnCalcFields 87 0007295: TBufDataset in dsCalcFields state
88 0007323: Invalid TBufDAtaset state after ApplyUpdates 89 0007324: Memory leak in TBufDataset ApplyUpdates 90 0007366: Wrong field values in TBufDataset
91 0007367: TSQLQuery does not call BindFields
92 0007446: Overloading with open array of integer parameter 93 0007565: TSQLQuery does not check pfInUpdate
94 0007654: dovarop and varCurrency
95 0007813: ObjectBinaryToText uses localized float strings 96 0008103: Memory error in cwstring
97 0008104: Non ASCII source code
98 0008140: Overloaded functions in interfaces
99 0008245: ObjectBinaryToText and apostrophe in string property 100 0008286: Type of TLargeintField.Value
101 0008386: Quoted decimal separator in FormatFloat 102 0008388: Single quoted string in FormatFloat
103 0008477: Potential memory error in TStringField.SetAsString 104 0008480: Bug in fpc_WideStr_Assign and fpc_WideStr_Incr_Ref 105 0008481: Bug in widestring assignment
106 0008482: TComponent.Name accepts any character 107 0009093: TfieldDef.Name not published
108 0009094: TFieldDef.DisplayName not published
109 0009115: TComponent.Name can not be set to ''
110 0009170: Erroneous finalizing of const widestring array 111 0009179: Error by record copy
112 0009187: win32 widestring copy error
113 0009190: win32 widestring parameter copy error 114 0009242: Debugging broken on win32 with PII
115 0009309: Wrong high(<openarray>) result

It seems I counted right, 115 is from today. :-)

Martin

According to

http://www.freepascal.org/mantis/summary_page.php

You have only 45, of which 2 are not yet fixed.

But that page doesn't show who reported bugs in the old bugtracker. Which explains the difference, I suppose.

Michael.