Setting default schema for Vertica Database

jpa playframework vertica
Ananth picture Ananth · May 27, 2011 · Viewed 7.4k times · Source

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

Eamonn O'Brien-Strain picture Eamonn O'Brien-Strain · May 2, 2012

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);

Related questions

Creating a composite Unique constraints on multiple columns

This is my model: class User {...} class Book { User author; int number; } Every book number starts at 1 per author and increments upwards. So we'll have Books 1,2,3 by John Grisham, Book 1..5 by George Martin, etc... Is there a unique constraint I …

Read More
Difference between JPA and EBean Play Framework

I'm relatively new to Play framework, I tried following the cookbook but it seems to be already outdated. Anyways I just want to know if there's a big difference between those I have mentioned. Some tutorials used eBean whilst the …

Read More
JPA/Hibernate improve batch insert performance

I have a data model that has a ONE TO MANY relationship between ONE entity and 11 other entities. These 12 entities together represent one data packet. The problem I am having is to do with the number of inserts that occur …

Read More