Index: /main/trunk/greenstone2/perllib/docprint.pm
===================================================================
--- /main/trunk/greenstone2/perllib/docprint.pm	(revision 32572)
+++ /main/trunk/greenstone2/perllib/docprint.pm	(revision 32573)
@@ -108,14 +108,23 @@
 }
 
+# pass by ref version: gets a ref to a string and returns ref to the modified string
+# so use this for large strings (fulltext)
+sub escape_textref {
+    my ($textref) = @_;
+    # special characters in the xml encoding
+    $$textref =~ s/&&/& &/g;
+    $$textref =~ s/&/&amp;/g; # this has to be first...
+    $$textref =~ s/</&lt;/g;
+    $$textref =~ s/>/&gt;/g;
+    $$textref =~ s/\"/&quot;/g;
+
+    return $textref;
+}
+
+# pass by value - the behaviour of the escape_text method used so far is to pass copies of strings
 sub escape_text {
     my ($text) = @_;
-    # special characters in the xml encoding
-    $text =~ s/&&/& &/g;
-    $text =~ s/&/&amp;/g; # this has to be first...
-    $text =~ s/</&lt;/g;
-    $text =~ s/>/&gt;/g;
-    $text =~ s/\"/&quot;/g;
-
-    return $text;
+    my $textref = &escape_textref(\$text);
+    return $$textref;
 }
 
Index: /main/trunk/greenstone2/perllib/gssql.pm
===================================================================
--- /main/trunk/greenstone2/perllib/gssql.pm	(revision 32572)
+++ /main/trunk/greenstone2/perllib/gssql.pm	(revision 32573)
@@ -270,9 +270,4 @@
     # NO: "When all the data has been fetched from a SELECT statement, the driver will automatically call finish for you. So you should not call it explicitly except when you know that you've not fetched all the data from a statement handle and the handle won't be destroyed soon."
     
-    #$meta_sth = $self->{'metadata_prepared_insert_statement_handle'};
-    #$txt_sth = $self->{'fulltxt_prepared_insert_statement_handle'};
-    #$meta_sth->finish() if($meta_sth);
-    #$txt_sth->finish() if($txt_sth);
-
     print STDERR "Disconnecting from database\n" if($self->{'verbosity'} > 1);
     
@@ -372,38 +367,76 @@
 # https://metacpan.org/pod/DBI#performance
 # 'The q{...} style quoting used in this example avoids clashing with quotes that may be used in the SQL statement. Use the double-quote like qq{...} operator if you want to interpolate variables into the string. See "Quote and Quote-like Operators" in perlop for more details.'
