Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: Win32::ODBC Connect to MSSQL

    Hi,

    I wanted to know if there is a way to connect to my MSSQL without having to setup a datasource in the control panel.

    Perferably I want to locate the database within my perl code instead. I don't want to have to setup a datasource each time I need a different database. Perhaps there is a way to pass the name of the database along with the username and password through the command line.


    Any suggestions?

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest something like:
    Code:
    #  ptp  20040419  Demo Perl ODBC connectivity
    
    use Win32::ODBC;
    
    my %data, $db, $DSN, $i, $rc;
    
    $DSN = "PatP";
    $DSN = "Driver={SQL Server};Trusted_Connection=yes;Server=desk_pphelan\\sql2000;Database=PatP;";
    
    if (!($db = new Win32::ODBC($DSN))){
        print "Error connecting to $DSN\n";
        print "Error: " . Win32::ODBC::Error() . "\n";
        exit;
    };
    
    if ($db->Sql("EXECUTE sp_who2")){
        print "SQL failed.\n";
        print "Error: " . $db->Error() . "\n";
        $db->Close();
        exit;
    };
    
    $i = 0;
    
    while ($db->FetchRow()) {
       undef %data;
       %data = $db->DataHash();
    
       $i++; print "\n$i: ";
       while (($key, $value) = each %data) { print "$key=<$value>\n" };
       };
    
    $db->Close();
    You just gotta love a scripting language that allows you to open ODBC connections, don't ya ???

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    6

    Smile

    Thanks for your suggestion I will try it right away.

  4. #4
    Join Date
    Apr 2004
    Posts
    6

    confused

    I am very new to perl and win32:dbc so maybe I am doing something wrong.

    Am I supposed to be in a certain directory in perl to run the code? For example should I be in the Win32 directory? Does it matter that I am using MSDE 2000 Release A? Also, what does this "Sql("EXECUTE sp_who2")" do?

    Perhaps, I should start basic first. I have a data source that I created last week called "mssqlConnection". The login is the default "sa" with no password.

    Sorry if the questions are really elementary.
    Thanks for the help.
    -Laura

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Re: confused

    Originally posted by lauram
    I am very new to perl and win32:dbc so maybe I am doing something wrong.

    Am I supposed to be in a certain directory in perl to run the code? For example should I be in the Win32 directory? Does it matter that I am using MSDE 2000 Release A? Also, what does this "Sql("EXECUTE sp_who2")" do?

    Perhaps, I should start basic first. I have a data source that I created last week called "mssqlConnection". The login is the default "sa" with no password.

    Sorry if the questions are really elementary.
    Thanks for the help.
    -Laura
    Sorry, you need to butcher the line that reads
    PHP Code:
    $DSN "Driver={SQL  Server};Trusted_Connection=yes;Server=desk_pphelan
    \\sql2000;Database=PatP;"

    to suit your needs. I'm using a Trusted (NT) connection, connecting to my own machine, using a scratch database. You'll need to edit the line to whatever parameters are appropriate for your situation.

    You REALLY want to avoid using sa with no password. That is a receipe for disaster. Please change your sa password NOW!

    You can run the Perl script from wherever you like. There is no directory dependance at all.

    The line that reads
    PHP Code:
    $db->Sql("EXECUTE sp_who2"
    simply executes a command on the SQL Server.

    -PatP

  6. #6
    Join Date
    Apr 2004
    Posts
    6
    Oh, okay. I understand now. Thanks.

    -Laura

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •