| 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
|
| MyClass.java |
|---|
package com.BlagTest;
import java.sql.*;
import java.util.ArrayList;
public class MyClass {
public ArrayList<String> getData(String p_carrid){
Connection connection = null;
ArrayList<String> carrid = new ArrayList<String>();
String data = "";
try{
Class.forName("com.sap.db.jdbc.Driver");
try{
connection =
DriverManager.getConnection("jdbc:sap://XX.XX.XXX.XXX:30115",
"SYSTEM","manager");
}
catch (SQLException e){
}
}
catch (ClassNotFoundException e){
}
if(connection != null){
try{
String sqlstring = "select CONNID, FLDATE, PRICE from
SFLIGHT.SFLIGHT where carrid = '" + p_carrid + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sqlstring);
while(rs.next()){
data = rs.getString("CONNID") + "/" +
rs.getString("FLDATE") + "/" +
rs.getString("PRICE");
carrid.add(data);
}
}
catch(SQLException e){
}
}
return carrid;
}
}
|
| BlagTest.java |
|---|
package com.BlagTest;
import android.app.Activity;
import android.os.Bundle;
import org.apache.cordova.*;
public class BlagTest extends DroidGap
{
private MyClass mc;
@Override
public void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
super.init();
mc = new MyClass();
super.appView.getSettings().setJavaScriptEnabled(true);
super.appView.addJavascriptInterface(mc, "MyCls");
super.loadUrl("file:///android_asset/www/index.html");
}
}
|
| index.html |
|---|
<html>
<head>
<title>SAP HANA from PhoneGap</title>
<meta name="viewport" content="width=device-width, initialscale=1.0"></meta>
<link rel="stylesheet" href="http://code.jquery.com/mobile/1.1.0/jquery.mobile-1.1.0.min.css"/>
<script src="http://code.jquery.com/jquery-1.7.1.min.js"></script>
<script src="http://code.jquery.com/mobile/1.1.0/jquery.mobile-1.1.0.min.js"></script>
<script>
function getData(){
var carridField = document.getElementById('carrid');
var getCarrid = carridField.value;
var myArrayList = window.MyCls.getData(getCarrid);
carridField.value = "";
$("#content").append("<ul id='list' data-role='listview' data-inset='true'</ul>");
$("#content").trigger("create");
for(var i = 0; i < myArrayList.size(); i++){
var array = "" + myArrayList.get(i);
array = array.split('/');
var _connid = array[0], _fldate = array[1], _price = array[2];
var list = "<li><p>CONNID: " + _connid + "</p><p>FLDATE: " +
_fldate + "</p><p>PRICE: " + _price + "</p></li>";
$("#list").append(list);
}
$("#list").listview("refresh");
}
</script>
</head>
<body>
<div data-role="page">
<div data-role="content" id="content">
<div align="center"><h1>SAP HANA from PhoneGap</h1></div>
Carrid: <input type="text" id="carrid" size="2"/>
<button id="submitCarrid" onClick="getData()">Submit</button>
</div>
</div>
</body>
</html>
|
| Field Name | Data Type | Length |
|---|---|---|
| DOCUMENT_ID | VARCHAR | 8 |
| YEAR | VARCHAR | 4 |
| AREA | VARCHAR | 2 |
| NAME | VARCHAR | 20 |
| LAST_NAME | VARCHAR | 20 |
| Field Name | Data Type | Length |
|---|---|---|
| DOCUMENT_ID | VARCHAR | 8 |
| YEAR | VARCHAR | 4 |
| AREA | VARCHAR | 2 |
| AMOUNT | VARCHAR |
| Doc_Header_Generator.py |
|---|
import random
import csv
names = ["Anne", "Gigi", "Juergen", "Ingo", "Inga", "Alvaro", "Mario",
"Julien", "Mike", "Michael", "Karin", "Rui", "John", "Rocky", "Sebastian",
"Kai-Yin", "Hester", "Katrin", "Uwe", "Vitaliy"]
last_names = ["Hardy", "Read", "Schmerder", "Sauerzapf", "Bereza", "Tejada",
"Herger", "Vayssiere", "Flynn", "Byczkowski", "Schattka",
"Nogueira", "Mayerhofer", "Ongkowidjojo", "Wieczorek", "Gau", "Hilbrecht",
"Staehr", "Kylau", "Rudnytskiy"]
area = ["001", "002", "003", "004", "005", "006", "007", "008", "009", "010"]
year = ["2000", "2001", "2002", "2003", "2004", "2005", "2006", "2007",
"2008", "2009", "2010", "2011", "2012"]
def Generate_File(pSchema, pNumber):
iNumber = 0
c = csv.writer(open("Doc_Header.csv", "wb"))
while iNumber < pNumber:
queries = []
r_doc_id = random.randrange(1, 999999)
r_names = random.randrange(0, 20)
r_lastnames = random.randrange(0, 20)
r_areas = random.randrange(0, 10)
r_years = random.randrange(0, 13)
iNumber += 1
queries.append(r_doc_id)
queries.append(year[r_years])
queries.append(str(area[r_areas]))
queries.append(names[r_names])
queries.append(last_names[r_lastnames])
c.writerow(queries)
num_files = input("How many records?: \n")
Generate_File(num_files)
|
| Doc_Detail_Generator.py |
|---|
import random
import csv
area = ["001", "002", "003", "004", "005", "006", "007", "008", "009", "010"]
year = ["2000", "2001", "2002", "2003", "2004", "2005", "2006", "2007",
"2008", "2009", "2010", "2011", "2012"]
def Generate_File(pSchema, pNumber):
iNumber = 0
c = csv.writer(open("Doc_Detail.csv", "wb"))
while iNumber < pNumber:
queries = []
r_doc_id = random.randrange(1, 999999)
r_areas = random.randrange(0, 10)
r_years = random.randrange(0, 13)
r_amount = random.randrange(0, 10000, 1)
iNumber += 1
queries.append(r_doc_id)
queries.append(year[r_years])
queries.append(str(area[r_areas]))
queries.append(r_amount)
c.writerow(queries)
num_files = input("How many records?: \n")
Generate_File(num_files)
|
| MySQL_Bottle_Documents.py |
|---|
from bottle import get, run
import mysql.connector
import time
@get('/show_query')
def show_form():
counter = 0
start = time.clock()
conn = mysql.connector.Connect(host='localhost', user='root',
password='root', database='P075400')
cur = conn.cursor()
query = '''SELECT A.DOCUMENT_ID, A.YEAR, A.AREA, SUM(AMOUNT)
FROM DOC_HEADER AS A INNER JOIN DOC_DETAIL AS B
WHERE A.DOCUMENT_ID = B.DOCUMENT_ID
AND A.YEAR = B.YEAR
AND A.AREA = B.AREA
GROUP BY DOCUMENT_ID, YEAR, AREA'''
cur.execute(query)
output = "<DIV ALIGN='CENTER'><TABLE BORDER='1'>"
output += "<TR BGCOLOR='#B9C9FE'>"
output += "<TH>Document</TH><TH>Year</TH>"
output += "<TH>Area</TH><TH>Amount</TH>"
output += "</TR>"
for row in cur:
counter += 1
document_id = str(row[0])
year = str(row[1])
area = str(row[2])
amount = str(row[3])
output += "<TR BGCOLOR='#E8EDFF'>"
output += '''<TD>%s</TD><TD>%s</TD>
<TD>%s</TD><TD>%s</TD>''' %
(document_id, year,area, amount)
output += "</TR>"
output += "</TABLE>"
end = time.clock()
time_taken = end - start
output += "<H1>SAP HANA - %s records in %s seconds</H1></DIV>"\
% (counter, time_taken)
return output
run(host='localhost', port=8080)
|
| PostgreSQL_Bottle_Documents.py |
|---|
from bottle import get, run
import psycopg2
import time
@get('/show_query')
def show_form():
counter = 0
start = time.clock()
conn = psycopg2.connect("dbname=P075400 user=postgres password=root")
cur = conn.cursor()
query = '''SELECT "DOC_HEADER"."DOCUMENT_ID", "DOC_HEADER"."YEAR",
"DOC_HEADER"."AREA", SUM("DOC_DETAIL"."AMOUNT") FROM
public."DOC_HEADER", public."DOC_DETAIL" WHERE
"DOC_HEADER"."DOCUMENT_ID" = "DOC_DETAIL"."DOCUMENT_ID"
AND "DOC_HEADER"."YEAR" = "DOC_DETAIL"."YEAR"
AND "DOC_HEADER"."AREA" = "DOC_DETAIL"."AREA"
GROUP BY "DOC_HEADER"."DOCUMENT_ID", "DOC_HEADER"."YEAR",
"DOC_HEADER"."AREA"'''
cur.execute(query)
output = "<DIV ALIGN='CENTER'><TABLE BORDER='1'>"
output += "<TR BGCOLOR='#B9C9FE'>"
output += "<TH>Document</TH><TH>Year</TH>"
output += "<TH>Area</TH><TH>Amount</TH>"
output += "</TR>"
for row in cur:
counter += 1
document_id = str(row[0])
year = str(row[1])
area = str(row[2])
amount = str(row[3])
output += "<TR BGCOLOR='#E8EDFF'>"
output += '''<TD>%s</TD><TD>%s</TD>
<TD>%s</TD><TD>%s</TD>''' %
(document_id, year, area, amount)
output += "</TR>"
output += "</TABLE>"
end = time.clock()
time_taken = end - start
output += "<H1>PostgreSQL - %s records in %s seconds</H1></DIV>"\
% (counter, time_taken)
return output
run(host='localhost', port=8080)
|
| SAPHANA_Bottle_Documents.py |
|---|
from bottle import get, run
import dbapi
import time
@get('/show_query')
def show_form():
counter = 0
start = time.clock()
conn = dbapi.connect('hanasvr-02', 30015, 'P075400', '5pA5kb6i')
cur = conn.cursor()
try:
ret = cur.execute("drop type test_out")
except dbapi.Error:
pass
try:
ret = cur.execute("drop procedure pyTest")
except dbapi.Error:
pass
queries = ['''create type test_out as table (DOCUMENT_ID NVARCHAR(8),
YEAR VARCHAR(4), AREA VARCHAR(2), AMOUNT BIGINT)''',
'''create procedure pyTest (OUT out_test TEST_OUT)\
language sqlscript reads sql data with result view ProcView as\
begin\
out_test = CE_CALC_VIEW("_SYS_BIC"."blag/CV_DOCUMENTS",
["DOCUMENT_ID","YEAR","AREA","AMOUNT"]);\
end''']
for query in queries:
cur.execute(query)
conn.commit()
query = '''select DOCUMENT_ID, YEAR, AREA, AMOUNT from ProcView'''
cur.execute(query)
ret = cur.fetchall()
output = "<DIV ALIGN='CENTER'><TABLE BORDER='1'>"
output += "<TR BGCOLOR='#B9C9FE'>"
output += "<TH>Document</TH><TH>Year</TH>"
output += "<TH>Area</TH><TH>Amount</TH>"
output += "</TR>"
for row in ret:
counter += 1
document_id = str(row["DOCUMENT_ID"])
year = str(row["YEAR"])
area = str(row["AREA"])
amount = str(row["AMOUNT"])
output += "<TR BGCOLOR='#E8EDFF'>"
output += '''<TD>%s</TD><TD>%s</TD>
<TD>%s</TD><TD>%s</TD>''' %
(document_id, year, area, amount)
output += "</TR>"
output += "</TABLE>"
end = time.clock()
time_taken = end - start
output += "<H1>SAP HANA - %s records in %s seconds</H1></DIV>"\
% (counter, time_taken)
return output
run(host='localhost', port=8080)
|
| Band_Model.py |
|---|
from sqlalchemy import Column, Integer, String
from meta import Base
class Singer(Base):
__tablename__ = 'SINGERS'
singer_id = Column(Integer, primary_key=True)
first_name = Column(String(20))
last_name = Column(String(20))
band = Column(String(20))
def __init__(self, singer_id, first_name, last_name, band):
self.singer_id = singer_id
self.first_name = first_name
self.last_name = last_name
self.band = band
def __repr__(self):
return "<Single('{first_name}', '{last_name}'\
,'{band}')>".format(**self.__dict__)
|
| Band_App.py |
|---|
from bottle import get, post, request, run, redirect
from meta import Base, engine, Session
from sqlalchemy import *
from sqlalchemy.orm.exc import NoResultFound
from Band_Model import Singer
def connect():
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
@get('/show')
def show_form():
output = "<div align='center'>"
output += "<h1>SQLAlchemy on SAP HANA</h1>"
output += "<a href='/add_singer'>Add Singer</a>"
output += "<table border=1>"
singers = Session.query(Singer).all()
output += "<tr><th>Id</th><th>First Name</th>"\
"<th>Last Name</th><th>Band</th>"\
"<th>Update</th><th>Delete</th></tr>"
for singer in singers:
update = "/update_singer?singer_id=" + str(singer.singer_id)
delete = "/delete_singer?singer_id=" + str(singer.singer_id)
output += "<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td>"\
"<td><a href='%s'>Update</a></td><td>"\
"<a href='%s'>Delete</a></td></tr>"\
% (singer.singer_id, singer.first_name, singer.last_name,
singer.band, update, delete)
output += "</table></div>"
return output
@get('/add_singer')
def add_form():
query = ""
singer_id = 0
try:
query = Session.query(Singer.singer_id).\
order_by(desc(Singer.singer_id)).first()
singer_id = int(query[0]) + 1
except NoResultFound:
singer_id = 1
except TypeError:
singer_id = 1
output = "<DIV ALIGN='CENTER'><TABLE>"
output += "<FORM METHOD='POST'><TR><TD>"
output += "<INPUT TYPE='HIDDEN' NAME='Singer_Id'"\
"value='%s'></TD></TR>" % (singer_id)
output += "<TR><TD>First Name</TD><TD>"
output += "<INPUT TYPE='TEXT' NAME='First_Name'></TD></TR>"
output += "<TR><TD>Last Name</TD><TD>"
output += "<INPUT TYPE='TEXT' NAME='Last_Name'></TD></TR>"
output += "<TR><TD>Band</TD>"
output += "<TD><INPUT TYPE='TEXT' NAME='Band'></TD></TR>"
output += "<TR><TD COLSPAN='2' ALIGN='CENTER'>"
output += "<INPUT TYPE='SUBMIT' value='Add Singer' NAME='Add_Singer'>"
output += "<INPUT TYPE='RESET' value='Clear'></TD></TR>"
output += "</FORM><TABLE></DIV>"
return output
@post('/add_singer')
def create():
Singer_Id = request.forms.get('Singer_Id')
Singer_Id = int(Singer_Id)
First_Name = request.forms.get('First_Name')
Last_Name = request.forms.get('Last_Name')
Band = request.forms.get('Band')
singer = Singer(Singer_Id, First_Name, Last_Name, Band)
Session.add(singer)
Session.commit()
redirect("/show")
@get('/update_singer')
def update_form():
query = ""
singer_id = 0
singer_id = int(request.query.singer_id)
query = Session.query(Singer.first_name, Singer.last_name, Singer.band).\
filter_by(singer_id=singer_id).first()
first_name = query[0]
last_name = query[1]
band = query[2]
output = "<DIV ALIGN='CENTER'><TABLE>"
output += "<FORM METHOD='POST'><TR><TD>"
output += "<INPUT TYPE='HIDDEN' NAME='Singer_Id'"\
"value='%s'></TD></TR>" % singer_id
output += "<TR><TD>First Name</TD><TD>"
output += "<INPUT TYPE='TEXT' NAME='First_Name'"\
"value='%s'></TD></TR>" % first_name
output += "<TR><TD>Last Name</TD><TD>"
output += "<INPUT TYPE='TEXT' NAME='Last_Name'"\
"value='%s'></TD></TR>" % last_name
output += "<TR><TD>Band</TD>"
output += "<TD><INPUT TYPE='TEXT' NAME='Band'"\
"value='%s'></TD></TR>" % band
output += "<TR><TD COLSPAN='2' ALIGN='CENTER'>"
output += "<INPUT TYPE='SUBMIT' value='Update Singer'"\
"NAME='Update_Singer'>"
output += "<INPUT TYPE='RESET' value='Clear'></TD></TR>"
output += "</FORM><TABLE></DIV>"
return output
@post('/update_singer')
def update():
Singer_Id = request.forms.get('Singer_Id')
Singer_Id = int(Singer_Id)
First_Name = request.forms.get('First_Name')
Last_Name = request.forms.get('Last_Name')
Band = request.forms.get('Band')
Session.query(Singer).filter_by(singer_id=Singer_Id).\
update({'first_name': First_Name, 'last_name': Last_Name,
'band': Band})
Session.commit()
redirect("/show")
@get('/delete_singer')
def delete():
singer_id = 0
singer_id = int(request.query.singer_id)
Session.query(Singer).filter_by(singer_id=singer_id).delete()
Session.commit()
redirect("/show")
connect()
run(host='localhost', port=8080)
|
