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