OceanBase Connector/ODBC in Oracle mode supports the escape of the IFNULL, SUBSTRING, and LEFT scalar functions.
IFNULL function
The syntax of the IFNULL function is as follows:
{fn IFNULL(exp1, exp2) }
The IFNULL function first checks whether exp1 is NULL. If it is not NULL, it returns exp1. Otherwise, it returns exp2.
In the following example, if exp1 is NULL, the result is exp2, which is "aaa".
SELECT {fn IFNULL(NULL, 'aaa')} FROM DUAL;
In the following example, if table.a is NULL, the result is b. If table.a is not NULL, the result is a.
SELECT {fn IFNULL(a, b) } FROM TABLE;
LEFT function
The syntax of the LEFT function is as follows:
{fn LEFT (string_exp, n)}
The LEFT function extracts a string of length n from the beginning of string_exp.
Here is an example:
SELECT {fn LEFT('helloword', 5)} FROM DUAL; -- The result is hello
SUBSTRING function
The syntax of the SUBSTRING function is as follows:
{fn SUBSTRING (string_exp, start, length**) }
The SUBSTRING function extracts a substring from string_exp, starting at position start and of length length. In this function, start=0 or start=1 both indicate the beginning of string_exp. The length parameter is optional. If it is not specified, the length extends to the end of string_exp.
Here are some examples:
SELECT {fn SUBSTRING('helloword', 5, 4)} FROM DUAL; -- The result is owor
SELECT {fn SUBSTRING('helloword', 5)} FROM DUAL; -- The result is oword
SELECT {fn SUBSTRING('helloword', 1)} FROM DUAL; -- The result is helloword
SELECT {fn SUBSTRING('helloword', 0)} FROM DUAL; -- The result is helloword
Comprehensive example
ODBC_TEST(test_ifnull)
{
SQLRETURN ret = 0;
SQLSMALLINT cnt;
char buf[1024] = { 0 };
char buf1[1024] = { 0 };
SQLCHAR * drop_sql = (SQLCHAR *)"DROP TABLE type_number";
SQLExecDirect(Stmt, (SQLCHAR*)drop_sql, (SQLINTEGER)strlen((char*)drop_sql));
OK_SIMPLE_STMT(Stmt, "CREATE TABLE type_number(a varchar2(100), b varchar2(100))");
OK_SIMPLE_STMT(Stmt, "insert into type_number values(NULL, 'my name is a')");
OK_SIMPLE_STMT(Stmt, "select {FN IFNULL(NULL, 100)} from dual");
CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
printf("%s\n", buf);
OK_SIMPLE_STMT(Stmt, "select {FN LEFT('helloword', 1)} from dual");
CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
printf("%s\n", buf);
OK_SIMPLE_STMT(Stmt, "select {FN SUBSTRING ('helloword', 5, 4)} from dual");
CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
printf("%s\n", buf);
OK_SIMPLE_STMT(Stmt, "select {FN SUBSTRING ('helloword', 5)} from dual");
CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
printf("%s\n", buf);
OK_SIMPLE_STMT(Stmt, "select {FN LEFT(b, 5)},b from type_number");
CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 2, SQL_C_CHAR, buf1, 1024, NULL));
CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
printf("%s,%s\n", buf, buf1);
OK_SIMPLE_STMT(Stmt, "select {FN IFNULL(a, 5)},b from type_number");
CHECK_STMT_RC(Stmt, ret = SQLNumResultCols(Stmt, &cnt));
CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 1, SQL_C_CHAR, buf, 1024, NULL));
CHECK_STMT_RC(Stmt, ret = SQLBindCol(Stmt, 2, SQL_C_CHAR, buf1, 1024, NULL));
CHECK_STMT_RC(Stmt, ret = SQLFetch(Stmt));
CHECK_STMT_RC(Stmt, ret = SQLFreeStmt(Stmt, SQL_CLOSE));
printf("%s,%s\n", buf, buf1);
return OK;
}