*
The moose likes EJB and other Java EE Technologies and the fly likes Why sql exception happens? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Why sql exception happens?" Watch "Why sql exception happens?" New topic
Author

Why sql exception happens?

walter wang
Ranch Hand

Joined: Jun 02, 2001
Posts: 150
After I create data base
I excute sql to create table
but exception occurs
Database access resulted in SQL exception:
SQLState , Error code 20000, Message:
Syntax error: Encountered "<EOF>" at line 2, column 27.

What does it mean?
why it happens?
Below is sql file to be excuted.
-- Create Schema
CREATE SCHEMA TSHIRTS;
-- Create TABLES

-- CREATE TABLE CUSTOMER
CREATE TABLE TSHIRTS.CUSTOMER
(
CUSTOMER_ID INTEGER NOT NULL,
FIRST_NAME VARCHAR(30) NOT NULL,
LAST_NAME VARCHAR(30) NOT NULL,
MIDDLE_NAME VARCHAR(20) NOT NULL,
ADDRESS_1 VARCHAR(50) NOT NULL,
ADDRESS_2 VARCHAR(50) ,
CITY VARCHAR(50) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP_CODE VARCHAR(5),
PHONE VARCHAR(12),
EMAIL VARCHAR(50) NOT NULL UNIQUE,
PRIMARY KEY(CUSTOMER_ID)
);
-- create table STATE
CREATE TABLE TSHIRTS.STATE
(
CODE CHAR(2) NOT NULL,
STATE_NAME VARCHAR(50) NOT NULL
) ;
-- create table ORDERS
CREATE TABLE TSHIRTS.ORDERS(
ORDER_ID INTEGER NOT NULL,
CUSTOMER_ID_FK INTEGER NOT NULL,
ORDER_DATE DATE,
SHIP_INSTRUCT VARCHAR(100),
SHIP_DATE DATE,
SHIP_WEIGHT FLOAT,
SHIP_CHARGE FLOAT,
PAID_DATE DATE,
PRIMARY KEY(ORDER_ID),
FOREIGN KEY(CUSTOMER_ID_FK) REFERENCES TSHIRTS.CUSTOMER(CUSTOMER_ID)
);
-- create table ITEM
CREATE TABLE TSHIRTS.ITEM(
ITEM_ID INTEGER NOT NULL,
ORDER_ID_FK INTEGER NOT NULL,
QUANTITY INTEGER,
TOTAL_PRICE FLOAT,
PRIMARY KEY(ITEM_ID),
FOREIGN KEY(ORDER_ID_FK) REFERENCES TSHIRTS.ORDERS(ORDER_ID)
);
-- create table SUPPLIER
CREATE TABLE TSHIRTS.SUPPLIER(
SUPPLIER_ID INTEGER NOT NULL,
NAME VARCHAR(30) ,
ADDRESS_1 VARCHAR(30) ,
ADDRESS_2 VARCHAR(30) ,
CITY VARCHAR(40) ,
STATE VARCHAR(2) ,
ZIP VARCHAR(5) ,
PRIMARY KEY(SUPPLIER_ID)
);
--- CREATE TABLE TSHIRT
CREATE TABLE TSHIRTS.TSHIRT
(ITEM_ID_FK INTEGER,
SHIRT_ID INTEGER,
SHIRT_SIZE VARCHAR(5),
UNIT_PRICE FLOAT,
COLOR VARCHAR(10),
DESIGN_FRONT VARCHAR(10),
DESIGN_BACK VARCHAR(10),
PICTURE_FRONT VARCHAR(50), -- this type is not in Cloud Scape
PICTURE_BACK VARCHAR(50), -- this type is not in Cloud Scape
SUPPLIER_ID_FK INTEGER,
-- Unit price need to enter here.
FOREIGN KEY(ITEM_ID_FK) REFERENCES TSHIRTS.ITEM(ITEM_ID),
FOREIGN KEY(SUPPLIER_ID_FK) REFERENCES TSHIRTS.SUPPLIER(SUPPLIER_ID),
PRIMARY KEY(SHIRT_ID)
);
-- create type CARD_TYPE
CREATE TABLE TSHIRTS.CHARGE_CARD_TYPE (
CARD_TYPE VARCHAR(6) NOT NULL,
CARD_NAME VARCHAR(8) NOT NULL,
CARD_NUMBER VARCHAR(20) NOT NULL,
EXPIRE_DATE DATE NOT NULL ,
CARD_CONFIRMATION_NUMBER VARCHAR(20),
PRIMARY KEY(CARD_NUMBER)
);
--- CREATE TABLE CREDIT
CREATE TABLE TSHIRTS.CREDIT(
CUSTOMER_ID_FK INTEGER NOT NULL,
ORDER_ID_FK INTEGER NOT NULL,
CARD_NUMBER_FK VARCHAR(20) NOT NULL ,
FOREIGN KEY(CUSTOMER_ID_FK) REFERENCES TSHIRTS.CUSTOMER(CUSTOMER_ID),
FOREIGN KEY(ORDER_ID_FK) REFERENCES TSHIRTS.ORDERS(ORDER_ID),
FOREIGN KEY(CARD_NUMBER_FK) REFERENCES TSHIRTS.CHARGE_CARD_TYPE(CARD_NUMBER)
);
--- CREATE TABLE AUTHENTICATION
CREATE TABLE TSHIRTS.AUTHENTICATION(
CUSTOMER_ID_FK INTEGER NOT NULL,
PASSWORD VARCHAR(20) NOT NULL,
AUTH_TYPE VARCHAR(20) NOT NULL ,
FOREIGN KEY(CUSTOMER_ID_FK) REFERENCES TSHIRTS.CUSTOMER(CUSTOMER_ID)
);
--- Load data into CUSTOMER table
INSERT INTO TSHIRTS.CUSTOMER VALUES( '101','Rajiv','Pauli','','213 Erstwild Court','Apt 30','Sunnyvale','CA','94086','408-789-8075','rp@rpuli.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '102','Carole','Sandler','S','785 Geary St','','San Francisco','CA','94117','415-822-1289','cs@sandler.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '103','Philip','Currie','','654 Poplar','Apt 50','Wahsington','CA','94303','415-328-4543','pc@pCurrie.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '104','Tony','Higgins','H','East Shopping Cntr.','422 Bay Road','Altanta','CA','94026','415-368-1100','th@tHiggins.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '105', 'Rio','Vector','','1899 La Loma Drive','','Los Altos','CA','94022','415-776-3249','rv@rvictor.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '106','Erick','Watson','','1143 Carver Place','','Mountain View','CA','94063','415-389-8789','ew@ewaston.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '107','Charles','Ream','','41 Jordan Avenue','','Palo Alto','CA','94304','415-356-9876','cr@cReam.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '108','Donald','Duck','','587 Alvarado','','Redwood City','CA','94063','415-544-8729','dd@dDuck.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '109','Ray','Miller','','Mayfair Mart','7345 Ross Blvd.','San Diago','CA','94086','408-723-8789','rm@rmiller.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '110','Ray','Jaguar','F','520 Topaz Way','','Redwood City','CA','94062','415-743-3611','rj@rjaguar.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '111','Francis','Keyes','','3199 Sterling Court','','Sunnyvale','CA','94085','408-277-7245','fk@fKeyes.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '112','Margarate','Rosenthal','','234 Wyandotte Way','','Los Altos','CA','94022','415-887-7235','mr@mRosenthal.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '113','Betty','Beatty','','654 Oak Grove','','Menlo Park','CA','94025','415-356-9982','bb@bbeatty.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '114','Frank','Albertson','','947 Waverly Place','','Cleveland','CA','94062','415-886-6677','fa@Albertson.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '115','Alfred','Greenspan','G','776 Gary Avenue','','Denver','CA','94025','415-356-1123','ag@agreenspan.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '116','Ariana','Parmelee','','1104 Spinosa Drive','','Mountain View','CA','94040','415-534-8822','ap@Parmelee.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '117','Arnold','Ken','','850 Lytton Court','','Dullas','CA','94063','415-245-4578','ak@aKen.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '118','Ridhard','Baxter','','5427 College','','Oakland','CA','94609','415-655-0011','rb@rBaxter.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '119','Bob','Shorter','','2405 Kings Highway','','Cherry Hill','NJ','08002','609-663-6079','rw@rShorter.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '120','Robert','Willcock','','6627 N. 17th Way','','Milwakee','AZ','85016','602-265-8754','rw@rWillcock.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '121','Jay','Wallack','','Lake Biltmore Mall','','New York','DE','19898','302-366-7511','jw@jwallack.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '122','Catherine','Jones','','543 Nassau Street','','Princeton','NJ','08540','609-342-0054','cb@cbrian.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '123','Mario','Hanion','','10100 Bay Meadows Ro','Suite 120','Jacksonville','FL','32256','904-823-4239','mh@mHanion.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '124','Chris','Patania','D','4715 S.E. Adams Blvd','Apt T1','Bartlesville','OK','74006','918-355-2074','cp@cpatania.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '125','Johney','Henry','','1450 Commonwealth Av','','Brighton','VA','02135','617-232-4159','jh@jHenry.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '126','Eleen','Neelie','','2539 South Utica Str','','Denver','CO','80219','303-936-7731','en@eNeelie.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '127','Kid','Satifer','','Blue Island Square','','Hampton','VA','60406','312-944-5691','ks@kSatifer.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '128','Frank','Little','','Athletic Department','','Baltimore','AZ','85008','602-533-1817','fk@fLittle.com');
--- Load data into ORDERS table
INSERT INTO TSHIRTS.ORDERS


public class Walter { public boolean is_Working_Now (boolean is_boss_Coming) { return is_boss_Coming; }
Angela Margot
Ranch Hand

Joined: Feb 07, 2002
Posts: 80
I've done quite a bit of databasing and haven't seen the use of semi-colons in the SQL. Maybe you should elminate those?


Angela Margot <img src="smile.gif" border="0"> <br />Sr. Software Engineer<br />SCJP2 SCWCD MCSD MCP
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Why sql exception happens?
 
Similar Threads
DB2 : about the CASCADE update/delete
Hypersonic DB question
Mapping same POJO to two tables in Hibernate XML mapping files
Hibernate is driving me crazy !
Restore problem