001/** 002 * 003 * Copyright (c) 2014, the Railo Company Ltd. All rights reserved. 004 * 005 * This library is free software; you can redistribute it and/or 006 * modify it under the terms of the GNU Lesser General Public 007 * License as published by the Free Software Foundation; either 008 * version 2.1 of the License, or (at your option) any later version. 009 * 010 * This library is distributed in the hope that it will be useful, 011 * but WITHOUT ANY WARRANTY; without even the implied warranty of 012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 013 * Lesser General Public License for more details. 014 * 015 * You should have received a copy of the GNU Lesser General Public 016 * License along with this library. If not, see <http://www.gnu.org/licenses/>. 017 * 018 **/ 019package lucee.runtime.tag; 020 021import java.sql.DatabaseMetaData; 022import java.sql.ResultSet; 023import java.sql.SQLException; 024import java.util.ArrayList; 025 026import lucee.commons.db.DBUtil; 027import lucee.commons.io.log.Log; 028import lucee.commons.io.log.LogUtil; 029import lucee.commons.lang.StringUtil; 030import lucee.runtime.PageContext; 031import lucee.runtime.config.ConfigImpl; 032import lucee.runtime.config.ConfigWebImpl; 033import lucee.runtime.config.Constants; 034import lucee.runtime.db.DataSource; 035import lucee.runtime.db.DataSourceManager; 036import lucee.runtime.db.DatasourceConnection; 037import lucee.runtime.db.SQL; 038import lucee.runtime.db.SQLImpl; 039import lucee.runtime.db.SQLItem; 040import lucee.runtime.db.SQLItemImpl; 041import lucee.runtime.debug.DebuggerPro; 042import lucee.runtime.debug.DebuggerUtil; 043import lucee.runtime.exp.ApplicationException; 044import lucee.runtime.exp.PageException; 045import lucee.runtime.ext.tag.TagImpl; 046import lucee.runtime.functions.displayFormatting.DecimalFormat; 047import lucee.runtime.listener.ApplicationContextPro; 048import lucee.runtime.op.Caster; 049import lucee.runtime.type.QueryImpl; 050import lucee.runtime.type.Struct; 051import lucee.runtime.type.StructImpl; 052import lucee.runtime.type.scope.Form; 053import lucee.runtime.type.util.CollectionUtil; 054import lucee.runtime.type.util.ListUtil; 055 056/** 057* Inserts records in data sources. 058* 059* 060* 061**/ 062public final class Insert extends TagImpl { 063 064 /** If specified, password overrides the password value specified in the ODBC setup. */ 065 private String password; 066 067 /** Name of the data source that contains your table. */ 068 private String datasource; 069 070 /** If specified, username overrides the username value specified in the ODBC setup. */ 071 private String username; 072 073 /** A comma-separated list of form fields to insert. If this attribute is not specified, all 074 ** fields in the form are included in the operation. */ 075 private String formfields; 076 077 /** For data sources that support table ownership such as SQL Server, Oracle, and Sybase SQL 078 ** Anywhere, use this field to specify the owner of the table. */ 079 private String tableowner=""; 080 081 /** Name of the table you want the form fields inserted in. */ 082 private String tablename; 083 084 /** For data sources that support table qualifiers, use this field to specify the qualifier for the 085 ** table. The purpose of table qualifiers varies across drivers. For SQL Server and Oracle, the qualifier 086 ** refers to the name of the database that contains the table. For the Intersolv dBase driver, the 087 ** qualifier refers to the directory where the DBF files are located. */ 088 private String tablequalifier=""; 089 090 @Override 091 public void release() { 092 super.release(); 093 password=null; 094 username=null; 095 formfields=null; 096 tableowner=""; 097 tablequalifier=""; 098 datasource=null; 099 } 100 101 /** set the value password 102 * If specified, password overrides the password value specified in the ODBC setup. 103 * @param password value to set 104 **/ 105 public void setPassword(String password) { 106 this.password=password; 107 } 108 109 /** set the value datasource 110 * Name of the data source that contains your table. 111 * @param datasource value to set 112 **/ 113 public void setDatasource(String datasource) { 114 this.datasource=datasource; 115 } 116 117 /** set the value username 118 * If specified, username overrides the username value specified in the ODBC setup. 119 * @param username value to set 120 **/ 121 public void setUsername(String username) { 122 this.username=username; 123 } 124 125 /** set the value formfields 126 * A comma-separated list of form fields to insert. If this attribute is not specified, all 127 * fields in the form are included in the operation. 128 * @param formfields value to set 129 **/ 130 public void setFormfields(String formfields) { 131 this.formfields=formfields.toLowerCase().trim(); 132 } 133 134 /** set the value tableowner 135 * For data sources that support table ownership such as SQL Server, Oracle, and Sybase SQL 136 * Anywhere, use this field to specify the owner of the table. 137 * @param tableowner value to set 138 **/ 139 public void setTableowner(String tableowner) { 140 this.tableowner=tableowner; 141 } 142 143 /** set the value tablename 144 * Name of the table you want the form fields inserted in. 145 * @param tablename value to set 146 **/ 147 public void setTablename(String tablename) { 148 this.tablename=tablename; 149 } 150 151 /** set the value tablequalifier 152 * For data sources that support table qualifiers, use this field to specify the qualifier for the 153 * table. The purpose of table qualifiers varies across drivers. For SQL Server and Oracle, the qualifier 154 * refers to the name of the database that contains the table. For the Intersolv dBase driver, the 155 * qualifier refers to the directory where the DBF files are located. 156 * @param tablequalifier value to set 157 **/ 158 public void setTablequalifier(String tablequalifier) { 159 this.tablequalifier=tablequalifier; 160 } 161 162 163 @Override 164 public int doStartTag() { 165 return SKIP_BODY; 166 } 167 168 @Override 169 public int doEndTag() throws PageException { 170 Object ds=getDatasource(pageContext,datasource); 171 172 173 174 DataSourceManager manager = pageContext.getDataSourceManager(); 175 DatasourceConnection dc=ds instanceof DataSource? 176 manager.getConnection(pageContext,(DataSource)ds,username,password): 177 manager.getConnection(pageContext,Caster.toString(ds),username,password); 178 try { 179 180 Struct meta =null; 181 try { 182 meta=getMeta(dc,tablequalifier,tableowner,tablename); 183 } 184 catch(SQLException se){ 185 meta=new StructImpl(); 186 } 187 188 189 SQL sql=createSQL(meta); 190 if(sql!=null) { 191 lucee.runtime.type.Query query = new QueryImpl(pageContext,dc,sql,-1,-1,null,"query"); 192 193 if(pageContext.getConfig().debug()) { 194 String dsn=ds instanceof DataSource?((DataSource)ds).getName():Caster.toString(ds); 195 boolean logdb=((ConfigImpl)pageContext.getConfig()).hasDebugOptions(ConfigImpl.DEBUG_DATABASE); 196 if(logdb) { 197 boolean debugUsage=DebuggerUtil.debugQueryUsage(pageContext,query); 198 ((DebuggerPro)pageContext.getDebugger()).addQuery(debugUsage?query:null,dsn,"",sql,query.getRecordcount(),pageContext.getCurrentPageSource(),query.getExecutionTime()); 199 } 200 } 201 // log 202 Log log = ((ConfigWebImpl)pageContext.getConfig()).getLog("datasource", true); 203 if(log.getLogLevel()>=Log.LEVEL_INFO) { 204 log.info("insert tag", "executed ["+sql.toString().trim()+"] in "+DecimalFormat.call(pageContext, query.getExecutionTime()/1000000D)+" ms"); 205 } 206 207 } 208 return EVAL_PAGE; 209 } 210 catch (PageException pe) { 211 // log 212 LogUtil.log(((ConfigWebImpl)pageContext.getConfig()).getLog("datasource", true) 213 , Log.LEVEL_ERROR, "insert tag", pe); 214 throw pe; 215 } 216 finally { 217 manager.releaseConnection(pageContext,dc); 218 } 219 } 220 221 222 223 224 public static Object getDatasource(PageContext pageContext, String datasource) throws ApplicationException { 225 if(StringUtil.isEmpty(datasource)){ 226 Object ds = ((ApplicationContextPro)pageContext.getApplicationContext()).getDefDataSource(); 227 228 if(StringUtil.isEmpty(ds)) 229 throw new ApplicationException( 230 "attribute [datasource] is required, when no default datasource is defined", 231 "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\";)"); 232 return ds; 233 } 234 return datasource; 235 } 236 237 public static Struct getMeta(DatasourceConnection dc,String tableQualifier, String tableOwner, String tableName) throws SQLException { 238 DatabaseMetaData md = dc.getConnection().getMetaData(); 239 Struct sct=new StructImpl(); 240 ResultSet columns = md.getColumns(tableQualifier, tableOwner, tableName, null); 241 242 try{ 243 String name; 244 while(columns.next()) { 245 name=columns.getString("COLUMN_NAME"); 246 sct.setEL(name, new ColumnInfo(name,columns.getInt("DATA_TYPE"),columns.getBoolean("IS_NULLABLE"))); 247 248 } 249 } 250 finally { 251 DBUtil.closeEL(columns); 252 } 253 return sct; 254 } 255 256 /** 257 * @param meta 258 * @return return SQL String for insert 259 * @throws PageException 260 */ 261 private SQL createSQL(Struct meta) throws PageException { 262 String[] fields=null; 263 Form form = pageContext.formScope(); 264 if(formfields!=null) fields=ListUtil.toStringArray(ListUtil.listToArrayRemoveEmpty(formfields,',')); 265 else fields=CollectionUtil.keysAsString(pageContext.formScope()); 266 267 StringBuffer names=new StringBuffer(); 268 StringBuffer values=new StringBuffer(); 269 ArrayList items=new ArrayList(); 270 String field; 271 for(int i=0;i<fields.length;i++) { 272 field = StringUtil.trim(fields[i],null); 273 if(StringUtil.startsWithIgnoreCase(field, "form.")) 274 field=field.substring(5); 275 276 if(!field.equalsIgnoreCase("fieldnames")) { 277 if(names.length()>0) { 278 names.append(','); 279 values.append(','); 280 } 281 names.append(field); 282 values.append('?'); 283 ColumnInfo ci=(ColumnInfo) meta.get(field,null); 284 if(ci!=null)items.add(new SQLItemImpl(form.get(field,null),ci.getType())); 285 else items.add(new SQLItemImpl(form.get(field,null))); 286 } 287 } 288 if(items.size()==0) return null; 289 290 StringBuffer sql=new StringBuffer(); 291 sql.append("insert into "); 292 if(tablequalifier.length()>0) { 293 sql.append(tablequalifier); 294 sql.append('.'); 295 } 296 if(tableowner.length()>0) { 297 sql.append(tableowner); 298 sql.append('.'); 299 } 300 sql.append(tablename); 301 sql.append('('); 302 sql.append(names); 303 sql.append(")values("); 304 sql.append(values); 305 sql.append(")"); 306 307 return new SQLImpl(sql.toString(),(SQLItem[])items.toArray(new SQLItem[items.size()])); 308 } 309 310 311 312 313 314 315 316 317 318} 319 320class ColumnInfo { 321 322 /** 323 * @return the name 324 */ 325 public String getName() { 326 return name; 327 } 328 329 /** 330 * @return the type 331 */ 332 public int getType() { 333 return type; 334 } 335 336 /** 337 * @return the nullable 338 */ 339 public boolean isNullable() { 340 return nullable; 341 } 342 343 private String name; 344 private int type; 345 private boolean nullable; 346 347 public ColumnInfo(String name, int type, boolean nullable) { 348 this.name=name; 349 this.type=type; 350 this.nullable=nullable; 351 } 352 353 @Override 354 public String toString(){ 355 return name+"-"+type+"-"+nullable; 356 } 357 358}