Database connection issue

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

The issue I am having with perl is simply in regard to calling a database from perl. Perhaps my code is not long enough or I'm not coding it properly. I'm attempting to connect to a database that resides in SQL Server 2014 and I'm attempting to access it locally. My code is as follows:

  #!/usr/bin/perl
use strict;
use warnings;

use DBI;

my $AJsql = "AJsql";

my $dsn = "dbi:ODBC:dsn=$AJsql";

my $dbh = DBI -> connect("dbi:ODBC:$AJsql;SERVER=CND4290H26") or die 
"Couldn't open database";


my $sth = $dbh->prepare(q{SELECT * FROM Games}) or die
"Couldn't create statement";

$sth->execute() or die "couldn't execute";

print $sth;


$dbh->disconnect;
[download]

The error I'm continually getting is, "Data source name not found and no default driver specified. <SQL-IM002> at script.pl line 11." That error is specifying directly to the connect statement. Any help at all is appreciated.

Comment on Database connection issue Download Code

Replies are listed 'Best First'.
Re: Database connection issue by Corion (Patriarch) on Nov 14, 2015 at 22:53 UTC
The ODBC data source is not found. This can have various causes. Maybe you have misspelled it. Start `odbcad32.exe` and check whether your ODBC data source name really is `AJsql`. Maybe you created the data source using the 64-bit `odbcad32.exe` but are using a 32-bit Perl. Or you are using a 64-bit Perl but want to connect to a 32-bit ODBC data source. Neither will work. You will need to match the bitness of the ODBC administrator program and your Perl. Have you looked at https://www.connectionstrings.com to maybe find the appropriate connection string to avoid the need for configuring a ODBC data source alltogether?	[reply] [d/l] [select]
Re^2: Database connection issue by Azard (Initiate) on Nov 14, 2015 at 23:11 UTC
I double checked the ODBC and it is spelled correctly. The source itself says that it's on both 32-bit and 64-bit platforms, so I don't think that's the issue. Could be, but I don' think so. I checked the link you helpfully provided but it nets me with another error altogether. The code would look like this, `my $dbh = DBI -> connect("Driver={SqlServer};SERVER=CND4290H26;Databa +se=GameInfo;Trusted_Connection=Yes;") or die "Couldn't open database";` [download] That gives me the error: Can't connect to data source because I can't work out what driver to use (It doesn't seem to contain a 'dbi:driver:' prefix and the DBI_DRIVER env var is not set) at script.pl line 11. When I put back the DBI:Driver, I get the same error I initially posted.	[reply] [d/l]
Re^3: Database connection issue by Corion (Patriarch) on Nov 15, 2015 at 07:53 UTC
That's expected. You will need to combine your reading of DBI together with the information from connectionstrings.com. You will need to add the correct DBD information in front of the connection string: `my $dsn = "dbi:ODBC:Driver={SqlServer};SERVER=CND4290H26;Database=Game +Info;Trusted_Connection=Yes;"; my $dbh = DBI->connect( $dsn ) or die ...;` [download]	[reply] [d/l]
Re^4: Database connection issue by Azard (Initiate) on Nov 16, 2015 at 13:16 UTC
Re^5: Database connection issue by Corion (Patriarch) on Nov 16, 2015 at 14:11 UTC
Some notes below your chosen depth have not been shown here
Re: Database connection issue by Pope-O-Matik (Pilgrim) on Nov 15, 2015 at 00:53 UTC
SELECT FROM Games* As an aside, * is best not used outside of COUNT(), EXISTS(), and ad-hoc queries. A column list would self-document the code, and prevent any issues that may arise from column changes, such as adding, deleting, or reordering.	[reply]
Re^2: Database connection issue by LanX (Saint) on Nov 15, 2015 at 02:48 UTC
> prevent any issues that may arise from column changes, such as adding, deleting, or reordering well using DBI->fetchrow_hashref should solve this, except maybe by not rasing an instant error for deleted columns (but the code would need to be rewritten in that case anyway.) Cheers Rolf _{(addicted to the Perl Programming Language and ☆☆☆☆ :) Je suis Charlie!}	[reply]