Gossamer Forum
Home : Products : Gossamer Links : Pre Sales :

Importing/Loading Database

Quote Reply
Importing/Loading Database
I need your help guys! My client purchased Links SQL. Now he wants a directory of local businesses ala Yellow Pages. He is going to get his staff to create the various categories offline and then wants to import the database.
I've been trying to import a test database using the MySQL load commands posted by pugdog a while ago. This is what I tried:
LOAD DATA LOCAL INFILE '/u/web/free66/cgi-local/directory/admin/cattest.txt' REPLACE INTO TABLE Category.Links FIELDS TERMINATED BY '|' (ID,Name,Description,Meta_Description,Meta_Keywords,Header,Footer,Number_of_Links,Has_New_Links,Has_Changed_Links,Newest_Link)

All I get is an access for user denied error. I tried another MySQL command I found in a post in order to change permissions, but that didn't work.
I then contacted the hosting company. First they said "the ability to import text files into MYSQL has been disabled. This is due to a stability issue of importing invalid data into the sql server." omg - so I tell em not to be so stupid and now they have pointed me to a MySQL Monitor script on the hosting control panel. The load command doesn't work in that either.
Please can someone check the above command.

This is an extract from the cattest.txt file:
ID|Name|Description|Meta_Description|Meta_Keywords|Header|Footer|Number_of_Links|Has_New_Links|Has_Changed_Links|Newest_Link
1|Surrey||Directory of businesses in the Surrey area of the UK.|Surrey, reading|||2|No|No|1999-01-01
2|Sussex||Directory of businesses in the Sussex area of the UK|Sussex|||0|No|No|1999-01-01

Why doesn't the Import data function in the Links Admin Menu work??


Quote Reply
Re: Importing/Loading Database In reply to
oops I posted it in the wrong place - should have been in SQL Discussion - sorry. Can an administrator move this?

Quote Reply
Re: Importing/Loading Database In reply to
You can delete Threads by checking the DELETE checkbox in the EDIT forms in this forum.

Wink

Regards,

Eliot Lee
Quote Reply
Re: Importing/Loading Database In reply to
Have you tried using the Import.pl script?

Regards,

Eliot Lee
Quote Reply
Re: Importing/Loading Database In reply to
First, the LOAD command above will only import from a file. The syntax is pointing to a file.

I think if your ISP turned off loading from a text file, then you should find another ISP. That is the _main_ way of backing up and restoring, importing and transferring data in and out and between databases.

If they have provided another solution to you, then ask them how to use that solution to import your data. Perhaps they have written a "shell" program around the load function, but if not, I would suggest a new ISP. I've never heard of turning off "load from file" on MySQL because you'd load bad data -- heck, you could type in bad data just as easily, and most likely to more damaging effect.





http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: Importing/Loading Database In reply to
Thanks for the replies. I'd love to change ISP - but it's not my decision. It's my clients website. Anyways this is what the ISP (a Verio company) has replied with:
The ability to import text files into MYSQL has been disabled. This is due a stability issue of importing invalid data into the sql server.

The database you wish to import can be cut and pasted into the command line interface provideed and, as long as it is wrapped with the correct sql commands, can be used to set up the new database.

Now the LOAD command can only import a file if I'm right. So I can't use that. Any ideas on what the other sql commands there are. Insert? surely that will mean sitting and typing all the data into the command line interface.

Quote Reply
Re: Importing/Loading Database In reply to
You are better off writing a program to do the inserts for you. Otherwise, you will be typing the lines in one by one.

Strike Verio off the list of hosting options compatible with commerce.



http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: Importing/Loading Database In reply to
In Reply To:
The ability to import text files into MYSQL has been disabled. This is due a stability issue of importing invalid data into the sql server.

The database you wish to import can be cut and pasted into the command line interface provideed and, as long as it is wrapped with the correct sql commands, can be used to set up the new database.
Good lord, I hate that sort of mis-information. They are saying you can't use LOAD DATA INFILE as you will import invalid data in the server? Instead you import it from the file line by line? Yikes.

The solution they provide is ridiculous.

To use this, you need to write a perl script to do the import. Something like:

Code:
my $dbh = DBI->connect( ... );
my $sth = $dbh->prepare ("INSERT INTO table VALUE (?, ?, ..., ?)");
open (FILE, "/path/to/file.txt") or die $!;
while (<FILE>) {
chomp;
@vals = split /DELIMITER/;
$sth->execute (@vals);
}
close FILE;
$sth->finish;
$dbh->disconnect;
Depending on your perl level that will either be obvious, give you some help, or totally confuse you. ;)

Cheers,

Alex

--
Gossamer Threads Inc.