Omit schema in the DERBY Query
database, datasource, derby, java, schema
Solution
There are basically two ways to control the default schema name:
- Issue the SET SCHEMA statement after you have connected to the database.
- Login as the user with the same name as the schema you wish to use.
If you haven't issued a SET SCHEMA statement, then Derby will use your username as the schema name.
So if you login as user "APP", and don't issue a SET SCHEMA statement, then your schema name will be APP.
Problem
I have created a database named 'movie_db', set default schema to APP. Then created a sample table named 'USERS'. My connection to DB is as follows: ``` <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="org.apache.derby.jdbc.ClientDriver"/> <property name="url" value="jdbc:derby://localhost:1527/movie_db"/> <property name="username" value="root"/> <property name="password" value="pass"/> </bean> ``` Now I want to write some tests and try to execute the following query: ``` SELECT * FROM USERS; ``` What I get: ``` java.sql.SQLSyntaxErrorException: Table/View 'USERS' does not exist. ``` When I specify exactly the schema I'm using: ``` SELECT * FROM APP.USERS ``` everything works fine. How can I omit schema name in my query? UPDATE: Like Bryan said, I've created a user with the name of my default schema and authorize with this login. This is the most simple way to omit schema name in the query. But still if I want to use multiple schemas the only way is to set schema explicitly.