Re: Use variables in SQL script?

2011-06-27 Thread Thomas
David,

I am afraid Derby doesn't implement variables - I had been looking for this
myself also some time back when writing Derby scripts for test data
creation/manipulation. I finally ended up writing small Java programs where
needed instead of or on top of just scripts. Since a couple of days I am using
Squirel as front-end rather than ij and this tool has a script plug-in which
sounds interesting, but I have not really looked into this plug-in yet whether
it would provide any help when it comes to variables. When I asked basically the
same question you just did to the derby community there was a mention that it
should not be too hard to enhance ij to offer this support - however I have not
raised a feature request and to the best of my knowledge this didn't go any
further than the communication on the user mailing list itself. I would be
willing to help document/test such feature, but can't program myself as I am not
a programmer.





Error code 42X05. Table doesn't exist.Pls help with code.

2011-06-27 Thread IkeAbalogu

CREATE TRIGGER NEWBALANCE
AFTER INSERT ON APP.PRODUCTQUANTMONITOR
REFERENCING NEW AS NEWROW
FOR EACH ROW
UPDATE NEWROW SET PREVIOUS_BALANCE =
CASE
WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1
THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID =
NEWROW.FK1_SUPPLYID)
ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE
FK1_SUPPLYID = NEWROW.FK1_SUPPLYID 
AND TANKNUMBER = NEWROW.TANKNUMBER)
END
;
-- 
View this message in context: 
http://old.nabble.com/Error-code-42X05.-Table-doesn%27t-exist.Pls-help-with-code.-tp31936869p31936869.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.



Re: Use variables in SQL script?

2011-06-27 Thread Rick Hillegas
Thanks for raising this topic, David and Thomas. Feel free to vote for 
this new enhancement request: 
https://issues.apache.org/jira/browse/DERBY-5298 I have worked around 
this problem by using the helper functions attached to 
https://issues.apache.org/jira/browse/DERBY-5242


Thanks,
-Rick

On 6/27/11 2:53 AM, Thomas wrote:

David,

I am afraid Derby doesn't implement variables - I had been looking for this
myself also some time back when writing Derby scripts for test data
creation/manipulation. I finally ended up writing small Java programs where
needed instead of or on top of just scripts. Since a couple of days I am using
Squirel as front-end rather than ij and this tool has a script plug-in which
sounds interesting, but I have not really looked into this plug-in yet whether
it would provide any help when it comes to variables. When I asked basically the
same question you just did to the derby community there was a mention that it
should not be too hard to enhance ij to offer this support - however I have not
raised a feature request and to the best of my knowledge this didn't go any
further than the communication on the user mailing list itself. I would be
willing to help document/test such feature, but can't program myself as I am not
a programmer.








Re: Error code 42X05. Table doesn't exist.Pls help with code.

2011-06-27 Thread Rick Hillegas
Can you share your schema and the error message you are seeing? That 
will help people advise you.


Thanks,
-Rick

On 6/27/11 5:20 AM, IkeAbalogu wrote:

CREATE TRIGGER NEWBALANCE
AFTER INSERT ON APP.PRODUCTQUANTMONITOR
REFERENCING NEW AS NEWROW
FOR EACH ROW
UPDATE NEWROW SET PREVIOUS_BALANCE =
CASE
WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1
THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID =
NEWROW.FK1_SUPPLYID)
ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE
FK1_SUPPLYID = NEWROW.FK1_SUPPLYID
AND TANKNUMBER = NEWROW.TANKNUMBER)
END
;




Re: Error code 42X05. Table doesn't exist.Pls help with code.

2011-06-27 Thread Rick Hillegas

When I run these commands, I get an error on this one:

ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product
FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE
RESTRICT;
ERROR X0Y46: Constraint 'FK1_PRODUCTSUPPLY_TO_PRODUCT' is invalid: 
referenced table PRODUCT does not exist.


That seems reasonable because I don't see a table called PRODUCT in this 
schema. Can you share the rest of your schema with us?


Thanks,
-Rick

On 6/27/11 6:22 AM, IkeAbalogu wrote:

Thanks for your response.I get Error code 42X05. Table/view doesn't exist
-- Create a Database table to represent the productsupply entity.
CREATE TABLE productsupply(
supplyidINTEGER NOT NULL,
supply_date DATE NOT NULL,
isfinished  SMALLINT NOT NULL,
finish_date DATE NOT NULL,
quantityBIGINT NOT NULL,
costprice   DOUBLE PRECISION NOT NULL,
saleprice   DOUBLE PRECISION NOT NULL,
fk1_nameVARCHAR(30) NOT NULL,
-- Specify the PRIMARY KEY constraint for table productsupply.
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINT  pk_productsupply PRIMARY KEY (supplyid)
);

-- Create a Database table to represent the productquantmonitor entity.
CREATE TABLE productquantmonitor(
checkdate   DATE NOT NULL,
tanknumber  INTEGER NOT NULL,
balance BIGINT NOT NULL,
previous_balanceBIGINT NOT NULL,
quantitysoldBIGINT NOT NULL,
fk1_supplyidINTEGER NOT NULL
);

-- Create a Database table to represent the producttank entity.
CREATE TABLE producttank(
tankid  INTEGER NOT NULL,
tankcapacityBIGINT NOT NULL,
fk1_nameVARCHAR(30) NOT NULL,
-- Specify the PRIMARY KEY constraint for table producttank.
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINT  pk_producttank PRIMARY KEY (tankid)
);
-- This constraint ensures that the foreign key of table productsupply
-- correctly references the primary key of table product

ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product
FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE
RESTRICT;

-- Alter table to add new constraints required to implement the
productquantmonitor_productsupply relationship

-- This constraint ensures that the foreign key of table
productquantmonitor
-- correctly references the primary key of table productsupply

ALTER TABLE productquantmonitor ADD CONSTRAINT
fk1_productquantmonitor_to_productsupply FOREIGN KEY(fk1_supplyid)
REFERENCES productsupply(supplyid) ON DELETE RESTRICT ON UPDATE RESTRICT;


Rick Hillegas-3 wrote:

Can you share your schema and the error message you are seeing? That
will help people advise you.

Thanks,
-Rick

On 6/27/11 5:20 AM, IkeAbalogu wrote:

CREATE TRIGGER NEWBALANCE
AFTER INSERT ON APP.PRODUCTQUANTMONITOR
REFERENCING NEW AS NEWROW
FOR EACH ROW
UPDATE NEWROW SET PREVIOUS_BALANCE =
CASE
WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) =
1
THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID =
NEWROW.FK1_SUPPLYID)
ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE
FK1_SUPPLYID = NEWROW.FK1_SUPPLYID
AND TANKNUMBER = NEWROW.TANKNUMBER)
END
;







Does anyone want to run Derby 10.9 on JVM 1.4 or on CDC/FP 1.1?

2011-06-27 Thread Rick Hillegas
The 1.4 JVM has not been supported as a free platform for some time 
(although I believe you can buy a support contract for 1.4 if you need 
to). Does anyone plan to run Derby 10.9 on this platform? Does anyone 
plan to run Derby 10.9 on the related small device CDC/FP 1.1 platform? 
For the next feature release, we are considering dropping support for 
one or both of these platforms. Your responses will help us make a decision.


Thanks,
-Rick


Re: Error code 42X05. Table doesn't exist.Pls help with code.

2011-06-27 Thread Rick Hillegas
After running the database creation script, I see the reported 42X05 
error when creating the problem trigger:


CREATE TRIGGER NEWBALANCE
AFTER INSERT ON APP.PRODUCTQUANTMONITOR
REFERENCING NEW AS NEWROW
FOR EACH ROW
UPDATE NEWROW SET PREVIOUS_BALANCE =
CASE
WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1
THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID =
NEWROW.FK1_SUPPLYID)
ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE
FK1_SUPPLYID = NEWROW.FK1_SUPPLYID
AND TANKNUMBER = NEWROW.TANKNUMBER)
END
;
ERROR 42X05: Table/View 'NEWROW' does not exist.

The error occurs because the triggered statement is trying to update the 
transient transition variable NEWROW rather than an actual table. The 
following rewritten trigger definition does compile and run cleanly. I 
don't know if this is what you are trying to achieve, though:


CREATE TRIGGER NEWBALANCE
AFTER INSERT ON APP.PRODUCTQUANTMONITOR
REFERENCING NEW AS NEWROW
FOR EACH ROW
UPDATE APP.PRODUCTQUANTMONITOR SET PREVIOUS_BALANCE =
CASE
WHEN (SELECT COUNT (FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1
THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID =
NEWROW.FK1_SUPPLYID)
ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE
FK1_SUPPLYID = NEWROW.FK1_SUPPLYID
AND TANKNUMBER = NEWROW.TANKNUMBER)
END
;

