ODBC problem

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

Hi, I have to access an old Sage MAS 90 database, I can connect fine with an ODBC query tool using the connection string "Driver={MAS 90 4.0 ODBC Driver};Directory=S:\v440\MAS90" But when I try with Perl I get:

use strict;
use warnings;
use DBI;
my $dbuser = "user";
my $dbpassword = "password";

# SOTAMAS90
my $CONNECT = 'Driver={MAS 90 4.0 ODBC Driver};Directory=S:\v440\MAS90
+';
my $dbh = DBI->connect("dbi:ODBC:$CONNECT", "$dbuser", "$dbpassword")
  or die "Cannot connect to $CONNECT: $DBI::errstr\n";
$dbh->disconnect
[download]

It fails with:

C:\>test_1.pl
DBI connect('Driver={MAS 90 4.0 ODBC Driver}','user',...) failed: [Mic
+rosoft][ODBC Driver Manager] Data source name not found and no defaul
+t driver specified (SQL-IM002) at C:\test_1.pl line 11.
Cannot connect to Driver={MAS 90 4.0 ODBC Driver}: [Microsoft][ODBC Dr
+iver Manager] Data source name not found and no default driver specif
+ied (SQL-IM002)
[download]

The driver is a 32bit on (if that's relevant) I've also tried using its user DSN name SOTAMAS90 instead but that fails as well. I've never used ODBC before and am not used to working on Windows so any help to helping me connect would be great!

Comment on ODBC problem Select or Download Code

Replies are listed 'Best First'.
Re: ODBC problem by afoken (Canon) on Feb 24, 2021 at 14:10 UTC
Are you trying to use a 32 bit ODBC driver on a 64 bit Windows? Then you have to use the "hidden" ODBC driver manager for 32 bit, see DBD::ODBC::FAQ. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re: ODBC problem by marto (Cardinal) on Feb 24, 2021 at 13:57 UTC
When using ODBC it should just be the DSN IIRC, have you tried something like this: `my $dsn = 'SOTAMAS90'; my $dbh = DBI->connect("dbi:ODBC:$dsn", $dbuser, $dbpass) or die "derp +: $DBI::errstr\n";` [download]	[reply] [d/l]
Re: ODBC problem by Corion (Pope) on Feb 24, 2021 at 13:59 UTC
Just in case that the backslash in your single-quoted string gets interpolated, I would try forward slashes in the path: `my $CONNECT = 'Driver={MAS 90 4.0 ODBC Driver};Directory=S:/v440/MAS90 +';` [download] ... or escaped backslashes: `my $CONNECT = "Driver={MAS 90 4.0 ODBC Driver};Directory=S:\\v440\\MAS +90";` [download] In any case, I would print `$CONNECT` to debug whether it has the value you expect.	[reply] [d/l] [select]
Re: ODBC problem by hippo (Chancellor) on Feb 24, 2021 at 20:03 UTC
`my $CONNECT = 'Driver={MAS 90 4.0 ODBC Driver};Directory=S:\v440\MAS90 +'; my $dbh = DBI->connect("dbi:ODBC:$CONNECT", "$dbuser", "$dbpassword")` [download] The synopsis suggests it should be `DSN=` and not `Driver=`. 🦛	[reply] [d/l] [select]
Re: ODBC problem by Jonathan (Curate) on Mar 02, 2021 at 13:08 UTC
I posted the original question (thanks for the link to retrieve my old account). I got this working by creating a System DSN entry (I had been trying with a User DSN previously) which included the path to the database `my $dbh = DBI->connect('dbi:ODBC:MAS90', $dbuser, $dbpassword) or die "Cannot connect: $DBI::errstr\n";` [download] This worked, apologies as this wasn't really a Perl issue	[reply] [d/l]
Re: ODBC problem by Anonymous Monk on Feb 25, 2021 at 09:59 UTC
Thanks for all the replies, turns out the problem was my 32bit ODBC driver wasn't happy with Activestate's 64bit Perl, I couldn't find a work around so removed Activestate and replaced it with Strawberry Perl 32bit and I can now connect successfully. I used to spend a lot of time on this site but can no longer access my account (long forgotten/lost email address). I still write a lot of Perl, along with Golang and NodeJS but it's good to be back even as an anonymous monk. Thanks again.	[reply]
Re^2: ODBC problem by LanX (Cardinal) on Feb 25, 2021 at 10:45 UTC
See Retrieving a forgotten username or password Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery}	[reply]
Re: ODBC problem by Anonymous Monk on Feb 25, 2021 at 15:50 UTC
stackoverflow.com/questions/57196641/sql-im002-errors-while-connecting-to-sql-server-from-perl-on-heroku	[reply]
Re: ODBC problem by Anonymous Monk on Feb 24, 2021 at 18:05 UTC
Well, do you supply the data-source name? I don't see it ...	[reply]