JDBC Manual

the {{ocient}} jdbc driver and command line interface (cli) enable you to connect to ocient using a jdbc connection ensure that you meet the prerequisites before using the ocient jdbc driver then, invoke the cli program, configure options, and connect to a database using the driver you can also use the data extract tool to extract a result set to delimited files in the target location for details about data extracting, see docid 1 ls6prwuefq it66b4ee for a list of commands available in the jdbc cli, see docid\ zyojxip3zkuvr9skpiyxo prerequisites this software is required for the jdbc driver 300,301 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type driver features the ocient jdbc connector supports these features as of the current version 152,510 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type invoke the ocient jdbc cli program if your system meets all the necessary prerequisites, you can run the jdbc cli by using the ocient jdbc jar file to do this, follow these steps go to the ocient {{maven}} https //mvnrepository com/artifact/com ocient/ocient jdbc4 for all jdbc versions for more information on which version to pick, see the docid\ rkvsbniz0hzgg7aaqxwxo page for the jdbc version you want to use, download the jar file with dependencies this jar file follows the format ocient jdbc4 \<version number> jar with dependencies jar move this jar file to the directory where your ocient system is installed from the shell terminal, run this command to launch the jdbc cli java classpath \<path to jar with dependencies> com ocient cli cli \[\<username> \[\<password>]] this example runs jdbc version 2 10 java classpath /ocient jdbc4 2 10 jar with dependencies jar com ocient cli cli testuser testpassword after launching, the jdbc cli prompts you to enter your username and password username admin\@system password admin the interface changes to the ocient cli ocient> connect to your system from the jdbc using a connection string assuming the standard port 4050 , a self signed certificate, and the sql node ip address 10 10 1 1 , you can connect to the system database with the following connecting string ocient> connect to jdbc\ ocient //10 10 1 1 4050/system; the cli responds with a connection message connected to jdbc\ ocient //10 10 1 1 4050/system ocient> now that you are connected to your system, you can execute any queries or commands for java version 1 8 0 144, https //www oracle com/java/technologies/javase jce8 downloads html and install the java cryptography extension (jce) unlimited strength jurisdiction policy files 8 jdbc cli configuration options the jdbc cli reads a configuration file consisting of key value pairs located at / ocient cli configuration with this format key1=value1 key2=value2 keyn=valuen the jdbc cli supports these options true 200,200,201left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type for supported commands, see docid\ zyojxip3zkuvr9skpiyxo use the ocient jdbc driver in java programs first, you must load the ocient driver class with this statement in a java program using the jdbc driver class forname("com ocient jdbc jdbcdriver"); the driver class is located in the jdbc driver jar file named ocient jdbc4 jar and must be available in the classpath defined for the program connect using jdbc the ocient jdbc driver supports connection properties that can be supplied using the docid\ zyojxip3zkuvr9skpiyxo command in the jdbc cli or as a properties object passed in a java application jdbc url example this connection string includes various connection properties that trail the login credentials connect to 'jdbc\ ocient //db example com 6432/salesdb;user=admin\@system;password=admin;loglevel=info;networktimeout=15000;enablebulkload=true;bulkloadthreshold=50000'; java drivermanager example alternatively, you can use the drivermanager class to provide connection properties if you are using a jdbc connection as part of a java application import java sql connection; import java sql drivermanager; import java sql sqlexception; import java util properties; public class ocientconnectionexample { public static void main(string\[] args) throws sqlexception { // base jdbc url host, port, and database string url = "jdbc\ ocient //db example com 6432/salesdb"; // jdbc connection properties properties props = new properties(); props setproperty("user", "analytics user"); // ocient username props setproperty("password", "strongpassw0rd!"); // ocient password props setproperty("loglevel", "info"); // driver logging verbosity props setproperty("networktimeout", "15000"); // 15s network timeout (ms) props setproperty("enablebulkload", "true"); // use bulk load for large batches props setproperty("bulkloadthreshold", "50000"); // min rows for bulk loa try (connection conn = drivermanager getconnection(url, props)) { } } } supported jdbc connection properties the jdbc driver supports these connection parameters 330,331 true left unhandled content type left unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left unhandled content type left unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left 1 1 unhandled content type left 1 1 unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type jdbc bulk loading for very large batches, the ocient jdbc driver provides a high speed bulk load path when you enable bulk loading, the driver bypasses the standard multi row insert operation and instead streams the batch data directly to the loader nodes in the cluster using ssh/sftp the data loads in the in memory https //jsonlines org/ format using a temporary, system generated ocient pipeline, providing a massive performance boost for large scale data ingestion configuration follow these steps to configure bulk loading in your jdbc driver ssh key access and node jdbc bulk loading does not use password authentication instead, you must use ssh access for all loader nodes in the cluster to point the driver to your private ssh key file, use the bulkloadsshkeypath jdbc connection property the client application must be able to read this file the driver automatically discovers available loader nodes by querying the docid\ tbjvq0xtd tcxq17hm nc and docid\ tbjvq0xtd tcxq17hm nc system catalog tables data type mapping the jdbc driver supports all standard scalar types for complex types, java sql struct ( java sql struct ) types load as ocient tuple types, and java array ( java sql array ) types load as ocient array types enable bulk loading the jdbc driver disables bulk loading by default to enable it, you must meet these conditions set the enablebulkload connection property to true use a parameterized insert statement that uses placeholders for values, e g , insert into customers (id, name, status) values (?, ?, ?) for details, see docid apnndn tjqmjdd5oqdvd the total number of rows in the batch group meets or exceeds the bulkloadthreshold property for recommended configuration settings for spark workloads, see docid\ pp91aew4 1hy1pft3f4zs set up parameterized insert statements these steps demonstrate how to use a single parameterized insert statement using the java preparedstatement class the parameterized statement binds different values for each row create the preparedstatement object ps with ? placeholders string sql = "insert into customers (id, name, status) values (?, ?, ?)"; preparedstatement ps = conn preparestatement(sql); bind parameter values by position ps setlong(1, 123l); // first ? ps setstring(2, "alice"); // second ? ps setstring(3, "active"); // third ? execute a single insert ps executeupdate(); or, add multiple rows as a batch // first row ps setlong(1, 123l); ps setstring(2, "alice"); ps setstring(3, "active"); ps addbatch(); // second row ps setlong(1, 124l); ps setstring(2, "bob"); ps setstring(3, "inactive"); ps addbatch(); // send them together int\[] results = ps executebatch(); close the resources ps close(); connection encryption (ssl/tls) the jdbc driver can use ssl/tls to connect to ocient, causing all traffic to be encrypted specify the tls property on the connect statement to enable tls support the tls property supports these values unverified traffic on the connection is encrypted, but no verification is done on the certificate received from the ocient system on traffic is encrypted, and the jdbc client must be able to verify the certificate received from the ocient system the tls on mode requires that the client knows the certificate authority that signed the certificate provided by the ocient system typically, this mode requires either that the certificate is signed by a well known certificate authority, or the certificate authority certificate has been imported into the truststore of the java system the java keytool utility is used to manipulate a java truststore docid\ ffbnx0kvadbdpj bfjsse discusses how you can configure user defined certificates for the ocient system sample java program using the ocient jdbc driver this sample program demonstrates how to utilize the ocient jdbc driver to establish a connection to a database, construct a prepared sql statement, execute the query, and iterate through the result set public class ocientjdbcexample { public static void main(final string args\[]) { class forname("com ocient jdbc jdbcdriver"); properties props = new properties(); props setproperty("user", "username"); props setproperty("password", "pwd"); props setproperty("force", "true"); string url = "jdbc\ ocient //192 168 121 82 4050/db"; connection conn = drivermanager getconnection(url, props); preparedstatement pstmt = conn preparestatement( "select l orderkey from tpch lineitem where l linenumber = ?"); pstmt setint(1, 4); resultset rs = pstmt executequery(); while(rs next()){ // do something with row } rs close(); pstmt close(); conn close(); return; } } for supported classes and methods, see docid\ vknnjxbrekwndt3kpt3ln related links docid 1 p8y vgpzkd8k 0hxqd7 docid 1 ls6prwuefq it66b4ee docid\ zyojxip3zkuvr9skpiyxo docid\ vknnjxbrekwndt3kpt3ln {{linux}} is the registered trademark of linus torvalds in the u s and other countries