JdbcColumnMeta

[Peter Borissow]

Dear Devs,    I would like to help expand the jdbc resultset metadata available when querying a table. Consider the following example:


CREATE TABLE ARTICLE (
    ID BIGINT,
    BODY text,
    LAST_MODIFIED TIMESTAMP with time zone,
        CONSTRAINT PK_ ARTICLE PRIMARY KEY (ID)
 );
After creating the table, I can query the column metadata using the java.sql.ResultSetMetaData. Example:

 java.sql.ResultSet rs = stmt.executeQuery("select * from article);
java.sql.ResultSetMetaData rsmd = rs.getMetaData();

However, the resultset /column metadata will return the following types:

- id: long- body: object- last_modified: object
Note that the body and last_modified fields are identified as generic objects instead of their formal java types. This is not consistent with other jdbc drivers (e.g. postgresql, h2, etc).

 After a little digging, I found the JdbcColumnMeta(GridQueryFieldMetadata info) constructor which calls the JdbcThinUtils class. However, at this point the GridQueryFieldMetadata is already mapped to a generic "object" so it is impossible to return the correct type from the JdbcThinUtils class.

Next, I decided to see how tables are created in the first place. Specifically, the GridSqlQueryParser.parseCreateTable() method. In there, I can see the correct column metadata. For example, if I add the following code in the parseCreateTable method...
for (Column col : data.columns){
    System.out.println(col.getName() + " (" + col.getType() + ")");
}

...it will print the following:
ID (5)
BODY (16)
LAST_MODIFIED (24)

I would like to preserve this metadata so that I can see it when using the jdbc resultset metadata object. Any suggestions?

Note that the ID column type (5) is preserved. I suspect the other types identified in the JdbcThinUtils class are preserved as well.


Thanks in advance,Peter

[Vladimir Ozerov]

Hi Peter,

We will be able to fix this in consistent way for both "thin" and "thick"
JDBC drivers once IGNITE-6173 [1] is merged. In this ticket we ensure that
metadata for all schema objects are present on all nodes, so that we can
simply delegate metadata request to underlying H2 table.

[1] https://issues.apache.org/jira/browse/IGNITE-6173

On Tue, Nov 20, 2018 at 8:06 PM Peter Borissow
<[hidden email]> wrote:

> Dear Devs,    I would like to help expand the jdbc resultset metadata
> available when querying a table. Consider the following example:
>
>
> CREATE TABLE ARTICLE (
>     ID BIGINT,
>     BODY text,
>     LAST_MODIFIED TIMESTAMP with time zone,
>         CONSTRAINT PK_ ARTICLE PRIMARY KEY (ID)
>  );
> After creating the table, I can query the column metadata using the
> java.sql.ResultSetMetaData. Example:
>
>  java.sql.ResultSet rs = stmt.executeQuery("select * from article);
> java.sql.ResultSetMetaData rsmd = rs.getMetaData();
>
> However, the resultset /column metadata will return the following types:
>
> - id: long- body: object- last_modified: object
> Note that the body and last_modified fields are identified as generic
> objects instead of their formal java types. This is not consistent with
> other jdbc drivers (e.g. postgresql, h2, etc).
>
>  After a little digging, I found the JdbcColumnMeta(GridQueryFieldMetadata
> info) constructor which calls the JdbcThinUtils class. However, at this
> point the GridQueryFieldMetadata is already mapped to a generic "object" so
> it is impossible to return the correct type from the JdbcThinUtils class.
>
> Next, I decided to see how tables are created in the first place.
> Specifically, the GridSqlQueryParser.parseCreateTable() method. In there, I
> can see the correct column metadata. For example, if I add the following
> code in the parseCreateTable method...
> for (Column col : data.columns){
>     System.out.println(col.getName() + " (" + col.getType() + ")");
> }
>
> ...it will print the following:
> ID (5)
> BODY (16)
> LAST_MODIFIED (24)
>
> I would like to preserve this metadata so that I can see it when using the
> jdbc resultset metadata object. Any suggestions?
>
> Note that the ID column type (5) is preserved. I suspect the other types
> identified in the JdbcThinUtils class are preserved as well.
>
>
> Thanks in advance,Peter
>
>
>
>
>
>
>
>

[Peter Borissow]

 Hello Vladimir,    Is there a ticket for the issue I described in JIRA or should we create one and mark it blocked pending #6173?

Thanks,Peter

    On Wednesday, November 21, 2018, 3:30:21 AM EST, Vladimir Ozerov <[hidden email]> wrote:  
 
 Hi Peter,

We will be able to fix this in consistent way for both "thin" and "thick"
JDBC drivers once IGNITE-6173 [1] is merged. In this ticket we ensure that
metadata for all schema objects are present on all nodes, so that we can
simply delegate metadata request to underlying H2 table.

[1] https://issues.apache.org/jira/browse/IGNITE-6173

On Tue, Nov 20, 2018 at 8:06 PM Peter Borissow
<[hidden email]> wrote:

> Dear Devs,    I would like to help expand the jdbc resultset metadata
> available when querying a table. Consider the following example:
>
>
> CREATE TABLE ARTICLE (
>    ID BIGINT,
>    BODY text,
>    LAST_MODIFIED TIMESTAMP with time zone,
>        CONSTRAINT PK_ ARTICLE PRIMARY KEY (ID)
>  );
> After creating the table, I can query the column metadata using the
> java.sql.ResultSetMetaData. Example:
>
>  java.sql.ResultSet rs = stmt.executeQuery("select * from article);
> java.sql.ResultSetMetaData rsmd = rs.getMetaData();
>
> However, the resultset /column metadata will return the following types:
>
> - id: long- body: object- last_modified: object
> Note that the body and last_modified fields are identified as generic
> objects instead of their formal java types. This is not consistent with
> other jdbc drivers (e.g. postgresql, h2, etc).
>
>  After a little digging, I found the JdbcColumnMeta(GridQueryFieldMetadata
> info) constructor which calls the JdbcThinUtils class. However, at this
> point the GridQueryFieldMetadata is already mapped to a generic "object" so
> it is impossible to return the correct type from the JdbcThinUtils class.
>
> Next, I decided to see how tables are created in the first place.
> Specifically, the GridSqlQueryParser.parseCreateTable() method. In there, I
> can see the correct column metadata. For example, if I add the following
> code in the parseCreateTable method...
> for (Column col : data.columns){
>    System.out.println(col.getName() + " (" + col.getType() + ")");
> }
>
> ...it will print the following:
> ID (5)
> BODY (16)
> LAST_MODIFIED (24)
>
> I would like to preserve this metadata so that I can see it when using the
> jdbc resultset metadata object. Any suggestions?
>
> Note that the ID column type (5) is preserved. I suspect the other types
> identified in the JdbcThinUtils class are preserved as well.
>
>
> Thanks in advance,Peter
>
>
>
>
>
>
>
>

 

[Vladimir Ozerov]

Hi Peter,

I am not aware of such ticket.

On Wed, Nov 21, 2018 at 2:23 PM Peter Borissow
<[hidden email]> wrote:

>  Hello Vladimir,    Is there a ticket for the issue I described in JIRA or
> should we create one and mark it blocked pending #6173?
>
> Thanks,Peter
>
>     On Wednesday, November 21, 2018, 3:30:21 AM EST, Vladimir Ozerov <
> [hidden email]> wrote:
>
>  Hi Peter,
>
> We will be able to fix this in consistent way for both "thin" and "thick"
> JDBC drivers once IGNITE-6173 [1] is merged. In this ticket we ensure that
> metadata for all schema objects are present on all nodes, so that we can
> simply delegate metadata request to underlying H2 table.
>
> [1] https://issues.apache.org/jira/browse/IGNITE-6173
>
> On Tue, Nov 20, 2018 at 8:06 PM Peter Borissow
> <[hidden email]> wrote:
>
> > Dear Devs,    I would like to help expand the jdbc resultset metadata
> > available when querying a table. Consider the following example:
> >
> >
> > CREATE TABLE ARTICLE (
> >    ID BIGINT,
> >    BODY text,
> >    LAST_MODIFIED TIMESTAMP with time zone,
> >        CONSTRAINT PK_ ARTICLE PRIMARY KEY (ID)
> >  );
> > After creating the table, I can query the column metadata using the
> > java.sql.ResultSetMetaData. Example:
> >
> >  java.sql.ResultSet rs = stmt.executeQuery("select * from article);
> > java.sql.ResultSetMetaData rsmd = rs.getMetaData();
> >
> > However, the resultset /column metadata will return the following types:
> >
> > - id: long- body: object- last_modified: object
> > Note that the body and last_modified fields are identified as generic
> > objects instead of their formal java types. This is not consistent with
> > other jdbc drivers (e.g. postgresql, h2, etc).
> >
> >  After a little digging, I found the
> JdbcColumnMeta(GridQueryFieldMetadata
> > info) constructor which calls the JdbcThinUtils class. However, at this
> > point the GridQueryFieldMetadata is already mapped to a generic "object"
> so
> > it is impossible to return the correct type from the JdbcThinUtils class.
> >
> > Next, I decided to see how tables are created in the first place.
> > Specifically, the GridSqlQueryParser.parseCreateTable() method. In
> there, I
> > can see the correct column metadata. For example, if I add the following
> > code in the parseCreateTable method...
> > for (Column col : data.columns){
> >    System.out.println(col.getName() + " (" + col.getType() + ")");
> > }
> >
> > ...it will print the following:
> > ID (5)
> > BODY (16)
> > LAST_MODIFIED (24)
> >
> > I would like to preserve this metadata so that I can see it when using
> the
> > jdbc resultset metadata object. Any suggestions?
> >
> > Note that the ID column type (5) is preserved. I suspect the other types
> > identified in the JdbcThinUtils class are preserved as well.
> >
> >
> > Thanks in advance,Peter
> >
> >
> >
> >
> >
> >
> >
> >
>