Defect in the auto-generation of SpecSQL in the generated interface .cs files

[David F. Sklar]

Greetings. I have found an easily-repaired defect in the auto-
generated SpecSQL functions generated by Rise version 2.5.0.13.

I am using VisStu 2005, .NET 2

Currently, for the SQL-Server ODBC drivers, the function SpecSQL will
return the string "select convert(int,SCOPE_IDENTITY())"

This causes this select statement to be concatenated to the insert
statement (in the "New..." methods) without any semicolon separator,
which causes the OdbcDataReader to fail in attempting to do the insert
operation and the subsequent "obtain the ID of the new record"
operation. That sequence simply fails because the lack of separator
causes the CommandText to be considered invalid.

I'm manually repairing all the RISE-generated interface files by
simply adding that important semicolon and having SpecSQL return this
for the sqlsvr case:

"; select convert(int,SCOPE_IDENTITY())"

Once I make that repair, everything works great! But it's quite a
hassle to do this manual repair after every code-gen operation from
Rise.

I'd love to see this repair in RISE soon, and am willing to test an
alpha/beta of the release with the repair! Thanks!

[Ola Hellgren]

Hi David,

As soon as we're convinced the semicolon isn't "dangerous" - i.e. it
must work on systens where the current code works - we'll add it.
Shouldn't take too long to sort out so hang in there :-)

Thing is, T-SQL shouldn't require a semicolon between the insert and
select statement, although it does support it. The semicolon it's an
ANSI SQL thing. So, the code should work with as well as without the
semicolon. And so it does on all other system we've come by so far.

We really like to know more about your database server and, possibly,
the ODBC-driver. Perhaps, there's a setting somewhere to force the
system into some kind of "mandatory ANSI" mode? The code below
implements the scenario at hand here using both constructs. Does it
fail on your SQL Server?

create table testSC
(
c_id int identity(1,1) primary key not null,
c_name nvarchar(50) null
)
GO
insert into testSC (c_name) values ('without; ') select scope_identity
()
GO
insert into testSC (c_name) values ('with ;'); select scope_identity()
GO


//Ola

[David F. Sklar]

This case is closed.

After a refactoring of my RISE project, this symptom has disappeared.
I cannot reproduce it anymore. The addition of the semicolon was
changing behavior of the driver, and that in itself is interesting...
but it was really just masking a problem with the model/interfaces
themselves. After refactoring and complete interface regeneration,
this symptom is just plain gone. Sorry to have bothered you, Ola!