Azure Blob Storage

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 the storage endpoint and login credentials for the Azure Blob Storage account.

Authentication Methods for Azure Blob Storage

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 Azure Blob Storage. It covers regular statements and prepared statements for complex or frequently executed queries.

Paths

Blob locations are written as container/blob-name. Backslashes are treated as slashes, and a leading slash is ignored.

When the connection Container property is empty, the first path segment is the container name and the remainder is the blob name. A path with no slash refers to the container itself.

When Container is set, paths are inside that container. If the path starts with the configured container name, that prefix is removed. Otherwise the whole path is the blob name.

When Start Path is also set, paths are relative to that prefix inside the container. A path that already begins with the prefix is left unchanged. A path that does not is placed under the prefix. A SELECT from Files, Folders, or Items can omit path and lists that prefix. A path in the query overrides it. When Start Path is blank, path stays required on those tables.

-- List items in the receipts folder of the documents containerSELECT * FROM Items WHERE path = 'documents/receipts'-- The same folder when the connection Container is documentsSELECT * FROM Items WHERE path = 'receipts'-- Read one file from a folder table. The path is relative to that folder.SELECTName, [Content-Length] FROM [Folder.documents/receipts] WHERE path = 'invoice.txt'

On a folder table such as Folder.documents or Folder.documents/receipts, a path that does not start with / is relative to that folder. A path that starts with / must be that folder or one of its descendants.

Tables and Functions

The driver exposes containers, blobs, and virtual folders as tables.

Containers

Use the Containers table to list, create, update, and delete containers. Create, update, and delete of containers is supported only on this table.

A container name is 3 to 63 characters, lowercase letters, numbers, and hyphens, and cannot start or end with a hyphen or contain two hyphens in a row.

PublicAccess accepts blob, container, or private. It can be set only on Containers.

Files

Use the Files table to read and write blobs. A SELECT requires path unless Start Path is set, in which case the query lists that folder. A path in the query overrides Start Path. Reads return files only.

FileContent is the blob bytes. Provide it as a Base64 string on write. A value that is not valid Base64 is stored as UTF-8 bytes. SELECT * does not return FileContent. Select that column explicitly when you need the bytes.

x-ms-blob-type accepts BlockBlob (the default), AppendBlob, and PageBlob. PageBlob is not supported on an account that has hierarchical namespace enabled. The blob type can be set on insert and cannot be changed later.

Updating FileContent replaces a block blob or page blob. Updating FileContent on an append blob appends the new bytes. Content-Type and Metadata can be set on insert and update. Metadata is a JSON object, for example {"env":"test"}.

Folders

Azure Blob Storage has no directory resource. A folder exists only as the prefix of a blob name, so an empty folder cannot be created. Upload a blob under that prefix instead. Use the Folders table to list those prefixes. A SELECT requires path unless Start Path is set, in which case the query lists that folder. A path in the query overrides Start Path. The query returns folders only. INSERT and UPSERT into Folders are rejected.

Folders cannot be updated. Use CALL Copy or CALL Move to relocate a folder that contains blobs. Deleting a folder deletes every blob under that prefix.

Items

Use the Items table to work with both files and folders. A SELECT requires path unless Start Path is set, in which case the query lists that folder. A path in the query overrides Start Path. Set _isFolder to true when deleting a folder. Inserting or upserting a folder is rejected, including a row with _isFolder set to true or a row that includes FileContent, Content-Type, Metadata, or x-ms-blob-type for a folder. Provide either path or Name as the location, not two different locations.

Folders as Tables

Each discovered container, and each child folder when recursive metadata is enabled, is also a table named Folder.{path}. For example, the container documents is Folder.documents, and a child folder is Folder.documents/receipts. When Start Path is receipts in container documents, discovery lists child folders such as Folder.documents/receipts/2024 and does not expose Folder.documents/receipts. Insert a file with Name and FileContent. Creating a folder on a Folder.* table is rejected.

Copy and Move

