<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<body link="#355491" alink="#4262a1" vlink="#355491" style="background: #e2e2e2; margin: 0; padding: 20px;">
<div>
<table cellpadding="0" bgcolor="#FFFFFF" border="0" cellspacing="0" style="border: 1px solid #dadada; margin-bottom: 30px; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" style="border: solid 2px #ccc; background: #dadada; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td bgcolor="#000000" valign="middle" height="58px" style="border-bottom: 1px solid #ccc; padding: 20px; -moz-border-radius-topleft: 3px; -moz-border-radius-topright: 3px; -webkit-border-top-right-radius: 5px; -webkit-border-top-left-radius: 5px;">
<h1 style="color: #333333; font: bold 22px Arial, Helvetica, sans-serif; margin: 0; display: block !important;">
<!-- To have a header image/logo replace the name below with your img tag -->
<!-- Email clients will render the images when the message is read so any image -->
<!-- must be made available on a public server, so that all recipients can load the image. -->
<a href="https://community.jboss.org/index.jspa" style="text-decoration: none; color: #E1E1E1">JBoss Community</a></h1>
</td>
</tr>
<tr>
<td bgcolor="#FFFFFF" style="font: normal 12px Arial, Helvetica, sans-serif; color:#333333; padding: 20px; -moz-border-radius-bottomleft: 4px; -moz-border-radius-bottomright: 4px; -webkit-border-bottom-right-radius: 5px; -webkit-border-bottom-left-radius: 5px;"><h3 style="margin: 10px 0 5px; font-size: 17px; font-weight: normal;">
Re: Feature request:Add an interface to allow end-user to have a second chance to create a normal database connection after failling to get connection because of database password error
</h3>
<span style="margin-bottom: 10px;">
created by <a href="https://community.jboss.org/people/zhouyaguo">Yaguo Zhou</a> in <i>IronJacamar</i> - <a href="https://community.jboss.org/message/789554#789554">View the full discussion</a>
</span>
<hr style="margin: 20px 0; border: none; background-color: #dadada; height: 1px;">
<div class="jive-rendered-content"><p>Appreciate for your reply and sorry for my late response because of my busy work.</p><p>Let me describe  my new feature's scenario more clearly:</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>For financial security reason, DBA in our company must change our database's password every 2 or 3 months, but unfortunately, every time after passwods are changed, we have to modify datasource password in java application server(we are using JBoss 5.1) and restart it, meanwhile, we sometimes forgot to change and restart one of our jboss instances, because  we have dozens of web application located on many jboss instance.so what we want is that after DBA changed passwords,  jboss can get the new password when receive a new http request and store the password in memory(in mbean or one property of class).</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>so we think about a solution and have finished it already:</p><p>step1. we developed a socket server program which is used to receive command message to save password and query password of databases(socket message format is design by our own and we call the socket server as DBPM server)</p><p>step2. DBA change the password through IBM Tivoli that is extended by our programmer to send password to DBPM server meanwhile, and DBPM server will store it centrally.</p><p>step3. I hack the code org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.java as follows:</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><blockquote class="jive-quote"><p>private LocalManagedConnection getLocalManagedConnection(Properties props,Properties copy) </p><p>                       throws JBossResourceException</p><p>   {</p><p>             boolean debug = log.isDebugEnabled();</p><p>       Connection con = null;</p><p>             try</p><p>             {</p><p>                       String url = getConnectionURL();</p><p>                       Driver d = getDriver(url); </p><p>                       </p><p>                       // alreay got the new password</p><p>                       if(this.newPasswordFromDbpm != null){</p><p>                                 copy.setProperty("password", newPasswordFromDbpm);</p><p>                       }</p><p>                       </p><p>                       try{</p><p>                                 con = d.connect(url, copy);</p><p>                       }catch(SQLException e){</p><p>                                 if(debug){</p><p>                                           log.debug("ErrorCode:"+e.getErrorCode() + " " + "SQLState:"+e.getSQLState());</p><p>                                 }</p><p>                                 </p><p>                                 // for mysql and db2</p><p>                                 if("28000".equals(e.getSQLState())){</p><p>                    </p><p>                                           if(debug){</p><p>                                                     log.debug("we catch a SQLException(SQLState=28000), so try to get new password from DBPM server, and then reconnect the database");</p><p>                                           }</p><p>                                          </p><p>                                          String dbName = url.split(":")[3].split("/")[1]; // get database name from url</p><p>                                          String dbUserNm = copy.getProperty("user");</p><p>                                          //try to get password!</p><p>                                          String dbpmPassword = DsPasswdGetter.doGetPasswd(dbName, dbUserNm, false);</p><p>                                          if(dbpmPassword != null){</p><p>                                                    if(debug){</p><p>                                                              log.debug("get dbpmPassword ok.. begin to reconnect the database");</p><p>                                                    }</p><p>                                                    copy.setProperty("password", dbpmPassword);</p><p>                                                    this.newPasswordFromDbpm = dbpmPassword;</p><p>                                                    //write it to xml configuration file</p><p>                                                    if(securityDomain != null){</p><p>                                                              Properties prop = new Properties();</p><p>                                                              prop.setProperty(securityDomain, dbpmPassword);</p><p>                                                              if(debug){</p><p>                                                                        log.debug("begin to write dbpmPassword to login-config.xml");</p><p>                                                              }</p><p>                                                              UpdateJdbcPassword.doModifyPasswd(prop);</p><p>                                                    }else{</p><p>                                                              throw new JBossResourceException("securityDomain is null");</p><p>                                                    }</p><p>                                                    </p><p>                                                    //begin to reconnect</p><p>                                                    con = d.connect(url, copy);</p><p>                                          }else{</p><p>                                                    //error to get password from DBPM server</p><p>                                                    throw new JBossResourceException("Can not get password from dbmp server for [dbName:"+dbName+", dbUserNm:"+dbUserNm+"]");</p><p>                                          }</p><p>                                }</p><p>                                </p><p>                                if(con==null)</p><p>                                  throw new JBossResourceException("Wrong driver class for this connection URL");</p><p>                       }</p><p>                       </p><p>                       return new LocalManagedConnection(this, con, props, transactionIsolation, preparedStatementCacheSize);</p><p>             }</p><p>             catch (Throwable e)</p><p>             {</p><p>                 if (con != null)</p><p>                 {</p><p>                    try</p><p>                    {</p><p>                       con.close();</p><p>                    }</p><p>                    catch (Throwable ignored)</p><p>                    {</p><p>                    }</p><p>                 }</p><p>                       throw new JBossResourceException("Could not create connection", e);</p><p>             }</p><p>   }</p></blockquote><p>step4. now suppose a http request arrived and all existing connected connections in pool are busy, so jca try to establish a new connection , and then flow into my code above.</p><p>step5. my code above will fetch the correct password back and reconnect successfully.</p><p>step6. jca return a correct connection to web application. i am happy to see that web application don't even know jca has helped him to get the correct password.</p><p>step7. so now we don't need to modify and restart jboss instance any more.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>The problem is that my hacked code doesn't merge into upstream trunk , and now it's in my own branch. so i will very happy to *implements* a interface to do things above to avoid hacking the jca source code. many thanks!</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>Best Regards,</p><p>Yaguo</p></div>
<div style="background-color: #f4f4f4; padding: 10px; margin-top: 20px;">
<p style="margin: 0;">Reply to this message by <a href="https://community.jboss.org/message/789554#789554">going to Community</a></p>
<p style="margin: 0;">Start a new discussion in IronJacamar at <a href="https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2098">Community</a></p>
</div></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>