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

I'm tryin to connect to a MSSQL Database. Any suggestions .... this code i made doesn't seem to be working. Thanks in advance. 
$dbh = DBI->connect("dbi:ADO:$database",$user, $password);
 || die "Cannot connect: $DBI::errstr\n" unless $dbh
  return;
$dbh->disconnect();

[download]

Replies are listed 'Best First'.
Re: Connection to a MSSQL Database
by Moonie (Friar) on Jan 13, 2002 at 07:37 UTC
    You may want to try using Win32::ODBC. Or if you wish to stick to using DBI, you may want to use ODBC instead of ADO...OR take grep's advice - take out the semicolon. 
    $dbh = DBI->connect("dbi:ADO:$database",$user, $password)
 or die "Cannot connect: $DBI::errstr\n";
$dbh->disconnect();

    [download]

    - Moon
[reply]
[d/l]
Re: Connection to a MSSQL Database
by simon.proctor (Vicar) on Jan 13, 2002 at 19:41 UTC
    If you can you should stick to the ODBC driver under the DBI as much as possible. Otherwise I would recommend that you use the Win32::OLE module and create recordsets to the database.

    The OLE method should only be a fall back position (though it can't always be helped) as your function calls are no longer DBI based. Hence this affects your portability.

    Two examples of OLE code can be found in the following:

    Or heres snippet from some recent code I have been using. This code selects information from a Memo field. 
    use constant    DATA    =>    'driver=Microsoft Access Driver (*.mdb);
+dbq=data.mdb';

my $db_connection = new Win32::OLE('ADODB.Connection');
    my $rs = new Win32::OLE("ADODB.Recordset");

    # Connect to the database and tie the recordset object to 
    # the database.
    $db_connection->Open(DATA);


    # Set the connection doohickey
    # $rs->CursorLocation(adUseClient);

    my $sql = "select c.info from MYTABLE as c;";
    
    $rs->Open($sql, $db_connection, adOpenKeySet);
    
    if($rs->BOF())
    {
        return;
    }

    my $data = $rs->getrows();
    
    return unless defined($data->[0][0]);
    return $data->[0][0];

    [download]


    Ok thats with access but it should be the same for MSSQL
[reply]
[d/l]
Re: Connection to a MSSQL Database
by grep (Monsignor) on Jan 13, 2002 at 07:11 UTC
    You dsn line should say "dbi:mysql:$database"

    From the docs:
    $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
    You can access the DBD:mysql docs by 'perldoc DBD::mysql'

     You have a semicolon ending the line "dbi:ADO:$database",$user, $password); This invalidates your || die statement.

    BTW unless you are using the tight binding of '||' you should use 'or'.

    UPDATE: as IlyaM pointed out I misread MSSQL as mysql. Then the semicolon looks like the answer.
    BTW: the docs for DBD::ADO are 'perldoc DBD::ADO'

    grep 
    grep> cd pub
grep> more beer

    [download]
[reply]
[d/l]
[select]
[reply]
Re: Connection to a MSSQL Database
by MiRaGe 508 (Novice) on Jan 13, 2002 at 09:49 UTC
    so r u sayin my code looks fine? For MSSQL?
[reply]
Re: Connection to a MSSQL Database
by MiRaGe 508 (Novice) on Jan 13, 2002 at 10:02 UTC
    is there a driver for MSSQL built into perl?
[reply]
Re: Connection to a MSSQL Database
by MiRaGe 508 (Novice) on Jan 14, 2002 at 00:39 UTC
    I can't seem to use win32:ole on this machine. I actually tried that before. I also tried using OLE on the machine and ODBC. Nothing seemed to work accept DBI. And did i mention i don't have control over the machine.
[reply]

Back to Seekers of Perl Wisdom