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

Hi All, I have this piece of code , where after 'do $database' , it gets into the database , but after prepare and execute it changes back to master database for a few lines and then again shows the correct DB. I cannot figure out why this is happening. Am I doing anything wrong. Any ideas please ??? Here is the code: 
for ($i=0;$i<$#databases+1;$i++) {
    $database = $databases[$i] ;

    ### Use DB
    $dbh->do("USE $database") || log_message ("SERI","Database error $
+dbh->errstr") ;

    ### Get details from sysobjects
    $sql = "";
    $sql = "SELECT 1" ;
    $sth = $dbh->prepare($sql);
    $sth->execute();

## Here it goes to master ??????

    $objcount =0; 
    while (@row = $sth->fetchrow_array) {
        ($object = $row[0]) =~ s/\s//g ;
print "$object \n";
        get_db_name();
        $sql = "";
        $sql = "GRANT ALL ON $object TO PUBLIC" ;
#       $dbh->do($sql) || log_message ("SERI","Database error $dbh->er
+rstr");
        $objcount ++;
    } ## end of while

## Here it again shows the correct DB

    log_message("INFO","$objcount objects affected in $database");
} ## end of for

[download]

Replies are listed 'Best First'.
Re: DBI Not working
by busunsl (Vicar) on Aug 16, 2001 at 16:17 UTC
    DBD::Sybase opens a new connection for each open statementhandle.
    So if, after a use, you open a statementhandle while you have one already open, the new one will connect to the database specified in the connect string and not the database in the use.

    This means there is no problem for your program, except that the subroutine get_db_name() works wrong :-).

    This might be a bug, and I second MZSanford in that you should contact Michael Peppler about that.

[reply]
[d/l]
[select]
      Right...

      It's a bug, but one that isn't quite trivial to fix - the code would have to track "use xxxx" requests, and store those in the primary handle.

      But a similar problem could occur if you executed a "use xxx" on a secondary handle. The whole issue of having multiple statement handles for one $dbh (which forces multiple connections) is a little shaky, unfortunately.

      Michael

[reply]
        Hi Michael, great to see you here!

        Now to the problem:

        Perhaps it's not that hard. You filter three informal messages (5701, 5703, 5704) comming from the server.
        AFAIK one of them is an 'environment' message, telling the client that the current database has changed.
        You could use this message, to determine whether the database has changed.

        btw. I could use this message too in a small project i'm doing at the moment. :-)
        It's not hard to change DBD::Sybase, but it's not easy to have DBD::Sybase compiled in some win32 environments, so i have to wait for activestate :-(

[reply]
Re: DBI Not working
by MZSanford (Curate) on Aug 16, 2001 at 13:46 UTC
    Just so i can check for Driver specific issues, which DBD are you using ?
    Thus spake the Master Programmer:
    "When you have learned to snatch the error code from the trap frame, it will be time for you to leave."
    -- The Tao of Programming
[reply]
      I haven't explicitly specified any DBD , I think its using the default, Sybase driver.
[reply]
        I am not sure about any defaults, what is the information being used by the connect() call ?
            It does appear it could be DBD::Sybase, based on the sysobjects comment, but i am asking because i could not find get_db_name() in my copies of DBI or DBD::Sybase and wanted to verify versions (if possible).
            Also, almost forgot, where is it printing that the database has changed to master ?
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom