I am using EDB 2.05 build6.

I have a Contacts table with a ContactRelationships table that allows users to define relationships between contacts.  The tables look something like  . . .

CREATE TABLE "Contacts"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"Name" VARCHAR(40) COLLATE "ANSI_CI" NOT NULL,
"FirstName" VARCHAR(24) COLLATE "ANSI_CI",
"FullName" VARCHAR(60) COLLATE "ANSI_CI" COMPUTED ALWAYS  . . .,
)

CREATE TABLE "ContactRelationships"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"ParentID" INTEGER NOT NULL,
"ChildID" INTEGER NOT NULL,
"RelationshipID" INTEGER NOT NULL
)

When the application adds a new Contact, a corresponding entry is also added to the ContactRelationships table.
EDB Manager shows the new rows on both the Contact table and ContactRelationships table.  Everything is working OK at this point.

However . . . . .

Using the application to add a new contact (say Brett Harding) and then run the following query, the ChildName is <null>.

SELECT
  parentID,
  (SELECT C1.FullName FROM Contacts C1 WHERE C1.ID = ParentID ) AS ParentName,
  ChildID,
  (SELECT C2.FullName FROM Contacts C2 WHERE C2.ID = ChildID ) AS ChildName
  FROM ContactRelationships
  WHERE ParentID = 1

ParentID: ParentName: ChildID:  ChildName
---------------------------------------------------------------
1: Harding, Richard: 111: Harding, Ruth
1: Harding, Richard: 112: Harding, Jessica
1: Harding, Richard: 113: <null>

If I run the query below, ChildName contains the correct name.


SELECT parentID, C1.FullName AS parentName, ChildID, C2.FullName as ChildName
   FROM ContactRelationships
   INNER JOIN Contacts C1 ON C1.ID = ContactRelationships.ParentID
   INNER JOIN Contacts C2 ON C2.ID = ContactRelationships.ChildID
   WHERE ParentID = 1

ParentID: ParentName: ChildID:  ChildName
---------------------------------------------------------------
1: Harding, Richard: 111: Harding, Ruth
1: Harding, Richard: 112: Harding, Jessica
1: Harding, Richard: 113: Harding, Brett

The Database is not in a Transaction.  If the application is terminated and restarted, the first query does return the new ChildName.  I wish to use the first query because it is a Sensitive query.

Can anyone suggestion why the 2 queries would be giving different results?

Many thanks.

Richard Harding

Richard


I can't see any reason using the information given (Tim or John Hay may be able to) and I suspect that it will be the data or something to do with the remaining part of the table definitions.

Out of interest what happens if you add another child. Does Brett Harding suddenly appear and the latest one not or....

Roy Lambert [Team Elevate]

Thank you Roy . . .
<<Out of interest what happens if you add another child. Does Brett Harding suddenly appear and the latest one not or....>>

If I add more children, the ChildNames of all the inserted rows are null.

Running both queries in EDB Manager returns the ChildNames.  Terminating the application and restarting the application returns all the ChildNames.

Richard Harding

Richard


>Thank you Roy . . .
><<Out of interest what happens if you add another child. Does Brett Harding suddenly appear and the latest one not or....>>
>
>If I add more children, the ChildNames of all the inserted rows are null.
>
>Running both queries in EDB Manager returns the ChildNames. Terminating the application and restarting the application returns all the ChildNames.

That makes me think the table isn't being refreshed somehow. Can you post the full table definitions and code you're using to insert the new children.

In the meantime, another test - change

(SELECT C2.FullName FROM Contacts C2 WHERE C2.ID = ChildID ) AS ChildName

to

(SELECT C2.FirstName FROM Contacts C2 WHERE C2.ID = ChildID ) AS ChildName

and see what happens.

Roy Lambert [Team Elevate]

Richard

> Running both queries in EDB Manager returns the ChildNames.  Terminating the application and restarting the
application returns all the ChildNames.

Just to be sure can I confirm the sequence is.

1.  Run EDB Manager
2.  Run your app
3.  Add a child
4.  Switch to EDBManager and run query
5.  Switch back to your app and run query

EDBManager produces correct result, your app produces null in fullname.

Are EDB Manager and your app both compiled with the same version?

Can you post source to a simple 1 form app which demonstrates this?

John

John


My current guess is its some sort of refresh problem with

"FullName" VARCHAR(60) COLLATE "ANSI_CI" COMPUTED ALWAYS  . . .,

which is why I want to see the full table structure. I don't remember seeing any problem of this nature being posted, but its possible.

Roy Lambert

Roy
>
> which is why I want to see the full table structure. I don't remember seeing any problem of this nature being posted,
but its possible.
>

Nor me - which is why I asked for the exact steps.  If it works in EDB Manager it does not look like a data issue.

John

Thank you John.

<<Just to be sure can I confirm the sequence is.

1.  Run EDB Manager
2.  Run your app
3.  Add a child
4.  Switch to EDBManager and run query
5.  Switch back to your app and run query

EDBManager produces correct result, your app produces null in fullname.  >>

Yes, the above is correct.  The app only produces null in the fullname for the newly inserted rows.  The query is created and executed in step 5 above.  The INNER JOIN query works but the query with the embedded SELECT statement in the SELECT clause produces the null in fullname.

<<Are EDB Manager and your app both compiled with the same version?>>

Yes, same version 2.05b6.

<<Can you post source to a simple 1 form app which demonstrates this?>>

Yes, I will work on this.

Thank you John and Roy.

<<  If it works in EDB Manager it does not look like a data issue. >>

Using EDB manager, I can see the data in the 2 tables and the 2 queries both show FullName for the Child.

If I extract the SELECT clause from the query - that is

SELECT C2.ID, C2.FullrstName FROM Contacts C2
   WHERE ID = :ChildID

then the FullName is shown correctly

Richard Harding

Roy and John

<<My current guess is its some sort of refresh problem with

"FullName" VARCHAR(60) COLLATE "ANSI_CI" COMPUTED ALWAYS  . . .,

>>

I thought that this was going to be the answer - that it was related to the COMPUTED field . .  but I tried replacing FullName with FirstName as you suggested and . . . same result - <null> appeared as the FirstName for the Child.

Instead of posting the full table definitions, I will create a new database and try to replicate the issue as simply as possible.

Thank you both for your assistance.

Richard Harding

Richard Harding