Topic: whats wrong with this little query?
I am trying to run this little query on my sqlite database and ik keeps throwing a syntax error near ALTER
ALTER TABLE posts ADD COLUMN srcfile int;
Whats wrong with this?
Re: whats wrong with this little query?
Try integer, not int
3 2007-03-29 20:09 (edited by pedrotuga 2007-03-29 20:09)
Re: whats wrong with this little query?
same error
really strange...
could it be permition issues?
the file has permitions -rw-rw-r--
- elbekko
- Senior Member
- Offline
Re: whats wrong with this little query?
Don't you need to specify field size?
Re: whats wrong with this little query?
i think thats only for text fields
Re: whats wrong with this little query?
Check what version of SQLite you are using. ALTER TABLE ADD COLUMN did not come until SQLite 3.2.
Re: whats wrong with this little query?
i am using sqlite2 in fact.
That basically means i cant add a column to a table. Or am i missing something?
Re: whats wrong with this little query?
No, SQLite 2 has ALTER TABLE ADD COLUMN support. I have no idea what the problem is. Here's an example of what PunBB runs in the update script:
'ALTER TABLE '.$db->prefix.'users ADD dst INTEGER NOT NULL DEFAULT 0'
"Programming is like sex: one mistake and you have to support it for the rest of your life."
9 2007-03-30 18:11 (edited by dmspilot00 2007-03-30 18:14)
Re: whats wrong with this little query?
Rickard wrote:
No, SQLite 2 has ALTER TABLE ADD COLUMN support.
That's incorrect. Alter table add column was not added until SQLite 3.2.0. See http://www.sqlite.org/changes.html.
Just to make sure I tried it out myself.
C:\>sqlite3
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table sometable (col1 int, col2 int);
sqlite> alter table sometable add column col3 int;
sqlite>C:\sqlite2_8_17>sqlite.exe
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> create table sometable (col1 int, col2 int);
sqlite> alter table add column col3 int;
SQL error: near "alter": syntax error
sqlite>Re: whats wrong with this little query?
2005 March 21 (3.2.0)
* Added support for ALTER TABLE ADD COLUMN.
Looks like that's right, as odd as them seems 
Re: whats wrong with this little query?
any tips on how to perform a sqlite dump?
Re: whats wrong with this little query?
Create a new table with the columns you want, copy the contents of original table to the new one, drop the original, and then create another new table with the original name and copy the contents again. You can wrap the whole thing in a transaction if you want.
CREATE TEMPORARY TABLE mytable2 (col1, col2, col3);
INSERT INTO mytable2 SELECT col1, col2, NULL [or default value for col3] FROM mytable;
DROP TABLE mytable;
CREATE TABLE mytable (col1, col2, col3);
INSERT INTO mytable SELECT col1, col2, col3 FROM mytable2;
DROP TABLE mytable2;Re: whats wrong with this little query?
thank you 