Scott Purcell [EMAIL PROTECTED] wrote on 03/10/2006 12:28:21 PM:
Hello,
I have been given a file to import into the mysql 5.0 database.
This is how it is formatted:
I created a database called app which is brand new.
Right off, the bat, I noticed there is a field called numeric. So I
changed that to int.
But when I try and import it, it is complaining about the double quotes.
So then I changed the double quotes to single, and it still complained.
What can I do to make this import work? I am trying to find the
guidelines.
Ps the file is pretty long, so here is only a sampling:
Thanks,
Scott
sample script snipped (thank you for posting the snippet. It made
diagnosing your problem much easier)
Scott,
You are going to need to edit this file to be less T-SQL and more ANSI
SQL. What you have is written to work on an MS SQL server or on MS Access.
There are several things you need to change in order to get MySQL to
accept it.
a) You should enquote datbase names, table names, and column names with
backticks ` ` not single quotes ' '. Double quotes are accepted, too, but
only if you tell the server they are OK. It's just safer to use backticks
and it's the standard method for MySQL.
DROP TABLE `APP`.`ATTACHMENT`;
b) If the table `ATTACHMENT` doesn't exist yet in the database `APP`, the
statement in a) will throw an error. To make this a conditional DROP
COMMAND, use the IF EXISTS modifier and you won't need to worry about it
(you get a warning instead of an error).
DROP TABLE IF EXISTS `APP`.`ATTACHMENT`;
c) In order to use FOREIGN KEYs as part of your design, you have to use a
storage engine that supports them. Right now in MySQL, only the InnoDB
engine supports FOREIGN KEYs so all of the tables you want to create a FK
_from_ or a FK _to_ (both ends) will need to have ENGINE=InnoDB; at the
end of the statement
CREATE TABLE ... (
...
) ENGINE=InnoDB;
d) In order to create a FK between two tables, you also need to have to
have both ends of the FK using the same data type (including signed or
unsigned) and they must both be indexed.
CREATE TABLE APP.CATEGORY (
`CATAGORY_ID` int NOT NULL,
...
`CREATED_BY` int,
...
`PARENT_CATAGORY` int,
CONSTRAINT CATEGORY_pkey PRIMARY KEY (CATAGORY_ID),
INDEX ix_created_by (`created_by`), - add these to this
table definition
INDEX ix_parent_catagory (`parent_catagory`), -
CONSTRAINT CATEGORY_PARENT_CATAGORY_fkey FOREIGN KEY
PARENT_CATAGORY) REFERENCES APP.CATEGORY (CATAGORY_ID) ON
UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT authorfk FOREIGN KEY (CREATED_BY) REFERENCES APP.USER
(USER_ID) ON UPDATE NO ACTION ON DELETE NO ACTION,
...
) ENGINE=InnoDB;
e) You have to define a storage size for all of your varchar columns.
CREATE TABLE `APP`.`ATTACHMENT` (
...
`ATTACHMEMT_PATH` varchar(256),
...
);
I am so sorry it's not going to be as painless as you originally thought.
However by needing to go through this you can also fix some spelling
errors in your column names (category is not spelled catagory like you
have in some of your columns). However, if you already have a lot of code
that uses the misspelling, it may not be worth the effort to fix it.
Please refer to the manual for more details about all of the changes I
suggest...
CREATE TABLE syntax:
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
CHAR and VARCHAR columns:
http://dev.mysql.com/doc/refman/5.0/en/char.html
The InnoDB storage engine:
http://dev.mysql.com/doc/refman/5.0/en/innodb.html
Foreign keys:
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
If this database already exists on a MS SQL machine and you can access it
from your location, it may be easier to use one of the migration tools
to automagically transmute and copy the structure from the old system
directly into MySQL.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
