Re: JPA required?

[José Ventura]

By default, connections start in Auto-Commit mode, which means every
statement is executed as a single transaction.
http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html#disable_auto_commit

To group several statements in a transaction, you first set autoCommit to
false (just like your example); then, execute all the statements you want;
and then execute *conn.commit()* (or *conn.rollback()* if you detect an
error). Since you said everything is working, I assume there is a call to
conn.commit() somewhere in your code.

If the call to commit() is being made "too soon", such as after each
statement (kind of what happens with autoCommit on), and the host goes down
between statements, you have to worry about consistency yourself. Consider
a program that withdraws money from one bank account, commits, then
deposits into another account, then commits: if the host goes down between
commits, some money just disappeared.

On the other hand, if the call to commit() is being made "too late" (only
when the user quits the program, for example), then if the host goes down
before that, none of the new data will be in the database. It will still be
consistent, but data will have been lost.

What you need to do is find out what statements (if any) should be grouped
in atomic blocks for your application, then commit() each time one of those
blocks finish.


On Wed, Feb 13, 2013 at 5:28 PM, JimCrowell37  wrote:

> Bergquist, Brett-2 wrote
> > For your use case, probably not.   JPA is not something that is going to
> > solve a database element corruption and in fact with JPA and its normal
> > use, you have less control when entity changes are flushed to the
> > database.
> >
> > Note that if you don't have your database stored on medium that has write
> > caching, if the host computer goes down, the database is not going to be
> > corrupt; it might not have the latest change, but it will be consistent
> if
> > you are using transactions.
>
> Brett...
> My Derby Database is hosted on my HDD in a /db folder under the Java
> Application folder...
>
> >" ... if you are using transactions."
> The above phrase made me look at my code to see if I am using transactions.
> I wrote the derby software some time ago and I have the following lines of
> code but I
> do not understand why I did the "conn.setAutoCommit(false)" statement...
>
> //  Control transactions manually...
> //  NOTE:   Auto commit is on by default in JDBC...
> conn.setAutoCommit(false);
>
> Everything is working fine but I wanted to assure myself that the above
> operation is OK.
>
> Many Thanks,
> Jim...
>
>
>
>
>
> --
> View this message in context:
> http://apache-database.10148.n7.nabble.com/JPA-required-tp127242p127277.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>

Re: JPA required?

[José Ventura]

Just a quick tip: Derby's documentation includes examples and tutorials
that do not use JPA. Start here and work your way through Activities 3 and
4:

http://db.apache.org/derby/docs/10.9/getstart/index.html

Hope that helps.


On Wed, Feb 13, 2013 at 2:13 PM, Bergquist, Brett wrote:

> For your use case, probably not.   JPA is not something that is going to
> solve a database element corruption and in fact with JPA and its normal
> use, you have less control when entity changes are flushed to the database.
>
> Note that if you don't have your database stored on medium that has write
> caching, if the host computer goes down, the database is not going to be
> corrupt; it might not have the latest change, but it will be consistent if
> you are using transactions.
>
> -Original Message-
> From: JimCrowell37 [mailto:jimcrow...@email.com]
> Sent: Tuesday, February 12, 2013 4:52 PM
> To: derby-user@db.apache.org
> Subject: JPA required?
>
> Hello,
>
> I have spent today reading up on JPA and I have a question if I really
> need it.
>
> I have a data entry form class where each data entry field is associated
> with an element of a Derby dynamic database table. As each data entry field
> looses it's form focus, I shall write the entered data entry value to the
> Database table. The Database table primary key is the fields row / column
> indices.
>
> Since my goal is to save all data entries in a persistent manner, my
> question is do I need to implement JPA?
>
> I think that the worst case scenario is that my end users host computer
> goes down sometime during the Database write processing and that Database
> element may be corrupted.
>
> That thought is what led me to learning about JPA to persist the Database
> transaction.
>
> Do I need to implement JPA or is there a better way to achieve my
> persistence goal?
>
> Regards,
> Jim...
>
>
>
> --
> View this message in context:
> http://apache-database.10148.n7.nabble.com/JPA-required-tp127242.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>
>

Re: What do you use to get an ER diagram of an existing Derby database?

[José Ventura]

Both SQuirreL 
SQL
(GPL)
and DBVisualizer  (free
version available) have diagram-generating features.


On Thu, Jan 24, 2013 at 3:54 PM, Stefan R.  wrote:

> Wie are using http://schemaspy.sourceforge.net together with
> http://www.graphviz.org
>
> Result look nice and it is intgratable with maven vor ant.
>
> Am 23.01.2013 20:20 schrieb "Al Eridani" :
>
> >
> > Hello, we have an existing Derby database that we would like to
> > document with an entity relationship diagram.
> >
> > I'm looking for a tool (preferably free) that will be able to generate
> > the diagram by inspecting the database.
> >
> > I use Eclipse under Linux and I have the Data Tools Platform
> > Enablement Extender SDK installed; it works well to make queries,
> > but no diagramming.
> >
> > Thank you in advance for any pointers.
> >
> > Al
>
>

Re: Getting transitive closure?

[José Ventura]

I might be mistaken, but I think Derby does not support this
out-of-the-box. There are, however, a few interesting solutions suggested
in this (2009) thread:

http://old.nabble.com/Recursive-query-on-common-table-td25819772.html


On Sun, Oct 14, 2012 at 8:52 AM, John English wrote:

