• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Why sql exception happens?

 
Ranch Hand
Posts: 159
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 80
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Make yourself as serene as a flower, as a tree. And on wednesdays, as serene as this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic