MySQL database to excel spreadsheet report

[Sport Girl]

Hi again my great online assistants, just would like to say that i'm testing the script under unix , and after researches on the internet, i have realised that to resolve 500 Internal Server Error, we should put the script in cgi-bin directory, use of ASCII transfer mode, chmod 755 scriptname.cgi, and then login to unix and type ./scriptname.cgi.

I have connected to the database using mysql -u***** -p***** bugs
and i could have access to the database from mysql
I have done all of those but the internet page is always displaying 500 Internal Server Error.The server encountered an internal error or misconfiguration and was unable to complete your request.

and in unix i had the following:
Unrecognized character \x93 at ./perlcode1.cgi line 11.

But i think that the connection syntax is correct
what to do please?

Best Regards.

The code is the following:

#!/usr/bin/perl -W
use DBI;
use DBD::mysql;
use strict;
use File::Copy;
use File::Path;
use File::Basename;
use Spreadsheet::WriteExcel::Big;
use Data::Dumper;
 
my $worksheet = ['Report.xls'];
my $dbh = DBI->connect( "DBI:mysql:dbname=bugs:localhost", "carole", "carole", { RaiseError => 1, PrintError => 0, AutoCommit => 0 } )
    or die("Error:$DBI::errstr");
my $excel_file = "C:/Perl/Report.xls";
$query = qq{SELECT 
b.bug_id, b.bug_file_loc, b.bug_severity, b.bug_status,
b.creation_ts, b.delta_ts, b.short_desc, b.op_sys, 
b.priority, b.rep_plarform, b.version, b.component_id,
b.resolution, b.target_milestone, b.status_whiteboard, b.votes, 
b.keywords, b.last_diffed, b.everconfirmed, b.reporter_accessible,
b.cclist_accessible, b.estimated_time, b.remaining_time, b.deadline,
b.alias, pf.login_name, pd.name 
FROM bugs b, profiles pf, products pd
WHERE bugs.assigned_to = profiles.user_id
AND bugs.product_id = products.id
ORDER BY bug_id};
my $sth = $dbh->prepare($query);
$sth->execute() or die $dbh->errstr;
while ( my $ref = $sth->fetchrow_hashref() ) {
    print $ref ->{
        'bug_id', 'assigned_to', 'bug_file_loc', 'bug_severity',
        'bug_status', 'creation_ts', 'delta_ts', 'short_desc',
        'op_sys', 'priority', 'product_id', 'rep_plarform',
        'version', 'component_id', 'resolution', 'target_milestone',
        'status_whiteboard', 'votes', 'keywords', 'last_diffed',
        'everconfirmed', 'reporter_accessible', 'cclist_accessible', 'estimated_time',
        'remaining_time', 'deadline', 'alias', 'login_name',
        'name'
      },
      "\n";
}
# -e checks to see if the file exists
 
if (-e $excel_file) {
    #open Excel file
    my $Book = $Excel->Workbooks->Open($excel_file);
    $exists = "true";
 
} else {
    $Book   = $Excel->Workbooks->Open($excel_file);
    $exists = "true";
 
} else {
    $Book   = $Excel->Workbooks->Add();
    $exists = "false";
}
 
if ( $exists eq "true" ) {
    $previousSheet             = $Book->ActiveSheet;
    $currentSheet              = $Book->Worksheets->Add();
    $Book->ActiveSheet->{Name} = "Report";
 
} else {
    $previousSheet = 0;
}
 
#create the spreadsheet
$excel->output();
 
#sort data of worksheet – ASC or DESC
$excel->sort_data( "Report.xls", 0, "ASC" );
 
#add headers to 'Report'
$excel->set_headers( 'Report.xls', [qw/Bugs report/] );
 
#print sheet-names
print join( ", ", $excel->sheets() ), "\n";
 
#get the result as a string
my $spreadsheet = $excel->output_as_string();
 
#print result into a file and handle error
$excel->output_to_file("Report.xls") or die $excel->errstr();
if ( $exists eq "true" ) {
    $Book->Save();
 
} else {
    $Book->SaveAs("$excel_file") or die $!;
}
$sth->finish();
$dbh->disconnect();
 

