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 misconfiguratio n 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();
-
5 5083 miller 1,089
Recognized Expert Top Contributor
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
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 misconfiguratio n 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();
-
-
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 3,509
Recognized Expert Moderator Specialist
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
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_mileston e
status_whiteboa rd
votes
keywords
lastdiffed
everconfirmed
reporter_access ible
cclist_accessib le
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();
-
-
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Johnny |
last post by:
I was looking for information on how to import an excel file or other
text file into a MySql database. I have done this with both access
and SQL Server and am looking for a way to do this in MySql. I have
several hundred items i want to import into a parts database that is
currently in an excel file but can be saved as comma separated or
other format. Any help would be greatly appreciated. Thanks in
advance.
Zach
|
by: bawar |
last post by:
Hey, I can say I belong to the newbies in this subject. I own my own
website with mySQL-databases, but now I have to show one table of
those mySQL-databases in an excel worksheet. It is the intention that
the user opens the excel-file trough the browser, so I hope there does
not have to be installed anything on the clients machine... (I looked
at that MyODBC but I don't understand it 100 % yet, I'm newbie hey).
It would be very nice if...
|
by: ctkevin |
last post by:
I am an experienced Access user but recently moved an Access database
to the web for my employer.
We use a host that does not allow the use of data access pages.
Is there anyway to pre-build a query and/or report in Access that could
generate an Excel download? I was thinking of using a macro, but I
have no idea how to invoke the macro.
Thanks in advance!
|
by: Anne Sachleben via AccessMonster.com |
last post by:
I am using the TransferSpreadsheet function to export a query result to a
specific worksheet in an Excel file titled "report". I want the result to
be exported to the worksheet titled "facts". Here are the parameters I
entered:
Transfer Type: Export
Spreadsheet Type: Microsoft Excel 8-10
Table Name: facts query
File Name: C:\report.xls
Has Field Names: No
|
by: DeniseY |
last post by:
I have an Access report that is created on the fly by the user selecting the fields to be included. The Access report comes out fine, but I want it to automatically output to an Excel spreadsheet. Again, I have this part working. But the fields in the resulting spreadsheet are in a different order than the Access report. (Example: The fields in the Access report might go FirstName, LastName,Address,City--in the Excel spreadsheet, they come up...
| |
by: nofear |
last post by:
I used to export my reports as snapshot but now I have to export them
to Excel
When I export my report to a Excel Spreadsheet the report header and
footer are not included
Only the data gets exported.
How do I format a Access report for Excel the right way?
|
by: Sport Girl |
last post by:
Hi everybody.
I am new in PERL language and working in it since little hours, actually i am still a trainee and i need help please.
In fact, i need a script in PERL that enables me to retrieve data from a table from the database in MySQL and write its contents in an excel sheet file as an excel repot.
I have done a script but it is still in draft cause it's not working.
Can somebody help me please.
|
by: Sport Girl |
last post by:
Hi everybody ,
i have the task of developing in Perl a script that retrieves data from 3 tables ( bugs, profiles, products) from a MySQL database called bugs and display them in an excel sheet report.
I always get when trying to test it " 500 Internal Server Error - The server encountered an internal error or misconfiguration and was unable to complete your request."
Can somebody help me please.
|
by: Master Ken |
last post by:
Hi All,
I'm very new to C# and ASP and I am asking for some help as I don't really know where to start on this project.
Some background info first
I run a report each week which outputs data to an excel spreadsheet, I then have to manually do insert and update query's to our oracle database using this spreadsheet.
I would like to create an application that would read the data form the spreadsheet and automatically update the database.
|
by: Alan M Dunsmuir |
last post by:
A client has sent me a (Windows) Excel Spreadsheet (.xls) file
containing a block of data he wants included as a table in a MySQL
database in a (Linux-based) PHP/MySQL Web application I'm developing for
him.
I have both Windows and Linux available on my own machine.
What is the recommended route to get this .xls file into MySQL?
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |