billfinkri
asked on
Using Perl DBI Connecting To MS-SQL
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.
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.
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.
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.
ASKER
Soooo...what does this mean? (Please?)
I'm afraid that I'm not familiar with this sort of syntax.
Then what/where should I change the code?
Thanks!
I'm afraid that I'm not familiar with this sort of syntax.
Then what/where should I change the code?
Thanks!
i just showed you the code! replace the DBI->connect line that you have with the one i just gave you. if you "have been using DBI::Oracle for 2 years now", you shouldn't have to ask me that question.
that syntax is the de facto DBI syntax. read the manual for DBI.
that syntax is the de facto DBI syntax. read the manual for DBI.
ASKER
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.)
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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;
ASKER
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!)
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!)
ASKER
FishMonger,
(Got IT!) Thanks for all of your time!
BTW - 'bout those lines I listed above ... there were a number of them, only through trial-error did I comment a few out, move a few around, just for testing.
Thus, I realized some of the code may have looked a bit odd.
Bill
(Got IT!) Thanks for all of your time!
BTW - 'bout those lines I listed above ... there were a number of them, only through trial-error did I comment a few out, move a few around, just for testing.
Thus, I realized some of the code may have looked a bit odd.
Bill
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.