Syntax of derby db & squirrel-sql

[Mr. X.]

Hello.
I am using the derby db with squirrel-sql.

First, I am looking for a tutorial for the sql of Derby.

Second, there are some questions :
1. When creating a table :
CREATE TABLE customers (
id int generated always as identity constraint cust_pk primary key, ...

)

How can I resolve the value of the last id after inserting a record ?

2. How can I execute several sql-statements, no metter if the last one
failed.

drop table my_table ; // yet it not exists
Create table my_table ...;
Create table my_table2 ... ;

3. How can I connect the db from another computer ?
(Should I use for that the EmbeddedDriver, which I didn't see a choice of
entering the computer name / host name).

Thanks

 

[Arne Vajhøj]

First, I am looking for a tutorial for the sql of Derby.

Start at http://db.apache.org/derby/manuals/index.html#docs_10.3

Second, there are some questions :
1. When creating a table :
CREATE TABLE customers (
id int generated always as identity constraint cust_pk primary key, ...

)

How can I resolve the value of the last id after inserting a record ?
http://db.apache.org/derby/docs/10.3/ref/rrefidentityvallocal.html

2. How can I execute several sql-statements, no metter if the last one
failed.

drop table my_table ; // yet it not exists
Create table my_table ...;
Create table my_table2 ... ;

Your Java code should be able to handle that.

3. How can I connect the db from another computer ?
(Should I use for that the EmbeddedDriver, which I didn't see a choice of
entering the computer name / host name).

I have some working code with something like:

Class.forName("org.apache.derby.jdbc.ClientDriver");
Connection con = DriverManager.getConnection("jdbc:derby://localhost/mydb");


But I must admit that I can not find a document for that now.

Arne

 

[Mr. X.]

Most important thing is how can I use the derby-db from another computer in
network (question 3).
Is that what make the difference between EmbeddedDriver & ClientDriver,
because I didn't find any documentation for that neighter.

Thanks

 

[Mr. X.]

Also,
when I start the derby outside eclipse 3.3. (I am using the Eclipse plugin,
where in my code :
Java -jar "C:\Program Files\eclipse
3.3\plugins\org.apache.derby.core_10.3.2\derbynet.jar" start

I cannot access the database when running squirrel-sql, so I getting the
following error :
myDB: The connection was refused because the database myDB was not found.

(the instance was myDB).

When I start the derby from eclipse :
right click on package explorer + project -> apache derby -> start derby
network server.
and trying again access the database when running squirrel-sql, everything
works fine.

What may be the problem, that running from Eclipse works for accessing the
db from squirrel-sql , and otherwise it doesn't ?

Thanks

 

[RedGrittyBrick]

Hello.
I am using the derby db with squirrel-sql.

First, I am looking for a tutorial for the sql of Derby.

Do you mean something like this?

Second, there are some questions :
1. When creating a table :
CREATE TABLE customers (
id int generated always as identity constraint cust_pk primary key, ...
)
How can I resolve the value of the last id after inserting a record ?
http://db.apache.org/derby/faq.html#identity


2. How can I execute several sql-statements, no metter if the last one
failed.

drop table my_table ; // yet it not exists
Create table my_table ...;
Create table my_table2 ... ;

a) Put the statements in a text file, start ij and use the command
run 'filename';
b) Write a small Java apllication using JDBC.
etc

3. How can I connect the db from another computer ?

I think you are looking for a tutorial on starting Derby
http://db.apache.org/derby/papers/DerbyTut/index.html

(Should I use for that the EmbeddedDriver, which I didn't see a choice of
entering the computer name / host name).

You seem confused: the embedded driver is for embedded DBMS use, in
other words, the DBMS and application run on the same computer, the DBMS
does not have to be separately started outside the application.

Use the client-server driver to access a shared database from one or
more client applications on other computers. In this case the DBMS must
first be started on the "server" computer.

 

[RedGrittyBrick]

Most important thing is how can I use the derby-db from another computer in
network (question 3).

Use the "Derby network client" JDBC driver.

Is that what make the difference between EmbeddedDriver & ClientDriver,
Yes.


because I didn't find any documentation for that neighter.

I have the feeling you didn't look very far.

http://en.wikipedia.org/wiki/Apache_Derby#Derby_Technologies

http://db.apache.org/derby/papers/DerbyTut/embedded_intro.html
http://db.apache.org/derby/papers/DerbyTut/ns_intro.html#ns_intro

http://db.apache.org/derby/binaries/djd_derby_intro.pdf
(see p11 for embedded and p48 for client-server)

 

[Arne Vajhøj]

Most important thing is how can I use the derby-db from another computer in
network (question 3).

I gave you an example for driver and URL.

You will need to replace localhost with something else
in the URL, but ...

Is that what make the difference between EmbeddedDriver & ClientDriver,
because I didn't find any documentation for that neighter.

It works.

And it must be somewhere in the docs.

Arne

 

[fishjoin]

How to display and insert table's comment and column's comment

Hi,

Good Day !

After search all the document, still can not find the solution.

How to display and insert table's comment and column's comment to Derby database by using Derby SQL script ?

I try using NetBean 6.0 and 6.5, it only allow to using GUI to insert the column's comment/remark, not SQL script, do you know the syntax to insert or create table's comment and column's comment ?

For example, oracle syntax like below

COMMENT ON TABLE TABLE_NAME IS 'Working table...';

COMMENT ON COLUMN TABLE_NAME.COLUMN_NAME IS 'Primary key...';

But this syntax didn't work in Derby.

Any help ?


regards,

fishjoin