Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2013
    Posts
    4

    Unanswered: ResultSet Update & Current Timestamp (C#)

    Hello,

    I'm using C# to execute a SELECT on the Database. The ResultSet Data is then read and updated line by line using Db2ResultSet.Update();

    Before the update the parameters are set like that:
    resultSet.SetDB2TimeStamp(rowCount, new B2TimeStamp(...);

    How can I set the current database timestamp which would be the result of "CURRENT TIMESTAMP"?


    It would be possible to do this executing the update as separate statement:
    UPDATE table SET column= CURRENT TIMESTAMP
    but since DB2 doesn't have a rowID on every row it is not possible to do this on exaclty the row which was selected before.


    Is there any solution for this? Or do we have to add a rowID column to all tables on which we want to execute an update with a CURRENT TIMESTAMP value? (That would really mean a lot of change, not sure if that's possible, so I'm hoping for another solution.)

    Thank you for any help!

    (Sorry, I didn't see a C# section so I didn't know where to post. I suppose it's similar in Java?)

    Using: DB2 Advanced Enterprise Server, Version 10.1.200.238, FixPak 2

  2. #2
    Join Date
    Jun 2013
    Posts
    4
    So it seems that ROWID is not even available on Windows. :/

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this a JAVA question or a C# question, if C# it should be in the C section
    also is it a language problem (C#) or a DB2 problem?

    when you refer to timestamp are you referring to the timestamp datatype or setting the current system time.

    I don;'t really undertsnad your question, mebbe Im being particularly thick this morning

    if you want to record when you read a row, then update that row with the primary key of the row(s) you have just read

    if you are using the timestamp datatype in your table design then writing back to any column of that row should set the timestamp
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2013
    Posts
    4

    Lightbulb

    Hi healdem,

    Quote Originally Posted by healdem View Post
    is this a JAVA question or a C# question, if C# it should be in the C section
    also is it a language problem (C#) or a DB2 problem?
    this is in fact a C# question, I didn't know were to put it since there was no C# forum, sorry for that!

    Quote Originally Posted by healdem View Post
    if you want to record when you read a row, then update that row with the primary key of the row(s) you have just read
    The select statement only selects the columns which shall be updated, therefore I do not know the primary key values of that row (and those statements are generated automatically from other code, so changing that would be laborious).

    Quote Originally Posted by healdem View Post
    if you are using the timestamp datatype in your table design then writing back to any column of that row should set the timestamp
    When I checked the column definition of that column I saw:
    UPDTSTMP timestamp DEFAULT CURRENT TIMESTAMP NOT NULL
    Wrong:
    you were right, just changing the other value already does the trick!

    But I don't really understand. This timestamp column was not empty before the update and other columns also have default values, like for example:
    COLUMNX smallint DEFAULT 0 NOT NULL
    but those values were preserved. Why is only the timestamp column updated automatically? Is it a special feature for the CURRENT TIMESTAMP?


    Not true, I read the wrong line!

    BUT: There is the possibility to set this default value on the resultSet record in C# by doing:
    Code:
    resultSet.SetDefault(columnIndexInResultSet);
    and that really works!
    Last edited by Krania; 06-24-13 at 10:16.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you excpect to update a row/rows within a relational database then you need to use the primary key or some other mechanism which uniquely identifies the row(s) to be updated.

    (in part) thats what the primary key is there for. whether thats is a lot of effort for you to change is up to you. its usually easier to go woth the flow, and accept that the designers of the SQL language knew what they were doing when they dreated SQL and when others created relational databases.

    when you update data you need to limit the rows affected, that is usually done by limiting the rows affected using a where clause. if you dont' specify such a limitations then all rows will be affected.

    specifying the primary key is the easiest and quickest way of limiting your changes to a specific row

    if you want to change a nukmber of rows then you cna use other elemtns in your where claues


    eg
    change rows which were last edited on the dd of mm in yyyy

    or increase price for all products from manufacturer x by y%

    if you choose not to use the PK then you make life very hard for yourself as you try and find a workaround for your intiial laziness in using the PK
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2013
    Posts
    4
    The row is already uniquely identified in die C# code by using the ResultSet.Update() function after a select for update with a where clause was used. Using the default function for setting the current timestamp seemed the best solution here. Especially since all tables used that way already defined the default value of those columns as current timestamp.

    With that solution I didn't have to change the table definitions or make big changes the C# code which already existed.

    I understand though, that this could lead to problems if the default value of the tables are not set correctly or if at some point it turns out that we need to use another "special value" which cannot be set as default value of the column. If that happens we'd maybe still need to implement the primary keys, but for now the problem was solved quickly and easily without effecting already working code and I expect that this will also suffice in the future.

    Thank you again for your support!

Posting Permissions

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