You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Leonard Sitongia <si...@ucar.edu> on 2007/06/26 18:39:53 UTC
Questions about ij> connect
Hi,
I have a couple of questions about using the ij command (Linux) for connect.
How do I create a schema?
It seems like I should create a schema before I create any databases, but I
need to connect to a database before I can do anything, right? What, then,
are the steps to create a schema using ij?
How do I specify a schema when creating a database?
Once I've created a schema, how do I specify it for the
connect ...;create=true command, or how do I set it for subsequent commands
while using ij?
Thanks for your help!
--
==Leonard E. Sitongia
High Altitude Observatory
National Center for Atmospheric Research
P.O. Box 3000 Boulder CO 80307 USA
sitongia@ucar.edu voice: (303)497-2454 fax: (303)497-1804
Re: Questions about ij> connect
Posted by John Embretsen <Jo...@Sun.COM>.
Lance J. Andersen wrote:
>
>
> John Embretsen wrote:
>> Also, be aware of default schemas and implicit schema creation, a
>> common source of confusion. From FAQ 5.3 [1]:
>>
>> "The current schema for any connection defaults to a schema
>> corresponding to the user name. If no user name is supplied then the
>> user name (and hence current schema) defaults to APP.
>>
>> However even though the current schema is set to the user name, that
>> schema may not exist. A schema is only created by CREATE SCHEMA or
>> creating an object (table etc.) in that schema (this is implicit
>> schema creation)."
>>
>>
> This point is key as I know until i did a deep dive based on what i saw
> with the Netbeans explorer, i did not really think about the schema
> getting created until the object for that user name (when create schema
> was not used) occurs.
>
> We should probably open a JIRA to clarify when the schema is created in
> more explict detail
Yes, feel free to do so; I couldn't find anywhere in the manuals where the
details of implicit schema creation is explained. I wasn't really aware of these
details either until I noticed a related NetBeans issue:
http://www.netbeans.org/issues/show_bug.cgi?id=106047
--
John
Re: Questions about ij> connect
Posted by "Lance J. Andersen" <La...@Sun.COM>.
John Embretsen wrote:
> Bryan Pendleton wrote:
>
>>> Once I've created a schema, how do I specify it for the connect
>>> ...;create=true command, or how do I set it for subsequent commands
>>> while using ij?
>>
>> There is a SET SCHEMA statement you can use:
>> http://db.apache.org/derby/docs/10.2/ref/rrefsqlj32268.html
>
> Also, be aware of default schemas and implicit schema creation, a
> common source of confusion. From FAQ 5.3 [1]:
>
> "The current schema for any connection defaults to a schema
> corresponding to the user name. If no user name is supplied then the
> user name (and hence current schema) defaults to APP.
>
> However even though the current schema is set to the user name, that
> schema may not exist. A schema is only created by CREATE SCHEMA or
> creating an object (table etc.) in that schema (this is implicit
> schema creation)."
>
>
This point is key as I know until i did a deep dive based on what i saw
with the Netbeans explorer, i did not really think about the schema
getting created until the object for that user name (when create schema
was not used) occurs.
We should probably open a JIRA to clarify when the schema is created in
more explict detail
> [1]: http://db.apache.org/derby/faq.html#schema_exist
>
>
Re: Questions about ij> connect
Posted by John Embretsen <Jo...@Sun.COM>.
Bryan Pendleton wrote:
>> Once I've created a schema, how do I specify it for the connect
>> ...;create=true command, or how do I set it for subsequent commands
>> while using ij?
>
> There is a SET SCHEMA statement you can use:
> http://db.apache.org/derby/docs/10.2/ref/rrefsqlj32268.html
Also, be aware of default schemas and implicit schema creation, a common source
of confusion. From FAQ 5.3 [1]:
"The current schema for any connection defaults to a schema corresponding to the
user name. If no user name is supplied then the user name (and hence current
schema) defaults to APP.
However even though the current schema is set to the user name, that schema may
not exist. A schema is only created by CREATE SCHEMA or creating an object
(table etc.) in that schema (this is implicit schema creation)."
[1]: http://db.apache.org/derby/faq.html#schema_exist
--
John
Re: Questions about ij> connect
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> How do I create a schema?
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj31580.html
describes the CREATE SCHEMA statement.
> It seems like I should create a schema before I create any databases,
No, it's the other way around. Each database can have one or more
schema. So create the database first, then connect to it and
create the schema within it.
> Once I've created a schema, how do I specify it for the
> connect ...;create=true command, or how do I set it for subsequent commands
> while using ij?
There is a SET SCHEMA statement you can use:
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj32268.html
thanks,
bryan
Re: Questions about ij> connect
Posted by Leonard Sitongia <si...@ucar.edu>.
On Tuesday 26 June 2007 10:39 am, Leonard Sitongia wrote:
> I have a couple of questions about using the ij command (Linux) for
> connect.
Thanks, Bryan, Stanley. That explains it.
==Leonard
Re: Questions about ij> connect
Posted by Stanley Bradbury <St...@gmail.com>.
Leonard Sitongia wrote:
> Hi,
>
> I have a couple of questions about using the ij command (Linux) for connect.
>
> How do I create a schema?
>
> It seems like I should create a schema before I create any databases, but I
> need to connect to a database before I can do anything, right? What, then,
> are the steps to create a schema using ij?
>
> How do I specify a schema when creating a database?
>
> Once I've created a schema, how do I specify it for the
> connect ...;create=true command, or how do I set it for subsequent commands
> while using ij?
>
> Thanks for your help!
>
Hi -
A Derby database schema can be thought of a partitioning or grouping of
related tables within a database. Therefore a schema exists within a
context of a database, not the other way around. Something that helped
me to better understand this is the fact that a Derby database has a set
of system table (including SYS.SYSSCHEMAS) and a schema does not.
Another concept of note is that the schema name is part of the table
identifier. It defaults properly when the default schema is set to that
of table. Schemas are simply a way of organizing/grouping tables in a
Derby system - you can join tables across shemas if you have the proper
permissions (more an issue when using 10.2 and 10.3 than earlier releases.
HTN