Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
DBTools Service sample for REST-SQL
package io.krisice;
import com.oracle.bmc.http.signing.RequestSigningFilter;
import javax.ws.rs.client.*;
import javax.ws.rs.core.MediaType;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
public class DBToolsServiceRestSQLExamples {
public static void main(String[] args) throws IOException {
System.setProperty("sun.net.http.allowRestrictedHeaders", "true");
var configurationFilePath = "~/.oci/config";
var profile = "SICDBAAS";
var ocid = "ocid1.databasetoolsconnection.oc1.phx.amaaaaaacsc5xjaatnzc7o3fmtmk76ecmkhhraamkobhqvi3yr4hx5sywt4q";
var dns = "sql.dbtools.us-phoenix-1.oci.oraclecloud.com";
RequestSigningFilter requestSigningFilter = RequestSigningFilter.fromConfigFile(configurationFilePath, profile);
Client client = ClientBuilder.newBuilder().build().register(requestSigningFilter);
// exec plain sql
executeSQL(dns, ocid,client);
// exec sql using rest-sql json w/ binds
executeSQLWithBinds(dns, ocid, client);
// exec sql and export to CSV
executeSQLExport(dns, ocid,client);
// CUSTOM Format exec sql and export to CSV w/ binds
executeSQLExportWithBindsCustomFormat(dns,ocid,client);
// built in formats
executeSQLExportWithBindsFormatted(dns,ocid,client,"json");
executeSQLExportWithBindsFormatted(dns,ocid,client,"text");
executeSQLExportWithBindsFormatted(dns,ocid,client,"csv");
executeSQLExportWithBindsFormatted(dns,ocid,client,"xml");
}
private static void executeSQLWithBinds(String dns, String ocid, Client client) {
var sqlpath = "/20201005/ords/" + ocid + "/_/sql";
var url = "https://"+dns + sqlpath;
// send json sql script
WebTarget target = client.target("https://"+ dns).path(sqlpath);
var jsonSQL = """
{"offset":0,"limit":100,"statementText":"SET SERVEROUTPUT ON
set timing ON;
set define &;
set escape OFF;
select :NAME from dual",
"binds":[{"name":"NAME","data_type":"string","value":"Todd"}]}""";
Entity<String> jsonSQLPayload = Entity.entity(jsonSQL,MediaType.APPLICATION_JSON_TYPE);
String jsonResults= target.request().post(jsonSQLPayload,String.class);
System.out.println(jsonResults);
System.out.println("\n\n");
}
private static void executeSQL(String dns, String ocid,Client client) {
var sqlpath = "/20201005/ords/" + ocid + "/_/sql";
var url = "https://"+dns + sqlpath;
var sql ="select * from dual";
WebTarget target = client.target("https://"+ dns).path(sqlpath);
// send raw sql script
Entity<String> sqlPayload = Entity.entity(sql,"application/sql");
String results= target.request().post(sqlPayload,String.class);
System.out.println(results);
System.out.println("\n\n");
}
private static void executeSQLExport(String dns, String ocid, Client client) throws IOException {
/// export to CSV
var sqlpath = "/20201005/ords/" + ocid + "/_/sql/export";
var url = "https://"+dns + sqlpath;
var sql = """
data={"statementText":"select * from all_objects where rownum<100","formatDetails":{"format":"csv","lineTerminator":"\\n","leftEnclosure":"\\"","rightEnclosure":"\\"","header":true}}
""";
WebTarget target = client.target("https://"+ dns).path(sqlpath);
// send raw sql script
Entity<String> sqlPayload = Entity.entity(sql,MediaType.APPLICATION_FORM_URLENCODED_TYPE);
printResults(target, sqlPayload);
}
private static void executeSQLExportWithBindsCustomFormat(String dns, String ocid, Client client) throws IOException {
/// export to CSV
var sqlpath = "/20201005/ords/" + ocid + "/_/sql/export";
var url = "https://"+dns + sqlpath;
var sql = """
data={"statementText":"select * from (select * from all_objects where rownum < :rownum)","formatDetails":{"delimiter":"|","format":"csv","header":true,"isInsertCommit":true,"leftEnclosure":"\\t","lineTerminator":",\\n","rightEnclosure":"\\t","rowInterval":100,"tableName":"EXPORT_TABLE"},"binds":[{"data_type":"NUMBER","value":"10","name":"rownum"}]}
""";
WebTarget target = client.target("https://"+ dns).path(sqlpath);
// send raw sql script
Entity<String> sqlPayload = Entity.entity(sql,MediaType.APPLICATION_FORM_URLENCODED_TYPE);
printResults(target, sqlPayload);
}
private static void executeSQLExportWithBindsFormatted(String dns, String ocid, Client client,String format) throws IOException {
/// export to <format>> xml,json,cxv,text
var sqlpath = "/20201005/ords/" + ocid + "/_/sql/export";
var url = "https://"+dns + sqlpath;
var sql = """
data={"statementText":"select * from (select * from all_objects where rownum < :rownum)","formatDetails":{"format":" """ + format + """
","header":true,"rowInterval":100,"tableName":"EXPORT_TABLE"},"binds":[{"data_type":"NUMBER","value":"10","name":"rownum"}]}
""";
WebTarget target = client.target("https://"+ dns).path(sqlpath);
// send raw sql script
Entity<String> sqlPayload = Entity.entity(sql,MediaType.APPLICATION_FORM_URLENCODED_TYPE);
printResults(target, sqlPayload);
}
// print it all out
private static void printResults(WebTarget target, Entity<String> sqlPayload) throws IOException {
var results = target.request().post(sqlPayload, Reader.class);
char[] c = new char[10240];
while(results.ready()){
int l = results.read(c);
System.out.println(new String(c,0,l));
}
System.out.println("\n\n");
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment