#16
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Hi


    Hi,

    Thanks for the reply.
    Unfortunately, it didn't help.
    I still get the same error because the execute statment failes.
    $RS = $oSP->Execute() or die "Cannot execute\n";
    Sends me the error "Cannot execute"

    Any other suggestions?

    Thanks.
  2. #17
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,642
    Rep Power
    4247
    Odd. Can you please post the exact text that you set $oSP->{'CommandText'} to (I mean the exact value of $query)
    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
  4. #18
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    ofc


    ofc, thats the value of $query:
    $query = "exec dba..CENTERITY_REPLICATION_STATUS";

    I've also tried to use:
    $query = "exec dba..CENTERITY_REPLICATION_STATUS;";
    $query = "SET NOCOUNT ON;exec dba..CENTERITY_REPLICATION_STATUS;SET NOCOUNT OFF;";

    Thanks.
  6. #19
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Another question


    Hi,

    I have another question (I still need your help in the previous one :/), how can I connect to MySQL DB using Win32::OLE?
    Everything I found - didn't work.
    When trying to use DBI, I got the following error:
    failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at D:\temp\yakir\tst3.pl line 11.

    Thanks again.
  8. #20
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Hi


    I realized that the only for me to do this in perl is by installing the mysql module. I wanna avoid it - so I've found a way to connect mysql by a command in the CLI (commnad line). The name of the command is mysql (surprise surprise), the problem is that I can't found that tool to linux...
    Does anyone know this command? If do - can you please give me a download link for it (assuming it's a free tool).

    P.S:
    Still didn't find an answer for the first question.
    Thanks.
  10. #21
  11. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,642
    Rep Power
    4247
    For your first question, just try:
    Code:
    $query = "dba..CENTERITY_REPLICATION_STATUS";
    and in that stored proc, put in the SET NOCOUNT ON; and SET NOCOUNT OFF;
    Let me know if that works for you.

    As for the other question, if you have the MyODBC driver downloaded and installed on your computer, you should be able to connect with ADO using this string:
    Code:
    Driver={MySQL ODBC 3.51 Driver};Server=192.168.1.100;Port=3306;User=username;Password=mypassword;Database=mydatabase;
    Adjust your parameters as per the ODBC driver version you've downloaded and the server's IP address, username and password.

    If you've installed MyOLEDB (and it hasn't been maintained in a while), you can use ADO and the following connection string:
    Code:
    Provider=MySQLProv;Server=192.168.1.100;Port=3306;User=username;Password=mypassword;Database=mydatabase
    Using the command line mysql tool is the last alternative. By the way, the same CLI tool is available under linux as well -- you may just not have installed it, or it may not be in your path.
    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
  12. #22
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Hi


    Hi,

    First thing first - thanks.
    As for the storred procedure - the $query = "dba..CENTERITY_REPLICATION_STATUS"; didn't work - though I didnt put in the SET NOCOUNT ON/OFF in the SP itself cuz my organization wouldn't allow it - there are too many of them.
    BUT, somehow (I will check it) - now it works using the following:
    $query = "SET NOCOUNT ON;exec dba..CENTERITY_REPLICATION_STATUS;SET NOCOUNT OFF;";

    As for the MySQL, yeah - the command is the last alternative so I will check out your suggestions.
    P.S: I did found the command in my Linux node, I just forgot to update it here.

    Again, thanks for everything. If I'll have any more questions I'll update it here, in addition - I'll let you know how the rest worked out for me.
  14. #23
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Hi


    Another quetion, sometimes the connect to DB operation can fail, I wanna check in-script if it failed, if it did - raise an alarm.
    At first, when the connection failed - the script aborted so I used:
    $conn->open("Provider=SQLOLEDB.1;Password=$passwd;Persist Security Info=True;User ID=$user;Initial Catalog=$target;Data Source=$ip") || warn "Couldn't connect to DB";

    So the script wont abort. It worked, but I wanna do something in the script if the connection failed. How do I do that?

    I've tried eval - didn't work.

    Thanks.
  16. #24
  17. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    836
    Rep Power
    496
    You could replace the 'warn "Couldn't connect to DB";' by a call to a function in which you do the warning and proceed with the additional code that you want to execute.
  18. #25
  19. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,642
    Rep Power
    4247
    >> but I wanna do something in the script if the connection failed. How do I do that?

    Don't know what you want to do in case of connection errors. Do you want to retry or email someone or what? Either way, you could perhaps write your code like this:
    Code:
    $conn->open("Provider=SQLOLEDB.1;Password=$passwd;Persist Security Info=True;User ID=$user;Initial Catalog=$target;Data Source=$ip") || do {
        log_error("Failed connection: $DBI::errstr\n");
        email_someone("Failed connection: $DBI:errstr\n");    
    };
    You can add more stuff to the do block as needed.
    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
  20. #26
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Hi


    Hi,

    This is what I needed.
    I wanted to configure a paramter called $connerr that indicates where the connection fails.
    Like the following:
    $connerr = 0;
    $conn->open("Provider=SQLOLEDB.1;Password=$passwd;Persist Security Info=True;User ID=$user;Initial Catalog=$target;Data Source=$ip") || do {$connerr = 1};
    $rs = $conn->Execute($query) || do {$connerr = 2};
    $data = $rs->Fields(0)->value || do {$connerr = 3};
    if ($connerr != 0)
    {
    #DO SOMETHING
    }
    else
    {
    #DO SOMETHING ELSE
    }

    The problem is that when trying to get the field value after the connection or execute fails - it aborts the script, even though I used "|| do {$connerr = 3}" or "|| warn "error" ".
    Why is that?

    Thanks.
  22. #27
  23. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,642
    Rep Power
    4247
    Originally Posted by yakirllc
    Hi,

    This is what I needed.
    I wanted to configure a paramter called $connerr that indicates where the connection fails.
    Like the following:
    $connerr = 0;
    $conn->open("Provider=SQLOLEDB.1;Password=$passwd;Persist Security Info=True;User ID=$user;Initial Catalog=$target;Data Source=$ip") || do {$connerr = 1};
    $rs = $conn->Execute($query) || do {$connerr = 2};
    $data = $rs->Fields(0)->value || do {$connerr = 3};
    if ($connerr != 0)
    {
    #DO SOMETHING
    }
    else
    {
    #DO SOMETHING ELSE
    }

    The problem is that when trying to get the field value after the connection or execute fails - it aborts the script, even though I used "|| do {$connerr = 3}" or "|| warn "error" ".
    Why is that?

    Thanks.
    Your method of error checking is wrong. If your statement doesn't execute, then $rs will be undef, therefore $rs->Fields(0) does not exist and $rs->Fields(0)->value is an error. What you want to do is check if your statement executed correctly and not even try to get to $rs->Fields(0) unless $rs is defined. One way to do it is like this:
    Code:
    if (defined $rs && defined $rs->Fields(0)) {
        $data = $rs->Fields(0)->value || do {$connerr = 3};
    }
    Another way is to check the value of $connerr as you go and make sure that you only execute code if there are no errors so far.
    Code:
    $connerr = 0;
    $conn->open("Provider=SQLOLEDB.1;Password=$passwd;Persist Security Info=True;User ID=$user;Initial Catalog=$target;Data Source=$ip") || do {$connerr = 1};
    if ($connerr == 0) {
        $rs = $conn->Execute($query) || do {$connerr = 2};
    }
    if ($connerr == 0) {
        $data = $rs->Fields(0)->value || do {$connerr = 3};
    }
    if ($connerr != 0)
    {
         #DO SOMETHING
    }
    else
    {
         #DO SOMETHING ELSE
    }
    Hope this helps.
    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
  24. #28
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Hi


    Hi,

    Yeah, it helps alot. This is what I did in my script but I wondered if there's anything else I can do.

    Thanks alot.
  26. #29
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Hi


    Hi,

    I have a question, what connection strings can I put in the provider string for MSSQL? Should it be different for 32 or 64 bit?
    anything else?
    Example:
    $conn->open("Provider=SQLOLEDB.1;Password=$passwd;Persist Security Info=True;User ID=$user;Initial Catalog=$target;Data Source=$ip");

    At this example the Provider is SQLOLEDB. Can it be different in some other cases?

    I'm asking because my script failes in the connection part while all the properties are correct...

    Thanks.
  28. #30
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    25
    Rep Power
    0

    Hi


    Another question, please take a look at my code and tell me if I'm doing something worng:
    use Win32::OLE;
    $passwd = "passwd";
    $user = "usr";
    $target = "DB";
    $ip = "IP";
    $port = 1433;
    $query = "\"select count(*) from table\"";
    $connerr = 0;
    # Connect to DB
    my $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") || do {$connerr = 1};
    if ($connerr == 0)
    {
    #Execute query
    $rs = $conn->Execute($query) || do {$connerr = 2};
    }
    if ($connerr == 0)
    {
    #Collect Data
    $data = $rs->Fields(0)->value || do {$connerr = 3};
    }
    if ($connerr != 0)
    {
    if ($connerr == 1)
    {
    $object = "Couldn't connect to DB $ip";
    }
    elsif ($connerr == 2)
    {
    $object = "Couldn't execute query in DB $ip";
    }
    else
    {
    $object = "Couldn't get field value from query in DB $ip";
    }
    print "$object\n";
    }

    Thanks.

IMN logo majestic logo threadwatch logo seochat tools logo