Data base connection error

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

Hi guys, I am a newbie to perl and keen to learn so would you please help me I try to write the script connecting to sqlserver 2005. I am using the windows NT authentication to set up the DSN The script is working when i run it from the command prompt Part of it is:

#!/usr/bin/perl
use CGI qw/:standard/;
use strict;
use Win32::ODBC;

my $db = new Win32::ODBC("DSN=portal-mk2");

my $DSN = "portal-mk2";
if (!($db = new Win32::ODBC($DSN))){
    print "Error connecting to $DSN\n";
    print "Error: " . Win32::ODBC::Error() . "\n";
    exit;
}

my $SqlStatement = "SELECT Id, StudFirstName, StudLastName, HomeGroup 
+ FROM dbo.StudDetails";
if ($db->Sql($SqlStatement)){
    print "SQL failed.\n";
    print "Error: " . $db->Error() . "\n";
    $db->Close();
    exit;
}
[download]

However when i try to use CGI to connect i have problem to open connection. Here is part of the script:

use CGI qw/:standard :html3 :netscape/;
use Win32::ODBC;

# Unpack parameters from form

$cmd=param('cmd');        #Command to processs
$DSN=param('DSN');        #Selected DSN
$table=param('table');        #Selected table from DSN
@cols=param('cols');        #Selected columns from table
$sort=param('sort');        #Column to sort on
$rows=param('rows');        #Max number of rows

# Off we go...
# If no command or DSN then get user to select one.

if ($cmd eq '' || $DSN eq ''){
  %DSN=Win32::ODBC::DataSources;    #Get list of datasources
  @keys=sort(keys(%DSN));        #put list of DSNs into array
  &screen_header;            #Start HTML response
  print start_form,
    hidden(-name=>'cmd',-value=>'table',-force=>1),
    table(
      Tr([
        td(['Data Source: ',
           popup_menu(-name=>'DSN',-value=>\@keys)."&nbsp&nbsp&nbsp".s
+ubmit(-name=>'GO')])
      ])
    ),
  end_form,
  hr,      
  end_html;
  exit(0);
}

# If no table selected then get table name

  if ($cmd eq 'table' || $table eq ''){
  $db=new Win32::ODBC($DSN);
  #create new instance
  if (! $db){
   perror("Error opening data source $DSN");
    exit(0);
  }
  @tables=$db->TableList("","","","TABLE"); #Get list of tables
[download]

Could you show me where error is?

Comment on Data base connection error Select or Download Code

Replies are listed 'Best First'.
Re: Data base connection error by roboticus (Chancellor) on Aug 26, 2009 at 03:10 UTC
mhoang: You mention that you're using Windows authentication ... is the webserver running under account that has DB privileges? ...roboticus	[reply]
Re^2: Data base connection error by mhoang (Acolyte) on Aug 27, 2009 at 00:16 UTC
I changed to sql server authentication now, I set up myself as the sysadmin for sql server. Do you think i have missed something esle? Is the connection correct now? `my $db = new Win32::ODBC("DSN=portal-mk2", username="mhoang", password="test");`?	[reply] [d/l]
Re^3: Data base connection error by roboticus (Chancellor) on Aug 27, 2009 at 10:52 UTC
mhoang: I can't really tell. You just don't provide much information to work with. DSN configuration Part of the problem is that you're using a DSN to connect, and that hides a lot of information. With just a DSN name, I don't know a thing about your data source configuration. I try to avoid using DSNs and instead spell out the details for each connection, but that's just a personal preference. If you're going to use a DSN to connect to SQL server, though, you need to know a few things. For example, what sort of DSN is it? I'd suggest using a System DSN if you have a choice. If it's a User DSN, then you have to make sure you set up the DSN for the account the web server runs in in exactly the same way as the DSN on your account. Which ODBC driver does the DSN use? The SQL Native Client might be pretty good, but I've been using the SQL Server driver (v 2000.85.1132.00) for the past few years, and it works well connecting to SQL Server 2005. So just to debug your connection, I'd try using that one. Once you resolve your connection issues, you can then try moving to a different driver, if desired. I use the DBI module with DBD::ODBC and other modules to access databases, so I have no experience with Win32::ODBC, so if it has any quirks, you'll either have to delve into the documentation, or perhaps someone else might mention them. Error details The next part of the problem is that you don't specify the way that it doesn't work. Error messages are sort of handy. Normally they tell you what the problem is. So if you don't tell us the exact text of the error message, we don't know where to start looking for problems. For all we know, you could be using a command line on one computer and the script running on another computer with a different configuration, etc. ...roboticus	[reply]
Re^4: Data base connection error by mhoang (Acolyte) on Sep 03, 2009 at 02:51 UTC
Re: Data base connection error by james2vegas (Chaplain) on Aug 26, 2009 at 02:26 UTC
A few things spring to mind. If this is a remote database, is your web server running as a user that can access the network (`LocalSystem` cannot) and the Database (it isn't, I imagine, running as the same user as your initial script).	[reply] [d/l]

DSN configuration

Error details