From 6c83d4ae6ab9ab213976c960b3bea4dce12da324 Mon Sep 17 00:00:00 2001
From: lingutln <lingutln@localhost>
Date: Fri, 10 May 2013 23:24:29 +0000
Subject: [PATCH] used stored_procedure for annotated_image table

svn path=/; revision=476
---
 .../ia_upload/annotation_data_importer.pl     | 20 ++++++++++---------
 .../ia_upload/stored_procedures.sql           |  8 ++++++++
 2 files changed, 19 insertions(+), 9 deletions(-)

diff --git a/image_annotation_db/ia_upload/annotation_data_importer.pl b/image_annotation_db/ia_upload/annotation_data_importer.pl
index 80b972f..20c5092 100644
--- a/image_annotation_db/ia_upload/annotation_data_importer.pl
+++ b/image_annotation_db/ia_upload/annotation_data_importer.pl
@@ -107,12 +107,7 @@ sub insert_image_data_into_database
   my $image_path = trim($image_data_fields[1]) . trim($image_data_fields[0]);
 
   # preprocessing ip_comment data
-  my $ip_comment = trim($image_data_fields[12]);
-
-  if(undef $ip_comment){
-    $ip_comment = ""
-  }
-
+  my $ip_comment     = trim($image_data_fields[12]);
   my $source_db      = trim($image_data_fields[14]);
   my $source_db_name = "";
   my $source_db_id   = "";
@@ -126,9 +121,16 @@ sub insert_image_data_into_database
   my $collection_date     = trim($image_data_fields[11]);
   my $comments 		  = trim($image_data_fields[15]);
 
-  $dbh->do('INSERT INTO annotated_image (curator_id, taxon_id, image_path, collection_location, collection_date, ip_comment, doi, source_db_name, source_db_id, comments) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
-	    undef, $curator_id, $taxon_id, $image_path, $collection_location, $collection_date, $ip_comment, $doi, $source_db_name, $source_db_id, $comments);
-  my $annotated_image_id = $dbh->{'mysql_insertid'};
+  #$dbh->do('INSERT INTO annotated_image (curator_id, taxon_id, image_path, collection_location, collection_date, ip_comment, doi, source_db_name, source_db_id, comments) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
+#	    undef, $curator_id, $taxon_id, $image_path, $collection_location, $collection_date, $ip_comment, $doi, $source_db_name, $source_db_id, $comments);
+  #my $annotated_image_id = $dbh->{'mysql_insertid'};
+
+  my $annotated_image_id;
+  my $sth = $dbh->prepare("call import_annotated_image('$curator_id', '$taxon_id', '$image_path', '$collection_location', '$collection_date', '$ip_comment', '$doi', '$source_db_name', '$source_db_id', '$comments');");
+  $sth->execute();
+  $sth->bind_col(1, \$annotated_image_id);
+  $sth->fetch();
+
 
   my @keywords 		= split(/\|/, trim($image_data_fields[2]));
   my @ontology_term_ids = split(/\|/, trim($image_data_fields[3]));
diff --git a/image_annotation_db/ia_upload/stored_procedures.sql b/image_annotation_db/ia_upload/stored_procedures.sql
index 513f10c..acacc1a 100644
--- a/image_annotation_db/ia_upload/stored_procedures.sql
+++ b/image_annotation_db/ia_upload/stored_procedures.sql
@@ -39,4 +39,12 @@ insert into taxon (species_id, species_name, genus) values (species_id, species_
 select last_insert_id();
 end //
 
+-- create(drop and create if exists) stored procedure for importing data into annotated_image table
 
+DROP procedure if exists import_annotated_image;
+delimiter //
+create procedure import_annotated_image (curator_id int(11), taxon_id int(11), image_path varchar(255), collection_location varchar(255), collection_date date, ip_comment varchar(255), doi varchar(45), source_db_name varchar(45), source_db_id varchar(30), comments varchar(255))
+begin
+insert into annotated_image (curator_id, taxon_id, image_path, collection_location, collection_date, ip_comment, doi, source_db_name, source_db_id, comments) values (curator_id, taxon_id, image_path, collection_location, collection_date, ip_comment, doi, source_db_name, source_db_id, comments);
+select last_insert_id();
+end //
\ No newline at end of file
-- 
2.34.1