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

I am running IIS with ActiveState Perl on a WIN32 machine. The DBD I am using is ODBC to connect to a FileMaker Pro database.

The following script runs from the command line, but not from an Internet browser.

_________ 

# declare libraries
use CGI qw(:standard);
use DBI;
use strict;

print "Content-Type: text/html\n\n"; 
print "<HTML><HEAD><TITLE>Log Script</TITLE></HEAD><BODY>";

# declare variables

my $a = 000;
my $b = 000;
my $c = 'xxxxx';
my $d = 'xxxxxx';

# Connect to datasource
my $dbh=DBI->connect('dbi:ODBC:<my dsn name>','xxxxxxx','xxxxxxxx')
    or print "Couldn't connect to database: " . DBI->errstr . "\n\n";

# Log Info
my $log = $dbh->prepare("Insert into <databasename>.fp3 (<fieldname1>,
+ <fieldname2.) values ('$d', '$c')")
    or print "Couldn't prepare INSERT statement: ". DBI->errstr. "\n\n
+";
$log->execute()
    or print "Couldn't execute INSERT statement: ". DBI->errstr. "\n\n
+";

# Get Journal URL

my $geturl = $dbh->prepare("SELECT <fieldname1> FROM <databasename>.fp
+3 WHERE <fieldname2> ='$d'")
    or print "Couldn't prepare SELECT statement: ". DBI->errstr. "\n\n
+";
$geturl->execute()
    or print "Couldn't execute SELECT statement: ". DBI->errstr. "\n\n
+";
my @data = $geturl->fetchrow_array();
my $jurl = $data[0];

# Set URL

print $jurl . "\n<P>";
     
my $URL = $jurl;

# print $URL;  

print $URL . "\n<P>"

[download]

_________

When I run it from a browser, I get the following error: 

Couldn't prepare INSERT statement: [FileMaker][ODBC FileMaker Pro driv
+er][FileMaker Pro]Connect failed (SQL-S1000)(DBD: st_prepare/SQLPrepa
+re err=-1)

[download]

If I delete the INSERT statements, then I get a similar error with the SELECT Statements: 

Couldn't prepare SELECT statement: [FileMaker][ODBC FileMaker Pro driv
+er][FileMaker Pro]Connect failed (SQL-S1000)(DBD: st_prepare/SQLPrepa
+re err=-1)

[download]

I have checked the permissions for the scripts and database directories, and everyone is set to at least read the files. In the case of the database directory, everyone is set to read and write.

I have also looked at the environmental variables. I ran a list from the command line and from the browser and compared them. There were a handful that were set through the command line but not through the browser, so I set them like this: 

#Set Environmentals
$ENV{USERNAME} = "Administrator";
$ENV{APPDATA} = "C:\\Documents and Settings\\Administrator.LRC\\Applic
+ation Data";
$ENV{ALLUSERSPROFILE} = "C:\\Documents and Settings\\All Users";
$ENV{TMP} = "C:\\WINNT\\TEMP";
$ENV{USERDNSDOMAIN} = "<our DNS domain>";
$ENV{USERDOMAIN} = "<our domain>";
$ENV{HOMEPATH} = "\\";
$ENV{HOMEDRIVE} = "C:";
$ENV{LOGONSERVER} = "\\\\<SERVER NAME>";

[download]

I received the exact same errors.

Does anyone have any idea where my problem is?

Thanks.

-Emma

edited: Mon Jul 15 19:13:00 2002 by jeffa - removed br tags and added code tags

Replies are listed 'Best First'.
Re: DBI Call works from command line but not from browser
by perrin (Chancellor) on Jul 15, 2002 at 20:13 UTC
    I would take it at its word: the connection is failing. You're on the right track, looking at environment variables. This is usually caused by differences in your environment when you run under CGI. It could be caused by running as a different user, or having a different PATH setting, or having some required file in the same directory that you run your command-line script from.
[reply]
      When I compare the PATH statement, they are the same. Also, even when I log on as Administrator through the browser I get the same error. Are their any environmental variables that are "assumed" at the command line that are not in the browser?

      Also, since I don't get an error when I actually connect to the database, it seems that the connection is made but just can't be maintained.

      Any ideas?

[reply]
        I'm not sure how you are logging on as Administrator through the browser, but the issue I was referring to is that CGI programs generally run as the user that the web server is running as, while command-line programs run as the user you are logged in as. Sometimes this leads to problems with permissions. I've never used CGI on a Windows system, so I can't give much more advice about that.

        I suspect that your connect statement does not actually connect. You're using ODBC, and a particularly odd driver (FileMaker), so it's quite possible that it doesn't bother to actually connect until you ask it to do something like select or insert. There may not be any real connection here at all, since I think FileMaker is not a networked database.

[reply]
      When I compare the PATH statement, they are the same. Also, even when I log on as Administrator through the browser I get the same error. Are their any environmental variables that are "assumed" at the command line that are not in the browser?

      Also, since I don't get an error when I actually connect to the database, it seems that the connection is made but just can't be maintained.

      Any ideas?

[reply]
Re: DBI Call works from command line but not from browser
by Silicon Cactus (Scribe) on Jul 15, 2002 at 20:25 UTC
    You do not mention so in your post, so *someone* should ask:
    Have you verified that you created a system DSN as opposed to a user DSN on the server? That *could* be your culprit.
[reply]
      Yes, I did create a System rather than a User DSN.
[reply]
DBI cannot connect from IIS
by LanceDeeply (Chaplain) on May 30, 2003 at 14:45 UTC
    oh man-

    i know this is an old post, but i was having the exact same problem and i couldnt find and answer anywhere on Super Search or Google.

    the problem is that under iis, dbi doesn't use the username and password you specify, it uses the default iis user. i found this out when checking the log file generated by adding this line before my connect:
        DBI->trace(9, 'C:\dbitrace.txt')

    [download]
    to rectify this, i changed the connection string to contain the username and password. i am using SQL Server so my dbi connection string looks something like this: 
    'dbi:ODBC:DRIVER={SQL Server};SERVER=theserver;DATABASE=thedb;UID=user
+id;PWD=password'

    [download]
    here are more examples of ODBC connection strings.

    hopefully- someone will find this usefull
[reply]
[d/l]
[select]
Re: DBI Call works from command line but not from browser
by screamingeagle (Curate) on Jul 16, 2002 at 17:17 UTC
    try using : 
    $dbh->do("use <databasename>") || die("SQL Error: " . $dbh->errstr);
my $log = $dbh->prepare("Insert into fp3 (<fieldname1>,
+ <fieldname2.) values ('$d', '$c')")
    or print "Couldn't prepare INSERT statement: ". DBI->errstr. "\n\n
+";

    [download]
    instead of prefixing the databasename in the SQL stmt...
    hth
[reply]
[d/l]

Back to Seekers of Perl Wisdom