Changeset: b44f304fcace for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/b44f304fcace Modified Files: clients/odbc/ChangeLog clients/odbc/driver/SQLProcedureColumns.c clients/odbc/driver/SQLProcedures.c clients/odbc/samples/metadata.c Branch: default Log Message:
Improved SQLProcedures() and SQLProcedureColumns(). They now list information also for all built-in system procedures and functions, not only those created via SQL. Also corrected the value of ORDINAL_POSITION for scalar function arguments. It would start at 2 instead of 1. Extended output of SQLProcedures() and SQLProcedureColumns() resultsets with an extra column SPECIFIC_NAME. This column contains the name which uniquely identifies this procedure or function within its schema. As MonetDB supports overloaded procedures and functions, the combination of PROCEDURE_SCHEM and PROCEDURE_NAME is not enough to uniquely identify a procedure or function. This extra column allows you to correctly match the corresponding rows returned by SQLProcedureColumns() with the specific rows of SQLProcedures(). This extra column SPECIFIC_NAME is implemented similar to the JDBC DatabaseMetaData methods getProcedures() and getProcedureColumns(). diffs (truncated from 396 to 300 lines): diff --git a/clients/odbc/ChangeLog b/clients/odbc/ChangeLog --- a/clients/odbc/ChangeLog +++ b/clients/odbc/ChangeLog @@ -1,6 +1,22 @@ # ChangeLog file for odbc # This file is updated with Maddlog +* Wed Jun 22 2022 Martin van Dinther <[email protected]> +- Improved SQLProcedures() and SQLProcedureColumns(). They now list + information also for all built-in system procedures and functions, not + only those created via SQL. Also corrected the value of ORDINAL_POSITION + for scalar function arguments. It would start at 2 instead of 1. +- Extended output of SQLProcedures() and SQLProcedureColumns() resultsets + with an extra column SPECIFIC_NAME. This column contains the name which + uniquely identifies this procedure or function within its schema. As + MonetDB supports overloaded procedures and functions, the combination of + PROCEDURE_SCHEM and PROCEDURE_NAME is not enough to uniquely identify + a procedure or function. This extra column allows you to correctly + match the corresponding rows returned by SQLProcedureColumns() with the + specific rows of SQLProcedures(). This extra column SPECIFIC_NAME is + implemented similar to the JDBC DatabaseMetaData methods getProcedures() + and getProcedureColumns(). + * Thu Jun 9 2022 Martin van Dinther <[email protected]> - For SQLForeignKeys() corrected the output of columns UPDATE_RULE and DELETE_RULE. These columns used to always return 3 (= SQL_NO_ACTION) diff --git a/clients/odbc/driver/SQLProcedureColumns.c b/clients/odbc/driver/SQLProcedureColumns.c --- a/clients/odbc/driver/SQLProcedureColumns.c +++ b/clients/odbc/driver/SQLProcedureColumns.c @@ -141,11 +141,14 @@ MNDBProcedureColumns(ODBCStmt *stmt, INTEGER CHAR_OCTET_LENGTH INTEGER ORDINAL_POSITION NOT NULL VARCHAR IS_NULLABLE + VARCHAR SPECIFIC_NAME (Note this is a MonetDB extension, needed to differentiate between overloaded procedure/function names) + (similar to JDBC DatabaseMetaData methods getProcedureColumns() and getFunctionColumns()) */ -/* see sql_catalog.h */ +/* see sql/include/sql_catalog.h */ +#define F_FUNC 1 +#define F_PROC 2 #define F_UNION 5 -#define FUNC_LANG_SQL 2 pos += snprintf(query + pos, querylen - pos, "select '%s' as \"PROCEDURE_CAT\", " "s.name as \"PROCEDURE_SCHEM\", " @@ -169,16 +172,21 @@ MNDBProcedureColumns(ODBCStmt *stmt, CHAR_OCTET_LENGTH(a) ", " "case when p.type = 5 and a.inout = 0 then a.number + 1 " "when p.type = 5 and a.inout = 1 then a.number - x.maxout " + "when p.type = 2 and a.inout = 1 then a.number + 1 " "when a.inout = 0 then 0 " - "else a.number + 1 " + "else a.number " "end as \"ORDINAL_POSITION\", " - "'' as \"IS_NULLABLE\" " + "'' as \"IS_NULLABLE\", " + /* Only the id value uniquely identifies a specific procedure. + Include it to be able to differentiate between multiple + overloaded procedures with the same name and schema */ + "cast(p.id as varchar(10)) AS \"SPECIFIC_NAME\" " "from sys.schemas s, " "sys.functions p left outer join (select func_id, max(number) as maxout from sys.args where inout = 0 group by func_id) as x on p.id = x.func_id, " "sys.args a%s " - "where p.language >= %d and " - "s.id = p.schema_id and " - "p.id = a.func_id", + "where s.id = p.schema_id and " + "p.id = a.func_id and " + "p.type in (%d, %d, %d)", stmt->Dbc->dbname, /* column_type: */ SQL_PARAM_INPUT, F_UNION, SQL_RESULT_COL, SQL_RETURN_VALUE, @@ -215,8 +223,9 @@ MNDBProcedureColumns(ODBCStmt *stmt, #endif /* from clause: */ stmt->Dbc->has_comment ? " left outer join sys.comments c on c.id = a.id" : "", - FUNC_LANG_SQL); - assert(pos < 6300); + /* where clause: */ + F_FUNC, F_PROC, F_UNION); + assert(pos < 6400); /* depending on the input parameter values we must add a variable selection condition dynamically */ @@ -246,7 +255,9 @@ MNDBProcedureColumns(ODBCStmt *stmt, } /* add the ordering (exclude procedure_cat as it is the same for all rows) */ - pos += strcpy_len(query + pos, " order by \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"COLUMN_TYPE\", \"ORDINAL_POSITION\"", querylen - pos); + pos += strcpy_len(query + pos, " order by \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\", \"COLUMN_TYPE\", \"ORDINAL_POSITION\"", querylen - pos); + + /* debug: fprintf(stdout, "SQLProcedureColumns SQL (%zu):\n%s\n\n", pos, query); */ /* query the MonetDB data dictionary tables */ rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos); diff --git a/clients/odbc/driver/SQLProcedures.c b/clients/odbc/driver/SQLProcedures.c --- a/clients/odbc/driver/SQLProcedures.c +++ b/clients/odbc/driver/SQLProcedures.c @@ -71,6 +71,8 @@ MNDBProcedures(ODBCStmt *stmt, N/A NUM_RESULT_SETS (Reserved for future use) VARCHAR REMARKS SMALLINT PROCEDURE_TYPE + VARCHAR SPECIFIC_NAME (Note this is a MonetDB extension, needed to differentiate between overloaded procedure/function names) + (similar to JDBC DatabaseMetaData methods getProcedures() and getFunctions()) */ if (stmt->Dbc->sql_attr_metadata_id == SQL_FALSE) { @@ -111,31 +113,35 @@ MNDBProcedures(ODBCStmt *stmt, if (query == NULL) goto nomem; -/* see sql_catalog.h */ +/* see sql/include/sql_catalog.h */ #define F_FUNC 1 #define F_PROC 2 #define F_UNION 5 -#define FUNC_LANG_SQL 2 pos += snprintf(query + pos, querylen - pos, - "select '%s' as \"PROCEDURE_CAT\", " + "select '%s' as \"PROCEDURE_CAT\", " "s.name as \"PROCEDURE_SCHEM\", " "p.name as \"PROCEDURE_NAME\", " "0 as \"NUM_INPUT_PARAMS\", " "0 as \"NUM_OUTPUT_PARAMS\", " "0 as \"NUM_RESULT_SETS\", " "%s as \"REMARKS\", " - "cast(case when p.type = %d then %d else %d end as smallint) as \"PROCEDURE_TYPE\" " - "from sys.schemas as s, " - "sys.functions as p%s " - "where p.schema_id = s.id and " - "p.language >= %d and " - "p.type in (%d, %d, %d)", - stmt->Dbc->dbname, - stmt->Dbc->has_comment ? "c.remark" : "cast(null as varchar(1))", - F_PROC, SQL_PT_PROCEDURE, SQL_PT_FUNCTION, - stmt->Dbc->has_comment ? " left outer join sys.comments c on p.id = c.id" : "", - FUNC_LANG_SQL, F_FUNC, F_PROC, F_UNION); - assert(pos < 800); + "cast(case when p.type = %d then %d else %d end as smallint) as \"PROCEDURE_TYPE\", " + /* Only the id value uniquely identifies a specific procedure. + Include it to be able to differentiate between multiple + overloaded procedures with the same name and schema */ + "cast(p.id as varchar(10)) AS \"SPECIFIC_NAME\" " + "from sys.schemas as s, " + "sys.functions as p%s " + "where p.schema_id = s.id and " + "p.type in (%d, %d, %d)", + stmt->Dbc->dbname, + stmt->Dbc->has_comment ? "c.remark" : "cast(null as varchar(1))", + F_PROC, SQL_PT_PROCEDURE, SQL_PT_FUNCTION, + /* from clause: */ + stmt->Dbc->has_comment ? " left outer join sys.comments c on c.id = p.id" : "", + /* where clause: */ + F_FUNC, F_PROC, F_UNION); + assert(pos < 900); /* Construct the selection condition query part */ if (NameLength1 > 0 && CatalogName != NULL) { @@ -157,7 +163,9 @@ MNDBProcedures(ODBCStmt *stmt, } /* add the ordering (exclude procedure_cat as it is the same for all rows) */ - pos += strcpy_len(query + pos, " order by \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\"", querylen - pos); + pos += strcpy_len(query + pos, " order by \"PROCEDURE_SCHEM\", \"PROCEDURE_NAME\", \"SPECIFIC_NAME\"", querylen - pos); + + /* debug: fprintf(stdout, "SQLProcedures SQL (%zu):\n%s\n\n", pos, query); */ /* query the MonetDB data dictionary tables */ rc = MNDBExecDirect(stmt, (SQLCHAR *) query, (SQLINTEGER) pos); diff --git a/clients/odbc/samples/metadata.c b/clients/odbc/samples/metadata.c --- a/clients/odbc/samples/metadata.c +++ b/clients/odbc/samples/metadata.c @@ -81,12 +81,13 @@ compareResult(SQLHANDLE stmt, SQLRETURN SQLRETURN ret; SQLSMALLINT columns; // Number of columns in result-set size_t expct_len = strlen(expected); - size_t outp_len = expct_len + 1000; + size_t outp_len = expct_len + 10000; char * outp = malloc(outp_len); size_t pos = 0; SQLUSMALLINT col; SQLLEN indicator; char buf[2048]; + int replaceId = 0; /* used to replace system id values in column SPECIFIC_NAME of getProcedures and getProcedureColumns */ if (outp == NULL) { fprintf(stderr, "Failed to allocate %zu memory!\n", outp_len); @@ -110,6 +111,17 @@ compareResult(SQLHANDLE stmt, SQLRETURN } pos += snprintf(outp + pos, outp_len - pos, "\n"); + /* detect if special handling of data of column SPECIFIC_NAME returned by SQLProcedures and SQLProcedureColumns + is needed as it contains system generated id values which can differ per version and platform */ + if (columns == 9 || columns == 20) { + /* this result could be from SQLProcedures or SQLProcedureColumns */ + if ((strncmp("SQLProcedures", functionname, 13) == 0) + || (strncmp("SQLProcedureColumns", functionname, 19) == 0)) { + if (strncmp("SPECIFIC_NAME", buf, 13) == 0) + replaceId = 1; + } + } + /* Loop through the rows in the result-set */ ret = SQLFetch(stmt); check(ret, SQL_HANDLE_STMT, stmt, "SQLFetch(1)"); @@ -120,10 +132,16 @@ compareResult(SQLHANDLE stmt, SQLRETURN ret = SQLGetData(stmt, col, SQL_C_CHAR, buf, sizeof(buf), &indicator); check(ret, SQL_HANDLE_STMT, stmt, "SQLGetData()"); if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) { - pos += snprintf(outp + pos, outp_len - pos, - (col > 1) ? "\t%s" : "%s", - // Handle null columns - (indicator == SQL_NULL_DATA) ? "NULL" : buf); + // Check if we need to replace the system id values to get stable output + if (replaceId == 0 || + (replaceId == 1 && col < columns)) { + pos += snprintf(outp + pos, outp_len - pos, + (col > 1) ? "\t%s" : "%s", + // Handle null columns + (indicator == SQL_NULL_DATA) ? "NULL" : buf); + } else { + pos += snprintf(outp + pos, outp_len - pos, "\treplacedId"); + } } } pos += snprintf(outp + pos, outp_len - pos, "\n"); @@ -224,6 +242,8 @@ main(int argc, char **argv) , SQL_NTS); check(ret, SQL_HANDLE_DBC, dbc, "SQLExecDirect (add privileges script)"); + // TODO add user procedures / functions to test SQLProcedures() and SQLProcedureColumns() more + /* run actual metadata query tests */ // All catalogs query ret = SQLTables(stmt, (SQLCHAR*)SQL_ALL_CATALOGS, SQL_NTS, @@ -665,19 +685,150 @@ main(int argc, char **argv) "mTests_sql_odbc_samples odbctst pk2c pkc2 mTests_sql_odbc_samples odbctst fk2c fkc2 1 2 3 fk2c_fkc2_fkc3_fkey pk2c_pkc2_pkc1_pkey 7\n" "mTests_sql_odbc_samples odbctst pk2c pkc1 mTests_sql_odbc_samples odbctst fk2c fkc3 2 2 3 fk2c_fkc2_fkc3_fkey pk2c_pkc2_pkc1_pkey 7\n"); - // TODO add tables with procedures such that below calls also return data rows + // test procedure sys.analyze(). There are 4 overloaded variants of this procedure in MonetDB with 0, 1, 2 or 3 input parameters. + ret = SQLProcedures(stmt, (SQLCHAR*)"", SQL_NTS, + (SQLCHAR*)"sys", SQL_NTS, (SQLCHAR*)"analyze", SQL_NTS); + compareResult(stmt, ret, "SQLProcedures (sys, analyze)", + "Resultset with 9 columns\n" + "PROCEDURE_CAT PROCEDURE_SCHEM PROCEDURE_NAME NUM_INPUT_PARAMS NUM_OUTPUT_PARAMS NUM_RESULT_SETS REMARKS PROCEDURE_TYPE SPECIFIC_NAME\n" + "mTests_sql_odbc_samples sys analyze 0 0 0 NULL 1 replacedId\n" + "mTests_sql_odbc_samples sys analyze 0 0 0 NULL 1 replacedId\n" + "mTests_sql_odbc_samples sys analyze 0 0 0 NULL 1 replacedId\n" + "mTests_sql_odbc_samples sys analyze 0 0 0 NULL 1 replacedId\n"); + + ret = SQLProcedureColumns(stmt, (SQLCHAR*)"", SQL_NTS, + (SQLCHAR*)"sys", SQL_NTS, (SQLCHAR*)"analyze", SQL_NTS, + (SQLCHAR*)"%", SQL_NTS); + compareResult(stmt, ret, "SQLProcedureColumns (sys, analyze, %)", + "Resultset with 20 columns\n" + "PROCEDURE_CAT PROCEDURE_SCHEM PROCEDURE_NAME COLUMN_NAME COLUMN_TYPE 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 SPECIFIC_NAME\n" + "mTests_sql_odbc_samples sys analyze sname 1 -9 VARCHAR 1024 2048 NULL NULL 2 NULL NULL -9 NULL 2048 1 replacedId\n" + "mTests_sql_odbc_samples sys analyze sname 1 -9 VARCHAR 1024 2048 NULL NULL 2 NULL NULL -9 NULL 2048 1 replacedId\n" + "mTests_sql_odbc_samples sys analyze tname 1 -9 VARCHAR 1024 2048 NULL NULL 2 NULL NULL -9 NULL 2048 2 replacedId\n" + "mTests_sql_odbc_samples sys analyze sname 1 -9 VARCHAR 1024 2048 NULL NULL 2 NULL NULL -9 NULL 2048 1 replacedId\n" + "mTests_sql_odbc_samples sys analyze tname 1 -9 VARCHAR 1024 2048 NULL NULL 2 NULL NULL -9 NULL 2048 2 replacedId\n" + "mTests_sql_odbc_samples sys analyze cname 1 -9 VARCHAR 1024 2048 NULL NULL 2 NULL NULL -9 NULL 2048 3 replacedId\n"); + + // test function sys.sin(). There are 2 overloaded variants of this function in MonetDB: sys.sin(real) and sys.sin(double). ret = SQLProcedures(stmt, (SQLCHAR*)"", SQL_NTS, - (SQLCHAR*)"odbctst", SQL_NTS, (SQLCHAR*)"myproc", SQL_NTS); - compareResult(stmt, ret, "SQLProcedures (odbctst, myproc)", - "Resultset with 8 columns\n" - "PROCEDURE_CAT PROCEDURE_SCHEM PROCEDURE_NAME NUM_INPUT_PARAMS NUM_OUTPUT_PARAMS NUM_RESULT_SETS REMARKS PROCEDURE_TYPE\n"); + (SQLCHAR*)"sys", SQL_NTS, (SQLCHAR*)"sin", SQL_NTS); + compareResult(stmt, ret, "SQLProcedures (sys, sin)", + "Resultset with 9 columns\n" + "PROCEDURE_CAT PROCEDURE_SCHEM PROCEDURE_NAME NUM_INPUT_PARAMS NUM_OUTPUT_PARAMS NUM_RESULT_SETS REMARKS PROCEDURE_TYPE SPECIFIC_NAME\n" + "mTests_sql_odbc_samples sys sin 0 0 0 NULL 2 replacedId\n" + "mTests_sql_odbc_samples sys sin 0 0 0 NULL 2 replacedId\n"); + + ret = SQLProcedureColumns(stmt, (SQLCHAR*)"", SQL_NTS, + (SQLCHAR*)"sys", SQL_NTS, (SQLCHAR*)"sin", SQL_NTS, + (SQLCHAR*)"%", SQL_NTS); + compareResult(stmt, ret, "SQLProcedureColumns (sys, sin, %)", + "Resultset with 20 columns\n" + "PROCEDURE_CAT PROCEDURE_SCHEM PROCEDURE_NAME COLUMN_NAME COLUMN_TYPE 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 SPECIFIC_NAME\n" + "mTests_sql_odbc_samples sys sin arg_1 1 7 REAL 24 14 7 2 2 NULL NULL 7 NULL NULL 1 replacedId\n" + "mTests_sql_odbc_samples sys sin res_0 5 7 REAL 24 14 7 2 2 NULL NULL 7 NULL NULL 0 replacedId\n" + "mTests_sql_odbc_samples sys sin arg_1 1 8 DOUBLE 53 24 15 2 2 NULL NULL 8 NULL NULL 1 replacedId\n" + "mTests_sql_odbc_samples sys sin res_0 5 8 DOUBLE 53 24 15 2 2 NULL NULL 8 NULL NULL 0 replacedId\n"); + + // test table returning function sys.env(). It has no input parameters. Only 2 result columns. + ret = SQLProcedures(stmt, (SQLCHAR*)"", SQL_NTS, + (SQLCHAR*)"sys", SQL_NTS, (SQLCHAR*)"env", SQL_NTS); + compareResult(stmt, ret, "SQLProcedures (sys, env)", + "Resultset with 9 columns\n" + "PROCEDURE_CAT PROCEDURE_SCHEM PROCEDURE_NAME NUM_INPUT_PARAMS NUM_OUTPUT_PARAMS NUM_RESULT_SETS REMARKS PROCEDURE_TYPE SPECIFIC_NAME\n" + "mTests_sql_odbc_samples sys env 0 0 0 NULL 2 replacedId\n"); ret = SQLProcedureColumns(stmt, (SQLCHAR*)"", SQL_NTS, - (SQLCHAR*)"odbctst", SQL_NTS, (SQLCHAR*)"myproc", SQL_NTS, + (SQLCHAR*)"sys", SQL_NTS, (SQLCHAR*)"env", SQL_NTS, _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
