The EntitySpaces Community

Share and learn about the EntitySpaces Architecture.

Welcome to The EntitySpaces Community Sign in | Join | Help

The EntitySpaces Community » EntitySpaces 2008 » Preview / Roadmap Issues » Beta IV - Microsoft SQL (2005) SP generation

Beta IV - Microsoft SQL (2005) SP generation

Last post 06-06-2008, 11:36 PM by dzilla. 2 replies.


	Sort Posts: Previous Next

06-06-2008, 8:25 PM 9680

dzilla
Joined on 01-29-2007
New Port Richey, FL
Posts 11

Beta IV - Microsoft SQL (2005) SP generation

This is something new I've run into with ES 2008.0.0518.0 - was not an issue with ES 2007

Fields with varchar(max) are being generated as varchar(0)
I also see this same issue in two other tables with fields defined as varchar(250), and they are being generated as varchar(0) as well. Obviously in the sample below - the varchar(50) & varchar(128) had no issue?

Here is one of the tables being used - the error is seen running the script & is in the Update & Insert portion of the generated proc's

Code:

CREATE TABLE [dbo].[Lesson](
	[ID] [uniqueidentifier] NOT NULL,
	[DepartmentID] [uniqueidentifier] NULL,
	[SubjectID] [uniqueidentifier] NULL,
	[ClientIdentifier] [varchar](50) NOT NULL,
	[Name] [varchar](50) NULL,
	[Description] [varchar](250) NULL,
	[Notes] [varchar](max) NULL,
	[TeacherID] [uniqueidentifier] NULL,
	[ALSActivityKey] [int] NULL,
	[ALSGuid] [varchar](128) NULL,
	[Style] [int] NULL,
	[LastUpdateDateTime] [datetime] NOT NULL CONSTRAINT [DF_Lesson_LastUpdateDateTime]  DEFAULT (getdate()),
	[CreateDateTime] [datetime] NOT NULL CONSTRAINT [DF_Lesson_CreateDateTime]  DEFAULT (getdate()),
 CONSTRAINT [PK_Lesson] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

This is the error:

Code:

Procedure Creation: proc_LessonLoadAll Succeeded
Msg 1001, Level 15, State 1, Procedure proc_LessonUpdate, Line 10
Line 10: Length or precision specification 0 is invalid.
Msg 137, Level 15, State 2, Procedure proc_LessonUpdate, Line 31
Must declare the scalar variable "@Notes".
Procedure Creation: proc_LessonUpdate Error on Creation
Msg 1001, Level 15, State 1, Procedure proc_LessonInsert, Line 10
Line 10: Length or precision specification 0 is invalid.
Msg 137, Level 15, State 2, Procedure proc_LessonInsert, Line 51
Must declare the scalar variable "@Notes".

The Update sp as generated in Beta IV:

Code:

CREATE PROCEDURE [proc_LessonUpdate]
(
	@ID uniqueidentifier, 
	@DepartmentID uniqueidentifier = NULL, 
	@SubjectID uniqueidentifier = NULL, 
	@ClientIdentifier varchar(50), 
	@Name varchar(50) = NULL, 
	@Description varchar(250) = NULL, 
	@Notes varchar(0) = NULL, 
	@TeacherID uniqueidentifier = NULL, 
	@ALSActivityKey int = NULL, 
	@ALSGuid varchar(128) = NULL, 
	@Style int = NULL, 
	@LastUpdateDateTime datetime, 
	@CreateDateTime datetime
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	UPDATE [Lesson]
	SET
		[DepartmentID] = @DepartmentID, 
		[SubjectID] = @SubjectID, 
		[ClientIdentifier] = @ClientIdentifier, 
		[Name] = @Name, 
		[Description] = @Description, 
		[Notes] = @Notes, 
		[TeacherID] = @TeacherID, 
		[ALSActivityKey] = @ALSActivityKey, 
		[ALSGuid] = @ALSGuid, 
		[Style] = @Style, 
		[LastUpdateDateTime] = @LastUpdateDateTime, 
		[CreateDateTime] = @CreateDateTime
	WHERE
		([ID] = @ID)

	SET @Err = @@Error


	RETURN @Err
END
GO

For sanity reference - the ES 2007 version of the same proc:

Code:

CREATE PROCEDURE [proc_LessonUpdate]
(
	@ID uniqueidentifier,
	@DepartmentID uniqueidentifier = NULL,
	@SubjectID uniqueidentifier = NULL,
	@ClientIdentifier varchar(50),
	@Name varchar(50) = NULL,
	@Description varchar(250) = NULL,
	@Notes varchar(max) = NULL,
	@TeacherID uniqueidentifier = NULL,
	@ALSActivityKey int = NULL,
	@ALSGuid varchar(128) = NULL,
	@Style int = NULL,
	@LastUpdateDateTime datetime,
	@CreateDateTime datetime
)
AS
BEGIN

	SET NOCOUNT OFF
	DECLARE @Err int

	UPDATE [Lesson]
	SET
		[DepartmentID] = @DepartmentID,
		[SubjectID] = @SubjectID,
		[ClientIdentifier] = @ClientIdentifier,
		[Name] = @Name,
		[Description] = @Description,
		[Notes] = @Notes,
		[TeacherID] = @TeacherID,
		[ALSActivityKey] = @ALSActivityKey,
		[ALSGuid] = @ALSGuid,
		[Style] = @Style,
		[LastUpdateDateTime] = @LastUpdateDateTime,
		[CreateDateTime] = @CreateDateTime
	WHERE
		[ID] = @ID


	SET @Err = @@Error


	RETURN @Err
END
GO

Have you seen this before?

Jim

Report abuse

06-06-2008, 11:31 PM 9685 in reply to 9680

David.Parsons
Joined on 01-14-2007
Posts 1,400

Re: Beta IV - Microsoft SQL (2005) SP generation

I took your CREATE TABLE and ran the ES2008 Beta IV SP template against MyGen 1.2.0.7, 1.3.0.3, and CodeSmith. They all look fine:

Code:

CREATE PROCEDURE [proc_LessonUpdate]
(
	@ID uniqueidentifier, 
	@DepartmentID uniqueidentifier = NULL, 
	@SubjectID uniqueidentifier = NULL, 
	@ClientIdentifier varchar(50), 
	@Name varchar(50) = NULL, 
	@Description varchar(250) = NULL, 
	@Notes varchar(MAX) = NULL, 
	@TeacherID uniqueidentifier = NULL, 
	@ALSActivityKey int = NULL, 
	@ALSGuid varchar(128) = NULL, 
	@Style int = NULL, 
	@LastUpdateDateTime datetime, 
	@CreateDateTime datetime
)
...

Running against the ES 2007.1.1210.0 template in MyGen 1.3 had the same results. MyGen 1.2 produced this difference:

Code:

...
	@Notes text = NULL,
...

Two things caught my eye. First, the Description field is a varchar(250), yet your posted Beta IV SP looks right. Looking at the template code, we are just using DataTypeNameComplete, so it baffles me why it would work for one table, but not another. Second, in all cases "MAX" was generated as uppercase for me. Your ES2007 SP was generated as varchar(max). I'm not sure what that means, other than we'll have to do some more digging.

BTW, we have a number of data type testing tables that generate SPs correctly for ES2008 as well. One already had a varchar(50) and varchar(max) defined, and I added a varchar(250), just to be sure.

Code:

CREATE PROCEDURE [dbo].[proc_DataTypeTestUpdate]
(
	@ID int, 
	@TextType text = NULL, 
	@IntType int = NULL, 
	@BitType bit = NULL, 
	@DateTimeType datetime = NULL, 
	@DecimalType decimal(8,4) = NULL, 
	@VarCharType varchar(50) = NULL, 
	@TinyIntType tinyint = NULL, 
	@CharType char(10) = NULL, 
	@ImageType image = NULL, 
	@VarBinaryType varbinary(50) = NULL, 
	@VarBinaryMaxType varbinary(MAX) = NULL, 
	@VarCharMaxType varchar(MAX) = NULL, 
	@GuidType uniqueidentifier = NULL, 
	@GuidTypeNewId uniqueidentifier = NULL, 
	@VarCharTypeLarge varchar(250) = NULL
)
...

David Neal Parsons
www.entityspaces.net

Report abuse

06-06-2008, 11:36 PM 9686 in reply to 9685

dzilla
Joined on 01-29-2007
New Port Richey, FL
Posts 11

Re: Beta IV - Microsoft SQL (2005) SP generation

Thanks David - I'll give it another go & see what else I can find.

Jim

Report abuse