OJB [ SQL Server ] how-to request:
First off... Im a newbie to OJB.. and just wanted to say how slick I think OJB
is :-) My hats off Thomas and the OJB team!
(sorry for the length of this post in advance and I hope I'm not asking the
obvious)
Im attempting to use OJB on a project that will deal with legacy tables using
both ORACLE and SQL Server.
The tables will be accessed by both older applications and newer applications
that utilize OJB and therefore I need them
to interoperate. The 'issue' is with generating foreign keys.
[ Background information ]
The old system uses STORED procedures for inserts on tables. On the ORACLE
side, it uses an ORACLE sequence (per table)
to generate a unique ID. On the SQL Server side however, it uses the SQL
Server's IDENTITY COLUMN 'feature'.
It was very simple to implement the ORACLE solution. I simply implemented a new
sequence manager that calls a stored procedure (creating it
on the fly if necessary) that gets the next value from the oracle sequence
associated with the table.
[Problem statement]
However in trying to implement the SQLServer solution I have run into problems.
SQL Server does not like you to specify the
IDENTITY COLUMN on the INSERT statement for a new object. It will throw the
following exception
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]
Cannot insert explicit value for identity column in table 'CfTextQuestion'
when IDENTITY_INSERT is set to OFF.
[ Approaches attempted ]
My first cut was to try and obtain the information from the 'identity' from a
SequenceManager that called a stored procedure.
CREATE PROCEDURE PSG_CFTEXTQUESTION(@aOutId int output) as
BEGIN
SELECT @aOutId=IDENT_CURRENT( 'CfTextQuestion' ) + IDENT_INCR(
'CfTextQuestion' )
END
However, this was inherently unsafe because it would be possible to have the
same ID returned (if the legacy code inserted something into the
table after you executed the stored procedure - and other issues). And... as
stated above... when OJB attempted to execute the insert statement... I got the
aforementioned exception. I did not see a facility where you could 'increment'
the IDENTITY column - counter (gee that would have been to easy)
[ 2nd approach ]
So.. then I investigated SETTING the IDENTITY INSERT to off on SQL Server...
via making the Persistent Class
implement the PersistenceBrokerAware class... and then in the
BEFORE STORE... turning ON the IDENTITY INSERT
AFTER STORE ... turning OFF the IDENTITY INSERT
There was a statement in the SQL Server docs that IDENTY INSERT ON can only be
active on ONE TABLE at a time (arghhhh).
However.. this solution isnt elegant / valid because (a) I still have the issue
with the stored procedure being 'unsafe' and (b) it would mean that
special code would need to be put in to check which platform you are on before
executing the 'BEFORE STORE' & 'AFTER STORE'
[ Questions ] < Keeping in mind that I have been using OJB for approximately a
week... >
1. It would appear that it is not possible to have a COLUMN that is specified
as an IDENTITY COLUMN -- AND -- in the OJB repository
because one would always get the above exception for an INSERT statement.
In my case.. since the primary key is the
IDENTITY column I need to have the field specified in the OJB repository
Unfortunately for me... I have to deal with legacy tables and hence 'the
legacy way' of generating unique id's. I like the OJB way much better!
Has anyone run into this problem out there? I would assume this is a
common way under SQL Server to generate primary keys.. and
since SQL Server is very similar to Sybase... they may have the same
facility (but im not familiar with it.. and therefore cant say for sure)
Oracle's approach to sequence generators seems much more sane.
If so... how did you solve the problem?
[ Possible solutions -- IFF there isnt a nicer way to solve the problem]
[ I realize this may seem specific to one database however.. im trying to avoid
having different repository files based on different database platforms ]
I was thinking on playing around and attempting to put in a facility that on a
field you could specify the 'identity' ness or
something similar so that on an insert statement... it would basically build an
INSERT statement without the fields specified
as identity-column="true" ... then after generating the statement automatically
re-read the row and update the object's state with the
value(s) of identity columns... Since Im a newbie with OJB however.. i have no
idea yet how 'sane' / 'doable' this approach is... or ramifications
in regard to other more complex O/R mappings.
Or am I just missing the boat and there is a simpler solution?
<class-descriptor class="com.mactiveinc.po.TextQuestion"
table="CfTextQuestion">
<field-descriptor id="1"
name="id"
column="ID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
identity-column="true" />
.
</class-descriptor>
Thanks in advance for any information...
Jeff
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
