Hi everyone,

Not sure if anyone else has seen this, but here's the problem: I am using LLBLGEN throughout the system just fine, but for one collection class I get no records loaded, even though the generated SQL returns 4 results in a query designer.

This is the code:

ISortExpression sort = new SortExpression();
sort.Add(WithdrawReasonFields.WithdrawReason | SortOperator.Ascending);

WithdrawReasonCollection withdrawReasonCollection = new WithdrawReasonCollection();
withdrawReasonCollection.GetMulti(null, 0, sort);

Debug.WriteLine(String.Format("Withdrawal reason count: {0}.", withdrawReasonCollection.Count));
Debug.Assert(withdrawReasonCollection.Count > 0);

This is the generated output trace:

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT "VCMS"."WITHDRAWREASON"."ID" AS "Id", "VCMS"."WITHDRAWREASON"."WITHDRAWREASON" AS "WithdrawReason" FROM "VCMS"."WITHDRAWREASON" ORDER BY "VCMS"."WITHDRAWREASON"."WITHDRAWREASON" ASC
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Withdrawal reason count: 0.

And this is the result of the same query on the database, using the same login credentials as the application, through the Oracle SQL Developer Client:

SELECT Cast("VCMS"."WITHDRAWREASON"."ID" as char(32)) AS "Id", 
  "VCMS"."WITHDRAWREASON"."WITHDRAWREASON" AS "WithdrawReason" 
FROM "VCMS"."WITHDRAWREASON"
order by "VCMS"."WITHDRAWREASON"."WITHDRAWREASON" ASC

Id                             WithdrawReason                                               
-------------------------------- -----------------------------------------------------------------
5FBE4F5A4495914184884E3D4630F98A    Are unable to comply ¿ did not consider flexibility.
8A8E987D641B4D43AA724E6F7704B637    Bargaining Council has jurisdiction.
993BE41563CE664DBF6C62340FB1B853    Needs are no longer there - Shutdowns are cancelled or postponed.
6D1289C6DE9B8E43BC0D4793F4439BDF    Other legislation is applicable.

(Apologies for the formatting of the output, cannot seem to get it right, I also added the cast to the ID to make it readable).

I am not sure if this is related to the issue I logged here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6978, in particular this quote:

Otis wrote:

It indeed should use F0, F1 etc. If it doesn't, it can't read the row back into an entity anyway. Strange.

Using LLBLGEN Pro v1.0.2005.1 Final, June 19th 2006. Using Self-Servicing. Using VS 2005, ASP.NET 2. Using Oracle 10g Data Provider.

Thanks, Brian Johnson

Does this happen with other entityCollections (with other database tables) or only specific to this one?

Just a paranoid check, would you also use the GetDBCount() method to check the number of records in the database?

Hi Walaa,

New code:

ISortExpression sort = new SortExpression();
sort.Add(WithdrawReasonFields.WithdrawReason | SortOperator.Ascending);

WithdrawReasonCollection withdrawReasonCollection = new WithdrawReasonCollection();
withdrawReasonCollection.GetMulti(null, 0, sort);

Debug.WriteLine(String.Format("Withdrawal reason count: {0}.", withdrawReasonCollection.GetDbCount()));
Debug.Assert(withdrawReasonCollection.GetDbCount() > 0);

New Output:

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT "VCMS"."WITHDRAWREASON"."ID" AS "Id", "VCMS"."WITHDRAWREASON"."WITHDRAWREASON" AS "WithdrawReason" FROM "VCMS"."WITHDRAWREASON" ORDER BY "VCMS"."WITHDRAWREASON"."WITHDRAWREASON" ASC
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreateRowCountDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT "VCMS"."WITHDRAWREASON"."ID" AS "Id", "VCMS"."WITHDRAWREASON"."WITHDRAWREASON" AS "WithdrawReason" FROM "VCMS"."WITHDRAWREASON"
Method Exit: CreateSelectDQ
Generated Sql query: 
    Query: SELECT COUNT(*) AS NumberOfRows FROM (SELECT "VCMS"."WITHDRAWREASON"."ID" AS "Id", "VCMS"."WITHDRAWREASON"."WITHDRAWREASON" AS "WithdrawReason" FROM "VCMS"."WITHDRAWREASON") TmpResult
Method Exit: CreateRowCountDQ
Withdrawal reason count: 0.

This only seems to happen on this collection. Others seem fine so far. Could the 'Id' property name be causing any problems? This is the only entity where Id was used.

I have also noticed that the GetDbCount() of this collection produces 2 queries, which seems strange:

Method Enter: CreateRowCountDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT "VCMS"."WITHDRAWREASON"."ID" AS "Id", "VCMS"."WITHDRAWREASON"."WITHDRAWREASON" AS "WithdrawReason" FROM "VCMS"."WITHDRAWREASON"
Method Exit: CreateSelectDQ
Generated Sql query: 
    Query: SELECT COUNT(*) AS NumberOfRows FROM (SELECT "VCMS"."WITHDRAWREASON"."ID" AS "Id", "VCMS"."WITHDRAWREASON"."WITHDRAWREASON" AS "WithdrawReason" FROM "VCMS"."WITHDRAWREASON") TmpResult
Method Exit: CreateRowCountDQ

Thanks, Brian Johnson

You've defined your PK as 'RAW'. RAW is a type on Oracle that's not that common. Please change that type if you can, e.g. CHAR(16) with binary data.

That the count also results in 0 rows is odd though.

Otis wrote:

You've defined your PK as 'RAW'. RAW is a type on Oracle that's not that common. Please change that type if you can, e.g. CHAR(16) with binary data.

That the count also results in 0 rows is odd though.

Thanks Otis, I have changed this. The RAW type was automated from the designer we use (the default conversion of uniqueidentifier for Oracle was RAW(16)). I can change this across the board to CHAR(32 BYTE) if this solves the issue (the CHAR(32 BYTE) makes sense for storing Guid's... At least I think it does ).

The change did not affect the above outcome though ... Same error.

The 2 queries is the output of the DQE, it executes 1 query, this one: SELECT COUNT(*) AS NumberOfRows FROM (SELECT "VCMS"."WITHDRAWREASON"."ID" AS "Id", "VCMS"."WITHDRAWREASON"."WITHDRAWREASON" AS "WithdrawReason" FROM "VCMS"."WITHDRAWREASON") TmpResult

the first query you see is the subquery used in the actual query.

What I find odd is that the query above returns 0. If you execute that exact same query in TOAD, what's returned? (please use that exact query, no casts, converts etc.)

Okay, either the 40+ hours with little sleep or the change to the CHAR(32 byte) has fixed this, but it seems to work fine now.