Hana ODBC Default Schema

[former_member184713]

Does someone know if we can specify the default schema when opening a connection to hana ?

There is a lot of odbc drivers that allows to automatically set the database in the connection string.

After connecting to the database, the program can select * from table   without having to specify select * from database.schema.table.

With hana odbc

We open the connection with a string like "ServerNode=Hanaserver:port;UID=user;PWD=password".

The, because our code was done following the above approach, it does a select * from table and ... the table is not found because there is no default schema specified.

I have to do a SET SCHEMA after each connection.

Is there a way to set the default schema directly in the connection string like we can do in with other drivers ?

Something like 

"ServerNode=Hanaserver:port;SCHEMA=MYSCHEMA;UID=user;PWD=password".

[former_member184713 · Accepted solution]

With newer version of the hana database, you can use a new property "CS" to set the current schema. It works on Rev 69, maybe before.

Example : "ServerNode=Hanaserver:port;UID=user;PWD=password",CS="MYSCHEMA".

Found this when browsering information about crystal reports.

[Former Member · Accepted solution]

This message was moderated.

[Former Member]

Hi keven,

could solve this problem, I have several days trying to fix it,

I tried with this connection string

  DRIVER={B1CRHPROXY};UID=<db_user>;PWD=<db_pwd>;SERVERNODE=<hana_host/ip>:<hana_port>;[DATABASE=<company_schema>;]

and with this

DRIVER = {HDBODBC32}; UID = <db_user>; PWD = <db_pwd>; SERVERNODE = <hana_host/ip>: <hana_port>;

but it works for a single schema.

any information to make the connection dynamic would be helpful

thanks in advance

[former_member184713]

What do you mean by Dynamic ? After a connection to the database, you can change the current schema using the SET SCHEMA command.

[former_member184713]

I created a new schema different from my database user. As Ravindra said, by default, statement execute in the user default schema, which is have the same name as the user. What I want is my statement to execute in my newly created schema that have custom tables. I can do this by executing Set Schema xxx; but I would like to know if this can be automated in the connection string.

I tried  Michael hint, it did not work. DATABASE= is a standart property of many odbc drivers but the Hana drivers does not support it or don't do anything with it. After all, on hana, there is only 1 big database per instance and many schema in that database. The notion of databases are replaced by schema.  You want to change schema and not database.

[former_member184768]

Hi Keven,

Ideally there should have been another DATABASE USER with its own default schema. Like other databases as Oracle, SQL Server, in HANA you can have multiple users in the same database and each user has its own default schema. There is no explicit need to create additional schema for the same user.

Is there any specific reason why you chose to create another schema for the same user rather than creating a new user itself.

Is it possible for you to create new user and use that user for the application.

One more option is, you can create views in the default schema of the user which is connecting to the database. These views can point to objects in the second schema.

Regards.

Ravi

[former_member184713]

Is there any specific reason why you chose to create another schema for the same user rather than creating a new user itself :

The same way as sap business one have different company database.

On a single user, it has SBOCOMMON, SBODEMOUS, CUSTOMERDATABASE, CUSTOMERDATABASETEST.

It would not make sense to have different user for each of these databases. It's a single product that require multiple databases.

Also the product (business one version for hana) will probably not support having different sql user/password to access each databases.

[former_member184768]

Hi Keven,

Just out of curiosity, do you have the tables in different schema than the default schema for user with which you are connecting to HANA.

By default HANA statement should execute against the object in the default schema for the connected user without the need for setting the schema explicitly.

Regards,

Ravi

[Former Member]

Hi Keven,

Have you tried the following;

Connect URL:

"DRIVER=HDBODBC32;UID=user;PWD=password;SERVERNODE=host1:30015;host2:30015;

host3:30015;DATABASE=xxx"

Regards

Michael