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 2020/05/04 14:50:54 UTC
[GitHub] [shardingsphere] TaoZhiMLND opened a new issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
TaoZhiMLND opened a new issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421
I have reproduced the behavior as @lvye351 says in #5383. The keyGenerator doesn't work in the transaction-2pc-xa-raw-jdbc sample module when a oracle database 12c and ojbdc8.jar used.
The log is as follows,
It seems that when table t_order created, its tableMetaData doesn't put into schemaMetaData.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-623791058
@lvye351 we can continue to look for other reasons
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624013196
Some suggestions:
1. We need to make sure about oracle SQL and semicolon.
2. If return true for ResultSet.next() with connection.getMetaData().getIndexInfo(), what's happen for null value of `resultSet.getString(INDEX_NAME)`?
3. The 3rd modification is OK.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624447041
2、ResultSet of OracleDatabaseMetaData#getIndexInfo contains a record describing table's statistics which INDEX_NAME is null .
```java
public java.sql.ResultSet getIndexInfo(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
boolean unique,
boolean approximate)
throws java.sql.SQLException
```
>Get a description of a table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
>Each index column description has the following columns:
>1. TABLE_CAT String => table catalog (may be null)
>2. TABLE_SCHEM String => table schema (may be null)
>3. TABLE_NAME String => table name
>4. NON_UNIQUE boolean => Can index values be non-unique? false when TYPE is tableIndexStatistic
>5. INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
>6. INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
>7. TYPE short => index type:
> tableIndexStatistic - this identifies table statistics that are returned in conjuction with a table's index descriptions
> tableIndexClustered - this is a clustered index
> tableIndexHashed - this is a hashed index
> tableIndexOther - this is some other style of index
>8. ORDINAL_POSITION short => column sequence number within index; zero when TYPE is tableIndexStatistic
…
Original document from <https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)>
ResultSet of DatabaseMetaData#getIndexInfo in mysql jdbc implementation doesn't have this record.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624144419
1、I found some infomation may explain the use of semicolon in Oracle JDBC.
> Usually the semi-colon is not part of the actual syntax of a statement (as most database internal APIs execute a single statement at a time). Instead the semi-colon is an 'end-of-statement' marker or statement separator that is - usually - defined in CLI or scripting tools for the database. This allows that tool to know when a statement ends, so it can send that single statement to the database for execution.
On the other hand, the JDBC api is intended to execute a single(!) statement at a time, therefor you don't need such a separator (the statement is the whole string). This means that a semi-colon is not needed, and as it is not part of the actual statement syntax for a lot of database it is also a syntax error to include it. Some JDBC drivers will strip the last ; from a statement to 'fix' that, some drivers don't.
Some drivers allow - contrary to the JDBC specification - multiple statements to be executed as a single string, this usually has to be enabled with a connection property, for example for MySQL it is the option allowMultiQueries (see the MySQL properties for details).
Original answer link <https://stackoverflow.com/questions/18941539/is-the-semicolon-necessary-in-sql>
>Note, however, that there is no semicolon at the end of the SQL92 CallableStatement
Original articles from [*Expert Oracle JDBC Programming*](https://books.google.com.ua/books?id=9MsOpXrElhUC&pg=PA190&lpg=PA190&dq=oracle+jdbc+semicolon&source=bl&ots=mEAqUNBKWl&sig=ACfU3U2WHLjzExwu1ZVpJXHvww7mpqMkRg&hl=zh-CN&sa=X&ved=2ahUKEwju6tnNjZ3pAhUhzqYKHV9XDo8Q6AEwBnoECAgQAQ#v=onepage&q=oracle%20jdbc%20semicolon&f=false)
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-623808684
I remember @dongzl used to modify here and convert table name to lowercase.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624474705
It's my pleasure, I will commit a pr soon.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lvye351 commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
lvye351 commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-623785043
> when configuration is as follows(actual table name use uppercase words), the issue above will absent,
>
> ```
> shardingRule:
> tables:
> t_order:
> actualDataNodes: ds_${0..1}.T_ORDER_${0..1}
> tableStrategy:
> standard:
> shardingColumn: order_id
> shardingAlgorithm:
> type: INLINE
> props:
> algorithm.expression: T_ORDER_${order_id % 2}
> keyGenerator:
> type: SNOWFLAKE
> column: order_id
> props:
> worker.id: 123
> ```
>
> But when TableMetaDataLoader#isTableExist return true, there is another error occur from ColumnMetaDataLoader#load.
> Java code is
>
> ```java
> try (ResultSet resultSet = connection.createStatement().executeQuery(generateEmptyResultSQL(table, databaseType))) {
> for (String each : columnNames) {
> isCaseSensitives.add(resultSet.getMetaData().isCaseSensitive(resultSet.findColumn(each)));
> }
> }
> ```
>
> Exception cause is
>
> ```
> Error : 933, Position : 38, Sql = SELECT * FROM "T_ORDER_0" WHERE 1 != 1;, OriginalSql = SELECT * FROM "T_ORDER_0" WHERE 1 != 1;, Error Msg = ORA-00933: SQL 命令未正确结束
> ```
>
> And this sql can execute successfully in PL/SQL.
before I have found that ，SELECT * FROM "T_ORDER_0" WHERE 1 != 1; the ; in the end is not allowed int oracle grammar，I commented it in code temporarily , key still not generated, so this is only one bug in ColumnMetaDataLoader.generateEmptyResultSQL
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-623866759
Now the test turn into green.
changes are as follows,
1、modify ColumnMetaDataLoade#generateEmptyResultSQL
```java
return "SELECT * FROM " + delimiterLeft + table + delimiterRight + " WHERE 1 != 1"
```
(remove semicolons behind WHERE 1 != 1 which not allowed in oracle)
2、return value of connection.getMetaData().getIndexInfo includes a null name record most times but not always . So IndexMetaDataLoader#load needs a check for whether indexName is null.
```java
public static Collection<IndexMetaData> load(final Connection connection, final String table) throws SQLException {
Collection<IndexMetaData> result = new HashSet<>();
try (ResultSet resultSet = connection.getMetaData().getIndexInfo(connection.getCatalog(), connection.getSchema(), table, false, false)) {
while (resultSet.next()) {
String indexName = resultSet.getString(INDEX_NAME);
if (Strings.isNullOrEmpty(indexName)) {
continue;
}
result.add(new IndexMetaData(indexName));
}
}
return result;
}
```
3、connection.getMetaData().getColumns in ColumnMetaDataLoader#load need specify a schema, otherwise it will return duplicated columns, so that I use connection.getSchema() replace null.
```java
try (ResultSet resultSet = connection.getMetaData().getColumns(connection.getCatalog(), connection.getSchema(), table, "%")) {
while (resultSet.next()) {
String columnName = resultSet.getString(COLUMN_NAME);
columnTypes.add(resultSet.getInt(DATA_TYPE));
columnTypeNames.add(resultSet.getString(TYPE_NAME));
isPrimaryKeys.add(primaryKeys.contains(columnName));
columnNames.add(columnName);
}
}
```
it is works to me. Can we discuss further how to modify it?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624447041
2、ResultSet of OracleDatabaseMetaData#getIndexInfo contains a record describing table's statistics which INDEX_NAME is null .
```java
public java.sql.ResultSet getIndexInfo(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
boolean unique,
boolean approximate)
throws java.sql.SQLException
```
>Get a description of a table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
>Each index column description has the following columns:
TABLE_CAT String => table catalog (may be null)
TABLE_SCHEM String => table schema (may be null)
TABLE_NAME String => table name
NON_UNIQUE boolean => Can index values be non-unique? false when TYPE is tableIndexStatistic
INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
TYPE short => index type:
tableIndexStatistic - this identifies table statistics that are returned in conjuction with a table's index descriptions
tableIndexClustered - this is a clustered index
tableIndexHashed - this is a hashed index
tableIndexOther - this is some other style of index
Original document from <https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)>
ResultSet of DatabaseMetaData#getIndexInfo in mysql jdbc implementation doesn't have this record.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-623866759
Now the test turn into green.
changes are as follows,
1、modify ColumnMetaDataLoade#generateEmptyResultSQL
```java
return "SELECT * FROM " + delimiterLeft + table + delimiterRight + " WHERE 1 != 1"
```
(remove semicolons behind WHERE 1 != 1 which not allowed in oracle)
2、return value of connection.getMetaData().getIndexInfo includes a null name record most times but not always . So IndexMetaDataLoader#load needs a check for whether indexName is null.
```java
public static Collection<IndexMetaData> load(final Connection connection, final String table) throws SQLException {
Collection<IndexMetaData> result = new HashSet<>();
try (ResultSet resultSet = connection.getMetaData().getIndexInfo(connection.getCatalog(), connection.getSchema(), table, false, false)) {
while (resultSet.next()) {
String indexName = resultSet.getString(INDEX_NAME);
if (Strings.isNullOrEmpty(indexName)) {
continue;
}
result.add(new IndexMetaData(indexName));
}
}
return result;
}
```
3、connection.getMetaData().getColumns in ColumnMetaDataLoader#load need specify a schema, otherwise it will return duplicated columns, so that I use connection.getSchema() replace null.
```java
try (ResultSet resultSet = connection.getMetaData().getColumns(connection.getCatalog(), connection.getSchema(), table, "%")) {
while (resultSet.next()) {
String columnName = resultSet.getString(COLUMN_NAME);
columnTypes.add(resultSet.getInt(DATA_TYPE));
columnTypeNames.add(resultSet.getString(TYPE_NAME));
isPrimaryKeys.add(primaryKeys.contains(columnName));
columnNames.add(columnName);
}
}
```
it is works to me. Can we discuss further how to modify it?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624473124
@TaoZhiMLND Wonderful investigate, very glad to see the research result.
Could you submit a pull request to fix the problem depends on your 3 modifications?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624447041
2、ResultSet of OracleDatabaseMetaData#getIndexInfo contains a record describing table's statistics which INDEX_NAME is null .
```java
public java.sql.ResultSet getIndexInfo(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
boolean unique,
boolean approximate)
throws java.sql.SQLException
```
>Get a description of a table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
>Each index column description has the following columns:
>1. TABLE_CAT String => table catalog (may be null)
>2. TABLE_SCHEM String => table schema (may be null)
>3. TABLE_NAME String => table name
>4. NON_UNIQUE boolean => Can index values be non-unique? false when TYPE is tableIndexStatistic
>5. INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
>6. INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
>7. TYPE short => index type:
> tableIndexStatistic - this identifies table statistics that are returned in conjuction with a table's index descriptions
> tableIndexClustered - this is a clustered index
> tableIndexHashed - this is a hashed index
> tableIndexOther - this is some other style of index
>8. ORDINAL_POSITION short => column sequence number within index; zero when TYPE is tableIndexStatistic
…
Original document from <https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)>
ResultSet of DatabaseMetaData#getIndexInfo in mysql jdbc implementation doesn't have this statistic record.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624144419
1、I found an answer in stackoverflow may explain the use of semicolon in Oracle JDBC.
> Usually the semi-colon is not part of the actual syntax of a statement (as most database internal APIs execute a single statement at a time). Instead the semi-colon is an 'end-of-statement' marker or statement separator that is - usually - defined in CLI or scripting tools for the database. This allows that tool to know when a statement ends, so it can send that single statement to the database for execution.
On the other hand, the JDBC api is intended to execute a single(!) statement at a time, therefor you don't need such a separator (the statement is the whole string). This means that a semi-colon is not needed, and as it is not part of the actual statement syntax for a lot of database it is also a syntax error to include it. Some JDBC drivers will strip the last ; from a statement to 'fix' that, some drivers don't.
Some drivers allow - contrary to the JDBC specification - multiple statements to be executed as a single string, this usually has to be enabled with a connection property, for example for MySQL it is the option allowMultiQueries (see the MySQL properties for details).
Original answer link: <https://stackoverflow.com/questions/18941539/is-the-semicolon-necessary-in-sql>
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624447041
2、ResultSet of OracleDatabaseMetaData#getIndexInfo contains a record describing table's statistics which INDEX_NAME is null .
```java
public java.sql.ResultSet getIndexInfo(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
boolean unique,
boolean approximate)
throws java.sql.SQLException
```
>Get a description of a table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
>Each index column description has the following columns:
TABLE_CAT String => table catalog (may be null)
TABLE_SCHEM String => table schema (may be null)
TABLE_NAME String => table name
NON_UNIQUE boolean => Can index values be non-unique? false when TYPE is tableIndexStatistic
INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
Original document from <https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)>
ResultSet of DatabaseMetaData#getIndexInfo in mysql jdbc implementation doesn't have this record.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-623870616
@lvye351 hi, you can have a try like above.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624447041
ResultSet of OracleDatabaseMetaData#getIndexInfo contains a record describing table's statistics which INDEX_NAME is null .
```java
public java.sql.ResultSet getIndexInfo(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
boolean unique,
boolean approximate)
throws java.sql.SQLException
```
>Get a description of a table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
>Each index column description has the following columns:
TABLE_CAT String => table catalog (may be null)
TABLE_SCHEM String => table schema (may be null)
TABLE_NAME String => table name
NON_UNIQUE boolean => Can index values be non-unique? false when TYPE is tableIndexStatistic
INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
Original document from <https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)>
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624144419
1、I found some infomation may explain the use of semicolon in Oracle JDBC.
> Usually the semi-colon is not part of the actual syntax of a statement (as most database internal APIs execute a single statement at a time). Instead the semi-colon is an 'end-of-statement' marker or statement separator that is - usually - defined in CLI or scripting tools for the database. This allows that tool to know when a statement ends, so it can send that single statement to the database for execution.
On the other hand, the JDBC api is intended to execute a single(!) statement at a time, therefor you don't need such a separator (the statement is the whole string). This means that a semi-colon is not needed, and as it is not part of the actual statement syntax for a lot of database it is also a syntax error to include it. Some JDBC drivers will strip the last ; from a statement to 'fix' that, some drivers don't.
Some drivers allow - contrary to the JDBC specification - multiple statements to be executed as a single string, this usually has to be enabled with a connection property, for example for MySQL it is the option allowMultiQueries (see the MySQL properties for details).
Original answer link: <https://stackoverflow.com/questions/18941539/is-the-semicolon-necessary-in-sql>
>Note, however, that there is no semicolon at the end of the SQL92 CallableStatement
Original articles from *Expert Oracle JDBC Programming*
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-623633891
when configuration is as follows(actual table name use uppercase words), the issue above will absent,
```
shardingRule:
tables:
t_order:
actualDataNodes: ds_${0..1}.T_ORDER_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithm:
type: INLINE
props:
algorithm.expression: T_ORDER_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
props:
worker.id: 123
```
But when TableMetaDataLoader#isTableExist return true, there is another error occur from ColumnMetaDataLoader#load.
Java code is
```java
try (ResultSet resultSet = connection.createStatement().executeQuery(generateEmptyResultSQL(table, databaseType))) {
for (String each : columnNames) {
isCaseSensitives.add(resultSet.getMetaData().isCaseSensitive(resultSet.findColumn(each)));
}
}
```
Exception cause is
```
Error : 933, Position : 38, Sql = SELECT * FROM "T_ORDER_0" WHERE 1 != 1;, OriginalSql = SELECT * FROM "T_ORDER_0" WHERE 1 != 1;, Error Msg = ORA-00933: SQL 命令未正确结束
```
And this sql can execute successfully in PL/SQL.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624447041
ResultSet of OracleDatabaseMetaData#getIndexInfo contains a record describing table's statistics which INDEX_NAME is null .
```java
public java.sql.ResultSet getIndexInfo(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
boolean unique,
boolean approximate)
throws java.sql.SQLException
```
>Get a description of a table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
>Each index column description has the following columns:
TABLE_CAT String => table catalog (may be null)
TABLE_SCHEM String => table schema (may be null)
TABLE_NAME String => table name
NON_UNIQUE boolean => Can index values be non-unique? false when TYPE is tableIndexStatistic
INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
Original articles from <https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)>
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624447041
2、ResultSet of OracleDatabaseMetaData#getIndexInfo contains a record describing table's statistics which INDEX_NAME is null . But DatabaseMetaData#getIndexInfo in mysql jdbc implementation doesn't have the statistics record.
```java
public java.sql.ResultSet getIndexInfo(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
boolean unique,
boolean approximate)
throws java.sql.SQLException
```
>Get a description of a table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
>Each index column description has the following columns:
>1. TABLE_CAT String => table catalog (may be null)
>2. TABLE_SCHEM String => table schema (may be null)
>3. TABLE_NAME String => table name
>4. NON_UNIQUE boolean => Can index values be non-unique? false when TYPE is tableIndexStatistic
>5. INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
>6. INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
>7. TYPE short => index type:
> tableIndexStatistic - this identifies table statistics that are returned in conjuction with a table's index descriptions
> tableIndexClustered - this is a clustered index
> tableIndexHashed - this is a hashed index
> tableIndexOther - this is some other style of index
>8. ORDINAL_POSITION short => column sequence number within index; zero when TYPE is tableIndexStatistic
…
Original document from <https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)>
or
<https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo-java.lang.String-java.lang.String-java.lang.String-boolean-boolean->
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624144419
1、I found some infomation may explain the use of semicolon in Oracle JDBC. This means that a semi-colon is not needed, and as it is not part of the actual statement syntax for a lot of database it is also a syntax error to include it.
> Usually the semi-colon is not part of the actual syntax of a statement (as most database internal APIs execute a single statement at a time). Instead the semi-colon is an 'end-of-statement' marker or statement separator that is - usually - defined in CLI or scripting tools for the database. This allows that tool to know when a statement ends, so it can send that single statement to the database for execution.
On the other hand, the JDBC api is intended to execute a single(!) statement at a time, therefor you don't need such a separator (the statement is the whole string). This means that a semi-colon is not needed, and as it is not part of the actual statement syntax for a lot of database it is also a syntax error to include it. Some JDBC drivers will strip the last ; from a statement to 'fix' that, some drivers don't.
Some drivers allow - contrary to the JDBC specification - multiple statements to be executed as a single string, this usually has to be enabled with a connection property, for example for MySQL it is the option allowMultiQueries (see the MySQL properties for details).
Original answer link <https://stackoverflow.com/questions/18941539/is-the-semicolon-necessary-in-sql>
>Note, however, that there is no semicolon at the end of the SQL92 CallableStatement
Original articles from [*Expert Oracle JDBC Programming*](https://books.google.com.ua/books?id=9MsOpXrElhUC&pg=PA190&lpg=PA190&dq=oracle+jdbc+semicolon&source=bl&ots=mEAqUNBKWl&sig=ACfU3U2WHLjzExwu1ZVpJXHvww7mpqMkRg&hl=zh-CN&sa=X&ved=2ahUKEwju6tnNjZ3pAhUhzqYKHV9XDo8Q6AEwBnoECAgQAQ#v=onepage&q=oracle%20jdbc%20semicolon&f=false)
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624474705
It's my pleasure, I will submit a pr soon.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-623866759
Now the test turn into green.
changes are as follows,
1、modify ColumnMetaDataLoade#generateEmptyResultSQL
```java
return "SELECT * FROM " + delimiterLeft + table + delimiterRight + " WHERE 1 != 1"
```
(remove semicolons behind WHERE 1 != 1 which not allowed in oracle)
2、return value of connection.getMetaData().getIndexInfo includes a null name record ~~most times but not(I have some mistakes)~~ always . So IndexMetaDataLoader#load needs a check for whether indexName is null.
```java
public static Collection<IndexMetaData> load(final Connection connection, final String table) throws SQLException {
Collection<IndexMetaData> result = new HashSet<>();
try (ResultSet resultSet = connection.getMetaData().getIndexInfo(connection.getCatalog(), connection.getSchema(), table, false, false)) {
while (resultSet.next()) {
String indexName = resultSet.getString(INDEX_NAME);
if (Strings.isNullOrEmpty(indexName)) {
continue;
}
result.add(new IndexMetaData(indexName));
}
}
return result;
}
```
3、connection.getMetaData().getColumns in ColumnMetaDataLoader#load need specify a schema, otherwise it will return duplicated columns, so that I use connection.getSchema() replace null.
```java
try (ResultSet resultSet = connection.getMetaData().getColumns(connection.getCatalog(), connection.getSchema(), table, "%")) {
while (resultSet.next()) {
String columnName = resultSet.getString(COLUMN_NAME);
columnTypes.add(resultSet.getInt(DATA_TYPE));
columnTypeNames.add(resultSet.getString(TYPE_NAME));
isPrimaryKeys.add(primaryKeys.contains(columnName));
columnNames.add(columnName);
}
}
```
it is works to me. Can we discuss further how to modify it?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624447041
2、ResultSet of OracleDatabaseMetaData#getIndexInfo contains a record describing table's statistics which INDEX_NAME is null . But DatabaseMetaData#getIndexInfo in mysql jdbc implementation doesn't return the statistics record.
```java
public java.sql.ResultSet getIndexInfo(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
boolean unique,
boolean approximate)
throws java.sql.SQLException
```
>Get a description of a table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
>Each index column description has the following columns:
>1. TABLE_CAT String => table catalog (may be null)
>2. TABLE_SCHEM String => table schema (may be null)
>3. TABLE_NAME String => table name
>4. NON_UNIQUE boolean => Can index values be non-unique? false when TYPE is tableIndexStatistic
>5. INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
>6. INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
>7. TYPE short => index type:
> tableIndexStatistic - this identifies table statistics that are returned in conjuction with a table's index descriptions
> tableIndexClustered - this is a clustered index
> tableIndexHashed - this is a hashed index
> tableIndexOther - this is some other style of index
>8. ORDINAL_POSITION short => column sequence number within index; zero when TYPE is tableIndexStatistic
>…
>11. CARDINALITY long => When TYPE is tableIndexStatistic, then this is the number of rows in the table; otherwise, it is the number of unique values in the index.
>12. PAGES long => When TYPE is tableIndexStatisic then this is the number of pages used for the table, otherwise it is the number of pages used for the current index.
Original document from <https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)>
or
<https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo-java.lang.String-java.lang.String-java.lang.String-boolean-boolean->
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-623556085
Although the table name what we created is lowercase, it stored in oracle is uppercase by default. Table name is case sensitive in oracle, TableMetaDataLoader#isTableExist will return false if we use the lowercase table name. So the tableMetaInfo doesn't put into schemaMetaData.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624144419
1、I found some infomation may explain the use of semicolon in Oracle JDBC.
> Usually the semi-colon is not part of the actual syntax of a statement (as most database internal APIs execute a single statement at a time). Instead the semi-colon is an 'end-of-statement' marker or statement separator that is - usually - defined in CLI or scripting tools for the database. This allows that tool to know when a statement ends, so it can send that single statement to the database for execution.
On the other hand, the JDBC api is intended to execute a single(!) statement at a time, therefor you don't need such a separator (the statement is the whole string). This means that a semi-colon is not needed, and as it is not part of the actual statement syntax for a lot of database it is also a syntax error to include it. Some JDBC drivers will strip the last ; from a statement to 'fix' that, some drivers don't.
Some drivers allow - contrary to the JDBC specification - multiple statements to be executed as a single string, this usually has to be enabled with a connection property, for example for MySQL it is the option allowMultiQueries (see the MySQL properties for details).
Original answer link: <https://stackoverflow.com/questions/18941539/is-the-semicolon-necessary-in-sql>
>Note, however, that there is no semicolon at the end of the SQL92 CallableStatement
Original articles from [*Expert Oracle JDBC Programming*](https://books.google.com.ua/books?id=9MsOpXrElhUC&pg=PA190&lpg=PA190&dq=oracle+jdbc+semicolon&source=bl&ots=mEAqUNBKWl&sig=ACfU3U2WHLjzExwu1ZVpJXHvww7mpqMkRg&hl=zh-CN&sa=X&ved=2ahUKEwju6tnNjZ3pAhUhzqYKHV9XDo8Q6AEwBnoECAgQAQ#v=onepage&q=oracle%20jdbc%20semicolon&f=false)
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624447041
2、ResultSet of OracleDatabaseMetaData#getIndexInfo contains a record describing table's statistics which INDEX_NAME is null .
```java
public java.sql.ResultSet getIndexInfo(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
boolean unique,
boolean approximate)
throws java.sql.SQLException
```
>Get a description of a table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
>Each index column description has the following columns:
TABLE_CAT String => table catalog (may be null)
TABLE_SCHEM String => table schema (may be null)
TABLE_NAME String => table name
NON_UNIQUE boolean => Can index values be non-unique? false when TYPE is tableIndexStatistic
INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
Original document from <https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)>
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND commented on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND commented on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-623556085
Although the table name what we created is lowercase, it stored in oracle is uppercase by default. Table name is case sensitive in oracle, TableMetaDataLoader#isTableExist will return false if we use the lowercase table name.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TaoZhiMLND edited a comment on issue #5421: The keyGenerator doesn't work in transaction-2pc-xa-raw-jdbc-example module when a oracle database used
Posted by GitBox <gi...@apache.org>.
TaoZhiMLND edited a comment on issue #5421:
URL: https://github.com/apache/shardingsphere/issues/5421#issuecomment-624447041
2、ResultSet of OracleDatabaseMetaData#getIndexInfo contains a record describing table's statistics which INDEX_NAME is null . But DatabaseMetaData#getIndexInfo in mysql jdbc implementation doesn't return the statistics record.
```java
public java.sql.ResultSet getIndexInfo(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
boolean unique,
boolean approximate)
throws java.sql.SQLException
```
>Get a description of a table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
>Each index column description has the following columns:
>1. TABLE_CAT String => table catalog (may be null)
>2. TABLE_SCHEM String => table schema (may be null)
>3. TABLE_NAME String => table name
>4. NON_UNIQUE boolean => Can index values be non-unique? false when TYPE is tableIndexStatistic
>5. INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
>6. INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
>7. TYPE short => index type:
> tableIndexStatistic - this identifies table statistics that are returned in conjuction with a table's index descriptions
> tableIndexClustered - this is a clustered index
> tableIndexHashed - this is a hashed index
> tableIndexOther - this is some other style of index
>8. ORDINAL_POSITION short => column sequence number within index; zero when TYPE is tableIndexStatistic
…
Original document from <https://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleDatabaseMetaData.html#getIndexInfo(java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean)>
or
<https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo-java.lang.String-java.lang.String-java.lang.String-boolean-boolean->
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org