SQL Server Connection and Data retrieving
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.
*&----------------------------------------------------------------------
*& 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
|
