Facebook Business

Building the JDBC URL

After installing the license, access the connection management page by executing the command java -jar kingswaysoft.jdbc.jar. Enter the necessary details, and the program will automatically generate the JDBC connection URL. Users can click Test Connection to test the generated URL and Copy to Clipboard to copy the connection string for use within the application where the JDBC driver is being used.

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 authentication settings for Facebook Business.

Authentication Methods for Facebook Business

Advanced Settings Page

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

Using the JDBC Driver

Explore detailed examples in this section that demonstrate the application of JDBC classes such as Connection, Statement, and ResultSet to effectively manage interactions with Facebook Business data. This section covers regular statements and prepared statements for executing queries.

Note: Table names follow the driver's current metadata. Use collection tables such as AdAccount/campaigns to list or create objects, and object tables such as Campaign to read, update, or delete an individual object. Enclose table names containing / in square brackets in SQL. Pass the name without brackets to JDBC metadata methods or in table_name filters. Include the required path parameters in the WHERE clause for reads, updates, and deletes, or in the column list for inserts. For ad-account-scoped tables, ad_account_id can be supplied with or without the act_ prefix.

The IDs below are illustrative. Replace them with IDs accessible to your token, and use the IDs returned by INSERT operations in subsequent reads, updates, and deletes.

Executing Statements

Once you've connected from your code (see Connecting with DriverManager and Connecting with DataSource), you can execute SQL statements using the Statement class. Refer to the Executing Prepared Statements section for information on how to execute parameterized 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 id, name FROM AdAccount/campaigns WHERE ad_account_id = '493444904779121'";
try {
    ResultSet resultSet = statement.executeQuery(sql);
    LOGGER.info(resultSet.toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

String sql = "SELECT account_id, campaign_id, impressions, spend "
           + "FROM AdAccount/insights WHERE ad_account_id = 'act_493444904779121' "
           + "AND level = 'campaign' AND time_increment = '1'";
try {
    ResultSet resultSet = statement.executeQuery(sql);
    LOGGER.info(resultSet.toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

USING OPTIONS for Access Tokens

Append USING OPTIONS (...) to a SQL statement to select an App Access Token or Page Access Token for that statement. These options apply to SELECT (including pagination), INSERT, UPDATE, DELETE, and batch writes. They do not replace the connection's original access token. Omitting both token options, or setting both to false, uses the connection's normal authentication.

UseAppToken and UsePageToken cannot both be true; enabling both raises an error. The selected Facebook endpoint must accept the chosen token type, and the token must have the permissions required for the operation. If UseAppSecretProof is enabled, the driver calculates the proof using the token selected for the request.

Use Page Token with USING OPTIONS

Set UsePageToken=true and supply PageId. The driver uses the connection's OAuth user token or System User token to retrieve a Page Access Token for that Page. The authenticated user or System User must have access to the Page and the required Page permissions.

SELECT id FROM Page WHERE page_id = 'me'
USING OPTIONS (UsePageToken=true, PageId='987654321')

Replace 987654321 with your Page ID. In this example, me resolves to the selected Page, so the returned id should match PageId. PageId selects the authentication token; any endpoint parameters such as page_id still belong in the SQL statement.

For an OAuth connection, you can list the Pages available to the current user before selecting a Page ID:

SELECT id, nameFROMUser/accounts WHEREuser_id = 'me'
USING OPTIONS (UsePageToken=false)

Supply PageId to select the intended Page. With a nonblank PageId, the driver retrieves the token directly from /{page-id}?fields=access_token. If PageId is omitted or blank, the driver looks up the current user and uses the first Page token returned by that user's /accounts endpoint; this fallback only checks the first response page. Token lookup raises an error if no Page token is returned.

On an OAuth connection, the following statement uses the original user token again:

SELECT id FROMUserWHEREuser_id = 'me'
USING OPTIONS (UsePageToken=false)

Use App Token with USING OPTIONS

Set UseAppToken=true to use an App Access Token. This option is available only with OAuth Authorization Code authentication. The driver uses the OAuth App ID and App Secret, loading them from the encrypted OAuth token file when necessary. Configure the token file path and password as usual; you do not need to supply an App Access Token in the SQL statement.

SELECT id, nameFROM Application WHERE application_id = '123456789'
USING OPTIONS (UseAppToken=true)

Replace 123456789 with your OAuth App ID. Enabling UseAppToken outside OAuth mode or omitting the required app credentials raises an error. Omit UseAppToken or set it to false on subsequent statements to use the connection's normal authentication.

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 AdAccount/campaigns (ad_account_id, name, objective, buying_type, bid_strategy, daily_budget, status, special_ad_categories) "
           + "VALUES ('act_493444904779121', 'jdbc-crud-campaign', 'OUTCOME_TRAFFIC', 'AUCTION', "
           + "'LOWEST_COST_WITHOUT_CAP', 500, 'PAUSED', '[]')";
try {
    int result = statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
120000000000000,null,null,{"name":"jdbc-crud-campaign","objective":"OUTCOME_TRAFFIC","buying_type":"AUCTION","bid_strategy":"LOWEST_COST_WITHOUT_CAP","daily_budget":500,"status":"PAUSED","special_ad_categories":"[]"},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.

String sql = "UPDATE Campaign SET name = 'jdbc-crud-campaign-updated', status = 'PAUSED' "
           + "WHERE campaign_id = '120000000000000'";
try {
    int result = statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
120000000000000,null,null,{"name":"jdbc-crud-campaign-updated","status":"PAUSED"},false

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.

String sql = "DELETE FROM Campaign WHERE campaign_id = '120000000000000'";
try {
    int result = statement.executeUpdate(sql);
    LOGGER.info(statement.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

id,errorcode,errormessage,processdata,haserrors
120000000000000,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.

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 id, name FROM Campaign WHERE campaign_id = ?";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setInt(1, 1);
    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.

String sql = "INSERT INTO AdAccount/adsets (ad_account_id, name, campaign_id, billing_event, optimization_goal, destination_type, targeting, status) "
        + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "act_493444904779121");
    ps.setString(2, "jdbc-ps-adset");
    ps.setString(3, "120000000000000");
    ps.setString(4, "IMPRESSIONS");
    ps.setString(5, "LINK_CLICKS");
    ps.setString(6, "WEBSITE");
    ps.setString(7, "{\"geo_locations\":{\"countries\":[\"US\"]}}");
    ps.setString(8, "PAUSED");
    ps.executeUpdate();
    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

UPDATE

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

String sql = "UPDATE AdSet SET name = ?, status = ? WHERE ad_set_id = ?";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "jdbc-ps-adset-updated");
    ps.setString(2, "PAUSED");
    ps.setString(3, "120000000000001");
    ps.executeUpdate();
    LOGGER.info(ps.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.

String sql = "DELETE FROM AdSet WHERE ad_set_id = ?";
try {
    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1, "120000000000001");
    ps.executeUpdate();
    LOGGER.info(ps.getResultSet().toString());
} catch (SQLException e) {
    LOGGER.severe(e.toString());
}

Batch Writes

Multi-row INSERT statements, UPDATE/DELETE statements with multiple keys, and PreparedStatement.addBatch() / executeBatch() use Graph API batch requests when BatchSizeForWriting is greater than 1. USING OPTIONS (BatchSize=25) overrides the connection setting. Each HTTP batch contains at most 50 requests, even when a larger size is configured.

Each subresponse is mapped to its input row, including returned IDs and errors. Prepared statement batches return an update count for each parameter set, or Statement.EXECUTE_FAILED for a failed entry. With ContinueOnErrors=false, the driver retains all results from the submitted batch and stops before sending the next batch; successful writes in that batch are not rolled back.

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 which include write-only tables, query the table system.tables.

try {
    Connection connection = buildRestConnectionFromDriverManager();
    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
FacebookBusiness,FacebookBusiness,ALMAdAccountInfo,TABLE,null,null,null,null,null,null
FacebookBusiness,FacebookBusiness,ALMEndAdvertiserInfo,TABLE,null,null,null,null,null,null
FacebookBusiness,FacebookBusiness,ALMEvent,TABLE,null,null,null,null,null,null
FacebookBusiness,FacebookBusiness,AREffect,TABLE,null,null,null,null,null,null
FacebookBusiness,FacebookBusiness,Ad,TABLE,null,null,null,null,null,null
FacebookBusiness,FacebookBusiness,Ad/adcreatives,TABLE,null,null,null,null,null,null
FacebookBusiness,FacebookBusiness,Ad/adrules_governed,TABLE,null,null,null,null,null,null
FacebookBusiness,FacebookBusiness,Ad/copies,TABLE,null,null,null,null,null,null
FacebookBusiness,FacebookBusiness,Ad/insights,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 tables which are write-only or operation-specific parameters, query the table system.columns.

try {
    Connection connection = buildRestConnectionFromDriverManager();
    ResultSet rs = connection.getMetaData().getColumns(null, null, "Ad/insights", null);
    LOGGER.info(rs.toString());
} catch (SQLException e) {
    e.printStackTrace();
}

INFO: 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
FacebookBusiness,FacebookBusiness,Ad/insights,account_currency,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,account_id,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,account_name,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,action_values,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,actions,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,ad_click_actions,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,ad_id,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,ad_impression_actions,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,ad_name,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,adset_end,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,adset_id,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,adset_name,12,VARCHAR,255,null,0,0,null,null,null,12,null,null,null,null,null,null,null,null,null,null,DT_WSTR
FacebookBusiness,FacebookBusiness,Ad/insights,adset_start,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 Facebook Business.

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

INFO: TABLE_CAT,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,KEY_SEQ,PK_NAME
null,null,AdAccount/campaigns,id,1,null

The getPrimaryKeys method returns the following columns:

Connection Settings