The ORM Foundation

Hi,

  Working through an extremly simple ORM model, to examine and test the generated custom tool files, I ran into an error when parsing the SQLServer.ddl.

  This model has only one facttype: Subject(sprm) has at most one Color(), (Both as VARCHAR 30), and creates one table, and SPs for Insert, Delete and Update.

This is the SP with the error:
CREATE PROCEDURE Simple.UpdateSubjectColor
(
	@Color NATIONAL CHARACTER VARYING(30) 
)
AS
	UPDATE Simple.Subject
SET Color = Color
	WHERE sprm = @sprm
GO

This is the error msg:
Msg 137, Level 15, State 2, Procedure UpdateSubjectColor, Line 10
Must declare the scalar variable "@sprm".

It's obvious that though @Color was delcared in the SP, @sprm was not (though it was in other SPs).  

So, should the DDL include the declaration for this variable?  Should the declarations for SPs be done in CREATE TABLE? Should gobal variables be used, rather than local?

Looks like the SQLServer.ddl is the only one that uses variables (I had generated the scripts for the other targets for the same model for comparison).

I didn't expect to hit something like this on so simple a test model.  Does the very simplicity of the test (single fact, single table), cause the error?

One related question: I noticed that the SQLServer.ddl does not start with a Begin/Start Transaction - or end with a Commit.  Is this due to SQL Server 2005, or the DDL script generation?  It would seem that doing the CREATE SCHEMA as a transaction would be better, if there was an error in executing the script.

Thanks BRN..

The DDL should include a parameter with that name. The fact that it doesn't is a bug that will be fixed in our next release (the fix should be available in the Subversion repository within the next few days).

As for why the schema creation doesn't occur within a transaction, that is a SQL Server issue. If I remember correctly, they don't support DDL statements inside of transactions. If you take a look at our generated SQL code for other dialects (e.g. SQL Standard or PostgreSQL), everything should be wrapped in a transaction so that the whole process is rolled back if anything fails.

Hi Kevin,

  Thanks for the notes on DDL generation.  I'll look for the fix.  Anything that leads to better targeted DDL generation is helpful.  I know the team is working on some major abstraction/absorption changes inside the framework that will effect this and other issues.  Still, having a workable way to test and evaluate models generated by the tool through a physical implementation is important.  Without it, it's tempting to doubt the correctness of the model, and try to fix something that's not broken.

Hi,

  I tried to find a reference re: DDL statements inside a transaction for SQL Server 2005.  The only ones I saw suggested that you don't do this for performance reasons (my guess is locking concerns - which isn't an issue in a dev testing situation).  I posted a question about this on MSDN forum, and will let you know if I get a better answer.  BRN..

Hi Kevin,

  I got this reply after posting the question on MSDN:

--------
      The create schema statement can be run in a user transaction. You should be able to find most of the information @ http://msdn2.microsoft.com/en-us/library/ms189462.aspx
--------

  I had also asked about dropping schemas in one shot, making it easier to clean up when only some of the schema creation went through, and got this from the same source:

--------
      On the second issue, I assume you are asking if there is a way to drop a schema along with all objects contained in it in one shot. Unfortunately, this is not possible right now. You need to drop or move objects out of the schema before dropping it. We are considering this feature for a future release.
--------

  So, it looks like SQL Server DDL can run within a transaction.  Whether that works with your method of generation is something I'll leave to you.  Hope it helps.  BRN..

Hi,
  Just an add on to the last comment on CREATE SCHEMA in SQL Server 2005.  Looking at the example in the reference, I think CREATE SCHEMA is an implied transaction, so bundling at least the table creation within the shema transaction might be as easy as deleting some of the GO directives.  Don't know if the supporting SPs can be included, but if not, as they reference tables in the schema, they should fail and not need to be deleted if the schema transaction fails - so accomplishing the same result.  BRN..

Hi,

Additional note on SQL Server 2005 CREATE SCHEMA:

CREATE TABLE works fine within CREATE SCHEMA implied transaction.  ALTER TABLE does not work (reference indicates it's not allowed).  As altering a table that doesn't exist (if the table creation fails within the create schema transaction), doesn't leave any residue, that's fine.  The same is true for SP creation; if the table creation fails, the SP creaction fails, without leaving parts in the BD to clean up later.

Suggestion: Have the DDL use CREATE SCHEMA, including all CREATE TABLE, as an implied transaction (not having any GO directives until the end - to commit the transaction.  Have all ALTER TABLE, SP creation, etc..., follow the CREATE SCHEMA implied transaction, with GO directives after each alter, sp, etc....

This makes it easy to parse or execute the DDL, without having to clear out debris if the table creations fail for any reason; and multiple SCHEMA names work fine within a single DB.  These changes to the DDL script are just a matter of removing some of the GO directives, and are easy enough with any editor; but it would be better if the generated DDL script followed this form from the start.

The tests I ran worked well using this pattern.

Final thought: Would it be a good idea to inclue a USE(dbname)statement at the top of the  DDL script - to avoid the possible mistake of executing on the master db?  As the tool wouldn't know the db name to use, would a temp db name make a good default - leaving the users to change this?  BRN..

Hi,

  The 2/6/2007 release corrects the @ variable not declarred error - a huge help.  There are other issues in the DDL generation that are new or still present.  I'll put these in another thread and bug report.  BRN..