This project contains the base driver code and enhancements to support the MarkLogic metadata functions through an Open-Source JDBC Driver.
This project and its code and functionality is not representative of MarkLogic Server and is not supported by MarkLogic. This is not a MarkLogic product, but an unsupported, unofficial tool developed by MarkLogic consultants in the field. You should test it in a development environment thoroughly before depending on it in production.
If possible you should use the [ODBC drivers supported by MarkLogic|https://developer.marklogic.com/products/odbc]. If you can only use JDBC (because for example the tool only supports JDBC) then you have two options for drivers. You have a choice of using the JDBC driver from PostgreSQL with no modifications postgresql-42.1.4.jar, or the enhanced MarkLogic driver mljdbc-42.1.4.jar contained in this project.
MarkLogic provides a read-only SQL engine and an application server over TCP/IP. The MarkLogic application server is called ODBC, but it is actually not specific to the ODBC protocol and can also support JDBC. The server implements a database native communication protocol that can efficiently support both ODBC and JDBC clients.
Read and understand the ML support for SQL before assuming JDBC will work (such as SQL dialect and transaction support).
As of JDK 8, the JDBC/ODBC bridge is no longer supported The class sun.jdbc.odbc.JdbcOdbcDriver has been removed from JAVA. Workarounds on the Windows platform have been proposed, but are discouraged. Commercial bridges are available at a cost. With a pure Java JDBC driver for MarkLogic, you can write applications in JAVA or use applications that support JDBC for connecting to databases. The PostgreSQL JDBC driver front end on the client connects with an ML “ODBC” server by means of the PostgreSQL network message protocol version 3 (PROTOCOL_VERSION). The server returns the relational-style data needed by the client such as BI applications to build reports. A JDBC solution eliminates the need for a client-side ODBC manager and driver since the JDBC driver speaks directly to the native MarkLogic ODBC application server protocol. This JDBC driver is based on code for the JDBC API specification 4.2, but the exposed implementation features are closer to version 1.20 January 10, 1997.
1. Create TDEs
MarkLogic allows you to define a template view that specifies which parts of the document make up a row in a view you can query via SQL. See the Creating Template Views in the Data Modelling Guide for instructions.
2. Setup ODBC application server
MarkLogic requires an ODBC application server to process SQL requests. See the Create an ODBC App Server section in the product documentation for setup instructions.
3. Install JAR and configure connection details
Download the JAR from this project and be sure to include it in your classpath.
Configure the connection driver name (aka Class.forName) and the connection string URL: jdbc:<vendor>://<host>:<port>/
Driver Name: "com.marklogic.Driver"
Driver URL: "jdbc:marklogic://localhost:8077/"
The MarkLogic custom driver now uses preferQueryMode=simple as default and does not require setting. preferQueryMode value of simple mode matches the MarkLogic ODBC Server protocol. preferQueryMode specifies which mode is used to execute queries to database: simple means (‘Q’ execute, no parse, no bind, text mode only), extended means always use bind/execute messages, extendedForPrepared means extended for prepared statements only, extendedCacheEverything means use extended protocol and try cache every statement (including Statement.execute(String sql)) in a query cache.
Be sure to include the slash after the host colon port jdbc:marklogic://<host>:<port>/
If you receive the error message: ERROR: XDMP-INTERNAL: Internal error: 'D': no such portal "" Be sure to check the connection string for typos: preferQueryMode=simple
If you receive the stack trace message: org.postgresql.util.PSQLException: Protocol error. Session setup failed. And the error log contains: Info: [Event:id=ODBCConnectionTask SendMessage] 71 [error response] <= E SERROR C08000 MXDMP-INTERNAL: Internal error: Password incorrect Be sure the password is correctly encoded in your JAVA for Digest authentication
readonly=true can optionally be configured since MarkLogic only supports read-only operations over SQL.
Driver URL: jdbc:marklogic://localhost:8077/?readonly=true
For logging and debugging in the driver loggerLevel=TRACE or loggerLevel=DEBUG can be configured to see driver-level messages on the client. Use loggerFile=pgjdbc-trace.log for capturing log.
Allowed values: OFF, DEBUG or TRACE
import java.sql.*;
public class Test {
public static void main (String[] args) {
try {
Class myClass = Class.forName("com.marklogic.Driver");
System.out.println("Before getConnection");
DriverManager.setLogStream(System.out);
Connection conn = DriverManager.getConnection("jdbc:marklogic://localhost:8077/","user","pw");
System.out.println("Connected successfully");
// JAVA SQL code
Statement stmt = conn.createStatement();
String sql = "SELECT SCHEMA, NAME FROM SYS_TABLES";
System.out.println("Before executeQuery");
ResultSet rs = stmt.executeQuery(sql);
System.out.println("Extract data from ResultSet");
while(rs.next()){
String schema = rs.getString("SCHEMA");
String name = rs.getString("NAME");
System.out.print("SCHEMA: " + schema);
System.out.print(", NAME: " + name);
System.out.print("\n");
}
rs.close();
stmt.close();
/* If using custom mljdbc driver */
//DatabaseMetaData md = conn.getMetaData();
//ResultSet mdrs = md.getTables(null, null, "%", null);
//while (mdrs.next()) {
// System.out.println("SCHEMA: " + mdrs.getString(2) + ", NAME: " + mdrs.getString(3));
//}
//mdrs.close();
//md.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
The PostgreSQL driver’s MD5 Digest hash method (in ConnectionFactoryImpl.java and MD5Digest.java) does not match the Digest method in MarkLogic. PostgreSQL encodes user/password/salt information in the following way: MD5(MD5(password + user) + salt) where salt is realm ex. “public”. The custom driver’s new approach uses user+”:”+salt+”:”+password like Apache with 32 bytes of salt.
Encryption can be accomplished with ssl=true using certificates. https://basildoncoder.com/blog/postgresql-jdbc-client-certificates.html
For SSL connections, you have several options:
The JDBC driver provides an option to establish an SSL connection without doing any validation. For SSL connections with a non-validating certificate such as a self-signed server certificate generated by a MarkLogic certificate template. First, configure MarkLogic Server for SSL.
jdbc:marklogic://localhost:8077/?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
And add tracing for feedback:
jdbc:marklogic://localhost:8077/?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&loggerLevel=TRACE
The org.postgresql.ssl.jdbc4 package includes the LibPQFactory class which handles .crt files:
jdbc:marklogic://localhost:8077/?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-ca&sslrootcert=certificate.crt&loggerLevel=TRACE
To determine if the JDBC driver is in use for a connection, turn on diagnostic trace events for ODBCConnectionTask SendMessage ODBCConnectionTask ReceiveMessage then when a new connection is made the MarkLogic Server log for the ODBC port will contain the following indicating that the JDBC driver is in use (and not the ODBC driver):
Info: [Event:id=ODBCConnectionTask ReceiveMessage] => Q SET application_name = 'MarkLogic JDBC Driver'
For logging and debugging loggerLevel=TRACE or loggerLevel=DEBUG can be configured to see driver-level messages on the client.
For debugging SSL, add DEBUG -Djavax.net.debug=SSL to your JAVA command line.
On the client, using a connection string with loggerLevel TRACE jdbc:marklogic://localhost:8077/?loggerLevel=TRACE
FINEST: FE=> StartupPacket(user=admin, database=, client_encoding=UTF8, DateStyle=ISO, TimeZone=America/New_York, extra_float_digits=2)
Jan 17, 2018 9:32:18 AM org.postgresql.core.v3.ConnectionFactoryImpl doAuthentication
FINEST: <=BE AuthenticationReqMD5(salt=XXXXXXXX)
Jan 17, 2018 9:32:18 AM org.postgresql.core.v3.ConnectionFactoryImpl doAuthentication
FINEST: FE=> Password(md5digest=md5XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX)
org.postgresql.util.PSQLException: Protocol error. Session setup failed.
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:622)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:222)
To help debug problems with JDBC connection to the ML ODBC server, I have found the diagnostic trace events to be very helpful. ODBCConnectionTask SendMessage ODBCConnectionTask ReceiveMessage
The PostgreSQL network message protocol will get logged with messages like “R”, “S” and “Q” (receive, send and query). For example, On the server with diagnostic trace events turned on:
[Event:id=ODBCConnectionTask SendMessage] 41 [authentication] <= R auth=5(MD5) md5salt=public [Event:id=ODBCConnectionTask ReceiveMessage] => p [Event:id=ODBCConnectionTask SendMessage] 71 [error response] <= E SERROR C08000 MXDMP-INTERNAL: Internal error: Password incorrect
ML is a read-only SQL interface (with some support for temporary views). SQL blocks that start with BEGIN are not supported.
https://docs.marklogic.com/guide/sql/SQLqueries#id_32736
Issues with dateTime are not a type mapping but a content problem: Trailing junk on timestamp: ‘T00:00:00’ at org.postgresql.jdbc.TimestampUtils.parseBackendTimestamp(TimestampUtils.java:339)
Java does not support ISO-8601 dateTime. Java datetimetz does not use the ‘T’ delimiter found in ISO-8601. '2013-01-02 03:04:05.060708 +9:00' vs 2013-01-02T03:04:05.060708 +9:00Work around does not work fn:format-dateTime(xs:dateTime(“2013-01-02T03:04:05.060708 +9:00”), “[Y0001]-[M01]-[D01] [H01]:[m01]:[s01].[f01] [z]”,”en”,”AD”,”US”)
Modified TimestampUtils
Could look at cast(dateTime as timestamptz)
Problem with the Oid mapping values for unsignedShort, short, byte, unsignedByte are 0 from ML SQL function pg_type_id()
anyURI = 25 appears to be supported as text
The PostgreSQL JDBC driver is not thread safe. The PostgreSQL server is not threaded. Each connection creates a new process on the server; as such any concurrent requests to the process would have to be serialized. The driver makes no guarantees that methods on connections are synchronized. It will be up to the caller to synchronize calls to the driver. A notable exception is org/postgresql/jdbc/TimestampUtils.java which is threadsafe.
— [select * from (select NULL, name as nspname, NULL from sys.sys_schemas) where 1 and nspname not in (‘sys’) order by nspname]
— [select * from (select name as relname, schema as nspname, type as relkind from sys.sys_tables) where 1 and nspname not in (‘sys’)]
— [select * from (select schema as nspname, table as relname, name as attname, pg_type_id(type) as atttypid, type as typname, cid as attnum, pg_type_size(type) as attlen, 0 as atttypmod, ‘notnull’ as attnotnull, 0 as relhasrules, type as relkind, 0 as oid, 0, 0, -1 from sys.sys_columns) where 1 and relname like ’employees’ and nspname like ‘main’ order by nspname, relname, attnum]
— [select * from (select -1, type as relkind from sys.sys_tables) where 1 and name = ’employees’ and schema = ‘main’]
SQLGetTypeInfo — [select * from (select schema as nspname, table as relname, name as attname, pg_type_id(type) as atttypid, type as typname, cid as attnum, pg_type_size(type) as attlen, 0 as atttypmod, ‘notnull’ as attnotnull, 0 as relhasrules, type as relkind, 0 as oid, 0, 0, -1 from sys.sys_columns) where 1 and relname like ’employees’ and nspname like ‘main’ order by nspname, relname, attnum] [select * from (select -1, type as relkind from sys.sys_tables) where 1 and name = ’employees’ and schema = ‘main’]
By continuing to use this website you are giving consent to cookies being used in accordance with the MarkLogic Privacy Statement.