Community
Participate
Working Groups
Build Identifier: 20111108 Manually changed setting parameters in egl sql statement. The generated java codes only change the stmtStr, but paramters index codes are not updated. Reproducible: Always Steps to Reproduce: 1.Connect DB2 SAMPLE database. 2.New a basic record along with the table SALES. record sales_rec {@table{name = "THOMASWU.SALES"}} SALES_DATE date? = "08/24/1900"; SALES_PERSON string? = "parma"; REGION string? = "KK"; SALES int? = 23; end 3.New a basic program, add the following replace code with Control+1 replace sales_rec to ds with #sql{ update THOMASWU.SALES set SALES_DATE = ?, SALES_PERSON = ?, REGION = ?, SALES = ? }; 4. Manually change the embeded sql statement as the following, then save the egl file. I removed the first parameter "SALES_DATE = ?" and added "where SALES_DATE = '08/24/1900'" here. replace sales_rec to ds with #sql{ update THOMASWU.SALES set SALES_PERSON = ?, REGION = ?, SALES = ? where SALES_DATE = '08/24/1900' }; Actual results: The generated java codes as following only updated sql in stmtStr, but the parameters index still included SALE_DATE I deleted, which will lead to sql error during executing. try { java.sql.PreparedStatement ezeStatement = (java.sql.PreparedStatement)ds.getStatement("testdb2.testSQLforDB2", 8); if (ezeStatement== null) { String stmtStr = "update THOMASWU.SALES set SALES_PERSON = ?, REGION = ?, SALES = ? where SALES_DATE = '08/24/1900'"; ezeStatement = ds.getConnection().prepareStatement(stmtStr); ds.registerStatement("testdb2.testSQLforDB2", 8, ezeStatement); } if(null == sales_rec1.SALES_DATE){ ezeStatement.setNull(1, java.sql.Types.DATE); } else{ ezeStatement.setDate(1, new java.sql.Date(sales_rec1.SALES_DATE.getTimeInMillis())); } if(null == sales_rec1.SALES_PERSON){ ezeStatement.setNull(2, java.sql.Types.VARCHAR); } else{ ezeStatement.setString(2, sales_rec1.SALES_PERSON); } if(null == sales_rec1.REGION){ ezeStatement.setNull(3, java.sql.Types.VARCHAR); } else{ ezeStatement.setString(3, sales_rec1.REGION); } if(null == sales_rec1.SALES){ ezeStatement.setNull(4, java.sql.Types.INTEGER); } else{ ezeStatement.setInt(4, sales_rec1.SALES); } ezeStatement.executeUpdate(); } catch(java.sql.SQLException ezeEx) { throw org.eclipse.edt.javart.util.JavartUtil.makeEglException(ezeEx); } ;
The reason you don't get a where clause is because you don't have any key fields defined. Add @ID to the key fields and the key fields will be moved from the set to the where fields.
Hello, Joe. The issue is not "where" clause missing. Here is another example. I have the following record mapping to a DB2 table. record act_std type Entity{@table{name = "THOMASWU.ACT"}} ACTNO smallInt{@id} = 123; ACTKWD string = "BMW"; ACTDESC string = "Car"; end I write a replace sql in egl as following, and it could be run successfully. The egl codes are: replace actStd to ds using(123) with #sql{ update THOMASWU.ACT set ACTKWD = ?, ACTDESC = ? where ACTNO = ? }; The generated java codes are: try { java.sql.PreparedStatement ezeStatement = (java.sql.PreparedStatement)ds.getStatement("testdb2.testSQLforDB2", 8); if (ezeStatement== null) { String stmtStr = "update THOMASWU.ACT set ACTKWD = ?, ACTDESC = ? where ACTNO = ?"; ezeStatement = ds.getConnection().prepareStatement(stmtStr); ds.registerStatement("testdb2.testSQLforDB2", 8, ezeStatement); } ezeStatement.setShort(1, (short) 123); ezeStatement.setString(1, actStd.ACTKWD); ezeStatement.setString(2, actStd.ACTDESC); ezeStatement.setObject(3, actStd.ACTNO); ezeStatement.executeUpdate(); } catch(java.sql.SQLException ezeEx) { throw org.eclipse.edt.javart.util.JavartUtil.makeEglException(ezeEx); } ; If I remove the follwoing line, "ACTKWD = ?," in egl codes, and save the change. The revised egl codes are: replace actStd to ds using(123) with #sql{ update THOMASWU.ACT set ACTDESC = ? where ACTNO = ? }; The generated java codes will be changed as following: try { java.sql.PreparedStatement ezeStatement = (java.sql.PreparedStatement)ds.getStatement("testdb2.testSQLforDB2", 8); if (ezeStatement== null) { String stmtStr = "update THOMASWU.ACT set ACTDESC = ? where ACTNO = ?"; ezeStatement = ds.getConnection().prepareStatement(stmtStr); ds.registerStatement("testdb2.testSQLforDB2", 8, ezeStatement); } ezeStatement.setShort(1, (short) 123); ezeStatement.setString(1, actStd.ACTKWD); ezeStatement.setString(2, actStd.ACTDESC); ezeStatement.setObject(3, actStd.ACTNO); ezeStatement.executeUpdate(); } catch(java.sql.SQLException ezeEx) { throw org.eclipse.edt.javart.util.JavartUtil.makeEglException(ezeEx); } ; You could see that stmtStr has removed ACTKWD, there are only 2 parameters "?". But in the ezeStatement.setXXX codes, there are still 3 parameters, the actStd.ACTKWD is still there. Please refer to screenshot.
Created attachment 206752 [details] Parameters error
When a GET action has a USING clause the USING clause is used to generate the set statements for the SQL where clause and the @ID columns are ignored. I made the DELETE and REPLACE consistent with the GET. When the DELETE and REPLACE have a USING clause the USING fields are used to generate the set statements for the SQL where clause, the @ID columns will be ignored. Your example is not valid, your SQL must match the record being used. We don't parse the SQL so we are nat able to determine that you have more fields than ?'s. If the record has 5 non ID fields your SQL must have 5 ? in the SET. For 070 only a record can be used as a target for a REPLACE action. In 1.0 we will investigate opening up the spec to allow scalars as a target for a REPLACE action.
Thanks for your explaination.
