Icon View Incident Report

Serious Serious
Reported By: Heiko Knuettel
Reported On: 7/28/2008
For: Version 2.01 Build 1
# 2723 Executing a Sensitive Query That Calls a Function in a Different Database Causes Incorrect Results

At application startup, I open a disk- and a memory database, and then copy the stored functions from the disk database to the memory database, so that I can use them later in the memory database context e.g. for a "create table as...".

That works fine with internal SQL/PSM functions.

But when using an external function, in this case flagset(value, flagno: Integer):Boolean, a simple function that just checks if a bit in a integer is set, I have some trouble.

When executing the query "select * from diskdb.table where flagset(flags, 2)=false" in the disk database context (DataBaseName := 'disk'), I get the correct result set.

But when executing the same query (with the diskdb qualifier) in the memory database context, I get an empty result set.

The external function is:

procedure TEDBExternalModule1.EDBExternalModuleRoutine(const RoutineName: String);
  if ansiuppercase(RoutineName)='FLAGSET' then
     Params.ParamByName('result').AsBoolean := 

function TEDBExternalModule1.flagset(intval, flag: Integer): Boolean;
  result := (intval and (1 shl flag))<>0;

EXTERNAL NAME "extmodule"

Comments Comments and Workarounds
The workaround is to generate an insensitive result set instead. Also, as of 2.01 B2, this type of query will cause an insensitive result set to be generated since sensitive result sets rely on all referenced objects being present in the database from which the table is being referenced. In this case, the table was from one database while the function was from a completely different database.

Resolution Resolution
Fixed Problem on 7/28/2008 in version 2.01 build 2

Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial