Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

Using DBI to make connection to a database

by campbell (Beadle)
on Jul 02, 2015 at 08:22 UTC ( #1132964=perlquestion: print w/replies, xml ) Need Help??

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

Greetings O enlightened ones

I have a problem with opening a handle to a database using the DBI Perl module, since, alas, I am a total DBI noob. I would figure it out myself, but I'm in a hurry...

I am using Strawberry Perl version 5.22 and Windows 7.

With the help of Windows' ODBC Data Source Administrator, I have configured a system DSN (named $myDSN), using the windows native ODBC driver (Version 06.01.7601) that connects to a MS-SQL database (named $db) on a remote server (named $srvr) on port $port with username $usr and password $pwd. I have tested the connectivity using the ODBC administrator, and it is fine.

The question is, how do I then get the DBI Perl module to use this information to set up a filehandle so that I can query / retrieve on the database?

I've tried to follow the syntax given in CPAN and (perhaps naively) tried the following:

my $dbh=DBI->connect("dbi:ODBC:Server=$srvr; UID=$usr;PWD=$pwd");
$DSN= 'dbi:DriverName:database=$db;host=$host;port=$port;' my $dbh=DBI->connect($DSN);

...which just results in various error messages to the effect that a connection isn't happening.

These error messages are, respectively:

DBI connect('variansystem; Server=10.1 75.68.28; UID='reports';PWD='reports'','',...) failed: [Microsoft][ODB +C Driver M anager] Data source name not found and no default driver specified (SQ +L-IM002) a t C:/Users/rpuser/Documents/RTDS/ line 232.
install_driver(DriverName) failed: Can 't locate DBD/ in @INC (you may need to install the DBD:: +DriverName module) (@INC contains: C:/Strawberry/perl/site/lib C:/Strawberry/per +l/vendor/l ib C:/Strawberry/perl/lib .) at (eval 16) line 3.

I'm obviously using the completely wrong syntax / approach (maybe I need to set enviroment variables or something?), but I haven't got the time to figure it out myself (which would obviously be more satisfying).

Thanks in advance.

Replies are listed 'Best First'.
Re: Using DBI to make connection to a database
by choroba (Cardinal) on Jul 02, 2015 at 08:28 UTC
    Please, show the error messages, so we can do more than just guess.

    Here's how we connect to a MS SQL Server at work:

    my $db = 'DBI'->connect("DBI:ODBC:Driver={SQL Server};Server=$serv +er;Database=$database", $user, $secret, @options);

    Have you installed DBD::ODBC?

    لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
      OK - now updated (sorry - I wasn't sure if I may have been muddying the waters by including the error messages as it seemed a fairly clear-cut case of using the wrong syntax.)

        So, have you installed DBD::DriverName? Or maybe you need to adapt your DSN string to use dbi:ODBC if you are on Windows and want to use DBD::ODBC?

Re: Using DBI to make connection to a database
by Corion (Patriarch) on Jul 02, 2015 at 08:40 UTC
    $DSN= 'dbi:DriverName:database=$db;host=$host;port=$port;'

    Have you verified that $DSN contains what you think it should contain?

    print "Connecting to [$DSN]\n";

    This might help you to find out whether the value of $DSN is what you think it is.

Re: Using DBI to make connection to a database
by mje (Curate) on Jul 02, 2015 at 09:13 UTC

    The syntax for a DBI connection string is 'dbi:DRIVERNAME:stuff' where DRIVERNAME is the name allocated to the DBD (database driver you want to use) in DBI. Whatever, you put for DRIVERNAME will cause DBI to attempt to load DBD::DRIVERNAME and hence when you used 'dbi:DriverName' you got "you may need to install the DBD::DriverName error. For DBD::ODBC, the DRIVERNAME should be 'ODBC'. So, your connection string should start 'dbi:ODBC'.

    After the 'dbi:ODBC' you add a further ':' and then usually follow it with a series of name=value pairs separated by semi colons ';'. So now your connection string looks like 'dbi:ODBC:attr1=value;attr2=value;'. DBD::ODBC (actually in this case ODBC more precisely) defines the attribute names you can use so basically, after 'dbi:ODBC:' you add whatever attributes ODBC and your ODBC driver define to connect to your ODBC data source. The most common and most simple thing to do is as you did and create a DSN (data source name) in the ODBC administrator and then you'd use 'dbi:ODBC:DSN=name_of_my_dsn' as the connection string (DBD::ODBC also supports for this case only 'dbi:ODBC:name_of_my_dsn' as back in time ODBC did not support attributes).

    If you don't want to create a system or user ODBC DSN you can use so called DSN-less connection strings. In this case you have to tell the ODBC driver manager which ODBC driver to use )in much the same way you tell DBI which DBD to load) and then you tell the ODBC driver which database to connect to e.g., 'dbi:ODBC:DRIVER={MS SQL Server Driver};Server=mydatabaseserver;Database=mydatabase;' like the example already provided in this thread. In this case you should consult your ODBC driver documentation for driver specific attributes and the ODBC documentation for what ODBC defined connection attributes there are (e.g., DSN, DRIVER, FILEDSN). NOTE: a) I made up "MS SQL Server Driver", you need to use the string listed in the ODBC administrator under the drivers tab b) {} are used in ODBC connection strings to delimit values which may contain spaces or other punctuation.

    Lastly, you need to be aware that since you are on Windows you might have a 64bit machine. As the ODBC driver is really a DLL (code wrapped up in a way that allows it to be dynamically loaded into a program) you cannot mix 32 bit apps with 64bit DLLs and vice versa. If your perl is 64 bit then you need to create your DSNs with the 64 bit ODBC driver manager setup dialogues and if your perl is 32 bit you need to use the 32 bit ODBC driver manager. If you hit this issue do a search for where to find the 2 different ODBC driver managers.

    Hope this helps although I imagine by now you've read some documentation or searched online which would probably have been quicker all round than posting here for a "quick" answer.

Re: Using DBI to make connection to a database
by fishmonger (Chaplain) on Jul 02, 2015 at 10:08 UTC

    With the help of Windows' ODBC Data Source Administrator, I have configured a system DSN (named $myDSN)

    Since you've already created the DSN via Windows ODBC Data Source Administrator, you just need to use that $myDSN var in your connection string. The ODBC driver will use that name to lookup the connection details (i.e., the specific driver, server name, port, and db name)

    my $myDSN = 'data_source_name'; my $dbh = DBI->connect("DBI:ODBC:$myDSN", 'user', 'password', { RaiseError => 1}) or die DBI->errstr;
Re: Using DBI to make connection to a database
by 1nickt (Canon) on Jul 02, 2015 at 08:48 UTC

    I would figure it out myself, but I'm in a hurry...

    Dude. Really? So everyone else should drop everything and hurry to help you? I would say that if you are in too much of a hurry to figure it out for yourself, you have a deeper problem than can be solved here.

    However, you did provide some halfway decent problem data, so ...

    Use Data::Dumper to check your data as you build and debug your program. (For a scalar, you can simply print() it.) Your $DSN is probably not what you think it is. Been about 20 years since I used ODBC, but it looks like you are passing your username and password with quotes around them.

    Remember: Ne dederis in spiritu molere illegitimi!
      Sorry - didn't mean it that way, just meant that as I'm a newbie with DBI it may take me a long while (that I don't have) to get it cracked, and that if an uber-user could crack it in a few seconds, that would be miles more efficient...

        No worries. Nice of you to reply back.

        It's common to have these sorts of problems when trying to connect to a DB with the DBI. The problems usually have to do with escaping and interpolation in perl. Try these links given in a recent thread. Because you can connect to your DB using ODBC manager or whatever, with the same credentials you think you are giving in your $DSN, it's tricky to understand what the problem could be. Often the DBI error message is long and/or incomplete or confusing. As I say, this is a common DBI beginner's mistake.

        What you can learn here are ways to become a better programmer, not just immediate solutions to what you're stuck on (although you'll often get those if you ask nicely).

        So that's why my first answer (and that of a another respondent) was to advise you to start debugging your program as you write it. Print out $DSN and you will see if it has any quoting/interpolation problems. Get into the habit of examining all your data as you develop your program.

        One way to do this is declare a global variable that turns debugging on or off, and then you can have conditional debugging statements in your code that are only printed when the switch is on. (Using && to test true for the first half of the statement before executing the second half...)

        #!/usr/bin/env perl -w use strict; use 5.010; use DBI; my $DEBUG = 1; &db_connect( 'foo', 'bar', 'baz' ); sub db_connect { my ($srvr, $usr, $pwd) = @_; my $DSN = "dbi:ODBC:Server=$srvr; UID=$usr;PWD=$pwd"; $DEBUG && say $DSN; my $dbh=DBI->connect($DSN, { RaiseError => 1 }); } __END__

        I write most code with this built in, whether the debug flag is set at the top of a script, or in a config file, or in an environment variable, or whatever. Change one value somewhere and the whole thing turns on or off. You can use it to enable/disable logging, send/not send email while testing, whatever. And you can easily have more than one level of debugging and "escalate" certain things that you really want to examine when all else is working. Once it's working okay, set $DEBUG to 0 and it shuts up. Next time there's a problem, or you want to add or change something, it's all there ready to turn back on.

        Good luck!

        Remember: Ne dederis in spiritu molere illegitimi!
Re: Using DBI to make connection to a database
by soonix (Canon) on Jul 03, 2015 at 06:10 UTC
    $DSN= 'dbi:DriverName:database=$db;host=$host;port=$port;'
    This won't work as you want. See Quote and Quote-like Operators in perlop. Generally, it's a good idea to add print statements as per Corion's advice. Not only for the DSN, also later for SQL statements. Instead of
    $sth = $dbh->do('SELECT whatever ...');
    it's better do write
    my $sql = 'SELECT whatever ...'; print "Statement: [$sql\n]"; $sth = $dbh->do($sql);
    I myself prefer
    use Data::Dumper; ... my $sql = 'SELECT whatever ...'; print Dumper $sql; my $sth = $dbh->prepare($sql) or die "prepare failed: ', $DBI::errstr; ...
    because if I have a reference somewhere, I see directly what that points to.

      That is bad advice!

      Please do not create SQL statements with fixed strings and do (remember bobby?)

      I agree with the wrong quotes being used (of course). Shell quotes are not perl quotes on Windows at least.

      If you want to see the statement used when something fails, use the appropriate means:

      my $dbh = DBI->connect ("dbi:Pg:", $user, $pass, { RaiseError => 1, PrintError => 1, ShowErrorStatement => 1, }) or die $DBI::errstr; my $sth = $dbh->prepare ("select foo from bar where boom = ?"); $sth->execute (1);

      Enjoy, Have FUN! H.Merijn

        I did not intend to advise creating SQL with fixed strings if you have variable parts. This was just an example (and didn't contain any variables in the SQL).

        And if you have things like q(SELECT ConfigValue FROM ConfigTable WHERE ConfigKey = 'ScreenWidth'), where you have neither placeholder nor variable interpolation, then there's other problems than simply the fixed string :-)

        Plus in the "I prefer" part I used prepare anyway…

        Update: Of course, $dbh->do("SELECT ... is very bad practice per se, because SELECT COUNT(*) ... usually is more efficient ;-) (see do in DBI) … used it for illustration purposes only.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1132964]
Approved by Athanasius
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (4)
As of 2022-12-02 23:20 GMT
Find Nodes?
    Voting Booth?