SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

SQL Server Connection and Data retrieving



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> SQL and Database Changes
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1639

PostPosted: Mon Mar 17, 2008 12:15 am    Post subject: SQL Server Connection and Data retrieving Reply with quote

Code:
REPORT z_mssql_tearsheet_page NO STANDARD PAGE HEADING MESSAGE-ID f5 LINE-SIZE 200.

*-- Initial OLE2 --*
INCLUDE ole2incl.

CONSTANTS: comma(1) TYPE c VALUE ';',
           single_quote(1) TYPE c VALUE '''',
           double_quote(1) TYPE c VALUE '"'.

CONSTANTS: _n_provider_(20) VALUE 'Provider=SQLOLEDB',
           _n_dsn_(20) VALUE 'Data Source=',
           _n_database_(30) VALUE 'Initial Catalog=',
           _n_security_(30) VALUE 'Integrated Security=SSPI'.

CONSTANTS: _provider_(10) VALUE 'MSDASQL',
           _driver_(30) VALUE 'driver={SQL Server}',
           _server_(10) VALUE 'server=',
           _database_(10) VALUE 'database=',
           _winnt_auth_(30) VALUE 'Trusted_Connection=Yes'.

TYPES: BEGIN OF t_order, " Order collection
         avm_nr TYPE jhak-avm_nr,
         motiv TYPE jhamot-motiv,
         s_termin TYPE jhaea-s_termin,
         actual_page TYPE zzl8000import-actual_page,
         belegeinh TYPE jhaea-belegeinh,
END OF t_order,

BEGIN OF v_order, " Order assignment
         avm_nr(10) TYPE n,
         motiv(6) TYPE n,
         s_termin TYPE d,
         actual_page(4) TYPE n,
         belegeinh TYPE jhaea-belegeinh,
END OF v_order,

BEGIN OF t_update, " ZZL8000Import update key table
         avm_nr TYPE jhaea-avm_nr,
         pos_nr TYPE jhaea-pos_nr,
         upos_nr TYPE jhaea-upos_nr,
         ein_nr TYPE jhaea-ein_nr,
         actual_page TYPE zzl8000import-actual_page,
END OF t_update.

* OLE2 objects
DATA: objconn TYPE ole2_object, " Connection instance
      objrs TYPE ole2_object, " Record set instance
* objFields type ole2_object, " Field collection
      objfield TYPE ole2_object. " Field

DEFINE check_ole_error.
  case sy-subrc.
    when 0.
    when 1.
      message e999 with sy-msgli.
    when others.
      message e999 with &1.
  endcase.
END-OF-DEFINITION.

DATA: it_order TYPE t_order OCCURS 0,
* it_update type t_update occurs 0,
         wa_update TYPE t_update,
         wa_order TYPE t_order,
         wa_v_order TYPE v_order,
         it_bu TYPE jhaea-belegeinh OCCURS 10 WITH HEADER LINE,
         it_basic_bu TYPE jhaea-belegeinh OCCURS 10 WITH HEADER LINE.

*----------------------------------------------------------------------*
* GUI
PARAMETERS: rundate LIKE sy-datum DEFAULT sy-datum OBLIGATORY.
PARAMETERS: server(30) TYPE c DEFAULT 'torodev1' OBLIGATORY,
            database(30) TYPE c DEFAULT 'thePaper' OBLIGATORY LOWER CASE.
SKIP.
PARAMETERS: o2file TYPE c AS CHECKBOX, "output TO file
            o2append TYPE c AS CHECKBOX DEFAULT space,
            o_file TYPE rlgrap-filename DEFAULT 'c:\thePaper.txt'.

PARAMETERS: o2screen TYPE c AS CHECKBOX DEFAULT 'X', "output TO screen
            update TYPE c AS CHECKBOX. "UPDATE database

DATA: h_mask(20) TYPE c VALUE ',*.txt ,*.txt.'.
DATA: h_path(20) TYPE c.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR o_file.

  CALL FUNCTION 'WS_QUERY'
    EXPORTING
      query     = 'CD' "// Current
      directory
    IMPORTING
      return    = h_path.

  CALL FUNCTION 'WS_FILENAME_GET'
    EXPORTING
      def_filename     = 'c:\thePaper.txt'
      def_path         = h_path
      mask             = h_mask
      mode             = 'O'
      title            = 'Open a text file'
    IMPORTING
      filename         = o_file
    EXCEPTIONS
      inv_winsys       = 1
      no_batch         = 2
      selection_cancel = 3
      selection_error  = 4
      OTHERS           = 5.

*----------------------------------------------------------------------*
* Process
START-OF-SELECTION.

* 1. open an SQL connection
  PERFORM sql_open USING server database. " MSDASQL

*perform OLEDB_open using server database. " SQLOLEDB

* 2. collect the recordset into internal table
  PERFORM sql_collectorders.

* 3. clean up for OLE objects
  PERFORM sql_cleanup.

* 4. dump to the screen
  IF o2screen EQ 'X'.
    PERFORM output2screen.
  ENDIF.

* 5. dump to a text file
  IF o2file EQ 'X' AND NOT o_file IS INITIAL.
    PERFORM output2file USING o_file o2append.
  ENDIF.

* 6. update the table zzl8000import
  IF update EQ 'X'.
    PERFORM update_page.
  ENDIF.


************************************************************************
* --- FORM ---
************************************************************************
*&---------------------------------------------------------------------*
*& SQL_open - open an sql server connection
*&---------------------------------------------------------------------*
FORM sql_open USING value(i_server) value(i_database).

*----------------------------------------------------------------------*
* Connection String
*----------------------------------------------------------------------*
* Use MSDASQL for a SQL Server connection
  DATA: connectionstring(128).
  CONCATENATE _driver_ comma
              _server_ i_server comma
              _winnt_auth_ comma
              _database_ i_database
     INTO connectionstring.

* Create an ADODB object
  CREATE OBJECT objconn 'ADODB.Connection'.
  check_ole_error 'Error in creating the SQL Server connection'.

* Set OLEDB provider
  SET PROPERTY OF objconn 'Provider' = _provider_.
  check_ole_error 'Error in setting property of Provider'.

* Set OLEDB connection string
  SET PROPERTY OF objconn 'ConnectionString' = connectionstring.
  check_ole_error 'Error in setting property of ConnectionString'.

* Open the connection
  CALL METHOD OF objconn 'Open'.
  check_ole_error 'Error in opening the connection'.

ENDFORM. " SQL_open


*&---------------------------------------------------------------------*
*& OLEDB_open - open an sql server connection with oledb for sql server
*&---------------------------------------------------------------------*
FORM oledb_open USING value(i_server)
                      value(i_database).

  DATA: connectionstring(128).
  CONCATENATE _n_provider_ comma
              _n_dsn_ i_server comma
              _n_database_ i_database comma
              _n_security_ comma
    INTO connectionstring.

* Create an ADODB object
  CREATE OBJECT objconn 'ADODB.Connection'.
  check_ole_error 'Error in creating the SQL Server OLEDB connection'.

* Set OLEDB connection string
  SET PROPERTY OF objconn 'ConnectionString' = connectionstring.
  check_ole_error 'Error in setting property of ConnectionString'.

* Open the connection
  CALL METHOD OF objconn 'Open'.
  check_ole_error 'Error in opening the connection'.

ENDFORM. " OLEDB_open


*&---------------------------------------------------------------------*
*& SQL_collectOrders - collect all the order from
tearsheet table
*&---------------------------------------------------------------------*
form sql_collectorders.

DATA: sql(400).

* The sql clause we use
CONCATENATE 'Select t.harrisItemName,'
            ' convert(char(10), t.tearDate, 112),'
            ' t.tearAdZoneList, p.pageNumber '
            ' From TearSheets t inner join pages p '
            ' on t.harrisName=p.realHarrisName '
            ' Where t.anyOldPage <> 1 and '
            ' t.tearDate = '
            single_quote
            rundate
            single_quote
            INTO sql.

* Create a Recordset by executing an SQL statement.
CALL METHOD OF objconn 'Execute' = objrs
  EXPORTING #1 = sql.
check_ole_error 'Error in running the method of Execute'.

DATA: w_harrisitemname(50),
      w_teardate TYPE d,
      w_tearadzonelist(200),
      w_tearpagenumber TYPE i.

* Loop through the recordset.
DATA: rs_eof TYPE i.

* Get EOF of the recordset
GET PROPERTY OF objrs 'EOF' = rs_eof.
check_ole_error 'Error in getting property of EOF'.

REFRESH it_order.
* Retrieve the recordset
WHILE rs_eof NE 1.
  " Ad#(8) + Ad Spec#(2)
  CALL METHOD OF objrs 'fields' = objfield
    EXPORTING
    #1 = 0.
  GET PROPERTY OF objfield 'Value' = w_harrisitemname.
  check_ole_error 'Error in getting harrisName'.

  " Pubishing Date(8)
  CALL METHOD OF objrs 'fields' = objfield
    EXPORTING
    #1 = 1.
  GET PROPERTY OF objfield 'Value' = w_teardate.
  check_ole_error 'Error in getting tearDate'.

  " Basic booking unit collection(10)
  CALL METHOD OF objrs 'fields' = objfield
    EXPORTING
    #1 = 2.
  GET PROPERTY OF objfield 'Value' = w_tearadzonelist.
  check_ole_error 'Error in getting tearAdZoneList'.

  " Page Number(4)
  CALL METHOD OF objrs 'fields' = objfield
    EXPORTING
    #1 = 3.
  GET PROPERTY OF objfield 'Value' = w_tearpagenumber.
  check_ole_error 'Error in getting tearPageNumber'.

  " Assignments
  wa_v_order-avm_nr = w_harrisitemname(8).
  wa_v_order-motiv = w_harrisitemname+8(2).
  wa_v_order-s_termin = w_teardate.
  wa_v_order-actual_page = w_tearpagenumber.

  " Copy to the working area
  MOVE-CORRESPONDING wa_v_order TO wa_order.

  " Multiple schedule lines
  REFRESH it_bu.
  CLEAR it_bu.
  SPLIT w_tearadzonelist AT '+' INTO TABLE it_bu.

  DATA: basic_bu TYPE jjtbe-xbasis_be.

  LOOP AT it_bu. " each schedule line
    PERFORM check_basic_bu USING it_bu CHANGING basic_bu.

    IF basic_bu EQ 'X'. " basic BU
      wa_order-belegeinh = it_bu.
      APPEND wa_order TO it_order.
    ELSE. " Comp BU
      REFRESH it_basic_bu.
      SELECT belegeinhu FROM jjtbehie INTO TABLE it_basic_bu
        WHERE be_hie_typ = '01'
          AND be_hie_ver = '0001'
          AND belegeinho = it_bu.

      LOOP AT it_basic_bu.
        wa_order-belegeinh = it_basic_bu.
        APPEND wa_order TO it_order.
      ENDLOOP.
    ENDIF.
  ENDLOOP.

* Move to next record
  CALL METHOD OF objrs 'MoveNext'.
  check_ole_error 'Error in calling the method of MoveNext'.

* Check the End Of File
  GET PROPERTY OF objrs 'EOF' = rs_eof.
  check_ole_error 'Error in getting property of EOF'.
ENDWHILE.

* Sort the internal table and remove the duplicated lines
SORT it_order BY avm_nr motiv s_termin belegeinh.
DELETE ADJACENT DUPLICATES FROM it_order.

ENDFORM. " SQL_collectOrders.


*&----------------------------------------------------------------------
*& SQL_cleanup - clean up the objects
*&----------------------------------------------------------------------
FORM sql_cleanup.

* Close the recordset
  CALL METHOD OF objrs 'Close'.

* Close the connection.
  CALL METHOD OF objconn 'Close'.

* Free the objects
  FREE OBJECT objrs.
  FREE OBJECT objconn.

ENDFORM. " SQL_cleaup

*&----------------------------------------------------------------------
*& output2File - dump to a text file
*&----------------------------------------------------------------------
FORM output2file USING value(o_file) TYPE rlgrap-filename
                       value(b_append) TYPE c.

* Download the internal table into a flat file
  DATA file_name TYPE string.
  file_name = o_file.

* Download the file to the local machine
  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      filename      = file_name
      filetype      = 'ASC'
      append        = b_append
      header_length = 0
      read_by_line  = 'X'
    TABLES
      data_tab      = it_order.

ENDFORM.                    "output2file

*&----------------------------------------------------------------------
*& output2screen - dump to the console
*&----------------------------------------------------------------------
FORM output2screen.

* Heading
  WRITE: 10 sy-uline(57).
  WRITE: /10 sy-vline, ' Order ', 24 sy-vline,
  25 'Motiv', 31 sy-vline,
  32 ' S_termin', 45 sy-vline,
  46 ' Page#', 55 sy-vline,
  56 'Book Unit', 66 sy-vline.
  WRITE: /10 sy-uline(57).

* Contents
  LOOP AT it_order INTO wa_order.
* Display
    WRITE: /10 sy-vline, wa_order-avm_nr, 24 sy-vline,
    25 wa_order-motiv, 31 sy-vline,
    32 wa_order-s_termin, 45 sy-vline,
    46 wa_order-actual_page, 55 sy-vline,
    56 wa_order-belegeinh, 66 sy-vline.
    WRITE: /10 sy-uline(57).
  ENDLOOP.

ENDFORM. " output2Screen

*&---------------------------------------------------------------------*
*& Check_basic_bu - check whether the BU is basic unit
*&---------------------------------------------------------------------*
FORM check_basic_bu USING value(bu) TYPE jjtbe-beleinh
                 CHANGING bu_basic_flag TYPE jjtbe-xbasis_be.

  SELECT SINGLE xbasis_be INTO bu_basic_flag
  FROM jjtbe WHERE beleinh = bu.

ENDFORM. " check_basic_bu

*&---------------------------------------------------------------------*
*& Update_page - update the page in table zzl8000import
*&---------------------------------------------------------------------*
FORM update_page.

ENDFORM. " update_page
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> SQL and Database Changes All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG.
SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG.
Every effort is made to ensure content integrity. Use information on this site at your own risk.