Re: How to change expired database password via DBI?

Are you sure Toad is using the ODBC Driver? What is the name of the dialogue which pops up from Toad?

The first time an ODBC Driver knows it cannot "login" is when SQLDriverConnect fails. There is no specific ODBC state for "login expired". However, there is the magic SQL_DRIVER_PROMPT and SQL_DRIVER_COMPLETE options to SQLDriverConnect which allow the ODBC driver to throw up a dialogue and complete the connection string. If Toad is using the SQL Server ODBC driver then I'm guessing the dialogue you are seeing is NOT a Toad thing but a SQL Server driver dialogue. You won't get this with DBD::ODBC because it does not set SQL_DRIVER_PROMPT although if you rebuild it yourself it could easily do so.

If you investigated it further by looking at the dialogue and trying to add SQL_DRIVER_PROMPT to SQLDriverConnect call in DBD::ODBC I would consider adding an attribute to DBD::ODBC to enable this functionality.

Comment on Re: How to change expired database password via DBI?

Replies are listed 'Best First'.
Re^2: How to change expired database password via DBI? by Sue D. Nymme (Monk) on Aug 19, 2009 at 16:08 UTC
Are you sure Toad is using the ODBC Driver? No, I am not sure. It is probably talking directly to the server via the SQL Server port. Unfortunately, there is no DBD::SQLServer, just DBD::ODBC. The rest of your post sounds interesting, but I have to admit it's way over my head. :-(	[reply]
Re^3: How to change expired database password via DBI? by mje (Curate) on Aug 20, 2009 at 07:28 UTC
It does not really matter how Toad is connecting to SQL Server, I only asked because you said you were using DBD::ODBC and were comparing Toad and ODBC. The point is that the SQL Server ODBC Driver has access to the main window in a MS windows environment and can therefore throw a dialogue up (probably the one you are seeing). The ODBC specification has an API called SQLDriverConnect that is used to connect to an ODBC Driver and it is that API that DBD::ODBC uses. The second argument to that API is a window handle and the last argument can be SQL_DRIVER_PROMPT which allows the driver to throw up a dialogue if it needs to. DBD::ODBC does not have a window handle as it is not a GUI application but an interface (and currently there is no way to pass one to it) so as a result it does not bother using the SQL_DRIVER_PROMPT argument to SQLDriverConnect and this prevents the SQL Server driver from throwing a dialogue. Toad on the other hand is a GUI app so IF it was using the SQL Server ODBC Driver (and the same probably applies to JDBC) it can pass a window handle to the driver and hence it can pop up a dialogue if it needs to.	[reply]
Re^4: How to change expired database password via DBI? by Sue D. Nymme (Monk) on Aug 20, 2009 at 13:33 UTC
Okay, I understand. And looking through the C code for DBD::ODBC, I see where SqlDriverConnect is being called, and that it uses SQL_DRIVER_NOPROMPT. So it appears that there is no way to do what I want in Perl. Rats.	[reply]
Re^5: How to change expired database password via DBI? by mje (Curate) on Aug 20, 2009 at 15:53 UTC
Re^2: How to change expired database password via DBI? by Sue D. Nymme (Monk) on Aug 20, 2009 at 15:39 UTC
Thanks again, mje. I've e-mailed the maintainer of DBD::ODBC and asked if enhancing the module is a possibility. We'll see how it goes from here.	[reply]
Re^3: How to change expired database password via DBI? by mje (Curate) on Aug 20, 2009 at 15:54 UTC
I am the maintainer :-)	[reply]