?
Solved

Concatenate the column's previous and changed values and put it into a string.

Posted on 2006-05-24
7
Medium Priority
?
418 Views
Last Modified: 2013-12-26
I have a data window (PB9) contains 50 columns retrieving from a sp.  In that window, suppose user did some changes in some columns. My task is to find out that changes and send it as a string for update sp. The update procedure is a COBOL Procedure. So only thing I need to do is to send it as a string (540) as shown as the below format.

Column_description (String(15))+ previous_value String(10)+changed_value String(10)+;

The total length of the string for one column is(15+10+10+1) = 36
So it should be like  
Trans_amtXXXXXXxxxx260.00xxxx342.00;

I have to send 15 column values as above format.  So the final string length is 15 (columns) * 36 (formatted of each column length) = 540


It should be like
Trans_amtXXXXXXxxxx260.00xxxx342.00;
State_taxXXXXXXxxx3100.00xxxx3200.00;
City_amtXXXXXXxxx5260.00xxxx5342.00;
Other_amtXXXXXXxxxx160.00xxxx120.00;

So how can I find out the previous value, changed value  and concatenate these values into one string.

Thanks for your advance help.
0
Comment
Question by:venkat4sv
  • 3
  • 3
7 Comments
 
LVL 6

Expert Comment

by:tr1l0b1t
ID: 16760236
Hi,

- to get Original value use : dw_1.GetItemString(row,"mycol",Primary!, TRUE)
- to get Current value simply : dw_1.GetItemString(row,"mycol")
- to concat strings use '+'

Example for a numeric col called "amount" :

String ls_result        // Here we'll store the final string
String ls_colname
String ls_amt
Long ll_row

// ...

ls_colname = "amount"
If dw_1.GetItemSatus(ll_row, ls_colname, Primary!) = Modified! Then
    // Column name filled with spaces at right
    ls_result += ls_colname + Fill(" ", 15 - Len(ls_colname))

    // Original Value filled with spaces at left
    ls_amt = String( dw_1.GetItemNumber(ll_row,ls_colname,Primary!,TRUE), "#,##0.00")
    ls_result += Fill(" ", 10 - Len(ls_amt)) + ls_amt

    // Current Value filled with spaces at left
    ls_amt = String( dw_1.GetItemNumber(ll_row,ls_colname), "#,##0.00")
    ls_result += Fill(" ", 10 - Len(ls_amt)) + ls_amt

    // ';' and NewLine
    ls_result += ";~r~n"
End If

// ...

Hope it helps
Regards
0
 

Author Comment

by:venkat4sv
ID: 16763888
In which event it will fit and I need to find out of 15 columns with different data types like sting, decimal and number.  Shall I use case st for each column in itemchanged event or which way is good. Your code is very good and how can I put it into a loop for all modified columns under which event? I appreciate if you give in detail.
Thanks
0
 
LVL 14

Expert Comment

by:sandeep_patel
ID: 16767248
Hi,

Try this...

long ll_count,ll_count1
string ls_coltype,ls_format,ls_colname
string ls_org,ls_new,ls_result

For ll_count1 = 1 to dw_1.rowcount()
      for ll_count = 1 to Long(dw_1.object.datawindow.column.count)
            If dw_1.GetItemStatus(ll_count1,ll_count,Primary!) = DataModified! Then
                  ls_coltype = Left(dw_1.Describe("#"+string(ll_count)+".coltype"),3)
                  ls_colname = dw_1.Describe("#"+string(ll_count)+".dbname")
            
                  Choose Case ls_coltype
                        Case 'cha'
                              ls_org = dw_1.GetItemString(ll_count1,ll_count,Primary!,TRUE)
                              ls_new = dw_1.GetItemString(ll_count1,ll_count)
                        Case 'int'
                              ls_org = String(dw_1.GetItemNumber(ll_count1,ll_count,Primary!,TRUE))
                              ls_new = String(dw_1.GetItemNumber(ll_count1,ll_count))
                        case 'dat'
                              // use getitemdatetime with suitable format
                        case 'lon'
                              // use getiemnumber with suitable format
                        case 'num'
                              // use getitemnumber with suitable format
                              
                        // Also add datatypes as per your requirement in this case
                        // see help of ColType datawindow property
                  End Choose
                  
                ls_result += ls_colname + Fill(" ", 15 - Len(ls_colname))
                  ls_result += Fill(" ", 10 - Len(ls_org)) + ls_org
                  ls_result += Fill(" ", 10 - Len(ls_new)) + ls_new
                  ls_result += ";~r~n"
            End If
      next
next
Messagebox('',ls_result)

you can create one function at window level and write this code in that function which returns value of ls_result
and just before calling your stored procedure call this function and pass the return value of ls_result to your stored procedure.

Regards,
Sandeep
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Accepted Solution

by:
tr1l0b1t earned 1000 total points
ID: 16784191
Hi venkat4sv,

Here you have a simpler code. Fill the array variable 'ls_colnames' with the columns
you need in output (no matter datatype). Data will be formatted as defined in DW.

String ls_colnames[] = {"Trans_amt", "State_tax", "City_amt", "Other_amt"}
String ls_format, ls_old, ls_new, ls_result
Long ll_col, ll_cols, ll_row, ll_rows, ll_colID

ll_rows = dw_1.RowCount()
ll_cols = UpperBound(ls_colnames)

For ll_row = 1 To ll_rows
      For ll_col = 1 to ll_cols
            ll_colID = Long(dw_1.Describe(ls_colnames[ll_col] + ".ID"))
            If dw_1.GetItemStatus(ll_row, ll_colID, Primary!) = DataModified! Then
                  ls_format = dw_1.GetFormat(ll_colID)
                  ls_old = String(dw_1.Object.Data.Primary.Original[ll_row, ll_colID], ls_format)
                  ls_new = String(dw_1.Object.Data.Primary.Current[ll_row, ll_colID], ls_format)
                  ls_result += ls_colnames[ll_col] + Fill(" ", 15 - Len(ls_colnames[ll_col]))
                  ls_result += Fill(" ", 10 - Len(ls_old)) + ls_old
                  ls_result += Fill(" ", 10 - Len(ls_new)) + ls_new
                  ls_result += ";~r~n"
            End If
      Next
Next

MessageBox("Result", ls_result)

As sandeep mentions, better if you isolate the generation in a function. And regarding events,
you should create 'ls_result' before datawindow update processing and call the procedure after.

Regards
0
 

Author Comment

by:venkat4sv
ID: 16787239
Hi,
I wrote a function and  calling before the update procedure. It is returning current values but not getting old values. Can you suggest if any modifications in your code. I appreciate your help.
Thanks,
0
 
LVL 6

Expert Comment

by:tr1l0b1t
ID: 16790839
Hi,

I've tested it and works fine. Original and current values only match
when it's a new row, so try to change the row status to NotModified!
just after dw retrieve (retrieveend event) :

      Long ll_rows, ll_row
      ll_rows = dw_1.RowCount()
      For ll_row = 1 To ll_rows
            // Must be done in two steps ..
            dw_1.SetItemStatus(ll_row, 0, Primary!, DataModified!)
            dw_1.SetItemStatus(ll_row, 0, Primary!, NotModified!)
      Next

Please, let me know what happens.
Regards
0
 

Author Comment

by:venkat4sv
ID: 16792371
Thank you for your time and solution. I got the result.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our object-oriented world the class is a minimal unit, a brick for constructing our applications. It is an abstraction and we know well how to use it. In well-designed software we are not usually interested in knowing how objects look in memory. …
Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question