sjjones85
11-27-2010, 05:07 PM
Hello, I'm steve and I've just registered here (one of the only forums that actually allowed me activate :) )
Anyway, as part of a University project, I have to create a Database (PostgreSQL) driven website which also utilizes Java Servlets and SQL.
However, creating tables and inserting test content into the database is prooving tricky and I've just about done nothing but swear for the past two days...
I'm no SQL guru, but the SQL code I'm using is:
-- DROP TABLE comments;
-- DROP TABLE photos;
-- DROP TABLE churches;
CREATE TABLE churches (
church_id CHAR(4) UNIQUE NOT NULL,
name VARCHAR(40),
address VARCHAR(500),
minister VARCHAR(40),
denomination VARCHAR(40),
year_built INT,
history VARCHAR(1000),
PRIMARY KEY(church_id)
);
INSERT INTO churches VALUES ('SPM','St Peter Mancroft','Chantry Road, Norwich','Peter Nokes','Church of England',1455,'History');
INSERT INTO churches VALUES ('SANC','Norwich Citadel','Lower Goat Lane, Norwich','Ian McBride','Salvation Army',1892,'History');
INSERT INTO churches VALUES ('SS','St Stephens','Rampant Horse Street, Norwich','Hereward Cooke','Church of England',1350,'History');
INSERT INTO churches VALUES ('KCC','Kings Community Church','King Street, Norwich','Goff Hope','New Frontiers',1987,'History');
CREATE TABLE photos (
photo_id CHAR(5) UNIQUE NOT NULL,
church_id CHAR(4) REFERENCES churches(church_id),
photo_name VARCHAR(40),
photo_url VARCHAR(50),
);
INSERT INTO photos VALUES ('STM1','STM','St Peter Mancroft','StPeterMancroft1.JPG');
INSERT INTO photos VALUES ('SANC1','SANC1','Norwich Citadel','SalArmy-Norwich1.JPG');
INSERT INTO photos VALUES ('SS1','SS','St Stephens','StStephens1.JPG');
INSERT INTO photos VALUES ('KCC1','KCC','Kings Church','Kings1.JPG');
CREATE TABLE comments (
comment_id CHAR(4) REFERENCES churches(church_id),
comment_date DATE,
author VARCHAR(40),
comment_body VARCHAR(1000),
);
INSERT INTO comments VALUES ('SPM','26 Nov 2010','Steve Jones','Comment for St Peter Mancroft');
INSERT INTO comments VALUES ('SANC','26 Nov 2010','Olly Crocombe','Comment for Norwich Citadel');
INSERT INTO comments VALUES ('SS','26 Nov 2010','Alec Walker','Comment for St Stephens');
INSERT INTO comments VALUES ('KCC','26 Nov 2010','Kira Ovington','Comment for Kings Community Church');
It creates the Churches table and inserts all data with no problems at all, but then has a moan telling me that ERROR: syntax error at or near ")"... I have no idea whats going on, I can't see anything wrong with the code and I'm starting to panic that I'm going to fail this assignment...
The message I get from postgreSQL is:
postgres=# \i project_db.txt
psql:project_db.txt:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index "churches_pkey" for table "churches"
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
psql:project_db.txt:28: ERROR: syntax error at or near ")"
LINE 6: );
^
psql:project_db.txt:30: ERROR: relation "photos" does not exist
LINE 1: INSERT INTO photos VALUES ('STM1','STM','St Peter Mancroft',...
^
psql:project_db.txt:31: ERROR: relation "photos" does not exist
LINE 1: INSERT INTO photos VALUES ('SANC1','SANC1','Norwich Citadel'...
^
psql:project_db.txt:32: ERROR: relation "photos" does not exist
LINE 1: INSERT INTO photos VALUES ('SS1','SS','St Stephens','StSteph...
^
psql:project_db.txt:33: ERROR: relation "photos" does not exist
LINE 1: INSERT INTO photos VALUES ('KCC1','KCC','Kings Church','King...
^
psql:project_db.txt:41: ERROR: invalid byte sequence for encoding "UTF8": 0xa0
psql:project_db.txt:43: ERROR: relation "comments" does not exist
LINE 1: INSERT INTO comments VALUES ('SPM','26 Nov 2010','Person1...
^
psql:project_db.txt:44: ERROR: relation "comments" does not exist
LINE 1: INSERT INTO comments VALUES ('SANC','26 Nov 2010','Person2...
^
psql:project_db.txt:45: ERROR: relation "comments" does not exist
LINE 1: INSERT INTO comments VALUES ('SS','26 Nov 2010','Person3...
^
psql:project_db.txt:46: ERROR: relation "comments" does not exist
LINE 1: INSERT INTO comments VALUES ('KCC','26 Nov 2010','Person4...
I will greatly appreciate any help with this issue.
Steve :)
Anyway, as part of a University project, I have to create a Database (PostgreSQL) driven website which also utilizes Java Servlets and SQL.
However, creating tables and inserting test content into the database is prooving tricky and I've just about done nothing but swear for the past two days...
I'm no SQL guru, but the SQL code I'm using is:
-- DROP TABLE comments;
-- DROP TABLE photos;
-- DROP TABLE churches;
CREATE TABLE churches (
church_id CHAR(4) UNIQUE NOT NULL,
name VARCHAR(40),
address VARCHAR(500),
minister VARCHAR(40),
denomination VARCHAR(40),
year_built INT,
history VARCHAR(1000),
PRIMARY KEY(church_id)
);
INSERT INTO churches VALUES ('SPM','St Peter Mancroft','Chantry Road, Norwich','Peter Nokes','Church of England',1455,'History');
INSERT INTO churches VALUES ('SANC','Norwich Citadel','Lower Goat Lane, Norwich','Ian McBride','Salvation Army',1892,'History');
INSERT INTO churches VALUES ('SS','St Stephens','Rampant Horse Street, Norwich','Hereward Cooke','Church of England',1350,'History');
INSERT INTO churches VALUES ('KCC','Kings Community Church','King Street, Norwich','Goff Hope','New Frontiers',1987,'History');
CREATE TABLE photos (
photo_id CHAR(5) UNIQUE NOT NULL,
church_id CHAR(4) REFERENCES churches(church_id),
photo_name VARCHAR(40),
photo_url VARCHAR(50),
);
INSERT INTO photos VALUES ('STM1','STM','St Peter Mancroft','StPeterMancroft1.JPG');
INSERT INTO photos VALUES ('SANC1','SANC1','Norwich Citadel','SalArmy-Norwich1.JPG');
INSERT INTO photos VALUES ('SS1','SS','St Stephens','StStephens1.JPG');
INSERT INTO photos VALUES ('KCC1','KCC','Kings Church','Kings1.JPG');
CREATE TABLE comments (
comment_id CHAR(4) REFERENCES churches(church_id),
comment_date DATE,
author VARCHAR(40),
comment_body VARCHAR(1000),
);
INSERT INTO comments VALUES ('SPM','26 Nov 2010','Steve Jones','Comment for St Peter Mancroft');
INSERT INTO comments VALUES ('SANC','26 Nov 2010','Olly Crocombe','Comment for Norwich Citadel');
INSERT INTO comments VALUES ('SS','26 Nov 2010','Alec Walker','Comment for St Stephens');
INSERT INTO comments VALUES ('KCC','26 Nov 2010','Kira Ovington','Comment for Kings Community Church');
It creates the Churches table and inserts all data with no problems at all, but then has a moan telling me that ERROR: syntax error at or near ")"... I have no idea whats going on, I can't see anything wrong with the code and I'm starting to panic that I'm going to fail this assignment...
The message I get from postgreSQL is:
postgres=# \i project_db.txt
psql:project_db.txt:15: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index "churches_pkey" for table "churches"
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
psql:project_db.txt:28: ERROR: syntax error at or near ")"
LINE 6: );
^
psql:project_db.txt:30: ERROR: relation "photos" does not exist
LINE 1: INSERT INTO photos VALUES ('STM1','STM','St Peter Mancroft',...
^
psql:project_db.txt:31: ERROR: relation "photos" does not exist
LINE 1: INSERT INTO photos VALUES ('SANC1','SANC1','Norwich Citadel'...
^
psql:project_db.txt:32: ERROR: relation "photos" does not exist
LINE 1: INSERT INTO photos VALUES ('SS1','SS','St Stephens','StSteph...
^
psql:project_db.txt:33: ERROR: relation "photos" does not exist
LINE 1: INSERT INTO photos VALUES ('KCC1','KCC','Kings Church','King...
^
psql:project_db.txt:41: ERROR: invalid byte sequence for encoding "UTF8": 0xa0
psql:project_db.txt:43: ERROR: relation "comments" does not exist
LINE 1: INSERT INTO comments VALUES ('SPM','26 Nov 2010','Person1...
^
psql:project_db.txt:44: ERROR: relation "comments" does not exist
LINE 1: INSERT INTO comments VALUES ('SANC','26 Nov 2010','Person2...
^
psql:project_db.txt:45: ERROR: relation "comments" does not exist
LINE 1: INSERT INTO comments VALUES ('SS','26 Nov 2010','Person3...
^
psql:project_db.txt:46: ERROR: relation "comments" does not exist
LINE 1: INSERT INTO comments VALUES ('KCC','26 Nov 2010','Person4...
I will greatly appreciate any help with this issue.
Steve :)