Skip to Content

Data manipulation and definition via Python and JDBC on HANA

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:

UPDATE SYSTEMDB.UNIVERSITY.STUDENT SET SEMESTERS=6 where STUDENT_ID=1

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("10.11.1.49", 39015, "SYSTEM", "MyPassword1")
cursor = connection.cursor()
query = """
    UPDATE SYSTEMDB.UNIVERSITY.STUDENT SET SEMESTERS=6 where STUDENT_ID=1
"""
cursor.execute(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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 04 at 04:43 AM

    Hi Patrick

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

    What it supports is the following list of parameters:

    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
      DATABASE: HXE
    

    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')
    True
    >>> 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).

    Lars

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Lars Breddemann

      I didnt find out how to change the search path (Do you know how to specify the path where the python script should look for the dbapi.py?) but moving the python file to the correct directory worked out :)

      I tried key / userkey before and it was not working. Now they are working both.

      Thanks for your help :-)

  • 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 help.sap.com).

    Add comment
    10|10000 characters needed characters exceeded