Hope this helps,
-Rick

On 6/27/11 6:55 AM, IkeAbalogu wrote:

Thanks.For your interest and help.Below is the entire schema


--
-- Database creation Script
--
-- Create a Database table to represent the product entity.
CREATE TABLE product(
nameVARCHAR(30) NOT NULL,
description LONG VARCHAR NOT NULL,
source  LONG VARCHAR NOT NULL,
-- Specify the PRIMARY KEY constraint for table product.
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINT  pk_product PRIMARY KEY (name)
);

-- Create a Database table to represent the productsupply entity.
CREATE TABLE productsupply(
supplyidINTEGER NOT NULL,
supply_date DATE NOT NULL,
isfinished  SMALLINT NOT NULL,
finish_date DATE NOT NULL,
quantityBIGINT NOT NULL,
costprice   DOUBLE PRECISION NOT NULL,
saleprice   DOUBLE PRECISION NOT NULL,
fk1_nameVARCHAR(30) NOT NULL,
-- Specify the PRIMARY KEY constraint for table productsupply.
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINT  pk_productsupply PRIMARY KEY (supplyid)
);

-- Create a Database table to represent the productquantmonitor entity.
CREATE TABLE productquantmonitor(
checkdate   DATE NOT NULL,
tanknumber  INTEGER NOT NULL,
balance BIGINT NOT NULL,
previous_balanceBIGINT NOT NULL,
quantitysoldBIGINT NOT NULL,
fk1_supplyidINTEGER NOT NULL
);

-- Create a Database table to represent the producttank entity.
CREATE TABLE producttank(
tankid  INTEGER NOT NULL,
tankcapacityBIGINT NOT NULL,
fk1_nameVARCHAR(30) NOT NULL,
-- Specify the PRIMARY KEY constraint for table producttank.
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINT  pk_producttank PRIMARY KEY (tankid)
);


--
-- Alter Tables to add fk constraints --

-- Now all the tables have been created the ALTER TABLE command is used to
define some additional
-- constraints.  These typically constrain values of foreign keys to be
associated in some way
-- with the primary keys of related tables.  Foreign key constraints can
actually be specified
-- when each table is created, but doing so can lead to dependency problems
within the script
-- i.e. tables may be referenced before they have been created.  This method
is therefore safer.

-- Alter table to add new constraints required to implement the
producttank_product relationship

-- This constraint ensures that the foreign key of table producttank
-- correctly references the primary key of table product

ALTER TABLE producttank ADD CONSTRAINT fk1_producttank_to_product FOREIGN
KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE
RESTRICT;

-- Alter table to add new constraints required to implement the
productsupply_product relationship

-- This constraint ensures that the foreign key of table productsupply
-- correctly references the primary key of table product

ALTER TABLE productsupply ADD CONSTRAINT fk1_productsupply_to_product
FOREIGN KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE
RESTRICT;

-- Alter table to add new constraints required to implement the
productquantmonitor_productsupply relationship

-- This constraint ensures that the foreign key 

Re: Does anyone want to run Derby 10.9 on JVM 1.4 or on CDC/FP 1.1?

2011-06-27 Thread Peter Ondruška
Please drop 1.4
Dne 27.6.2011 16:05 Rick Hillegas rick.hille...@oracle.com napsal(a):
 The 1.4 JVM has not been supported as a free platform for some time
 (although I believe you can buy a support contract for 1.4 if you need
 to). Does anyone plan to run Derby 10.9 on this platform? Does anyone
 plan to run Derby 10.9 on the related small device CDC/FP 1.1 platform?
 For the next feature release, we are considering dropping support for
 one or both of these platforms. Your responses will help us make a
decision.

 Thanks,
 -Rick


Re: Error code 42X05. Table doesn't exist.Pls help with code.

2011-06-27 Thread IkeAbalogu

Thanks.I see the error.I am updating newrow because it is the newly inserted
row that the trigger will update its previousbalance column.I will try to
come with a where statement to get to the newly inserted row.Your help will
still be appreciated.
Thanks again.



Rick Hillegas-3 wrote:
 
 After running the database creation script, I see the reported 42X05 
 error when creating the problem trigger:
 
 CREATE TRIGGER NEWBALANCE
 AFTER INSERT ON APP.PRODUCTQUANTMONITOR
 REFERENCING NEW AS NEWROW
 FOR EACH ROW
 UPDATE NEWROW SET PREVIOUS_BALANCE =
 CASE
 WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1
 THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID =
 NEWROW.FK1_SUPPLYID)
 ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE
 FK1_SUPPLYID = NEWROW.FK1_SUPPLYID
 AND TANKNUMBER = NEWROW.TANKNUMBER)
 END
 ;
 ERROR 42X05: Table/View 'NEWROW' does not exist.
 
 The error occurs because the triggered statement is trying to update the 
 transient transition variable NEWROW rather than an actual table. The 
 following rewritten trigger definition does compile and run cleanly. I 
 don't know if this is what you are trying to achieve, though:
 
 CREATE TRIGGER NEWBALANCE
 AFTER INSERT ON APP.PRODUCTQUANTMONITOR
 REFERENCING NEW AS NEWROW
 FOR EACH ROW
 UPDATE APP.PRODUCTQUANTMONITOR SET PREVIOUS_BALANCE =
 CASE
 WHEN (SELECT COUNT (FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1
 THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID =
 NEWROW.FK1_SUPPLYID)
 ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE
 FK1_SUPPLYID = NEWROW.FK1_SUPPLYID
 AND TANKNUMBER = NEWROW.TANKNUMBER)
 END
 ;
 
 Hope this helps,
 -Rick
 
 On 6/27/11 6:55 AM, IkeAbalogu wrote:
 Thanks.For your interest and help.Below is the entire schema


 --
 -- Database creation Script
 --
 -- Create a Database table to represent the product entity.
 CREATE TABLE product(
  nameVARCHAR(30) NOT NULL,
  description LONG VARCHAR NOT NULL,
  source  LONG VARCHAR NOT NULL,
  -- Specify the PRIMARY KEY constraint for table product.
  -- This indicates which attribute(s) uniquely identify each row of data.
  CONSTRAINT  pk_product PRIMARY KEY (name)
 );

 -- Create a Database table to represent the productsupply entity.
 CREATE TABLE productsupply(
  supplyidINTEGER NOT NULL,
  supply_date DATE NOT NULL,
  isfinished  SMALLINT NOT NULL,
  finish_date DATE NOT NULL,
  quantityBIGINT NOT NULL,
  costprice   DOUBLE PRECISION NOT NULL,
  saleprice   DOUBLE PRECISION NOT NULL,
  fk1_nameVARCHAR(30) NOT NULL,
  -- Specify the PRIMARY KEY constraint for table productsupply.
  -- This indicates which attribute(s) uniquely identify each row of data.
  CONSTRAINT  pk_productsupply PRIMARY KEY (supplyid)
 );

 -- Create a Database table to represent the productquantmonitor entity.
 CREATE TABLE productquantmonitor(
  checkdate   DATE NOT NULL,
  tanknumber  INTEGER NOT NULL,
  balance BIGINT NOT NULL,
  previous_balanceBIGINT NOT NULL,
  quantitysoldBIGINT NOT NULL,
  fk1_supplyidINTEGER NOT NULL
 );

 -- Create a Database table to represent the producttank entity.
 CREATE TABLE producttank(
  tankid  INTEGER NOT NULL,
  tankcapacityBIGINT NOT NULL,
  fk1_nameVARCHAR(30) NOT NULL,
  -- Specify the PRIMARY KEY constraint for table producttank.
  -- This indicates which attribute(s) uniquely identify each row of data.
  CONSTRAINT  pk_producttank PRIMARY KEY (tankid)
 );


 --
 -- Alter Tables to add fk constraints --

 -- Now all the tables have been created the ALTER TABLE command is used
 to
 define some additional
 -- constraints.  These typically constrain values of foreign keys to be
 associated in some way
 -- with the primary keys of related tables.  Foreign key constraints can
 actually be specified
 -- when each table is created, but doing so can lead to dependency
 problems
 within the script
 -- i.e. tables may be referenced before they have been created.  This
 method
 is therefore safer.

 -- Alter table to add new constraints required to implement the
 producttank_product relationship

 -- This constraint ensures that the foreign key of table producttank
 -- correctly references the primary key of table product

 ALTER TABLE producttank ADD CONSTRAINT fk1_producttank_to_product FOREIGN
 KEY(fk1_name) REFERENCES product(name) ON DELETE RESTRICT ON UPDATE
 RESTRICT;

 -- Alter table to add new constraints required to implement the
 productsupply_product relationship

 -- This constraint ensures that the foreign key of table productsupply
 -- correctly references the primary key of table product

 

