Informix - INSERT INTO issue
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: Informix - INSERT INTO issue

  1. #1
    Join Date
    Feb 2005
    Location
    "The Capital"
    Posts
    5,306

    Informix - INSERT INTO issue

    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):
    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.
    Last edited by exterminator; January 19th, 2006 at 10:17 AM.

  2. #2
    Join Date
    Jul 2004
    Location
    Jakarta, Indonesia
    Posts
    596

    Re: Informix - INSERT INTO issue

    Code:
    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

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL

  3. #3
    Join Date
    Feb 2005
    Location
    "The Capital"
    Posts
    5,306

    Re: Informix - INSERT INTO issue

    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:
    Code:
    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.

  4. #4
    Join Date
    Feb 2005
    Location
    "The Capital"
    Posts
    5,306

    Re: Informix - INSERT INTO issue

    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.

  5. #5
    Join Date
    Jul 2004
    Location
    Jakarta, Indonesia
    Posts
    596

    Re: Informix - INSERT INTO issue

    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

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL

  6. #6
    Join Date
    Feb 2005
    Location
    "The Capital"
    Posts
    5,306

    Re: Informix - INSERT INTO issue

    Quote Originally Posted by erickwidya
    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.
    Quote Originally Posted by erickwidya
    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.
    Quote Originally Posted by erickwidya
    PS: again it just something i thought in MSSQL, glad u already solved this issue
    Well, thanks a lot for your participation. Cheers, regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center