djibel has asked for the wisdom of the Perl Monks concerning the following question:

Dear, I have a curious problem with perl CGI and ODBC. I have a script which connect to a database (Progress) using DBI (with DBD::ODBC). If I execute this script without CGI, it is working, example : 
#!C:/perl/bin/perl.exe
use warnings;
use strict;

use DBI;

my $dbh = DBI->connect('DBI:ODBC:DSN=MY DATABASE', 'login', '' )
or die "Can't connect to database\n$DBI::errstr";

# My  code

[download]
But, if I want to use this script with CGI, It do not work. 
#!C:/perl/bin/perl.exe
use warnings;
use strict;
use CGI;
use CGI::Carp qw(fatalsToBrowser);

use DBI;

my $cgi = new CGI;
print $cgi->header();
print $cgi->start_html();

my $dbh = DBI->connect('DBI:ODBC:DSN=MY DATABASE', 'login', '' )
or die "Can't connect to database\n$DBI::errstr";

# My  code

print $cgi->end_html();

[download]
I have this message : Can't connect to database, Microsoft ODBC Driver Manager Data source name not found and no default driver specified (SQL-IM002) Why connection work without CGI and failed with CGI ? Best Regards,

Replies are listed 'Best First'.
Re: CGI and ODBC (progress)
by Corion (Patriarch) on Apr 28, 2010 at 09:13 UTC

    Most likely this is because the user account your webserver (and hence, your CGI program) is running as does not have the appropriate permissions to use the ODBC DSN, or that ODBC DSN does not exist for that user.

[reply]
      I am not understand because in another computer, I have installed easyphp and openedge and in this computer, I have not problem. But Now, I have install in other XP computer separately apache, php, mysql, phpmyadmin and openedge, but CGI script doesn't work !
[reply]

        Have you checked the problem causes I mentioned? What user is your webserver running as? Can that user see and use the the ODBC DSN?

        What are the differences between the two machines? Try to find them and one of the differences is likely the cause.

[reply]
Debugging tip: Change only one thing at a time (was Re: CGI and ODBC (progress))
by roboticus (Chancellor) on Apr 28, 2010 at 14:25 UTC

    djibel

    I'm surprised no-one has mentioned it, but here goes: It appears that you've misattributed the error to CGI because you changed too many variables between your "it worked" test and your "it didn't work" test. You changed two variables: (1) whether or not you used CGI, and (2) the account you ran the program under. (You probably changed others, as well, such as running it under a web server, etc., but those are the two that are important for this example.)

    You can run the program without CGI under your own account, in which case you should have seen the proper data come out. Alternatively, you could have run it under the web server account in a command-line window to see it fail to retrieve the data of interest. So remember, when you're isolating a problem, make sure you've limited the changes to a single variable before deciding on the culprit. It's far too easy to make the assumption that everything else is the same, when in fact it's not.

    ...roboticus

    Programmer A: Hmmm ... this program ran just fine last week, but now it's giving me an error message!

    Programmer B: OK, what changed?

    Programmer A: Nothing!

    Programmer B: Nonsense, something had to change!

    Programmer A: No, really, nothing changed!

    Programmer B: OK, then since you ran the program last week, why did you it today? If you changed nothing, you'll get the same answer.

    Programmer A: Oh, ummm, err.... OK, the data changed.

    Programmer B: OK, then that changed. What happens when you run the program with the same data?

    Programmer A: I tried that a few minutes ago, and it gave me the same error message.

    Programmer B: OK, then, what else changed?

    Programmer A: Nothing!

    Programmer B: rolls eyes...

[reply]
      That is my last test. I tried now on my own computer where CGI and ODBC work.

      1- If I use Easyphp. The script work using CGI
      2- If I stop easyphp and install apache separatly and test my cgi script. it's failed (no connection database).

      On easyphp apache and apache server, they use the same user :
      User daemon
      Group daemon

      What is the problem ?
[reply]

        djibel

        It sounds like you're still changing more than one variable at a time. You can run it under your account in a command-line window without CGI and it works, right? If so, put in CGI and run it in the same command-line window, and it should still run. (The text may be a bit much to dig through, as you'll have your HTML stuff, but that ought not stop you.)

        I don't know what Easyphp is, so I can't help you move from that step to the apache configuration, though.

        ...roboticus

[reply]
Re: CGI and ODBC (progress)
by mje (Curate) on Apr 28, 2010 at 10:11 UTC

    Are you using a "USER" DSN? Generally you should create a "SYSTEM" DSN (see the ODBC Driver Manager GUI) if you want all users to see the DSN. If you want private "USER" DSNs then you will need to create one as the user Apache is running as as corion says.

[reply]
      apache user is (in httpd.conf) : User daemon Group daemon I did not found how to check with this user.
[reply]

        I don't know how Apache works in Windows but are you sure you have a user called daemon in Windows? What I am talking about is your Windows username - the person you login as. If you login as user fred and create a USER DSN then only user fred sees that DSN. That means a process (like Apache) running as user daemon only sees USER DSNs created by daemon and cannot see USER DSNs created by fred. For this reason DSNs you want accessible by multiple people are created as SYSTEM DSNs.

[reply]
Re: CGI and ODBC (progress)
by maxhq (Novice) on Apr 28, 2010 at 16:27 UTC

    I once had a problem with CGI and a Sybase database that was caused by a missing environment variable.

    Just put something like print map {"$_ = $ENV{$_}\n"} sort (keys %ENV); into your script and see if there are any ODBC related settings.

    If there are some, try setting them using $ENV{'SOMETHING'} = 'value';.

[reply]
[d/l]
[select]
[reply]
[d/l]

Back to Seekers of Perl Wisdom