KSQL报错

ly201026 · 发表于 2013-11-6 09:58:23

这个KSQL有什么问题？执行报错

UPDATE TC_InstorageGoodsDetail t SET t.fWPZT = '在库'
WHERE EXISTS (
SELECT a.*
FROM TC_InstorageDetail a
WHERE t.GoodsID = a.GoodsID AND a.InStoreMode = '采购入库' AND a.fID = t.fMaster AND a.InStoreNo = 'RK2013110600001')

复制代码

我把a.fID 改成a也不行

ecoolper · 发表于 2013-11-6 10:03:43

错误信息贴出来

ly201026 · 发表于 2013-11-6 10:17:31

ecoolper 发表于 2013-11-6 10:03
错误信息贴出来

2013-11-06 10:13:34 excute Action error:<?xml version="1.0" encoding="UTF-8"?>
<actions><action direct-execute="false" process="/TCERP/InStorage/process/test/testProcess" activity="businessActivity2" action-flag="__action_0__" executor="/ORG01.ogn/PSN01@ORG01.psm" execute-context="" name="externalAdvanceProcessAction" accept="*/*"> <parameters xmlns:xbiz="http://www.justep.com/xbiz#"> <parameter name="task"> <xbiz:simple type="http://www.w3.org/2001/XMLSchema#String">1EF37BD5A306406E8C7866C238CCE997</xbiz:simple> </parameter><parameter name="control"><xbiz:object class="com.justep.system.process.ProcessControl"><process-control><task-join>false</task-join><save-history>true</save-history><task-join-mode>twmMultiple</task-join-mode><customized-enabled>false</customized-enabled><jump-enabled>false</jump-enabled><message/><dialog-enabled>true</dialog-enabled><status>none</status><to><process-control-item id="D774D0B13236488D9DD59319A23F842A" activity-id="D774D0B13236488D9DD59319A23F842A" is-end="true" selected="true" readonly="true"><unit>end1</unit><template/><process>/TCERP/InStorage/process/test/testProcess</process><executor-kinds/><executor-range/><executors/><task-relation-value><sTypeName>入库管理</sTypeName><sLock>3BD744EF894B430DB411B5006CB26254</sLock><SA_Task>259AEBD4B22E4E24BDC9DBA868A31724</SA_Task><sActivityInTemplate>end1</sActivityInTemplate><sProcessTemplateID2/><sProcess>/TCERP/InStorage/process/test/testProcess</sProcess><sActivityName/><sLastModifyTime>2013-11-06T10:13:26.375Z</sLastModifyTime><sProcessName>入库管理</sProcessName></task-relation-value></process-control-item></to><notice/></process-control>
</xbiz:object></parameter> </parameters></action></actions>
com.justep.model.exception.ModelException: java.lang.reflect.InvocationTargetException
at com.justep.system.action.Engine.invokeActions(Unknown Source)
at com.justep.business.server.BusinessServer.doAction(Unknown Source)
at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
at com.justep.business.server.BusinessServer.excute(Unknown Source)
at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:592)
at com.justep.system.action.Engine.invokeActions(Native Method)
... 21 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:592)
at com.justep.system.action.Engine.invokeAction(Native Method)
at com.justep.system.action.Engine.invokeAction(Unknown Source)
at com.justep.model.impl.UtilImplementInstance.invokeAction(Unknown Source)
at com.justep.model.impl.UtilImplementInstance.invokeAction(Unknown Source)
at com.justep.system.action.ActionUtils.invokeAction(Unknown Source)
at com.justep.system.process.ProcessUtils.advanceProcess(Unknown Source)
at ExternalProcessProcedure.advanceProcess(ExternalProcessProcedure.java:34)
... 26 more
Caused by: com.justep.model.exception.ModelException: 执行ksql:update TC_InstorageGoodsDetail t set t.fWPZT = '在库' where exists ( select a.* from TC_InstorageDetail a where t.GoodsID = a.GoodsID and a.InStoreMode = '采购入库' and a.fID = t.fMaster and a.InStoreNo = 'RK2013110600001')时出错！
at com.justep.system.data.impl.TableUtilsImplement.ksqlUpdate(Unknown Source)
at com.justep.system.data.impl.TableUtilsImplement.ksqlExecuteUpdate(Unknown Source)
at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
at TestProcess.updateGoodsDetail(TestProcess.java:131)
at TestProcess.businessActivity2AfterAdvance(TestProcess.java:103)
... 37 more
Caused by: com.justep.model.exception.ModelException: 模型/TCERP/InStorage/data下未找到概念TC_InstorageDetail的Store配置！
at com.justep.util.Utils.check(Unknown Source)
at com.justep.system.ksql.ConceptMapping.getConceptMapping(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.confirmSymTable(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.handleAllRelation(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.selectSentenceNode(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.selectSentenceNode(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.compExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.notExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.andExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.orExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.conditionExpression(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.whereExpression(Unknown Source)
at com.justep.system.ksql.parser.UpdateParser.start(Unknown Source)
at com.justep.system.ksql.parser.AbstractParser.parse(Unknown Source)
... 44 more

复制代码

后台异常信息提示找不到store配置，但是SQL中的两张表都是在同一个业务模块下的啊

ecoolper · 发表于 2013-11-6 10:29:26

x5对子查询支持的不全面，建议直接放弃这种写法，
用com.justep.system.data.SQL.executeUpdate方法做更新

另：希望x5在KSQL方面加强支持

ly201026 · 发表于 2013-11-6 10:40:35

ecoolper 发表于 2013-11-6 10:29
x5对子查询支持的不全面，建议直接放弃这种写法，
用com.justep.system.data.SQL.executeUpdate方法做更新
...

那个List binds参数是什么参数？是SQL语句拼接时的参数吗？

fpj · 发表于 2013-11-6 10:47:07

ly201026 发表于 2013-11-6 10:40
那个List binds参数是什么参数？是SQL语句拼接时的参数吗？

打开本模块data目录中的model.config.xml文件，include其概念TC_InstorageDetail 模块的data目录

把a.fID 改成a　再试试

ecoolper · 发表于 2013-11-6 10:47:49

对，是参数，jdbc原生的参数写法：select * from table where fid =?

ly201026 · 发表于 2013-11-6 10:53:54

fpj 发表于 2013-11-6 10:47
打开本模块data目录中的model.config.xml文件，include其概念TC_InstorageDetail 模块的data目录

把a.fI ...

不行的，那两个表本来就在改业务模块下。data应该不需要引用。a.fid改成a也试过

ly201026 · 发表于 2013-11-6 10:59:05

ecoolper 发表于 2013-11-6 10:47
对，是参数，jdbc原生的参数写法：select * from table where fid =?

public static void updateGoodsDetail() {
String status = "";
String inStoreNo = ProcessUtils.getProcessData1();
String detailSql = "select t.* from TC_InStorageDetail t where t.InStoreNo = '"+inStoreNo+"'";
Table table = KSQL.select(detailSql, null, DATA_MODEL, null);
Iterator<Row> it = table.iterator();
while(it.hasNext()) {
Row row = it.next();
String storeMode = row.getString("InStoreMode");
if("采购入库".equals(storeMode) || "翻新入库".equals(storeMode)) {
status = "在库";
}else if("废品入库".equals(storeMode)) {
status = "旧品在库";
}
List list = new ArrayList();
list.add(status);
list.add(storeMode);
list.add(inStoreNo);
String sql = "update TC_InstorageGoodsDetail t " +
" set t.fWPZT = ? " +
"where exists (" +
" select a.* from TC_InstorageDetail a " +
" where t.GoodsID = a.GoodsID " +
" and a.InStoreMode = ?" +
" and a = t.fMaster" +
" and a.InStoreNo = ?')";
Map<String,String> mapSql = new HashMap<String,String>();
mapSql.put("sql", sql);
// KSQL.executeUpdate(sql, null, DATA_MODEL, null);
SQL.executeUpdate(mapSql, list, DATA_MODEL);

复制代码

是这样写的吗？貌似有提示非法SQL参数什么意思？

Caused by: com.justep.model.exception.ModelException: 非法的sql参数，当前的数据库类型：MSSQL传入的sql中的数据库类型：[sql]
at com.justep.util.Utils.check(Unknown Source)
at com.justep.system.data.SQL.executeUpdate(Unknown Source)
at com.justep.system.data.SQL.executeUpdate(Unknown Source)
at TestProcess.updateGoodsDetail(TestProcess.java:141)
at TestProcess.businessActivity2AfterAdvance(TestProcess.java:106)
... 37 more

复制代码

ecoolper · 发表于 2013-11-6 10:59:24

这是x5.2.4中demo的例子

String oracle = "SELECT * FROM ( SELECT RPRODUCTID , RPRODUCTNAME ,(SELECT RCATEGORYNAME FROM DEMO_REPORTPRODUCTCATEGORY WHERE DEMO_REPORTPRODUCTCATEGORY.RCATEGORYID = DEMO_REPORTPRODUCTS.RCATEGORYID) AS RCATEGORYNAME , (SELECT RCOMPANYNAME FROM DEMO_REPORTSUPPLIERS WHERE DEMO_REPORTSUPPLIERS.RSUPPLIERID = DEMO_REPORTPRODUCTS.RSUPPLIERID)AS RCOMPANYNAME, RQUANTITYPERUNIT, RUNITPRICE, RUNITSINSTOCK FROM DEMO_REPORTPRODUCTS )A ";
String mssql = "SELECT * FROM ( SELECT RPRODUCTID , RPRODUCTNAME ,(SELECT RCATEGORYNAME FROM DEMO_REPORTPRODUCTCATEGORY WHERE DEMO_REPORTPRODUCTCATEGORY.RCATEGORYID = DEMO_REPORTPRODUCTS.RCATEGORYID) AS RCATEGORYNAME , (SELECT RCOMPANYNAME FROM DEMO_REPORTSUPPLIERS WHERE DEMO_REPORTSUPPLIERS.RSUPPLIERID = DEMO_REPORTPRODUCTS.RSUPPLIERID)AS RCOMPANYNAME, RQUANTITYPERUNIT, RUNITPRICE, RUNITSINSTOCK FROM DEMO_REPORTPRODUCTS )A ";
oracle += " WHERE 1=1 ";
mssql += " WHERE 1=1 ";
if(Utils.isNotEmptyString(company)&& !company.equals("null") ){
oracle += String.format("and A.RCOMPANYNAME = '%s' ", company);
mssql += String.format("and A.RCOMPANYNAME = '%s' ", company);
}
if(Utils.isNotEmptyString(product)&&!product.equals("null") ){
oracle += String.format("and A.RPRODUCTNAME = '%s' ", product);
mssql += String.format("and A.RPRODUCTNAME = '%s' ", product);
}
if(Utils.isNotEmptyString(category)&&!category.equals("null") ){
oracle += String.format("and A.RCATEGORYNAME = '%s' ", category);
mssql += String.format("and A.RCATEGORYNAME = '%s' ", category);
}
HashMap<String,String> sqlMap = new HashMap<String,String>();
sqlMap.put(SQL.DEFAULT_DB_NAME,mssql);
sqlMap.put(DatabaseProduct.ORACLE.name(),oracle);
sqlMap.put(DatabaseProduct.MSSQL.name(),mssql);
sqlMap.put(DatabaseProduct.KINGBASE.name(),oracle);
sqlMap.put(DatabaseProduct.SYBASE.name(),mssql);
sqlMap.put(DatabaseProduct.DB2.name(),oracle);
return SQL.select(sqlMap,null,"/demo/report/data");

复制代码

		自动登录	找回密码
密码			注册

[结贴] KSQL报错

评分