> If I have a table defined like so:
>
>   create table COMPONENTS (
> ID  integer   generated always as identity,
> COMPONENT   varchar(255)  not null,
> PARENT  integer,
> constraint COMPONENTS_PK  primary key (ID),
> constraint COMPONENTS_1   foreign key (PARENT)
>   references COMPONENTS(ID)
>   on delete cascade
>   );
>
> with (for example):
>
>   insert into COMPONENTS(COMPONENT,PARENT) values
>  ('IC package',null),-- ID = 1
>  ('Flip-flop',1),-- ID = 2
>  ('D-type',2),   -- ID = 3
>  ('JK flip-flop',2), -- ID = 4
>  ('7474 dual D-type flip-flop',3),
>  ('4013 dual CMOS D-type flip-flop',3),
>  ('7476 dual JK flip-flop with preset and clear',4),
>  ('4027 dual CMOS JK flip-flop',4);
>
> Is there a clever an efficient way to get the set of all flip-flop
> packages, i.e. ID=2, all items with parent=2, and so on down the chain of
> descendants? Or in other words, the transitive closure of the set?
>
> TIA,
> --
> John English
>

Re: Using Identity Colums with or without Sequences derby 10.8.1.2

[José Ventura]

I believe this documentation page answers your question about how to
retrieve the generated values:

http://db.apache.org/derby/docs/10.7/ref/crefjavstateautogen.html

In your case, the INSERT statement that creates a record in INPUTFILES will
return a ResultSet which contains the generated value. You can then use
this value in subsequent INSERT statements to create child records in table
OUTPUTFILES.

Your next question, about the difference between GENERATED ALWAYS and
GENERATED BY DEFAULT is explained here:

http://db.apache.org/derby/docs/10.2/ref/rrefsqlj37836.html

Basically, GENERATED BY DEFAULT will only generate a value if you don't
explicitly provide one. GENERATED ALWAYS will simply not allow you to
specify the value, and will always generate one.

Hope that helps...

On Mon, Oct 1, 2012 at 10:26 AM,  wrote:

> **
> Hi,
> I'd like to use an identity-column And I am not quite sure how to receive
> the used number, if I used default.
> Actually I need the particular identity-value for the entries of other
> tables, since they are supposed to reference it.
>
> Am I forced to give those identities for my own like with a
> sequence-number, or can I somwhow get it by some "magic" statement?
> 
> here my particular case:
> CREATE TABLE "APP"."INPUTFILES"
> (
>   INPUTFILE_ID   int generated always as
> identity
>   ,"NAME"VARCHAR(512) NOT NULL
>   ,"TMPST_IN"TIMESTAMP NOT NULL
> DEFAULT CURRENT_TIMESTAMP
> );
>
> CREATE TABLE "APP"."OUTPUTFILES"
> (
>   OUTPUTFILE_ID int generated always as
> identity
>   ,INPUTFILE_ID int
>   ,"NAME"VARCHAR(512) NOT NULL
>   ,"TMPST_IN"TIMESTAMP NOT NULL
> DEFAULT CURRENT_TIMESTAMP
> );
>
>
> ALTER TABLE "APP"."INPUTFILES" ADD CONSTRAINT "PK_INPUTFILE_ID" PRIMARY
> KEY ("INPUTFILE_ID");
>
>  ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "PK_OUTPUTFILE_ID"
> PRIMARY KEY ("OUTPUTFILE_ID");
>
> ALTER TABLE "APP"."OUTPUTFILES" ADD CONSTRAINT "FK_INPUTFILE_ID" FOREIGN
> KEY ("INPUTFILE_ID") REFERENCES "APP"."INPUTFILES" ("INPUTFILE_ID") ON
> DELETE CASCADE ON UPDATE RESTRICT;
>
> ---
>
> Am I right that I need to use generated by default as identity instead of int
> generated always as identity
> And that there is no way to get the identity value if generated by derby?
>
> Malte Kempff**
>
>
>

Re: Derby Network Server - Questions about -h parameter

[José Ventura]

I don't have an answer, but I *think* this depends largely on the
underlying OS. Basically, on some OSs you can listen on "::" and both ipv4
and ipv6 traffic will be delivered to your app; on others, you might need
extra tweaking or two listening sockets:

http://stackoverflow.com/questions/10378471/how-to-support-both-ipv4-ipv6-on-java

Hope that helps...

On Tue, Aug 28, 2012 at 12:56 PM, Oskar Zinger  wrote:

> Hello Derby Community,
>
> I have a question about Derby Network Server start and specifically about
> -h option / parameter.
>
> NetworkServerControl(InetAddress address, int portNumber)
> This constructor creates an instance that listens on the specified
> portNumber on the specified address. The InetAddress will be passed to
> ServerSocket. NULL is an invalid address value. The following examples show
> how you might allow Network Server to accept connections from other hosts:
>
> //accepts connections from other hosts on an IPv4 system
> NetworkServerControl serverControl =
>   new NetworkServerControl(InetAddress.getByName("0.0.0.0"),1527);
>
> //accepts connections from other hosts on an IPv6 system
> NetworkServerControl serverControl =
>   new NetworkServerControl(InetAddress.getByName("::"),1527);
>
> If I use "::" for IPv6 system, will the network server accept connections
> coming from IPv4 network, and vice versa?
>
> Thanks,
> Oskar
>
> --
> Oskar Zinger
> Product Architect - IBM Tivoli Netcool/Impact
> ozin...@us.ibm.com
>

Re: Starting Derby with Database

[José Ventura]

I think that, if you are using bash, the variable needs to be set before
the command. Try this:

$ DERBY_OPTS="-Dderby.system.home=/greenWorldDB" startNetworkServer

If that fails, export the variable in a separate command:

$ export DERBY_OPTS="-Dderby.system.home=/greenWorldDB"
$ startNetworkServer

Hope that helps...

On Mon, Aug 6, 2012 at 4:19 PM, Jordan-61  wrote:

>
> Greetings José, thank you for your input. I tried the command switch you
> suggested and it generated an error that it was an invalid command.
>
> [smartplant@SmartPlantWeb WEB-INF]$ startNetworkServer
> DERBY_OPTS=-Dderby.system.home=/greenWorldDB
> Mon Aug 06 14:08:11 EDT 2012 : Security manager installed using the Basic
> server security policy.
> Mon Aug 06 14:08:11 EDT 2012 : Invalid number of arguments for command
> start.
> Usage: NetworkServerControl 
> Commands:
> start [-h ] [-p ] [-noSecurityManager] [-ssl ]
> shutdown [-h ][-p ] [-ssl ] [-user ]
> [-password ]
> ping [-h ][-p ] [-ssl ]
> sysinfo [-h ][-p ] [-ssl ]
> runtimeinfo [-h ][-p ] [-ssl ]
> logconnections {on|off} [-h ][-p ] [-ssl ]
> maxthreads [-h ][-p ] [-ssl ]
> timeslice [-h ][-p ] [-ssl ]
> trace {on|off} [-s ][-h ][-p ] [-ssl
> ]
> tracedirectory [-h ][-p ] [-ssl
> ]
>
> I was searching for other suggestions and ran across this:
> http://www.vogella.com/articles/ApacheDerby/article.html
>
> It recommends starting the derby server using the startNetworkServer
> command
> and then attaching to the database using the  command
> jdbc:derby://localhost:1527/c:\temp\mydatabase.
>
> However, attempting to connect to the database using that command generates
> an invalid path error despite the path existing.
>
> [root@SmartPlantWeb WEB-INF]# jdbc:derby://localhost:1527/greenWorldDB
> bash: jdbc:derby://localhost:1527/greenWorldDB: No such file or directory
> [root@SmartPlantWeb WEB-INF]# jdbc:derby://localhost:1527//greenWorldDB
> bash: jdbc:derby://localhost:1527//greenWorldDB: No such file or directory
> [root@SmartPlantWeb WEB-INF]# cd /greenWorldDB
> [root@SmartPlantWeb greenWorldDB]#
>
> Do you have suggestions for either method of connecting to the database?
> Thanks in advance,
>
> Jordan
>
>
>
>
> José Ventura-3 wrote:
> >
> > I believe that the startNetworkServer command uses an environment
> variable
> > DERBY_OPTS to hold additional properties. Try setting DERBY_OPTS=*-D*
> > derby.system.home=/greenWorldDB and running the command again (without
> the
> > -derby.system.home part).
> >
> > Also, note the -D in front of the property name, as that is a Java system
> > property.
> >
> > Hope that helps...
> >
> > On Wed, Aug 1, 2012 at 2:50 PM, Jordan  wrote:
> >
> >> Greetings all-
> >>
> >> I'm a new Derby user and am trying to recover a derby database from a
> >> server that died that was set up by a co-worker who passed away as well.
> >> I
> >> have the entire database set up on a new Fedora 17 server which contains
> >> the service.properties file and the log, tmp, and seg0 directories and
> >> Derby is installed and configured properly with DERBY_HOME and
> JAVA_HOME.
> >>
> >> I have read through some of the documentation on getting started but
> seem
> >> to be missing something basic. How do I start Derby from the terminal
> and
> >> point it to my database folder? I tried the below method but it didn't
> >> work. How is the derby.system.home parameter entered while starting the
> >> database?
> >>
> >> [smartplant@SmartPlantWeb WEB-INF]$ startNetworkServer
> >> -derby.system.home=/greenWorldDB
> >> Wed Aug 01 13:38:03 EDT 2012 : Argument -derby.system.home=/greenWorldDB
> >> is unknown.
> >>
> >> Any tips would be appreciated. Thank you,
> >>
> >> Jordan
> >>
> >
> >
> --
> View this message in context:
> http://old.nabble.com/Starting-Derby-with-Database-tp34242141p34262881.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>

Re: Starting Derby with Database

[José Ventura]

I believe that the startNetworkServer command uses an environment variable
DERBY_OPTS to hold additional properties. Try setting DERBY_OPTS=*-D*
derby.system.home=/greenWorldDB and running the command again (without the
-derby.system.home part).

Also, note the -D in front of the property name, as that is a Java system
property.

Hope that helps...

On Wed, Aug 1, 2012 at 2:50 PM, Jordan  wrote:

> Greetings all-
>
> I'm a new Derby user and am trying to recover a derby database from a
> server that died that was set up by a co-worker who passed away as well. I
> have the entire database set up on a new Fedora 17 server which contains
> the service.properties file and the log, tmp, and seg0 directories and
> Derby is installed and configured properly with DERBY_HOME and JAVA_HOME.
>
> I have read through some of the documentation on getting started but seem
> to be missing something basic. How do I start Derby from the terminal and
> point it to my database folder? I tried the below method but it didn't
> work. How is the derby.system.home parameter entered while starting the
> database?
>
> [smartplant@SmartPlantWeb WEB-INF]$ startNetworkServer
> -derby.system.home=/greenWorldDB
> Wed Aug 01 13:38:03 EDT 2012 : Argument -derby.system.home=/greenWorldDB
> is unknown.
>
> Any tips would be appreciated. Thank you,
>
> Jordan
>

Re: Stopping network server & cold backup

[José Ventura]

I'm using the tips in this page to achieve a similar goal:

http://db.apache.org/derby/docs/10.6/adminguide/cadminhubbkup75469.html

