Specifying Schema in JDBC Connection

[Former Member]

Hi Guys,

MaxDB provides the possibility to logically unite several database objects (e.g. Tables, Views, Indexes, ...) in so called "Schemas".

However, I couldn't find any documentation about how to connect to a certain schema via JDBC. How should the JDBC URL look like? Assuming I have this kind of URL jdbc:sapdb://localhost:7200/MAXDB. Is there a way to connect to the schema "DEMO"?

Thanks for the help.

Cheers,

Gerardo

[benjamin_bichler]

Hi Gerardo.

If you go to the MaxDB documentation (e.g. for 7.8 it's http://maxdb.sap.com/doc/7_8/default.htm) and navigate to "SAP MaxDB Library" --> "Interfaces" --> "Java Manual" --> "SAP MaxDB JDBC Driver" --> "Connecting to a Database" --> "Connection URL", you will get some more information on how to connect to your desired database with a JDBC URL.

One example is to use "java.sql.DriverManager.getConnection method" to connect to the database. This example mentions the option "currentschema" which can be used to define the schema for the session.

So a possible URL for your purpose may look like:

jdbc:sapdb://myhost:7210/MYDATABASE?currentschema=MYDESIREDSCHEMA

Hope this answers your question.

Greetings

Benjamin

[Former Member]

Ben,

that worked for me

odbc: cs=schemaname

jjbdc currentschema=schemaname

dataservices: cs="schemaname"

[lbreddemann]

Hi Gerardo,

yes, it's rather simple to change the currently active default schema.

The command for this is called SET CURRENT_SCHEMA = <SCHEMA_NAME>.

Using the HelloMaxDB example from the documenation this can be used like this.

import java.sql.*;
public class HelloMaxDB
{
	public static void main(String[] args)
	throws ClassNotFoundException, SQLException
	{
		String username = "MONA";
		String password = "RED";
		String host = "";
		String dbname = "DB77";

		/*
   * Load JDBC Driver
   */
		Class.forName ("com.sap.dbtech.jdbc.DriverSapDB");

		/*
   * Define Connection URL
   */
		String url = "jdbc:sapdb://" + host + "/" + dbname;

		/*
   * Connect to the Database
   */
		Connection connection = DriverManager.getConnection (url, username, password);

		/*
   * Execute SQL Statements
   */
		Statement stmt = connection.createStatement ();
		ResultSet resultSet = stmt.executeQuery ("SELECT * FROM HOTEL.CUSTOMER");
		resultSet.next ();
		String hello = resultSet.getString (1);
        System.out.println ("SELECTED with schema name:");
		System.out.println (hello);

     /* 
     * Change the default schema
     */
        stmt.execute ( "SET CURRENT_SCHEMA=HOTEL");
		resultSet = stmt.executeQuery ("SELECT * FROM CUSTOMER");
		resultSet.next ();
		hello = resultSet.getString (1);
		System.out.println ("SELECTED without schema name:");
        System.out.println (hello);

        
		/*
		* close all objects
   */
		resultSet.close ();
		stmt.close();
		connection.close ();
		}
}

Actuallyl easy cheesy, isn't it?

Cheers,

Lars

[Former Member]

Hi Lars,

ok this helps, thanks.

Do you know if there is a way to define the default schema outside of a query. Maybe, using the the Database Studio to set the default schema. Or something any other way??

Cheers,

Gerardo

[lbreddemann]

> Do you know if there is a way to define the default schema outside of a query. Maybe, using the the Database Studio to set the default schema. Or something any other way??
> Gerardo

Hi Gerardo,

this setting is not limited to a specific SQL command, but it prevails throughout the whole session - so it's about to set it once after logon and from then on, you don't have to specify the schema any more.

The default schema however cannot be changed. It will always be the schema that is automatically created for each logon user - it's the logon users own name space if you will so.

Now that the technical limitations for this are clarified, I would be interested to learn your use case for this.

Usually schemas would be used to encapsulate the logical database design from other aspects of a database instance (e.g. authorizations, other applications etc.).

For that, it's sufficient to add the SET CURRENT_SCHEMA to the database logon code in your application.

For usages with free form SQL (which is rather not a end user interface) the manual command can't be avoided if you want to rely on the schema only.

What you of course can always do is: define (public) synonyms for the objects you want to access in the other schema.

With synonyms in place you don't have to set the current schema to reach the objects even without specifying the full object name.

So, what's your use for the schema feature?

regards,

Lars