Regarding the rbMake component and Min/Max event handlers

After sending a small test project to Rubicon and getting a similar test back i think i understand what is going on.

The original “bug” description went:

When i assign event handlers to the TextLinks OnMaxIndex and OnMinIndex and run rbMake.Execute i get an exception from the RDBMS saying “empty statements are not allowed”. But when i refrain from assigning those events, the rbMake.Execute works fine.

OK, i think i know whats going on and what might be my (huge) misunderstanding.
This is all about rbMake (no other component).

When rbMake works (unassigned events)

The engine executes the queries to get min and max.

  1. SELECT MAX(ID) AS MAXINDEX FROM DOCUMENTS

  2. SELECT ID, SEARCH_META_TEXT, SEARCH_CONTENT_TEXT FROM DOCUMENTS WHERE ID = :Location

  3. SELECT MIN(ID) AS MININDEX FROM DOCUMENTS

  4. SELECT ID, SEARCH_META_TEXT, SEARCH_CONTENT_TEXT FROM DOCUMENTS WHERE ID = :Location

(I assume 2 and 4 is for checking that i provided the correct values.)

Then the engine creates the “main” query, i.e. the one that fetches rows with text to index:

SELECT ID, SEARCH_META_TEXT, SEARCH_CONTENT_TEXT
FROM DOCUMENTS
WHERE (ID > :Lowest) AND (ID <= :Highest) ORDER BY ID

When rmMake failes (assigned event) - in MY project

The engine skips the queries for checking out min and max (of course, that is the idea) and goes immediately to open the FDQueryText cursor.

So in TrbTextDataSetLink.Open (called from TrbCustomMake.Execute) the FDQueryText is activated.

  • In my dfm the query is empty - i get an exception.
  • In Rubicon dfm there is a query, it is activated (not fetched though).

But this activation is useless IMHO because then the engine creates the correct SQL including the WHERE clause.

Now i would like to know, Lajos (who sent the Rubicon test project), did you manually put the SQL in the dfm?

This is the SQL that is present in your projects dfm but not in mine:

SELECT ID, SEARCH_CONTENT_TEXT, SEARCH_META_TEXT FROM DOCUMENTS
ORDER BY ID

This is the query that is actually fetched (it is generated, or it might be parsed from the dfm SQL):

SELECT ID, SEARCH_CONTENT_TEXT, SEARCH_META_TEXT
FROM DOCUMENTS
WHERE (ID > :Lowest) AND (ID <= :Highest) ORDER BY ID

Same as above.

If the FDQueryText.SQL property is cleared, you will get the same error as i get, the RDBMS complains about an empty statement.

What i cannot fathom; in essence why i have been so confused about this:

  • using Min/Max events a query is needed in the FDQueryText before calling rbMake.
  • using Rubicons own code (unassigned events) the same SQL is 100% automatically created.

IMHO this is a discrepancy (i hope i chose the right word) that is very confusing. When starting the compiled demo (or if it was the few i could compile) i remember the SQL had “Do not set Rubicon will create the SQL for you” or similar.

In order to see the difference in the Rubicon test project, assign an event handler to the TextLinks FDQuerys OnCommandChanged and display the SQL.Text in a memo or similar.

To add to this in the Rubicon project you have selected “ID” in the fields editor. Why? Before you call rbMake you must reinit the FieldNames to remove the ID field or you will get “ambigious column name” exception or similar.

First off, in my Delphi 11 the checkboxes are so small, i thought the property editor it did not work. It does :slight_smile:

image

My eyesight is bad :slight_smile:

/D

1 Like

Hi Dany. This small “projectFirebird1” is one independent project to make Rubicon Index. In rbMakeTextFireDAC1 component I deleted events onMaxIndex and OnMinIndex. Rubicon have the possibilities to count MinIndex and MaxIndex. In unit rbDs are functions GetMaxIndex and getMinIndex. Those are 100% OK. Rubicon Make run perfectly and make about 69000 records in Index table. You have only to add paths and change the FDConnection parameters and the project run. There are all needed for R.Make, You have nothing to add. I’ll make this project also on dynamic way / Lajos
Please see the video: HREF Tools Corp Rubicon - Quick Start Guide - YouTube
In this video is the very easy way to create Rubicon Make and Search

There no need to do anything. Problem solved.

Welcome, Dany!

You are pointing to a very important sore spot. I agree. It needs improvement. I think we have 3 competing “this will be easiest for you…” directions.

Just to confirm, this is with Firebird 3 and FireDAC bridge K, correct? I see 'FD" prefixes.

There had been difficulty with these OnMinIndex/OnMaxIndex handlers for a long time. Until very recently, Make and Search samples did not work unless you assigned them. Lajos gets credit for fixing that. Now they can work automatically as you describe. This is useful for prototyping.

However that is not usually the fastest/best way for a production system. I would expect many people to have a faster way to know their maximum index — keeping in mind that this really means “max index in the next 12 months” if you want to avoid a lot of ongoing resizing. With Firebird SQL I might use something like GEN_ID( mygenerator, 0 ) and add 1000, if I thought 1000 more records would be added in the next year.

Noted! We did not adjust the PixelsPerInch in the property editor DFM files yet. I agree, it’s definitely too-tiny for Delphi 11 on a 4k monitor. Your screenshot is readable though :slight_smile:

There should not be any field lists in DFM files for the sample projects because those are all meant to sort themselves out at runtime. If you give me a filespec (path + filename), I will double check.

Ann

In my deployed projects i use IBObjects. But the MVP (minimal viable project) was made using FireDAC, correct and the tests was run using FB 3.0. I have identified the exact same inconsistencies using the IBO bridge.

This post is not about the MinMax values, neither about any performance gains. Please start another thread for that. This post is about the inconsistencies in processing when event handler are assigned vs. not assigned. Only that.

Ok. Roger that. I will try it myself and then confirm. In the queue…

Hi Dany

I am looking at OnMinIndex for another issue (SegmentSize) today.

Quick question. In your data, is the actual minimum index 1 or greater than 1 ?

I ran into trouble with the handler assigned while the minimum was > 1. I was using IBObjects for this, fwiw.

There is a difference, as you said, between having the handler vs no handler.

procedure TrbCustomTextIBOLink.Lowest;   // implemented per-bridge

    if Assigned(FOnMinIndex) then
      if GotoNearestLocation(MinLocation) then
      else
        RaiseRubiconError(rbeBlockSizeTooSmall)
    else
      GotoLocation(MinLocation);

Basically GotoNearestLocation(1) fails when the actual lowest PK is > 1 and that leads to the dreaded BlockSizeTooSmall exception.

I’d be happy to hear from anyone else who has traced into this and has an opinion on whether GotoNearestLocation should fail in this situation.

Ann

Tricky, i do not have the sources at hand. My issue was the fact that when rbMake had events assigned, a query was activated but never used (it would later be overwritten). But that extra activation forces users to have a valid SQL in that query. So what i stumbled upon should show in the beginning of an rbMake.Execute. That had the same behaviour for FireDAC and IBObjects bridges.

Hope this helps!