Using shell script to create tables 3

[josel]

Howdy!

I have several dozen tables to create and lots of them have a lot of fields. It is easy to create these tables using MyPHPAdmin or any other GUI tool but I want to make it even easier :|)

I wrote this script:

mysql -u filepro -pfilePro -e "CREATE TABLE 'eglwms.jose2' (
'field_01' VARCHAR(10),
'field_02' DECIMAL(9,2) DEFAULT '0.00' NOT NULL,
'field_03' VARCHAR(10) NOT NULL,
'field_04' VARCHAR(10) NOT NULL,
'field_05' VARCHAR(10),
PRIMARY KEY ('field_03'),
INDEX ('field_04'),
UNIQUE (
'field_01' 
),
) COMMENT = 'Ocean Export BL';"

Of course, it does not work. I get an error

ERROR 1064 at line 1:  You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL version for the right syntax to use near ''dbname.testtable' (
'field_01' VARCHAR(10),
'field_02' DECIMAL(9,2

I am following sample code as used by MyPHPAdmin when you create a new table. The Admin uses backstick but if I used these, the OS interprets them as commands within the quotes (").

What must I do to overcome this one?

Thank you all in advance!


Jose Lerebours

KNOWLEDGE: Something you can give away endlessly and gain more of it in the process! - Jose Lerebours

 

[TonyGroves]

If you escape the backticks with backslashes, that should work.

Another solution would be to store the SQL in a file, and pipe the file to mysql:
[tt] mysql -u filepro -pfilePro <query.sql[/tt]

 

[josel]

I should know better, but after 10+ hours at work, I start to transform into a "regular" fella!

Your suggestion cured the original error. I am now getting a different one, it tells me that the table name is not valid. Evidently, it does not like `dbname.table`.

How do I specify the database in cases such as this?

Regards;


Jose Lerebours


KNOWLEDGE: Something you can give away endlessly and gain more of it in the process! - Jose Lerebours

 

[nego78]

with -D databasename switch

mysql -u filepro -pfilePro -D databasename <query.sql

gry online

 

[TonyGroves]

Nego78 is right, or you could also use[tt] dbname.`tablename` [/tt].

 

[josel]

Great stuff guys!

It works like a charm. I am in my way to writing the code for the the dynamic scripts to create needed tables. Oh boy, I feel armed and dangerous.

Thanks;


Jose Lerebours


KNOWLEDGE: Something you can give away endlessly and gain more of it in the process! - Jose Lerebours