| PHP_SAPHANA.php |
|---|
<?php
$conn = odbc_connect("HANA_KT_SYS","SYSTEM","manager", SQL_CUR_USE_ODBC);
if (!($conn)) {
echo "<p>Connection to DB via ODBC failed: ";
echo odbc_errormsg ($conn );
echo "</p>\n";
}
else{
if(isset($_POST["CARRID"]) == false)
{
$sql = "SELECT CARRID, CARRNAME FROM SFLIGHT.SCARR
WHERE MANDT = 300";
$rs = odbc_exec($conn,$sql);
print("<DIV ALIGN='CENTER'>");
print("<H1>SAP HANA from PHP</H1>");
print("<FORM NAME='Get_Data'
ACTION='$_SERVER[PHP_SELF]' METHOD='POST'>");
print("<SELECT NAME='CARRID'>");
while($row = odbc_fetch_array($rs)){
$carrid = $row["CARRID"];
$carrname = $row["CARRNAME"];
print("<OPTION VALUE='$carrid'>$carrname");
}
print("</SELECT>");
print("<INPUT TYPE='SUBMIT' VALUE='Get Data'>");
print("</FORM>");
print("</DIV>");
}
else{
$carrid_param = $_POST["CARRID"];
$sql = "SELECT * FROM \"_SYS_BIC\".\"blag/AV_FLIGHTS\"
WHERE CARRID = '$carrid_param'";
$rs = odbc_exec($conn,$sql);
print("<DIV ALIGN='CENTER'><TABLE BORDER=1>");
print("<TR><TH>MANDT</TH>
<TH>CARRID</TH>
<TH>CONNID</TH>
<TH>COUNTRYFR</TH><TH
>CITYFROM</TH>
<TH>AIRPFROM</TH>
<TH>COUNTRYTO</TH>
<TH>CARRNAME</TH>
<TH>DISTANCE</TH></TR>");
while($row = odbc_fetch_array($rs)){
$mandt = $row["MANDT"];
$carrid = $row["CARRID"];
$connid = $row["CONNID"];
$countryfr = $row["COUNTRYFR"];
$cityfrom = $row["CITYFROM"];
$airpfrom = $row["AIRPFROM"];
$countryto = $row["COUNTRYTO"];
$carrname = $row["CARRNAME"];
$distance = $row["DISTANCE"];
print("<TR><TD>$mandt</TD><TD>$carrid</TD>
<TD>$connid</TD><TD>$countryfr</TD>
<TD>$cityfrom</TD><TD>$airpfrom</TD>
<TD>$countryto</TD><TD>$carrname</TD>
<TD>$distance</TD></TR>");
}
print("</TABLE>");
print("<A HREF='PHP_SAPHANA.php'>Go Back</A></DIV>");
}
}
?>
|
| ZERP_TO_HANA |
|---|
*&---------------------------------------------------------------------*
*& Report ZERP_TO_HANA *
*&---------------------------------------------------------------------*
*& Author: Alvaro "Blag" Tejada Galindo. *
*& Developer Experience *
*& Company: SAP Labs Montreal. *
*& Date: June 04, 2012. *
*&---------------------------------------------------------------------*
*& This program comes with no warranty. Use it at your own risk. *
*& This is just a personal project no aimed for productive *
*& environments and not sponsored or supported by SAP. *
*& I'm not responsible for any caused damage. *
*&---------------------------------------------------------------------*
*& Reviewed on: December 11, 2012. *
*& Reviewer: Alvaro "Blag" Tejada Galindo. *
*& Reason: Definition of the Data download structure. *
*&---------------------------------------------------------------------*
*& Reviewed on: January 11, 2013. *
*& Reviewer: Alvaro "Blag" Tejada Galindo. *
*& Reason: Definition of the Struct/Data download structure. *
*&---------------------------------------------------------------------*
*& Reviewed on: January 13, 2013. *
*& Reviewer: Alvaro "Blag" Tejada Galindo. *
*& Reason: Check the Outputlen of the Domain. *
*&---------------------------------------------------------------------*
REPORT ZERP_TO_HANA.
TYPES: BEGIN OF TY_DD03L,
FIELDNAME TYPE DD03L-FIELDNAME,
POSITION TYPE DD03L-POSITION,
KEYFLAG TYPE DD03L-KEYFLAG,
ROLLNAME TYPE DD03L-ROLLNAME,
DATATYPE TYPE DD03L-DATATYPE,
LENG TYPE DD03L-LENG,
DECIMALS TYPE DD03L-DECIMALS,
DOMNAME TYPE DD03L-DOMNAME,
END OF TY_DD03L.
TYPES: BEGIN OF TY_DD04L,
ROLLNAME TYPE DD04L-ROLLNAME,
DOMNAME TYPE DD04L-DOMNAME,
OUTPUTLEN TYPE DD04L-OUTPUTLEN,
END OF TY_DD04L.
TYPES: BEGIN OF TY_LINES,
LINE TYPE STRING,
END OF TY_LINES.
TYPES: BEGIN OF TY_TYPES,
ERP TYPE STRING,
HANA TYPE STRING,
END OF TY_TYPES.
DATA: T_DD03L TYPE TABLE OF TY_DD03L,
T_DD04L TYPE TABLE OF TY_DD04L,
T_LINES TYPE TABLE OF TY_LINES,
T_TYPES TYPE TABLE OF TY_TYPES.
DATA: V_FILENAME TYPE STRING.
FIELD-SYMBOLS: <FS_DD03L> LIKE LINE OF T_DD03L,
<FS_DD04L> LIKE LINE OF T_DD04L,
<FS_LINES> LIKE LINE OF T_LINES,
<FS_TYPES> LIKE LINE OF T_TYPES.
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-T01.
PARAMETERS:
P_SCHEMA(12) TYPE C OBLIGATORY,
P_TABLE TYPE DATABROWSE-TABLENAME OBLIGATORY,
P_FOLDER TYPE STRING OBLIGATORY,
P_STRUC RADIOBUTTON GROUP RDN DEFAULT 'X',
P_DATA RADIOBUTTON GROUP RDN.
SELECTION-SCREEN END OF BLOCK B1.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FOLDER.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>DIRECTORY_BROWSE
EXPORTING
WINDOW_TITLE = 'Select a Folder'
INITIAL_FOLDER = 'C:\'
CHANGING
SELECTED_FOLDER = P_FOLDER.
START-OF-SELECTION.
PERFORM GET_TYPES.
IF P_STRUC EQ 'X'.
PERFORM GET_STRUCTURE USING P_TABLE.
ELSE.
PERFORM GET_DATA USING P_TABLE.
ENDIF.
*&---------------------------------------------------------------------*
*& Form GET_TYPES *
*&---------------------------------------------------------------------*
FORM GET_TYPES.
SELECT FIELDNAME POSITION KEYFLAG ROLLNAME
DATATYPE LENG DECIMALS DOMNAME
INTO TABLE T_DD03L
FROM DD03L
WHERE TABNAME EQ P_TABLE.
SORT T_DD03L BY POSITION ASCENDING.
SELECT ROLLNAME DOMNAME OUTPUTLEN
INTO TABLE T_DD04L
FROM DD04L
FOR ALL ENTRIES IN T_DD03L
WHERE ROLLNAME EQ T_DD03L-ROLLNAME
AND DOMNAME EQ T_DD03L-DOMNAME.
"NVARCHAR
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'CLNT'.
<FS_TYPES>-HANA = 'NVARCHAR'.
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'CHAR'.
<FS_TYPES>-HANA = 'NVARCHAR'.
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'NUMC'.
<FS_TYPES>-HANA = 'NVARCHAR'.
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'UNIT'.
<FS_TYPES>-HANA = 'NVARCHAR'.
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'CUKY'.
<FS_TYPES>-HANA = 'NVARCHAR'.
"INTEGER
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'INT4'.
<FS_TYPES>-HANA = 'INTEGER'.
"DECIMAL
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'QUAN'.
<FS_TYPES>-HANA = 'DECIMAL'.
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'DEC'.
<FS_TYPES>-HANA = 'DECIMAL'.
"FLOAT
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'FLTP'.
<FS_TYPES>-HANA = 'FLOAT'.
"TINYINT
APPEND INITIAL LINE TO T_TYPES ASSIGNING <FS_TYPES>.
<FS_TYPES>-ERP = 'INT1'.
<FS_TYPES>-HANA = 'TINYINT'.
ENDFORM. " GET_TYPES
*&---------------------------------------------------------------------*
*& Form GET_STRUCTURE *
*&---------------------------------------------------------------------*
FORM GET_STRUCTURE USING P_TABLE.
DATA: PKEY TYPE STRING,
L_TYPE TYPE STRING.
CONCATENATE P_FOLDER '\' P_TABLE '_STRUCT.txt'
INTO V_FILENAME.
APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
CONCATENATE 'CREATE COLUMN TABLE' P_SCHEMA
INTO <FS_LINES>-LINE SEPARATED BY SPACE.
CONCATENATE <FS_LINES>-LINE '."' P_TABLE '" (' INTO
<FS_LINES>-LINE.
LOOP AT T_DD03L ASSIGNING <FS_DD03L>.
FIND REGEX '\.' IN <FS_DD03L>-FIELDNAME.
IF SY-SUBRC EQ 0.
CONTINUE.
ENDIF.
APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
READ TABLE T_TYPES ASSIGNING <FS_TYPES>
WITH KEY ERP = <FS_DD03L>-DATATYPE.
L_TYPE = <FS_TYPES>-HANA.
PERFORM DELETE_ZEROS CHANGING <FS_DD03L>-LENG.
READ TABLE T_DD04L ASSIGNING <FS_DD04L>
WITH KEY ROLLNAME = <FS_DD03L>-ROLLNAME
DOMNAME = <FS_DD03L>-DOMNAME.
IF SY-SUBRC EQ 0 AND NOT <FS_DD04L> IS INITIAL.
PERFORM DELETE_ZEROS CHANGING <FS_DD04L>-OUTPUTLEN.
IF <FS_DD04L>-OUTPUTLEN GT <FS_DD03L>-LENG.
<FS_DD03L>-LENG = <FS_DD04L>-OUTPUTLEN.
ENDIF.
ENDIF.
PERFORM DELETE_ZEROS CHANGING <FS_DD03L>-DECIMALS.
CASE L_TYPE.
WHEN 'NVARCHAR' OR 'FLOAT' OR 'TINYINT'.
CONCATENATE L_TYPE '(' <FS_DD03L>-LENG ')'
INTO L_TYPE.
WHEN 'DECIMAL'.
CONCATENATE L_TYPE '(' <FS_DD03L>-LENG ',' <FS_DD03L>-DECIMALS ')'
INTO L_TYPE.
ENDCASE.
FIND REGEX '\/' IN <FS_DD03L>-FIELDNAME.
IF SY-SUBRC EQ 0.
CONCATENATE '"' <FS_DD03L>-FIELDNAME '"'
INTO <FS_DD03L>-FIELDNAME.
ENDIF.
CONCATENATE <FS_DD03L>-FIELDNAME L_TYPE
INTO <FS_LINES>-LINE SEPARATED BY SPACE.
CONCATENATE <FS_LINES>-LINE ',' INTO <FS_LINES>-LINE.
IF <FS_DD03L>-KEYFLAG EQ 'X'.
CONCATENATE PKEY '"' <FS_DD03L>-FIELDNAME '",'
INTO PKEY.
ENDIF.
ENDLOOP.
REPLACE REGEX ',\Z' IN PKEY WITH SPACE.
APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
CONCATENATE 'PRIMARY KEY (' PKEY '));'
INTO <FS_LINES>-LINE.
PERFORM DOWNLOAD_FILE USING V_FILENAME
T_LINES.
ENDFORM. " GET_STRUCTURE
*&---------------------------------------------------------------------*
*& Form GET_DATA *
*&---------------------------------------------------------------------*
FORM GET_DATA USING P_TABLE.
DATA: L_TABLE TYPE REF TO DATA,
L_LINE TYPE STRING,
L_LINEAUX TYPE STRING,
L_WHERE TYPE STRING,
L_TYPE TYPE STRING.
FIELD-SYMBOLS: <FS_TABLE> TYPE ANY TABLE,
<FS_TABLE_HEADER> TYPE ANY,
<FS_LINE>.
CONCATENATE P_FOLDER '\' P_TABLE '_DATA.txt'
INTO V_FILENAME.
CREATE DATA L_TABLE TYPE TABLE OF (P_TABLE).
ASSIGN L_TABLE->* TO <FS_TABLE>.
read table t_dd03l ASSIGNING <fs_dd03l>
with key domname = 'SPRAS'.
IF SY-SUBrC EQ 0.
CONCATENATE <fs_dd03l>-FIELDNAME 'EQ ''E''' INTO L_WHERE
SEPARATED BY SPACE.
SELECT *
FROM (P_TABLE)
INTO TABLE <FS_TABLE>
WHERE (L_WHERE).
ELSE.
SELECT *
FROM (P_TABLE)
INTO TABLE <FS_TABLE>.
ENDIF.
LOOP AT <FS_TABLE> ASSIGNING <FS_TABLE_HEADER>.
APPEND INITIAL LINE TO T_LINES ASSIGNING <FS_LINES>.
CONCATENATE 'insert into "' P_SCHEMA '"."' P_TABLE '" values(' into <FS_LINES>-LINE.
LOOP AT T_DD03L ASSIGNING <FS_DD03L>.
FIND REGEX '\.' IN <FS_DD03L>-FIELDNAME.
IF SY-SUBRC EQ 0.
CONTINUE.
DELETE T_LINES FROM <FS_LINES>.
ENDIF.
CONCATENATE '<FS_TABLE_HEADER>-' <FS_DD03L>-FIELDNAME
INTO L_LINE.
ASSIGN (L_LINE) TO <FS_LINE>.
MOVE <FS_LINE> TO L_LINEAUX.
CONDENSE L_LINEAUX NO-GAPS.
READ TABLE T_TYPES ASSIGNING <FS_TYPES>
WITH KEY ERP = <FS_DD03L>-DATATYPE.
L_TYPE = <FS_TYPES>-HANA.
CASE L_TYPE.
WHEN 'NVARCHAR'.
CONCATENATE <FS_LINES>-LINE '''' L_LINEAUX ''',' into <FS_LINES>-LINE.
WHEN 'DECIMAL' OR 'INTEGER' OR 'TINYINT' OR 'FLOAT'.
CONDENSE L_LINEAUX NO-GAPS.
CONCATENATE <FS_LINES>-LINE L_LINEAUX ',' into <FS_LINES>-LINE.
ENDCASE.
ENDLOOP.
REPLACE REGEX ',\Z' IN <FS_LINES>-LINE WITH ');'.
ENDLOOP.
PERFORM DOWNLOAD_FILE USING V_FILENAME
T_LINES.
ENDFORM. " GET_DATA
*&---------------------------------------------------------------------*
*& Form download_file *
*&---------------------------------------------------------------------*
FORM DOWNLOAD_FILE USING P_FILENAME
P_TABLE.
DATA: SIZE TYPE I.
CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD
EXPORTING
BIN_FILESIZE = SIZE
FILENAME = P_FILENAME
FILETYPE = 'ASC'
CHANGING
DATA_TAB = P_TABLE.
ENDFORM. "download_file
*&---------------------------------------------------------------------*
*& Form DELETE_ZEROS *
*&---------------------------------------------------------------------*
FORM DELETE_ZEROS CHANGING P_VALUE.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
INPUT = P_VALUE
IMPORTING
OUTPUT = P_VALUE.
ENDFORM. "DELETE_ZEROS
|
