System specifics: LLBLGen Pro v3.1 Final (May 25th, 2011) Runtime is 3.1.11.427 Using Adapter model with .NET 4.0 Database is Oracle 11g Using the MS Oracle driver

Source Table definition is: CREATE TABLE TAP ( TAP_ID NUMBER(10) NOT NULL, DEVICE_ID VARCHAR2(10 BYTE) NOT NULL, TRANSACTION_ID NUMBER(6) NOT NULL, TRANSACTION_DTM DATE NOT NULL, TOKEN_ID NUMBER(9) NOT NULL, SERVICE_TYPE_ID NUMBER(3) NOT NULL, LOCATION_ID CHAR(20 BYTE) NOT NULL, ENTRY_EXIT_ID NUMBER(3) NOT NULL, TRIP_ENTRY_EXIT_ID NUMBER(3), TAP_TYPE_ID NUMBER(3) NOT NULL, TRIP_ID NUMBER(9), TAP_STATUS_ID NUMBER(3) NOT NULL, UNMATCHED_FLAG NUMBER(1) NOT NULL, INSERTED_DTM DATE DEFAULT SYSDATE NOT NULL ) TABLESPACE USERS PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;

CREATE UNIQUE INDEX AK1_TAP ON TAP (DEVICE_ID, TRANSACTION_ID, TRANSACTION_DTM) LOGGING TABLESPACE USERS PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL;

View definition is: CREATE OR REPLACE FORCE VIEW UNMATCHED_TAP (TOKEN_ID) AS SELECT DISTINCT TA.TOKEN_ID FROM TAP TA WHERE UNMATCHED_FLAG = 0;

Source Code in question: int count = (int)adapter.GetDbCount(new UnmatchedTapEntityFactory().CreateFields(), null);

Generated SQL is: SELECT COUNT(*) AS NumberOfRows FROM (SELECT UNMATCHED_TAP.TOKEN_ID AS TokenId FROM UNMATCHED_TAP )

The problem:

The view above is mapped to an entity definition in the LLBLGen designer. Our problem is that the statement in question returns a count of zero when it should return a count of four. We have confirmed that running the generated SQL statement from SqlPlus or Toad returns the correct value of four. So we assume the problem lies with LLBLGen or the MS Oracle driver. Can you confirm this?

What about this:

int count = adapter.GetDbCount(new EntityCollection<UnmatchedTapEntity>(), null);

Would it make a difference?

I am trying this now. But one more piece of information I forgot is that this only fails when the package is running as a service after it has been installed using an MSI file built with VS2010.

That did not fix the problem.

jlkInChantillyVA wrote:

I am trying this now. But one more piece of information I forgot is that this only fails when the package is running as a service after it has been installed using an MSI file built with VS2010.

I can't reproduce it. Are you sure you are using the correct connection string in your service config? Does this happen with other objects or just with that entity/view?

The connection string is correct because other SQL statements are executing just fine. I do see one other problem in another web app that I am tracking down as well, but for now it appears to be just the GetDbCount method that fails in this service app. I will try a GetDbCount method on one of the regular tables in this same app to see if that always returns zero as well.

Are you sure that you have the same version of the Oracle drivers installed on the machine where it fails - it does seem wierd that you have inconsistent results between machines...

Matt

Confirmed that GetDbCount works perfectly fine on an Oracle table, but not on an Oracle view. Maybe because it is mapped to an entity instead of a typed view?

jlkInChantillyVA wrote:

Confirmed that GetDbCount works perfectly fine on an Oracle table, but not on an Oracle view. Maybe because it is mapped to an entity instead of a typed view?

That shouldn't make any difference. I did similar tests over here and everything works fine. Let me know if you want to see my tests.

Is there any chance you cook a simple solution that reproduce the problem and attach it? (no bin directories, no dlls, just your .llblgenproj file, your test code and a DDL script, all zipped). You can open a HelpDesk thread (private) to post your code there.

This gets weirder: I have now confirmed that this exact GetDbCount statement works perfectly fine (returns a count of 1) when included in an ASP.NET application running on the same computer as the program that it fails (returns a count of 0) in now. The program where the statement is failing is installed (not a unit test and not running from the IDE) and running as a Windows service on a Windows 2008 server. Is it possible that the service could be using a different driver or can I conclude that this rules out a driver problem?

Just to e sure.

So the same win-service can get correct count from a table, but can not from a view with the same connection string.

That is correct.

Ran out of clues. Will have to fall back to David's: "Is there any chance you cook a simple solution that reproduce the problem and attach it?"

I found a workaround by using GetScalar instead of GetDbCount. We are using the adapter model to support both SQL Server Express 2008 and Oracle 11g databases. I did notice that the object type returned by the scalar method in that service on the Windows 2008 server was Int32 for SqlServer and Decimal for Oracle. I don't know if that difference causes a problem under the covers in the GetDBCount method or not, but frankly I don't have any more time to spend on this. I'm going to switch to using GetScalar instead of GetDbCount in all my projects, and I consider this matter closed.

You never mention you were using the same code for SqlServer. Indeed it could be, I don't know. Anyway good you find a workaround on this.