Basically, instead of shutting down the server, you use a command to freeze
the DB, then copy the files, then unfreeze the DB. The freeze call will
block until it is safe to copy the files.

On Tue, Jul 31, 2012 at 5:49 PM, Mo Maison  wrote:

> Hello Derby users,
>
> I encounter sometimes a problem during cold backups :
> Derby is launched as a server (listening localhost only ) ;
> I stop the network server with standard command :
>   java -cp ... \
>   org.apache.derby.drda.**NetworkServerControl shutdown \
>   -h localhost -p 1527
>
> Right after command has returned, I perform a tar of the
> database files.
> However, I have observed that, on a slow filesystem, somes
> files get deleted while tar is reading them. Which let me think
> that server is not fully stopped when process returns
> (and thus backup may be corrupted).
>
> Is that expected ?
> How can I be sure that everything is quiescent before performing
> the cold backup ?
> I use Derby 10.6.2 on 32 bits Linux.
>
> Thank you for your advices,
>
>  M. Maison
>

Re: AW: schema-questions

[José Ventura]

Out of curiosity, and maybe a little off-topic: is there a reason to use a
separate schema, when you can use a separate database?

For example, if I have two completely unrelated applications, I think I
would create two databases, and one datasource for each:




On Oracle I may have one database with several schemas, hence the need to
specify the one I want. On Derby, would there be a benefit to reuse the
same database with multiple schemas?

Just asking off the top of my head, I haven't really tried the above...

- José

On Mon, Jul 23, 2012 at 11:32 AM, Kristian Waagan <
kristian.waa...@oracle.com> wrote:

>  On 23.07.2012 15:34, malte.kem...@de.equens.com wrote:
>
> Actually I found 2 hints
>
>1. use SET CURRENT SCHEMA
> 2. Make your own custom authentication
>
>
> For completeness, one can also specify the schema explicitly in the
> queries of course. I don't know if there are scenarios where this approach
> won't work.
>
>
> --
> Kristian
>

Re: Null values with conditions

[José Ventura]

I think the suggested answer will allow all values to be null. If at least
one pair must not be null, we can add the following:

check
(
(
( a1 is not null and a2 is not null )
or
( a1 is null and a2 is null )
)
and
(
( b1 is not null and b2 is not null )
or
( b1 is null and b2 is null )
)
*and ( a1 is not null or b1 is not null )   *
)

(we can test only a1/b1 here because the "other member of the pair" has
already been tested earlier).

On Mon, May 7, 2012 at 9:36 AM, Rick Hillegas wrote:

> On 5/7/12 5:14 AM, biene_m...@yahoo.de wrote:
>
>> Hi,
>>
>> i have a table with 4 columns:
>> A1
>> A2
>> B1
>> B2
>> i now need something like this (in relation to null values):
>> (A1 AND A2) OR (B1 AND B2)
>>
>> So that if A1 is not null A2 must not be null and the other way around
>> (so if A2 is not null A1 is not allowed to be null).
>> The same for B1 and B2.
>> And at least one of the 2 pairs must not be null.
>>
>> Can someone tell me if this is possible or how?
>>
>>
>> Thanks for any suggestions / answers
>>
>> Biene Majo
>>
>>  Hi Biene,
>
> What you describe sounds like a CHECK constraint to me. Not sure that I
> understand the exact condition you're trying to catch, but something like
> the following might work for you:
>
> create table t
> (
>a1 int,
>a2 int,
>b1 int,
>b2 int,
>
>check
>(
>(
>( a1 is not null and a2 is not null )
>or
>( a1 is null and a2 is null )
>)
>and
>(
>( b1 is not null and b2 is not null )
>or
>( b1 is null and b2 is null )
>)
>)
> );
>
> Hope this helps,
> -Rick
>

Re: Understanding an UPDATE

[José Ventura]

One way to do it is to only update T1 when a corresponding record exists on
T2:

update t1
set txt1 = ( select txt2 from t2 where t2.id = t1.id )
where exists ( select * from t2 where t2.id = t1.id )


On Tue, Mar 6, 2012 at 12:10 PM, TXVanguard  wrote:

>
> Do I need to duplicate the WHERE clause used in the SELECT statement?  Can
> you show me how to rewrite the UPDATE statement? (I'm not looking for help
> with homework; I'm a beginner, trying to learn SQL while I'm porting some
> software at work.)  Thanks.
>
>
> Jean-Yves Linet-2 wrote:
> >
> > You don't have where clause in your update, so all rows of t1 are
> updated.
> > And as you have no row in t2 with id=1, the select return a null value.
> >
> > Le 6 mars 2012 02:19, TXVanguard  a écrit :
> >
> >>
> >> When I run the following SQL statements in JavaDB:
> >>
> >> 
> >>
> >> drop table t1;
> >> drop table t2;
> >>
> >> create table t1 ( TXT1 varchar(6), ID integer );
> >>
> >> create table t2 ( ID integer, TXT2 varchar(12));
> >>
> >> insert into t1 (TXT1,id) values ('ONE',1);
> >> insert into t1 (TXT1,id) values ('TWO',2);
> >>
> >> insert into t2 (id,TXT2) values (2,'TWO');
> >> insert into t2 (id,TXT2) values (3,'THREE');
> >>
> >> UPDATE t1 SET TXT1 = (select (TXT2) FROM t2 WHERE (t2.ID = t1.ID));
> >>
> >> =
> >>
> >> then, table t1 has the following values:
> >>
> >>VAL1  ID
> >> ROW 1:   1
> >> ROW 2: 'TWO' 2
> >>
> >> Why is the value of column VAL1 in the first row set to  by the
> >> update
> >> statement?
> >> --
> >> View this message in context:
> >> http://old.nabble.com/Understanding-an-UPDATE-tp33448196p33448196.html
> >> Sent from the Apache Derby Users mailing list archive at Nabble.com.
> >>
> >>
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/Understanding-an-UPDATE-tp33448196p33451523.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>

