Posted: Mon Mar 17, 2008 12:15 am Post subject: SQL Server Connection and Data retrieving
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.
*----------------------------------------------------------------------*
* 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).
* 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'.
" 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.
*&---------------------------------------------------------------------*
*& 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.
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.
