Preface
This document assumes you are already using or used Timo’s Extension write back to MYSQL. You can use same XAMPP, however since I already have npm on my machine I was using that as a webserver to host the PHP file which finally writes back the data to SAP HANA.
Extensions work for both HANA 1.0 and also 2.0(XSA-HDI)
Step 1: Create a New User on HANA if you don’t already have one
Make sure the user has a content admin role over the required schema (synonyms/CDS objects if using 2.0).
Step 2: As mentioned in MYSQL blog post create similar tables in HANA. You can create a columnar or a row table in HANA according to your needs. Both will work for this solution.
Note :For autoincrement in HANA for any column you would need to define the column as primary key and make it as IDENTITY
Step 3: Download and Install required Drivers
- The code used the 32 bit HDB HANA driver. Execute c:\Windows\SysWOW64\odbcad32.exe in run
- Download the HDB 32 bit HANA driver from SAP market place(SAP made it far more easier now to download the drivers)
- install the drivers
Step 4: Follow the same instructions from Timo blog post over copying all the required files of the extensions.
Before testing the HANA insert records PHP file, make sure to modify the ini file under location xampp\php in my case it is under npm. Modifaction requires you to just uncomment below two dll libraries which are already in the INI file.
extension=php_pdo_odbc.dll
extension=php_odbc.dll
Step 5: Inserting records into HANA PHP file
Below is the code : You can run this PHP from your browser to test if statements are working. Easiest way is use Select ‘A’ from dummy staement or even simple INSERT statment with dummy values into the table you created.
<?php
$customerName = $_POST['name'];
$clusterName = $_POST['cluster'];
$checkBox = $_POST['checkbox'];
$userInput = $_POST['userinput'];
$selectedMeasure = $_POST['measure'];
if (! extension_loaded('odbc'))
{
die('ODBC extension not enabled / loaded');
}
$driver = 'HDBODBC32';
$host = "HANA:30015";
// Default name of your hana instance
$db_name = "HDB";
// Username
$username = 'makkena';
// Password
$password = "HANA";
// Try to connect
$conn = odbc_connect("Driver=$driver;ServerNode=$host;Database=$db_name;", $username, $password, SQL_CUR_USE_ODBC);
if (!$conn)
{
// Try to get a meaningful error if the connection fails
echo "Connection failed.\n";
echo "ODBC error code: " . odbc_error() . ". Message: " . odbc_errormsg();
}
else
{
// Do a basic select from DUMMY with is basically a synonym for SYS.DUMMY if you need to test in HANA
$sql = "INSERT INTO CPG.getdataandwriteback (ClusterName,CustomerName,CheckBox,UserInput,SelectedMeasure) VALUES ('".$clusterName."','".$customerName."','".$checkBox."','".$userInput."','".$selectedMeasure."')";
$result = odbc_exec($conn, $sql);
if (!$result)
{
echo "Error while sending SQL statement to the database server.\n";
echo "ODBC error code: " . odbc_error() . ". Message: " . odbc_errormsg();
}
else
{
while ($row = odbc_fetch_object($result))
{
var_dump($row);
}
}
odbc_close($conn);
}
Step 6: Update your MarkSelection JS file to refer to this PHP file created above.
- Assume you are using Timo JS code, just rename the reference of Timo’s INSERT PHP file to HANA one that you created in Step 5.
- Also since Timo’s dashboard is using the data source name as MYSQL you might have to change that in case you decide to change the data source name in your dashboard. For example, if I created the Data Source named HANA on these Tables create in the above steps I will have to change the JS script code referencing the data source name.
