001 package railo.runtime.tag; 002 003 import java.sql.DatabaseMetaData; 004 import java.sql.SQLException; 005 import java.util.ArrayList; 006 007 import railo.commons.lang.StringUtil; 008 import railo.runtime.config.ConfigImpl; 009 import railo.runtime.db.DataSource; 010 import railo.runtime.db.DataSourceManager; 011 import railo.runtime.db.DatasourceConnection; 012 import railo.runtime.db.SQL; 013 import railo.runtime.db.SQLImpl; 014 import railo.runtime.db.SQLItem; 015 import railo.runtime.db.SQLItemImpl; 016 import railo.runtime.debug.DebuggerImpl; 017 import railo.runtime.debug.DebuggerPro; 018 import railo.runtime.engine.ThreadLocalPageContext; 019 import railo.runtime.exp.DatabaseException; 020 import railo.runtime.exp.PageException; 021 import railo.runtime.ext.tag.TagImpl; 022 import railo.runtime.op.Caster; 023 import railo.runtime.type.QueryImpl; 024 import railo.runtime.type.Struct; 025 import railo.runtime.type.StructImpl; 026 import railo.runtime.type.scope.Form; 027 import railo.runtime.type.util.ArrayUtil; 028 import railo.runtime.type.util.CollectionUtil; 029 import railo.runtime.type.util.ListUtil; 030 031 /** 032 * Updates existing records in data sources. 033 * 034 * 035 * 036 **/ 037 public final class Update extends TagImpl { 038 039 /** If specified, password overrides the password value specified in the ODBC setup. */ 040 private String password; 041 042 /** Name of the data source that contains a table. */ 043 private String datasource; 044 045 /** If specified, username overrides the username value specified in the ODBC setup. */ 046 private String username; 047 048 /** A comma-separated list of form fields to update. If this attribute is not specified, all fields 049 ** in the form are included in the operation. */ 050 private String formfields; 051 052 /** For data sources that support table ownership, for example, SQL Server, Oracle, and Sybase 053 ** SQL Anywhere, use this field to specify the owner of the table. */ 054 private String tableowner; 055 056 /** Name of the table you want to update. */ 057 private String tablename; 058 059 /** For data sources that support table qualifiers, use this field to specify the qualifier for the 060 ** table. The purpose of table qualifiers varies across drivers. For SQL Server and Oracle, the qualifier 061 ** refers to the name of the database that contains the table. For the Intersolv dBase driver, the 062 ** qualifier refers to the directory where the DBF files are located. */ 063 private String tablequalifier; 064 065 @Override 066 public void release() { 067 super.release(); 068 password=null; 069 username=null; 070 formfields=null; 071 tableowner=null; 072 tablequalifier=null; 073 datasource=null; 074 } 075 076 /** set the value password 077 * If specified, password overrides the password value specified in the ODBC setup. 078 * @param password value to set 079 **/ 080 public void setPassword(String password) { 081 this.password=password; 082 } 083 084 /** set the value datasource 085 * Name of the data source that contains a table. 086 * @param datasource value to set 087 **/ 088 public void setDatasource(String datasource) { 089 this.datasource=datasource; 090 } 091 092 /** set the value username 093 * If specified, username overrides the username value specified in the ODBC setup. 094 * @param username value to set 095 **/ 096 public void setUsername(String username) { 097 this.username=username; 098 } 099 100 /** set the value formfields 101 * A comma-separated list of form fields to update. If this attribute is not specified, all fields 102 * in the form are included in the operation. 103 * @param formfields value to set 104 **/ 105 public void setFormfields(String formfields) { 106 this.formfields=formfields; 107 } 108 109 /** set the value tableowner 110 * For data sources that support table ownership, for example, SQL Server, Oracle, and Sybase 111 * SQL Anywhere, use this field to specify the owner of the table. 112 * @param tableowner value to set 113 **/ 114 public void setTableowner(String tableowner) { 115 this.tableowner=tableowner; 116 } 117 118 /** set the value tablename 119 * Name of the table you want to update. 120 * @param tablename value to set 121 **/ 122 public void setTablename(String tablename) { 123 this.tablename=tablename; 124 } 125 126 /** set the value tablequalifier 127 * For data sources that support table qualifiers, use this field to specify the qualifier for the 128 * table. The purpose of table qualifiers varies across drivers. For SQL Server and Oracle, the qualifier 129 * refers to the name of the database that contains the table. For the Intersolv dBase driver, the 130 * qualifier refers to the directory where the DBF files are located. 131 * @param tablequalifier value to set 132 **/ 133 public void setTablequalifier(String tablequalifier) { 134 this.tablequalifier=tablequalifier; 135 } 136 137 138 @Override 139 public int doStartTag() { 140 return SKIP_BODY; 141 } 142 143 @Override 144 public int doEndTag() throws PageException { 145 146 Object ds=Insert.getDatasource(pageContext,datasource); 147 148 DataSourceManager manager = pageContext.getDataSourceManager(); 149 DatasourceConnection dc=ds instanceof DataSource? 150 manager.getConnection(pageContext,(DataSource)ds,username,password): 151 manager.getConnection(pageContext,Caster.toString(ds),username,password); 152 try { 153 154 Struct meta =null; 155 try { 156 meta=Insert.getMeta(dc,tablequalifier,tableowner,tablename); 157 } 158 catch(SQLException se){ 159 meta=new StructImpl(); 160 } 161 162 String[] pKeys=getPrimaryKeys(dc); 163 SQL sql=createSQL(dc,pKeys,meta); 164 if(sql!=null) { 165 railo.runtime.type.Query query = new QueryImpl(pageContext,dc,sql,-1,-1,-1,"query"); 166 167 if(pageContext.getConfig().debug()) { 168 String dsn=ds instanceof DataSource?((DataSource)ds).getName():Caster.toString(ds); 169 boolean logdb=((ConfigImpl)pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE); 170 if(logdb){ 171 boolean debugUsage=DebuggerImpl.debugQueryUsage(pageContext,query); 172 ((DebuggerPro)pageContext.getDebugger()).addQuery(debugUsage?query:null,dsn,"",sql,query.getRecordcount(),pageContext.getCurrentPageSource(),query.getExecutionTime()); 173 } 174 } 175 } 176 return EVAL_PAGE; 177 } 178 finally { 179 manager.releaseConnection(pageContext,dc); 180 } 181 } 182 183 private String[] getPrimaryKeys(DatasourceConnection dc) throws PageException { 184 railo.runtime.type.Query query = getPrimaryKeysAsQuery(dc); 185 int recCount=query.getRecordcount(); 186 String[] pKeys=new String[recCount]; 187 188 if(recCount==0) throw new DatabaseException("can't find primary keys of table ["+tablename+"]",null,null,dc); 189 190 for(int row=1;row<=recCount;row++) { 191 pKeys[row-1]=Caster.toString(query.getAt("column_name",row)); 192 } 193 194 return pKeys; 195 } 196 197 private railo.runtime.type.Query getPrimaryKeysAsQuery(DatasourceConnection dc) throws PageException { 198 199 // Read Meta Data 200 DatabaseMetaData meta; 201 try { 202 meta = dc.getConnection().getMetaData(); 203 } 204 catch (SQLException e) { 205 throw new DatabaseException(e,dc); 206 } 207 208 try { 209 return new QueryImpl(meta.getPrimaryKeys(tablequalifier, tableowner, tablename),-1,"query",pageContext.getTimeZone()); 210 } 211 catch (SQLException e) { 212 try { 213 return new QueryImpl(meta.getBestRowIdentifier(tablequalifier, tableowner, tablename, 0, false),-1,"query",pageContext.getTimeZone()); 214 } 215 catch (SQLException sqle) { 216 throw new DatabaseException("can't find primary keys of table ["+tablename+"]",sqle,null,dc); 217 } 218 } 219 } 220 221 /** 222 * @param keys primary Keys 223 * @return return SQL String for update 224 * @throws PageException 225 */ 226 private SQL createSQL(DatasourceConnection dc,String[] keys, Struct meta) throws PageException { 227 String[] fields=null; 228 Form form = pageContext.formScope(); 229 if(formfields!=null) fields=ListUtil.toStringArray(ListUtil.listToArrayRemoveEmpty(formfields,',')); 230 else fields=CollectionUtil.keysAsString(pageContext.formScope()); 231 232 StringBuffer set=new StringBuffer(); 233 StringBuffer where=new StringBuffer(); 234 ArrayList setItems=new ArrayList(); 235 ArrayList whereItems=new ArrayList(); 236 String field; 237 for(int i=0;i<fields.length;i++) { 238 field = StringUtil.trim(fields[i],null); 239 if(StringUtil.startsWithIgnoreCase(field, "form.")) 240 field=field.substring(5); 241 242 if(!field.equalsIgnoreCase("fieldnames")) { 243 if(ArrayUtil.indexOfIgnoreCase(keys,field)==-1) { 244 if(set.length()==0) set.append(" set "); 245 else set.append(","); 246 set.append(field); 247 set.append("=?"); 248 ColumnInfo ci=(ColumnInfo) meta.get(field); 249 if(ci!=null)setItems.add(new SQLItemImpl(form.get(field,null),ci.getType())); 250 else setItems.add(new SQLItemImpl(form.get(field,null))); 251 } 252 else { 253 if(where.length()==0) where.append(" where "); 254 else where.append(" and "); 255 where.append(field); 256 where.append("=?"); 257 whereItems.add(new SQLItemImpl(form.get(field,null))); 258 } 259 } 260 } 261 if((setItems.size()+whereItems.size())==0) return null; 262 263 if(whereItems.size()==0)throw new DatabaseException("can't find primary keys ["+ListUtil.arrayToList(keys,",")+"] of table ["+tablename+"] in form scope",null,null,dc); 264 265 StringBuffer sql=new StringBuffer(); 266 sql.append("update "); 267 if(tablequalifier!=null && tablequalifier.length()>0) { 268 sql.append(tablequalifier); 269 sql.append('.'); 270 } 271 if(tableowner!=null && tableowner.length()>0) { 272 sql.append(tableowner); 273 sql.append('.'); 274 } 275 sql.append(tablename); 276 sql.append(set); 277 sql.append(where); 278 279 return new SQLImpl(sql.toString(),arrayMerge(setItems,whereItems)); 280 } 281 282 private SQLItem[] arrayMerge(ArrayList setItems, ArrayList whereItems) { 283 SQLItem[] items=new SQLItem[setItems.size()+whereItems.size()]; 284 285 int index=0; 286 // Item 287 int size=setItems.size(); 288 for(int i=0;i<size;i++) { 289 items[index++]=(SQLItem) setItems.get(i); 290 } 291 // Where 292 size=whereItems.size(); 293 for(int i=0;i<size;i++) { 294 items[index++]=(SQLItem) whereItems.get(i); 295 } 296 return items; 297 } 298 } 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314