ErrorMsg: Invalid variant type conversion

[ctiedeman]

I have the following table/view defined in Oracle 8.1.6:

CREATE TABLE HOUSE_ALLOWANCE (
UNIT_NO NUMBER(10, 0) NOT NULL,
DIST_ID VARCHAR2(5) NOT NULL,
HOUSE_ALLOWANCE_AMT NUMBER(10, 0) NOT NULL);

CREATE VIEW MAX_HOUSE_ALLOWANCE AS
SELECT UNIT_NO,
DIST_ID,
MAX(HOUSE_ALLOWANCE_AMT) AS HOUSE_ALLOWANCE_AMT
FROM HOUSE_ALLOWANCE
GROUP BY UNIT_NO, DIST_ID;

I drop a TOracleSession, TOracleDataSet, TDataSetProvider, and TClientDataSet onto a form and connect them all up. I put the following sql in the TOracleDataSet:

SELECT
Max_house_allowance.UNIT_NO,
Max_house_allowance.DIST_ID,
Max_house_allowance.HOUSE_ALLOWANCE_AMT
FROM MAX_HOUSE_ALLOWANCE Max_house_allowance

I set TOracleSession.Connected := True to connect to the database. When I set TClientDataSet.Active := True, I get an error message saying: "Invalid variant type conversion". If I set it to active a second time, it works as expected. It only happens when doing a MAX() or similar function. Is there a way to avoid opening the ClientDataSet twice?

 

[Marco Kalter]

Can you debug the server application and check where this conversion error occurs?

------------------
Marco Kalter
Allround Automations

 

[ctiedeman]

I stepped into the Provider.Data statement. During its call to TDataPacketWriter.AddIndexDefs() [in Provider.pas], it tries to make a call to TDataPacketWriter.PSGetIndexDefs([ixUnique]). It is somewhere within this method that it makes a call of VarChangeType(UnAssigned, Null, 8) [in System.pas] which is where the conversion error gets raised. If you call the Provider.Data method a second time, the PSGetIndexDefs call returns a valid TIndexDefs.

 

[ctiedeman]

I have found a work-around to the problem. If I create a synonym pointing to the view and use it in my sql instead, everything works as expected. It seems to be tied to the sql that DOA uses when querying meta data:

begin
begin
select user, table_name into :table_owner, :table_name
from sys.user_tables where table_name = :name;
exception when no_data_found then
begin
select table_owner, table_name into :table_owner, :table_name
from sys.user_synonyms where synonym_name = :name;
exception when no_data_found then
begin
select table_owner, table_name into :table_owner, :table_name
from sys.all_synonyms where owner = 'PUBLIC' and synonym_name = :name;
exception when no_data_found then
:table_owner := null; :table_name := null;
end;
end;
end;
end;

Does this mean that we should be using synonyms for all of our views, or should the above sql be checking the sys.user_views table as well?

 

[Marco Kalter]

It seems that the query should check user_views as well. I will look into this.

------------------
Marco Kalter
Allround Automations