Java Development Tools

Hello JDevTeam & JDevelopers,

I want to use JDev/ADF with a MS SQL Server 2005 database that contains tables employing IDENTITY Columns.

Using JDev/ADF and DBSequence with an Oracle database employing before triggers/sequences accomplishes what I am trying to do except I want to accomplish the same thing using a MSSQL Server 2005 database. Unfortunately I cannot change the database.

I have been able to select records but I am unable to insert records (due to my lack of knowledge) when using MS/SQL Server Identity Columns with JDev/ADF.

The following are the steps taken thus far.

-------------------------------------------------------------------------------------

Step1: Create table named test in the 2005 MSSQL Server (see script below).

-------------------------------------------------------------------------------------

Step2: Register 3rd Party JDBC Driver with JDeveloper; Using use Tools/Manage Libraries. Create a new entry in User Libraries with the following;
Library Name = Ms2005Jdbc
Class Path = C:\dev\Ms2005Jdbc\sqljdbc_1.0\enu\sqljdbc.jar
(note: Latest TYPE 4 JDBC driver for Microsoft SQL Server 2005 - free at http://msdn.microsoft.com/data/ref/jdbc/)

-------------------------------------------------------------------------------------

Step3:Create New Database Connection;
Connection Name = testconn1
Type = Third Party JDBC Driver
Authentication Username = sa, Password = password, Check Deploy Password
Connection
Driver Class = com.microsoft.sqlserver.jdbc.SQLServerDriver
Library = Ms2005Jdbc
Classpath = C:\dev\Ms2005Jdbc\sqljdbc_1.0\enu
URL = jdbc:sqlserver://192.168.1.151:1433;instanceName=sqlexpress;databaseName=test
Test Connection = Success!

-------------------------------------------------------------------------------------

Step5: Create a new application workspace using Web Application default template

-------------------------------------------------------------------------------------

Step6: In Model project, Create new Business Components Diagram.

-------------------------------------------------------------------------------------

Step7: Create new Entity Object. Goto to connections/testconn1, open tables and drag table test onto the diagram.

-------------------------------------------------------------------------------------

Step8: Generate Default Data Model Components by right-clicking on Entity Object. Except all the defaults.

-------------------------------------------------------------------------------------


When I test the Appmodule I select the view object and can scroll through all the records without error. If I try to insert a record, I get JBO-27014: Attribute testid in test is required.

Going back to the EntityObject I deselect the Mandatory attribute and re-run the test. Now when I try to insert it accepts the value for testname but it does not update the PK testid like it would using an "JDev/ADF/DBSequence/Oracle database/before trigger/sequence" solution.

Going back to the EntityObject and selecting refresh on insert does not solve this problem either. Changing the URL connection string and adding "SelectMethod=cursor" did not help and changing the SQl Flavor to SQLServer produced errors in the Business Components Browser. I've tried overriding isAttributeChanged() and other things as well.

I am totally stuck! Can anyone provide a solution?

Thanks for you help,
BG...

-------------------------------------------------------------------------------------

Create table named test

use [testdb]
go

set ansi_nulls on
go
set quoted_identifier on
go

create table [test](
[testid] [int] identity(0,1) not null,
[testname] [nvarchar](50) collate sql_latin1_general_cp1_ci_as not null,
constraint [pk_test] primary key nonclustered
(
[testid] asc
)with (pad_index = off, ignore_dup_key = off) on [primary]
) on [primary]