001 package railo.runtime.tag; 002 003 import java.sql.DatabaseMetaData; 004 import java.sql.ResultSet; 005 import java.sql.SQLException; 006 import java.util.ArrayList; 007 008 import railo.commons.db.DBUtil; 009 import railo.commons.lang.StringUtil; 010 import railo.runtime.PageContext; 011 import railo.runtime.db.DataSourceManager; 012 import railo.runtime.db.DatasourceConnection; 013 import railo.runtime.db.SQL; 014 import railo.runtime.db.SQLImpl; 015 import railo.runtime.db.SQLItem; 016 import railo.runtime.db.SQLItemImpl; 017 import railo.runtime.debug.DebuggerImpl; 018 import railo.runtime.exp.ApplicationException; 019 import railo.runtime.exp.PageException; 020 import railo.runtime.ext.tag.TagImpl; 021 import railo.runtime.listener.ApplicationContextPro; 022 import railo.runtime.type.List; 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 028 /** 029 * Inserts records in data sources. 030 * 031 * 032 * 033 **/ 034 public final class Insert extends TagImpl { 035 036 /** If specified, password overrides the password value specified in the ODBC setup. */ 037 private String password; 038 039 /** Name of the data source that contains your table. */ 040 private String datasource; 041 042 /** If specified, username overrides the username value specified in the ODBC setup. */ 043 private String username; 044 045 /** A comma-separated list of form fields to insert. If this attribute is not specified, all 046 ** fields in the form are included in the operation. */ 047 private String formfields; 048 049 /** For data sources that support table ownership such as SQL Server, Oracle, and Sybase SQL 050 ** Anywhere, use this field to specify the owner of the table. */ 051 private String tableowner=""; 052 053 /** Name of the table you want the form fields inserted in. */ 054 private String tablename; 055 056 /** For data sources that support table qualifiers, use this field to specify the qualifier for the 057 ** table. The purpose of table qualifiers varies across drivers. For SQL Server and Oracle, the qualifier 058 ** refers to the name of the database that contains the table. For the Intersolv dBase driver, the 059 ** qualifier refers to the directory where the DBF files are located. */ 060 private String tablequalifier=""; 061 062 /** 063 * @see javax.servlet.jsp.tagext.Tag#release() 064 */ 065 public void release() { 066 super.release(); 067 password=null; 068 username=null; 069 formfields=null; 070 tableowner=""; 071 tablequalifier=""; 072 datasource=null; 073 } 074 075 /** set the value password 076 * If specified, password overrides the password value specified in the ODBC setup. 077 * @param password value to set 078 **/ 079 public void setPassword(String password) { 080 this.password=password; 081 } 082 083 /** set the value datasource 084 * Name of the data source that contains your table. 085 * @param datasource value to set 086 **/ 087 public void setDatasource(String datasource) { 088 this.datasource=datasource; 089 } 090 091 /** set the value username 092 * If specified, username overrides the username value specified in the ODBC setup. 093 * @param username value to set 094 **/ 095 public void setUsername(String username) { 096 this.username=username; 097 } 098 099 /** set the value formfields 100 * A comma-separated list of form fields to insert. If this attribute is not specified, all 101 * fields in the form are included in the operation. 102 * @param formfields value to set 103 **/ 104 public void setFormfields(String formfields) { 105 this.formfields=formfields.toLowerCase().trim(); 106 } 107 108 /** set the value tableowner 109 * For data sources that support table ownership such as SQL Server, Oracle, and Sybase SQL 110 * Anywhere, use this field to specify the owner of the table. 111 * @param tableowner value to set 112 **/ 113 public void setTableowner(String tableowner) { 114 this.tableowner=tableowner; 115 } 116 117 /** set the value tablename 118 * Name of the table you want the form fields inserted in. 119 * @param tablename value to set 120 **/ 121 public void setTablename(String tablename) { 122 this.tablename=tablename; 123 } 124 125 /** set the value tablequalifier 126 * For data sources that support table qualifiers, use this field to specify the qualifier for the 127 * table. The purpose of table qualifiers varies across drivers. For SQL Server and Oracle, the qualifier 128 * refers to the name of the database that contains the table. For the Intersolv dBase driver, the 129 * qualifier refers to the directory where the DBF files are located. 130 * @param tablequalifier value to set 131 **/ 132 public void setTablequalifier(String tablequalifier) { 133 this.tablequalifier=tablequalifier; 134 } 135 136 137 /** 138 * @see javax.servlet.jsp.tagext.Tag#doStartTag() 139 */ 140 public int doStartTag() { 141 return SKIP_BODY; 142 } 143 144 /** 145 * @throws PageException 146 * @see javax.servlet.jsp.tagext.Tag#doEndTag() 147 */ 148 public int doEndTag() throws PageException { 149 datasource=getDatasource(pageContext,datasource); 150 151 152 153 DataSourceManager manager = pageContext.getDataSourceManager(); 154 DatasourceConnection dc=manager.getConnection(pageContext,datasource,username,password); 155 try { 156 157 Struct meta =null; 158 try { 159 meta=getMeta(dc,tablequalifier,tableowner,tablename); 160 } 161 catch(SQLException se){ 162 meta=new StructImpl(); 163 } 164 165 166 SQL sql=createSQL(meta); 167 if(sql!=null) { 168 railo.runtime.type.Query query = new QueryImpl(dc,sql,-1,-1,-1,"query"); 169 170 if(pageContext.getConfig().debug()) { 171 boolean debugUsage=DebuggerImpl.debugQueryUsage(pageContext,query); 172 ((DebuggerImpl)pageContext.getDebugger()).addQuery(debugUsage?query:null,datasource,"",sql,query.getRecordcount(),pageContext.getCurrentPageSource(),query.executionTime()); 173 } 174 } 175 return EVAL_PAGE; 176 } 177 finally { 178 manager.releaseConnection(pageContext,dc); 179 } 180 } 181 182 183 184 185 public static String getDatasource(PageContext pageContext, String datasource) throws ApplicationException { 186 if(StringUtil.isEmpty(datasource)){ 187 datasource=((ApplicationContextPro)pageContext.getApplicationContext()).getDefaultDataSource(); 188 189 if(StringUtil.isEmpty(datasource)) 190 throw new ApplicationException( 191 "attribute [datasource] is required, when no default datasource is defined", 192 "you can define a default datasource as attribute [defaultdatasource] of the tag cfapplication or as data member of the application.cfc (this.defaultdatasource=\"mydatasource\";)"); 193 } 194 return datasource; 195 } 196 197 public static Struct getMeta(DatasourceConnection dc,String tableQualifier, String tableOwner, String tableName) throws SQLException { 198 DatabaseMetaData md = dc.getConnection().getMetaData(); 199 Struct sct=new StructImpl(); 200 ResultSet columns = md.getColumns(tableQualifier, tableOwner, tableName, null); 201 202 try{ 203 String name; 204 while(columns.next()) { 205 name=columns.getString("COLUMN_NAME"); 206 sct.setEL(name, new ColumnInfo(name,columns.getInt("DATA_TYPE"),columns.getBoolean("IS_NULLABLE"))); 207 208 } 209 } 210 finally { 211 DBUtil.closeEL(columns); 212 } 213 return sct; 214 } 215 216 /** 217 * @param meta 218 * @return return SQL String for insert 219 * @throws PageException 220 */ 221 private SQL createSQL(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 names=new StringBuffer(); 228 StringBuffer values=new StringBuffer(); 229 ArrayList items=new ArrayList(); 230 String field; 231 for(int i=0;i<fields.length;i++) { 232 field = StringUtil.trim(fields[i],null); 233 if(StringUtil.startsWithIgnoreCase(field, "form.")) 234 field=field.substring(5); 235 236 if(!field.equalsIgnoreCase("fieldnames")) { 237 if(names.length()>0) { 238 names.append(','); 239 values.append(','); 240 } 241 names.append(field); 242 values.append('?'); 243 ColumnInfo ci=(ColumnInfo) meta.get(field,null); 244 if(ci!=null)items.add(new SQLItemImpl(form.get(field,null),ci.getType())); 245 else items.add(new SQLItemImpl(form.get(field,null))); 246 } 247 } 248 if(items.size()==0) return null; 249 250 StringBuffer sql=new StringBuffer(); 251 sql.append("insert into "); 252 if(tablequalifier.length()>0) { 253 sql.append(tablequalifier); 254 sql.append('.'); 255 } 256 if(tableowner.length()>0) { 257 sql.append(tableowner); 258 sql.append('.'); 259 } 260 sql.append(tablename); 261 sql.append('('); 262 sql.append(names); 263 sql.append(")values("); 264 sql.append(values); 265 sql.append(")"); 266 267 return new SQLImpl(sql.toString(),(SQLItem[])items.toArray(new SQLItem[items.size()])); 268 } 269 270 271 272 273 274 275 276 277 278 } 279 280 class ColumnInfo { 281 282 /** 283 * @return the name 284 */ 285 public String getName() { 286 return name; 287 } 288 289 /** 290 * @return the type 291 */ 292 public int getType() { 293 return type; 294 } 295 296 /** 297 * @return the nullable 298 */ 299 public boolean isNullable() { 300 return nullable; 301 } 302 303 private String name; 304 private int type; 305 private boolean nullable; 306 307 public ColumnInfo(String name, int type, boolean nullable) { 308 this.name=name; 309 this.type=type; 310 this.nullable=nullable; 311 } 312 313 /** 314 * @see java.lang.Object#toString() 315 */ 316 public String toString(){ 317 return name+"-"+type+"-"+nullable; 318 } 319 320 }