# DBScriptRunnerImpl.jcs Sample

This topic inludes the source code for the DBScriptRunnerImpl.jcs Sample.

## Sample Location

This sample is located in the following directory in your WebLogic Workshop installation:

BEA_HOME/weblogic81/samples/workshop/ExtensionDevKit/ControlDevKit/DBScripter/dbScript/

## Sample Source Code

`001 package dbScript; 002 003 import com.bea.control.*; 004 import java.sql.Connection; 005 import java.sql.Statement; 006 import java.sql.ResultSet; 007 import java.sql.ResultSetMetaData; 008 import java.sql.SQLException; 009 import java.sql.SQLWarning; 010 import javax.sql.DataSource; 011 import java.util.*; 012 import javax.naming.InitialContext; 013 import java.sql.DatabaseMetaData; 014 import java.io.*; 015 016 import java.lang.reflect.Method; 017 import dbScript.util.DsUtils; 018 import dbScript.util.Constants; 019 020 021 /** 022 * Implementation of DBSriptRunner sample custom Java control; see 023 * {@link <a href="../../navDBScriptRunner.html">DBScriptRunner Control</a>}. 024 * 025 * @see <a href="../../navDBScriptRunner.html">DBScriptRunner Control</a>. 026 * 027 * @jcs:control-tags file="DBScriptRunner-tags.xml" 028 * @jcs:jc-jar label="Script Runner" 029 * version="0.8" 030 * insert-wizard-class="dbScript.designUI.InsertWizCreateJCX" 031 * icon-16="/images/dbScript.gif" 032 * palette-priority="9" 033 * group-name="SQL Utilities" 034 * description= "Creates a control to define datasources and run SQL scripts" 035 * @editor-info:code-gen control-interface="false" 036 * 037 */ 038 public class DBScriptRunnerImpl 039 implements DBScriptRunner, 040 com.bea.control.Extensible, 041 com.bea.control.ControlSource 042 { 043 044 045 /** 046 * @common:context 047 */ 048 com.bea.control.ControlContext context; 049 050 051 transient Object[] _args = null; 052 transient String _dataSource = null; 053 transient DataSource _ds = null; 054 transient Connection _conn = null; 055 transient int _iBatchSize =1; 056 057 058 public void context_onAcquire() throws Exception 059 { 060 try 061 { 062 _dataSource = context.getControlAttribute(Constants.TAG_CONN, Constants.ATTR_DS); 063 String sBatchSize = context.getControlAttribute(Constants.TAG_CONN, Constants.ATTR_UB); 064 _iBatchSize = Integer.parseInt(sBatchSize); 065 return; 066 } catch (NumberFormatException nfe) { 067 } catch (Exception e) { 068 e.printStackTrace(); 069 throw e; 070 } 071 } 072 /** 073 * Extensible.invoke handles all extended interface methods 074 * Have to use this in order to have method-level annotations. 075 */ 076 public Object invoke(Method method, Object[] args) throws Exception 077 { 078 try 079 { 080 String mName = method.getName(); 081 _args = args; 082 String script = context.getMethodAttribute(Constants.TAG_DBSCR, Constants.ATTR_SF); 083 String dsType = context.getMethodAttribute(Constants.TAG_DBSCR, Constants.ATTR_NEWDSTYPE); 084 085 if ((script==null) && (dsType!=null)) 086 { 087 // CreateDS method 088 DsUtils dsu = new DsUtils(); 089 String url= context.getMethodAttribute(Constants.TAG_DBSCR, Constants.ATTR_URL); 090 String sProps = context.getMethodAttribute(Constants.TAG_DBSCR, Constants.ATTR_PROPS); 091 Properties props = dsu.getPropertiesFromString(sProps); 092 String sPromptUrl = context.getMethodAttribute(Constants.TAG_DBSCR, Constants.ATTR_PROMPTURL); 093 String sPromptUser = context.getMethodAttribute(Constants.TAG_DBSCR, Constants.ATTR_PROMPTUSER); 094 String sCreateNoTxDs = context.getMethodAttribute(Constants.TAG_DBSCR, Constants.ATTR_CREATENOTX); 095 boolean fPromptUrl = (new Boolean(sPromptUrl)).booleanValue(); 096 boolean fPromptUser = (new Boolean(sPromptUser)).booleanValue(); 097 boolean fCreateNoTxDs = (new Boolean(sCreateNoTxDs)).booleanValue(); 098 String sAdminUser = context.getMethodAttribute(Constants.TAG_DBSCR, Constants.ATTR_ADMINUSER); 099 String sAdminPwd = context.getMethodAttribute(Constants.TAG_DBSCR, Constants.ATTR_ADMINPWD); 100 String sNewDSName = context.getMethodAttribute(Constants.TAG_DBSCR, Constants.ATTR_NEWDSNAME); 101 String encryptedPassword=null; 102 String[] aUrl=url.split(";"); 103 int i=0; 104 if (fPromptUrl) 105 { 106 for (i=0;i<aUrl.length;i++) 107 aUrl[i]=(String)args[i]; 108 } 109 if (fPromptUser) 110 { 111 assert (args.length ==i+2); 112 props.setProperty("user", (String)args[i]); 113 i++; 114 if (props.containsKey("password")) 115 props.setProperty("password", (String)args[i]); 116 else 117 encryptedPassword = (String)args[i]; 118 } 119 if (null==sNewDSName) 120 sNewDSName = "Ds" + dsType + "Test"; 121 122 return dsu.createDataSource(dsType, sNewDSName, 123 aUrl, props, encryptedPassword, 124 sAdminUser, sAdminPwd, 125 fCreateNoTxDs); 126 127 } 128 if ((script!=null) && (script.length()>0)) 129 { 130 runScriptInternal(script, mName, true); 131 return null; 132 } 133 if (args.length==1) 134 { 135 runScriptInternal((String)args[0], mName, false); 136 return null; 137 } 138 throw new Exception ("Unknown method signtaure"); 139 140 } catch (Exception e) { 141 e.printStackTrace(); 142 throw e; 143 } 144 145 } 146 147 148 private void runScriptInternal(String script, String method, boolean fOpenAsResource) 149 { 150 StringBuffer sbMsg = new StringBuffer(2000); 151 sbMsg.append("Method ").append(method).append("invoked.\n"); 152 153 String cmdBatch=""; 154 boolean bEOF=false; 155 int numErrs=0; 156 String sInsertCmd = "INSERT"; 157 int [] numRows; 158 int totRows=0; 159 long start = System.currentTimeMillis(); 160 161 // SQL resources 162 Statement st = null; 163 Connection conn = null; 164 InputStream is = null; 165 Reader reader = null; 166 167 try 168 { 169 Properties h = new Properties(); 170 h.put(javax.naming.Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory"); 171 InitialContext jndiContext= new InitialContext (h); 172 _ds = (DataSource)jndiContext.lookup(_dataSource); 173 174 conn = _ds.getConnection(); 175 DatabaseMetaData dbmd = conn.getMetaData(); 176 177 if (!(dbmd.supportsBatchUpdates())) 178 { 179 sbMsg.append("Batch inserts not supported; setting batch to 1 \n"); 180 _iBatchSize = 1; 181 } 182 183 if (fOpenAsResource) 184 { 185 // read the SQL script from the current class loader 186 is = getClass().getClassLoader().getResourceAsStream(script); 187 if(is == null) throw new RuntimeException("Could not load resource: " + script); 188 } 189 else 190 { 191 is= new java.io.FileInputStream(script); 192 } 193 // use platform encoding 194 reader = new BufferedReader(new InputStreamReader(is)); 195 196 // read all commands 197 List stmts = readCmds(reader); 198 199 st = conn.createStatement(); 200 201 Iterator iterator = stmts.iterator(); 202 while(iterator.hasNext()) 203 { 204 try 205 { 206 st.clearBatch(); 207 int sz = 0; 208 // create the next batch 209 while(sz < _iBatchSize && iterator.hasNext()) 210 { 211 cmdBatch = (String)iterator.next(); 212 st.addBatch(cmdBatch); 213 sz++; 214 215 // non-INSERT commands have a batch size of 1 216 if(!cmdBatch.startsWith(sInsertCmd)) 217 break; 218 } 219 // execute the batch 220 numRows = st.executeBatch(); 221 222 //keep track of rowcounts 223 for (int k=0;k<numRows.length;k++) 224 { 225 if (numRows[k] > 0) 226 totRows += numRows[k]; 227 } 228 229 // keep track of any warnings 230 SQLWarning sw = st.getWarnings(); 231 while (sw!=null) 232 { 233 sbMsg.append(sw.getMessage() + "\n"); 234 sw=sw.getNextWarning(); 235 } 236 } 237 catch (SQLException se) 238 { 239 String sqlState=se.getSQLState(); 240 int nativeError=se.getErrorCode(); 241 242 //ignore "object not found" errors on drop 243 if (cmdBatch.toLowerCase().startsWith("drop")) 244 { 245 //sbMsg.append(se.getMessage() + "\n"); 246 } 247 else 248 { 249 sbMsg.append("Batch failed : \n"); 250 sbMsg.append(cmdBatch + "\n"); 251 sbMsg.append(se.getMessage() + "\n"); 252 sbMsg.append("SQLSTATE: " + se.getSQLState() + "\n\n"); 253 numErrs++; 254 if (numErrs > 25) 255 bEOF=true; 256 } 257 } 258 } // end of statement iterator 259 260 long end = System.currentTimeMillis(); 261 sbMsg.append("\n\nScript " + script + " executed successfully \n"); 262 if (totRows > 0) 263 sbMsg.append((new Integer(totRows).toString()) + " rows affected\n"); 264 sbMsg.append(((end-start)/1000) + " seconds elapsed\n"); 265 sbMsg.append("Used batch size of ").append(new Integer(_iBatchSize)).append("\n");; 266 } 267 catch (Exception e) 268 { 269 e.printStackTrace(); 270 sbMsg.append(e.getMessage()); 271 sbMsg.append("\n \n Batch Terminated \n\n Last stmt attempted: \n\n"); 272 sbMsg.append(cmdBatch); 273 } 274 275 // Send the notification that the batch is done. Since the callback is defined on the 276 // JCX we don't have its interface at compile time. So we use the reverse of the 277 // invoke function above by calling context.sendEvent(). 278 // alternatively we could call getCallbackInterface(); 279 try 280 { 281 Object [] cbArgs = new Object[1]; 282 cbArgs[0] = sbMsg.toString(); 283 context.sendEvent("onScriptDone", cbArgs); 284 } 285 catch (Exception e) 286 { 287 System.out.println("Unable to send callback; error : " + e.getMessage()); 288 } 289 290 // release all resources 291 try{if(st != null) st.close();}catch(SQLException ignore){} 292 try{if(conn != null) conn.close();}catch(SQLException ignore){} 293 try{if(is != null) is.close();}catch(IOException ignore){} 294 try{if(reader != null) reader.close();}catch(IOException ignore){} 295 return; 296 297 } 298 299 private List readCmds(Reader is) 300 throws IOException 301 { 302 List list = new ArrayList(); 303 StringBuffer buf = new StringBuffer(); 304 305 int ch; 306 boolean inQuote = false; 307 while((ch = is.read()) > -1) 308 { 309 if(ch == '\'') 310 inQuote = !inQuote; 311 312 // handle CR || LF, replace CR with ' ', skip LF 313 if(ch == 13) 314 ch = ' '; 315 else if(ch == 10) 316 continue; 317 318 if(ch == ';' && !inQuote) 319 { 320 list.add(buf.toString().trim()); 321 buf = new StringBuffer(512); 322 } 323 else buf.append((char)ch); 324 } 325 326 return list; 327 } 328 329 330 }`