none
Updating a timestamp field (using OLE-DB) RRS feed

  • Question

  • I have a DB2 database that I have to update by updating a timestamp field. 

    I've tried various approaches (see below) with my OleDbCommand object, each of which have failed....however, if I type these into the SQL Script Editor that comes with the iSeries Navigator then it works fine!

    Any help would be most appreciated.

    Griff

    ----------------------

    Approach #1:

    I set the CommandText to be:

    UPDATE 
    MYSCHEMA.MYTABLE 
    SET  
    MYCOLUMN = CURRENT_TIMESTAMP 
    WHERE  
    MYIDFIELD >=1 AND MYIDFIELD <= 100 

    Error message: REXX variable "MYTABLE  " contains inconsistent data.  SQLSTATE=55019

    Approach #2

    I set the CommandText to be:

    UPDATE  
    MYSCHEMA.MYTABLE  
    SET   
    MYCOLUMN = '2009-01-27-15.26.13' 
    WHERE   
    MYIDFIELD >=1 AND MYIDFIELD <= 100  

    Error message: REXX variable "MYTABLE  " contains inconsistent data.  SQLSTATE=55019

    Approach #3

    I then tried a completely paramaterised approach (error here is: SQL0206N  "@FIRSTID" is not valid in the context where it is used.)

     

    string template = @"UPDATE {0}.{1} SET {2} = {3} WHERE {4} >= @{5} AND  {4} <= @{6}"
     
    string[] arguments = new string[]  
        {  
            dealer.Name, 
            tableName,  
            processedFieldName,  
            processedFieldValue, 
            idFieldName,  
            idFieldName, 
            idFieldName 
        }; 
     
    cmdSource.Parameters.Add(new OleDbParameter() 
        DbType = DbType.Int32, 
        ParameterName = "@" + idFieldName, 
        SourceColumn = idFieldName, 
        Value = successfullyProcessedMutations[0] 
    }); 
     
    cmdSource.Parameters.Add(new OleDbParameter() 
        DbType = DbType.Int32, 
        ParameterName = "@" + idFieldName, 
        SourceColumn = idFieldName, 
        Value = successfullyProcessedMutations[1] 
    }); 

     which equates to:

     

    UPDATE  
    MYSCHEMA.MYTABLE 
    SET 
    MYTABLE = CURRENT_TIMESTAMP 
    WHERE 
    MYTABLED >= @FIRSTID AND MYTABLE <= @LASTID 
         
    DB Parameters: 
        @FIRSTID [Integer] = '1'
        @LASTID [Integer] = '501'
     
    Thursday, January 29, 2009 4:16 PM

All replies

  • Apparently, I have to specify that this is "non-journaled"...

    I think that this means that there are no transactions: http://www-01.ibm.com/support/docview.wss?uid=swg21206832

    Not sure how to specify this though with my OLEDBConnection!

    Thursday, January 29, 2009 4:33 PM