CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Need help writing a script that interacts with a MySQL DB

 

First page Previous page 1 2 Next page Last page  View All


FishMonger
Veteran / Moderator

Oct 20, 2012, 6:35 PM

Post #26 of 45 (6746 views)
Re: [MDTech.us_MAN] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post

Did you try connecting from the command line using the same params as in the script? If that fails, then there is no reason to expect it to work in the Perl script. Once you're able to connect from the command line, then we can work on using those params in the script.


MDTech.us_MAN
Novice

Oct 20, 2012, 7:04 PM

Post #27 of 45 (6744 views)
Re: [FishMonger] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post


In Reply To
Did you try connecting from the command line using the same params as in the script? If that fails, then there is no reason to expect it to work in the Perl script. Once you're able to connect from the command line, then we can work on using those params in the script.


I guess the connection is successful.
Here is what is I get in Debian:

Code
root@MDTech:~# mysql -h instance28854.db.xeround.com -P 18847 -u maxwell175 -p 
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is *num_removed_by_me*
Server version: 5.1.42 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>


Maxwell.


(This post was edited by MDTech.us_MAN on Oct 20, 2012, 7:05 PM)


MDTech.us_MAN
Novice

Oct 22, 2012, 7:23 PM

Post #28 of 45 (6735 views)
Re: [MDTech.us_MAN] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post

Yes the connection is successful! I can retrieve some data from tables!


MDTech.us_MAN
Novice

Oct 23, 2012, 5:50 AM

Post #29 of 45 (6730 views)
Re: [MDTech.us_MAN] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post

I still can't connect using Perl!


FishMonger
Veteran / Moderator

Oct 23, 2012, 7:12 AM

Post #30 of 45 (6725 views)
Re: [MDTech.us_MAN] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post

Try changing your connect statement from:

Code
my $conn = DBI->connect('DBI:mysql:bcpadb;host=instance28854.db.xeround.com:18847', 'maxwell175', 'qwertyuiop', { RaiseError => 1 }) || die "Could not connect to the database";


to this:

Code
DBI->trace(4); # value can be between 0 and 15 
my $conn = DBI->connect('DBI:mysql:bcpadb:instance28854.db.xeround.com:18847',
'maxwell175', 'qwertyuiop',
{ RaiseError => 1 })
or die "Could not connect to the database " . $DBI::errstr;


There are 2 changes being made: 1) the addition of the trace (low level debugging) and 2) slight change in syntax on the connect statement,


FishMonger
Veteran / Moderator

Oct 23, 2012, 7:19 AM

Post #31 of 45 (6723 views)
Re: [MDTech.us_MAN] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post

You may want to read the documentation regarding setting/using trace.

http://search.cpan.org/~timb/DBI-1.622/DBI.pm#TRACING


MDTech.us_MAN
Novice

Oct 23, 2012, 1:46 PM

Post #32 of 45 (6720 views)
Re: [FishMonger] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post


In Reply To
You may want to read the documentation regarding setting/using trace.

http://search.cpan.org/~timb/DBI-1.622/DBI.pm#TRACING


Ok, I tried the code you gave , but still, same message:

Code
Software error: 

Could not connect to the database Can't connect to MySQL server on 'instance28854.db.xeround.com' (111) at /home/www/mdtech.us.ms/BCPA/BCPA_form/PROC/index.cgi line 15.

For help, please send mail to the webmaster ([no address given]), giving this error message and the time and date of the error.



FishMonger
Veteran / Moderator

Oct 23, 2012, 2:01 PM

Post #33 of 45 (6717 views)
Re: [MDTech.us_MAN] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post

The trace output goes to stderr by default, not stdout. You need to redirect it to the browser or to a file.
To redirect it to the browser you need to do this:

Code
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);

Then after outputting the html header, add this:

Code
warningsToBrowser(1);


When redirected to the browser via warningsToBrowser, they show up as html comments, so you'd need to "view source" to see that output.

To redirect the trace output to a file, you add the optional "file" parameter to the DBI->trace call.
http://search.cpan.org/~timb/DBI-1.622/DBI.pm#Trace_Output


(This post was edited by FishMonger on Oct 23, 2012, 2:03 PM)


MDTech.us_MAN
Novice

Oct 25, 2012, 9:41 AM

Post #34 of 45 (6710 views)
Re: [FishMonger] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post


In Reply To
The trace output goes to stderr by default, not stdout. You need to redirect it to the browser or to a file.
To redirect it to the browser you need to do this:

