X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=kmig.d%2Fbin%2Fmig-export;h=474958cca64e6e74706b4d96e1f9059a77787e4e;hp=01d92485e4b49a1fde3399d9008ef79b2fd935be;hb=HEAD;hpb=10a8da64cef5c1408e076905cbe090375ef7dd9a

diff --git a/kmig.d/bin/mig-export b/kmig.d/bin/mig-export
index 01d9248..474958c 100755
--- a/kmig.d/bin/mig-export
+++ b/kmig.d/bin/mig-export
@@ -27,7 +27,7 @@ $dbh->{mysql_enable_utf8mb4} = 1;
 
 my @taglist = @ARGV;
 my $arg_list_length = scalar @taglist;
-if($arg_list_length < 1) { @taglist = ("authorisedvalues","borrowerattributes","calendar","circrules","itemtypes","libraries","patrontypes","preferences","reports","smsproviders"); } 
+if($arg_list_length < 1) { @taglist = ("authorisedvalues","booksellers","budgets","borrowerattributes","calendar","circrules","itemtypes","letters","libraries","messages","patrontypes","preferences","reports","smsproviders"); } 
 $MIGGITDIR =~ s/\/\//\//;
 
 my $timestamp = create_timestamp();
@@ -39,11 +39,21 @@ foreach my $backup (@taglist) {
         print "Backing up $backupfile ... \n";
         backup_authorisedvalues($dbh,$backupfile);
     }
+    if ($backup eq 'booksellers') {
+        $backupfile = $MIGGITDIR . 'booksellers' . '.' . $timestamp . '.xml';
+        print "Backing up $backupfile ... \n";
+        backup_booksellers($dbh,$backupfile);
+    }
     if ($backup eq 'borrowerattributes') {
-        $backupfile = $MIGGITDIR . 'borrower_attributes' . '.' . $timestamp . '.xml';
+        $backupfile = $MIGGITDIR . 'borrowerattributes' . '.' . $timestamp . '.xml';
         print "Backing up $backupfile ... \n";
 	    backup_borrower_attributes($dbh,$backupfile);
     }
