JavaDB: Check constraints

create table FREQUENCIES (
 NAME varchar(20) check (NAME in ('daily', 'weekly', 'monthly', 'yearly'))
);
insert into FREQUENCIES (NAME) values ('daily'), ('monthly');
select * from FREQUENCIES;

However something like

insert into FREQUENCIES (NAME) values ('hourly');

will lead to an error:

Error code -1, SQL state 23513: The check constraint 'SQL090305033046840' was violated while performing an INSERT or UPDATE on table 'ADMIN.FREQUENCIES'.
Line 1, column 1

See also "JavaDB 10.1.3: ALTER TABLE statement". Read more...

JavaDB: Default values for TIMESTAMP fields

create table LOG (
ID int primary key generated always as identity (start with 2147483647, increment by -1),
 -- the largest INTEGER: see 'Numeric Limitations' at http://developers.sun.com/docs/javadb/10.2.2/ref/rrefnumericlimits.html
T timestamp with default current_timestamp,
 -- see 'CREATE TABLE statement' at http://developers.sun.com/docs/javadb/10.2.2/ref/rrefsqlj24513.html
 -- and 'CURRENT TIMESTAMP' at http://developers.sun.com/docs/javadb/10.2.2/ref/rrefsqlj15866.html#rrefsqlj15866
MSG varchar(32672)
 -- maximal length of VARCHAR: see 'String Limitations' at http://developers.sun.com/docs/javadb/10.2.2/ref/rrefstringlimits.html
);

insert into LOG (MSG) values ('Rolling back the current changes...');
insert into LOG (MSG) values ('Starting a new transaction...');
insert into LOG (MSG) values ('Fetching new data...');
insert into LOG (MSG) values ('Done!');

select * from LOG order by T;
select ID, cast (T as CHAR(23)) as TIMESTAMP, MSG from LOG;

2147483647 2009-03-02 16:50:03.614 Rolling back the current changes...
2147483646 2009-03-02 16:50:03.692 Starting a new transaction...
2147483645 2009-03-02 16:50:03.708 Fetching new data...
2147483644 2009-03-02 16:50:03.723 Done!

See also:

• JavaDB 10.1.3: Data types
• Limitations of JavaDB

Read more...

JavaDB: Auto-generation of primary keys

create table PKTEST (
    ID int primary key generated always as identity,
    TXT varchar(12345)
);
insert into PKTEST (TXT) values ('aaa'), ('bbb'), ('ccc');
select * from PKTEST;
drop table PKTEST;

У опции "generated always as identity" есть два параметра - "start with" и "increment by".

create table PKTEST (
    ID int primary key generated always as identity (start with 100000, increment by 10),
    TXT varchar(12345)
);

Read more...

Blogger Help

• How can I create expandable post summaries?
• What is a post template?
• How can I get more storage for my images?

Read more...

Epson Stylus

• SX105: spec | review from TestFreaks
• SX400: review from TestSeek | review from TrustedReviews | review from MacUser

Read more...

XML SQL Utility (XSU)

XSU can

• generate XML documents from any SQL query,
• generate DTDs,
• perform simple transformations,
• insert XML into DB tables or views,
• update or delete records from a DB, given an XML document,
• and more...

Read more...

eXtensible Business Reporting Language

"XBRL is a standards-based way to communicate business and financial information. It uses the XML syntax and related XML technologies such as XML Schema, XLink, XPath, and Namespaces to articulate this semantic meaning." Read more...