jakarta-ee - 为什么可空字段在此 WSO2 DSS INSERT 查询中不起作用?
问题描述
我正在研究WSO2 EI版本6.1.1,我发现一个大问题试图实现一个简单的DSS INSERT 查询,该查询必须将新记录插入到允许某些字段为空的表中。
这是我与此特定查询定义和相关操作相关的DSS代码:
<query id="insert_user" returnGeneratedKeys="true" useConfig="user_db">
<sql>
insert into User(
application_UID,
country_id,
localization_id,
local_user_id,
isActive,
isFullProfile,
birth_date,
gender,
prefix
)
values(
:application_UID,
:country_id,
:localization_id,
:local_user_id,
:is_active,
:is_full_profile,
:birth_date,
:gender,
:prefix
)
</sql>
<result element="GeneratedKeys" rowName="Entry" useColumnNumbers="true">
<element column="1" name="ID" xsdType="integer"/>
</result>
<param name="application_UID" sqlType="STRING"/>
<param name="country_id" sqlType="BIGINT"/>
<param name="localization_id" sqlType="BIGINT"/>
<param name="local_user_id" sqlType="BIGINT"/>
<param name="is_active" sqlType="BIT"/>
<param name="is_full_profile" sqlType="BIT"/>
<param defaultValue="#{NULL}" name="birth_date" sqlType="STRING"/>
<param defaultValue="#{NULL}" name="gender" sqlType="STRING"/>
<param defaultValue="#{NULL}" name="prefix" sqlType="STRING"/>
</query>
<operation name="AddUser">
<call-query href="insert_user">
<with-param name="application_UID" query-param="application_UID"/>
<with-param name="country_id" query-param="country_id"/>
<with-param name="localization_id" query-param="localization_id"/>
<with-param name="local_user_id" query-param="local_user_id"/>
<with-param name="is_active" query-param="is_active"/>
<with-param name="is_full_profile" query-param="is_full_profile"/>
<with-param name="birth_date" query-param="birth_date"/>
<with-param name="gender" query-param="gender"/>
<with-param name="prefix" query-param="prefix"/>
</call-query>
</operation>
最后 3 个字段在我的数据库上可以为空,如您所见,我以这种方式定义了这些字段(我使用defaultValue="#{NULL}"使这些字段可以为空):
<param defaultValue="#{NULL}" name="birth_date" sqlType="STRING"/>
<param defaultValue="#{NULL}" name="gender" sqlType="STRING"/>
<param defaultValue="#{NULL}" name="prefix" sqlType="STRING"/>
问题是,如果我进入 try it 工具并执行以下查询:
<body>
<p:AddUser xmlns:p="http://ws.wso2.org/dataservice">
<!--Exactly 1 occurrence-->
<p:application_UID>xxx</p:application_UID>
<!--Exactly 1 occurrence-->
<p:country_id>1</p:country_id>
<!--Exactly 1 occurrence-->
<p:localization_id>1</p:localization_id>
<!--Exactly 1 occurrence-->
<p:local_user_id>1</p:local_user_id>
<!--Exactly 1 occurrence-->
<p:is_active>true</p:is_active>
<!--Exactly 1 occurrence-->
<p:is_full_profile>true</p:is_full_profile>
<!--Exactly 1 occurrence-->
<p:birth_date>?</p:birth_date>
<!--Exactly 1 occurrence-->
<p:gender>?</p:gender>
<!--Exactly 1 occurrence-->
<p:prefix>?</p:prefix>
</p:AddUser>
</body>
(如您所见,我没有设置最后 3 个可为空字段的值)我在 Try It 工具的输出中收到此错误消息:
<soapenv:Fault xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope">
<soapenv:Code>
<soapenv:Value>axis2ns393:DATABASE_ERROR</soapenv:Value>
</soapenv:Code>
<soapenv:Reason>
<soapenv:Text xml:lang="en-US">DS Code: DATABASE_ERROR Nested Exception:- javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processPreNormalQuery': Data truncation: Incorrect date value: '?' for column 'birth_date' at row 1 DS Code: DATABASE_ERROR Source Data Service:- Name: user-dss Location: /opt/wso2/wso2ei/wso2/tmp/carbonapps/-1234/1524668046564userreg-cap_1.0.0.car/user-dss_1.0.0/user-dss-1.0.0.dbs Description: N/A Default Namespace: http://ws.wso2.org/dataservice Current Request Name: AddUser Current Params: {local_user_id=1, is_active=true, gender=?, prefix=?, birth_date=?, is_full_profile=true, application_UID=xxx, country_id=1, localization_id=1} Nested Exception:- com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '?' for column 'birth_date' at row 1 </soapenv:Text>
</soapenv:Reason>
<soapenv:Detail>
<axis2ns392:DataServiceFault xmlns:axis2ns392="http://ws.wso2.org/dataservice">
<axis2ns392:current_params>{local_user_id=1, is_active=true, gender=?, prefix=?, birth_date=?, is_full_profile=true, application_UID=xxx, country_id=1, localization_id=1}</axis2ns392:current_params>
<axis2ns392:source_data_service>
<axis2ns392:data_service_name>user-dss</axis2ns392:data_service_name>
<axis2ns392:description>N/A</axis2ns392:description>
<axis2ns392:location>/opt/wso2/wso2ei/wso2/tmp/carbonapps/-1234/1524668046564userreg-cap_1.0.0.car/user-dss_1.0.0/user-dss-1.0.0.dbs</axis2ns392:location>
<axis2ns392:default_namespace>http://ws.wso2.org/dataservice</axis2ns392:default_namespace>
</axis2ns392:source_data_service>
<axis2ns392:ds_code>DATABASE_ERROR</axis2ns392:ds_code>
<axis2ns392:nested_exception>com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '?' for column 'birth_date' at row 1</axis2ns392:nested_exception>
<axis2ns392:current_request_name>AddUser</axis2ns392:current_request_name>
</axis2ns392:DataServiceFault>
</soapenv:Detail>
</soapenv:Fault>
在我的碳日志中,我有这样的东西:
TID: [-1234] [] [2018-04-25 16:57:13,853] ERROR {org.wso2.carbon.dataservices.core.DBInOutMessageReceiver} - Error in in-out message receiver {org.wso2.carbon.dataservices.core.DBInOutMessageReceiver}
DS Code: DATABASE_ERROR
Nested Exception:-
javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processPreNormalQuery': Data truncation: Incorrect date value: '?' for column 'birth_date' at row 1
DS Code: DATABASE_ERROR
Source Data Service:-
Name: user-dss
Location: /opt/wso2/wso2ei/wso2/tmp/carbonapps/-1234/1524668046564userreg-cap_1.0.0.car/user-dss_1.0.0/user-dss-1.0.0.dbs
Description: N/A
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: AddUser
Current Params: {local_user_id=1, is_active=true, gender=?, prefix=?, birth_date=?, is_full_profile=true, application_UID=xxx, country_id=1, localization_id=1}
Nested Exception:-
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '?' for column 'birth_date' at row 1
at org.wso2.carbon.dataservices.core.dispatch.SingleDataServiceRequest.processRequest(SingleDataServiceRequest.java:75)
at org.wso2.carbon.dataservices.core.dispatch.DataServiceRequest.dispatch(DataServiceRequest.java:357)
at org.wso2.carbon.dataservices.core.DataServiceProcessor.dispatch(DataServiceProcessor.java:41)
at org.wso2.carbon.dataservices.core.DBInOutMessageReceiver.invokeBusinessLogic(DBInOutMessageReceiver.java:57)
at org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.invokeBusinessLogic(AbstractInOutSyncMessageReceiver.java:42)
at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:110)
at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:180)
at org.apache.synapse.transport.passthru.ServerWorker.processEntityEnclosingRequest(ServerWorker.java:404)
at org.apache.synapse.transport.passthru.ServerWorker.run(ServerWorker.java:151)
at org.apache.axis2.transport.base.threads.NativeWorkerPool$1.run(NativeWorkerPool.java:172)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processPreNormalQuery': Data truncation: Incorrect date value: '?' for column 'birth_date' at row 1
DS Code: DATABASE_ERROR
Source Data Service:-
Name: user-dss
Location: /opt/wso2/wso2ei/wso2/tmp/carbonapps/-1234/1524668046564userreg-cap_1.0.0.car/user-dss_1.0.0/user-dss-1.0.0.dbs
Description: N/A
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: AddUser
Current Params: {local_user_id=1, is_active=true, gender=?, prefix=?, birth_date=?, is_full_profile=true, application_UID=xxx, country_id=1, localization_id=1}
Nested Exception:-
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '?' for column 'birth_date' at row 1
at org.wso2.carbon.dataservices.core.engine.DSOMDataSource.execute(DSOMDataSource.java:102)
at org.wso2.carbon.dataservices.core.dispatch.SingleDataServiceRequest.processRequest(SingleDataServiceRequest.java:73)
... 12 more
TID: [-1234] [] [2018-04-25 16:57:13,855] ERROR {org.apache.synapse.transport.passthru.ServerWorker} - Error processing POST request for : /services/user-dss.SOAP12Endpoint/AddUser {org.apache.synapse.transport.passthru.ServerWorker}
org.apache.axis2.AxisFault: DS Code: DATABASE_ERROR
Nested Exception:-
javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processPreNormalQuery': Data truncation: Incorrect date value: '?' for column 'birth_date' at row 1
DS Code: DATABASE_ERROR
Source Data Service:-
Name: user-dss
Location: /opt/wso2/wso2ei/wso2/tmp/carbonapps/-1234/1524668046564userreg-cap_1.0.0.car/user-dss_1.0.0/user-dss-1.0.0.dbs
Description: N/A
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: AddUser
Current Params: {local_user_id=1, is_active=true, gender=?, prefix=?, birth_date=?, is_full_profile=true, application_UID=xxx, country_id=1, localization_id=1}
Nested Exception:-
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '?' for column 'birth_date' at row 1
at org.wso2.carbon.dataservices.core.DBUtils.createAxisFault(DBUtils.java:740)
at org.wso2.carbon.dataservices.core.DBInOutMessageReceiver.invokeBusinessLogic(DBInOutMessageReceiver.java:67)
at org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.invokeBusinessLogic(AbstractInOutSyncMessageReceiver.java:42)
at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:110)
at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:180)
at org.apache.synapse.transport.passthru.ServerWorker.processEntityEnclosingRequest(ServerWorker.java:404)
at org.apache.synapse.transport.passthru.ServerWorker.run(ServerWorker.java:151)
at org.apache.axis2.transport.base.threads.NativeWorkerPool$1.run(NativeWorkerPool.java:172)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processPreNormalQuery': Data truncation: Incorrect date value: '?' for column 'birth_date' at row 1
DS Code: DATABASE_ERROR
Source Data Service:-
Name: user-dss
Location: /opt/wso2/wso2ei/wso2/tmp/carbonapps/-1234/1524668046564userreg-cap_1.0.0.car/user-dss_1.0.0/user-dss-1.0.0.dbs
Description: N/A
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: AddUser
Current Params: {local_user_id=1, is_active=true, gender=?, prefix=?, birth_date=?, is_full_profile=true, application_UID=xxx, country_id=1, localization_id=1}
Nested Exception:-
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '?' for column 'birth_date' at row 1
at org.wso2.carbon.dataservices.core.engine.DSOMDataSource.execute(DSOMDataSource.java:102)
at org.wso2.carbon.dataservices.core.dispatch.SingleDataServiceRequest.processRequest(SingleDataServiceRequest.java:73)
at org.wso2.carbon.dataservices.core.dispatch.DataServiceRequest.dispatch(DataServiceRequest.java:357)
at org.wso2.carbon.dataservices.core.DataServiceProcessor.dispatch(DataServiceProcessor.java:41)
at org.wso2.carbon.dataservices.core.DBInOutMessageReceiver.invokeBusinessLogic(DBInOutMessageReceiver.java:57)
... 9 more
如果我使用这样的查询,它会发生类似的错误:
<body>
<p:AddUser xmlns:p="http://ws.wso2.org/dataservice">
<!--Exactly 1 occurrence-->
<p:application_UID>xxx</p:application_UID>
<!--Exactly 1 occurrence-->
<p:country_id>1</p:country_id>
<!--Exactly 1 occurrence-->
<p:localization_id>1</p:localization_id>
<!--Exactly 1 occurrence-->
<p:local_user_id>1</p:local_user_id>
<!--Exactly 1 occurrence-->
<p:is_active>true</p:is_active>
<!--Exactly 1 occurrence-->
<p:is_full_profile>true</p:is_full_profile>
<!--Exactly 1 occurrence-->
<p:birth_date></p:birth_date>
<!--Exactly 1 occurrence-->
<p:gender></p:gender>
<!--Exactly 1 occurrence-->
<p:prefix></p:prefix>
</p:AddUser>
</body>
也尝试以这种方式获得相同的问题:
<body>
<p:AddUser xmlns:p="http://ws.wso2.org/dataservice">
<!--Exactly 1 occurrence-->
<p:application_UID>xxx</p:application_UID>
<!--Exactly 1 occurrence-->
<p:country_id>1</p:country_id>
<!--Exactly 1 occurrence-->
<p:localization_id>1</p:localization_id>
<!--Exactly 1 occurrence-->
<p:local_user_id>1</p:local_user_id>
<!--Exactly 1 occurrence-->
<p:is_active>true</p:is_active>
<!--Exactly 1 occurrence-->
<p:is_full_profile>true</p:is_full_profile>
<!--Exactly 1 occurrence-->
<p:birth_date>null</p:birth_date>
<!--Exactly 1 occurrence-->
<p:gender>null</p:gender>
<!--Exactly 1 occurrence-->
<p:prefix>null</p:prefix>
</p:AddUser>
</body>
我发现 woks 的唯一方法是执行排除这些参数的查询,使用此查询可以正常工作:
<body>
<p:AddUser xmlns:p="http://ws.wso2.org/dataservice">
<!--Exactly 1 occurrence-->
<p:application_UID>xxx</p:application_UID>
<!--Exactly 1 occurrence-->
<p:country_id>1</p:country_id>
<!--Exactly 1 occurrence-->
<p:localization_id>1</p:localization_id>
<!--Exactly 1 occurrence-->
<p:local_user_id>1</p:local_user_id>
<!--Exactly 1 occurrence-->
<p:is_active>true</p:is_active>
<!--Exactly 1 occurrence-->
<p:is_full_profile>true</p:is_full_profile>
</p:AddUser>
</body>
在这种情况下有效并且记录正确插入到我的数据库表中,但我不能使用它,因为我必须从 ESB 流中调用此 DSS 服务并且必须使用所有参数(如果未设置参数或null) 它必须插入 null。
为什么?如何修复此查询并使用NULL值调用它,将 NULL 保存为我的可为空字段的值?
解决方案
为什么?
恕我直言,这是预期的行为
<p:birth_date>?</p:birth_date>
<!--Exactly 1 occurrence-->
<p:gender>?</p:gender>
<!--Exactly 1 occurrence-->
<p:prefix>?</p:prefix>
在这里您将文本 ?
作为日期发送,因此 mysql 正确地抛出了一个表达式
如果 <p:birth_date></p:birth_date>
值是一个空字符串并且拥有字符串null
也无济于事。
如何修复此查询
我不太使用 mysql,我建议使用 str_to_date 函数,它应该将 null 或空字符串表示为 null 值
推荐阅读
- python - 从文件名中提取日期并添加为列
- react-native - React Native AsyncStorage.getItem 不起作用。({“_40”:0,“_55”:空,“_65”:0,“_72”:空})
- machine-learning - 时间序列模型 - 设备/客户明智
- c# - 在类属性中设置自定义列名以导出到文件
- javascript - 如何将 Formik Field 渲染为 textarea + styled-components?
- mysql - 如何在node.js所有应用程序文件中使用mysql?
- service-worker - 从 Service Worker 获取重定向 API 调用
- c# - 如何在 Powershell 中使用 C# 接口和扩展方法?
- java - 当我向既是“管理员”又是“用户”的用户提供令牌时出现未经授权的错误
- python - Django for 循环仅通过查询集循环一次