Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

connecting Perl with MS SQL Server on NT using ODBC

by peacemaker1820 (Pilgrim)
on Jul 01, 2002 at 17:32 UTC ( #178615=perlquestion: print w/ replies, xml ) Need Help??
peacemaker1820 has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks.

This is the code I have

print "HTTP/1.0 200 OK\n"; print "Content-Type: text/html\n\n"; print "<HTML><HEAD><TITLE></TITLE></HEAD>\n"; print "<BODY>\n"; use DBI; use CGI; my $DSN = 'driver={SQL Server};Server=SRV01;Database=SDPSI;UID=my_id;P +WD=my_pswd'; #Connect the database handle. $dbh = DBI->connect("DBI:ODBC:$DSN") or die "$DBI::errstr\n"; #If connected, print... print("It's in."); #Close connection when finished: $dbh->disconnect; print "hello there"; print "</BODY>\n"; print "</HTML>\n";

When I compile this code on my machine it gives me the following error:

DBI->connect(driver={SQL Server};Server=SRV01;Database=SDPSI;uid=u_id;pwd=u_pswd ) failed: MicrosoftODBC Driver Manager Invalid string or buffer length (SQL-S1090)(DBD: db_login/SQLConnect err=-1) at database.pl line 12 MicrosoftODBC Driver Manager Invalid string or buffer length (SQL-S1090)(DBD : db_login/SQLConnect err=-1)

Does ANYONE know what to do???

Edit by dws to add code tags

Comment on connecting Perl with MS SQL Server on NT using ODBC
Download Code
Re: connecting Perl with MS SQL Server on NT using ODBC
by Mission (Hermit) on Jul 01, 2002 at 18:09 UTC
    peacemaker1820, I had to include the IP address, Port number, and the domain\server in order to get the MS SQL to work.

    Check to make sure that you have current modules as well...
    PPM>install DBI PPM>install DBD-ODBC

    Here is some sample code to show you what works for my environment (W2K) but it should be similar (if not identical.)

    #!/usr/bin/perl -w use strict; use DBI; print "Content-type: text/html\n\n"; # Set up variables for the connection my $server_name = 'domain\server'; my $server_ip = '1.2.3.4:port'; my $database_name = 'Tim'; my $database_user = 'Taylor'; my $database_pass = 'toolman'; my $DSN = 'driver={SQL Server};server=$server_name;tcpip=$server_ip;da +tabase=$database_name;uid=$database_user;pwd=$database_pass;'; my $dbh = DBI->connect("dbi:ODBC:$DSN") || die "Couldn't open database +: $DBI::errstr\n"; # Prepare the SQL query for execution my $SQL1 = $dbh->prepare(<<End_SQL) || die "Couldn't prepare statement +: $DBI::errstr\n"; select * FROM Test_Table End_SQL print '<table border="0" width="100%" cellpadding="0" cellspacing="0" +bgcolor="#ffffff" summary=""><tr><td align="center">'; print '<table border="1" cellpadding="5" cellspacing="0" bgcolor="#fff +fff" summary="">'; # Execute the query $SQL1->execute() || die "Couldn't execute statement: $DBI::errstr\n"; # Fetch each row and print it while ( my ($field1,$field2,$field3,$field4,$field5) = $SQL1->fetchrow +_array() ) { print "<tr><td>$field1</td><td>$field2</td><td>$field3</td><td>$f +ield4</td><td>$field5</td></tr>"; } print "</table></td></tr></table>"; # Disconnect from the database $dbh->disconnect();

    Also, make sure that you use:
    #!/usr/bin/perl -w use strict;
    Use the search at the top for additional help on those and other topics (it's been discussed numerous times.) Also see the node: How to RTFM it's a really good insite to finding Perl help.
    HTH

    - Mission Updated: fixed my typos (again.)
      Excuse me... I tried many time using your sample code for ODBC, but there is an error everytime i tried to use this code:
      #!/usr/bin/perl -w use strict; use DBI; # Module connection for ODBC # Set up variables for the connection my $server_name = "shadow"; my $server_ip = "10.207.130.8:8080"; my $database_name = "Smart Response System"; my $database_user = "cs59173"; my $database_pass = "5055"; my $DSN = "driver={SQL Server};server=$server_name;tcpip=$server_ip;da +tabase=$database_name;uid=$database_user;pwd=$database_pass"; my $DBH = DBI->connect("DBI:ODBC:$DSN") or die "Couldn't open database +: $DBI::errstr\n";
      The error message is:

      DBI->connect(driver={SQL Server};server=shadow;tcpip=10.207.130.8;database=Smart Response System;uid=cs59173;pwd=5055) failed: MicrosoftODBC Driver Manager Invalid string or buffer length (SQL-S1090)(DBD: db_login/SQLConnect err=-1) at c:\Perl\SRS\sql.pl line 13

      FIY, i have tried also sequence of examples by other monks, but also failed. Could you please help tracing the problems? Thanx a lot...
        x-c-ute-o-ner,

        Sorry that I don't see an obvious error in this code. I do have a couple of questions that might help your debugging. Just look through the list and make sure you have the correct information.

        • The conection is expecting a MS SQL server connection, is that your correct database type?
        • Did you try to re-install the DBI.pm (module) and DBD-ODBC.pm (driver) on the server and on your testing machine using PPM?
        • Are you certain of the IP?
        • Did you double check your Username, PWD?
        • Is the database name correct? (BTW: None of my databases have spaces in their names. That doesn't mean that it won't work like that, but I've not tried that before. You may want to rename you database to something without spaces.)

        I don't see an error, and those questions are just to get you thinking on the debugging process. I hope you have good luck on this problem. If there is any progress, or different problem, please make sure you post the final solution, or additional problems.

        Thanks,


        - Mission
Re: connecting Perl with MS SQL Server on NT using ODBC
by peacemaker1820 (Pilgrim) on Jul 01, 2002 at 19:27 UTC
    Instead of the statement my $DSN...... use:
    my ( $server_name, $database, $user_id, $password ) =qw ( SERVER DATABASE USER PASSWORD );

    my $DSN = "driver={SQL Server}; Server=$server_name; Database=$database; UID=$user_id; PWD=$password";

      peacemaker1820,

      TIMTOWTDI

      I chose to post code that was legible from a beginner viewpoint, to hopefully help you and others (Anonymous Monk's | lurkers) who may not know what qw does. That's also the reson for the glorified comment tags. When I do research and development on something that is not functioning, I start with the basics until I get something to work, then I move up. That's how I do development, and that's why I posted simple code.

      If you still need assistance, feel free to /msg me.

      Welcome to the Monestary, peacemaker1820.

      - Mission
Re: connecting Perl with MS SQL Server on NT using ODBC
by BlueBlazerRegular (Friar) on Jul 01, 2002 at 21:12 UTC
    peacemaker1820,

    I get the same error if I send a bad userid/password combo.

    Most of the time SQL Server is set up to use your NT logon as your userid/password. Try sending the command with the userid and password values blank. That way SQL Server will default to your NT logon (assuming that SQL Server was set up that way).

    Good luck and let us know what happens...

    Pat

Re: connecting Perl with MS SQL Server on NT using ODBC
by screamingeagle (Curate) on Jul 01, 2002 at 22:37 UTC
    if you're running the script on unix and trying to connect to an NT-based SQL Server, you could try and use the FreeTDS library...
    a) in the FreeTDS "interfaces" file, configure the connection to the SQL Server like this :
    myserver query tcp tds4.2 10.0.2.1 1433
    b) in your Perl script, include code which goes something like this :
    $ENV{'SYBASE'} = "/freetds" unless $ENV{'SYBASE'}; my $dbh; $dbh = DBI->connect("dbi:Sybase:server=myserver",userid,password);

    hth
      Hellow monks

      i'm getting a similar problem with SQL Server ( I'm on windows )

      use DBI; my $user = "rodrigo"; my $pass = "123456"; my $server = "localhost\\WHATSUP:3477"; my $database_name = "equip"; my $DSN = "driver={SQL Server};server=$server;database=$database_name; +uid=$user;pwd=$pass"; my $DBH = DBI->connect("DBI:ODBC:$DSN") or die "Couldn't open database +: $DBI::errstr\n";
      But i'm getting the following error:
      DBI connect('driver={SQL Server};server=localhost\WHATSUP:3477;databas +e=equip;uid=rodrigo;pwd=123456','',...) failed: [Microsoft][ODBC SQL +Server Driver][DBNETLIB]Invalid connection. (SQL-08001) [st ate was 08001 now 01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (ParseConn +ectParams()). (SQL-01000) at C :\fsecali\connectDB.pl line 9 Couldn't open database: [Microsoft][ODBC SQL Server Driver][DBNETLIB]I +nvalid connection. (SQL-08001) [state was 08001 now 01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (ParseConn +ectParams()). (SQL-01000)
      I have no idea what should i do. Thanks in advanced
        try
        my $server = "localhost\\WHATSUP;tcpip=3477";
        poj

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://178615]
Approved by Mission
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (13)
As of 2014-12-17 22:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (37 votes), past polls