selectKey not working --- Please help !!

[nilesh]

Hello,

I'm new to iBatis SQLMaps and trying out some common features before deciding to use it in our project...

I'm not able to use selectKey to generate a logical sequence while inserting a record..

consider following example... a record need to be inserted in employee table with employeeno beging generated as a logical sequence.. this doesn't work for let say companyno=5 if there is no record present in employee with companyno=5

selectKey returns NULL and it tries to add a record with employeeno=NULL and as a result throws a SQL exception.... if a record is present with that companyno, it works fine...

<insert id="insert" parameterClass="map">
    <selectKey resultClass="int" keyProperty="employeeno">
              select max(employeeno)+1 from employee where companyno = #companyno#
        </selectKey>
        insert into employee (
              companyno,
              employeeno,
              empname,
              empage)
        values (
              #companyno#,
              #employeeno#,
              #empname:VARCHAR#,
              #empage#)
</insert>

Please let me know if there is any better way of doing this... Thanks in advance for your help !!

Regards
Nilesh

[Larry Meadors]

Most databases will allow you to replace null values with something else.

select max(isnull(empNo, 1))+1 from ....

Larry

[Kris Jenkins]

IMO, that's a very bad way to generate the employeeno.  What happens if two calls happen, and this sequence occurs:

A) Select max(employeeno)+1...=52.
B) Select max(employeeno)+1...=52
A) Insert new employee #52.
B) Insert new employee #52.

...at best you've got a unique contraint exception.  At worst you have two employees with the same ID.

Far better to use a sequence and avoid this problem all together.*  The syntax for this will depend on your database.

HTH,
Kris

* Plus which, depending on your database select max( somenumber) can end up being _really_ slow.

[nilesh]

Thanks for quick responses !!

Larry,
isnull works for the records which already exists in the table. e.g. "select isnull(employeeno, 1) from employee where companyno=5" returns an empty recordset if there is no record present in employee table for that company

Kris,
sequnce is a good option... but i'm using MS SQL Server database, which i believe, do not have sequence concept. Alternative is to define an IDENTITY column - where database generates a unique id each time a record is inserted. But that doesn't fulfill my 'logical sequence' requirement. IDENTITY will create a new employeeno irrespective of which company does this employee belong to.

Btw, i also tried with default values and that didn't work either..

<insert id="insert" parameterClass="map">
    <selectKey resultClass="int" keyProperty="employeeno">
        select max(employeeno)+1 from employee where companyno = #companyno#
    </selectKey>
    insert into employee (
        companyno,
        employeeno,
        empname, 
        empage)
    values (
        #companyno#,
        #employeeno:NUMERIC:-1#,
        #empname:VARCHAR#,
        #empage#)
</insert>

database design may be wrong.. but apart from that, i think, there has to be some way in iBatis to achieve creating logical sequence as above..

[Larry Meadors]

Given those requirements, I think your best bet may be to either break it into two steps, or write a stored procedure.

IMO, selectKey is intended to handle the simple cases, and this is not one...

Larry

[Kris Jenkins]

You could try...

select count(*)+1 from employee where companyno = #companyno#

That ought to return 1 when there are no rows (depending on what MS thought about standards on that particular day :*p)

But I stand by my original point - if you've got any decent level of concurrency you're going to hit exceptions.  Are you sure a logical sequence is that useful?

Cheers,
Kris

[Clinton Begin]

Sorry if you've answered this already.   But why aren't you using an IDENTITY column?  I don't like autogenerated key columns any more than the next guy (Oracle sequence rocks).  Unfortunately someone has already dictated that you'll use MS SQL Server.  Hence you should probably use its facilities for unique ID generation. 

When at a Chinese restaurant, it's best to order Chinese food, because their Mexican probably sucks.  ;-)

Cheers,
Clinton

[nilesh]

Larry,
selectKey works very well when the corresponding SQL returns a recordset... the problem is only when the recordset comes back as empty...

well, i removed selectKey completely and now trying with very simple example --

<insert id="insert" parameterClass="map"> 
    insert into employee ( 
        companyno, 
        employeeno, 
        empname, 
        empage) 
    values ( 
        #companyno#, 
        #employeeno:NUMERIC:-1#, 
        #empname:VARCHAR#, 
        #empage#) 
</insert> 

employeeno is not passed thro the input map.. and since the default value is provided, it should use that during the insert operation... however it throws an exception as --

Cannot insert the value NULL into column 'employeeno'

Would you plz let me know what am i doing wrong while defining a default value.....

Thanks !!

[Larry Meadors]

Yes, I agree, it works great in simple cases, and I use it all the time for them.

However I think when you start getting into more complex scenarios (like this one) that you should start to look for other solutions.

[Brandon Goodin]

THIS IS A USER SUPPORT QUESTION AND IS NOT RELATED TO THE DEVELOPMENT OF THE IBATIS FRAMEWORK. PLEASE POST ON THE USER SUPPORT FORUM IN THE FUTURE.