Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

ALTER TABLE, have variable as ADD - possible?

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
bede123
Asked:
bede123
  • 7
  • 6
  • 4
  • +2
3 Solutions
 
gdemariaCommented:
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
 
bede123Author Commented:
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
 
gdemariaCommented:

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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
dwkdCommented:
ADD '#FieldName#' VARCHAR (60)
0
 
gdemariaCommented:
> 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
 
Brijesh ChauhanStaff IT EngineerCommented:
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
 
Brijesh ChauhanStaff IT EngineerCommented:
Also, No gap is requited between varchar and (60) .. just varchar(60)...
0
 
dagaz_deCommented:
try

ALTER TABLE prodcustomfields
`#FieldName#` varchar(60)
0
 
bede123Author Commented:
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
 
dagaz_deCommented:
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
 
Brijesh ChauhanStaff IT EngineerCommented:
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
 
Brijesh ChauhanStaff IT EngineerCommented:
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
 
bede123Author Commented:
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
 
Brijesh ChauhanStaff IT EngineerCommented:
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
 
bede123Author Commented:
@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
 
Brijesh ChauhanStaff IT EngineerCommented:
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
 
bede123Author Commented:
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
 
Brijesh ChauhanStaff IT EngineerCommented:
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
 
bede123Author Commented:
that's a perfect explanation. thank you.
0
 
gdemariaCommented:
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now