New to DBI Help making connection

[ChewDoggie]

Hello all,

I'm an old-hat VB6 developer who's trying to write my first Perl DBI script. In VB6, one would create a connection object ("cn", e.g.), instantiate it, create your connection string and the pass it to the "Open" method to open a connection to a database.

I've been reading the tutorial suggested by the Perl FAQ located at

http://www.perl.com/pub/a/1999/10/DBI.html

and it has been VERY helpful. It says to provide the database driver for the database. So, my code currently looks like this for connecting to an Access Database:

use DBI;

use strict;

my ($dbh,
    $sth,
    $dir,
    @row);

$dir = "C:/Program Files/Microsoft Visual Studio/VB98/EMS.MDB";
$dbh = DBI->Connect("DBI:CSV:f_dir=$dir") or die "Couldn't connect to the daggone database" . $dbh->errstr . "\n";
$sth = $dbh->prepare('Select * from classes') or die "Couldn't prepare the daggone sql string" . $dbh->errstr . "\n";
$sth->execute();

And I keep receiving this error:

Can't locate auto/DBI/Connect.al in @INC (@INC contains: C:/perl/site/lib C:/perl/lib .) at dbi.pl line 11
at C:/perl/lib/AutoLoader.pm line 47
AutoLoader::AUTOLOAD('DBI', 'DBI:CSV:f_dir=Crogram FilesMicrosoft Visual StudioVB98MS.MDB') called at dbi.pl line 11

Do I have to include a a "use lib" command? I thought that the "use DBI" statement was sufficient. I went to both of those directories and there's no "Connect.al" file or directory. BTW, I'm on a Windows XP machine running ActivePerl. I installed DBI via the PPM utility that comes with ActivePerl. Can anyone assist me with my errant code?

Thanks!

Chewdoggie

The True measure of an individual is how he treats a person who can do him absolutely no good.

 

[sycoogtit]

DBI->Connect should be DBI->connect.

--

http://MyUniversityTutor.com

 

[ChewDoggie]

OK, that was a big help for sure. Now the next and final question...I have SQL Server 2000 installed on my machine as well (not a "MySQL"). What driver to I specify for that database? Does DBI even support the SQL 2000 ?

Thanks, sycoogtit !

Chewdoggie

The True measure of an individual is how he treats a person who can do him absolutely no good.

 

[KevinADC]

You probably want DBD::ODBC

------------------------------------------
- Kevin, perl coder unexceptional!

 

[ChewDoggie]

Thanks, Kevin.

I was able to make a successful connection to the SQL 2000 DB but the "execute" statement fails with the following Error:

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Registration'. (SQL-42S02) [state was 42S02 now 42000]
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000) at dbi.pl line 11.

Here's my code:

use DBI;

use strict;

my ($dbh,
    $sth,
    $dir,
    $Columns,
    $i,
    @row);

$dbh = DBI->connect('DBI:ODBC:DRIVER={SQL Server};Server=MYSERVER;attr=database=EMS') or die "Couldn't connect to the daggone database" . $dbh->errstr;
$sth = $dbh->prepare('Select * from Registration') or die "Couldn't prepare the daggone sql string" . $dbh->errstr;
$sth->execute();
while (@row = $sth->fetchrow_array)
	{
        for ($i = 0;$i <= $#row;$i++)
                {
                print "$row[$i]\n";
                }
	}
$sth->finish();
$dbh->disconnect();

The 'Registration' table exists in the EMS database and has records in it.

Thanks for all your help.

Chewdoggie


10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[ChewDoggie]

OK, I got it.

I changed the "prepare" statement to read:

$sth = $dbh->prepare('Select * from EMS.dbo.Registration') or die "Couldn't prepare the daggone sql string" . $dbh->errstr;

and it returned records. WOOHOO!!!

Thanks to all !

Chewdoggie


10% of your life is what happens to you. 90% of your life is how you deal with it.