Dropbox

Building the JDBC URL

After installing the license, open the connection management page by running java -jar kingswaysoft.jdbc.jar. Enter the required details to generate the JDBC connection URL. Click Test Connection to test the generated URL, or Copy to Clipboard to copy the connection string for use in your application.

Note: If the license is not installed, you can still use the connection manager to generate a JDBC URL; however, the Test Connection feature will be disabled.

General Page

The General page allows you to specify connection properties and login credentials for the Dropbox API.

General Settings

Authentication Methods for Dropbox

Advanced Settings Page

The Advanced Settings page allows you to specify advanced settings for the connection.

Using the JDBC Driver

This section provides examples that use JDBC classes such as Connection, Statement, and ResultSet to manage interactions with Dropbox data. It covers regular statements and prepared statements for complex or frequently executed queries.

Query Level Options

The driver supports passing some connection options directly within the query. Query level options will override connection level properties. Append the clause USING OPTIONS (\{option\}=\{value\}) to your INSERT/UPDATE/DELETE/UPSERT query. This clause is optional.

For example, to override the property BatchSizeForWriting for a specific query, set the clause to USING OPTIONS (batchsize=N), where N is the size of the batch to write. This can be useful when executing multiple statements with different batch sizes without having to re-initialize the connection.

Dropbox queries support the following query options:

SelectionMode

To control which items are returned from a folder table, add USING OPTIONS (SelectionMode='\{value\}') to the end of the query.

FileOverwriteMode

When a INSERT, UPSERT, UPDATE or CALL query encounters a conflict due to a duplicate file name by default an error will be returned. To change the behavior in these situations, use the option FileOverwriteMode in your query.

Valid values for CALL and UPDATE:

Valid values for INSERT and UPSERT:

Relative and Full File Paths

The driver supports using the path of a file in queries. A path value starting with / is expected to be full path while a path value not prefixed with / is handled as relative path.

-- Delete 'Child.txt' from the 'Parent' folder using the full pathDELETEFROM Folder.Parent WHERE path = "/Parent/Child.txt"-- Delete 'Child.txt' from the 'Parent' folder using relative pathDELETEFROM Folder.Parent WHERE path = "Child.txt"-- From the 'Parent' folder, delete 'Child.txt'DELETEFROM Files WHERE path = "/Parent/Child.txt"-- From the 'Root' folder, delete 'Child.txt'DELETEFROM Files WHERE path = "/Child.txt"

Folders as Tables

Folders found within the Dropbox account will be used as tables within the driver. For example, if you have a folder named Receipts, it will appear as Folder.Receipts within the driver. This allows you to directly interact with the folder without having to specify the path to the folder.

When Start Path is set, Files, Folders, and Items can omit path. The query lists that folder. A path written in the query overrides Start Path. When Start Path is blank, path stays required, together with the other locators for that table. An explicit path, file_id, or folder_id still resolves from All Files.

Searching for Items, Files and Folders

Items

Use the Items table to query the Dropbox API for files and folders. This table requires specifying either query or path.

When using query, you can search by name. Results are returned where the file or folder name matches the query.

When using path, you can search by the path of a folder. For example, to query all items at the root level of the Dropbox account, the query would be SELECT * FROM Items WHERE path = '/'. This will return all items and folders from the root level.

Files

Use the Files table to query the Dropbox API for files. This table requires specifying the field path. Use this table when reading to return only files found in the specified path. When writing, specify the path that the file will be placed in.

Folders

Use the Folders table to query the Dropbox API for folders. This table requires specifying the field path. Use this table when reading to return only folders found in the specified path. When writing, specify the path that the folder will be placed in.

Executing Statements

After you have connected from your code, you can execute SQL statements using the Statement class. For connection details, see Connecting with DriverManager or Connecting with DataSource. For parameterized statements, see Executing Prepared Statements.

SELECT

Use the Statement class's generic execute method or the executeQuery method to execute SQL statements that return data. To retrieve the results of a query, call the getResultSet method of the Statement.

String sql = "SELECT * FROM Outer USING OPTIONS(SelectionMode='Recursive')";
try {
    ResultSet resultSet = statement.executeQuery(sql);
    LOGGER.info(resultSet.toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

INSERT

Use either the generic execute method or the executeUpdate method of the Statement class to execute an INSERT operation.

The results of SQL queries are saved in a ResultSet. You can retrieve the ResultSet after execution to view the inserted data's ID, exceptions raised during execution, and details of the affected data.

String sql = "INSERT INTO Files (path, file_content) VALUES ('/Outer/file.jpg', '{Base64 encoded string}') USING OPTIONS(FileOverwriteMode='Overwrite')";

try {
    int result = statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
id:rnjZ6vjAfTAAAAAAAAADWg,null,null,null,false

UPDATE

Use either the generic execute method or the executeUpdate method of the Statement class to execute an UPDATE operation.

The results of SQL queries are saved in a ResultSet. You can retrieve the ResultSet after execution to view the updated data's ID, exceptions raised during execution, and details of the affected data.

Note: The to_path destination folder must not already exist when from_path is a folder, otherwise a conflict error will be thrown.

String sql = "UPDATE Files SET to_path = '/Outer/Inner/fileTo.jpg' WHERE from_path = '/Outer/file.jpg'";
try {
    statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
id:rnjZ6vjAfTAAAAAAAAADWg,null,null,{"to_path":"/Outer/Inner/file.jpg","from_path":"/Outer/file.jpg","autorename":false,"allow_shared_folder":true,"allow_ownership_transfer":true},false

CALL

Use either the generic execute method or the executeUpdate method of the Statement class to execute a CALL operation. Refer to system.functions to determine available operations. To determine if a field is supported refer to system.functions.columns for more information.

String sql = "CALL Copy (from_path, to_path) VALUES ('/Outer/file.jpg', '/Outer/Inner/file.jpg')";
try {
    statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id:rnjZ6vjAfTAAAAAAAAADWg,null,null,{"to_path":"/Outer/Inner/file.jpg","from_path":"/Outer/file.jpg","autorename":false,"allow_shared_folder":true,"allow_ownership_transfer":true},false

String sql = "CALL Move (from_path, to_path) VALUES ('/Outer/file.jpg', '/Outer/Inner/file.jpg')";
try {
    statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id:rnjZ6vjAfTAAAAAAAAADWg,null,null,{"to_path":"/Outer/Inner/file.jpg","from_path":"/Outer/file.jpg","autorename":false,"allow_shared_folder":true,"allow_ownership_transfer":true},false

String sql = "CALL Copy (from_path, to_path) VALUES ('/Outer/file.jpg', '/Outer/Inner/file.jpg') USING OPTIONS (FileOverwriteMode='RaiseError')";
try {
    statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

DELETE

Use either the generic execute method or the executeUpdate method of the Statement class to execute a DELETE operation.

The results of SQL queries are saved in a ResultSet. You can retrieve the ResultSet after execution to view the deleted data's ID, exceptions raised during execution, and details of the affected data.

Note: A path value starting with / is expected to be full path while a path value not prefixed with / is handled as relative path. The item type of path is not validated; deletion is performed based on the provided path.

String sql = "DELETE FROM Files WHERE path = '/Outer/Inner/fileTo.jpg'";
try {
    statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
id:rnjZ6vjAfTAAAAAAAAADWg,null,null,{"path":"/Outer/Inner/file.jpg"},false

UPSERT

Using the UPSERT operation, you can either insert or update an existing record in one call.

Not all tables support the upsert operation. Query the system.tables table to identify which tables support UPSERT operations.

The results of SQL queries are saved in a ResultSet. You can retrieve the ResultSet after execution to view the ID of upserted data, exceptions raised during execution, and the data affected by the upsert.

Note: A path value starting with / is expected to be full path while a path value not prefixed with / is handled as relative path.

String sql = "UPSERT INTO Files (path, file_content) VALUES ('/Outer/file.jpg', '{Base64 encoded string}')";
try {
    statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
id:rnjZ6vjAfTAAAAAAAAADWg,null,null,null,false

Executing Prepared Statements

Using a PreparedStatement can improve performance when you need to execute a SQL statement multiple times with different parameters. Unlike a Statement object, a PreparedStatement object is provided with a SQL statement when it is created, which can then be executed with different values each time. This special type of statement is derived from the more general Statement class.

Below are the steps outlining how to execute a prepared statement:

SELECT

Use the PreparedStatement class's generic execute method or the executeQuery method to execute SQL statements that return data.

The results of SQL queries are saved in a ResultSet. You can retrieve the ResultSet after execution to view the retrieved data.

String sql = "SELECT * FROM Outer USING OPTIONS(SelectionMode='Recursive')";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    boolean ret = ps.execute();
    if (ret) {
        ResultSet rs = ps.getResultSet();
        LOGGER.info(rs.toString());
    }
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

INSERT

Use either the generic execute method or the executeUpdate method of the PreparedStatement class to execute an INSERT operation.

The results of SQL queries are saved in a ResultSet. You can retrieve the ResultSet after execution to view the ID of inserted data, exceptions raised during execution, and the data affected by the insertion.

String sql = "INSERT INTO Files (path, file_content) VALUES (?, ?) USING OPTIONS(FileOverwriteMode='Overwrite')";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "/Outer/file.jpg");
    ps.setString(2, "{Base64 encoded string}");
    ps.executeUpdate();

    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
id:rnjZ6vjAfTAAAAAAAAADWg,null,null,null,false

UPDATE

Use either the generic execute method or the executeUpdate method of the PreparedStatement class to execute an UPDATE operation.

The results of SQL queries are saved in a ResultSet. You can retrieve the ResultSet after execution to view the ID of updated data, exceptions raised during execution, and the data affected by the update.

Note: The to_path destination folder must not already exist when from_path is a folder, otherwise a conflict error will be thrown.

String sql = "UPDATE Files SET to_path = ? WHERE from_path = ?";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "/Outer/Inner/fileTo.jpg");
    ps.setString(2, "/Outer/file.jpg");
    ps.executeUpdate();

    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
id:rnjZ6vjAfTAAAAAAAAADWg,null,null,{"to_path":"/Outer/Inner/file.jpg","from_path":"/Outer/file.jpg","autorename":false,"allow_shared_folder":true,"allow_ownership_transfer":true},false

CALL

Use either the generic execute method or the executeUpdate method of the Statement class to execute a CALL operation. Refer to system.functions to determine available operations. To determine if a field is supported refer to system.functions.columns for more information.

String sql = "CALL Copy (from_path, to_path) VALUES (?, ?)";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "/Outer/file.jpg");
    ps.setString(2, "/Outer/Inner/fileTo.jpg");
    ps.executeUpdate();
    
    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id:rnjZ6vjAfTAAAAAAAAADWg,null,null,{"to_path":"/Outer/Inner/file.jpg","from_path":"/Outer/file.jpg","autorename":false,"allow_shared_folder":true,"allow_ownership_transfer":true},false

String sql = "CALL Move (from_path, to_path) VALUES (?, ?)";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "/Outer/file.jpg");
    ps.setString(2, "/Outer/Inner/fileTo.jpg");
    ps.executeUpdate();
    
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id:rnjZ6vjAfTAAAAAAAAADWg,null,null,{"to_path":"/Outer/Inner/file.jpg","from_path":"/Outer/file.jpg","autorename":false,"allow_shared_folder":true,"allow_ownership_transfer":true},false

String sql = "CALL Copy (from_path, to_path) VALUES (?, ?) USING OPTIONS (FileOverwriteMode='RaiseError')";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "/Outer/file.jpg");
    ps.setString(2, "/Outer/Inner/fileTo.jpg");
    ps.executeUpdate();
    
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

DELETE

Use either the generic execute method or the executeUpdate method of the PreparedStatement class to execute a DELETE operation.

The results of SQL queries are saved in a ResultSet. You can retrieve the ResultSet after execution to view the deleted data's ID, exceptions raised during execution, and details of the affected data.

Note: A path value starting with / is expected to be full path while a path value not prefixed with / is handled as relative path. The item type of path is not validated; deletion is performed based on the provided path.

String sql = "DELETE FROM Files WHERE path = ?";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "/Outer/Inner/fileTo.jpg");
    ps.executeUpdate();
    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    e.printStackTrace();
}

id,errorcode,errormessage,processdata,haserrors
id:rnjZ6vjAfTAAAAAAAAADWg,null,null,{"path":"/Outer/Inner/file.jpg"},false

UPSERT

Using the UPSERT operation, you can either insert or update an existing record in one call.

Not all tables support the upsert operation. Query the system.tables table to identify which tables support UPSERT operations.

The results of SQL queries are saved in a ResultSet. You can retrieve the ResultSet after execution to view the ID of upserted data, exceptions raised during execution, and the data affected by the upsert.

Note: A path value starting with / is expected to be full path while a path value not prefixed with / is handled as relative path.

String sql = "UPSERT INTO Files (path, file_content) VALUES (?, ?)";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "/Outer/file.jpg");
    ps.setString(2, "{Base64 encoded string}");
    ps.executeUpdate();
    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.error(e);
}