-sub prepare_insert_metadata_row_stmthandle {
-    my $self = shift (@_);    
+#
+# This method uses lazy loading to prepare the SQL insert stmt once for a table and store it,
+# then execute the (stored) statement each time it's needed for that table.
+sub insert_row_into_metadata_table {
+    my $self = shift (@_);
+    my ($doc_oid, $section_name, $meta_name, $escaped_meta_value, $debug_only, $debug_out) = @_;
+    
     #my ($did, $sid, $metaname, $metavalue) = @_;
     my $dbh = $self->{'db_handle'};
-    
-    my $tablename = $self->get_metadata_table_name();
-
-    #my $stmt = "INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES ('$did', '$sid', '$metaname', '$metavalue');"; # ?, ?, ?, ?
-
-    # using qq{} since we want $tablename placeholder to be filled in
-    # returns Statement Handle object!
-    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n");
-
-    print STDERR "@@@@ Prepared meta insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2);
-    
-    return $sth;
-}
-
-sub prepare_insert_fulltxt_row_stmthandle {
+
+    my $sth = $self->{"sth_insert_into_meta_table"};
+    if(!$sth) {
+	my $tablename = $self->get_metadata_table_name();
+	
+	#my $stmt = "INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES ('$did', '$sid', '$metaname', '$metavalue');"; # ?, ?, ?, ?
+	
+	# using qq{} since we want $tablename placeholder to be filled in
+	# returns Statement Handle object!
+	$sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n");
+
+	print STDERR "@@@@ Prepared meta insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2);
+	
+	$self->{"sth_insert_into_meta_table"} = $sth; # store it for future use
+    }
+
+    # Now we're ready to execute the command, unless we're only debugging
+    
+    if($debug_only) {
+	# just print the statement we were going to execute
+	
+	print $debug_out $sth->{'Statement'} . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n";
+    } else {
+	$sth->execute($doc_oid, $section_name, $meta_name, $escaped_meta_value)
+	    || warn ("Unable to write metadata row to db:\n\tOID $doc_oid, section $section_name,\n\tmeta name: $meta_name, val: $escaped_meta_value");
+	# Execution failure will print out info anyway: since db connection sets PrintError
+    }
+}
+
+# As above. Likewise uses lazy loading to prepare the SQL insert stmt once for a table and store it,
+# then execute the (stored) statement each time it's needed for that table.
+sub insert_row_into_fulltxt_table {
     my $self = shift (@_);
     #my ($did, $sid, $fulltext) = @_;
-    my $dbh = $self->{'db_handle'};
-    
-    my $tablename = $self->get_fulltext_table_name();
-
-    #my $stmt = "INSERT INTO $tablename (did, sid, fulltxt) VALUES ('$did', '$sid', '$fulltext');"; ?, ?, ?
-
-    # using qq{} since we want $tablename placeholder to be filled in
-    # returns Statement Handle object!
-    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n");
-    
-    print STDERR "@@@@ Prepared fulltext insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2);
-    
-    return $sth;
+    my ($doc_oid, $section_name, $section_textref, $debug_only, $debug_out) = @_;
+    
+    my $dbh = $self->{'db_handle'};
+    
+    my $sth = $self->{"sth_insert_into_fulltxt_table"};
+    if(!$sth) {
+	my $tablename = $self->get_fulltext_table_name();
+
+	#my $stmt = "INSERT INTO $tablename (did, sid, fulltxt) VALUES ('$did', '$sid', '$fulltext');"; ?, ?, ?
+	
+	# using qq{} since we want $tablename placeholder to be filled in
+	# returns Statement Handle object!
+	$sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n");
+	
+	print STDERR "@@@@ Prepared fulltext insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2);
+
+	$self->{"sth_insert_into_fulltxt_table"} = $sth; # store it for future use
+    }
+
+    # Now we're ready to execute the command, unless we're only debugging
+
+    if($debug_only) {
+	# just print the statement we were going to execute, minus the fulltxt value
+	my $txt_repr = $$section_textref ? "<TXT>" : "NULL";
+	print $debug_out $sth->{'Statement'} . "($doc_oid, $section_name, $txt_repr)\n";
+    } else {
+	$sth->execute($doc_oid, $section_name, $$section_textref)
+	    || warn ("Unable to write fulltxt row to db for row:\n\tOID $doc_oid, section $section_name");
+    }
 }
 
Index: /main/trunk/greenstone2/perllib/plugouts/GreenstoneSQLPlugout.pm
===================================================================
--- /main/trunk/greenstone2/perllib/plugouts/GreenstoneSQLPlugout.pm	(revision 32572)
+++ /main/trunk/greenstone2/perllib/plugouts/GreenstoneSQLPlugout.pm	(revision 32573)
@@ -177,5 +177,5 @@
 	$success = $gs_sql->ensure_fulltxt_table_exists();
     }
-    #if(!$gs_sql->load_db_and_tables($db_name, $proc_mode)) {
+    
     if(!$success) {	
 	# This is fatal for the plugout, let's terminate here after disconnecting again
@@ -186,19 +186,7 @@
     }
 
-    # prepare the shared/common HANDLES to SQL insert statements that contain placeholders
-    # and which we will reuse repeatedly when actually executing the insert statements
-    
-    if($proc_mode eq "all" || $proc_mode eq "meta_only" ) {
-	$self->{'metadata_prepared_insert_statement_handle'} = $gs_sql->prepare_insert_metadata_row_stmthandle();
-    }
-    if($proc_mode eq "all" || $proc_mode eq "text_only" ) {
-	$self->{'fulltxt_prepared_insert_statement_handle'} = $gs_sql->prepare_insert_fulltxt_row_stmthandle();
-    }
-
     # store the DBI wrapper instance
     $self->{'gs_sql'} = $gs_sql;
     
-    ##print STDERR "#### Meta stmt: " . $self->{'metadata_prepared_insert_statement_handle'}->{'Statement'} . "\n";
-    ##print STDERR "#### Full stmt: " . $self->{'fulltxt_prepared_insert_statement_handle'}->{'Statement'} . "\n";
     
     # if setting up to work with sql db failed, we'd have terminated and wouldn't come up to here:
@@ -300,9 +288,5 @@
     my $root_section = $doc_obj->get_top_section();
 
-    # load the prepared INSERT statement handles for both tables (can be undef for any table depending on whether meta_only or txt_only are set)
-    my $metadata_table_sth = $self->{'metadata_prepared_insert_statement_handle'};
-    my $fulltxt_table_sth = $self->{'fulltxt_prepared_insert_statement_handle'};
-    
-    $self->recursive_write_meta_and_text($doc_obj, $doc_oid, $root_section, $metadata_table_sth, $fulltxt_table_sth);
+    $self->recursive_write_meta_and_text($doc_obj, $doc_oid, $root_section);
 }
 
