Unable to connect through JDBC

[pixelspark]

(Wolfgang Gehner wrote:)

Next issue is that the org.postgresql.Driver runs an initial query to figure out database encoding, to which Catena returns an error. So the stack trace is:

org.postgresql.util.PSQLException: ERROR: syntax error: 'set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end;'

Here is an example of the code that calls this:

http://grepcode.com/file/repo1.maven.org/maven2/postgresql/postgresql/8.4-702.jdbc3/org/postgresql/core/v2/ConnectionFactoryImpl.java#ConnectionFactoryImpl.runInitialQueries%28org.postgresql.core.v2.ProtocolConnectionImpl%2Cjava.lang.String%2Corg.postgresql.core.Logger%29

Let me know what you think would be good next steps. A working JDBC driver would make enterprise integration really smooth.

[pixelspark]

Catena does not support the 'set datestyle' nor the 'select version()' commands. These could be accepted by our pq server to improve compatibility. This means the SQL parser should be changed to parse these too (for select version() this is quite easy to implement; a bigger issue is the fact that the JDBC driver sends two queries separated by a semicolon, which is not currently supported).

The real issue here is that the JDBC driver assumes a Postgres database where really there isn't. As JDBC provides a quite uniform interface to its users, it is expected that it will fetch other metadata further down the road using commands Catena won't understand either. It would be preferable to have either a non-JDBC client (using HTTP or the PQ protocol) or have a separate JDBC driver (perhaps a fork of the Postgres one) with the Postgres specifics removed (and perhaps Catena specifics added in the future - this is why JDBC has drivers in the first place).

You can submit queries over HTTP if you want (POST to /api/query; see the web client source for more information; e.g. https://github.com/pixelspark/catena/blob/master/Resources/components/blockchain.js#L389). Note that this will only accept read-only queries. Mutating queries are a bit more involved, The /api/query endpoint will return a 420 result code with some additional information that can be used to compose a transaction for the mutating query. This transaction can then be submitted over the WebSocket interface (at /). The reason for this is that it is acceptable to send a private key over the pq connection (as it is intended to be used only locally) whereas this is not acceptable for the HTTP API. Also, the WebSocket endpoint is what instances of Catena use to broadcast transactions to each other.

[wgehner]

The latest Postgres JDBC driver (42.1.4) gets beyond the initial version check (it is implemented differently, without querying the db) but a query (select * from grants) fails with org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend. at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:332) It appears that the server closes the socket when it probably shouldn't. Thanks for describing the HTTP WebSocket API as a workaround.

[pixelspark]

@wgehner Good to know (do you end queries with a ";"? They all should be). Might be a regular exception (which for some reason is ignored before the connection is closed). Could you:

• Have a look at Catena full debugging output (run it as Catena -v debug)
• Provide me with a piece of Java source code or otherwise that I can use to reproduce this issue

[pixelspark]

OK, so the issue appears to be that the JDBC driver attempts to use protocol features that are not currently implemented by Catena (more specifically, server-side prepared statements). Support for this feature needs to be added in order for this to work.

Note, you may want to try to force the protocolVersion to an older one ("2" according to the documentation). On my system (with the latest JDBC driver version) this does not appear to work for some reason, but it may be possible to use older versions of the JDBC driver.

import java.sql.*;
import java.util.Properties;

public class test {
    public static void main(String[] args) {
        try {
            Class.forName("org.postgresql.Driver");
            String url = "jdbc:postgresql://localhost:8339/test";

            Properties props = new Properties();
            /* props.setProperty("protocolVersion","2"); */
            props.setProperty("user","random");
            props.setProperty("password","random");
            Connection db = DriverManager.getConnection(url, props);

            Statement st = db.createStatement();
            ResultSet rs = st.executeQuery("SELECT kind FROM grants;");
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
            rs.close();
            st.close();
        }
        catch(Exception e) {
            System.err.println(e.getMessage());
        }
    }
}

[pixelspark]

@wgehner latest commit introduces support for the extended queries as sent by JDBC. Note that parameter binding is not yet implemented (will be quite easy now). Please let me know if this works!

[pixelspark]

@wgehner parameter binding should also be supported, at least for string values. The following now works for me from Java:

Connection db = DriverManager.getConnection(url, props);
PreparedStatement st = db.prepareStatement("SELECT (1+1), kind, user, table FROM grants WHERE kind=?;");
st.setString(1, "insert");
ResultSet rs = st.executeQuery();

while (rs.next()) {
    System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3)+ "\t" + rs.getString(4));
}

[wgehner]

This works for me as well. Awesome, thanks!