?
Solved

ALTER TABLE, have variable as ADD - possible?

Posted on 2011-03-15
20
Medium Priority
?
437 Views
Last Modified: 2012-06-27
hi all,

i'm putting together a very simple ALTER TABLE sql statement which will allow me to create a new table/column from my front end (web site)

the table name will depend on what i have entered into a input form field so the ADD part of the SQL needs to accept a variable but i dont seem about to get it to work.

example:

ALTER TABLE prodcustomfields
ADD #FieldName# VARCHAR (60)

Open in new window

0
Comment
Question by:bede123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
  • +2
20 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 35141937
You're doing that inside a CFQUERY?

 That should work, try it the command without a variable first.  If that works, it should also work with the CF variable


 are you getting an error?
0
 
LVL 1

Author Comment

by:bede123
ID: 35141983
yeah i tried running it against the DB first without a variable and it works fine. but if i run it inside my cfquery i get the standard:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123 VARCHAR (60)' at line 2

let me show you some more in case i have messed up someonewhere else.

<cfif IsDefined ("form.Submit")>

<cfquery name="CreateNewField" datasource="#request.dsn#" username="#request.dsnuser#" password="#request.dsnpassword#">
ALTER TABLE prodcustomfields
ADD #FieldName# VARCHAR (60)
</cfquery>

<cfelse>

<cfform name="CreateNewProdFields" action="" >

<label for="FieldName">FieldName</label>
</br>
<cfinput type="text" name="FieldName" >
</br>	
<cfinput type="submit" name="Submit" Value="Submit">

</cfform>
</cfif>

Open in new window


look about right?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35142109

You also tried running it through cfquery without a variable, right?

I think in the /CFIDE/administrator there is a list of commands that are allowed.  Perhaps this is one of them that needs checking or is not avialable.  The list is in the Data Source definition page

0
TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

 
LVL 4

Expert Comment

by:dwkd
ID: 35142490
ADD '#FieldName#' VARCHAR (60)
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 668 total points
ID: 35144081
> use near '123 VARCHAR (60)' at line 2

what is 123 ?  Is that the column name?

That may not be a valid column name.. had you tried others?
0
 
LVL 11

Assisted Solution

by:Brijesh Chauhan
Brijesh Chauhan earned 1332 total points
ID: 35144406
Alter table syntax

ALTER ONLINE TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT;

Open in new window

So in your case..

ALTER ONLINE TABLE prodcustomfields p
ADD COLUMN #FieldName# VARCHAR (60)

Open in new window


As pointed by @gdemaria in his post 35144081, '123' would not be a valid column name, try 'a123' .....

Also, which version of MySQL are you on ?

here are the docs for 5.0

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

0
 
LVL 11

Assisted Solution

by:Brijesh Chauhan
Brijesh Chauhan earned 1332 total points
ID: 35144411
Also, No gap is requited between varchar and (60) .. just varchar(60)...
0
 
LVL 3

Expert Comment

by:dagaz_de
ID: 35146102
try

ALTER TABLE prodcustomfields
`#FieldName#` varchar(60)
0
 
LVL 1

Author Comment

by:bede123
ID: 35146597
thanks everyone for your help.

turns out i had a couple of issues.

for testing purposes i was using 123 as the column name. MySQL didnt like this so i also need to build in some sort of validation to ensure reserved column names are attempted in the future.

also i removed the gap from varchar(60)

I have also updated my ALTER TABLE to ALTER ONLINE TABLE - it seems to work both ways but i guess ONLINE is the official way.

thanks again everyone.

i hope you think the point split i fair.

z


0
 
LVL 3

Expert Comment

by:dagaz_de
ID: 35146623
to use 123 as a column name is no problem you must just use the ` accent sign to tell mysql that this is you column.
Like is said `#FieldName#`
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 35146700
Dagaz_de is correct, adding the ` accent sign allows you to add the column name, I tested it and it worked correctly..


mysql> alter online table ee add column `123` varchar(60);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc ee;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| 123   | varchar(60) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 35146714
using it without the accent sign gives you the error

mysql> alter online table ee add column 123 varchar(60);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '123 varchar(60)' at line 1
0
 
LVL 1

Author Comment

by:bede123
ID: 35146775
thanks again. becuase the column name is/will be a variable i cant be 100% sure that i'll need the ``

unless the `` will be acceptable for all colun names? could i just leave the `` in place?
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 35146791
Yes, it is acceptable for all column names

mysql> alter table ee add column `abcd` varchar(255);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
0
 
LVL 1

Author Comment

by:bede123
ID: 35146794
@brijeshchauhan

ive seen examples that look like this before but i'm not sure how to interprit them.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| 123   | varchar(60) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

should they be ready from top to bottom like in a column or left to right?
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 35146826
Well I have copied it from the mysql editor..

the first row says what it represents -> Field, Type, NULL, Key, Defualt, Extra - these are basically your headers, Field column will hold the column names(id, 123), Type column will be the data type(int, varchar, etc), NULL would read if the column can accept null values or not, KEY means if the column is a key, Default would should default values and Extra any other info....

So basically, for each column you read left to right for it's properties... like filed 123 -> varchar(60) -> Yes -> NULL -> '' and then the top headers that filed name is 123, datatype is varchar(60), if can be NULL,  default value is NULL and nothing in extra..

Hope it makes sense...
0
 
LVL 1

Author Comment

by:bede123
ID: 35146965
a ha ok thanks.

let me throw this one at you then:

ADD `#Fieldname#` #SelectFieldType#(#FieldLength#) #AllowNull#

i'm setting the allownull variable with either ALLOW NULL or NOT NULL

this seems to work ok. but how then do i assign the default value?

what if sometimes i want the default value to be NULL or then maybe i want the default value to be FOO

would it look like this?

ADD `#Fieldname#` #SelectFieldType#(#FieldLength#) #AllowNull# FOO

or

ADD `#Fieldname#` #SelectFieldType#(#FieldLength#) #AllowNull# NULL





0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 35147036
By default, the default value is NULL and NULL column is YES.

consider the following

1. The below statement, 1236 Column will not allow NULL and default is 123

mysql> alter table ee add column `1236` varchar(255) default '123' NOT NULL;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

2. The query below will have column 1237 as NOT NULL and the default value is NULL

mysql> alter table ee add column `1237` varchar(255)  NOT NULL;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. This statement will allow nulls for 1238 and the default value is 123

mysql> alter table ee add column `1238` varchar(255) default '123' ;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
0
 
LVL 1

Author Comment

by:bede123
ID: 35147285
that's a perfect explanation. thank you.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35147835
For a dynamic application, I suggest not allowing the unusual column names.  Names starting with numbers, spaces, etc that may be allowed with special characters, IMHO, should be avoided.   Avoiding them will help you code the rest of the application more smoothly, you won't be forced to use ` or [] or whatever around the columns in SELECTs, JOINs, etc...

Just a suggestion for easier coding, not a technical requirement
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question