Solved

ALTER TABLE, have variable as ADD - possible?

Posted on 2011-03-15
20
422 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
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
 
LVL 4

Expert Comment

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

Accepted Solution

by:
gdemaria earned 167 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 333 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 333 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

17 Experts available now in Live!

Get 1:1 Help Now