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