//log//

By now it should be known we’re notorious users of SAP MaxDB in a “non-SAP environment”, and, for that matter, we have done rather well throughout the last seven years. By now, we gained quite some experience running, administering, working with that RDBMS in our environment, and we manage to get our production work done on top of it without thinking about it all too much which seems a good thing. However, there are several nuissances about that platform, both talking about political SAP product and licensing decisions and about overall technical issues, lack of support for most of the neat tools, toys and frameworks included.

However, all along trying to do a quick data exporter to move documents and metadata to a testbed system in one of our current research projects, I managed to get rid of one of these issues in a way which, at second look, is in an almost embarrassing way obvious and easy once you care to take a closer look: All along with using Java (EE) for most of my production code, I am enthusiastically into Python for smaller throwaway scripts solving particular small problems all along the way. Unfortunately, the MaxDB Python bindings don’t seem to be in any way maintained anymore and ceased working with recent Python versions ages ago. The MaxDB forum, generally a helpful and friendly place, also didn’t help much concerning this…

… yet, looking at the (broken) MaxDB Python bindings makes one (or, better, made me) completely miss one obvious way how to work around this: MaxDB provides an ODBC driver, which is proven to be production ready (as our core application works on top of it) and available for Linux / unixODBC environments, too, easily working on i386 and x86_64 systems and even in current distributions. Python, on the other side, offers pyodbc, a mature (version 3.something at time of writing) module enabling Python applications to access ODBC data sources and do whatever needs to be done in there.

Getting this to work is somewhat easy, assuming you have an existing MaxDB installation at hand. If not, MaxDB is rather well documented so this shouldn’t be too much of an issue – and overally, looking at some of the points outlined above, I wonder whether this is of relevance to anyone not into running SAP MaxDB yet, anyhow. To get ODBC to run, doing

sudo apt-get install unixodbc python-pyodbc

on Debian / Ubuntu distributions should give you all you need. Done that, you will need an ODBC data source configuration for SAP MaxDB. Much of that procedure is well documented in the SAP MaxDB site, too – briefly, create an .odbc.ini file in your $HOME and add something like this:


[MAXDB]
Driver = /opt/maxdb/apps/lib/libsqlod.so
SQLMode = INTERNAL
ServerDB = TESTDB
ServerNode = localhost
IsolationLevel = COMMITTED


If in doubt, refer to this site outlining the meaning of the various configuration parameters found in here. Again, in a nutshell: [MAXDB] is the name of the ODBC data source you will use later on to refer to this connection. Driver is the full path to the MaxDB unixODBC driver which should be found in the lib/ folder of your MaxDB “independent program folder” (which you specified during installation and which, in its bin/, contains things such as x_server and dbmcli). ServerDB and ServerNode tell the ODBC configuration which database instance on which server to use.

That done, you’re mostly through. Once you made sure your database instance as well as the MaxDB x_server is running, you can use isql (a small ODBC SQL console that comes with unixODBC) for connecting to and playing a bit with the database:

kr@kaleid:~$ isql MAXDB myuser mypass
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

Obviously, isql accepts the ODBC datasource name as well as the login credentials (username, password) to get you connected and ready to go. Play with it a bit by issuing some more or less meaningful SQL statements:

SQL> SELECT 1 FROM DUAL
+------------+
| EXPRESSION1|
+------------+
| 1          |
+------------+
SQLRowCount returns 1
1 rows fetched
SQL> SELECT ID FROM DOCINFO WHERE ID > 6200
+------------+
| ID         |
+------------+
| 6201       |
| 6202       |
| 6203       |
| 6204       |
| 6205       |
| 6206       |
| 6207       |
| 6208       |
| 6209       |
| 6210       |
| 6211       |
| 6212       |
| 6213       |
| 6214       |
+------------+
SQLRowCount returns 1
14 rows fetched

Happy so far? Fine, then moving on to pyodbc:

import pyodbc
cxn=pyodbc.connect("DSN=MAXDB;UID=myuser;PWD=mypass")
c=cxn.cursor()
c.execute("SELECT * FROM DOCINFO WHERE ID > 6200")

print c.fetchone()

Not too difficult, either. Get hold of the pyodbc module in your code by importing it, get a connection to your preconfigured ODBC data source, get a cursor in that connection and do as you like. The Getting Started page in the pyodbc wiki is a good starting point for finding out more about how to use the Python ODBC client code.

What to use it for?

So, if using Java, JDBC and eventually things such as JPA, one might wonder what’s the point of using Python, be that on top of ODBC or MaxDB native drvers, at all? Two reasons / use cases in my environment:

• Having a more powerful SQL console at hand. Though both isql and, a bit less powerful, MaxDBs very own sqlcli (which has its most profound weakness in lacking readline / history support) are usable, I so far lacked an easy-on “extended SQL console” to play a bit with existing data structures, SELECT and JOIN a few things and see where to get which data from in a database schema which needs some work to be done right. Using Python is incredibly straightforward for these purposes, yet quite powerful.
• Crafting temporary scripted solutions for temporary problems (such as data export) without completely damaging application security. So far, then and now I was tempted to utilize Jython, JSR-223 and scripts dynamically loaded / evaluated within a Java EE context at runtime and provide these with the context required to do data retrieval, most notably of course JPA EntityManager instances or JDBC DataSources. In the end, this is all about paving way for small, temporary hacks by introducing even more crude, questionable hacks, and from a Java EE application stability / security point of view, this ain’t that good an idea at all, that’s why I never used it so far. Running Python on top of MaxDB unixODBC eases this a bit, as, around here, it’s way easier and more controllable to provide a scripted environment with strictly limited database credentials and make sure nothing “bad” happens (actually, most of these scripts don’t need more than just READ access to parts of the MaxDB table space).

So by now, where were we? Ah yes, writing data exporters… 😉

Filed under: