Reading and Writing External Data.

It's been a fun couple of weeks for me short stay in the hospital my appendix decided that it had had enough and wanted to go somewhere else.

When I left work for my little stay in the hospital my company was installing enhancement pack 4 and after a week they were still having problems so they decided to back out the enhancement pack.

I would love to hear how other companies handle this process it just seemed to me to be a total nightmare. The entire development machine was down for a week.

When we started this process I was in the middle of writing a program that was going to read data from another ISeries and then update the data.
I was having an issue with the update and I must have updated my entry in DBCO and somehow I removed the trailing ";" from Connection Info which is not a good idea. For the next few day's I tried to get my connecting to work again without much help I contacted SAP and they played with it for about two days before they came back with "place a trailing ;" and all of a second everything works again.

I thought I would share the native SQL code I used to Delete/Update records on a remote ISeries.

First the Delete

* Check to see if connection is active  
  EXEC SQL.
    SET CONNECTION :con_name
  ENDEXEC.
  if sy-subrc <> 0.
* Connection not yet opened.
    EXEC SQL.
      CONNECT TO :con_name
    ENDEXEC.
  endif.
* Perform the delete nothing magical here just remember 
* to place a ":" in front of your abap variables.
  EXEC SQL.
    DELETE from svvndxp where company            = :local_company
                          and hs_vendor_number   = :local_hs
                          and sap_account_number = :local_sap
  ENDEXEC.
* Don't forget to Commit
  EXEC SQL.
    COMMIT
  ENDEXEC.
* I have been dropping my connection after all updates
* you don't need to I am sure it slows the program down 
* but I felt like being lazy.
  EXEC SQL.
    DISCONNECT :con_name
  ENDEXEC.

Now for the update.

* Same as with the delete check to see if the connection is open if not
* open it.  
  EXEC SQL.
    SET CONNECTION :con_name
  ENDEXEC.
  if sy-subrc <> 0.
    EXEC SQL.
      CONNECT TO :con_name
    ENDEXEC.
  endif.

* Update the data file.
* I'm using an ALV grid to display the records and I have editing turned
* on so I am using hidden fields on the grid to store the original value.
  local_hs       = global_svvndxp-hs_vendor_number.
  local_company  = global_svvndxp-company.
  local_sap      = global_svvndxp-sap_account_number.
  local_hcompany = global_svvndxp-hidden_company.
  local_hhs      = global_svvndxp-hidden_hs_vendor_number.
  local_hsap     = global_svvndxp-hidden_sap_account_number.
  EXEC SQL.
    update svvndxp set COMPANY            = :local_company,
                       HS_VENDOR_NUMBER   = :local_hs,
                       SAP_ACCOUNT_NUMBER = :local_sap
           where COMPANY            = :local_hcompany
             and HS_VENDOR_NUMBER   = :local_hhs
             and SAP_ACCOUNT_NUMBER = :local_hsap
  ENDEXEC.
* Commit transaction
  EXEC SQL.
    COMMIT
  ENDEXEC.
  EXEC SQL.
    DISCONNECT :con_name
  ENDEXEC. 

Well that's it again if you would like the whole program let me know and I would be happy to send it to you.