Setting default schema for Vertica Database

Question

I am building a web application using Play! with Vertica database as back-end. The JDBC connection string for Vertica contains the server and database name, but my tables are under a specific schema (say "dev_myschema"). Thus, I should refer to my table as "dev_myschema.mytable". There is an exact copy of all these tables in a production schema as well (say "prod_myschema") with real data.

I would like to set this schema name in the configuration file so that it is easy to switch between these two schema. For now, I have a getConnection method in a helper class, that does DB.getConnection() and sets the configured schema as the default schema for that connection object. However, the same does not help in other model classes where it is mentioned along with its Entity annotation (@Entity @Table(name=dev_myschema.mytable))

Is there a way by which I can specify the schema name in the configuration file and have it read by the connection method as well as the model annotations?

Thanks.

Answer 1

Eugene got it almost correct, but was missing an underscore. The correct Vertica SQL syntax to set the default schema is:

set search_path to dev_myschema

As Eugene suggested, if you are using low-level JDBC, as soon as you create your Connection object you can do:

conn.createStatement().executeUpdate("set search_path to " + schemaName);