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