Updating data in one row at a time (transactional processing)

[dandevoe]

Hello all,

This may seem like a very "dumb" question, but I decided to take the risk of being mocked & ridiculed...

I know how to perform "mass updates" in SQL, but haven't experienced the need to perform "transactional" processing - one row at a time.

I need to write a program that connects to an MS SQL 2012 server, retrieve records, recalculate the data in a field (column) & update on the MS SQL server, one row at a time.

I am able to connect to the table & read in records, utilizing the jtds driver.

The flow of the program will go something like this:
* Select records
* for each row, determine quantity available
* if calculated quantity available <> quantity available that is stored in the table row that is being processed, update quantity available

I am only reading one table, so there are no joins involved.

Any thoughts/advice?

Thanks

[DeadManWalks]

Re: Updating data in one row at a time (transactional processing)

How do you calculate the Quantity available.

And to just confirm , this is just one table>

if so sounds like a simple

Update mytable set mycolumn =
case of MyCalcuatedQTY <> MyStoredQty then XXX else YYYY End
where MyCalcuatedQTY <> MyStoredQty

Ish anyway.

[dandevoe]

Re: Updating data in one row at a time (transactional processing)

Yes, it is one table.

I have an old program (not a stored procedure) that needs to be called for each row, which computes the quantity available for the item number that is passed in.

So I essentially want to read the entire contents of a table row-by-row, determine quantity available for an item, and update the column in that row.

Thanks

[GLS400]

Re: Updating data in one row at a time (transactional processing)

Hi Dan:

Definately not the expert here .... (I'm a noobie at sql)...

Check out this thread ..... it has most of what your are looking for:

http://www.code400.com/forum/showthread.php/11929-Updating-fields-from-a-file-in-sqlrpgle

Key points:
1. declare cursor....for update
2. open cursor
3. fetch
4. execute your program
5. update where current of cursor
repeat 3/4& 5 until end

Be sure to set commitment control to NONE

Best of Luck
GLS

[dandevoe]

Re: Updating data in one row at a time (transactional processing)

Part of the issue that I'm dealing with is this is not utilizing a table/file that is native on our IBM i - this is utilizing a MS SQL 2012 server, directly from RPG, with the JDBC service program. So declaring cursors, etc. (including setting source type to SQLRPGLE) isn't done.

I'm utilizing the jtds driver, and am just having trouble getting a handle around the syntax to update one row at a time.

Scott Klement wrote an excellent article on how to utilize the jtds driver from with RPG, but I cannot find an example that emulates what I'm attempting to do.

I'll keep digging, and when I eventually figure it out, I'll post sample code.

[jamief]

Re: Updating data in one row at a time (transactional processing)

Thanks for doing all the heavy lifting, looking forward to the finished product.

Jamie

[dandevoe]

Re: Updating data in one row at a time (transactional processing)

I was unable to figure out a way to perform the processing in the way that I described above, but came up with an alternative (that seems "messy", but works).

Essentially, I am obtaining my first result set, processing the result set one row at a time, if I need to update a column in that row, I am executing a separate update / set statement, based on the primary key (ProductVariantID) for the row that I am currently processing.

Here is some sample code (proprietary logic removed) - utilizing Scott Klement's JDBC service program & jtds-1.3.0.jar):

       prop = JDBC_Properties();
       JDBC_setProp(prop: 'user'         : 'user-id');
       JDBC_setProp(prop: 'password'     : 'top-secret-password');
       JDBC_setProp(prop: 'databaseName' : 'blah');

       conn = JDBC_ConnProp('net.sourceforge.jtds.jdbc.Driver'
                           :'jdbc:jtds:sqlserver:/url.to.the.database.com:1433'
                           : prop );
       JDBC_freeProp(prop);

       if (conn = *NULL);
          dsply 'Unable to connect to database';
          return;
       endif;

       dsply 'Connection established to database';

       // Prepare SQL statement string to select item numbers & quantities
       prepstm1 = JDBC_prepStmt(conn:
                 'SELECT ProductVariantID, Sku, Inventory ' +
                 'FROM [Revindex_Storefront_ProductVariant] ' +
                 'WHERE(contition1 = ?) and (condition2 = ?)' +
                 'ORDER BY sku');
       if (prepstm1 = *NULL);
           jdbc_close(conn);
           return;
       endif;

       // Set SQL parameter values
       JDBC_SetString(prepstm1: 1: 'some-value');
       JDBC_SetString(prepstm1: 2: 'some-other-value');

       // Query the database
       rs = jdbc_ExecPrepQry(prepstm1);

       dow (jdbc_nextRow(rs));
          ProductID = %int(jdbc_getCol(rs: 1));
          sku       = jdbc_getCol(rs: 2);
          Quantity  = %int(jdbc_getCol(rs: 3));
         
          // do some processing to see if quantity needs to be updated - if it does
          NewQuantity = [whatever];         // Set new quantity
          if (Quantity = NewQuantity);
             iter;
          endif;

          prepstm2 = JDBC_prepStmt(conn:
                    'UPDATE [Revindex_Storefront_ProductVariant] ' +
                    'SET Inventory = ? ,' +
                    '    UpdateDate = ? ' +
                    'WHERE(ProductVariantID = ?)');
          if (prepstm2 = *NULL);
              jdbc_close(conn);
          endif;

          // Set SQL parameter values
          JDBC_SetInt      (prepstm2: 1: NewQuantity);
          JDBC_SetTimeStamp(prepstm2: 2: %timestamp());
          JDBC_SetInt      (prepstm2: 3: VariantID);
          rc = JDBC_ExecPrepUpd(prepstm2);
          if (rc < 0);
             ErrMsg = 'Execute Prepared Failed!';
          endif;
          if (prepstm2 = *NULL);
             jdbc_close(conn);
          endif;

          // Set SQL parameter values
          JDBC_SetInt      (prepstm2: 1: NewQuantity);
          JDBC_SetTimeStamp(prepstm2: 2: %timestamp());
          JDBC_SetInt      (prepstm2: 3: VariantID);
          rc = JDBC_ExecPrepUpd(prepstm2);
          if (rc < 0);
             ErrMsg = 'Execute Prepared Failed!';
          endif;

          JDBC_FreePrepStmt(prepstm2);          
       enddo;

       JDBC_FreeResult(rs);
       JDBC_FreePrepStmt(prepstm1);
       JDBC_Close(conn);