HANA session switches automatically to SYSTEM schema.

[Former Member]

Hi Developers,

We're facing an issue in which during our work with HANA the session switches to the system database after a period of idie activity against the DBMS.

For example we have the SCHEMA ABC and in it the table XYZ,

We've got the code the query:

"SET SCHEMA ABC"

...

...

After a while we have the query

SELECT * FROM XYZ;

but sometimes we get the error:

[SAP AG][LIBODBCHDB DLL][HDBODBC] Base table or view not found;259 invalid table name:  Could not find table/view XYZ in schema SYSTEM

Can you please assist?

Thank you,

Tal.

[Former Member]

Hi Tal et al.,

This issue appears to be is connected to the HANA ODBC driver libodbcHDB. I also experienced this kind of error message, e.g. when connecting through ODBC from a ruby on rails app to HANA.  Therefore, please check that you are using the latest version of the ODBC driver and your driver configuration in /etc/odbc.ini. If the issue still persists, I recommend to open a ticket via the service market place to file a corresponding bug.

Cheers,

Matthieu

[Former Member]

I am having the exact same issue.

ODBC ERROR: State = S0002 Error = [SAP AG][LIBODBCHDB32DLL][HDBODBC32] Base table not found;259 invalid table name: Could not find table/view XXX in schema SYSTEM: line 1 c0l 167 (at pos 166)

Is there a way to set the default schema?

~ terry o.

[Former Member]

Hi Terry,

Try to issue the following SQL statement:

SET SCHEMA "schema_name";

This sets the default database schema to "schema_name" for the remaining statements.

However, please be aware that in case of a connection reset / auto reconnect the default schema is reset and you might need to execute the same statement again.

Alternatively, you might want to change your SQL statements. Instead of

SELECT "x", "y", "z" FROM "your_table";

you can also use

SELECT "x", "y", "z" FROM "schema_name"."your_table";

to refer to a specific table "your_table" within the database schema "schema_name"

Best,

Matthieu

[lbreddemann]

Hey Tal,

this is likely happening due to automatic re-connects in SAP HANA studio.

When you change the default schema via the SET command, SAP HANA studio cannot know this and in turn cannot re-execute the SET command upon re-connect.

There's not really an easy way to change this - you will have to execute the SET command again after a re-connect.

- Lars

[Former Member]

Hi Lars,

Thank you for your reply.

However this is not happening in HANA studio but in an application connecting to HANA via ODBC,

After we use the set schema command the queries work fine but after a while it seems like it somehow get reset and points again to the SYSTEM schema.

Is there a timeout when the connection is idle? is this the expected behavior?

Regards,

Tal.