Hi Team,

   D10.2.3, latest EDB.

   Simple form - EDBEngine, EDBSession, EDBDatabase, EDBQuery, Datasource, DBGrid, Navigator.

   Local mode.  SQL in query is as follows..

{sql}
select * from backorders
where JobNo = 999999999 and buscode = :BusCode
order by OrderNo, PartNo
{sql}

   EDBQuery has RequestSensitive = True.
   
   Problem - As soon as the Query is set to Active, the ReadOnly flag immediately sets, defeating the purpose of the
RequestSensitive.

   As there is only the Engine, Session & Database preceding it, what is causing it to set to ReadOnly????

Regards & TIA,
Ian

Oh?  I just discovered that if I take out the Order By statement it doesn't set the readonly when made active.
That's not much good to me or make sense.

On 10/22/2018 6:23 PM, Ian Branch wrote:
> Oh?  I just discovered that if I take out the Order By statement it doesn't set the readonly when made active.
> That's not much good to me or make sense.

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Result_Set_Cursor_Sensitivity

Further observation.
   The two fields for the Order BY - Orn=derNo & Part no are both VarChar & Nullable.
   Don't know if this has any bearing on the issue.
Ian

Raul wrote:

> On 10/22/2018 6:23 PM, Ian Branch wrote:
> > Oh?  I just discovered that if I take out the Order By statement it doesn't set the readonly when made active.
> > That's not much good to me or make sense.
>
> https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Result_Set_Cursor_Sensitivity

Thanks Raul,
   I had seen that and can't see any condition that isn't being met  for the table.  Unless of course I am missreading
some aspect.

   1)   DECLARE not relevant.
   2)   No DISTINCT.
   3)   No computed or aggregates.
   4)   No Group by.
   5)   The Order by is on two fields that have their own simple index.
   6)    No sub queries.

Regards,
Ian
   

Resolved..

Turns out the query doesn't like the Case Insensitive in the Index for OrderNo.
Removed the Case Insensitive and all good now.
Don't know if this is as it should be but at least I have my query working noe.

Regards,
Ian

On 10/22/2018 7:01 PM, Ian Branch wrote:
>    4)   No Group by.

It's usually the  "... there is an ORDER BY clause that minimally
matches the columns, and the collations defined for the columns" but
looks like you already got it solved.

Raul

Ian


What you, and possibly Raul, have missed is the fact that you're using a compound ORDER BY and there's no compound index to match it. The way ElevateDB works, unless it got changed when I wasn't looking, is that multiple single column indices do not match a compound ORDER BY.

I could cheat by looking at the copy of the data you sent me.

You need to create a new index eg

CREATE INDEX "ONoPNo"
ON "BackOrders"
("OrderNo" COLLATE "ANSI" ASC,"PartNo" COLLATE "ANSI_CI" ASC)

NB: the index must match column, collation & direction with the ORDER BY clause

Roy Lambert

Hi Roy,

   What I did have was, in your context)..

      ("OrderNo" COLLATE "ANSI_CI" ASC,"PartNo" COLLATE "ANSI" ASC)

   I tried just..

      ("OrderNo" COLLATE "ANSI_CI" ASC)

   And it still didn't like it.

   I then went to..

      ("OrderNo" COLLATE "ANSI" ASC)

   And it was happy.  Culminating in..

      ("OrderNo" COLLATE "ANSI" ASC,"PartNo" COLLATE "ANSI" ASC)

   It was the CI I had in the Index for OrderNo that wasn't in the column definition.  

   I didn't fully appreciate the intimate interaction between the two.  Something I need to check across this project now.

Regards,
Ian