Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    SQL Server with specific port


    Hi,

    I'm trying to find a way to connect to SQL Server with perl through a specific port (the default is 1433, I want to connect with some other port).
    I've seen many ways to connect to SQL but none specify how to connect through a specific port.
    Can you please help me with that?

    P.S:
    I'd prefer to use special libarires as less as possible.
    My servers know the DBI library, but they dont know the DBD library.
    I prefer working with Win32::OLE like:

    my $conn = Win32::OLE->new('ADODB.connection');
    $conn->open("Provider=SQLOLEDB.1;Password=passwd;Persist Security Info=True;User ID=user;Initial Catalog=master;Data Source=IPADDRESS");
    $rs = $conn->Execute("select * from table");

    Thanks alot!
  2. #2
  3. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,254
    Rep Power
    1810
    I guess you are saying there is no DBI driver installed, and you are unable to have one installed? Not sure what you mean about the server. The one time I needed to connect to MS SQL server at work, I installed all the necessary drivers on my client machine, and was able to connect to the DB server that way.

    But I didn't use DBI either. The server was set up with ODBC, and so all the configuration (port, etc) was done through that. There are companies that provide commercial drivers. I think I tested one from EasySoft, and it worked fine. Only problem was the prohibitively expensive, subscription-based pricing.

    I finally was able to work out the details and connect through an open-source driver: iODBC. Once working, it was great, but it actually took me a few days to figure out what was needed.

    Hope you will find it easier.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    tnx


    Hi Keath,

    This is what I meant, I don't have the DBI.pm on the deployed servers but I can distribute it if necessery.

    Please remeber that my goal is to connect to the MS SQL through a different port then 1433 - for example 1435 and so on.

    Can you please provide me with the syntax of how to do it?

    Thanks again.
  6. #4
  7. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,254
    Rep Power
    1810
    Well, I misspoke earlier. I used DBI, I'm sure, but I still used the ODBC driver. The connection details, including the port, are defined in the datasource. The result is that on the perl side, the connection is simple:
    Code:
    my $data_source = q/dbi:ODBC:datasource_name/;
    my $dbh = DBI->connect($data_source, $user, $password)
        or die "Can't connect to $data_source: $DBI::errstr";
    The hard part is getting that datasource configured. Here's a link to the Easysoft website where they show an example.

    http://www.easysoft.com/developer/la..._tutorial.html

    Similar article at the iODBC website:

    http://www.iodbc.org/dataspace/iodbc...IODBCPerlHOWTO



    Edit: this is the sort of situation that will make you fall in love with Microsoft all over again.
    Last edited by keath; August 15th, 2013 at 08:07 AM.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Hi


    Hi again,

    For what it's worth, I see references of UNIX pathes only.
    I'm gonna run that script on windows.

    Another thing, did I get it right? the only way to connect to SQL DB through a custom port is by editing some file on the system that called DataSource?

    Can't I do it in the script itself?

    Thanks.
  10. #6
  11. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,254
    Rep Power
    1810
    Yeah, those are unix drivers, so you'll need to configure the ODBC driver on your system.

    I don't know for sure that ODBC is the only way of connecting, but it's the most common and easily supported by Microsoft. ODBC drivers are often included with Excel, so you may already have something working, and if not your company probably already has the drivers available to you. It shouldn't cost you anything. If you search files from the Start menu and search for ODBC, you can find the configuration tool on your system.

    This is like a double layer. DBI is perl's database independent library, to standardize the querying tasks. ODBC was Microsoft's similar version, but it works pretty much only with other Microsoft programs at this point. Open Database Connectivity is the standard way MSSQL wants to talk with others, and it is configured separately, outside of your script.

    Feel free to google for other solutions, but everything I found back in the day pointed me towards this method, and it worked.
  12. #7
  13. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,254
    Rep Power
    1810
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Hi


    Hi,

    Thanks alot for the help.

    I'll try to do it.

    Have a good day.
  16. #9
  17. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,607
    Rep Power
    4247
    To answer the original question, you can specify the port # as part of the ADO connection string like this:
    Code:
    $conn->open("Provider=SQLOLEDB.1;Password=passwd;Persist Security Info=True;User ID=user;Initial Catalog=master;Data Source=IPADDRESS,PORT_NUMBER");
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Tnx!!!


    Hi,

    You are AWSOME!
    It works! thanks alot.
    Lol, I gave a shot to:
    $conn->open("Provider=SQLOLEDB.1;Password=passwd;Persist Security Info=True;User ID=user;Initial Catalog=master;Data Source=IPADDRESS;Port=1435");

    and it didn't work so I gave up.

    Again, thanks alot for your help.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Another question


    Hi again,

    Now I have another question.
    I built a script that runs dynamic queries.
    It takes the queries from a txt file.
    The issue is that the fields in each query can change. The name and quentity can change.
    For example:
    select user_name from users
    select comany_name, group name from company
    select count(*) from company
    and so on....

    My question is how do I get the field names for each query?
    I saw a way in DBI library but I'm using Win32:OLE library, like the previous respond.

    Can you help me again?

    Thanks alot.
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Storred Procedure


    Hi,

    I managed to find how to print field numer 0 or 1 or any other field number. It applies to my needs.

    I have another question, for some reason, when trying to run Storred Precedure with the same syntax as I used to run SQL SELECT query. The syntax I used is:

    I looked up in Google how to run Storred procedures in perl and encountered the following syntax:
    $conn = Win32::OLE->new('ADODB.connection');
    $oSP = Win32::OLE->new("ADODB.Command");
    $RS = Win32::OLE->new("ADODB.Recordset");
    $conn->open("Provider=SQLOLEDB.1;Password=$passwd;Persist Security Info=True;User ID=$user;Initial Catalog=$target;Data Source=$ip,$port");

    $oSP->{'CommandText'} = $query;
    $oSP->{'CommandType'} = 4;
    $oSP->{'ActiveConnection'} = $conn;
    $oSP->{'CommandTimeout'} = 1200000;
    $RS = $oSP->Execute();
    while (! $RS->EOF)
    {
    $value = $RS->Fields(0)->value;
    print "$value\n";
    $RS->MoveNext;
    }
    In both cases I got the same error:
    Can't call method "value" on an undefined value at script.pl

    Can you help me?

    Thanks.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0
    The Syntax I used in the first case is:

    $conn = Win32::OLE->new('ADODB.connection');
    $conn->open("Provider=SQLOLEDB.1;Password=$passwd;Persist Security Info=True;User ID=$user;Initial Catalog=$target;Data Source=$ip,$port");
    $rs = $conn->Execute($query);
    while (!$rs->EOF)
    {
    $data = $rs->Fields(0)->value;
    print "$data";
    $rs->MoveNext;
    }

    I forgot to put the syntax in the last response.
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    help


    Someone...? please.
  28. #15
  29. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,607
    Rep Power
    4247
    Haven't played around with ADO in ages, but last time I mucked with SQL Server, ADO and stored procs (it was in python, not perl, but similar situation applies), I found that I would get mysterious errors when the stored proc returned a record set and finally found that you need to change the stored proc to have an extra statement:
    SET NOCOUNT ON;
    Put that as the first statement in the body of the stored proc

    Then, at the bottom of the stored proc, put:
    SET NOCOUNT OFF;

    See if that helps you out and please report back.
    Up the Irons
    What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
    "Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
    Down with Sharon Osbourne

    "I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo