Informix - INSERT INTO issue

[exterminator]

So here's the problem - I have to insert a record into a table in informix. The statement looks something like this (executed from the client side code):

INSERT INTO MYTABLE
(column_a, column_b, column_c, version, column_e)
VALUES
(value_a, value_b, value_c, value_version, value_e)

Now this looks simple.

There is a primary key set on the table which consists of columns a, b, c and version & a unique index set on columns a, b, c and e.

Now, whenever I insert records I need to see if there already exists a record with column values for columns a, b, and c as same as what I am inserting (and whatever e - same e value in such situation would not be accepted - this would be ensured by the unique index) and if there are any then increase the value of the version column by 1 and go ahead with the insert.

What do you suggest? I am open to using triggers as well. And to just say it again - its Informix I am using. Thanks for having a look. Regards.

[erickwidya]

IF NOT EXISTS(SELECT * FROM mytable WHERE column_a = @a, column_b = @b, column_c = @c)
BEGIN
  INSERT ...
  VALUES ...
ELSE
  SELECT @version = version_column + 1 FROM mytable WHERE column_a = @a, column_b = @b, column_c = @c

  INSERT ... 
  VALUES(@a, @b, @c, @version)  
END

IF @@ERROR <> 0 
  RETURN -1
ELSE
  RETURN 0

PS : not sure in Informix..it's for MSSQL
@ = Query parameter or in this case SPROC's parameter

for trigger, i'm not sure if it can be done using it since trigger can't accept parameter like SPROC, well at least in MSSQL

[exterminator]

Thanks, erickwidya, for your reply!

That is a solution but I was to fire the query from the client side and did not want to use a stored procedure or a trigger (although I was open to using them if no single query could do that job) and this is what I thought of:

INSERT INTO mytable
(column_a, column_b, column_c, version, column_e)
VALUES
(value_a, value_b, value_c, 
isnull((SELECT MAX(version)+1 from mytable 
          WHERE 
          column_a=value_a AND 
          column_b=value_b AND 
          column_c = value_c), 1), value_e)

This seems to be the one for me. But the problem now is that Informix doesn't have the isnull function as does MS SQL server and Sybase (and probably even Oracle). So I thought of writing a UDF for this but I am stuck with silly syntax errors. Is there any other way out of this isnull thing? This is important in the cases when the table would be empty and select MAX returns null. Am I missing something really simple out here? Thanks again for reply. Regards.

[exterminator]

Got it... there is a function NVL that does exactly what I wanted. The thread is resolved but you are welcome to pour in anything interesting on the topic. regards.

[erickwidya]

This is important in the cases when the table would be empty and select MAX returns null

why don't u DECLARE @cmd that check for EXISTence of the record first..if there any then set the @cmd = usual INSERT but if there isn't set @cmd = INSERT with SELECT MAX(version)+1 and after that execute that @cmd?

That is a solution but I was to fire the query from the client side and did not want to use a stored procedure or a trigger

why? from what i read using SPROC is more faster than using Client Side code also it encapsulate the code(SELECT, ACTION query) that needed into Server side (more less code to maintain i think)

..there is a function NVL that does exactly what I wanted.

so it act like ISNULL in MSSQL?

PS: again it just something i thought in MSSQL, glad u already solved this issue

[exterminator]

why? from what i read using SPROC is more faster than using Client Side code also it encapsulate the code(SELECT, ACTION query) that needed into Server side (more less code to maintain i think)

Very true. I will not say anything on this . Actually, thats the current project architecture or design or coding guideline whatever you may call it, which is wierd. But yeah, I would have had made an exception if it was really impossible on clientside.

so it act like ISNULL in MSSQL?

Yes. Has an expression as the first argument and a replacement value, the second, that is returned in case the expression evaluates to null else returns the non-null result of the expression. Same as isnull of SQL Server.

PS: again it just something i thought in MSSQL, glad u already solved this issue

Well, thanks a lot for your participation. Cheers, regards.