You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "peilinqian (via GitHub)" <gi...@apache.org> on 2023/04/19 10:43:27 UTC
[GitHub] [shardingsphere] peilinqian opened a new issue, #25236: close
peilinqian opened a new issue, #25236:
URL: https://github.com/apache/shardingsphere/issues/25236
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
### Expected behavior
### Actual behavior
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
### Example codes for reproduce this issue (such as a github link).
--
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
[GitHub] [shardingsphere] FlyingZC commented on issue #25236: Use JDBC to execute cursor-related SQL. After closing the cursor, redefine the cursor with the same name. There is a problem with the cursor.
Posted by "FlyingZC (via GitHub)" <gi...@apache.org>.
FlyingZC commented on issue #25236:
URL: https://github.com/apache/shardingsphere/issues/25236#issuecomment-1624850733
Hi, this case is successfully executed locally.
```java
@Test
public void testOgCursor() throws Exception {
dataSource = xxx.getDataSource();
Connection connection = dataSource.getConnection();
String tb_name = "t_ssdb";
Statement stmt = connection.createStatement();
// creatTable(tb_name, stmt);
for (int i = 0; i < 3; i++) {
cursor(tb_name, stmt);
}
stmt.close();
connection.close();
}
private static void creatTable(String tb_name, Statement stmt) throws SQLException {
int create = stmt.executeUpdate("drop table if exists " + tb_name + ";" + "create table " + tb_name
+ "(id int,c_id int,a int,b text);");
System.out.println("createresult = " + create);
int insert = stmt.executeUpdate(
"insert into " + tb_name + " values " + "(1,1,10,'test1'),(1,2,10,'Test12'),(1,2,null,'test12'),"
+ "(2,1,20,'test21'),(2,2,20,'test22'),(2,3,null,'Test22'),"
+ "(3,3,null,'test3'),(3,3,null,'test3'),(3,1,null,'test31');");
System.out.println("insert = " + insert);
}
private static void cursor(String tb_name, Statement stmt) throws SQLException {
System.out.println("=============================================================");
stmt.execute("start transaction;");
stmt.execute("cursor cursor1 for select id,c_id from " + tb_name + " order by 1,2;");
String[] sqls = {"FETCH FORWARD 1 FROM cursor1;", "fetch 2 from cursor1;", "fetch from cursor1;",
"fetch next from cursor1;", "fetch forward all from cursor1;", "FETCH cursor1;"};
for (
int i = 1;
i < sqls.length + 1; ++i) {
System.out.println("excute sql" + i + ": " + sqls[i - 1]);
ResultSet resultSet = stmt.executeQuery(sqls[i - 1]);
while (resultSet.next()) {
System.out.print(resultSet.getInt(1) + " | ");
System.out.println(resultSet.getInt(2) + " | ");
}
}
stmt.execute("close cursor1;");
stmt.execute("end;");
}
```
Result is as follow:
```sql
=============================================================
excute sql1: FETCH FORWARD 1 FROM cursor1;
1 | 1 |
excute sql2: fetch 2 from cursor1;
1 | 2 |
1 | 2 |
excute sql3: fetch from cursor1;
2 | 1 |
excute sql4: fetch next from cursor1;
2 | 2 |
excute sql5: fetch forward all from cursor1;
2 | 3 |
3 | 1 |
3 | 3 |
3 | 3 |
excute sql6: FETCH cursor1;
=============================================================
excute sql1: FETCH FORWARD 1 FROM cursor1;
1 | 1 |
excute sql2: fetch 2 from cursor1;
1 | 2 |
1 | 2 |
excute sql3: fetch from cursor1;
2 | 1 |
excute sql4: fetch next from cursor1;
2 | 2 |
excute sql5: fetch forward all from cursor1;
2 | 3 |
3 | 1 |
3 | 3 |
3 | 3 |
excute sql6: FETCH cursor1;
=============================================================
excute sql1: FETCH FORWARD 1 FROM cursor1;
1 | 1 |
excute sql2: fetch 2 from cursor1;
1 | 2 |
1 | 2 |
excute sql3: fetch from cursor1;
2 | 1 |
excute sql4: fetch next from cursor1;
2 | 2 |
excute sql5: fetch forward all from cursor1;
2 | 3 |
3 | 1 |
3 | 3 |
3 | 3 |
excute sql6: FETCH cursor1;
```
--
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] strongduanmu commented on issue #25236: Use JDBC to execute cursor-related SQL. After closing the cursor, redefine the cursor with the same name. There is a problem with the cursor.
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #25236:
URL: https://github.com/apache/shardingsphere/issues/25236#issuecomment-1515880796
@FlyingZC Assigned.
--
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] FlyingZC commented on issue #25236: Use JDBC to execute cursor-related SQL. After closing the cursor, redefine the cursor with the same name. There is a problem with the cursor.
Posted by "FlyingZC (via GitHub)" <gi...@apache.org>.
FlyingZC commented on issue #25236:
URL: https://github.com/apache/shardingsphere/issues/25236#issuecomment-1624858547
The following case can also be successfully executed locally, start a transaction, and create a cursor with the same name.
```java
@Test
public void testOgCursor() throws Exception {
dataSource = xx.getDataSource();
Connection connection = dataSource.getConnection();
String tb_name = "t_ssdb";
Statement stmt = connection.createStatement();
// creatTable(tb_name, stmt);
executeStatementWithLog(stmt, "start transaction;");
for (int i = 0; i < 3; i++) {
cursor(tb_name, stmt);
}
executeStatementWithLog(stmt, "end;");
stmt.close();
connection.close();
}
private static void executeStatementWithLog(Statement stmt, String sql) throws SQLException {
System.out.println(sql);
stmt.execute(sql);
}
private static void creatTable(String tb_name, Statement stmt) throws SQLException {
int create = stmt.executeUpdate("drop table if exists " + tb_name + ";" + "create table " + tb_name
+ "(id int,c_id int,a int,b text);");
System.out.println("createresult = " + create);
int insert = stmt.executeUpdate(
"insert into " + tb_name + " values " + "(1,1,10,'test1'),(1,2,10,'Test12'),(1,2,null,'test12'),"
+ "(2,1,20,'test21'),(2,2,20,'test22'),(2,3,null,'Test22'),"
+ "(3,3,null,'test3'),(3,3,null,'test3'),(3,1,null,'test31');");
System.out.println("insert = " + insert);
}
private static void cursor(String tb_name, Statement stmt) throws SQLException {
System.out.println("=============================================================");
stmt.execute("cursor cursor1 for select id,c_id from " + tb_name + " order by 1,2;");
String[] sqls = {"FETCH FORWARD 1 FROM cursor1;", "fetch 2 from cursor1;", "fetch from cursor1;",
"fetch next from cursor1;", "fetch forward all from cursor1;", "FETCH cursor1;"};
for (
int i = 1;
i < sqls.length + 1; ++i) {
System.out.println("excute sql" + i + ": " + sqls[i - 1]);
ResultSet resultSet = stmt.executeQuery(sqls[i - 1]);
while (resultSet.next()) {
System.out.print(resultSet.getInt(1) + " | ");
System.out.println(resultSet.getInt(2) + " | ");
}
}
stmt.execute("close cursor1;");
}
```
```sql
start transaction;
=============================================================
excute sql1: FETCH FORWARD 1 FROM cursor1;
1 | 1 |
excute sql2: fetch 2 from cursor1;
1 | 2 |
1 | 2 |
excute sql3: fetch from cursor1;
2 | 1 |
excute sql4: fetch next from cursor1;
2 | 2 |
excute sql5: fetch forward all from cursor1;
2 | 3 |
3 | 1 |
3 | 3 |
3 | 3 |
excute sql6: FETCH cursor1;
=============================================================
excute sql1: FETCH FORWARD 1 FROM cursor1;
1 | 1 |
excute sql2: fetch 2 from cursor1;
1 | 2 |
1 | 2 |
excute sql3: fetch from cursor1;
2 | 1 |
excute sql4: fetch next from cursor1;
2 | 2 |
excute sql5: fetch forward all from cursor1;
2 | 3 |
3 | 1 |
3 | 3 |
3 | 3 |
excute sql6: FETCH cursor1;
=============================================================
excute sql1: FETCH FORWARD 1 FROM cursor1;
1 | 1 |
excute sql2: fetch 2 from cursor1;
1 | 2 |
1 | 2 |
excute sql3: fetch from cursor1;
2 | 1 |
excute sql4: fetch next from cursor1;
2 | 2 |
excute sql5: fetch forward all from cursor1;
2 | 3 |
3 | 1 |
3 | 3 |
3 | 3 |
excute sql6: FETCH cursor1;
end;
```
--
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] FlyingZC commented on issue #25236: Use JDBC to execute cursor-related SQL. After closing the cursor, redefine the cursor with the same name. There is a problem with the cursor.
Posted by "FlyingZC (via GitHub)" <gi...@apache.org>.
FlyingZC commented on issue #25236:
URL: https://github.com/apache/shardingsphere/issues/25236#issuecomment-1624859997
The reason why you encounter this error is: the query result has two columns, trying to get the third column.
```
excute sql1: FETCH FORWARD 1 FROM cursor1;
1 | 1 | Exception in thread "main" org.opengauss.util.PSQLException: 栏位索引超过许可范围：3，栏位数：2。
at org.opengauss.jdbc.PgResultSet.checkColumnIndex(PgResultSet.java:2897)
at org.opengauss.jdbc.PgResultSet.checkResultSet(PgResultSet.java:2938)
at org.opengauss.jdbc.PgResultSet.getString(PgResultSet.java:2012)
at CursorCase0024.main(CursorCase0024.java:51)
```
--
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] FlyingZC commented on issue #25236: Use JDBC to execute cursor-related SQL. After closing the cursor, redefine the cursor with the same name. There is a problem with the cursor.
Posted by "FlyingZC (via GitHub)" <gi...@apache.org>.
FlyingZC commented on issue #25236:
URL: https://github.com/apache/shardingsphere/issues/25236#issuecomment-1515816176
I want to investigate it. @strongduanmu
--
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] FlyingZC closed issue #25236: Use JDBC to execute cursor-related SQL. After closing the cursor, redefine the cursor with the same name. There is a problem with the cursor.
Posted by "FlyingZC (via GitHub)" <gi...@apache.org>.
FlyingZC closed issue #25236: Use JDBC to execute cursor-related SQL. After closing the cursor, redefine the cursor with the same name. There is a problem with the cursor.
URL: https://github.com/apache/shardingsphere/issues/25236
--
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