Hi,

I've migrated my DBISAM v3.30 database to ElevateDB 1.04 B5.
Below DDL statements are for same table in DBISAM and ELEVATEDB.

DBISAM
----------
CREATE TABLE IF NOT EXISTS "TEMP"
(
  "db_table_name" CHARACTER(30) NOT NULL,
  "level_num" INTEGER NOT NULL,
  "field_name" CHARACTER(30),
PRIMARY KEY ("db_table_name","level_num") COMPRESS NONE
LANGUAGE "ANSI Standard" SORT "Default Order"
);

ELEVATEDB 1.04 B5
-----------------------
CREATE TABLE "TEMP"
(
"db_table_name" VARCHAR(30) COLLATE "ANSI" NOT NULL,
"level_num" INTEGER NOT NULL,
"field_name" VARCHAR(30) COLLATE "ANSI",
CONSTRAINT "PK" PRIMARY KEY ("db_table_name", "level_num")
)

The column 'db_table_name' has table names all in UPPERCASE and fields 'level_name' and 'field_name' in LOWERCASE.

In Dbisam
------------
"SELECT DISTINCT field_name FROM TEMP" - returns distinct field_name.
"SELECT DISTINCT db_table_name FROM TEMP" - returns distinct db_table_name.

In ElevateDB
---------------
"SELECT DISTINCT field_name FROM TEMP" - returns distinct field_name.
"SELECT DISTINCT db_table_name FROM TEMP" - doesn't work. All values are returned. Why ?

What's the difference b/w Dbisam and ElevateDB ? In both DB's the Index has Case-Insensitive = 'No'.

<< In ElevateDB
---------------
"SELECT DISTINCT field_name FROM TEMP" - returns distinct field_name.
"SELECT DISTINCT db_table_name FROM TEMP" - doesn't work. All values are
returned. Why ?

What's the difference b/w Dbisam and ElevateDB ? In both DB's the Index has
Case-Insensitive = 'No'. >>

I think this is a bug in EDB 1.04. If you request an insensitive result set,
the results are correct.

AFAIK, a DISTINCT query should always return a static (insensitive) result
regardless of whether a sensitive result was requested or not.

Ole Willy Tuv

Praveen,

<< What's the difference b/w Dbisam and ElevateDB ? In both DB's the Index
has Case-Insensitive = 'No'. >>

It's a bug.  The workaround is to set the TEDBQuery.RequestSensitive
property to False before executing the query.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< AFAIK, a DISTINCT query should always return a static (insensitive)
result regardless of whether a sensitive result was requested or not. >>

Actually, it can return a sensitive result set if the SELECT columns cover
the entire primary key, which is what EDB is trying to do but screwed up
because it only partially-matched the primary key columns.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< It's a bug.  The workaround is to set the TEDBQuery.RequestSensitive
property to False before executing the query. >>

Thanks!!

--
Praveen
Elevate Software
www.elevatesoft.com

Tim,

<< Actually, it can return a sensitive result set if the SELECT columns cover
the entire primary key, which is what EDB is trying to do but screwed up
because it only partially-matched the primary key columns. >>

So, it's a bug ? When can we expect a fix ?

You mention setting TEDBQuery.Requestsensitive = False.
What if i'm using TEDBDataBase. ?

--
Praveen

Praveen,

<< So, it's a bug ? When can we expect a fix ? >>

A new build will be out by Wednesday.

<< You mention setting TEDBQuery.Requestsensitive = False. What if i'm using
TEDBDataBase. ? >>

You'll have to use a manually-created TEDBQuery component to work around
this issue.  You can pass it into the TEDBDatabase.Execute method:

http://www.elevatesoft.com/edb1d7_tedbdatabase_execute.htm

and it will be used instead by the Execute method.

--
Tim Young
Elevate Software
www.elevatesoft.com