SQL Server Incorrect syntax near '@P1'

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL Server Incorrect syntax near '@P1'

Andrey Rogov
I use SQL Server 2012 and try to organize paging using
ROW_NUMBER() OVER ( order by #{columnname}# #{desc} ) AS 'RowNumber'.
SQL Server gives an error

DEBUG [http-nio-8080-exec-2] java.sql.PreparedStatement debug- {pstm-100004} Parameters: [eh.workdate,   desc]

DEBUG [http-nio-8080-exec-2] java.sql.PreparedStatement debug- {pstm-100004} Types: [java.lang.String, java.lang.String]

SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:  

--- The error occurred in baseportal/com/portal/sql/TelegramJournalSQL.xml. 

--- The error occurred while applying a parameter map. 

--- Check the TelegramJournalSQL.getTelegramJournalHistory-InlineParameterMap. 

--- Check the statement (query failed). 

--- Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P1'.


Give me advise, please


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: SQL Server Incorrect syntax near '@P1'

Kazuki Shimizu
Probably, you cannot specify the bind variable(#{...}) on "ORDER BY" clause. Please try to specify the replacement variable(${...}).
If you use the iBATIS(mybatis 2), it's format is '$...$' instead of '${...} '.

NOTE:
If you use the replacement variable, you should be careful a SQL injection.

Thanks.

On Sunday, March 24, 2019 at 12:36:29 AM UTC+9, andrey.rogov wrote:
I use SQL Server 2012 and try to organize paging using
ROW_NUMBER() OVER ( order by #{columnname}# #{desc} ) AS 'RowNumber'.
SQL Server gives an error

DEBUG [http-nio-8080-exec-2] java.sql.PreparedStatement debug- {pstm-100004} Parameters: [eh.workdate,   desc]

DEBUG [http-nio-8080-exec-2] java.sql.PreparedStatement debug- {pstm-100004} Types: [java.lang.String, java.lang.String]

SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:  

--- The error occurred in baseportal/com/portal/sql/TelegramJournalSQL.xml. 

--- The error occurred while applying a parameter map. 

--- Check the TelegramJournalSQL.getTelegramJournalHistory-InlineParameterMap. 

--- Check the statement (query failed). 

--- Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P1'.


Give me advise, please


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: SQL Server Incorrect syntax near '@P1'

Andrey Rogov
Thank you a lot !, it works !
I remember the sql injection danger,  anf  I apply more parameters inside the code and manage the transaction (READONLY).



суббота, 23 марта 2019 г., 17:55:58 UTC+2 пользователь Kazuki Shimizu написал:
Probably, you cannot specify the bind variable(#{...}) on "ORDER BY" clause. Please try to specify the replacement variable(${...}).
If you use the iBATIS(mybatis 2), it's format is '$...$' instead of '${...} '.

NOTE:
If you use the replacement variable, you should be careful a SQL injection.

Thanks.

On Sunday, March 24, 2019 at 12:36:29 AM UTC+9, andrey.rogov wrote:
I use SQL Server 2012 and try to organize paging using
ROW_NUMBER() OVER ( order by #{columnname}# #{desc} ) AS 'RowNumber'.
SQL Server gives an error

DEBUG [http-nio-8080-exec-2] java.sql.PreparedStatement debug- {pstm-100004} Parameters: [eh.workdate,   desc]

DEBUG [http-nio-8080-exec-2] java.sql.PreparedStatement debug- {pstm-100004} Types: [java.lang.String, java.lang.String]

SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:  

--- The error occurred in baseportal/com/portal/sql/TelegramJournalSQL.xml. 

--- The error occurred while applying a parameter map. 

--- Check the TelegramJournalSQL.getTelegramJournalHistory-InlineParameterMap. 

--- Check the statement (query failed). 

--- Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P1'.


Give me advise, please


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.