...

View Full Version : Incorrect Coding Errors : Sql



shayanzez
10-15-2011, 05:18 PM
Hell i have 3 mysql tables that need some fixing. It will not show inside my phppgadmin sql table. It fails to create a table is what i am trying to say i guess. Here are my sql files:

ofOffline.sql


CREATE TABLE ofOffline(
messageId INTEGER PRIMARY KEY AUTOINCREMENT,
toUserId int not null,
fromUserName varchar(100),
creationDate date not null,
messageSize int not null,
message text not null,
isRead bool default false,
colorRGB int,
ip varchar(15) not null,
host varchar(50) not null
);

INSERT INTO ofOffline (
toUserId,
fromUserName,
creationDate,
messageSize,
message,
colorRGB,
ip,
host
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?);

masterLog.sql


CREATE TABLE masterlog(
id INTEGER PRIMARY KEY AUTOINCREMENT,
site_id int,
recordtime time not null,
recorddate date not null,
master varchar(200) not null,
info text
);

INSERT INTO masterlog(
site_id,
recordtime,
recorddate,
master,
info)
VALUES (?,?,?,?,?);

DELETE FROM masterlog WHERE recorddate < ?;


ofBan.sql


CREATE TABLE ofBan(
id INTEGER PRIMARY KEY AUTOINCREMENT,
toUserId int not null,
ip varchar(15) not null,
host varchar(50) not null
);

INSERT INTO ofBan (
toUserId,
ip,
host
)
VALUES(?, ?, ?);

Can i get some help fixing the coding? So it will import successfully? Thank you in advanced guys. My friend told me to come here for help because he thinks this site is useful so i figure i will give it a go :p

Regards,
Shayanzez.

BubikolRamios
10-15-2011, 05:33 PM
You should tell what error message is.

shayanzez
10-15-2011, 07:00 PM
Thank you so much for the quick reply kind sir. I am truly sorry for the incovience of my screw ups. Here are the paginate error results:

masterLog.sql


masterLog.sql:8: ERROR: syntax error at or near "AUTOINCREMENT"
LINE 3: id INTEGER PRIMARY KEY AUTOINCREMENT,
^
masterLog.sql:16: ERROR: syntax error at or near ","
LINE 13: VALUES (?,?,?,?,?);
^
masterLog.sql:18: ERROR: syntax error at or near ";"
LINE 1: DELETE FROM masterlog WHERE recorddate < ?;
^

Total runtime: 178.063 ms

SQL executed.

ofBan.sql


ofBan.sql:6: ERROR: syntax error at or near "AUTOINCREMENT"
LINE 3: id INTEGER PRIMARY KEY AUTOINCREMENT,
^
ofBan.sql:13: ERROR: syntax error at or near ","
LINE 11: VALUES(?, ?, ?);
^

Total runtime: 14.424 ms

SQL executed.

ofOffline.sql


ofOffline.sql:12: ERROR: syntax error at or near "AUTOINCREMENT"
LINE 3: messageId INTEGER PRIMARY KEY AUTOINCREMENT,
^
ofOffline.sql:24: ERROR: syntax error at or near ","
LINE 21: VALUES (?, ?, ?, ?, ?, ?, ?, ?);
^

Total runtime: 5.602 ms

SQL executed.

BubikolRamios
10-15-2011, 07:16 PM
are you writing this sqls out of head ?

Dont do it, for now.Gget your self a decent editor, like http://www.heidisql.com/

1. sample of correct syntax, you see that PRIMARY KEY gets on the end of syntax