Copy and Move are functions, not tables. Query system.functions and system.functions.columns for their parameters. They copy or move a blob, or every blob under a folder prefix. A prefix with no blobs is rejected. Containers cannot be copied or moved, and the destination cannot be a container root.

Query Level Options

Append USING OPTIONS (option=value) to a statement. The clause is optional.

For SELECT, SelectionMode controls which items are returned. Valid values are Selected Item, Recursive, Selected Level, Recursive (Files Only), and Selected Level (Files Only). The default is Recursive. Files Only modes are not valid on the Folders table. Recursive reads honor Max Folder Depth.

List filters can be set in the WHERE clause: Include_Snapshots, Include_Copy, and Include_Uncommitted_Blobs.

For CALL Copy and CALL Move, OverwriteMode (also accepted as FileOverwriteMode) controls an existing destination. Valid values are Overwrite, SkipExisting, and RaiseError. The default is Overwrite. SkipExisting leaves the destination in place and does not count the row. OverwriteMode is rejected on INSERT, UPDATE, DELETE, and UPSERT.

Write batching is not supported. BatchSizeForWriting must remain 1.

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 Name, path, [Content-Length], [x-ms-blob-type] FROM Files "
        + "WHERE path = 'documents/receipts' USING OPTIONS (SelectionMode='Selected Level')";
try {
    ResultSet resultSet = statement.executeQuery(sql);
    LOGGER.info(resultSet.toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

String sql = "SELECT path, FileContent FROM Files WHERE path = 'documents/receipts/invoice.txt' "
        + "USING OPTIONS (SelectionMode='Selected Item')";
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 affected path, exceptions raised during execution, and details of the affected data.

An insert fails when the blob or container already exists. Use UPSERT to create or replace it.

String sql = "INSERT INTO Files (path, FileContent, [Content-Type], Metadata) "
        + "VALUES ('documents/notes.txt', 'aGVsbG8=', 'text/plain', '{\"env\":\"test\"}')";
try {
    statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
documents/notes.txt,null,null,null,false

String fileSql = "INSERT INTO Files (path, FileContent) VALUES ('documents/receipts/invoice.txt', 'aGVsbG8=')";
String containerSql = "INSERT INTO Containers (Name, PublicAccess) VALUES ('documents', 'private')";

UPDATE

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

path and Name identify the row and cannot be changed. A container cannot be renamed. x-ms-blob-type cannot be changed. The Folders table does not support update.

Updating a container sets PublicAccess. Updating a file can set FileContent, Content-Type, and Metadata.

String sql = "UPDATE Files SET FileContent = 'dXBkYXRlZA==', [Content-Type] = 'text/plain' "
        + "WHERE path = 'documents/notes.txt'";
try {
    statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
documents/notes.txt,null,null,null,false

CALL

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

Provide from_path and either to_path, or PathToParent together with an optional name. to_path cannot be combined with PathToParent or name. When name is omitted, the source name is kept. The source and destination must be different, and a folder cannot be moved into itself.

String sql = "CALL Copy (from_path, to_path) VALUES ('documents/notes.txt', 'documents/archive/notes.txt') "
        + "USING OPTIONS (OverwriteMode='RaiseError')";
try {
    statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
documents/archive/notes.txt,null,null,null,false

String sql = "CALL Move (from_path, PathToParent, name) "
        + "VALUES ('documents/receipts', 'documents/archive', 'receipts')";
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.

Deleting a folder removes the blobs stored under that prefix. Deleting a container removes the container. Identify a container by Name or path.

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

id,errorcode,errormessage,processdata,haserrors
documents/notes.txt,null,null,null,false

UPSERT

Using the UPSERT operation, you can either insert or update an existing record in one call. Containers, Files, Items, and Folder.* tables support upsert of containers and blobs. Upserting a folder is rejected.

Upserting an existing append blob keeps the append blob type and appends FileContent. Upserting an existing block blob replaces it.

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

String sql = "UPSERT INTO Files (path, FileContent) VALUES ('documents/notes.txt', 'dXBzZXJ0')";
try {
    statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors,isnew
documents/notes.txt,null,null,null,false,false

UPSERT results include an isnew column. The driver does not set it from the Azure response, so do not use it to tell a create apart from a replace.

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.

String sql = "SELECT * FROM Containers WHERE Name = ?";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "documents");
    boolean ret = ps.execute();
    if (ret) {
        ResultSet rs = ps.getResultSet();
        LOGGER.info(rs.toString());
    }
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

INSERT

String sql = "INSERT INTO Files (path, FileContent) VALUES (?, ?)";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "documents/notes.txt");
    ps.setString(2, "aGVsbG8=");
    ps.executeUpdate();
    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
jdbcc24148a7/documents/notes.txt,null,null,null,false

UPDATE

String sql = "UPDATE Containers SET PublicAccess = ? WHERE Name = ?";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "blob");
    ps.setString(2, "documents");
    ps.executeUpdate();
    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
documents,null,null,null,false

CALL

String sql = "CALL Copy (from_path, to_path) VALUES (?, ?)";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "jdbc-append-695ea595.txt");
    ps.setString(2, "jdbc-folder-520bc1bd/notes.txt");
    ps.executeUpdate();
    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
jdbcc24148a7/jdbc-folder-520bc1bd/notes.txt,null,null,null,false

DELETE

String sql = "DELETE FROM Folders WHERE path = ?";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "documents/receipts");
    ps.executeUpdate();
    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
documents/receipts,null,null,null,false

UPSERT

String sql = "UPSERT INTO Files (path, FileContent, [x-ms-blob-type]) VALUES (?, ?, ?)";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "documents/log.txt");
    ps.setString(2, "b25l");
    ps.setString(3, "AppendBlob");
    ps.executeUpdate();
    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors,isnew
