Migrating, not an too SQL knowledgeable...

[Joachim Dagerot <li...@dagerot.nu>]

Hi all, my first post, I'm an experienced Derby user - have been
running it for almost two hours now! :-)

Anyway, I'm converting a very simple database from HSQLDB to Derby but
I'm running into a few 'accent' issues between the SQL scripts.

I am not very good on creating SQL DB's, I usually stay with my joins,
so if someone could help me getting these few table-create-lines to be
accepted by Derby I would be very grateful.

I have found that an undeclared VARCHAR is not accepted, so I will
replace those with VARCHAR(50), but there are other stuff, which I
don't even know what they are doing in HSQLDB, like this column phrase
for example: "BATCHTRADEVOLUMEBUY VARCHAR(255),AK_INTERNALBATCHID
INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)  NOT NULL
PRIMARY KEY)"

Please let me know if there's something I can do to solve this myself,
except for sitting trial/error and reference the manual, that would
simply take me ages.


Thanks in advance,
Joachim


CREATE TABLE DEAL(DEALID VARCHAR NOT NULL PRIMARY KEY,DEALDATE
TIMESTAMP,TRADER VARCHAR,AMOUNT FLOAT,PRODUCTID VARCHAR,ISDELTAPOS
BOOLEAN DEFAULT 'FALSE')
CREATE TABLE PRODUCT(PRODUCTID VARCHAR NOT NULL PRIMARY KEY,CONFIGURED
INTEGER,ENDDATE TIMESTAMP,STARTDATE TIMESTAMP,HOURS INTEGER,DELTAPOS
INTEGER)
CREATE TABLE XMLIO(DIRECTION VARCHAR(3),DATE TIMESTAMP,FILENAME
VARCHAR(255))
CREATE TABLE NP_BATCH(BATCHID VARCHAR(24) NOT NULL,BATCHDEALCOUNT
TIMESTAMP,BATCHDATE TIMESTAMP,BATCHMESSAGEID
VARCHAR(255),BATCHSOURCESYSTEM VARCHAR(255),BATCHTARGETSYSTEM
VARCHAR(255),BATCHTIME VARCHAR(24),BATCHTRADECOUNT
INTEGER,BATCHTRADECOUNTBUY INTEGER,BATCHTRADECOUNTSELL
INTEGER,BATCHTRADEVOLUMESELL VARCHAR(255),BATCHTRANSMISSION
INTEGER,MARKET INTEGER,BATCHTRADEVOLUMEBUY
VARCHAR(255),AK_INTERNALBATCHID INTEGER GENERATED BY DEFAULT AS
IDENTITY(START WITH 0)  NOT NULL PRIMARY KEY)
CREATE TABLE NP_DEAL(DEALID VARCHAR(255) NOT NULL,DEALAPPROVEDATE
VARCHAR(30),DEALAPPROVESTATUS VARCHAR(255),DEALAPPROVETIME
VARCHAR(255),DEALCOMBIPRODUCT INTEGER,DEALCONTRACTTICKER
VARCHAR(255),DEALDATE DATE,DEALEXTERNALID VARCHAR(255),DEALINTERNAL
INTEGER,DEALOTHER INTEGER,DEALPRICEAMOUNT VARCHAR(255),DEALREGDATE
VARCHAR(255),DEALREGTIME VARCHAR(255),DEALSOURCE
VARCHAR(255),DEALSTATUS VARCHAR(255),DEALTIME
VARCHAR(10),DEALTRADECOUNT INTEGER,DEALTRADECOUNTBUY
INTEGER,DEALTRADECOUNTSELL INTEGER,DEALTRADEVOLUMEBUY
VARCHAR(255),DEALTRADEVOLUMESELL VARCHAR(255),AK_INTERNALDEALID
INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)  NOT NULL
PRIMARY KEY,AK_INTERNALBATCHID INTEGER)
CREATE TABLE NP_TRADE(TRADEID INTEGER NOT NULL,TRADEAPPROVEDATE
DATE,TRADEAPPROVESTATUS VARCHAR(255),TRADEAPPROVETIME
VARCHAR(255),TRADEDISTRCOUNT INTEGER,TRADEDISTRVOLUME
VARCHAR(255),TRADEEXTERNALID VARCHAR(255),TRADEMEMBERREF
VARCHAR(255),TRADEVOLUME VARCHAR(255),DEALID
INTEGER,AK_INTERNALTRADEID INTEGER GENERATED BY DEFAULT AS
IDENTITY(START WITH 0)  NOT NULL PRIMARY KEY,AK_INTERNALDEALID
INTEGER,TRADERID VARCHAR(25))
CREATE TABLE NP_DISTRIBUTION(DISTRID VARCHAR(255) NOT
NULL,DISTRACCOUNTREF VARCHAR(255),DISTREXTERNALID
VARCHAR(255),DISTRMEMBERREF VARCHAR(255),DISTRPRICEAMOUNT
VARCHAR(255),DISTRVOLUME VARCHAR(255),AK_INTERNALDISTRIBUTIONID
INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)  NOT NULL
PRIMARY KEY,AK_INTERNALTRADEID INTEGER)
CREATE TABLE LOG(DEALID VARCHAR NOT NULL PRIMARY KEY,DATE
TIMESTAMP,ACTION VARCHAR(255))

Re: Migrating, not an too SQL knowledgeable...

[Edward Rayl <er...@bellsouth.net>]

Joachim,

>
>I have found that an undeclared VARCHAR is not accepted, so I will
>replace those with VARCHAR(50), but there are other stuff, which I
>don't even know what they are doing in HSQLDB, like this column phrase
>for example: "BATCHTRADEVOLUMEBUY VARCHAR(255),AK_INTERNALBATCHID
>INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)  NOT NULL
>PRIMARY KEY)"
>  
>

AK_INTERNALBATCHID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)

would become:

AK_INTERNALBATCHID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 0)



Ed