I have been working lately on converting native queries in Java project from DB2 to SQL.
Apart from the date conversion one of the more tricky conversion was NEXTVALUE() which had no equivalent in sql server 2008.
So here is how I did it.
Essentially we will create a table which wil have auto generated Ids that will supply us next value.
In order not to save ids in table created above we need to have stored procedure which will get id and roll back the transaction.
Additional links
Also calling stored procedure Seam project (Java) was a big task and has been accoplished in Entity class like code below:
Declaration in Model/Entity class:
Calling a Stored Procedure:
Apart from the date conversion one of the more tricky conversion was NEXTVALUE() which had no equivalent in sql server 2008.
So here is how I did it.
Essentially we will create a table which wil have auto generated Ids that will supply us next value.
Create table tableName ( [id] [int] IDENTITY(1,1) NOT NULL ON [PRIMARY]); In order not to save ids in table created above we need to have stored procedure which will get id and roll back the transaction.
CREATE procedure spName
as
begin
declare @id int
set NOCOUNT ON
insert into tableName default values
set @id = scope_identity()
delete from tableName WITH (READPAST)
select @id as id
end
Additional links
If you are using an identity column on your SQL Server tables,
you can set the next insert value to whatever value you want. An example
is if you wanted to start numbering your ID column at 1000 instead of
1.
It would be wise to first check what the current identify value is. We can use this command to do so:
Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it. (which it really shouldn’t)
It would be wise to first check what the current identify value is. We can use this command to do so:
DBCC CHECKIDENT (‘tablename’, NORESEED)
DBCC CHECKIDENT (orders, NORESEED)
DBCC CHECKIDENT (orders, RESEED, 999)Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it. (which it really shouldn’t)
DBCC CHECKIDENT ( ‘databasename.dbo.orders’,RESEED, 999)Also calling stored procedure Seam project (Java) was a big task and has been accoplished in Entity class like code below:
Declaration in Model/Entity class:
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.Table;
@Entity
@SqlResultSetMapping(name="mappingName", columns=@ColumnResult(name="id"))
@NamedNativeQueries({
@NamedNativeQuery(name = NativeQueries.spName,
query = NativeQueries.spName,
hints={ @QueryHint(name = "org.hibernate.callable",value = "true"), @QueryHint(name = "org.hibernate.readOnly",value = "true")},
resultSetMapping = "mappingName")
})
Calling a Stored Procedure:
if(entityManager == null) {
entityManager = (EntityManager)Component.getInstance("entityManager");
if(entityManager == null) {
log.fatal("entityManager is not available in seam context!");
}
}
Query query = entityManager.createNamedQuery(spName);
NEXTNUMBER (Integer) query.getSingleResult();
