Expanding avatica statement support

[Nick Dimiduk <nd...@gmail.com>]

I've started running an avatica server with JdbcMeta against a larger set
of queries. This has enlightened me of the semantics of JDBC's different
execute methods. It seems our RPC interfaces do not expose enough
information to be able to pass these semantics between client and server.
For instance, CREATE TABLE and UPSERT statements are not accepted.

My question is whether we should extend the RPC protocol to follow JDBC
spec (execute, executeQuery, executeUpdate) or add parameters to our single
method. I'm comfortable with the short-term decision (more RPC boilerplate
vs switching logic on client and server) but I'm wondering if there are
longer-term consequences I should consider; I'm not terribly familiar with
JDBC, nor how it differs from ODBC, &c. Any advice is appreciated.

Thanks,
Nick

Re: Expanding avatica statement support

[Nick Dimiduk <nd...@gmail.com>]

I'm not looking for parsing queries or validation, I just want to be able
to express exactly what the user intended. In exposing Phoenix, I'm finding
it does parse SQL on the client side and it is particular about what kinds
of SQL is executed with which methods. The current approach of everything
piping through prepareAndExecute and executing as
PreparedStatement#executeQuery is not sufficient for my use case. I'm not
sure if that's a bug in Phoenix or how things are broadly done.

On Fri, Mar 27, 2015 at 11:08 AM, Julian Hyde <ju...@hydromatic.net> wrote:

> I don't know the details of the JDBC spec but general impression is that
> any method (execute, executeUpdate, executeQuery) can be used for and kind
> of statement (SELECT, DML, DDL) provided that it can provide the required
> return type (result set, row count, void). Remember you can call
> Statement.execute then Statement.getResultSet immediately afterwards.
>
> Also, the client does not know what kind of statement it has been given,
> nor should it try to figure it out (say by parsing the statement).
>
> For example, the client does not need to treat "explain" differently from
> "select". "explain" is a query that returns a single row with a single
> column, so for instance executeQuery("explain plan for ...") should behave
> the same as executeQuery("values '<the plan>'").
>
> I think the client should send the SQL to the server along with the
> requested result type (result set, row count, void). The server should send
> back anything it has (row-count if applicable, status code, a list of 0 or
> more result sets, each represented as a Signature).
>
> Statement.getResultSet and Statement.getUpdateCount will never require an
> RPC; they just wrap what was previously returned.
>
> Statement.getMoreResults moves to the next result set. For now every
> statement will return either be 0 or 1 result set but I see no harm in
> having a list of result sets.
>
> JDBC also allows you to access an array-valued column as a ResultSet (via
> ResultSet.getArray(int).getResultSet()) but let's not worry about that
> right now.
>
> Julian
>
>
>
> > On Mar 27, 2015, at 8:12 AM, Nick Dimiduk <nd...@gmail.com> wrote:
> >
> > I've started running an avatica server with JdbcMeta against a larger set
> > of queries. This has enlightened me of the semantics of JDBC's different
> > execute methods. It seems our RPC interfaces do not expose enough
> > information to be able to pass these semantics between client and server.
> > For instance, CREATE TABLE and UPSERT statements are not accepted.
> >
> > My question is whether we should extend the RPC protocol to follow JDBC
> > spec (execute, executeQuery, executeUpdate) or add parameters to our
> single
> > method. I'm comfortable with the short-term decision (more RPC
> boilerplate
> > vs switching logic on client and server) but I'm wondering if there are
> > longer-term consequences I should consider; I'm not terribly familiar
> with
> > JDBC, nor how it differs from ODBC, &c. Any advice is appreciated.
> >
> > Thanks,
> > Nick
>
>

Re: Expanding avatica statement support

[Julian Hyde <ju...@hydromatic.net>]

I don't know the details of the JDBC spec but general impression is that any method (execute, executeUpdate, executeQuery) can be used for and kind of statement (SELECT, DML, DDL) provided that it can provide the required return type (result set, row count, void). Remember you can call Statement.execute then Statement.getResultSet immediately afterwards.

Also, the client does not know what kind of statement it has been given, nor should it try to figure it out (say by parsing the statement).

For example, the client does not need to treat "explain" differently from "select". "explain" is a query that returns a single row with a single column, so for instance executeQuery("explain plan for ...") should behave the same as executeQuery("values '<the plan>'").

I think the client should send the SQL to the server along with the requested result type (result set, row count, void). The server should send back anything it has (row-count if applicable, status code, a list of 0 or more result sets, each represented as a Signature).

Statement.getResultSet and Statement.getUpdateCount will never require an RPC; they just wrap what was previously returned.

Statement.getMoreResults moves to the next result set. For now every statement will return either be 0 or 1 result set but I see no harm in having a list of result sets.

JDBC also allows you to access an array-valued column as a ResultSet (via ResultSet.getArray(int).getResultSet()) but let's not worry about that right now.

Julian



> On Mar 27, 2015, at 8:12 AM, Nick Dimiduk <nd...@gmail.com> wrote:
> 
> I've started running an avatica server with JdbcMeta against a larger set
> of queries. This has enlightened me of the semantics of JDBC's different
> execute methods. It seems our RPC interfaces do not expose enough
> information to be able to pass these semantics between client and server.
> For instance, CREATE TABLE and UPSERT statements are not accepted.
> 
> My question is whether we should extend the RPC protocol to follow JDBC
> spec (execute, executeQuery, executeUpdate) or add parameters to our single
> method. I'm comfortable with the short-term decision (more RPC boilerplate
> vs switching logic on client and server) but I'm wondering if there are
> longer-term consequences I should consider; I'm not terribly familiar with
> JDBC, nor how it differs from ODBC, &c. Any advice is appreciated.
> 
> Thanks,
> Nick