jdbcc24148a7/documents/log.txt,null,null,null,false,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.

Containers, Files, Folders, and Items are always available, together with the system tables. Folder.{container} tables are added for each container the credentials can list. Child folder tables are added when Recursive Metadata is enabled, or when that folder table is queried. When Start Path names a container, Folder.{container} and its child folders are added. When it names a folder prefix, folder tables are added only under that prefix, and the start prefix itself is not a table. Copy and Move are functions and are listed by system.functions.

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
AzureBlobStorage,AzureBlobStorage,Containers,TABLE,null,null,null,null,null,null
AzureBlobStorage,AzureBlobStorage,Files,TABLE,null,null,null,null,null,null
AzureBlobStorage,AzureBlobStorage,Folder.jdbcc24148a7,TABLE,null,null,null,null,null,null
AzureBlobStorage,AzureBlobStorage,Folders,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 columns. Containers columns include Name, path, PublicAccess, LastModified, ETag, LeaseStatus, LeaseState, LeaseDuration, HasImmutabilityPolicy, HasLegalHold, DefaultEncryptionScope, DenyEncryptionScopeOverride, ImmutableStorageWithVersioningEnabled, and Metadata. File, folder, item, and Folder.* tables share the current Blob property columns, including path, Name, FileContent, Content-Type, Content-Language, Content-Length, Metadata, x-ms-blob-type, x-ms-access-tier, x-ms-smart-access-tier, x-ms-encryption-scope, and _isFolder. The Folders table does not include FileContent.

try {
    ResultSet rs = connection.getMetaData().getColumns(null, null, "Containers", 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
AzureBlobStorage,AzureBlobStorage,Containers,ETag,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
AzureBlobStorage,AzureBlobStorage,Containers,LastModified,93,TIMESTAMP,29,null,0,0,null,null,null,93,null,null,null,null,null,null,null,null,null,null,DT_DBTIMESTAMP
AzureBlobStorage,AzureBlobStorage,Containers,Name,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
AzureBlobStorage,AzureBlobStorage,Containers,path,12,VARCHAR,255,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 Azure Blob Storage.

Containers uses Name. Files, Folders, Items, and Folder.* tables use path.

try {
    ResultSet resultSet = connection.getMetaData().getPrimaryKeys(null, null, "Files");
    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,Files,path,1,null

The getPrimaryKeys method returns the following columns:

Connection Settings