I have a script that inserts .csv data into a table;
I want different users to insert their data in the same table. To differenciate them I make them fill a name (called "nom") field in the html before uploading the .csv.
But when another user uploads data, the previous one disappears! I just use a INSERT INTO values, so it should not happen.
Sorry if there are big mistakes, but I'm newbie in Perl (the script is not mine, just modified). Thanks a lot!
Expand|Select|Wrap|Line Numbers
- use CGI qw(:standard);
- use DBI;
- MAIN:{
- # print webpage headers
- print header;
- # variables that will hold form values and other information
- my $query;
- my $nom;
- my $dbname1;
- my $username1;
- my $password1;
- my $port1;
- my $schema1;
- my $host1;
- my $dbh;
- my $sth;
- my $updir = 'C:\\Apache\htdocs\upload';
- my $wfilename;
- # read values from html page
- $query=new CGI;
- $dbname1=$query->param("dbname");
- print "dbname=$dbname1";
- $username1=$query->param("username");
- $password1=$query->param("password");
- $nom=$query->param("nom");
- print "NOM=$nom";
- $host1=$query->param("host");
- $port1=$query->param("port");
- print "port=$port1";
- $schema1=$query->param("schema");
- # connect to database
- $dbh = DBI->connect("dbi:PgPP:dbname=$dbname1;host=$host1;port=$port1",$username1, $password1) or die "Could not connect to database. Error: ".DBI->errstr;
- print "dbh";
- $data_filehandle=$query->upload("datafile");
- $wfilename="datafile_ainsertar.csv";
- open(fileout,">$updir/$wfilename");
- while ( <$data_filehandle> )
- {
- print fileout;
- }
- close(fileout);
- print "Data was successfully uploaded <br>";
- print "NAME OF THE FILE TO PUT ON POSTGIS (CREAT ARA): $wfilename";
- # read and insert data into cases
- open (IN, "$updir\\datafile_ainsertar.csv");
- while (<IN>) {
- #Blank lines can creep into .csv files. Skip them.
- next if (m/^\s*$/);
- chomp;
- my ($id, $longitude, $latitude, $species, $genus, $family) = parse_csv($_);
- # Make all fields SQL-friendly
- $id = nullify_field($id);
- $longitude = nullify_field($longitude);
- $latitude = nullify_field($latitude);
- $species = nullify_field($species);
- $genus = nullify_field($genus);
- $family = nullify_field($family);
- $sth=$dbh->prepare('insert into '.public.'.species6 (id, longitude, latitude, species, genus, family) values ('."$id".',\''."$longitude".'\',\''."$latitude".'\',\''."$species".'\',\''."$genus".'\',\''."$family".'\')');
- $sth->execute or die "Error inserting data into species table";
- $count = $count + $sth->rows;
- };
- close IN;
- print "Data was successfully inserted <br />";
- # create geometry
- $sth=$dbh->prepare('UPDATE '.public.'.species6 SET the_geom = PointFromText(\'POINT(\' || longitude || \' \' || latitude || \')\',4326)');
- $sth->execute;
- print $nom;
- print "Geometry was successfully created <br />";
- $sth2=$dbh->prepare('UPDATE '.public.'.species6 SET name = ?');
- $sth2->execute($nom) or print "ERRRROR";
- # ---------------------------------------------- Functions --------------------------------------------
- sub parse_csv {
- # The goal here is to just go ahead and split on commas,
- # and then find chunks that start with a " and assume that we've
- # broken apart a field containing commas; re-join the chunks to
- # the chunk beginning with " until we find a chunk ending with ".
- my @chunks;
- my $chunk;
- my @fields;
- my $field;
- my $line = shift;
- @chunks = split(/,/, $line);
- my $i;
- for ($i = 0; $i <= $#chunks; ++$i) {
- $chunk = $chunks[$i];
- $field = $chunk;
- # If chunk starts wtih a double-quote but does not end with one,
- if (substr($chunk, 0, 1) eq '"' && substr($chunk, -1, 1) ne '"') {
- # join the current chunk with the next chunk, replacing the
- # comma that got eliminated during the split on commas
- ++$i;
- $chunk = $chunks[$i];
- $field .= "," . $chunk;
- # and continue to do so until you find a chunk that ends with "
- # or you have run out of chunks.
- while (substr($chunk, -1, 1) ne '"' && $i <= $#chunks) {
- ++$i;
- $chunk = $chunks[$i];
- $field .= "," . $chunk;
- }
- # Our goal is to get rid of all field delimitors, so
- # get rid of the leading double-quote
- $field = substr($field, 1);
- # and the trailing double-quote.
- chop($field);
- }
- # If the chunk starts and ends with a double-quote,
- if (substr($chunk, 0, 1) eq '"' && substr($chunk, -1, 1) eq '"') {
- # get rid of the leading double-quote
- $field = substr($field, 1);
- # and the trailing double-quote.
- chop($field);
- }
- # In CSV files, double quotes are escaped by doubling them up,
- # so un-double them.
- $field =~ s/""/"/g;
- # Finally, we have a field that is completely usable, so add it to the
- # array of fields we will return at the end of this subroutine.
- push(@fields, $field);
- # Clear the field for re-use in the next iteration of this loop.
- $field = "";
- }
- return @fields;
- }
- sub nullify_field {
- # For SQL fields that do not need to be wrapped in single quotes.
- # If a field is empty, replace it with the string "null",
- # which can safely be used in sql insert statements.
- my $field = $_[0];
- if ($field) {
- return $field;
- } else {
- return "null";
- }
- }
- print "<b>hem escrit algo a la BD</b>";
- print end_html;
- }