Re: Does anyone want to run Derby 10.9 on JVM 1.4 or on CDC/FP 1.1?

2011-06-27 Thread Marco Ferretti
+1 for dropping



On 27/giu/2011, at 17:01, Peter Ondruška peter.ondru...@kaibo.eu wrote:

 Please drop 1.4
 
 Dne 27.6.2011 16:05 Rick Hillegas rick.hille...@oracle.com napsal(a):
  The 1.4 JVM has not been supported as a free platform for some time 
  (although I believe you can buy a support contract for 1.4 if you need 
  to). Does anyone plan to run Derby 10.9 on this platform? Does anyone 
  plan to run Derby 10.9 on the related small device CDC/FP 1.1 platform? 
  For the next feature release, we are considering dropping support for 
  one or both of these platforms. Your responses will help us make a decision.
  
  Thanks,
  -Rick


Re: Error code 42X05. Table doesn't exist.Pls help with code.

2011-06-27 Thread Rick Hillegas
Instead of declaring a trigger which fires an update statement, you 
might want to consider declaring a trigger which fires a database 
procedure--the procedure would then update the row which you just 
inserted. The resulting code might be easier to reason about.


Hope this helps,
-Rick

On 6/27/11 8:16 AM, IkeAbalogu wrote:

Thanks.I see the error.I am updating newrow because it is the newly inserted
row that the trigger will update its previousbalance column.I will try to
come with a where statement to get to the newly inserted row.Your help will
still be appreciated.
Thanks again.



Rick Hillegas-3 wrote:

After running the database creation script, I see the reported 42X05
error when creating the problem trigger:

CREATE TRIGGER NEWBALANCE
AFTER INSERT ON APP.PRODUCTQUANTMONITOR
REFERENCING NEW AS NEWROW
FOR EACH ROW
UPDATE NEWROW SET PREVIOUS_BALANCE =
CASE
WHEN (SELECT COUNT (NEWROW.FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1
THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID =
NEWROW.FK1_SUPPLYID)
ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE
FK1_SUPPLYID = NEWROW.FK1_SUPPLYID
AND TANKNUMBER = NEWROW.TANKNUMBER)
END
;
ERROR 42X05: Table/View 'NEWROW' does not exist.

The error occurs because the triggered statement is trying to update the
transient transition variable NEWROW rather than an actual table. The
following rewritten trigger definition does compile and run cleanly. I
don't know if this is what you are trying to achieve, though:

CREATE TRIGGER NEWBALANCE
AFTER INSERT ON APP.PRODUCTQUANTMONITOR
REFERENCING NEW AS NEWROW
FOR EACH ROW
UPDATE APP.PRODUCTQUANTMONITOR SET PREVIOUS_BALANCE =
CASE
WHEN (SELECT COUNT (FK1_SUPPLYID) FROM APP.PRODUCTQUANTMONITOR) = 1
THEN (SELECT QUANTITY FROM APP.PRODUCTSUPPLY WHERE SUPPLYID =
NEWROW.FK1_SUPPLYID)
ELSE (SELECT MIN(DISTINCT BALANCE) FROM PRODUCTQUANTMONITOR WHERE
FK1_SUPPLYID = NEWROW.FK1_SUPPLYID
AND TANKNUMBER = NEWROW.TANKNUMBER)
END
;

Hope this helps,
-Rick

On 6/27/11 6:55 AM, IkeAbalogu wrote:

Thanks.For your interest and help.Below is the entire schema


--
-- Database creation Script
--
-- Create a Database table to represent the product entity.
CREATE TABLE product(
nameVARCHAR(30) NOT NULL,
description LONG VARCHAR NOT NULL,
source  LONG VARCHAR NOT NULL,
-- Specify the PRIMARY KEY constraint for table product.
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINT  pk_product PRIMARY KEY (name)
);

-- Create a Database table to represent the productsupply entity.
CREATE TABLE productsupply(
supplyidINTEGER NOT NULL,
supply_date DATE NOT NULL,
isfinished  SMALLINT NOT NULL,
finish_date DATE NOT NULL,
quantityBIGINT NOT NULL,
costprice   DOUBLE PRECISION NOT NULL,
saleprice   DOUBLE PRECISION NOT NULL,
fk1_nameVARCHAR(30) NOT NULL,
-- Specify the PRIMARY KEY constraint for table productsupply.
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINT  pk_productsupply PRIMARY KEY (supplyid)
);