CREATE TABLE galery_1_bug (
id_galery INT(10) UNSIGNED ZEROFILL NOT NULL COMMENT 'klju─Ť na galery_1',
bug_tree_child_id INT(10) UNSIGNED NOT NULL DEFAULT '0',
id_bug_tree_path VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (id_galery),
CONSTRAINT FK_galery_1_bug_1 FOREIGN KEY (id_galery) REFERENCES galery_1 (id_galery) ON UPDATE NO ACTION ON DELETE CASCADE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;


2. as for inserting VALUES (?,?,?,?,?); ... they are not part of sql, it should be filled
from PHP. Read google for prepared statements.
forinstance this: http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html, for java, but same thing for PHP.

I personay don't like that, ?,?,?....To much confusion.

shayanzez
10-15-2011, 07:34 PM
On ofOffline.sql it shows that i have an error on line 2 any way to fix that kind sir?:


SQL error:

ERROR: syntax error at or near "AUTOINCREMENT"
LINE 2: messageId INTEGER PRIMARY KEY AUTOINCREMENT,
^

In statement:

CREATE TABLE ofOffline(
messageId INTEGER PRIMARY KEY AUTOINCREMENT,
toUserId int not null,
fromUserName varchar(100),
creationDate date not null,
messageSize int not null,
message text not null,
isRead bool default false,
colorRGB int,
ip varchar(15) not null,
host varchar(50) not null
);

INSERT INTO ofOffline (
toUserId,
fromUserName,
creationDate,
messageSize,
message,
colorRGB,
ip,
host
)

BubikolRamios
10-15-2011, 09:02 PM
On ofOffline.sql it shows that i have an error on line 2 any way to fix that kind sir?:


SQL error:

ERROR: syntax error at or near "AUTOINCREMENT"
LINE 2: messageId INTEGER PRIMARY KEY AUTOINCREMENT,
^

In statement:

CREATE TABLE ofOffline(
messageId INTEGER PRIMARY KEY AUTOINCREMENT,
toUserId int not null,
fromUserName varchar(100),
creationDate date not null,
messageSize int not null,
message text not null,
isRead bool default false,
colorRGB int,
ip varchar(15) not null,
host varchar(50) not null
);

INSERT INTO ofOffline (
toUserId,
fromUserName,
creationDate,
messageSize,
message,
colorRGB,
ip,
host
)

as said above. primary key will not work there:




CREATE TABLE ofOffline(
messageId INTEGER AUTOINCREMENT,
toUserId int not null,
fromUserName varchar(100),
creationDate date not null,
messageSize int not null,
message text not null,
isRead bool default false,
colorRGB int,
ip varchar(15) not null,
host varchar(50) not null,
PRIMARY KEY (messageId)

);



not tested.

guelphdad
10-16-2011, 02:17 AM
Hell i have 3 mysql tables that need some fixing. It will not show inside my phppgadmin sql table

are you using mysql or postgres? phppgadmin is a front end interface for Postgres which is a different database application.

xxcorrosionxx
10-16-2011, 10:09 AM
The file can be used on postgresql, but it needs to be rewritten because we are at trial and error atm.

Regards,
xxcorrosionxx

xxcorrosionxx
10-16-2011, 10:15 AM
Also i noticed the values are VALUES (?, ?, ?, ?, ?, ?, ?, ?);? Which is incorrect because its use question marks but if you look at the offline.sql it has 8 settings the same number as the values:


INSERT INTO ofOffline (
toUserId,
fromUserName,
creationDate,
messageSize,
message,
colorRGB,
ip,
host
)

xxcorrosionxx
10-16-2011, 10:23 AM
Also i noticed the messageId INTEGER AUTOINCREMENT, is giving you error? What if you used this since messageid is primary?


messageId PRIMARY KEY AUTOINCREMENT,

xxcorrosionxx
10-16-2011, 10:49 AM
Sorry about that what if you do?:


messageId integer NOT NULL,

xxcorrosionxx
10-16-2011, 11:10 AM
I went ahead and edited to postgresql standards and it is pissing me off:


CREATE TABLE ofOffline(
messageId integer NOT NULL,
toUserId int not null,
fromUserName varchar(100),
creationDate date not null,
messageSize int not null,
message text not null,
isRead bool default false,
colorRGB int,
ip varchar(15) not null,
host varchar(50) not null,
PRIMARY KEY (messageId)

);

INSERT INTO ofOffline (
toUserId,
fromUserName,
creationDate,
messageSize,
message,
colorRGB,
ip,
host
)
VALUES ('', '', '', '', '', '', '', '');

All your left with now is figuring out what goes inside the values judging by the insert.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum