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    }