db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Forums @ Existanze" <for...@existanze.com>
Subject Migrating from Postgresql to Apache Derby
Date Thu, 06 Mar 2008 14:53:05 GMT
Hello all,

As the subject reads, my development team and I are trying to migrate to 
apache derby, because
we will start deploying our application with the database embedded, 
instead of having the hassle
of installing a database on each client. Following are some of my 
question regarding this migration,
that I haven't been able to find an answer to. I hope I don't tire you 
with this long email.

After reading the documentation and viewing various examples I have been 
able to successfully
migrate my "schema" to derby, I have been able to recreate all of our 
tables on a derby database.

Because of previous (now very apparent) incorrect decisions in relying 
on some Postgresql specific
functions, I have found myself at a dead end in completing this 
migration process.

In order to make up for some of the functionality I want to create 
several functions, stored procedures and triggers that will do the job.

Forgive me if I compare postgres and derby too much, but that is the way 
that I know how to explain it.

When I created a "BEFORE INSERT" trigger in postgres I was able to 
modify any of the fields of the entry that was about to be added. In 
derby I haven't found any way to do this.

Using the following example:

CREATE  TRIGGER set_proper_id
NO CASCADE BEFORE INSERT ON person
REFERENCING NEW AS new_person
FOR EACH ROW MODE DB2SQL
    new_person.person_code = --some generated code
   


I am under the assumption that by referencing the new entry I can 
modified it before the constraint checks,
at least that is what is said in the reference. Am I missing something 
here? Is what I am trying to do just not possible?

I understood most of the example which used AFTER UPDATE or AFTER 
INSERT, but in those situations my constraints come into play on the 
person table before anything gets inserted and everything breaks.

Is the trigger function supposed to return a value? In postgresql it was 
clearly defined that we should return the entry that was going to be 
inserted. It was very convenient that I didn't have to create the whole 
record just to change a single field and return the modified entry,  I 
hope that is not the case here because the person table has too many fields.

When I create a table, is it possible to specify the default value of a 
column to be the result of a user_function? 

For example

CREATE TABLE test(
    test_id integer not null generated always as identity(start with 1, 
increment by 1) primary key,
    test_entry_code VARCHAR(10) NOT NULL DEFAULT custom_function()
)


Well, that is it for now, I hope someone answers or at least sheds some 
light

Best Regards,
Fotis









Mime
View raw message