You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/10/10 06:41:59 UTC

[GitHub] [shardingsphere] dissdic opened a new issue, #15134: to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL

dissdic opened a new issue, #15134:
URL: https://github.com/apache/shardingsphere/issues/15134

   ## Feature Request
   
   **For English only**, other languages will not accept.
   
   Please pay attention on issues you submitted, because we maybe need more details. 
   If no response anymore and we cannot make decision by current information, we will **close it**.
   
   Please answer these questions before submitting your issue. Thanks!
   
   ### Is your feature request related to a problem?
   yes
   ### Describe the feature you would like.
   in the case of not knowning the specific type of the field to be used in where condition in a SQL,I can only invoke the setValue method of the PreparedStatement instance.but things don't work like I expected.in the underlying code,shardingsphere don't process the type conversion.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL [shardingsphere]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #15134:
URL: https://github.com/apache/shardingsphere/issues/15134#issuecomment-2026021482

   There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] github-actions[bot] closed issue #15134: to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL

Posted by GitBox <gi...@apache.org>.
github-actions[bot] closed issue #15134: to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL
URL: https://github.com/apache/shardingsphere/issues/15134


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] dissdic commented on issue #15134: to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL

Posted by GitBox <gi...@apache.org>.
dissdic commented on issue #15134:
URL: https://github.com/apache/shardingsphere/issues/15134#issuecomment-1169757336

   I've copy the stacktrace info and paste it below.
   Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT kkk.id as id,kkk.name as name,kkk.domain_name as domain_name,kkk."version" as "version",kkk.insert_user as insert_user,kkk.insert_date as insert_date,kkk.update_user as update_user,kkk.update_date as update_date FROM wms_kkk as kkk &nbsp;WHERE kkk.id = ? ]; nested exception is org.postgresql.util.PSQLException: 错误: 操作符不存在: bigint = character varying
   &nbsp;&nbsp;建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
   &nbsp;&nbsp;位置：256
   at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
   at logwire.web.service.query.sql.jdbc.JdbcTemplateUtil.translateException(JdbcTemplateUtil.java:79)
   at logwire.web.service.query.sql.jdbc.JdbcTemplateUtil.execute(JdbcTemplateUtil.java:61)
   at logwire.web.service.query.sql.DynamicSqlSelect.doSelect(DynamicSqlSelect.java:733)
   at logwire.web.service.query.sql.DynamicSqlSelect.doSelect(DynamicSqlSelect.java:660)
   at logwire.web.service.query.sql.DynamicSqlSelect.forIterator(DynamicSqlSelect.java:443)
   at logwire.web.service.query.sql.DynamicSqlSelect.getSingleRow(DynamicSqlSelect.java:601)
   ... 54 common frames omitted
   Caused by: org.postgresql.util.PSQLException: 错误: 操作符不存在: bigint = character varying
   &nbsp;&nbsp;建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
   &nbsp;&nbsp;位置：256
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
   at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
   at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
   at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
   at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108)
   at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
   at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
   at org.apache.shardingsphere.driver.executor.callback.impl.PreparedStatementExecuteQueryCallback.executeQuery(PreparedStatementExecuteQueryCallback.java:40)
   at org.apache.shardingsphere.driver.executor.callback.ExecuteQueryCallback.executeSQL(ExecuteQueryCallback.java:44)
   at org.apache.shardingsphere.driver.executor.callback.ExecuteQueryCallback.executeSQL(ExecuteQueryCallback.java:36)
   at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)
   at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)
   at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101)
   at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:87)
   at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:81)
   at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)
   at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:49)
   at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.executeQuery(DriverJDBCExecutor.java:73)
   at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery0(ShardingSpherePreparedStatement.java:212)
   at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:181)
   at logwire.web.service.query.sql.DynamicSqlSelect.lambda$doSelect$15(DynamicSqlSelect.java:725)
   at logwire.web.service.query.sql.jdbc.JdbcTemplateUtil.execute(JdbcTemplateUtil.java:46)
   ... 58 common frames omitted
   for set values in to a SQL,we use the&nbsp;setValues(PreparedStatement ps) in&nbsp;PreparedStatementSetter,as you can see:
   
   
   and the implementation of this abstract method is like this:
   
   
   
   
   
   and the code of the another setValue method is:
   
   
   the core code is&nbsp;
   field.setValueIgnoreCheck(ps, index + 1, values.get(i), this.dialect);which present as:
   
   the code of the method&nbsp;setValueInPreparedStatement is:
   
   
   
   despite these execution process,we can see the explicit detail info of this method while debugging:
   
   
   
   this shows that eventually the setValue of ShardingSpherePreparedStatement was invoked.and I got the Exception written in the beginning.
   ------------------&nbsp;原始邮件&nbsp;------------------
   发件人:                                                                                                                        "apache/shardingsphere"                                                                                    ***@***.***&gt;;
   发送时间:&nbsp;2022年6月14日(星期二) 中午12:56
   ***@***.***&gt;;
   ***@***.******@***.***&gt;;
   主题:&nbsp;Re: [apache/shardingsphere] to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL (Issue #15134)
   
   
   
   
   
    
   Hi @dissdic
    Any update here?
    
   —
   Reply to this email directly, view it on GitHub, or unsubscribe.
   You are receiving this because you were mentioned.Message ID: ***@***.***&gt;


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] terrymanu commented on issue #15134: to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL

Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #15134:
URL: https://github.com/apache/shardingsphere/issues/15134#issuecomment-1182097991

   @dissdic Hi, can you reformat your content? It is difficult to read.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] dissdic commented on issue #15134: to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL

Posted by GitBox <gi...@apache.org>.
dissdic commented on issue #15134:
URL: https://github.com/apache/shardingsphere/issues/15134#issuecomment-1128539034

   sorry,I've worked on other things recently,so i don't have the stacktrace info right now,i will reply you with it later.basicly the problem i encounter is just like what you said.In my case,the automatic type conversion didn't work,cause when using shardingsphere in my project,the implementation of PreparedStatement was switched from PgPreparedStatement to ShardingSpherePreparedStatement,and the&nbsp; PgPreparedStatement seems do all the type conversion if you see the source code while the ShardingSpherePreparedStatement don't
   I'm not sure if the JDBC driver or the PgSql server would do the type conversion like you said,I'll give you the stacktrace i got.
   &nbsp;
   
   
   1814788049
   ***@***.***
   
   
   
   &nbsp;
   
   
   
   
   ------------------&nbsp;原始邮件&nbsp;------------------
   发件人: ***@***.***&gt;; 
   发送时间: 2022年5月17日(星期二) 中午11:26
   收件人: ***@***.***&gt;; 
   抄送: ***@***.***&gt;; ***@***.***&gt;; 
   主题: Re: [apache/shardingsphere] to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL (Issue #15134)
   
   
   
   
   
    
   Hi @dissdic
    Could you provide more details about your issue?
    Database like PostgreSQL will handle the type conversion on server side, which means user can provide string as value no matter what the column's type is. And the JDBC Driver may handle the type conversion stuff implicitly.
    
   —
   Reply to this email directly, view it on GitHub, or unsubscribe.
   You are receiving this because you were mentioned.Message ID: ***@***.***&gt;


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] TeslaCN commented on issue #15134: to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL

Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #15134:
URL: https://github.com/apache/shardingsphere/issues/15134#issuecomment-1128367297

   Hi @dissdic 
   Could you provide more details about your issue? 
   Database like PostgreSQL will handle the type conversion on server side, which means user can provide string as value no matter what the column's type is. And the JDBC Driver may handle the type conversion stuff implicitly.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] TeslaCN commented on issue #15134: to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL

Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #15134:
URL: https://github.com/apache/shardingsphere/issues/15134#issuecomment-1154710656

   Hi @dissdic 
   Any update here?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] dissdic commented on issue #15134: to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL

