Comments (7)
You need to pass in a valid SQL statement into the statement creation method via "SELECT TOP " + count + " * FROM metadata"
.
The exception on OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
is a bug in Spring Data R2DBC that will be adressed with spring-projects/spring-data-r2dbc#55 and spring-projects/spring-data-relational#125.
from r2dbc-mssql.
SQL server uses at-prefixed parameters, see
https://github.com/r2dbc/r2dbc-mssql/blob/master/src/test/java/io/r2dbc/mssql/ColumnMetadataIntegrationTests.java#L134
Care to describe your entire scenario? Looks like you’re integrating with a Spring Data R2DBC and there we can pass thru parameters and we can use colon-prefixed named parameters in the current snapshot version.
from r2dbc-mssql.
I'm totally aware of that but still it does not work.
Currently I'm trying to invoke the database client inside of a rest controller call directly:
@GetMapping
Flux<Metadata> findAll(@RequestParam(defaultValue = "20") final int size) {
return databaseClient
.execute()
.sql("SELECT TOP @P0 * FROM metadata")
.bind("P0", size)
.as(Metadata.class)
.fetch()
.all();
}
Results in:
executeMany;` uncategorized R2dbcException for SQL [SELECT TOP @P0 * FROM metadata]; Incorrect syntax near '@P0'.; nested exception is R2dbcException{errorCode=102, sqlState='S0001'} io.r2dbc.mssql.MssqlException: Incorrect syntax near '@P0'.
Prior to that I tried it with a @Query
method in my ReactiveCrudRepository
.
I have a Spring Boot application with WebFlux + Netty.
R2DBC dependencies look like that:
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-r2dbc</artifactId>
<version>1.0.0.M1</version>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-releasetrain</artifactId>
<version>Lovelace-RELEASE</version>
<scope>import</scope>
<type>pom</type>
</dependency>
<dependency>
<groupId>io.r2dbc</groupId>
<artifactId>r2dbc-spi</artifactId>
<version>1.0.0.M6</version>
</dependency>
<dependency>
<groupId>io.r2dbc</groupId>
<artifactId>r2dbc-mssql</artifactId>
<version>1.0.0.M7</version>
</dependency>
from r2dbc-mssql.
Parametrization of TOP
does not seem to be possible as per SQL Server. Using the same statement with JDBC gives me the same failure:
SQLServerDriver driver = new SQLServerDriver();
Properties properties = new Properties();
Connection connection = driver.connect("jdbc:sqlserver://localhost:1433;databaseName=master", properties);
PreparedStatement ps = connection.prepareStatement("SELECT TOP ? * FROM mytable");
ps.setInt(1, 10);
ps.executeQuery();
Driver Logging:
Mär 15, 2019 3:04:26 PM com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement buildExecSQLParams
FEIN: SQLServerPreparedStatement:1: calling sp_executesql: SQL:SELECT TOP @P0 * FROM mytable
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254)
Closing this one as invalid.
On a related note: Please file a feature request in https://github.com/spring-projects/spring-data-r2dbc to add paging capabilities to DatabaseClient.select()
.
from r2dbc-mssql.
Oh there is paging in the DatabaseClient.select():
return databaseClient.select()
.from(Metadata.class)
.page(PageRequest.of(page, size))
.fetch()
.all();
Guess what happens:
executeMany; uncategorized R2dbcException for SQL [SELECT id, title, cost_center_number, property_number, building_number, story_code, unit_code, code, building_section, street_address, drawing_type, drawing_status, date_created FROM metadata OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY]; Incorrect syntax near '1'.; nested exception is R2dbcException{errorCode=102, sqlState='S0001'} io.r2dbc.mssql.MssqlException: Incorrect syntax near '1'.
from r2dbc-mssql.
That one does not yet consider dialect specifics. SQL server requires ORDER BY fields for pagination. That’s going to be considered in Spring Data‘s Dialect support.
from r2dbc-mssql.
I still don't get the vibe how this is supposed to work.
from r2dbc-mssql.
Related Issues (20)
- Transaction Names should be truncated when too long. HOT 3
- How to execute store procedure? HOT 1
- Ensure that`PreferredCursorExecution` is the only configuration flag to control cursor preference HOT 5
- Support for TVP HOT 1
- Add ActiveDirectoryServicePrincipal authentication mechanism HOT 1
- Release 1.0.1.RELEASE
- Could not find prepared statement with handle error HOT 12
- Capture encoder in `Encoded` instead of the plain value
- Mixed up statements when pool is enabled HOT 18
- Release 1.0.2.RELEASE
- Upgrade to Reactor 2022.0.9
- MssqlNonTransientException: Procedure or function has too many arguments specified. HOT 12
- Can you tell me the url of Microsoft SQL Server client protocol ? please HOT 2
- Add support for java.time.Instant HOT 5
- Memory leak detected while using r2dbc-mssql 1.0.2 HOT 2
- Intermittent same queries is taking more time. HOT 2
- Support Azure Managed Identities to authenticate to Azure SQL DB HOT 1
- Insert multiple rows as single statement
- Created by mistake, can you delete this issue please.
- Could not find prepared statement with handle HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from r2dbc-mssql.