@@ -311,5 +295,5 @@
 sub recursive_write_meta_and_text {
     my $self = shift (@_);
-    my ($doc_obj, $doc_oid, $section, $metadata_table_sth, $fulltxt_table_sth) = @_;    
+    my ($doc_obj, $doc_oid, $section) = @_;    
 
     # If section=ROOT, write "root" as section name into table
@@ -321,10 +305,8 @@
 
     my $debug_out = $self->{'debug_outhandle'};
-#    print STDERR "#### Meta stmt: " . $metadata_table_sth->{'Statement'} . "\n";
-#    print STDERR "#### Full stmt: " . $fulltxt_table_sth->{'Statement'} . "\n";
-    
-    #my $proc_mode = $self->{'process_mode'};
-    #if($proc_mode eq "all" || $proc_mode eq "meta_only" ) {
-    if($metadata_table_sth) { # meta insert statement handle will be undef if not writing meta
+
+    my $gs_sql = $self->{'gs_sql'};
+    my $proc_mode = $self->{'process_mode'};
+    if($proc_mode eq "all" || $proc_mode eq "meta_only" ) {
 	
 	foreach my $data (@{$section_ptr->{'metadata'}}) {
@@ -336,43 +318,30 @@
 	    # Write out the current section's meta to collection db's METADATA table	    
 	    
-	    # for each set of values to write to meta table, execute the prepared statement, filling in the values
-
-	    if($self->{'debug'}) {
-		# just print the statement we were going to execute
-
-		print $debug_out $metadata_table_sth->{'Statement'} . "($doc_oid, $section_name, $meta_name, $escaped_meta_value)\n";
+	    # For each set of values to write to meta table, this next method call will
+	    # efficiently execute an insert SQL statement (using a prepared insert statement), 
+	    # filling in the values
+	    # OR if debugging, then it will print the SQL insert statement but not execute it
+
+	    $gs_sql->insert_row_into_metadata_table($doc_oid, $section_name, $meta_name, $escaped_meta_value, $self->{'debug'}, $debug_out);	    
+
 	    }
-	    else {
-		
-		$metadata_table_sth->execute($doc_oid, $section_name, $meta_name, $escaped_meta_value)
-		    || warn ("Unable to write metadata row to db:\n\tOID $doc_oid, section $section_name,\n\tmeta name: $meta_name, val: $escaped_meta_value");
-		# Execution failure will print out info anyway: since db connection sets PrintError
-	    }
-	}
-    }
-    
-    #if($proc_mode eq "all" || $proc_mode eq "text_only" ) {
-    if($fulltxt_table_sth) { # fulltxt insert statement handle will be undef if not writing fulltxt
-
-	if($self->{'debug'}) {
-	    # just print the statement we were going to execute, minus the fulltxt value
-	    my $txt_repr = $section_ptr->{'text'} ? "<TXT>" : "NULL";
-	    print $debug_out $fulltxt_table_sth->{'Statement'} . "($doc_oid, $section_name, $txt_repr)\n";
-	} else {
-	    my $section_text = &docprint::escape_text($section_ptr->{'text'});
-	    
-	    # fulltxt column can be SQL NULL. undef value gets written out as NULL:
-	    # https://stackoverflow.com/questions/12708633/which-one-represents-null-undef-or-empty-string
-		
-	    # Write out the current section's text to collection db's FULLTeXT table
-	    $fulltxt_table_sth->execute($doc_oid, $section_name, $section_text)
-		|| warn ("Unable to write fulltxt row to db for row:\n\tOID $doc_oid, section $section_name");
-	    # Execution failure will print out info anyway: since db connection sets PrintError
-	}
+    }
+    
+    
+    if($proc_mode eq "all" || $proc_mode eq "text_only" ) {
+    
+	my $section_textref = &docprint::escape_textref(\$section_ptr->{'text'});
+	
+	# fulltxt column can be SQL NULL. undef value gets written out as NULL:
+	# https://stackoverflow.com/questions/12708633/which-one-represents-null-undef-or-empty-string
+	# The following will do the SQL insertion
+	# or if debug, the following will print the SQL insert stmt without executing it
+	$gs_sql->insert_row_into_fulltxt_table($doc_oid, $section_name, $section_textref, $self->{'debug'}, $debug_out);
+    
     }
     
     # output all subsections: RECURSIVE CALL
     foreach my $subsection (@{$section_ptr->{'subsection_order'}}) {
-	$self->recursive_write_meta_and_text($doc_obj, $doc_oid, "$section.$subsection", $metadata_table_sth, $fulltxt_table_sth);
+	$self->recursive_write_meta_and_text($doc_obj, $doc_oid, "$section.$subsection");
     }
 }
