Variables in Create Statement

Question

I am trying to use some variables in a creation script for database setups. I am not exactly sure how to use them. Please explain how to properly format my code. Below is the code I try, and the error I get:

SET @username = 'xxxx'; -- store number goes here
SET @password = 'xxxxxx'; -- store password goes here

CREATE TABLE IF NOT EXISTS `my_table` (
`id` int(11) auto_increment,
`release_date` datetime,
`front_image_file` varchar(255),
PRIMARY KEY  (`id`)
) ENGINE=FEDERATED  
DEFAULT CHARSET=latin1 
AUTO_INCREMENT=1 
CONNECTION='mysql://`@username`:`@password`@host_name:3306/database_name/table_tame' ;

error

#1432 - Can't create federated table. The data source connection string 'mysql://`@username`:`@password`@host_name:3306/databa' is not in the correct format

I also tried it without the `

Trying EdmundG's solution

SET @username = 'xxxx'; -- store number goes here
SET @password = 'xxxxxx'; -- store password goes here

CREATE TABLE IF NOT EXISTS `my_table` (
`id` int(11) auto_increment,
`release_date` datetime,
`front_image_file` varchar(255),
PRIMARY KEY  (`id`)
) ENGINE=FEDERATED  
DEFAULT CHARSET=latin1 
AUTO_INCREMENT=1 
CONNECTION='Uid=@username; Pwd=@password; Server=****; Port=3306; Database =****; Table=****;' ;

didn't work, still says not formatted correctly

Answer 1

OK third try.

There are two possible problems I can see.

1. You have right quotes instead of generic single quotes in your SQL.

2. You need to define the variables and execute before running the SQL that uses those variables.

See http://www.connectionstrings.com/mysql

Answer 2

If you are using MySQL 5+ could you try placing it in a stored function, and then running the function?

Answer 3

you can't do this. mysql doesn't parse DDL statements for variable.

Answer 4

try this

SET @username = 'xxxx';-- store number goes here

SET @password = 'xxxxxx';-- store password goes here

CREATE TABLE IF NOT EXISTS my_table ( id int(11) AUTO_INCREMENT, release_date datetime, front_image_filevarchar(255), PRIMARY KEY (id)) ENGINE=FEDERATED DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 CONNECTION='Uid='+@username'; Pwd='+@password+'; Server=**; Port=3306; Database =**';

Answer 5

MySQL doesn't support variable interpolation directly into syntax like that.

You could, however, build a string with those variables and then prepare and execute it as dynamic SQL.

SET @username = 'xxxx'; -- store number goes here
SET @password = 'xxxxxx'; -- store password goes here

SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS `my_table` (',
    '  `id` int(11) auto_increment,',
    '  `release_date` datetime,',
    '  `front_image_file` varchar(255),',
    '  PRIMARY KEY  (`id`)',
    ') ENGINE=FEDERATED',
    ' DEFAULT CHARSET=latin1', 
    ' AUTO_INCREMENT=1', 
    ' CONNECTION=''mysql://', @username, ':', @password, 
    '@host_name:3306/database_name/table_tame');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE stmt;