Using SQL LOAD DATA INFILE via CGI

sdyates has asked for the wisdom of the Perl Monks concerning the following question:

I am am to create the table inside the database witht he information gathered in the CGI form. However, I cannot get the database to read the information from the file. $UserFile is something like c:\bla.txt --obviously linux does not understand this and I need a way to send the file directly from my machien to mysql. What dfo I need to do?

#====> sql commands
my $sth = $dbh->prepare("CREATE TABLE $TableName ($SQLString)") or bla
+, bla bla;
$sth->execute or bla, bla bla;

 my $sth = $dbh->prepare("LOAD DATA INFILE $UserFile INTO TABLE $Table
+Name") or bla, bla bla;
$sth->execute or bla, bla bla;

#====> form used to get info...
sub generate_form {
    # Create the form
    print start_form(-method=>'POST');

    print center(
    table( {-border => 0},
    Tr( {-align=>'left', -valign=>'top'}, td("<B>Log File to import:")
+, td(filefield(-name=>'LogFile',
    -default=>'', -size=>50,  -maxlength=>50)) ), 
    Tr( {-align=>'left', -valign=>'top'}, td("<B>Database name:"), td(
+textfield(-name=>'DBName',
    -default=>'', -size=>50,  -maxlength=>50)) ),
    Tr( {-align=>'left', -valign=>'top'}, td("<B>Database user:"), td(
+textfield(-name=>'DBUser',
    -default=>'', -size=>50,  -maxlength=>50)) ),
    Tr( {-align=>'left', -valign=>'top'}, td("<B>Database password:"),
+ td(textfield(-name=>'DBPassword',
    -default=>'', -size=>50,  -maxlength=>50)) ),
    Tr( {-align=>'left', -valign=>'top'}, td("<B>Table name:"), td(tex
+tfield(-name=>'TableName',
    -default=>'', -size=>50,  -maxlength=>50)) ),
    Tr( {-align=>'center', -valign=>'top'}, td({-colspan=>3}, submit('
+action', 'Add addess')))      
    ) # end: table()
); # end: center()
print end_form();
}
[download]

I appreciate your help. I have searched site, but nothing I can find directly relates to what i am trying to do...Perhaps I cannot see the forest for the trees...

Comment on Using SQL LOAD DATA INFILE via CGI Download Code

Replies are listed 'Best First'.
Letting users upload to your CGI scripts (boo) by boo_radley (Parson) on Aug 22, 2001 at 20:08 UTC
It sound like you're trying to take a file from the user's drive, upload it and then do something interesting with it. If you look through CGI's POD, you'll come across a section called "Creating a file upload field". It describes the process of adding fields to a form to let users send your script files. It uploads the file and provides a filehandle automatically for your enjoyment. There's not much to add to Stein's documentation, really, it's pretty cookbook like.	[reply]
Re: Letting users upload to your CGI scripts (boo) by sdyates (Scribe) on Aug 22, 2001 at 20:11 UTC
You are correct, I am trying to take a file from the user's drive, upload it and then do something interesting with it. I am working with 500MB+ log files. With multiple users wanting to analise the results, loading the datafile into a DB seems to be the best way to go. Thanks for the info.	[reply]
Re: Using SQL LOAD DATA INFILE via CGI by MZSanford (Curate) on Aug 22, 2001 at 19:58 UTC
The best i can say is to upload the users file to another name, then use it. Otherwise, the library function which the `LOAD DATA INFILE` statement uses will not be able to see the datafile (as it probably uses `open()`). If you upload the users file to `/tmp/${userName}.${UserFile}`, and then run it, i would guess it would work. _{can't sleep clowns will eat me} -- MZSanford	[reply] [d/l] [select]
Re: Re: Using SQL LOAD DATA INFILE via CGI by sdyates (Scribe) on Aug 22, 2001 at 20:21 UTC
I am using the correct syntax for uploading the file. I think you are on to something when you mention uploading the file. I am not sure about the syntax. I basically want to pass the file diredctly to the database through the web browser. I don't want to have to write the file elsewhere. What is the best way to do this?	[reply]
Re: Re: Re: Using SQL LOAD DATA INFILE via CGI by MZSanford (Curate) on Aug 22, 2001 at 20:26 UTC
I am not much of a CGI programmer, but i know a bit about Database Client libraries, which is why i replied (that, and i'm opionated). I don't think there is a way to tell `LOAD DATA INFILE` to read from anything but a file. I guess you could use `mknod` to create a pipe, then fork a process to write to it while `LOAD DATA INFILE` is reading, but that might be a bit too complex a replacment for a write/load/delete temp file solution ... you make the call . Hope that helps. _{can't sleep clowns will eat me} -- MZSanford	[reply] [d/l] [select]
Re: Using SQL LOAD DATA INFILE via CGI by ckohl1 (Hermit) on Aug 22, 2001 at 23:27 UTC
Since your CGI application seems to be on a different box than the database, you have to make sure that both machines can see your upload file. Database functions such as LOAD and UNLOAD are (usually) local to the database server. This will require the database server to be able to see the uploaded file via a network file share (SAMBA). A workaround for you could be to shell out to a database loader tool that will locally slurp your upload file (on the Window box), and load it into the remote database. Or, you could take a performance hit and perform individual inserts into the table by slurping a line at a time from your file. Chris 'You can't get there from here.'	[reply]


Perl: the Markov chain saw
	PerlMonks