Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New to the CF scene
    Join Date
    Oct 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Exclamation Incorrect Coding Errors : Sql

    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

    PHP Code:
    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

    PHP Code:
    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

    PHP Code:
    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

    Regards,
    Shayanzez.

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    You should tell what error message is.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • Users who have thanked BubikolRamios for this post:

    shayanzez (10-15-2011)

  • #3
    New to the CF scene
    Join Date
    Oct 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

    Code:
    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

    Code:
    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

    Code:
    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.

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    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
    Code:
    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/tu.../prepared.html, for java, but same thing for PHP.

    I personay don't like that, ?,?,?....To much confusion.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #5
    New to the CF scene
    Join Date
    Oct 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Cool

    On ofOffline.sql it shows that i have an error on line 2 any way to fix that kind sir?:

    PHP Code:
    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
        


  • #6
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,994
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Quote Originally Posted by shayanzez View Post
    On ofOffline.sql it shows that i have an error on line 2 any way to fix that kind sir?:

    PHP Code:
    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:


    Code:
    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.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by shayanzez View Post
    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.

  • Users who have thanked guelphdad for this post:

    xxcorrosionxx (10-16-2011)

  • #8
    Regular Coder
    Join Date
    Jan 2011
    Posts
    117
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Cool

    The file can be used on postgresql, but it needs to be rewritten because we are at trial and error atm.

    Regards,
    xxcorrosionxx

  • #9
    Regular Coder
    Join Date
    Jan 2011
    Posts
    117
    Thanks
    27
    Thanked 0 Times in 0 Posts
    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:

    PHP Code:
    INSERT INTO ofOffline (
        
    toUserId,
        
    fromUserName,
        
    creationDate
        
    messageSize
        
    message,
        
    colorRGB,
        
    ip,
        
    host
        


  • #10
    Regular Coder
    Join Date
    Jan 2011
    Posts
    117
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Also i noticed the messageId INTEGER AUTOINCREMENT, is giving you error? What if you used this since messageid is primary?

    PHP Code:
    messageId PRIMARY KEY AUTOINCREMENT

  • #11
    Regular Coder
    Join Date
    Jan 2011
    Posts
    117
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Sorry about that what if you do?:

    PHP Code:
    messageId integer NOT NULL

  • #12
    Regular Coder
    Join Date
    Jan 2011
    Posts
    117
    Thanks
    27
    Thanked 0 Times in 0 Posts
    I went ahead and edited to postgresql standards and it is pissing me off:

    PHP Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •