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 Rick Schneider <ri...@hotmail.com> on 2009/10/08 21:38:32 UTC
Create DB Structure in one statement
OK.. I am trying to create my entire DB structure (about 6 tables or so) in a single JDBC Statement to embedded Derby 10.5. I keep running into issues with the proper syntax to separate the different statements. Is there a way to do this other than running multiple statements?
Thanks
Re: Create DB Structure in one statement
Posted by Rick Schneider <ri...@hotmail.com>.
Hi.. thanks for the quick response... that is exactly what I am trying, and
I am getting SQL Exceptions on the ; in the statement... I will have another
look and make sure its not something else going wrong..
Thanks again
--------------------------------------------------
From: "bruehlicke" <br...@gmail.com>
Sent: Thursday, October 08, 2009 4:23 PM
To: "Derby Discussion" <de...@db.apache.org>
Subject: Re: Create DB Structure in one statement
> Statement s = _connection.createStatement();
>
> s.execute(
> "CREATE TABLE TABLE1 (" +
> "ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS
> AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
> "NAME VARCHAR(80) NOT NULL," +
> "VALUE VARCHAR(12) NOT NULL," +
> "DESCRIPTION VARCHAR(255)," +
> "CREATED_BY VARCHAR(40) DEFAULT USER," +
> "CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
> "LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
> ")" + ";" +
> "CREATE TABLE TABLE2 (" +
> "ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS
> AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
> "NAME VARCHAR(80) NOT NULL," +
> "VALUE VARCHAR(12) NOT NULL," +
> "DESCRIPTION VARCHAR(255)," +
> "CREATED_BY VARCHAR(40) DEFAULT USER," +
> "CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
> "LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
> ")"
> );
>
>
> Should work - this would create 2 table in one statement.
>
> B-)
>
>
> On Thu, Oct 8, 2009 at 2:38 PM, Rick Schneider
> <ri...@hotmail.com> wrote:
>> OK.. I am trying to create my entire DB structure (about 6 tables or so)
>> in
>> a single JDBC Statement to embedded Derby 10.5. I keep running into
>> issues
>> with the proper syntax to separate the different statements. Is there a
>> way
>> to do this other than running multiple statements?
>>
>> Thanks
>
Re: Create DB Structure in one statement
Posted by Rick Schneider <ri...@hotmail.com>.
I did a workaround... I used the Pattern class to split into token on the ;,
and then looped through each statement to run it. Worked, and fairly clean.
--------------------------------------------------
From: "Knut Anders Hatlen" <Kn...@Sun.COM>
Sent: Thursday, October 08, 2009 5:36 PM
To: "Derby Discussion" <de...@db.apache.org>
Subject: Re: Create DB Structure in one statement
> bruehlicke <br...@gmail.com> writes:
>
>> Statement s = _connection.createStatement();
>>
>> s.execute(
>> "CREATE TABLE TABLE1 (" +
>> "ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS
>> AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
>> "NAME VARCHAR(80) NOT NULL," +
>> "VALUE VARCHAR(12) NOT NULL," +
>> "DESCRIPTION VARCHAR(255)," +
>> "CREATED_BY VARCHAR(40) DEFAULT USER," +
>> "CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
>> "LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
>> ")" + ";" +
>> "CREATE TABLE TABLE2 (" +
>> "ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS
>> AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
>> "NAME VARCHAR(80) NOT NULL," +
>> "VALUE VARCHAR(12) NOT NULL," +
>> "DESCRIPTION VARCHAR(255)," +
>> "CREATED_BY VARCHAR(40) DEFAULT USER," +
>> "CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
>> "LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
>> ")"
>> );
>>
>>
>> Should work - this would create 2 table in one statement.
>
> If this doesn't work (I didn't think we supported this, but I haven't
> actually tested), you could take a look at ij's API at
> http://db.apache.org/derby/javadoc/publishedapi/jdbc4/org/apache/derby/tools/ij.html,
> in particular the runScript() methods which allow you to pass in
> multiple statements through a stream.
>
>> On Thu, Oct 8, 2009 at 2:38 PM, Rick Schneider
>> <ri...@hotmail.com> wrote:
>>> OK.. I am trying to create my entire DB structure (about 6 tables or so)
>>> in
>>> a single JDBC Statement to embedded Derby 10.5. I keep running into
>>> issues
>>> with the proper syntax to separate the different statements. Is there a
>>> way
>>> to do this other than running multiple statements?
>>>
>>> Thanks
>
> --
> Knut Anders
>
Re: Create DB Structure in one statement
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
bruehlicke <br...@gmail.com> writes:
> Statement s = _connection.createStatement();
>
> s.execute(
> "CREATE TABLE TABLE1 (" +
> "ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS
> AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
> "NAME VARCHAR(80) NOT NULL," +
> "VALUE VARCHAR(12) NOT NULL," +
> "DESCRIPTION VARCHAR(255)," +
> "CREATED_BY VARCHAR(40) DEFAULT USER," +
> "CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
> "LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
> ")" + ";" +
> "CREATE TABLE TABLE2 (" +
> "ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS
> AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
> "NAME VARCHAR(80) NOT NULL," +
> "VALUE VARCHAR(12) NOT NULL," +
> "DESCRIPTION VARCHAR(255)," +
> "CREATED_BY VARCHAR(40) DEFAULT USER," +
> "CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
> "LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
> ")"
> );
>
>
> Should work - this would create 2 table in one statement.
If this doesn't work (I didn't think we supported this, but I haven't
actually tested), you could take a look at ij's API at
http://db.apache.org/derby/javadoc/publishedapi/jdbc4/org/apache/derby/tools/ij.html,
in particular the runScript() methods which allow you to pass in
multiple statements through a stream.
> On Thu, Oct 8, 2009 at 2:38 PM, Rick Schneider
> <ri...@hotmail.com> wrote:
>> OK.. I am trying to create my entire DB structure (about 6 tables or so) in
>> a single JDBC Statement to embedded Derby 10.5. I keep running into issues
>> with the proper syntax to separate the different statements. Is there a way
>> to do this other than running multiple statements?
>>
>> Thanks
--
Knut Anders
Re: Create DB Structure in one statement
Posted by bruehlicke <br...@gmail.com>.
Statement s = _connection.createStatement();
s.execute(
"CREATE TABLE TABLE1 (" +
"ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
"NAME VARCHAR(80) NOT NULL," +
"VALUE VARCHAR(12) NOT NULL," +
"DESCRIPTION VARCHAR(255)," +
"CREATED_BY VARCHAR(40) DEFAULT USER," +
"CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
"LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
")" + ";" +
"CREATE TABLE TABLE2 (" +
"ID BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1)," +
"NAME VARCHAR(80) NOT NULL," +
"VALUE VARCHAR(12) NOT NULL," +
"DESCRIPTION VARCHAR(255)," +
"CREATED_BY VARCHAR(40) DEFAULT USER," +
"CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
"LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP" +
")"
);
Should work - this would create 2 table in one statement.
B-)
On Thu, Oct 8, 2009 at 2:38 PM, Rick Schneider
<ri...@hotmail.com> wrote:
> OK.. I am trying to create my entire DB structure (about 6 tables or so) in
> a single JDBC Statement to embedded Derby 10.5. I keep running into issues
> with the proper syntax to separate the different statements. Is there a way
> to do this other than running multiple statements?
>
> Thanks