Skip to Content

Data manipulation and definition via Python and JDBC on HANA

Dec 21, 2017 at 02:56 PM


avatar image

Specifically, I would like to use Python and/or JDBC to create stored procedures on HANA.

I tried executing simple DML queries such as the following first:


While the above works fine when executed from the SQL console, I get the following error when I execute it from JDBC or Python:

"feature not supported: Unsupported DML / DDL on remote database object(s)"

The same happens for definition requests such as CREATE PROCEDURE.

Here is a minimal (not) working example in Python:

from hdbcli import dbapi
import sys
connection = dbapi.connect("", 39015, "SYSTEM", "MyPassword1")
cursor = connection.cursor()
query = """
print (cursor.fetchone())

The privilege of user "SYSTEM" for schema "UNIVERSITY" is "CREATE ANY".

Are there any restrictions to the allowed kinds of queries? Or is there something wrong with the way I access the table?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Lars Breddemann
Jan 04 at 04:43 AM

Hi Patrick

looking into I found that the python client (hdbcli) doesn't support the "database" parameter.

What it supports is the following list of parameters:

    address : ip address or hostname of DB instance
    port : port number of the DB instance
    user : user name
    password : password for user name
    autocommit : auto commit mode
    packetsize : communication packet size
    userkey : user store key
    properties : additional dict object with special properties

You could of course go and figure out the tenant database port yourself now, but I would recommend a different approach.

Set up the connection information with hdbuserstore and simply reference the user key in the connection parameters (both in JDBC and hdbcli/Python). For example my user key is called hxedev and points to the HXE database:

c:\Program Files\SAP\hdbclient>hdbuserstore list hxedev
KEY hxedev
  ENV : hxehost:39013
  USER: devdude

In the Python code I can just write:

>>> from hdbcli import dbapi
>>> conn = dbapi.connect(key="HXEDEV")
>>> cursor = conn.cursor()
>>> cursor.execute ('SELECT current_time FROM DUMMY')
>>> for row in cursor:
...     print(row)
(datetime.time(4, 6, 22),)

With JDBC I can write:

connection = DriverManager.getConnection("jdbc:sap://?key=HXEDEV");

And that's all that is required. No logon data in the source code.

Hope that helps (btw: it also works for ODBC connections).


Show 3 Share
10 |10000 characters needed characters left characters exceeded

Thanks Lars, this approach looks promising...

I created the key using the following command:
hdbuserstore set hxedev SYSTEM MyPassword1

For SYSTEMDB, the connection via Python and JDBC works (yay! has also worked before, though).

So how do I specify the database in the user store?

Edit: I found out the correct port number for the tenant database and with that, also the Python code works (thanks to the hint to gave in the answer). However, I prefer the user store method and setting the database name does not seem to be documented I really need your help here.


hdbuserstore, fortunately, provides built-in help:

hdbuserstore help
Usage: hdbuserstore [options] command [arguments]

  -u <USER>       perform operation for other operating system user
  -v              verbose mode, print operations done
  -i              interactive mode, ask for missing values
  -h              this help message
  -H <HOST>       assume host name <HOST>
Commands (the command name is case insensitive):
        Print help message.
        Add or update a profile entry.
        <KEY>       entry key name
        <ENV>       database location (host:port)
        <USERNAME>  user name
        <PASSWORD>  password
        <DATABASE>  database name in MDC environment
  AddFromDir <DIR>
        Add entries from a store in <DIR> without overwriting existing keys.
        <DIR>       store directory from which entries to be read
  Delete <KEY>
        Delete entry with key <KEY>.
  List [<KEY> ...]
        List entries of store. The passwords are not shown.
  ListFromDir <DIR>
        List entries from a store in <DIR>.
        <DIR>       store directory from which entries to be read
        Generate new encryption key and encrypt passwords again.

So creating an entry that points to a tenant DB looks like this:

hdbuserstore SET HXEDEV hxehost:39013@HXE devdude devpassword

The <ENV> bit is the one where the tenant DB is specified with @<DATABASE>.

One thing to mind is all this requires an SAP HANA 2 SP02 (or newer) client.


I was checking the SAP HANA Security Guide so intensely, that I forgot to check the help function...

The respective chapter gives an example for setting a database but it does not conform to the syntax description ('@' is missing). Too bad that I wasn't clever enough to figure this out by myself yesterday ;-)

Lars Breddemann
Dec 21, 2017 at 11:37 PM

When you're connected to the SYSTEMDB and your data is stored in a schema in this SYSTEMDB, you don't need to provide the DBNAME in the SQL statements.

So, just leave out the "SYSTEMDB." bit from your statements and your code should work, both with JDBC and Python.

As a general hint: don't use the SYSTEMDB for your application data. Create a tenant DB (even with HANA Express Edition, there is a default tenant DB) and connected to this tenant DB, instead.

If you want to understand more about why you got that error message, I recommend reading up on the concepts behind multiple database containers in SAP HANA (see online documentation on

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Thanks a lot for the remarks. I'm eager to learn how to use HANA properly :-)

When I leave out "SYSTEMDB." I get the error "invalid table name: Could not find table/view STUDENT in schema UNIVERSITY".

I created a tenant database ("MYDB") using the following statement and imported the schema UNIVERSITY:

import "UNIVERSITY"."*" as binary from '/usr/sap/HXE/home/HXE_Transfer/UNIVERSITY'

Then I modified the script from my original question such that the following statement is executed instead:


I get the error: "insufficient privilege: Not authorized". The same happens for other custom schemas on MYDB.


Sounds to me like you are connecting to the SystemDB. Instead of that, rather connect to the tenant DB.

And import the data into it.

Unless you explicitly configure access to tenant databases to be shared, you cannot access tenant databases from other DBs - including the SystemDB.


Sorry if I get you wrong. But don't you connect to the database system (i.e. HXE) and then access a particular database by adding the prefix "<DBNAME>." to the tablename in queries? So how do I connect to a specific db from Java or Python coding or how do I configure it to be shared?

I already have imported the data into the new tenant db ("MYDB").


I managed to successfully execute the statement from JDBC by connecting to the database using the following connection string:

connection = DriverManager.getConnection(

Note, that I needed to change the port number to 39013 otherwise it won't work.

However, I still do not know how to connect to the tenant DB via Python. I tried the following:

connection = dbapi.connect(address="", database="MYDB", port=39013, user="SYSTEM", password="Initial1")

Error: "authentication failed"


Lars Breddemann do you have an idea what I'm doing wrong in the Python call? Both the JDBC and the Python connect look semantically equal so I am clueless.

Is there some additional parameter I need to set?