id,errorcode,errormessage,processdata,haserrors
id:rnjZ6vjAfTAAAAAAAAADWg,null,null,null,false

Metadata Discovery

This section provides examples on how to retrieve table and column metadata using the getTables, getColumns, and getPrimaryKeys methods from the DatabaseMetaData interface. These are essential for discovering database structures.

Tables

The getTables method from the DatabaseMetaData interface can be used to retrieve a list of tables.

This method only retrieves tables that are not write-only.

To get a list of tables that includes write-only tables, query the table system.tables.

try {
    ResultSet rs = connection.getMetaData().getTables(null, null, null, null);
    LOGGER.info("\r\n" + rs.toString());
} catch (SQLException e) {
    LOGGER.severe(e.getMessage());
}

TABLE_CAT,TABLE_SCHEM,TABLE_NAME,TABLE_TYPE,REMARKS,TYPE_CAT,TYPE_SCHEM,TYPE_NAME,SELF_REFERENCING_COL_NAME,REF_GENERATION
null,null,Folder.Apps,TABLE,null,null,null,null,null,null
null,null,Folder.Apps/Markdown Webhooks Example,TABLE,null,null,null,null,null,null
null,null,Folder.Biml Blog,TABLE,null,null,null,null,null,null
null,null,Folder.CRM Email Attachment,TABLE,null,null,null,null,null,null
null,null,Folder.DemoSharings,TABLE,null,null,null,null,null,null
null,null,Folder.Documentation,TABLE,null,null,null,null,null,null
null,null,Folder.DropBox_test,TABLE,null,null,null,null,null,null
null,null,Folder.DropBox_test/Fol1,TABLE,null,null,null,null,null,null
null,null,Folder.DropBox_test/Fol3,TABLE,null,null,null,null,null,null
null,null,Folder.Temp,TABLE,null,null,null,null,null,null
......

