TypedView bug?

Posts   
 
    
Stoop
User
Posts: 66
Joined: 28-Feb-2004
# Posted on: 05-Mar-2004 13:39:29   

Hi

I have been running some tests agains the NW db. To my project I added a typed view based on the 'Alphabetical list of products' view. I tried running it it from code (just the 'Fill' method - no sorting, filtering) & it blew on me in the 'GetMultiAsDataTable' in the TypedListDAO class. I took a look at the command text and here what was generated:

"SELECT [dbo].[Alphabetical list of products].[ProductID] AS [ProductId],[dbo].[Alphabetical list of products].[ProductName],[dbo].[Alphabetical list of products].[SupplierID] AS [SupplierId],[dbo].[Alphabetical list of products].[CategoryID] AS [CategoryId],[dbo].[Alphabetical list of products].[QuantityPerUnit],[dbo].[Alphabetical list of products].[UnitPrice],[dbo].[Alphabetical list of products].[UnitsInStock],[dbo].[Alphabetical list of products].[UnitsOnOrder],[dbo].[Alphabetical list of products].[ReorderLevel],[dbo].[Alphabetical list of products].[Discontinued],[dbo].[Alphabetical list of products].[CategoryName] FROM [dbo].[Alphabetical list of products]"

I plopped this SQL over into a new view in SQL Server, tried to run it and got a "Invalid nane 'CatagoryName'" error

If you adjust the generated code to this, and paste into SQL view it works just fine

"SELECT [dbo].[Alphabetical list of products].[ProductID] AS [ProductId],[dbo].[Alphabetical list of products].[ProductName],[dbo].[Alphabetical list of products].[SupplierID] AS [SupplierId],[dbo].[Alphabetical list of products].[CategoryID] AS [CategoryId],[dbo].[Alphabetical list of products].[QuantityPerUnit],[dbo].[Alphabetical list of products].[UnitPrice],[dbo].[Alphabetical list of products].[UnitsInStock],[dbo].[Alphabetical list of products].[UnitsOnOrder],[dbo].[Alphabetical list of products].[ReorderLevel],[dbo].[Alphabetical list of products].[Discontinued],[dbo].[Alphabetical list of products].[Expr1] FROM [dbo].[Alphabetical list of products]"

This seems to be because the SQL in the 'Alphabetical list of products' view declares CustomerName as Expr1:

SELECT dbo.Products.*, dbo.Categories.CategoryName AS Expr1 FROM dbo.Categories INNER JOIN dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID WHERE (dbo.Products.Discontinued = 0)

The LLgen doesn't seem to pick this up...

Steve

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 05-Mar-2004 14:04:01   

Odd stuff simple_smile

This is what I get: SELECT [dbo].[Alphabetical list of products].[ProductID], [dbo].[Alphabetical list of products].[ProductName], [dbo].[Alphabetical list of products].[SupplierID], [dbo].[Alphabetical list of products].[CategoryID], [dbo].[Alphabetical list of products].[QuantityPerUnit], [dbo].[Alphabetical list of products].[UnitPrice], [dbo].[Alphabetical list of products].[UnitsInStock], [dbo].[Alphabetical list of products].[UnitsOnOrder], [dbo].[Alphabetical list of products].[ReorderLevel], [dbo].[Alphabetical list of products].[Discontinued], [dbo].[Alphabetical list of products].[CategoryName] FROM [dbo].[Alphabetical list of products]

which works fine.

My SqlServer 2000 view: CREATE view "Alphabetical list of products" AS SELECT Products.*, Categories.CategoryName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE (((Products.Discontinued)=0))

I'm now confused if it's an Sql Server thingy (SqlServer 7 vs SqlServer 2000 differences) or not.

Frans Bouma | Lead developer LLBLGen Pro
Stoop
User
Posts: 66
Joined: 28-Feb-2004
# Posted on: 05-Mar-2004 14:18:04   

Hi

Noooo - I'm running SQL Server 2000, so I have no clue why the SQL for the 'Alphabetical list of products' view is different. Maybe others could check & see if the SQL in their SQL Servers matches mine or yours...

In any case, what if a developer set up a view like the one I mentioned? LLgen would still throw an error.. Would this still be considered a bug or would it be up to the developer to tweak his/her view untill the generated SQL by LLgen worked?

I'm inclined to think that this is a bug, with a known work around.

Consider the scenario where the developer is an work envioronment wher he can NOT modify a view, for example in a legacy project. Then the deveoper is hosed...

Thanks for your quick reply!!!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 05-Mar-2004 14:40:24   

Stoop wrote:

Hi

Noooo - I'm running SQL Server 2000, so I have no clue why the SQL for the 'Alphabetical list of products' view is different. Maybe others could check & see if the SQL in their SQL Servers matches mine or yours...

In any case, what if a developer set up a view like the one I mentioned? LLgen would still throw an error.. Would this still be considered a bug or would it be up to the developer to tweak his/her view untill the generated SQL by LLgen worked?

I'm inclined to think that this is a bug, with a known work around.

Consider the scenario where the developer is an work envioronment wher he can NOT modify a view, for example in a legacy project. Then the deveoper is hosed...

Thanks for your quick reply!!!

No, the invoices view for example contains a lot of aliases. Works like a charm. I simply read which columns the view has from the information_schema views of sqlserver.

I've checked it on the northwind of MSDE here and it too has the same schema as I have. Very weird.

If you refresh the northwind catalog in the project with the catalog you tried the code against, I'm pretty sure the refresher will tell you that a column's mapping has been changed in that particular view from CategoryName to Expr1.

I simply read the columns of the view as it is defined in the db. So if someone creates a project on catalog X and tries the generated code on catalog Y where the same view has different columnnames, it will of course crash simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Stoop
User
Posts: 66
Joined: 28-Feb-2004
# Posted on: 05-Mar-2004 21:01:23   

I have no clue...

I refreshed the catalog & all of a sudden it works OK.

I created my LLgen project (a test project using the NW db) , got the schemas & didn't do anything since (until I refreshed the catalog as per your suggestion). I didn't touch the view's SQL since I originally built the project, and as far as I see it's the same as yours.

I have no clue. I work alone at home, so nobody else has touched anything..

Your right - it is strange

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 05-Mar-2004 21:31:40   

Weird, Steve... Please let me know a.s.a.p. if you run into such an issue again. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Mar-2004 17:21:43   

I found it!

It's related to SqlServer 7.

In SqlServer 7, the CategoryName is aliased as 'Expr1'. Very weird bug in that view, as this is a completely weird alias.

I've applied sp3 now to the sqlserver 7 box, I'll now upgrade to a later service pack, perhaps they've fixed it there. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Stoop
User
Posts: 66
Joined: 28-Feb-2004
# Posted on: 10-Mar-2004 22:18:42   

but I'm running SQL2000....

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Mar-2004 09:40:35   

Stoop wrote:

but I'm running SQL2000....

confused Hmmm... simple_smile Very weird. I'll re-check with a vanilla sqlserver 2000 install of northwind.

Frans Bouma | Lead developer LLBLGen Pro