[miller]

Hello Sport Girl,

Your code, as you originally posted it, contained a lot of unrecognizable characters. I'm guessing that they may be unicode or some other special character set, and that is leading to problems with running your script on the server. I suspect that the problem is either in the editor that you're using or the method in which you're transferring the file to your web server.

I've translated all the special character to their likely intended character in your above post. I've also run your code through Perl Tidy in order to clean up your formatting. You may now notice an error in your code where you have two "else" statements.

Anyway, I suggust that you figure out what is causing your character set issue before trying to do anything else. That is likely leading to problems throughout your code base, not just with this one script.

- Miller

[Sport Girl]

Hi Miller, thank u very much 4 trying to helping me. I appreciate it very much.

In fact i have done what you have told me and changed the character set to ASCII in my local machine where i am supposed to do the testing but the error 500 Internal Server Error is still persisting.
I don't know if the fact is that i am not given permissions or priveleges to change in the configuration as a trainee, so they do not take effects or it's just i can't test my new code for security reasons. I have talked to my consultant about it but....
Anyway i'll post the new code , the error on the internet page is: Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.

and on unix when trying to do ./scriptname.cgi is:
Unrecognized character \x93 at ./scriptname.cgi line 11.

I have done under unix "Setup- file transfer" and the following message appears:
Unable to transfer file: Host does not respond- Could not connect.

But I am logged in in unix with a username and password(i am using Reflection - Host-Unix and Digital) and i am connected in MySQL to the database( i am using Reflection - Reflection X) and i am connected to the FTP in a email address( i am using Reflection - FTP Client). I checked the ASCII character set in the Unix and FTP windows.

So could it be security or configuration reasons or what else should i do?

 
#!/usr/bin/perl -W
use DBI;
use DBD::mysql;
use strict;
use File::Copy;
use File::Path;
use File::Basename;
use Spreadsheet::WriteExcel::Big;
use Data::Dumper;
my $worksheet = ['Report.xls'];
my $dbh = DBI->connect(DBI:mysql:dbname=bugs:localhost, carole, carole, { RaiseError=>1, PrintError=>0, AutoCommit=>0}) or die(Error:DBI::errstr);
my $excel_file = ‘C:/Perl/Report.xls’;
$query =qq{SELECT
b.bug_id, b.bug_file_loc, b.bug_severity, b.bug_status, b.creation_ts, b.delta_ts, b.short_desc, b.op_sys, b.priority, b.rep_plarform, b.version, b.component_id, b.resolution, b.target_milestone, b.status_whiteboard, b.votes, b.keywords, b.last_diffed,
b.everconfirmed, b.reporter_accessible, b.cclist_accessible, b.estimated_time, b.remaining_time, b.deadline,  b.alias, pf.login_name, pd.name
FROM bugs b, profiles pf, products pd
WHERE bugs.assigned_to = profiles.user_id
AND bugs.product_id = products.id
ORDER BY bug_id;};
my $sth = $dbh -> prepare($query);
$sth ->execute() or die $dbh ->errstr;
while ( my $ref = $sth->fetchrow_hashref()) {
    print $ref ->{
'bug_id', 'assigned_to', 'bug_file_loc', 'bug_severity', 'bug_status', 'creation_ts', 'delta_ts', 'short_desc', 'op_sys', 'priority', 'product_id', 'rep_plarform', 'version', 'component_id', 'resolution', 'target_milestone', 'status_whiteboard', 'votes',
 'keywords', 'last_diffed', 'everconfirmed', 'reporter_accessible', 'cclist_accessible', 'estimated_time', 'remaining_time', 'deadline',  'alias', 'login_name', 'name'}, "\n";
}
//-e checks to see if the file exists
if (-e $excel_file)
{
#open Excel file
my $Book = $Excel->Workbooks->Open($excel_file);
$exists="true";
}else{
$Book = $Excel->Workbooks->Add();
$exists = "false";
}
if($exists eq "true"){
  $previousSheet = $Book->ActiveSheet;
  $currentSheet = $Book->Worksheets->Add();
  $Book->ActiveSheet->{Name}="Report";
}else{
  $previousSheet = 0;
}
#create the spreadsheet
$excel -> output();
#sort data of worksheet – ASC or DESC
$excel -> sort_data(‘Report.xls’,0, ‘ASC’);
#add headers to 'Report'
$excel -> set_headers('Report.xls', [qw/Bugs report/]);
#print sheet-names
print join( , ,$excel -> sheets()), \n;
#get the result as a string
my $spreadsheet = $excel -> output_as_string();
#print result into a file and handle error
$excel->output_to_file(Report.xls) or die $excel -> errstr();
if($exists eq "true"){
  $Book->Save();
}else{
  $Book->SaveAs("$excel_file") or die $!;
}
$sth -> finish();
$dbh -> disconnect();
 
 