Posted by GitBox <gi...@apache.org>.
dissdic commented on issue #15134:
URL: https://github.com/apache/shardingsphere/issues/15134#issuecomment-1195254033

   for some reason,the format in the view got messed up.
   **here is the stacktrace info I got :**
   ```
   Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT kkk.id as id,kkk.name as name,kkk.domain_name as domain_name,kkk."version" as "version",kkk.insert_user as insert_user,kkk.insert_date as insert_date,kkk.update_user as update_user,kkk.update_date as update_date FROM wms_kkk as kkk  WHERE kkk.id = ? ]; nested exception is org.postgresql.util.PSQLException: 错误: 操作符不存在: bigint = character varying
     建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
     位置：256
   at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
   at logwire.web.service.query.sql.jdbc.JdbcTemplateUtil.translateException(JdbcTemplateUtil.java:79)
   at logwire.web.service.query.sql.jdbc.JdbcTemplateUtil.execute(JdbcTemplateUtil.java:61)
   at logwire.web.service.query.sql.DynamicSqlSelect.doSelect(DynamicSqlSelect.java:733)
   at logwire.web.service.query.sql.DynamicSqlSelect.doSelect(DynamicSqlSelect.java:660)
   at logwire.web.service.query.sql.DynamicSqlSelect.forIterator(DynamicSqlSelect.java:443)
   at logwire.web.service.query.sql.DynamicSqlSelect.getSingleRow(DynamicSqlSelect.java:601)
   ... 54 common frames omitted
   Caused by: org.postgresql.util.PSQLException: 错误: 操作符不存在: bigint = character varying
     建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
     位置：256
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
   at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
   at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
   at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
   at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:108)
   at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
   at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
   at org.apache.shardingsphere.driver.executor.callback.impl.PreparedStatementExecuteQueryCallback.executeQuery(PreparedStatementExecuteQueryCallback.java:40)
   at org.apache.shardingsphere.driver.executor.callback.ExecuteQueryCallback.executeSQL(ExecuteQueryCallback.java:44)
   at org.apache.shardingsphere.driver.executor.callback.ExecuteQueryCallback.executeSQL(ExecuteQueryCallback.java:36)
   at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)
   at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)
   at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101)
   at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:87)
   at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:81)
   at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)
   at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:49)
   at org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.executeQuery(DriverJDBCExecutor.java:73)
   at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery0(ShardingSpherePreparedStatement.java:212)
   at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:181)
   at logwire.web.service.query.sql.DynamicSqlSelect.lambda$doSelect$15(DynamicSqlSelect.java:725)
   at logwire.web.service.query.sql.jdbc.JdbcTemplateUtil.execute(JdbcTemplateUtil.java:46)
   ... 58 common frames omitted
   ```
   for setting values in to a SQL,we used the setValues(PreparedStatement ps) in PreparedStatementSetter,as you can see in the image below:
   
   ![image](https://user-images.githubusercontent.com/42462093/180973912-d5499409-7b79-4f4b-bf7b-de03a172d483.png)
   
   **and the implementation of this abstract method is like this:**
   
   ![image](https://user-images.githubusercontent.com/42462093/180974190-c99be6e7-adcb-437b-8a2a-1a7510abf34a.png)
   
   **and the code of the another setValue method present in the sceenshot is:**
   
   ![image](https://user-images.githubusercontent.com/42462093/180974462-cc75b4ae-7a6f-4793-84c6-ab3fca459c5f.png)
   
   ![image](https://user-images.githubusercontent.com/42462093/180974646-df6c54e8-7239-4039-ad65-55f6bd166629.png)
   
   the core code is:
   > field.setValueIgnoreCheck(ps, index + 1, values.get(i), this.dialect);
   
   which present as:
   
   ![image](https://user-images.githubusercontent.com/42462093/180974877-d9b7a838-86b2-4ce9-8c81-07c835d097fa.png)
   
   the code of the method setValueInPreparedStatement shown in the sceenshot is:
   
   ![image](https://user-images.githubusercontent.com/42462093/180975084-fd53ffc3-83e5-42be-8996-389537f8de00.png)
   
   despite these execution processes,I got the explicit detail info of this method while debugging:
   
   ![image](https://user-images.githubusercontent.com/42462093/180975265-cbd0295b-6c36-4d9d-95f0-b7cc5717a7d1.png)
   
   this shows that eventually the setValue of ShardingSpherePreparedStatement was invoked.and I got the Exception written in the beginning.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [shardingsphere] github-actions[bot] commented on issue #15134: to consider the sqlType of value parameter when setting the value to the corresponding placeholder in a executing SQL

Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #15134:
URL: https://github.com/apache/shardingsphere/issues/15134#issuecomment-1272350412

   Hello , this issue has not received a reply for several days.
   This issue is supposed to be closed.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org