Solved

ALTER TABLE, have variable as ADD - possible?

Posted on 2011-03-15
20
417 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
  • 7
  • 6
  • 4
  • +2
20 Comments
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
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
Comment Utility
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
Comment Utility

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
 
LVL 4

Expert Comment

by:dwkd
Comment Utility
ADD '#FieldName#' VARCHAR (60)
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 167 total points
Comment Utility
> 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 333 total points
Comment Utility
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 333 total points
Comment Utility
Also, No gap is requited between varchar and (60) .. just varchar(60)...
0
 
LVL 3

Expert Comment

by:dagaz_de
Comment Utility
try

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

Author Comment

by:bede123
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Expert Comment

by:Brijesh Chauhan
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
that's a perfect explanation. thank you.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now