What's wrong with my "DBI" code? I can NOT connect to the SQL server.
If you find my mistake, PLEASE be specific as possible with the syntax, please? (Even down to if I'm using the curley brackets correctly or not.)
I've been using DBI::Oracle for 2 years now, and have never seen a problem - but I just can't seem to use DBI to connect to the SQL server.
Thanks In Advance!
# Normal Perl Intro, /perl/bin/perl stuff goes here.
#
#
# Let The Users Know There Was A Problem
my $Error_Message = "\nThere Was A Problem Connecting To The Database\n";
my $q = new CGI;
use CGI;
# The Below Line - Takes Care Of Letting The User Know There Was An Error In The Web-Page!
use CGI::Carp (fatalsToBrowser);
use DBI;
# The Below Line Is Not Needed, Correct?
# use DBD::ODBC;
#
# print $q->header;
$dbusername = 'domain\user.name';
$dbpassword = 'password_here';
$server = 'server_name_here';
$database = 'Northwind';
$dbh = DBI->connect("DBI:ODBC:dri
die $DBI::errstr if ($DBI::errstr);
$sql_statement = ('select * from northwind.employees');
$statementHandle = $dbh->prepare($sql_stateme
$statementHandle->execute(
while (($cat1) = $statementHandle->fetchrow
{
print $cat1."\n";
}
$statementHandle->disconne
====================
Here Are My Errors Reported
====================
[Fri Oct 28 13:18:24 2005] test_mssql.cgi:
[Fri Oct 28 13:18:24 2005] test_mssql.cgi: There Was A Problem Connecting To The Database
[Fri Oct 28 13:18:24 2005] test_mssql.cgi: [Microsoft][ODBC SQL Server Driver]Client unable to establish connection (SQL-08001)
[Fri Oct 28 13:18:24 2005] test_mssql.cgi: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()). (SQL-01000
)(DBD: db_login/SQLConnect err=-1) at test_mssql.cgi line 54.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
also, i'm not sure, but i think your DSN syntax is wrong. try this:
my $dbh = DBI->connect("dbi:ODBC:dri
case DOES make a difference. for instance, trying to use the "pg" postgres driver will fail. but if you use the "Pg" postgres driver, it succeeds.
Thanks for "your code" ... I cut/pasted your line of code directly from EE here, and received the same error. (I don't think it IS connecting.)
If my syntax is (now) correct with your connect "code" - then I'm probably looking in the wrong place.
RE: Read CPAN's DBI manual?
Thanks for your troubles ... that's exactly where my other browser window is opened to as I type this response.
For what it's worth - one of the reasons to use (and pay for) EE is getting answers quickly and to have other programmers maybe shed some light on a missing comma or semicolon someplace.
Thus your suggestion to "Read The Manual For DBI", while a good suggestion, negates the purpose of EE. EE is just a quick way of getting what the boss would like done ASAP.
Truly withOUT sounding like I didn't appreciate your time - maybe a bit of patience on your tone in your responses is in line. (Then again, maybe you're having just as good a day as I am.)
well, perhaps your server isn't using named pipes for connecting. have you tried using TCP/IP?
hrm, here's a snippet that says to do just that:
Why do I get "[Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile())"
It is a resource issue when running a lot of processes (e.g. MultiProcess in the OOB Server) to the MS SQL Server ODBC driver issue. Switch to TCP/IP instead of named pipes in the MS SQL Server ODBC DSN and the problem will go away.
I see a number of issues that need to be addressed.
1) You're not using the strict pragma and you probably haven't enabled warnings.
2) You're creating your cgi object before the importing the CGI module.
3) You commented out the line that prints the html header, so it's unclear if this is or is not a cgi script.
4) Is the name of your database 'Northwind' or 'northwind'? Case matters when connecting.
5) Your select statement implies that you're retrieving multiple fields, but your while loop is only using 1 field.
6) You're performing the disconnect on the statement handle instead of the database handle.
See if this version works better.
#!/usr/bin/perl
use strict;
use warnings;
use CGI;
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);
use DBI;
my ($dbh, $sth);
my $Error_Message = "\nThere Was A Problem Connecting To The Database\n";
my $driver = 'database driver'; # e.g., mysql or ODBC or ???
my $dbusername = 'domain\user.name';
my $dbpassword = 'password_here';
my $server = 'server_name_here';
my $database = 'Northwind';
my $q = new CGI;
print $q->header;
warningsToBrowser(1);
$dbh = DBI->connect("dbi:$driver:
{'RaiseError' => 1, 'PrintError' => 1}
) || die "$Error_Message $DBI::errstr";
$sth = $dbh->prepare('select * from employees');
$sth->execute() || die $sth->errstr;
while (my @cat1 = $sth->fetchrow_array ) {
print "@cat1", "\n";
}
$dbh->disconnect();
Here's a little info on your 8001 error message.
8001: The Services DLL encountered an error while allocating memory
Memory could not be allocated. Verify that your system resources are not exhausted.
Here's a short script that will show what drivers and data sources you have available.
#!usr/bin/perl -w
use DBI;
@driver_names = DBI->available_drivers;
@data_sources = DBI->data_sources('ODBC');
print "Driver names: @driver_names",$/,$/;
print $_,$/ for @data_sources;
FishMonger,
PERFECT!
Actually, your second script, which demonstrated which resources were available for me, is what did the the trick...!
Thus, I've discovered my problem - I'm NOT connecting to a "remote" host - I've only the LOCAL host Databases, using ODBC, to connect to. At least that's what the reported "Available Connections Were For Me."
What/If there is a syntax to connect to a 'remote' SQL Server ???
Thanks for your time, now that I've discovered the problem, just need to (correctly) connect to a remote SQL Server.
Any light you might be able to shed there?
Even so, I'll give the points here - you've given me enough to look in the right places, again. (Instead of WONDERING why I'm not connecting, now I know!)
Business Accounts
Answer for Membership
by: punkpulsePosted on 2005-10-28 at 10:41:27ID: 15181096
http://msdn.microsoft.com/
01000 is "general warning" -- your connection was successful. your problem is that you are checking for the existance of DBI->errstr rather than the defined value of $dbh. you should never try to check for an error condition setting unless the function has failed. since you never check the value of the DBI::connect call, your logic is flawed.