Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> TypedView bug?
 

Pages: 1
Bugs & Issues
TypedView bug?
Page:1/1 

  Print all messages in this thread  
Poster Message
Stoop
User



Location:
Reno, Nevada
Joined on:
28-Feb-2004 12:01:28
Posted:
66 posts
# 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

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37319 posts
# Posted on: 05-Mar-2004 14:04:01.  
Odd stuff Regular Smiley

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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Stoop
User



Location:
Reno, Nevada
Joined on:
28-Feb-2004 12:01:28
Posted:
66 posts
# 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!!!
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37319 posts
# 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 Regular Smiley


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Stoop
User



Location:
Reno, Nevada
Joined on:
28-Feb-2004 12:01:28
Posted:
66 posts
# 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
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37319 posts
# 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. Regular Smiley

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37319 posts
# 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. Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Stoop
User



Location:
Reno, Nevada
Joined on:
28-Feb-2004 12:01:28
Posted:
66 posts
# Posted on: 10-Mar-2004 22:18:42.  
but I'm running SQL2000....

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37319 posts
# Posted on: 11-Mar-2004 09:40:35.  
Stoop wrote:
but I'm running SQL2000....

Confused Hmmm... Regular Smiley
Very weird. I'll re-check with a vanilla sqlserver 2000 install of northwind.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.