Best way to upload data to a DB

[Palli]

On my website i want to allow users to upload tab delimited files which are then stored as new tables in a MySQL database.



i am currently using java servlets to handle requests made to the database. Could i pass the text file to the servlet and use SQL function "LOAD DATA INFILE...".

Would i have to create the table first, on the fly. so in the servlet it would first obtain the column names and create a table accordingly using, "CREATE TABLE test ...." . and then call "LOAD DATA INFILE...".


Does anyone have any tips on the best way of achieving this, or a link to a tutorial or guide?


Thanks

[Khalid Ali]

as far as my understanding is load data infile does require a table to exist already, here is the syntax
MySQL tutorial (http://dev.mysql.com/doc/refman/5.0/en/load-data.html)

[Palli]

Yeah i thought so.

2 Questions then. When have sent the attached text file to the servlet how can i extract it data on the fly, without saving it, inputstream? anyone have any examples.

Thanks

[Khalid Ali]

Yes, servletinput stream will give u a method to readLine() use this put all the file data in a string variable and the do your processing

[Palli]

The text file will be tab delimited... if i put all the information into a string variable using inputstream won't it lose the tab delimited formatting?

[Khalid Ali]

not it wont, it will have delimitation character in it. However if you want you can use String.split("delimiter character") to create an array

[Palli]

Khalid Ali, thanks for your help.

I now have it working perfectly, thank you very much.

Two tutorials i found were also very useful, if anyone else wants to know....


http://www.developershome.com/wap/wapUpload/wap_upload.asp?page=jsp

http://www.onjava.com/pub/a/onjava/2001/04/05/upload.html?page=1