如何通过ODBC API判断不同DBMS的ALTER TABLE语法支持？

Great question—building a cross-DBMS app with ODBC means relying on standard metadata APIs instead of hardcoding per-database logic, which is exactly what you're aiming for. Let's break down how to solve each of your requirements using ODBC's built-in functions:

ODBC's SQLGetInfo function can retrieve the syntax template for adding columns to a table via the SQL_ALTER_TABLE_ADD_COLUMN info type. This template will explicitly show whether parentheses are needed around column definitions.

How to implement: #

Call SQLGetInfo with SQL_ALTER_TABLE_ADD_COLUMN to get the DBMS-specific syntax. For example:

• SQL Server returns something like: ADD <column_definition> [,...] (no parentheses)
• Oracle returns: ADD (<column_definition> [,...]) (with parentheses)

You can parse the returned string to check for the presence of parentheses wrapping the column definition section.

Sample code snippet (C-style ODBC):

SQLCHAR syntaxBuf[1024];
SQLSMALLINT syntaxLen;
SQLRETURN ret = SQLGetInfo(hdbc, SQL_ALTER_TABLE_ADD_COLUMN, syntaxBuf, sizeof(syntaxBuf), &syntaxLen);

if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
    if (strstr((char*)syntaxBuf, "(") != NULL && strstr((char*)syntaxBuf, ")") != NULL) {
        // Parentheses are required for ADD clause
    } else {
        // No parentheses needed
    }
}

Again, use SQLGetInfo, but this time with the SQL_ALTER_TABLE_MODIFY_COLUMN info type. The returned syntax template will indicate if multiple column modifications are allowed in one statement.

How to implement: #

• For Oracle, the template will include [,...] inside the parentheses (e.g., MODIFY (<column_definition> [,...])), showing support for multiple columns.
• For SQL Server, the template will only allow a single column definition (e.g., ALTER COLUMN <column_definition>), with no comma-separated option.

Check if the syntax string includes the [,...] marker, which denotes multiple entries are permitted.

Sample code snippet:

SQLCHAR syntaxBuf[1024];
SQLSMALLINT syntaxLen;
SQLRETURN ret = SQLGetInfo(hdbc, SQL_ALTER_TABLE_MODIFY_COLUMN, syntaxBuf, sizeof(syntaxBuf), &syntaxLen);

if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
    if (strstr((char*)syntaxBuf, "[,...]") != NULL) {
        // Supports modifying multiple columns in one ALTER TABLE
    } else {
        // Requires separate ALTER TABLE statements for each column
    }
}

The same SQL_ALTER_TABLE_MODIFY_COLUMN syntax template from the previous step will start with the exact keyword(s) the DBMS uses for modifying columns.

How to implement: #

Parse the start of the returned syntax string to extract the keyword:

• SQL Server's template starts with ALTER COLUMN
• Oracle's template starts with MODIFY

Sample code snippet:

SQLCHAR syntaxBuf[1024];
SQLSMALLINT syntaxLen;
SQLRETURN ret = SQLGetInfo(hdbc, SQL_ALTER_TABLE_MODIFY_COLUMN, syntaxBuf, sizeof(syntaxBuf), &syntaxLen);

if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
    char* syntaxStr = (char*)syntaxBuf;
    if (strncmp(syntaxStr, "ALTER COLUMN", 12) == 0) {
        // Use ALTER COLUMN for modifying columns
    } else if (strncmp(syntaxStr, "MODIFY", 6) == 0) {
        // Use MODIFY for modifying columns
    }
}

Important Notes: #

• Some ODBC drivers might not fully support all these info types. If SQLGetInfo returns an error, you can fall back to checking the DBMS name (retrieved via SQLGetInfo with SQL_DBMS_NAME) as a secondary option, but prefer the syntax template method first for maximum compatibility.
• Always handle SQL_SUCCESS_WITH_INFO cases, as drivers might return warnings while still providing valid syntax data.

内容的提问来源于stack exchange，提问作者JKC