The getTables method returns the following metadata columns:

Columns

Use the getColumns method of the DatabaseMetaData interface to retrieve detailed information about database columns.

This method returns columns only for tables that are not write-only.

To get columns for write-only tables, query the table system.columns.

try {
    ResultSet rs = connection.getMetaData().getColumns(null, null, "Folder.Apps", null);
    LOGGER.info(rs.toString());
} catch (SQLException e) {
    e.printStackTrace();
}

TABLE_CAT,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,DATA_TYPE,TYPE_NAME,COLUMN_SIZE,BUFFER_LENGTH,DECIMAL_DIGITS,NUM_PREC_RADIX,NULLABLE,REMARKS,COLUMN_DEF,SQL_DATA_TYPE,SQL_DATETIME_SUB,CHAR_OCTET_LENGTH,ORDINAL_POSITION,IS_NULLABLE,SCOPE_CATALOG,SCOPE_SCHEMA,SCOPE_TABLE,SOURCE_DATA_TYPE,IS_AUTOINCREMENT,IS_GENERATEDCOLUMN,DTS_TYPE
null,null,Folder.Apps,.tag,12,VARCHAR,null,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
null,null,Folder.Apps,client_modified,93,TIMESTAMP,null,null,0,0,null,null,null,93,null,null,null,null,null,null,null,null,null,null,DT_DBTIMESTAMP
null,null,Folder.Apps,content_hash,12,VARCHAR,null,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
null,null,Folder.Apps,file_content,2004,BLOB,null,null,0,0,null,null,null,2004,null,null,null,null,null,null,null,null,null,null,DT_IMAGE
null,null,Folder.Apps,file_lock_info.created,93,TIMESTAMP,null,null,0,0,null,null,null,93,null,null,null,null,null,null,null,null,null,null,DT_DBTIMESTAMP
null,null,Folder.Apps,file_lock_info.is_lockholder,16,BOOLEAN,null,null,0,0,null,null,null,16,null,null,null,null,null,null,null,null,null,null,DT_BOOL
null,null,Folder.Apps,file_lock_info.lockholder_name,12,VARCHAR,null,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
null,null,Folder.Apps,has_explicit_shared_members,16,BOOLEAN,null,null,0,0,null,null,null,16,null,null,null,null,null,null,null,null,null,null,DT_BOOL
null,null,Folder.Apps,id,12,VARCHAR,null,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
......

The getColumns method returns the following columns:

Primary Keys

The getPrimaryKeys method in the DatabaseMetaData interface is used to retrieve metadata about primary keys for a given table in Dropbox.

try {
    ResultSet resultSet = connection.getMetaData().getPrimaryKeys(null, null, "Folder.Apps");
    LOGGER.info("\r\n" + resultSet.toString());
} catch (SQLException e) {
    LOGGER.severe(e.getMessage());
}

TABLE_CAT,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,KEY_SEQ,PK_NAME
null,null,Folder.Apps,path,1,null

The getPrimaryKeys method returns the following columns:

Connection Settings