New topic: help with alter table COLUMN please
<http://forums.realsoftware.com/viewtopic.php?t=31369> Page 1 of 1 [ 5 posts ] Previous topic | Next topic Author Message Dralion Post subject: help with alter table COLUMN pleasePosted: Wed Dec 02, 2009 7:28 am Joined: Sat Mar 15, 2008 8:14 am Posts: 143 Location: Montreal, Quebec, Canada i everybody i want to rename column in my sqlite DB - iv populate the name of the column in a listbox and by cliclking on it - place it in a textfield (efchange) and what to replace by the sring in another textfield (efnewname) BUT i got the message (the name of my column doesnt exist)... Weird because the name came from the DB itself here my code... iv got no error on compiling so syntax is not so bad Code:dim sqx as string rs2 = m_db2.SQLSelect ("SELECT * FROM room") If rs2=Nil Then If m_db2.Error Then MsgBox(m_db2.ErrorMessage) return Else sqx="ALTER TABLE room."+"'"+efchange.text+"'"+endofline+"RENAME TO "+"'"+efnewname.text+"'" m_db2.SQLexecute (sqx) If m_db2.Error Then MsgBox(m_db2.ErrorMessage) m_db2.SQLExecute("Commit") //no error, save change End If iv try puting the name of my db. + the name of the table + variable... now iv got syntax error Code:sqx="ALTER TABLE YDBSalut.room"+"'"+efchange.text+"'"+endofline+"RENAME TO "+"'"+efnewname.text+"'" Thanks for help _________________ -------------------- RB 2009r3 on winXP Sp2 Top jefftullin Post subject: Re: help with alter table COLUMN pleasePosted: Wed Dec 02, 2009 8:10 am Joined: Wed Nov 15, 2006 3:50 pm Posts: 1033 I was going to unpick the SQL, but I did a quick search on ALTER TABLE in SQLLite, and found this: http://www.sqlite.org/lang_altertable.html "SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table." ============================================ Where a database allows column renaming (not all do), the syntax is ALTER TABLE TABLENAME RENAME COLUMN OLDCOLUMNNAME TO NEWCOLUMNNAME but your code reads ALTER TABLE room.'Sometext' RENAME TO 'Newtext'; And probably should have been "ALTER TABLE room RENAME " +efchange.text +endofline+"TO " +efnewname.text + ";" ======================================================== So where renaming is not possible, you can create a new table with the correct column names. Insert into it using a select from the old table. Then drop the old table. Then rename the new table to use the old table name. Top Dralion Post subject: Re: help with alter table COLUMN pleasePosted: Thu Dec 03, 2009 7:54 am Joined: Sat Mar 15, 2008 8:14 am Posts: 143 Location: Montreal, Quebec, Canada ouch!! ok...now the path to do what i want is: 1-populate all DB column (between 3 and 40) 2-select the name of the old column and type the new name 3-CREATE NEW TABLE With all unchanged column (with data) and ad new column name Transfering the data of coresponding old column name NOW talking code its another game I cannot use CREATE TABLE new_table AS (SELECT * FROM old_table); because it copy ALL old column I cannot use CREATE TABLE new_table AS (SELECT column_1, column2, ... column_n FROM old_table_1, old_table_2, ... old_table_n); because i dont know the name and how many column So i must put variable name and quantity of column to SELECT Can someone point me to right coding direction please Thanks _________________ -------------------- RB 2009r3 on winXP Sp2 Top DaveS Post subject: Re: help with alter table COLUMN pleasePosted: Thu Dec 03, 2009 8:56 am Joined: Sun Aug 05, 2007 10:46 am Posts: 1804 Location: San Diego, CA is this a one time thing? or something the program needs to to on an on-going basis? If its one time... [or rarely]..... look into a free tool called SQLITEMAN [google it] It works with RB databases... and will alter columns for you..... it does all the juggling behind the scenes for you _________________ Dave Sisemore MacPro, OSX 10.5.8 RB2009r2 Top Dralion Post subject: Re: help with alter table COLUMN pleasePosted: Thu Dec 03, 2009 9:55 am Joined: Sat Mar 15, 2008 8:14 am Posts: 143 Location: Montreal, Quebec, Canada Its gone be a routine. Every DB i received (from client) must pass this step.. To standardize the column name and be ready for MANY cross reference manipulation between column. At the same time, a log is writen to be able to repeat exact step to specific DB client. Eventually its gone be a loop finding old name et rename to new automatically So i think, its must be custom coding Thanks _________________ -------------------- RB 2009r3 on winXP Sp2 Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 5 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