Re: How to approach Derby embedded?

[José Ventura]

The first question would be, "Is JDBC too much for me?"

If you are familiar with JDBC and frameworks built upon it (Hibernate,
etc), then Derby's required configuration is definitely tiny by comparison.
You can run an embedded database by just:

   - adding derby.jar to your classpath (if using maven, that's already
   done for you)
   - specifying your JDBC url as
   "jdbc:derby:path/to/database/directory;create=true"
   - specifying your JDBC driver as "org.apache.derby.jdbc.EmbeddedDriver"

Once you get a JDBC Connection you can use it to execute CREATE TABLEs and
the like, and then you're ready to go.

If you aren't familiar with JDBC (or think it is overkill as well), then
maybe you could take a look at other persistent stores: the only one that
comes to my mind right now is Prevayler , but I'm
sure there are several others (I know of several NoSQL stores -- but if
Derby is overkill for you then I suppose they will be too).

Just my 2 cents.

On Sat, Mar 3, 2012 at 7:51 PM, Kenneth McDonald <
kenneth.m.mcdon...@sbcglobal.net> wrote:

> I need a very simple (even SQL is more than what I need) embedded database
> for some persistent stores. Derby seems like it would be ideal, except that
> even configuring seems to require more than what I need. In particular, I'm
> seeing in the docs that I need to edit my class path (which I shouldn't
> need to do for my purposes), that I may need to set up more complex
> configurations (than I need for my purposes), etc. etc. Can this be
> simplified?
>
> More specifically; I have Derby downloaded and installed via Maven
> (actually via SBT in Scala). I want to run it as a purely embedded jar, to
> provide persistent store and dirt-simple queries. I don't want to worry
> about shell variables, CLIs, or any similar hooey--just the simplest
> possible SQL to store/access my data. Can I do this with Derby?
>
> My comparison point is bdb the way bdb was under Python several versions
> ago--not the way bdb has become more recently. Can I achieve a similar
> level of simplicity?
>
> Thanks,
> Ken

Re: Shutdown DB does not work / threadding issue

[José Ventura]

Try calling .join() on the thread object you created to run your Runnable.
This will make the current thread wait until the child has finished. I
forgot the reason why is solves the problem, it must be in the Java
concurrency tutorials somewhere.
On Feb 21, 2012 4:26 PM, "Karl Weber"  wrote:

Re: how to make two attributes unique?

[José Ventura]

What you need is an UNIQUE constraint with more than one column.
Follow the reference on ALTER TABLE syntax here:
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj13590.html

In your case, the statement is:

ALTER TABLE my_table ADD CONSTRAINT unique_ou_per_div UNIQUE ( divisionId, ou )

Update: Just realized that the above documentation does not clearly
indicate that there can be multiple columns inside the parenthesis.
Maybe we should file a doc bug?

On Mon, Oct 10, 2011 at 5:19 AM, Spezifikum  wrote:
> Hi,
> i have organisational units (ou) within divisions. The name within the
> division should be unique.
> ou:
> name=CHAR(80)
> divisionID=INT CONSTRAINT OperationalUnitDivisionID_FK REFERENCES division
>
> These two attributes should be unique in combination.
> I only found many examples on how to make one attribute unique, but i guess
> this is a dumb newbie question - sorry.
> As a side note: There never will be OUs nested within OUs.
>
> Thanks
> Malte
>

Re: trouble with ipv6 connection

[José Ventura]

(The mailing list seems to have rejected my message, trying again without URL).

Not a solution to your problem, but binding to 0.0.0.0 will make the
server listen on all IPv4 interfaces/addresses, not IPv6 ones. To make
it listen on all IPv6 interfaces/addresses you need to specify :: (two
colons) as the bind address.

There might be ways to make it listen to all interfaces of both kinds,
see question 21657 on serverfault(dot)com.

On Wed, Sep 21, 2011 at 5:36 PM, Kathey Marsden
 wrote:
> I am having some trouble making a NetworkServer IPv6 connection and think it
> may be a user error or machine configuration issue, but am not sure what
> that might be.
>
> I have two dual stack IPv6/IPv4 machines running Linux.
>
>  - If I start the server listening on the IPv6 hostname and try to connect
> from the second machine, I get the permission denied error below.  I think
> this connection attempt is never actually reaching my running Network
> Server, but is being blocked somehow at the machine level.  If it were
> windows I would blame the firewall software.
>
>
> ij> connect
> 'jdbc:derby://:1527/mydb;user=user2;password=pass2;create=true';
> ERROR 08001: java.net.SocketException : Error connecting to server
> wicopt1-v6.rtp.raleigh.ibm.com on port 1527 with message Permission denied.
> java.sql.SQLNonTransientConnectionException: java.net.SocketException :
> Error connecting to server wicopt1-v6.rtp.raleigh.ibm.com on port 1527 with
> me
> ssage Permission denied.
>        at
> org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
> Source)
>        at org.apache.derby.client.am.SqlException.getSQLException(Unknown
> Source)
>        at org.apache.derby.jdbc.ClientDriver.connect(Unknown Source)
>        at java.sql.DriverManager.getConnection(DriverManager.java:322)
>        at java.sql.DriverManager.getConnection(DriverManager.java:297)
>        at org.apache.derby.impl.tools.ij.ij.dynamicConnection(Unknown
> Source)
>        at org.apache.derby.impl.tools.ij.ij.ConnectStatement(Unknown Source)
>        at org.apache.derby.impl.tools.ij.ij.ijStatement(Unknown Source)
>        at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown
> Source)
>        at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
>        at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
>        at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
>        at org.apache.derby.impl.tools.ij.Main.main(Unknown Source)
>        at org.apache.derby.tools.ij.main(Unknown Source)
> Caused by: org.apache.derby.client.am.DisconnectException:
> java.net.SocketException : Error connecting to server
> wicopt1-v6.rtp.raleigh.ibm.com on por
> t 1527 with message Permission denied.
>        at org.apache.derby.client.net.NetAgent.(Unknown Source)
>        at org.apache.derby.client.net.NetConnection.newAgent_(Unknown
> Source)
>        at org.apache.derby.client.am.Connection.(Unknown Source)
>        at org.apache.derby.client.net.NetConnection.(Unknown Source)
>        at org.apache.derby.client.net.NetConnection40.(Unknown Source)
>        at
> org.apache.derby.client.net.ClientJDBCObjectFactoryImpl40.newNetConnection(Unknown
> Source)
>        ... 12 more
> Caused by: java.net.SocketException: Permission denied
>        at java.net.PlainSocketImpl.socketConnect(Native Method)
>        at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:383)
>        at
> java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:245)
>        at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:232)
>        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:377)
>        at java.net.Socket.connect(Socket.java:539)
>        at java.net.Socket.connect(Socket.java:488)
>        at java.net.Socket.(Socket.java:385)
>        at java.net.Socket.(Socket.java:199)
>        at javax.net.DefaultSocketFactory.createSocket(SocketFactory.java:1)
>        at org.apache.derby.client.net.OpenSocketAction.run(Unknown Source)
>        at
> java.security.AccessController.doPrivileged(AccessController.java:251)
>
> I started the server like:
> java  -Dderby.drda.logconnections=true
>  org.apache.derby.drda.NetworkServerContro
> l start -noSecurityManager -h 
>
> (I tried also with the options at
> :http://db.apache.org/derby/docs/10.2/adminguide/tadminconfigipx.html but
> still no luck).
>
> A few more data points.
>  - I can connect to the IPv6 server if I connect from the IPv4 shell of the
> same machine.
> - If I start the server specifying the IPv4 hostname I can connect from the
> second machine.
> - If I start the server with hostname 0.0.0.0 I can connect to the IPv4
> hostname but not the IPv6 one.
> - I can successfully ping6 the IPv6 server hostname from the second machine.
>
>
> Any idea what might cause this?  In the past for IPv6 testing I have only
> had access to one machine on the local network so had tested connecting to
> the IPv6 server, only from the IPv6 and IPv4 shells of the same m

Re: Derby secure by default

[José Ventura]

(perhaps this has already been discussed in the devs' mailing list, if so,
forgive my ignorance).

I'm not sure whether making the default value "on" will actually improve
security as a whole. If a developer hasn't given thought to security, there
are plenty of other pitfalls that may compromise an application (e.g. "where
should I store the (previously unneeded yet now required) username and
password?").

On the other hand, if s/he did in fact think about security, then odds are
that are a simple, concise documentation will point him/her to happily turn
on the switch with minimum nuisance, and proceed to secure the rest of the
application.

@Roy:
I believe that, since the change would happen in a next major release (11
vs. current 10.x), backwards compatibility wouldn't be *required* (but would
certainly be *appreciated*).

On Mon, Sep 19, 2011 at 4:55 PM, Rick Hillegas wrote:

> Hi Mike,
>
> Some comments inline...
>
> On 9/19/11 10:38 AM, Mike Matrigali wrote:
>
>> I am not sure how it applies to all of these points, but I am wondering if
>> secure by default should be implemented on a per database basis rather than
>> a system level basis?  It seems wierd that security could
>> change based on how the next embedded startup set a flag.
>>
> I think that it should behave like derby.database.**sqlAuthorization: once
> it's been turned on it is stored in the database and you can't turn it off
> at the system level. I agree that it would be weird to let the next user
> subvert the security of your database by flipping a command line switch.
>
>>
>> What about having the property be part of what user requests at database
>> creation time?  And maybe allow some secure way either disable or enable
>> it.  The discussion could continue on what the default for a newly created
>> database would be.  At least for point 1-4 seems to make more sense, not
>> sure about 5,6.
>>
>> I personally think many of these points make most sense for derby network
>> server.
>>
> I'm also concerned about the embedded database on a USB stick. I could
> argue that it is more vulnerable than the server-side database locked up in
> a machine room.
>
>> While many possibly get in the way for many zero-admin
>> embedded applications.
>>
> I'm imagining that this change may be fairly unobtrusive. For an embedded
> database which has only one user (the dbo), the big change is that the dbo
> has to specify a username and password. There won't be any need to GRANT
> access to other users so (2) won't be noticed. Items (3) and (4) won't
> burden most applications. (5) and (6) are only issues for client/server
> usage.
>
>>  Since we have one codeline for the most part
>> for both it is hard to have one default.
>>
> I agree that a common default would be best. It will make it easier to
> reason about Derby's behavior and simplify our user guides.
>
> Thanks,
> -Rick
>
>
>>
>> Rick Hillegas wrote:
>>
>>> The Derby developers are considering introducing a single master security
>>> property. Turning this property on will enable most Derby security
>>> mechanisms:
>>>
>>> 1) Authentication - Will be on, requiring username/password credentials
>>> at connection time. Derby will supply a default authentication mechanism.
>>>
>>> 2) SQL authorization - Will be on, hiding a user's data from other
>>> people. In addition, Derby will support more SQL Standard protections for
>>> Java routines.
>>>
>>> 3) File permissions - Will be tightened as described by DERBY-5363.
>>>
>>> 4) PUBLIC -This keyword will not be allowed as a user name.
>>>
>>> 5) SSL/TLS encryption - Will shield client/server traffic.
>>>
>>> 6) Server administration -  Will require credentials.
>>>
>>> When the property is off, Derby will behave as it does today:
>>> Authentication, authorization, and network encryption will be off, file
>>> permissions will inherit defaults from the account which runs the VM, PUBLIC
>>> will be a legal user name, and server administration won't need credentials.
>>>
>>> This new master property will make it easier to configure a more secure
>>> application. We want to introduce the property in an upcoming 10.x release,
>>> where it will default to being off. That means that it won't cause
>>> compatibility problems.
>>>
>>> Later on, we might change the default for this property so that it would
>>> normally be turned on. This would make Derby more secure out of the box at
>>> the cost of breaking existing applications. Many applications would need to
>>> explicitly turn the property off in order to run as they did previously.
>>> Release notes would document this behavioral change and we would bump the
>>> major release id from 10 to 11 in order to call attention to the change.
>>>
>>> We would like your feedback on this trade-off between security out of the
>>> box versus disruption. Should this extra security be enabled by default?
>>>
>>> Thanks,
>>> -Rick
>>>
>>>
>>>
>>
>>
>

Re: Move from embedded to network server

[José Ventura]

To make the embedded connection start a network server instead of an
embedded server, use the following property:

http://db.apache.org/derby/docs/10.5/adminguide/radminconfigstartnetworkserver.html

I think that page links to other useful properties in this scenario
(derby.drda.*).

On Mon, Sep 12, 2011 at 7:42 AM, johny_quest  wrote:

>
> I checked the configuration options for IBM Director and Derby and here are
> the available options:
>
>
> ;==
> ; Apache Derby
>
> ;==
> DbmsApplication = Apache Derby
> DbmsTcpIpListenerPort = 1527
> DbmsServerName = localhost
> DbmsDatabaseName = database
> DbmsUserId = tioadmin
> DbmsPassword = **
> DbmsDatabaseAppHome = C:\Program
>
> Files\IBM\Director\lwi\runtime\database\eclipse\plugins\org.apache.derby.core_10.5.3.1
> DbmsDatabaseHome = C:\Program Files\IBM\Director
>
> I can change the port here, probably I have to set the IP or host name
> instead of localhost, but there is no url to specify. Maybe I should change
> the DatabaseAppHome to where derby network server is installed (the lib
> folder with the java files).
>
>
> José Ventura-3 wrote:
> >
> > That's about right.
> >
> > I usually specify the directory I want by opening a command prompt in the
> > parent directory. For example, if the files are located in
> > C:/derby/databases/mydb, then I open a prompt in C:/derby/databases and
> > issue the start command from there. There are probably other ways to do
> > it,
> > check the documentation.
> >
> > In the above example, the URL you would use (both in Director and other
> > clients) would be "jdbc:derby://host:port/mydb" (note the database name,
> > which is the name of a subdirectory where derby was started).
> >
> > Hope that helps.
> >
> > On Mon, Sep 12, 2011 at 6:21 AM, johny_quest 
> wrote:
> >
> >>
> >> Ok, lets see if I got you correctly:
> >>
> >> I stop IBM Director.
> >> I start the Derby Network Server giving it the database directory as
> home
> >> (is this done within the start command?)
> >> I then reconfigure IBM Director to connect with the network mode url
> (not
> >> the embedded) and the new port number (because I set the Network server
> >> to
> >> listen on port 1528).
> >>
> >> I should then be able to connect simultaniously from more than one
> >> clients,
> >> right?
> >>
> >>
> >> José Ventura-3 wrote:
> >> >
> >> > Derby only allows one instance *of the database software* (RDBMS) to
> >> open
> >> > the files in a directory at a time. According to the error you
> >> received,
> >> > there already is an instance of the RDBMS open on c:/Program
> >> > Files/IBM/Director/database (probably started by an embedded
> connection
> >> in
> >> > IBM Director).
> >> >
> >> > Even if you use Derby Network Server, you will still be limited to one
> >> > instance *of the database software*, however this instance will accept
> >> > connections from any number of *clients*.
> >> >
> >> > I think what you want to do is stop IBM Director, then start Derby
> >> Network
> >> > Server upon that directory, then configure IBM Director to use a
> client
> >> > connection instead of an embedded connection. That way, both Director
> >> and
> >> > other clients (such as razorSQL) will be able to connect, read and
> >> write
> >> > data to the database.
> >> >
> >> > - José
> >> >
> >> > On Fri, Sep 9, 2011 at 12:02 PM, johny_quest 
> >> wrote:
> >> >
> >> >>
> >> >> Hello guys,
> >> >> I have the following situation, that I have tried to solve for a
> >> couple
> >> >> of
> >> >> week but I can't seem to get it working.
> >> >> Here is the deal.
> >> >>
> >> >> I have a derby database that is running in embedded mode as a part of
> >> an
> >> >> application (IBM Director software runs Apache Derby database by
> >> >> default).
> >> >>
> >> >> What I have to do, is to connect another type of software to that
> >> >> database
> >> >> using ODBC connector.
> >> >>
> >> >&g

Re: Move from embedded to network server

[José Ventura]

That's about right.

I usually specify the directory I want by opening a command prompt in the
parent directory. For example, if the files are located in
C:/derby/databases/mydb, then I open a prompt in C:/derby/databases and
issue the start command from there. There are probably other ways to do it,
check the documentation.

In the above example, the URL you would use (both in Director and other
clients) would be "jdbc:derby://host:port/mydb" (note the database name,
which is the name of a subdirectory where derby was started).

Hope that helps.

On Mon, Sep 12, 2011 at 6:21 AM, johny_quest  wrote:

>
> Ok, lets see if I got you correctly:
>
> I stop IBM Director.
> I start the Derby Network Server giving it the database directory as home
> (is this done within the start command?)
> I then reconfigure IBM Director to connect with the network mode url (not
> the embedded) and the new port number (because I set the Network server to
> listen on port 1528).
>
> I should then be able to connect simultaniously from more than one clients,
> right?
>
>
> José Ventura-3 wrote:
> >
> > Derby only allows one instance *of the database software* (RDBMS) to open
> > the files in a directory at a time. According to the error you received,
> > there already is an instance of the RDBMS open on c:/Program
> > Files/IBM/Director/database (probably started by an embedded connection
> in
> > IBM Director).
> >
> > Even if you use Derby Network Server, you will still be limited to one
> > instance *of the database software*, however this instance will accept
> > connections from any number of *clients*.
> >
> > I think what you want to do is stop IBM Director, then start Derby
> Network
> > Server upon that directory, then configure IBM Director to use a client
> > connection instead of an embedded connection. That way, both Director and
> > other clients (such as razorSQL) will be able to connect, read and write
> > data to the database.
> >
> > - José
> >
> > On Fri, Sep 9, 2011 at 12:02 PM, johny_quest 
> wrote:
> >
> >>
> >> Hello guys,
> >> I have the following situation, that I have tried to solve for a couple
> >> of
> >> week but I can't seem to get it working.
> >> Here is the deal.
> >>
> >> I have a derby database that is running in embedded mode as a part of an
> >> application (IBM Director software runs Apache Derby database by
> >> default).
> >>
> >> What I have to do, is to connect another type of software to that
> >> database
> >> using ODBC connector.
> >>
> >> First I read about the fact, that only one connection is possibla at a
> >> time.
> >> I tried to connect to the database with razorSQL just to see if I can
> >> connect, but the error is:
> >>
> >> DERBY SQL error: SQLCODE:
> >> -1, SQLSTATE: XJ040, SQLERRMC: Failed to start database 'c:/Program
> >> Files/IBM/Director/database' with class loader
> >> sun.misc.Launcher$AppClassLoader@11b86e7,
> >> see the next exception
> >> for details.::SQLSTATE: XSDB6Another instance of Derby may
> >> have already booted the database C:\Program Files\IBM\Director\database.
> >>
> >> After that I installed a new apache derby and set it in network mode. I
> >> had
> >> to change the port to 1528 because obviously the other instance is
> >> listening
> >> on 1527. Using the new setting I managed to connect to the server,
> create
> >> database and tables etc. - it is working, but I still cannot connect to
> >> the
> >> needed database. The error is the same.
> >>
> >> Please, can someone suggest a solution for this problem.
> >> I will be very grateful.
> >>
> >> --
> >> View this message in context:
> >>
> http://old.nabble.com/Move-from-embedded-to-network-server-tp32431926p32431926.html
> >> Sent from the Apache Derby Users mailing list archive at Nabble.com.
> >>
> >>
> >
> >
>
> --
> View this message in context:
> http://old.nabble.com/Move-from-embedded-to-network-server-tp32431926p32446393.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>

Re: Move from embedded to network server

[José Ventura]

Derby only allows one instance *of the database software* (RDBMS) to open
the files in a directory at a time. According to the error you received,
there already is an instance of the RDBMS open on c:/Program
Files/IBM/Director/database (probably started by an embedded connection in
IBM Director).

Even if you use Derby Network Server, you will still be limited to one
instance *of the database software*, however this instance will accept
connections from any number of *clients*.

I think what you want to do is stop IBM Director, then start Derby Network
Server upon that directory, then configure IBM Director to use a client
connection instead of an embedded connection. That way, both Director and
other clients (such as razorSQL) will be able to connect, read and write
data to the database.

- José

On Fri, Sep 9, 2011 at 12:02 PM, johny_quest  wrote:

>
> Hello guys,
> I have the following situation, that I have tried to solve for a couple of
> week but I can't seem to get it working.
> Here is the deal.
>
> I have a derby database that is running in embedded mode as a part of an
> application (IBM Director software runs Apache Derby database by default).
>
> What I have to do, is to connect another type of software to that database
> using ODBC connector.
>
> First I read about the fact, that only one connection is possibla at a
> time.
> I tried to connect to the database with razorSQL just to see if I can
> connect, but the error is:
>
> DERBY SQL error: SQLCODE:
> -1, SQLSTATE: XJ040, SQLERRMC: Failed to start database 'c:/Program
> Files/IBM/Director/database' with class loader
> sun.misc.Launcher$AppClassLoader@11b86e7,
> see the next exception
> for details.::SQLSTATE: XSDB6Another instance of Derby may
> have already booted the database C:\Program Files\IBM\Director\database.
>
> After that I installed a new apache derby and set it in network mode. I had
> to change the port to 1528 because obviously the other instance is
> listening
> on 1527. Using the new setting I managed to connect to the server, create
> database and tables etc. - it is working, but I still cannot connect to the
> needed database. The error is the same.
>
> Please, can someone suggest a solution for this problem.
> I will be very grateful.
>
> --
> View this message in context:
> http://old.nabble.com/Move-from-embedded-to-network-server-tp32431926p32431926.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>