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    }