I just started a new job as webmaster/computer person for a local B2B wholesale company. One of the things that my boss wants to do is use PHP to show a database of merchandise on the company website (and their website hosting plan only allows MySQL databases). One of his suppliers already has a database of all their products, which they e-mailed to him, so that he could use it on the website. He forwarded the e-mail to me.
It's a zip file, and the supplier mentioned in their e-mail that this was an Oracle database file, but said that it should be easy to migrate it to MySQL, and included lots of links to migration software. I unzipped the file and saw that it was made of:
a file with the extension .sql, which contained the SQL code to create the tables (I think this is called a "dump file," right?);
several delimited text files, containing the table data;
and several images.
Since it's all basically text, I don't see anything specifically "Oracle" about it, and I don't think I need to take the supplier's suggestion of creating an Oracle database first and then migrating it to MySQL. It seems like I could skip the middle man and just create the MySQL database out of these files. My company's web host provides phpMyAdmin for working with SQL. So I could enter the dump file as SQL code, and then use the function where I can insert data into a table from a text file.
The only problem is that I am getting error messages when I try to use the dump file to create the tables. Here is the code for one of the tables:
*****************
CREATE TABLE DIRECTORY (
ID NUMBER CONSTRAINT pk_dir PRIMARY KEY,
NAME VARCHAR2(150) NOT NULL,
DESCRIPTION VARCHAR2(500),
AVAILABLE NUMBER(1) DEFAULT 1 NOT NULL,
PORTAUTHORITY NUMBER(1) DEFAULT 0 NOT NULL,
PARENTID NUMBER CONSTRAINT fk_dir REFERENCES DIRECTORY(ID),
HEADERIMAGE VARCHAR2(100),
TITLEIMAGE VARCHAR2(100),
BTNIMAGE VARCHAR2(100),
DWNBTNIMAGE VARCHAR2(100),
LIFESIMAGE1 VARCHAR2(100), LIFESIMAGE2 VARCHAR2(100), HTMLTEMPLATE VARCHAR2(100),
MISCFIELD1 VARCHAR2(100),
MISCFIELD2 VARCHAR2(100),
MISCFIELD3 VARCHAR2(100),
PING NUMBER(1),
MAIN NUMBER(1),
NAUTICA NUMBER(1),
TEAMWAER NUMBER(1),
GOLF NUMBER(1)
);
******************
I did some searching on the web, and interestingly, I came across a Usenet post from 2003 from a guy with the same problem. It looked like the exact same file, and even the same situation (it was a file from a supplier catalog that this guy was trying to use with MySQL). So maybe our supplier provides their catalog to a lot of companies. Anyway, several people responded to this guy's post, saying that some of the code was specific to Oracle. They said that "varchar2" should be changed to "varchar" and that "number" should be changed to "numeric." They also said that "constraint pk_dir" and "constraint fk_dir" are only used in Oracle. (Here is the link to the Usenet thread, if you are interested:
http://groups.google.com/group/alt.php.sql/browse_frm/thread/71a29ccb07315a0b/6454dc416dc6f5da?lnk=st&q=constraint+pk_dir&rnum=1&hl=en#6454dc416dc6f5da )
I made the changes to "varchar2" and "number" but wasn't sure what to do with "constraint pk_dir" and "constraint fk_dir" so I removed them. I was able to successfully create the tables this way, but I want to make sure that nothing was lost in the translation. Is there some kind of MySQL equivalent to "constraint pk_dir" and "constraint fk_dir"? Being an SQL novice, I don't know what those bits of code would do, but I assume that it has something to do with defining table relationships. I'm thinking that I'll have created all the tables and then find out that something doesn't work properly because I deleted those parts, so if anybody has any advice/suggestions, I would appreciate it.
-Jaclyn