-- Create a Database table to represent the productquantmonitor entity.
CREATE TABLE productquantmonitor(
checkdate   DATE NOT NULL,
tanknumber  INTEGER NOT NULL,
balance BIGINT NOT NULL,
previous_balanceBIGINT NOT NULL,
quantitysoldBIGINT NOT NULL,
fk1_supplyidINTEGER NOT NULL
);

-- Create a Database table to represent the producttank entity.
CREATE TABLE producttank(
tankid  INTEGER NOT NULL,
tankcapacityBIGINT NOT NULL,
fk1_nameVARCHAR(30) NOT NULL,
-- Specify the PRIMARY KEY constraint for table producttank.
-- This indicates which attribute(s) uniquely identify each row of data.
CONSTRAINT  pk_producttank PRIMARY KEY (tankid)
);


--
-- Alter Tables to add fk constraints --

-- Now all the tables have been created the ALTER TABLE command is used
to
define some additional
-- constraints.  These typically constrain values of foreign keys to be
associated in some way
-- with the primary keys of related tables.  Foreign key constraints can
actually be specified
-- when each table is created, but doing so can lead to dependency
problems
within the script
-- i.e. tables may be referenced before they have been created.  This
method
is therefore safer.

-- Alter table to add new constraints required to implement the
producttank_product relationship

-- This constraint ensures that the foreign key of table producttank
-- correctly references the primary key of table product

ALTER TABLE producttank ADD CONSTRAINT fk1_producttank_to_product FOREIGN

Re: Error code 42X05. Table doesn't exist.Pls help with code.

2011-06-27 Thread Knut Anders Hatlen
IkeAbalogu ikeabal...@hotmail.com writes:

 Thanks.I see the error.I am updating newrow because it is the newly inserted
 row that the trigger will update its previousbalance column.I will try to
 come with a where statement to get to the newly inserted row.Your help will
 still be appreciated.

Perhaps you could add a primary key column to the PRODUCTQUANTMONITOR
table and use that column in a WHERE clause in the UPDATE statement? The
primary key could be automatically generated by Derby using an identity
column:

  CREATE TABLE PRODUCTQUANTMONITOR (
ID INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
...)

-- 
Knut Anders


Re: Does anyone want to run Derby 10.9 on JVM 1.4 or on CDC/FP 1.1?

2011-06-27 Thread Brandon L. Duncan
Drop it.

On Mon, Jun 27, 2011 at 11:26 AM, Marco Ferretti
marco.ferre...@gmail.comwrote:

 +1 for dropping



 On 27/giu/2011, at 17:01, Peter Ondruška peter.ondru...@kaibo.eu wrote:

 Please drop 1.4
 Dne 27.6.2011 16:05 Rick Hillegas  rick.hille...@oracle.com
 rick.hille...@oracle.com napsal(a):
  The 1.4 JVM has not been supported as a free platform for some time
  (although I believe you can buy a support contract for 1.4 if you need
  to). Does anyone plan to run Derby 10.9 on this platform? Does anyone
  plan to run Derby 10.9 on the related small device CDC/FP 1.1 platform?
  For the next feature release, we are considering dropping support for
  one or both of these platforms. Your responses will help us make a
 decision.
 
  Thanks,
  -Rick




Re: Does anyone want to run Derby 10.9 on JVM 1.4 or on CDC/FP 1.1?

2011-06-27 Thread Mike Matrigali

I think it would be ok to drop testing/support for jdk1.4.  But I think
it continues to be valuable to support running derby on small 
devices/set top devices

through support of the CDC/FP 1.1 platform.

Derby  has a architecture which allows developers to add optional
features that depend on newer jdk features, which has been used in
the past and continues to be supported.


Rick Hillegas wrote:
The 1.4 JVM has not been supported as a free platform for some time 
(although I believe you can buy a support contract for 1.4 if you need 
to). Does anyone plan to run Derby 10.9 on this platform? Does anyone 
plan to run Derby 10.9 on the related small device CDC/FP 1.1 platform? 
For the next feature release, we are considering dropping support for 
one or both of these platforms. Your responses will help us make a 
decision.


Thanks,
-Rick