Click here to get back home

Create database using variable name
 Home | NewsGroups | Search | About
Login Password
Register Now! Lost Password?
 mailing.database.mysql    Post an article   get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content
Subject Author Date
Create database using variable name deja 09-13-2005
Get Chitika Premium
Posted by deja on September 13, 2005, 8:59 am
Please log in for more thread options
 Hello,

I am using an SQL script to create a temporary database backup. While
there may be "other" or "better" ways to do this - I am really only
asking if the syntax (with tweaking) is possible - and if so what it
is.

Here is a very stripped down version of what I'm trying to attempt:

set @backUpName = 'myBackup';
create database @backUpName;


This throws a syntax error, but I don't know why.

Thanks!
CF



Posted by Malcolm Dew-Jones on September 13, 2005, 9:27 am
Please log in for more thread options
 deja@chronofish.com wrote:
: Hello,

: I am using an SQL script to create a temporary database backup. While
: there may be "other" or "better" ways to do this - I am really only
: asking if the syntax (with tweaking) is possible - and if so what it
: is.

: Here is a very stripped down version of what I'm trying to attempt:

: set @backUpName = 'myBackup';
: create database @backUpName;

: This throws a syntax error, but I don't know why.

I thought I knew why, but wanted to confirm it. I googled the mysql SET
command, and it had a link to "user variables".


MySQL Reference Manual :: 9.3 User Variables

        ...
        User variables may be used where expressions are allowed. This
        does not currently include contexts that explicitly require a
        literal value

In other words, things like
        SELECT * FROM table WHERE col1 = @a_value;
will work but
        SELECT * FROM @table_name;
will not work because the table name must be a literal value, not an
expression.

(caveat, mysql version not taken into account)


--

This programmer available for rent.


Posted by Bill Karwin on September 13, 2005, 10:08 am
Please log in for more thread options
Malcolm Dew-Jones wrote:
>         User variables may be used where expressions are allowed. This
>         does not currently include contexts that explicitly require a
>         literal value
>
> In other words, things like
>         SELECT * FROM table WHERE col1 = @a_value;
> will work but
>         SELECT * FROM @table_name;
> will not work because the table name must be a literal value, not an
> expression.

Well, to be accurate, a table name is not a literal value. A table name
is a reference to some schema object. A literal value is something like
"abc" or 12.

I would guess (without having tried it ;-) that the comment about
literal values refers to contexts like the arguments of the LIMIT
clause, which take literal integer values, but not expressions or
parameters. That is, the following would not be permitted:
SELECT * FROM table LIMIT @a, @b;

Regards,
Bill K.


Similar ThreadsPosted
Can NOT Create or Drop a Database :o( September 16, 2005, 10:04 am
create database privileges May 26, 2006, 4:46 am
Please help : #1006 - Can't create database 'boby'. (errno: 13) October 19, 2006, 7:32 am
Account privilege requirements to execute CREATE DATABASE. November 29, 2005, 8:49 am
order by with variable June 8, 2006, 10:52 am
PreparedStatements with variable number of parameters July 17, 2006, 3:40 pm
User Variable and DATE_ADD Function February 9, 2007, 11:28 am
multiple variable length seaching on same column July 28, 2005, 5:01 am
Navicat Frontend: Possible to accept keyboard input to set user variable? January 10, 2006, 6:37 pm
Extracting Field data and putting each field into a shell variable? June 19, 2006, 3:28 pm

Our other projects:

Art Dolls, Fairies and Mermaids - Sunnyfaces.net

Roy's Linux, Programming and Search Engines messages

1-Script XML SitemapXML Sitemap