Skip to Content

based on revision 48 (SPS 5) of SAP HANA Studio and Database


I just browsed throught the updated SAP HANA Developer Guide, SPS 05, Ver. 1.1, 21.12.2012 (really that date? not even 12.12.12? πŸ™‚ ) and found something pretty nice in there for ODBC and JDBC users.

By the way: meanwhile (as of SPS 5) three of the files from the HANA documentation website have been included into the local eclipse help.

This means:no more back and forth between the PDF and the HANA studio as well as ‘F1’ help in the studio.

As you can see, the SAP HANA Administration Guide, the SAP HANA Developer Guide and the SAP HANA SQLScript Reference are now ‘on board‘.

/wp-content/uploads/2013/01/hana_docs_in_studio_177871.png

While I’ve no idea on when the SQL reference will follow, I still use the web browser integration until then.

Let’s see the nuggets in there!

A common task is to check the database connectivity via ODBC after a user workstation has been set up.

This is easy to do with the SAP HANA client program odbcreg.exe (to be found in the installation folder of the SAP HANA client software).

By using the command odbcreg.exe -t hdbodbc the driver is loaded and a logon dialog window is opened.

A nice giveaway is that the exact driver software version information can be found in the title bar of this dialog window.

You can now either type in the usual SAP HANA logon credentials (hostname, port number, username and password) or, and this is really nice,  you can use an entry from the secure HDBUSERSTORE.

I’ve mentioned the HDBUSERSTORE earlier and really like how it makes getting access to the SAP HANA server seamless.

So, instead of filling out the logon form you can just enter @<KEY> to have ODBC use the HDBUSERSTORE-entry named <KEY>.

In my example I used the entry HANLARS to connect to my test instance HAN.

ODBC-connection-test-userstore.png

Once odbcreg.exe got the logon data it tries to connect to the SAP HANA instance and runs a command like

   SELECT now() FROM dummy;

(for which you see the output as well in the command line window).

As no special authorizations are required for this command, running it means:

    if you get a result, that’s good.You’re in πŸ™‚ !

or

    if you don’t get a result, well, don’t go looking for some “missing” authorizations on SAP HANA level.

    In that case, the client didn’t even get through to the SAP HANA server.

   

By now you may wonder, why the heck I’m so happy about this HDBUSERSTORE feature in ODBC.

After all, most often people don’t make connection tests, but need to logon to the database to actually do something with it.

Something you would do with ODBC… like connecting Excel or R or PYTHON or…

Well, guess what, this way of providing a link to the HDBUSERSTORE also works with any ODBC connection string!

As an example I use the same HDBUSERSTORE entry in R Studio, the IDE for R development, to re-run one of Blags test cases:

userstore_in_R.png

Isn’t that nice?

No need to type in the server hostname and port any more.

No insecure coding due to hard coded username/password information any longer.

PLUS: you can use the very same code without change for multiple users and workstations.


All you have to do is to setup the HDBUSERSTORE entry you refer to for the user that should run your code.

As promised above, there’s also something in here for JDBC users.

Although unfortunately JDBC cannot make use of the HDBUSERSTORE by its very design, the JDBC driver .jar-file actually can do more than one might expect at first.

Besides printing out its own version number, the JDBC driver also has a little connection test on board, very similar to the ODBC driver.

By running

C:\Program Files\sap\hdbclient>java -jar ngdbc.jar –version

package com.sap.db.jdbc, SAP In-Memory Database JDBC Driver, SAP AG, 1.00.48.Build 0372847-1510 (Commit-Hash: not set)

we get the exact version information.

And by running

C:\Program Files\sap\hdbclient>java -jar ngdbc.jar -n <hostname>:30015 -u <username>,<password>

Now I’m successfully connected

|            |

————–

| 42         |

we not only get a connection test, but also the Answer to the Ultimate Question of Life, The Universe, and Everything

AWESOME, I say πŸ™‚

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

      1. Vladislav Volodin

        I simply enabled tracing of JDBC, and saw the following request:

        Select top 1 42 as “connect test” from users

        I did not dig the JAVA code deeply, but it seems that in the code the caption is not retrieved properly.

        (0) 
        1. Lars Breddemann Post author

          I see what you mean.

          Actually this is not an error.

          The internal helper method that prints the “Now I’m successfully connected” message simply used ResultSetMetaData.getColumnName() to retrieve the header of a column that should be printed.

          Since this is a computed column in this case (constant string literal) getColumnName() needs to be empty. Instead the data could be found in getColumnDisplayName().

          And after all: this is really just a little handy add-on to the driver to allow for a super-quick no-effort-connection test.

          It really is provided as a take-it-or-leave-it approach πŸ˜‰ .

          – Lars

          (0) 
  1. Kumar Mayuresh

    Lars

    Great blog, will definitively try, and will get back to you in case of trouble πŸ™‚

    Alvaro

    IF it works then its really a “Happy day for developers and even for users” πŸ™‚

    Cheers

    Kumar.

    (0) 
  2. Oliver Isenberg

    Hi Lars,

    thanks for this good article. It works fine on my systemΒ  until I like to connect in RStudio. Can you share more information about the syntax to connect?

    I used the following syntax, where RH8 is my DSN and RH8Connect is my key I generated with hdbuserstore

    ch<-odbcConnect(“RH8″,”@RH8Connect”)

    Many thanks for your support and all the best

    Oliver

    (0) 

Leave a Reply