[Sport Girl]

Hi everybody, just want to say that i have resolved the problem of the character set but i have syntax errors:

can somebody help me how can i rerieve data from multilple database tables cause my query syntax is wrong: i need to retrieve data from the tables bugs, profiles and products from the database bugs.

Please can somebody help, i really need it?

 
#!/usr/bin/perl -W
use DBI;
use DBD::mysql;
use strict;
use File::Copy;
use File::Path;
use File::Basename;
use Spreadsheet::WriteExcel::Big;
use Data::Dumper;
my $worksheet = ['Report.xls'];
my $dbh = DBI->connect("dbi:mysql:database=bugs;host=localhost") or die "Connection Error";
my $excel_file = 'C:/Perl/Report.xls';
$query =qq{SELECT
b.bug_id, b.bug_file_loc, b.bug_severity, b.bug_status, b.creation_ts, b.delta_ts, b.short_desc, b.op_sys, b.priority, b.rep_plarform, b.version, b.component_id, b.resolution, b.target_milestone, b.status_whiteboard, b.votes, b.keywords, b.last_diffed,
b.everconfirmed, b.reporter_accessible, b.cclist_accessible, b.estimated_time, b.remaining_time, b.deadline,  b.alias, pf.login_name, pd.name
FROM bugs b, profiles pf, products pd
WHERE bugs.assigned_to = profiles.user_id
AND bugs.product_id = products.id
ORDER BY bug_id;};
my $sth = $dbh -> prepare($query);
$sth ->execute() or die $dbh ->errstr;
while ( my $ref = $sth->fetchrow_hashref()) {
    print $ref ->{
'bug_id', 'assigned_to', 'bug_file_loc', 'bug_severity', 'bug_status', 'creation_ts', 'delta_ts', 'short_desc', 'op_sys', 'priority', 'product_id', 'rep_plarform', 'version', 'component_id', 'resolution', 'target_milestone', 'status_whiteboard', 'votes',
 'keywords', 'last_diffed', 'everconfirmed', 'reporter_accessible', 'cclist_accessible', 'estimated_time', 'remaining_time', 'deadline',  'alias', 'login_name', 'name'}, "\n";
}
if (-e $excel_file)
{
#open Excel file
my $Book = Workbooks->Open($excel_file);
$exists="true";
}else{
$Book =Workbooks->Add();
$exists = "false";
}
if($exists eq "true"){
  $previousSheet = $Book->ActiveSheet;
  $currentSheet = $Book->Worksheets->Add();
  $Book->ActiveSheet->{Name}="Report";
}else{
  $previousSheet = 0;
}
#create the spreadsheet
$excel_file -> output();
#sort data of worksheet  ASC or DESC
$excel -> sort_data('Report.xls',0,'ASC');
#add headers to 'Report'
$excel -> set_headers('Report.xls', [qw/Bugs report/]);
#get the result as a string
my $spreadsheet = $excel_file -> output_as_string();
#print result into a file and handle error
$excel->output_to_file('Report.xls') or die $excel -> errstr();
if($exists eq "true"){
  $Book->Save();
}else{
  $Book->SaveAs("$excel_file") or die $!;
}
$sth -> finish();
$dbh -> disconnect();
 
 

[numberwhun]

Unfortunately, I don't have tons of experience with DBI or SQL so I would have to look into your question. But, I did want to add this, instead of using the "-W" or "-w" switch to the shebang line, you should really use the "use warnings;" pragma instead. Just standard best practice is all.

Regards,

Jeff

[Sport Girl]

Hi Jeff,
thank u for ur cooperation .I appreciate it very much.
Actually i have made a step in syntax errors but still there is something to do:

in fact i can't get the data retrieved from the bugs, products and profiles tables written in the excel sheet file.
How can i get the data retreived written to the excel sheet unless from the table bugs?

I'll give details:

the database name is bugs

the fields in the table bugs are:

bug_id
bug_file_loc
$bug_severity
bug_status
creation_ts
delta_ts
short_desc
op_sys
priority
rep_platform
version
component_id
resolution
target_milestone
status_whiteboard
votes
keywords
lastdiffed
everconfirmed
reporter_accessible
cclist_accessible
estimated_time
remaining_time
deadline
my $alias

The field in the profiles table to be retrieved: login_name
The field in the products table to be retreived is : name

Can u give me a hand with this one?

 
#!/usr/bin/perl 
use strict;
use warnings;
use DBI;
use DBD::mysql;
use lib qw(.);
use Bugzilla;
use Bugzilla::Bug;
use Bugzilla::Util;
use Bugzilla::Constants;
use Time::Local;
use Spreadsheet::WriteExcel;
 
 
my $dbh = DBI->connect('DBI:mysql:database=bugs;host=localhost', 'jinny', 'jinny') or die "Connection Error";
 
my $Excelfile = "/tmp/Report.xls";
 
#create a new instance
my $excel = Spreadsheet::WriteExcel->new("$Excelfile");
my $worksheet = $excel->addworksheet("Bugs Report");
my $now = localtime time;
$worksheet->write(0, 0, "Report generated on :$now" );
 
my $stmt = "SELECT b.bug_id, b.bug_file_loc, b.bug_severity, b.bug_status, b.creation_ts, b.delta_ts, b.short_desc, b.op_sys, b.priority, b.rep_platform, b.version, b.component_id, b.resolution, b.target_milestone, b.status_whiteboard, b.votes, b.keywords, b.lastdiffed,
b.everconfirmed, b.reporter_accessible, b.cclist_accessible, b.estimated_time, b.remaining_time, b.deadline,  b.alias, pf.login_name, pd.name
FROM bugs b, profiles pf, products pd
ORDER BY bug_id";
 
 
my $sth = $dbh->prepare($stmt);
my @data;
 
$sth->execute() or die $dbh->errstr;
 
while ( @data = $sth->fetchrow_array()){
    my $bug_id = $data[1];
    my $bug_file_loc = $data[2];
    my $bug_severity = $data[3];
    my $bug_status = $data[4];
    my $creation_ts = $data[5];
    my $delta_ts = $data[6];
    my $short_desc = $data[7];
    my $op_sys = $data[8];
    my $priority = $data[9];
    my $rep_platform = $data[10];
    my $version = $data[11];
    my $component_id = $data[12];
    my $resolution = $data[13];
    my $target_milestone = $data[14];
    my $status_whiteboard = $data[15];
    my $votes = $data[16];
    my $keywords = $data[17];
    my $lastdiffed = $data[18];
    my $everconfirmed = $data[19];
    my $reporter_accessible = $data[20];
    my $cclist_accessible = $data[21];
    my $estimated_time = $data[22];
    my $remaining_time= $data[23];
    my $deadline = $data[24];
    my $alias= $data[25];
    my $login_name = $data[26];
    my $name = $data[27];
    my $row = 0;
    my $col = 0;
    foreach my $stmt (@data) {
        $worksheet->write($row++, @data);
        last;
    }
}
 
$sth->finish();
$dbh->disconnect();