+    if ($backup eq 'budgets') {
+        $backupfile = $MIGGITDIR . 'budgets' . '.' . $timestamp . '.xml';
+        print "Backing up $backupfile ... \n";
+        backup_budgets($dbh,$backupfile);
+    }
     if ($backup eq 'calendar') {
         $backupfile = $MIGGITDIR . 'calendar' . '.' . $timestamp . '.xml';
         print "Backing up $backupfile ... \n";
@@ -59,11 +69,21 @@ foreach my $backup (@taglist) {
         print "Backing up $backupfile ... \n";
         backup_itemtypes($dbh,$backupfile);
     }
+    if ($backup eq 'letters') {
+        $backupfile = $MIGGITDIR . 'letters' . '.' . $timestamp . '.xml';
+        print "Backing up $backupfile ... \n";
+        backup_letters($dbh,$backupfile);
+    }
     if ($backup eq 'libraries') {
         $backupfile = $MIGGITDIR . 'libraries' . '.' . $timestamp . '.xml';
         print "Backing up $backupfile ... \n";
         backup_libraries($dbh,$backupfile);
     }
+    if ($backup eq 'messages') {
+        $backupfile = $MIGGITDIR . 'messages' . '.' . $timestamp . '.xml';
+        print "Backing up $backupfile ... \n";
+        backup_messages($dbh,$backupfile);
+    }
     if ($backup eq 'patrontypes') {
         $backupfile = $MIGGITDIR . 'patrontypes' . '.' . $timestamp . '.xml';
         print "Backing up $backupfile ... \n";
@@ -93,10 +113,10 @@ sub backup_authorisedvalues {
     open(my $fh, '>', $backupfile) or die "Could not open $backupfile!";
     my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, );
     $xml->xmlDecl('UTF-8');
+    $xml->startTag('document');
+
     $xml->startTag('authorisedvalues');
-    my $query = "SELECT category,authorised_value,lib,lib_opac,imageurl FROM authorised_values WHERE category != 'ITEMTYPECAT'";
-    my $sth = $dbh->prepare($query);
-    $sth->execute();
+    my $sth = sql_giveback($dbh,"SELECT category,authorised_value,lib,lib_opac,imageurl FROM authorised_values WHERE category != 'ITEMTYPECAT'");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('value');
         $xml->dataElement('category', $row[0]); 
@@ -107,6 +127,61 @@ sub backup_authorisedvalues {
         $xml->endTag('value');        
     }
     $xml->endTag('authorisedvalues');
+
+    $xml->startTag('categories');
+    $sth = sql_giveback($dbh,"SELECT category_name FROM authorised_value_categories WHERE category_name != 'ITEMTYPECAT'");
+    while (my @row = $sth->fetchrow_array) {
+        $xml->startTag('value');
+        $xml->dataElement('name', $row[0]);
+        $xml->endTag('value');
+    }
+    $xml->endTag('categories');
+
+    $xml->endTag('document');
+    $xml->end();
+    close $fh;
+    return;
+}
+
+sub backup_booksellers {
+    my $dbh = shift;
+    my $backupfile = shift;
+
+    open(my $fh, '>', $backupfile) or die "Could not open $backupfile!";
+    my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, );
+    $xml->xmlDecl('UTF-8');
+    $xml->startTag('booksellers');
+    my $sth = sql_giveback($dbh,"SELECT name,address1,address2,address3,address4,phone,accountnumber,othersupplier,currency,booksellerfax,notes,bookselleremail,booksellerurl,postal,url,active,listprice,invoiceprice,gstreg,listincgst,invoiceincgst,tax_rate,discount,fax,deliverytime FROM aqbooksellers");
+    while (my @row = $sth->fetchrow_array) {
+        $xml->startTag('value');
+        $xml->dataElement('name', $row[1]);
+        $xml->dataElement('address1', $row[2]);
+        $xml->dataElement('address2', $row[3]);
+        $xml->dataElement('address3', $row[4]);
+        $xml->dataElement('address4', $row[5]);
+        $xml->dataElement('phone', $row[6]);
+        $xml->dataElement('accountnumber', $row[7]);
+        $xml->dataElement('othersupplier', $row[8]);
+        $xml->dataElement('currency', $row[9]);
+        $xml->dataElement('booksellerfax', $row[10]);
+        $xml->dataElement('notes', $row[11]);
+        $xml->dataElement('bookselleremail', $row[12]);
+        $xml->dataElement('booksellerurl', $row[13]);
+        $xml->dataElement('postal', $row[14]);
+        $xml->dataElement('url', $row[15]);
+        $xml->dataElement('active', $row[16]);
+        $xml->dataElement('listprice', $row[17]);
+        $xml->dataElement('invoiceprice ', $row[18]);
+        $xml->dataElement('gstreg', $row[19]);
+        $xml->dataElement('listincgst', $row[20]);
+        $xml->dataElement('invoiceincgst', $row[21]);
+        $xml->dataElement('tax_rate', $row[22]);
+        $xml->dataElement('discount', $row[23]);
+        $xml->dataElement('fax ', $row[24]);
+        $xml->dataElement('deliverytime', $row[25]);
+        $xml->endTag('value');
+    }
+    $xml->endTag('booksellers');
     $xml->end();
     close $fh;
     return;
@@ -122,9 +197,7 @@ sub backup_borrower_attributes {
     $xml->startTag('document');
 
     $xml->startTag('borrower_attribute_types');
-    my $query = "SELECT code,description,repeatable,opac_display,opac_editable,staff_searchable,authorised_value_category,display_checkout,category_code,class FROM borrower_attribute_types";
-    my $sth = $dbh->prepare($query);
-    $sth->execute();
+    my $sth = sql_giveback($dbh,"SELECT code,description,repeatable,opac_display,opac_editable,staff_searchable,authorised_value_category,display_checkout,category_code,class FROM borrower_attribute_types");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('value');
         $xml->dataElement('code', $row[0]);
@@ -141,18 +214,14 @@ sub backup_borrower_attributes {
     $xml->endTag('borrower_attribute_types');
 
     $xml->startTag('authorised_value_categories');
-    $query = "SELECT category_name FROM authorised_value_categories WHERE category_name IN (select category_name from borrower_attribute_types)";
-    $sth = $dbh->prepare($query);
-    $sth->execute();
+    $sth = sql_giveback($dbh,"SELECT category_name FROM authorised_value_categories WHERE category_name IN (select category_name from borrower_attribute_types)");
     while (my @row = $sth->fetchrow_array) {
         $xml->dataElement('value', $row[0]);
     }
     $xml->endTag('authorised_value_categories');
 
     $xml->startTag('authorised_values');
-    $query = "SELECT category,authorised_value,lib,lib_opac,imageurl FROM authorised_values where category in (select category_code from borrower_attribute_types);";
-    $sth = $dbh->prepare($query);
-    $sth->execute();
+    $sth = sql_giveback($dbh,"SELECT category,authorised_value,lib,lib_opac,imageurl FROM authorised_values where category in (select category_code from borrower_attribute_types);");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('value');
         $xml->dataElement('category', $row[0]);
@@ -170,6 +239,67 @@ sub backup_borrower_attributes {
     return;
 }
 
+sub backup_budgets {
+    my $dbh = shift;
+    my $backupfile = shift;
+
+    open(my $fh, '>', $backupfile) or die "Could not open $backupfile!";
+    my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, );
+    $xml->xmlDecl('UTF-8');
+    $xml->startTag('document');
+
+    $xml->startTag('budgets');
+    my $sth = sql_giveback($dbh,'SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME = "aqbudgets"');
+    my @ai = $sth->fetchrow_array;
+    $xml->dataElement('autoincrement', $ai[0]);
+    $sth = sql_giveback($dbh,"SELECT budget_id,budget_parent_id,budget_code,budget_name,budget_branchcode,budget_amount,budget_encumb,budget_expend,budget_notes,timestamp,budget_period_id,sort1_authcat,sort2_authcat,budget_owner_id,budget_permission FROM aqbudgets");
+    while (my @row = $sth->fetchrow_array) {
+        $xml->startTag('value');
+        $xml->dataElement('budget_id', $row[0]);
+        $xml->dataElement('budget_parent_id', $row[1]);
+        $xml->dataElement('budget_code', $row[2]);
+        $xml->dataElement('budget_name', $row[3]);
+        $xml->dataElement('budget_branchcode', $row[4]);
+        $xml->dataElement('budget_amount', $row[5]);
+        $xml->dataElement('budget_encumb', $row[6]);
+        $xml->dataElement('budget_expend', $row[7]);
+        $xml->dataElement('budget_notes', $row[8]);
+        $xml->dataElement('timestamp', $row[9]);
+        $xml->dataElement('budget_period_id', $row[10]);
+        $xml->dataElement('sort1_authcat', $row[11]);
+        $xml->dataElement('sort2_authcat', $row[12]);
+        $xml->dataElement('budget_owner_id', $row[13]);
+        $xml->dataElement('budget_permission', $row[14]);
+        $xml->endTag('value');
+    }
+    $xml->endTag('budgets');
+
+    $xml->startTag('budgetperiods');
+    $sth = sql_giveback($dbh,'SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME = "aqbudgetperiods"');
+    @ai = $sth->fetchrow_array;
+    $xml->dataElement('autoincrement', $ai[0]);
+    $sth = sql_giveback($dbh,"SELECT budget_period_id,budget_period_startdate,budget_period_enddate,budget_period_active,budget_period_description,budget_period_total,budget_period_locked,sort1_authcat,sort2_authcat FROM aqbudgetperiods");
+    while (my @row = $sth->fetchrow_array) {
+        $xml->startTag('value');
+        $xml->dataElement('budget_period_id', $row[0]);
+        $xml->dataElement('budget_period_startdate', $row[1]);
+        $xml->dataElement('budget_period_enddate', $row[2]);
+        $xml->dataElement('budget_period_active', $row[3]);
+        $xml->dataElement('budget_period_description', $row[4]);
+        $xml->dataElement('budget_period_total', $row[5]);
+        $xml->dataElement('budget_period_locked', $row[6]);
+        $xml->dataElement('sort1_authcat', $row[7]);
+        $xml->dataElement('sort2_authcat', $row[8]);
+        $xml->endTag('value');
+    }
+    $xml->endTag('budgetperiods');
+
+    $xml->endTag('document');
+    $xml->end();
+    close $fh;
+    return;
+}
+
 sub backup_calendar {
     my $dbh = shift;
     my $backupfile = shift;
@@ -179,9 +309,7 @@ sub backup_calendar {
     $xml->xmlDecl('UTF-8');
     $xml->startTag('holidays');
 
-    my $query = "SELECT branchcode,weekday,day,month,title,description FROM repeatable_holidays";
-    my $sth = $dbh->prepare($query);
-    $sth->execute();
+    my $sth = sql_giveback($dbh,"SELECT branchcode,weekday,day,month,title,description FROM repeatable_holidays");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('repeatable');
         $xml->dataElement('branchcode',$row[0]);
@@ -193,9 +321,7 @@ sub backup_calendar {
         $xml->endTag('repeatable');
     }
 
-    $query = "SELECT branchcode,day,month,year,isexception,title,description FROM special_holidays";
-    $sth = $dbh->prepare($query);
-    $sth->execute();
+    $sth = sql_giveback($dbh,"SELECT branchcode,day,month,year,isexception,title,description FROM special_holidays");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('special');
         $xml->dataElement('branchcode',$row[0]);
@@ -224,9 +350,7 @@ sub backup_circrules {
     $xml->startTag('document');
 
     $xml->startTag('circ');
-    my $query = "SELECT branchcode, categorycode, itemtype, rule_name, rule_value FROM circulation_rules";
-    my $sth = $dbh->prepare($query);
-    $sth->execute();
+    my $sth = sql_giveback($dbh,"SELECT branchcode, categorycode, itemtype, rule_name, rule_value FROM circulation_rules");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('rule');
         $xml->dataElement('branchcode', $row[0]);
@@ -239,9 +363,7 @@ sub backup_circrules {
     $xml->endTag('circ');
 
     $xml->startTag('issuing');
-    $query = "SELECT categorycode,itemtype,restrictedtype,rentaldiscount,reservecharge,fine,finedays,maxsuspensiondays,suspension_chargeperiod,firstremind,chargeperiod,chargeperiod_charge_at,accountsent,issuelength,lengthunit,hardduedate,hardduedatecompare,renewalsallowed,renewalperiod,norenewalbefore,auto_renew,no_auto_renewal_after,no_auto_renewal_after_hard_limit,reservesallowed,holds_per_record,holds_per_day,branchcode,overduefinescap,cap_fine_to_replacement_price,onshelfholds,opacitemholds,article_requests,note FROM issuingrules";
-    $sth = $dbh->prepare($query);
-    $sth->execute();
+    $sth = sql_giveback($dbh,"SELECT categorycode,itemtype,restrictedtype,rentaldiscount,reservecharge,fine,finedays,maxsuspensiondays,suspension_chargeperiod,firstremind,chargeperiod,chargeperiod_charge_at,accountsent,issuelength,lengthunit,hardduedate,hardduedatecompare,renewalsallowed,renewalperiod,norenewalbefore,auto_renew,no_auto_renewal_after,no_auto_renewal_after_hard_limit,reservesallowed,holds_per_record,holds_per_day,branchcode,overduefinescap,cap_fine_to_replacement_price,onshelfholds,opacitemholds,article_requests,note FROM issuingrules");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('rule');
         $xml->dataElement('categorycode', $row[0]);
@@ -298,9 +420,7 @@ sub backup_itemtypes {
     $xml->startTag('document');
 
     $xml->startTag('itemtypes');
-    my $query = "SELECT itemtype, description, rentalcharge, rentalcharge_daily, rentalcharge_hourly, defaultreplacecost, processfee, notforloan, imageurl, summary, checkinmsg, checkinmsgtype, sip_media_type, hideinopac, searchcategory FROM itemtypes";
-    my $sth = $dbh->prepare($query);
-    $sth->execute();
+    my $sth = sql_giveback($dbh,"SELECT itemtype, description, rentalcharge, rentalcharge_daily, rentalcharge_hourly, defaultreplacecost, processfee, notforloan, imageurl, summary, checkinmsg, checkinmsgtype, sip_media_type, hideinopac, searchcategory FROM itemtypes");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('value');
         $xml->dataElement('itemtype', $row[0]);
@@ -323,9 +443,7 @@ sub backup_itemtypes {
     $xml->endTag('itemtypes');
 
     $xml->startTag('authorised_values');
-    $query = "SELECT category,authorised_value,lib,lib_opac,imageurl FROM authorised_values where category = 'ITEMTYPECAT';";
-    $sth = $dbh->prepare($query);
-    $sth->execute();
+    $sth = sql_giveback($dbh,"SELECT category,authorised_value,lib,lib_opac,imageurl FROM authorised_values where category = 'ITEMTYPECAT';");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('value');
         $xml->dataElement('category', $row[0]);
@@ -338,9 +456,7 @@ sub backup_itemtypes {
     $xml->endTag('authorised_values');
 
     $xml->startTag('localizations');
-    $query = "SELECT entity, code, lang, translation FROM localization WHERE entity = 'itemtypes';";
-    $sth = $dbh->prepare($query);
-    $sth->execute();
+    $sth = sql_giveback($dbh,"SELECT entity, code, lang, translation FROM localization WHERE entity = 'itemtypes';");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('value');
         $xml->dataElement('entity', $row[0]);
@@ -357,6 +473,36 @@ sub backup_itemtypes {
     return;
 }
 
+sub backup_letters {
+    my $dbh = shift;
+    my $backupfile = shift;
+
+    open(my $fh, '>', $backupfile) or die "Could not open $backupfile!";
+    my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, );
+    $xml->xmlDecl('UTF-8');
+    $xml->startTag('letters');
+
+    my $sth = sql_giveback($dbh,"SELECT module,code,branchcode,name,is_html,title,content,message_transport_type,lang FROM letter");
+    while (my @row = $sth->fetchrow_array) {
+        $xml->startTag('letter');
+        $xml->dataElement('module',$row[0]);
+        $xml->dataElement('code',$row[1]);
+        $xml->dataElement('branchcode',$row[2]);
+        $xml->dataElement('name',$row[3]);
+        $xml->dataElement('is_html',$row[4]);
+        $xml->dataElement('title',$row[5]);
+        $xml->dataElement('content',$row[6]);
+        $xml->dataElement('message_transport_type',$row[7]);
+        $xml->dataElement('lang',$row[8]);
+        $xml->endTag('letter');
+    }
+
+    $xml->endTag('letters');
+    $xml->end();
+    close $fh;
+    return;
+}
+
 sub backup_libraries {
     my $dbh = shift;
     my $backupfile = shift;
@@ -366,9 +512,7 @@ sub backup_libraries {
     $xml->xmlDecl('UTF-8');
     $xml->startTag('libraries');
 
-    my $query = "SELECT id,parent_id,branchcode,title,description,ft_hide_patron_info,ft_search_groups_opac,ft_search_groups_staff FROM library_groups";
-    my $sth = $dbh->prepare($query);
-    $sth->execute();
+    my $sth = sql_giveback($dbh,"SELECT id,parent_id,branchcode,title,description,ft_hide_patron_info,ft_search_groups_opac,ft_search_groups_staff FROM library_groups");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('library_group');
         $xml->dataElement('id',$row[0]);
@@ -382,9 +526,7 @@ sub backup_libraries {
         $xml->endTag('library_group');
     }
 
-    $query = "SELECT branchcode,branchname,branchaddress1,branchaddress2,branchaddress3,branchzip,branchcity,branchstate,branchcountry,branchphone,branchfax,branchemail,branchreplyto,branchreturnpath,branchurl,issuing,branchip,branchprinter,branchnotes,opac_info,geolocation,marcorgcode,pickup_location FROM branches";
-    $sth = $dbh->prepare($query);
-    $sth->execute();
+    $sth = sql_giveback($dbh,"SELECT branchcode,branchname,branchaddress1,branchaddress2,branchaddress3,branchzip,branchcity,branchstate,branchcountry,branchphone,branchfax,branchemail,branchreplyto,branchreturnpath,branchurl,issuing,branchip,branchprinter,branchnotes,opac_info,geolocation,marcorgcode,pickup_location FROM branches");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('library');
         $xml->dataElement('branchcode',$row[0]);
@@ -419,6 +561,58 @@ sub backup_libraries {
     return;
 }
 
+sub backup_messages {
+    my $dbh = shift;
+    my $backupfile = shift;
+
+    open(my $fh, '>', $backupfile) or die "Could not open $backupfile!";
+    my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, );
+    $xml->xmlDecl('UTF-8');
+    $xml->startTag('document');
+
+    $xml->startTag('message_attributes');
+    my $sth = sql_giveback($dbh,'SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME = "message_attributes"');
+    my @ai = $sth->fetchrow_array;
+    $xml->dataElement('autoincrement', $ai[0]);
+    $sth = sql_giveback($dbh,"SELECT message_attribute_id, message_name, takes_days FROM message_attributes");
+    while (my @row = $sth->fetchrow_array) {
+        $xml->startTag('value');
+        $xml->dataElement('message_attribute_id', $row[0]);
+        $xml->dataElement('message_name', $row[1]);
+        $xml->dataElement('takes_days', $row[2]);
+        $xml->endTag('value');
+    }
+    $xml->endTag('message_attributes');
+
+    $xml->startTag('message_transport_types');
+    $sth = sql_giveback($dbh,"SELECT message_transport_type FROM message_transport_types");
+    while (my @row = $sth->fetchrow_array) {
+        $xml->startTag('value');
+        $xml->dataElement('message_transport_type', $row[0]);
+        $xml->endTag('value');
+    }
+    $xml->endTag('message_transport_types');
+
+    $xml->startTag('message_transports');
+    $sth = sql_giveback($dbh,"SELECT message_attribute_id, message_transport_type, is_digest, letter_module, letter_code, branchcode FROM message_transports");
+    while (my @row = $sth->fetchrow_array) {
+        $xml->startTag('value');
+        $xml->dataElement('message_attribute_id', $row[0]);
+        $xml->dataElement('message_transport_type', $row[1]);
+        $xml->dataElement('is_digest', $row[2]);
+        $xml->dataElement('letter_module', $row[3]);
+        $xml->dataElement('letter_code', $row[4]);
+        $xml->dataElement('branchcode', $row[5]);
+        $xml->endTag('value');
+    }
+    $xml->endTag('message_transports');
+
+    $xml->endTag('document');
+    $xml->end();
+    close $fh;
+    return;
+}
+
 sub backup_patrontypes {
     my $dbh = shift;
     my $backupfile = shift;
@@ -427,9 +621,7 @@ sub backup_patrontypes {
     my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, );
     $xml->xmlDecl('UTF-8');
     $xml->startTag('patron');
-    my $query = "SELECT categorycode,description,enrolmentperiod,enrolmentperioddate,upperagelimit,dateofbirthrequired,finetype,bulk,enrolmentfee,overduenoticerequired,issuelimit,reservefee,hidelostitems,category_type,BlockExpiredPatronOpacActions,default_privacy,checkprevcheckout,reset_password,change_password FROM categories";
-    my $sth = $dbh->prepare($query);
-    $sth->execute();
+    my $sth = sql_giveback($dbh,"SELECT categorycode,description,enrolmentperiod,enrolmentperioddate,upperagelimit,dateofbirthrequired,finetype,bulk,enrolmentfee,overduenoticerequired,issuelimit,reservefee,hidelostitems,category_type,BlockExpiredPatronOpacActions,default_privacy,checkprevcheckout,reset_password,change_password FROM categories");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('category');
         $xml->dataElement('categorycode',$row[0]);
@@ -467,9 +659,7 @@ sub backup_preferences {
     my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, );
     $xml->xmlDecl('UTF-8');
     $xml->startTag('preferences');
-    my $query = "SELECT variable,value FROM systempreferences WHERE value != '' AND value IS NOT NULL";
-    my $sth = $dbh->prepare($query);
-    $sth->execute();
+    my $sth = sql_giveback($dbh,"SELECT variable,value FROM systempreferences WHERE value != '' AND value IS NOT NULL");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('pref');
         $xml->dataElement('variable',$row[0]);
@@ -491,9 +681,7 @@ sub backup_reports {
     $xml->xmlDecl('UTF-8');
     $xml->startTag('reports');
 
-    my $query = "SELECT s.date_created, s.last_modified, s.savedsql, s.report_name, s.type, s.notes, s.cache_expiry, s.public, s.report_area, s.report_group, s.report_subgroup, b.userid FROM saved_sql s LEFT JOIN borrowers b ON b.borrowernumber = s.borrowernumber";
-    my $sth = $dbh->prepare($query);
-    $sth->execute();
+    my $sth = sql_giveback($dbh,"SELECT s.date_created, s.last_modified, s.savedsql, s.report_name, s.type, s.notes, s.cache_expiry, s.public, s.report_area, s.report_group, s.report_subgroup, b.userid FROM saved_sql s LEFT JOIN borrowers b ON b.borrowernumber = s.borrowernumber");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('sqlreport');
         $xml->dataElement('date_created',$row[0]);
@@ -525,9 +713,7 @@ sub backup_smsproviders {
     my $xml = XML::Writer->new(OUTPUT => $fh, DATA_MODE => 1, DATA_INDENT => 2, );
     $xml->xmlDecl('UTF-8');
     $xml->startTag('sms');
-    my $query = "SELECT name,domain FROM sms_providers";
-    my $sth = $dbh->prepare($query);
-    $sth->execute();
+    my $sth = sql_giveback($dbh,"SELECT name,domain FROM sms_providers");
     while (my @row = $sth->fetchrow_array) {
         $xml->startTag('provider');
         $xml->dataElement('name',$row[0]);
@@ -553,6 +739,22 @@ sub create_timestamp {
     return $str;
 }
 
+sub sql_noresult {
+    my $dbh = shift;
+    my $statement = shift;
+    my $sth = $dbh->prepare($statement);
+    $sth->execute();
+    return;
+}
+   
+sub sql_giveback {
+    my $dbh = shift;
+    my $query = shift;
+    my $sth = $dbh->prepare($query);
+    $sth->execute();
+	return $sth;
+}
+
 sub abort {
     my $msg = shift;
     print STDERR "$0: $msg", "\n";