Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
418 views
in Technique[技术] by (71.8m points)

stored procedures - Does JPA 2.0 support SQL Server table variables?

I am using JPA 2.0 and SQL Server 2008 and I have determined that JPA doesn't like my stored procedures when I use a table variable.

For example, this sproc works:

declare @t table
(
    id      int identity
,   test    varchar(50)
)

select 'blah' as test  -- I'm ignoring the table variable above

However, when I try to insert something into the table variable @t with this code, it crashes:

declare @t table
(
    id      int identity
,   test    varchar(50)
)

insert into @t
    select cast(getdate() as varchar(60))

select * from @t

I get the following error:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.2.v20100323-r6872):    org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
Error Code: 0

I would hate to have to rewrite my complex sprocs to not use table variables if I can help it.

Thanks for any suggestions.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Random thought: Try adding SET NOCOUNT ON.

Your code

insert into @t
    select cast(getdate() as varchar(60))

select * from @t

...will return this

(1 row(s) affected)
id          test
----------- --------------------------------------------------
1           Jun 14 2011  3:58PM

(1 row(s) affected)

There are actual three result "items" from SQL Server. The first has no assoicated result set.

... and a shameless plug of my question SET NOCOUNT ON usage where breaking ORMs and such is mentioned because of this


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...