Code
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);

Then after outputting the html header, add this:

Code
warningsToBrowser(1);


When redirected to the browser via warningsToBrowser, they show up as html comments, so you'd need to "view source" to see that output.

To redirect the trace output to a file, you add the optional "file" parameter to the DBI->trace call.
http://search.cpan.org/~timb/DBI-1.622/DBI.pm#Trace_Output


Ok, I got the warning:

Code
<!-- warning: DBI connect('bcpadb:instance28854.db.xeround.com:18847','maxwell175',...) failed: Can't connect to MySQL server on 'instance28854.db.xeround.com' (111) at /home/www/mdtech.us.ms/BCPA/BCPA_form/PROC/index.cgi line 17 --> 
<h1>Software error:</h1>
<pre>Could not connect to the database Can't connect to MySQL server on 'instance28854.db.xeround.com' (111) at /home/www/mdtech.us.ms/BCPA/BCPA_form/PROC/index.cgi line 17.</pre>
<p>
For help, please send mail to the webmaster (<a href="mailto:[no address given]">[no address given]</a>), giving this error message
and the time and date of the error.

</p>


Maxwell.


FishMonger
Veteran / Moderator

Oct 25, 2012, 10:25 AM

Post #35 of 45 (6705 views)
Re: [MDTech.us_MAN] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post

Ok, that output does not include the trace output. Did you enable tracing and if so did you redirect via warningsToBrowser(1) or to a file as shown in the DBI documentation?

You should go back through your posts and remove your db username and password so that other can't access the db!!

Here's my working test script which accesses your database.

Code
#!/usr/bin/perl 

use strict;
use warnings;
use DBI;

my $dsn = 'DBI:mysql:bcpadb:instance28854.db.xeround.com:18847';
my $dbh = DBI->connect($dsn, '*******', '*******',
{ RaiseError => 1 })
|| die "Could not connect to the database " . $DBI::errstr;

my $sth = $dbh->prepare("SELECT * FROM people2");
$sth->execute;

while (my @row = $sth->fetchrow_array) {
print "@row\n";
}


Output:

Quote
c:\testing>MDTech.us_MAN.pl
John Doe 21 Summer Dr Holland 18966 N/A N/A N/A N/A N/A Unemployed 2155794610 N/A N/A johndoe@buckscoparalegals.com


The root of your problem is that you didn't properly setup the user rights in my mysql database. Your db table is empty. It should have entries in it specifying who can access the database and from where. You need to use the mysql grant function to setup those rights.

http://dev.mysql.com/doc/refman/5.1/en/grant.html


MDTech.us_MAN
Novice

Oct 25, 2012, 10:59 AM

Post #36 of 45 (6702 views)
Re: [FishMonger] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post

Hello:

I used your code in my script and everything works fine, but, when I tried running that same script using my hosting service, it doesn't work!!!

Code:
http://pastebin.com/kK9tD2D4


(This post was edited by MDTech.us_MAN on Oct 25, 2012, 11:05 AM)


FishMonger
Veteran / Moderator

Oct 25, 2012, 11:47 AM

Post #37 of 45 (6699 views)
Re: [MDTech.us_MAN] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post

Saying "it doesn't work" doesn't tell me anything. What does "doesn't work" mean? What was the error/trace output?


(This post was edited by FishMonger on Oct 25, 2012, 11:47 AM)


MDTech.us_MAN
Novice

Oct 25, 2012, 2:10 PM

Post #38 of 45 (6694 views)
Re: [FishMonger] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post


In Reply To
Saying "it doesn't work" doesn't tell me anything. What does "doesn't work" mean? What was the error/trace output?




Sorry for not being specific. I meant that I get the same thing as I described in my previous post:


Quote
Ok, I got the warning:

Code
<!-- warning: DBI connect('bcpadb:instance28854.db.xeround.com:18847','maxwell175',...) failed: Can't connect to MySQL server on 'instance28854.db.xeround.com' (111) at /home/www/mdtech.us.ms/BCPA/BCPA_form/PROC/index.cgi line 17 -->  
<h1>Software error:</h1>
<pre>Could not connect to the database Can't connect to MySQL server on 'instance28854.db.xeround.com' (111) at /home/www/mdtech.us.ms/BCPA/BCPA_form/PROC/index.cgi line 17.</pre>
<p>
For help, please send mail to the webmaster (<a href="mailto:[no address given]">[no address given]</a>), giving this error message
and the time and date of the error.

</p>


Maxwell.



FishMonger
Veteran / Moderator

Oct 25, 2012, 2:37 PM

Post #39 of 45 (6692 views)
Re: [MDTech.us_MAN] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post


In Reply To
Hello:

I used your code in my script and everything works fine, but, when I tried running that same script using my hosting service, it doesn't work!!!


Please clarify. Does that mean that you tested it on 2 different systems, your test system and your hosting service provider's system? Or, on the same system under 2 separate environments, command line and web page?


MDTech.us_MAN
Novice

Oct 25, 2012, 7:05 PM

Post #40 of 45 (6687 views)
Re: [FishMonger] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post

Ok, here is the set up:
My Computer: Strawberry Perl (Windows x64)
Hosting service: Apache with Perl and (I think) Linux
NOTE: I do not have physical or console access to the above server!

Maxwell.


In Reply To

In Reply To
Hello:

I used your code in my script and everything works fine, but, when I tried running that same script using my hosting service, it doesn't work!!!


Please clarify. Does that mean that you tested it on 2 different systems, your test system and your hosting service provider's system? Or, on the same system under 2 separate environments, command line and web page?



FishMonger
Veteran / Moderator

Oct 25, 2012, 7:44 PM

Post #41 of 45 (6684 views)
Re: [MDTech.us_MAN] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post


Quote
NOTE: I do not have physical or console access to the above server!

The problem you're experiencing is a perfect example why I always make sure I have shell (ssh) access to the server.

Since I'm able to gain direct remote access to your database on your web host but you can't access it through your web site on that server, shows that the database access rights aren't setup correctly. I don't know if that's due to a mistake on your end or your web host, but at this point you should contact your web host.

If you haven't reset the access rights, I might be able to login and fix it for you, but that would not be appropriate.


MDTech.us_MAN
Novice

Oct 25, 2012, 8:09 PM

Post #42 of 45 (6680 views)
Re: [FishMonger] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post


In Reply To

Quote
NOTE: I do not have physical or console access to the above server!

The problem you're experiencing is a perfect example why I always make sure I have shell (ssh) access to the server.

Since I'm able to gain direct remote access to your database on your web host but you can't access it through your web site on that server, shows that the database access rights aren't setup correctly. I don't know if that's due to a mistake on your end or your web host, but at this point you should contact your web host.

If you haven't reset the access rights, I might be able to login and fix it for you, but that would not be appropriate.


I already set the rights to ALL using phpMyAdmin.

Maxwell.


MDTech.us_MAN
Novice

Oct 25, 2012, 8:11 PM

Post #43 of 45 (6679 views)
Re: [FishMonger] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post


In Reply To

Quote
NOTE: I do not have physical or console access to the above server!

The problem you're experiencing is a perfect example why I always make sure I have shell (ssh) access to the server.

Since I'm able to gain direct remote access to your database on your web host but you can't access it through your web site on that server, shows that the database access rights aren't setup correctly. I don't know if that's due to a mistake on your end or your web host, but at this point you should contact your web host.

If you haven't reset the access rights, I might be able to login and fix it for you, but that would not be appropriate.


Also, tomorrow I will try setting up the server on my Windows server that I have at my house.

Maxwell.

P.S. Don't forget to read my above post!


(This post was edited by MDTech.us_MAN on Oct 25, 2012, 8:11 PM)


FishMonger
Veteran / Moderator

Oct 25, 2012, 8:39 PM

Post #44 of 45 (6674 views)
Re: [MDTech.us_MAN] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post


In Reply To

I already set the rights to ALL using phpMyAdmin.

Maxwell.


Your rights are still too permissive. I still have direct access to your db as well as the root mysql db.


MDTech.us_MAN
Novice

Oct 26, 2012, 7:29 AM

Post #45 of 45 (6669 views)
Re: [FishMonger] Need help writing a script that interacts with a MySQL DB [In reply to] Can't Post

Well, is there a SQL command I could run to set the correct permissions?

Also, I am currently setting up my home Windows server. If you have any requirements for setting up perl, reply to this message.

Maxwell.


In Reply To

In Reply To

I already set the rights to ALL using phpMyAdmin.

Maxwell.


Your rights are still too permissive. I still have direct access to your db as well as the root mysql db.


First page Previous page 1 2 Next page Last page  View All
 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives