コード例 #1
0
static SQLRETURN
MNDBTablePrivileges(ODBCStmt *stmt,
		    SQLCHAR *CatalogName,
		    SQLSMALLINT NameLength1,
		    SQLCHAR *SchemaName,
		    SQLSMALLINT NameLength2,
		    SQLCHAR *TableName,
		    SQLSMALLINT NameLength3)
{
	RETCODE rc;
	char *query = NULL;
	char *query_end = NULL;
	char *cat = NULL, *sch = NULL, *tab = NULL;

	fixODBCstring(CatalogName, NameLength1, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(SchemaName, NameLength2, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(TableName, NameLength3, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);

#ifdef ODBCDEBUG
	ODBCLOG("\"%.*s\" \"%.*s\" \"%.*s\"\n",
		(int) NameLength1, (char *) CatalogName,
		(int) NameLength2, (char *) SchemaName,
		(int) NameLength3, (char *) TableName);
#endif

	if (stmt->Dbc->sql_attr_metadata_id == SQL_FALSE) {
		if (NameLength1 > 0) {
			cat = ODBCParseOA("e", "value",
					  (const char *) CatalogName,
					  (size_t) NameLength1);
			if (cat == NULL)
				goto nomem;
		}
		if (NameLength2 > 0) {
			sch = ODBCParsePV("s", "name",
					  (const char *) SchemaName,
					  (size_t) NameLength2);
			if (sch == NULL)
				goto nomem;
		}
		if (NameLength3 > 0) {
			tab = ODBCParsePV("t", "name",
					  (const char *) TableName,
					  (size_t) NameLength3);
			if (tab == NULL)
				goto nomem;
		}
	} else {
		if (NameLength1 > 0) {
			cat = ODBCParseID("e", "value",
					  (const char *) CatalogName,
					  (size_t) NameLength1);
			if (cat == NULL)
				goto nomem;
		}
		if (NameLength2 > 0) {
			sch = ODBCParseID("s", "name",
					  (const char *) SchemaName,
					  (size_t) NameLength2);
			if (sch == NULL)
				goto nomem;
		}
		if (NameLength3 > 0) {
			tab = ODBCParseID("t", "name",
					  (const char *) TableName,
					  (size_t) NameLength3);
			if (tab == NULL)
				goto nomem;
		}
	}

	/* construct the query now */
	query = malloc(1200 + (cat ? strlen(cat) : 0) +
		       (sch ? strlen(sch) : 0) + (tab ? strlen(tab) : 0));
	if (query == NULL)
		goto nomem;
	query_end = query;

	/* SQLTablePrivileges returns a table with the following columns:
	   table_cat    VARCHAR
	   table_schem  VARCHAR
	   table_name   VARCHAR NOT NULL
	   grantor      VARCHAR
	   grantee      VARCHAR NOT NULL
	   privilege    VARCHAR NOT NULL
	   is_grantable VARCHAR
	 */

	sprintf(query_end,
		"select e.value as table_cat, "
			"s.name as table_schem, "
			"t.name as table_name, "
			"case a.id "
			     "when s.owner then '_SYSTEM' "
			     "else g.name "
			     "end as grantor, "
			"case a.name "
			     "when 'public' then 'PUBLIC' "
			     "else a.name "
			     "end as grantee, "
			"case p.privileges "
			     "when 1 then 'SELECT' "
			     "when 2 then 'UPDATE' "
			     "when 4 then 'INSERT' "
			     "when 8 then 'DELETE' "
			     "when 16 then 'EXECUTE' "
			     "when 32 then 'GRANT' "
			     "end as privilege, "
			"case p.grantable "
			     "when 1 then 'YES' "
			     "when 0 then 'NO' "
			     "end as is_grantable "
		"from sys.schemas s, "
		      "sys._tables t, "
		      "sys.auths a, "
		      "sys.privileges p, "
		      "sys.auths g, "
		      "sys.env() e "
		"where p.obj_id = t.id "
		  "and p.auth_id = a.id "
		  "and t.schema_id = s.id "
		  "and t.system = false "
		  "and p.grantor = g.id "
		  "and e.name = 'gdk_dbname'");
	assert(strlen(query) < 1000);
	query_end += strlen(query_end);

	/* Construct the selection condition query part */
	if (cat) {
		/* filtering requested on catalog name */
		sprintf(query_end, " and %s", cat);
		query_end += strlen(query_end);
		free(cat);
	}
	if (sch) {
		/* filtering requested on schema name */
		sprintf(query_end, " and %s", sch);
		query_end += strlen(query_end);
		free(sch);
	}
	if (tab) {
		/* filtering requested on table name */
		sprintf(query_end, " and %s", tab);
		query_end += strlen(query_end);
		free(tab);
	}

	/* add the ordering */
	strcpy(query_end,
	       " order by table_cat, table_schem, table_name, privilege, grantee");
	query_end += strlen(query_end);

	/* query the MonetDB data dictionary tables */
	rc = MNDBExecDirect(stmt, (SQLCHAR *) query,
			    (SQLINTEGER) (query_end - query));

	free(query);

	return rc;

  nomem:
	/* note that query must be NULL when we get here */
	if (cat)
		free(cat);
	if (sch)
		free(sch);
	if (tab)
		free(tab);
	/* Memory allocation error */
	addStmtError(stmt, "HY001", NULL, 0);
	return SQL_ERROR;
}
コード例 #2
0
ファイル: SQLProcedures.c プロジェクト: cswxu/monetdb-mcs
static SQLRETURN
MNDBProcedures(ODBCStmt *stmt,
	       SQLCHAR *CatalogName,
	       SQLSMALLINT NameLength1,
	       SQLCHAR *SchemaName,
	       SQLSMALLINT NameLength2,
	       SQLCHAR *ProcName,
	       SQLSMALLINT NameLength3)
{
	RETCODE rc;

	/* buffer for the constructed query to do meta data retrieval */
	char *query = NULL;
	char *query_end;
	char *cat = NULL, *sch = NULL, *pro = NULL;

	/* convert input string parameters to normal null terminated C strings */
	fixODBCstring(CatalogName, NameLength1, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(SchemaName, NameLength2, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(ProcName, NameLength3, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);

#ifdef ODBCDEBUG
	ODBCLOG("\"%.*s\" \"%.*s\" \"%.*s\"\n",
		(int) NameLength1, (char *) CatalogName,
		(int) NameLength2, (char *) SchemaName,
		(int) NameLength3, (char *) ProcName);
#endif

	/* SQLProcedures returns a table with the following columns:
	   VARCHAR      procedure_cat
	   VARCHAR      procedure_schem
	   VARCHAR      procedure_name NOT NULL
	   n/a          num_input_params (reserved for future use)
	   n/a          num_output_params (reserved for future use)
	   n/a          num_result_sets (reserved for future use)
	   VARCHAR      remarks
	   SMALLINT     procedure_type
	 */

	if (stmt->Dbc->sql_attr_metadata_id == SQL_FALSE) {
		if (NameLength1 > 0) {
			cat = ODBCParseOA("e", "value",
					  (const char *) CatalogName,
					  (size_t) NameLength1);
			if (cat == NULL)
				goto nomem;
		}
		if (NameLength2 > 0) {
			sch = ODBCParsePV("s", "name",
					  (const char *) SchemaName,
					  (size_t) NameLength2);
			if (sch == NULL)
				goto nomem;
		}
		if (NameLength3 > 0) {
			pro = ODBCParsePV("p", "name",
					  (const char *) ProcName,
					  (size_t) NameLength3);
			if (pro == NULL)
				goto nomem;
		}
	} else {
		if (NameLength1 > 0) {
			cat = ODBCParseID("e", "value",
					  (const char *) CatalogName,
					  (size_t) NameLength1);
			if (cat == NULL)
				goto nomem;
		}
		if (NameLength2 > 0) {
			sch = ODBCParseID("s", "name",
					  (const char *) SchemaName,
					  (size_t) NameLength2);
			if (sch == NULL)
				goto nomem;
		}
		if (NameLength3 > 0) {
			pro = ODBCParseID("p", "name",
					  (const char *) ProcName,
					  (size_t) NameLength3);
			if (pro == NULL)
				goto nomem;
		}
	}

	query = malloc(1000 + (cat ? strlen(cat) : 0) +
		       (sch ? strlen(sch) : 0) + (pro ? strlen(pro) : 0));
	if (query == NULL)
		goto nomem;
	query_end = query;

/* see sql_catalog.h */
#define F_FUNC 1
#define F_PROC 2
#define F_UNION 5
	snprintf(query_end, 1000,
		 "select e.value 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, "
			"cast('' as varchar(1)) as remarks, "
			"cast(case when p.type = %d then %d else %d end as smallint) as procedure_type "
		 "from sys.schemas as s, "
		      "sys.env() as e, "
		      "sys.functions as p "
		 "where p.schema_id = s.id and "
		       "p.sql = true and "
		       "p.type in (%d, %d, %d) and "
		       "e.name = 'gdk_dbname'",
		 F_PROC, SQL_PT_PROCEDURE, SQL_PT_FUNCTION,
		 F_FUNC, F_PROC, F_UNION);
	assert(strlen(query) < 800);
	query_end += strlen(query_end);

	/* Construct the selection condition query part */
	if (cat) {
		/* filtering requested on catalog name */
		sprintf(query_end, " and %s", cat);
		query_end += strlen(query_end);
		free(cat);
	}
	if (sch) {
		/* filtering requested on schema name */
		sprintf(query_end, " and %s", sch);
		query_end += strlen(query_end);
		free(sch);
	}
	if (pro) {
		/* filtering requested on procedure name */
		sprintf(query_end, " and %s", pro);
		query_end += strlen(query_end);
		free(pro);
	}

	/* add the ordering */
	strcpy(query_end,
	       " order by procedure_cat, procedure_schem, procedure_name");
	query_end += strlen(query_end);

	/* query the MonetDB data dictionary tables */

	rc = MNDBExecDirect(stmt, (SQLCHAR *) query, SQL_NTS);

	free(query);

	return rc;

  nomem:
	/* note that query must be NULL when we get here */
	if (cat)
		free(cat);
	if (sch)
		free(sch);
	if (pro)
		free(pro);
	/* Memory allocation error */
	addStmtError(stmt, "HY001", NULL, 0);
	return SQL_ERROR;
}
コード例 #3
0
ファイル: SQLForeignKeys.c プロジェクト: Clay-Birkett/monetdb
static SQLRETURN
SQLForeignKeys_(ODBCStmt *stmt,
		SQLCHAR *PKCatalogName,
		SQLSMALLINT NameLength1,
		SQLCHAR *PKSchemaName,
		SQLSMALLINT NameLength2,
		SQLCHAR *PKTableName,
		SQLSMALLINT NameLength3,
		SQLCHAR *FKCatalogName,
		SQLSMALLINT NameLength4,
		SQLCHAR *FKSchemaName,
		SQLSMALLINT NameLength5,
		SQLCHAR *FKTableName,
		SQLSMALLINT NameLength6)
{
	RETCODE rc;

	/* buffer for the constructed query to do meta data retrieval */
	char *query = NULL;
	char *query_end = NULL;	/* pointer to end of built-up query */
	char *pcat = NULL, *psch = NULL, *ptab = NULL;
	char *fcat = NULL, *fsch = NULL, *ftab = NULL;

	/* deal with SQL_NTS and SQL_NULL_DATA */
	fixODBCstring(PKCatalogName, NameLength1, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(PKSchemaName, NameLength2, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(PKTableName, NameLength3, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(FKCatalogName, NameLength4, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(FKSchemaName, NameLength5, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(FKTableName, NameLength6, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);

#ifdef ODCBDEBUG
	ODBCLOG("\"%.*s\" \"%.*s\" \"%.*s\" \"%.*s\" \"%.*s\" \"%.*s\"\n",
		(int) NameLength1, PKCatalogName,
		(int) NameLength2, PKSchemaName,
		(int) NameLength3, PKTableName,
		(int) NameLength4, FKCatalogName,
		(int) NameLength5, FKSchemaName,
		(int) NameLength6, FKTableName);
#endif
	/* dependent on the input parameter values we must add a
	   variable selection condition dynamically */

	if (stmt->Dbc->sql_attr_metadata_id == SQL_FALSE) {
		if (NameLength1 > 0) {
			pcat = ODBCParseOA("e", "value",
					   (const char *) PKCatalogName,
					   (size_t) NameLength1);
		}
		if (NameLength2 > 0) {
			psch = ODBCParseOA("pks", "name",
					   (const char *) PKSchemaName,
					   (size_t) NameLength2);
		}
		if (NameLength3 > 0) {
			ptab = ODBCParseOA("pkt", "name",
					   (const char *) PKTableName,
					   (size_t) NameLength3);
		}
		if (NameLength4 > 0) {
			fcat = ODBCParseOA("e", "value",
					   (const char *) FKCatalogName,
					   (size_t) NameLength4);
		}
		if (NameLength5 > 0) {
			fsch = ODBCParseOA("fks", "name",
					   (const char *) FKSchemaName,
					   (size_t) NameLength5);
		}
		if (NameLength6 > 0) {
			ftab = ODBCParseOA("fkt", "name",
					   (const char *) FKTableName,
					   (size_t) NameLength6);
		}
	} else {
		if (NameLength1 > 0) {
			pcat = ODBCParseID("e", "value",
					   (const char *) PKCatalogName,
					   (size_t) NameLength1);
		}
		if (NameLength2 > 0) {
			psch = ODBCParseID("pks", "name",
					   (const char *) PKSchemaName,
					   (size_t) NameLength2);
		}
		if (NameLength3 > 0) {
			ptab = ODBCParseID("pkt", "name",
					   (const char *) PKTableName,
					   (size_t) NameLength3);
		}
		if (NameLength4 > 0) {
			fcat = ODBCParseID("e", "value",
					   (const char *) FKCatalogName,
					   (size_t) NameLength4);
		}
		if (NameLength5 > 0) {
			fsch = ODBCParseID("fks", "name",
					   (const char *) FKSchemaName,
					   (size_t) NameLength5);
		}
		if (NameLength6 > 0) {
			ftab = ODBCParseID("fkt", "name",
					   (const char *) FKTableName,
					   (size_t) NameLength6);
		}
	}

	/* first create a string buffer (1200 extra bytes is plenty:
	   we actually need just over 1000) */
	query = malloc(1200 + (pcat ? strlen(pcat) : 0) +
		       (psch ? strlen(psch) : 0) + (ptab ? strlen(ptab) : 0) +
		       (fcat ? strlen(fcat) : 0) + (fsch ? strlen(fsch) : 0) +
		       (ftab ? strlen(ftab) : 0));
	assert(query);
	query_end = query;

	/* SQLForeignKeys returns a table with the following columns:
	   VARCHAR      pktable_cat
	   VARCHAR      pktable_schem
	   VARCHAR      pktable_name NOT NULL
	   VARCHAR      pkcolumn_name NOT NULL
	   VARCHAR      fktable_cat
	   VARCHAR      fktable_schem
	   VARCHAR      fktable_name NOT NULL
	   VARCHAR      fkcolumn_name NOT NULL
	   SMALLINT     key_seq NOT NULL
	   SMALLINT     update_rule
	   SMALLINT     delete_rule
	   VARCHAR      fk_name
	   VARCHAR      pk_name
	   SMALLINT     deferrability
	 */

	sprintf(query_end,
		"select "
		"e.\"value\" as pktable_cat, "
		"pks.\"name\" as pktable_schem, "
		"pkt.\"name\" as pktable_name, "
		"pkkc.\"name\" as pkcolumn_name, "
		"e.\"value\" as fktable_cat, "
		"fks.\"name\" as fktable_schem, "
		"fkt.\"name\" as fktable_name, "
		"fkkc.\"name\" as fkcolumn_name, "
		"cast(fkkc.\"nr\" + 1 as smallint) as key_seq, "
		"cast(%d as smallint) as update_rule, "
		"cast(%d as smallint) as delete_rule, "
		"fkk.\"name\" as fk_name, "
		"pkk.\"name\" as pk_name, "
		"cast(%d as smallint) as deferrability "
		"from sys.\"schemas\" fks, sys.\"tables\" fkt, "
		"sys.\"objects\" fkkc, sys.\"keys\" as fkk, "
		"sys.\"schemas\" pks, sys.\"tables\" pkt, "
		"sys.\"objects\" pkkc, sys.\"keys\" as pkk, "
		"sys.\"env\"() e "
		"where fkt.\"id\" = fkk.\"table_id\" and "
		"pkt.\"id\" = pkk.\"table_id\" and "
		"fkk.\"id\" = fkkc.\"id\" and "
		"pkk.\"id\" = pkkc.\"id\" and "
		"fks.\"id\" = fkt.\"schema_id\" and "
		"pks.\"id\" = pkt.\"schema_id\" and "
		"fkk.\"rkey\" = pkk.\"id\" and "
		"fkkc.\"nr\" = pkkc.\"nr\" and "
		"e.\"name\" = 'gdk_dbname'",
		SQL_NO_ACTION, SQL_NO_ACTION, SQL_NOT_DEFERRABLE);
	assert(strlen(query) < 1100);
	query_end += strlen(query_end);

	/* Construct the selection condition query part */
	if (pcat) {
		/* filtering requested on catalog name */
		sprintf(query_end, " and %s", pcat);
		query_end += strlen(query_end);
		free(pcat);
	}
	if (psch) {
		/* filtering requested on schema name */
		sprintf(query_end, " and %s", psch);
		query_end += strlen(query_end);
		free(psch);
	}
	if (ptab) {
		/* filtering requested on table name */
		sprintf(query_end, " and %s", ptab);
		query_end += strlen(query_end);
		free(ptab);
	}
	if (fcat) {
		/* filtering requested on catalog name */
		sprintf(query_end, " and %s", fcat);
		query_end += strlen(query_end);
		free(fcat);
	}
	if (fsch) {
		/* filtering requested on schema name */
		sprintf(query_end, " and %s", fsch);
		query_end += strlen(query_end);
		free(fsch);
	}
	if (ftab) {
		/* filtering requested on table name */
		sprintf(query_end, " and %s", ftab);
		query_end += strlen(query_end);
		free(ftab);
	}

/* TODO finish the FROM and WHERE clauses */

	/* add the ordering */
	/* if PKTableName != NULL, selection on primary key, order
	   on FK output columns, else order on PK output columns */
	sprintf(query_end, " order by %stable_schem, %stable_name, key_seq",
		PKTableName != NULL ? "fk" : "pk",
		PKTableName != NULL ? "fk" : "pk");
	query_end += strlen(query_end);

	/* query the MonetDB data dictionary tables */
	rc = SQLExecDirect_(stmt, (SQLCHAR *) query,
			    (SQLINTEGER) (query_end - query));

	free(query);

	return rc;
}
コード例 #4
0
static SQLRETURN
SQLTablePrivileges_(ODBCStmt *stmt,
		    SQLCHAR *CatalogName,
		    SQLSMALLINT NameLength1,
		    SQLCHAR *SchemaName,
		    SQLSMALLINT NameLength2,
		    SQLCHAR *TableName,
		    SQLSMALLINT NameLength3)
{
	RETCODE rc;
	char *query = NULL;
	char *query_end = NULL;
	char *cat = NULL, *sch = NULL, *tab = NULL;

	fixODBCstring(CatalogName, NameLength1, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(SchemaName, NameLength2, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(TableName, NameLength3, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);

#ifdef ODBCDEBUG
	ODBCLOG("\"%.*s\" \"%.*s\" \"%.*s\"\n",
		(int) NameLength1, (char *) CatalogName,
		(int) NameLength2, (char *) SchemaName,
		(int) NameLength3, (char *) TableName);
#endif

	if (stmt->Dbc->sql_attr_metadata_id == SQL_FALSE) {
		if (NameLength1 > 0) {
			cat = ODBCParseOA("e", "value",
					  (const char *) CatalogName,
					  (size_t) NameLength1);
		}
		if (NameLength2 > 0) {
			sch = ODBCParsePV("s", "name",
					  (const char *) SchemaName,
					  (size_t) NameLength2);
		}
		if (NameLength3 > 0) {
			tab = ODBCParsePV("t", "name",
					  (const char *) TableName,
					  (size_t) NameLength3);
		}
	} else {
		if (NameLength1 > 0) {
			cat = ODBCParseID("e", "value",
					  (const char *) CatalogName,
					  (size_t) NameLength1);
		}
		if (NameLength2 > 0) {
			sch = ODBCParseID("s", "name",
					  (const char *) SchemaName,
					  (size_t) NameLength2);
		}
		if (NameLength3 > 0) {
			tab = ODBCParseID("t", "name",
					  (const char *) TableName,
					  (size_t) NameLength3);
		}
	}

	/* construct the query now */
	query = malloc(1200 + (cat ? strlen(cat) : 0) +
		       (sch ? strlen(sch) : 0) + (tab ? strlen(tab) : 0));
	query_end = query;

	/* SQLTablePrivileges returns a table with the following columns:
	   table_cat    VARCHAR
	   table_schem  VARCHAR
	   table_name   VARCHAR NOT NULL
	   grantor      VARCHAR
	   grantee      VARCHAR NOT NULL
	   privilege    VARCHAR NOT NULL
	   is_grantable VARCHAR
	 */

	sprintf(query_end,
		"select"
		" e.\"value\" as \"table_cat\","
		" \"s\".\"name\" as \"table_schem\","
		" \"t\".\"name\" as \"table_name\","
		" case \"a\".\"id\""
		"      when \"s\".\"owner\" then '_SYSTEM'"
		"      else \"g\".\"name\""
		"      end as \"grantor\","
		" case \"a\".\"name\""
		"      when 'public' then 'PUBLIC'"
		"      else \"a\".\"name\""
		"      end as \"grantee\","
		" case \"p\".\"privileges\""
		"      when 1 then 'SELECT'"
		"      when 2 then 'UPDATE'"
		"      when 4 then 'INSERT'"
		"      when 8 then 'DELETE'"
		"      when 16 then 'EXECUTE'"
		"      when 32 then 'GRANT'"
		"      end as \"privilege\","
		" case \"p\".\"grantable\""
		"      when 1 then 'YES'"
		"      when 0 then 'NO'"
		"      end as \"is_grantable\" "
		"from \"sys\".\"schemas\" \"s\","
		" \"sys\".\"_tables\" \"t\","
		" \"sys\".\"auths\" \"a\","
		" \"sys\".\"privileges\" \"p\","
		" \"sys\".\"auths\" \"g\","
		" \"sys\".\"env\"() \"e\" "
		"where \"p\".\"obj_id\" = \"t\".\"id\""
		" and \"p\".\"auth_id\" = \"a\".\"id\""
		" and \"t\".\"schema_id\" = \"s\".\"id\""
		" and \"t\".\"system\" = false"
		" and \"p\".\"grantor\" = \"g\".\"id\""
		" and \"e\".\"name\" = 'gdk_dbname'");
	assert(strlen(query) < 1000);
	query_end += strlen(query_end);

	/* Construct the selection condition query part */
	if (cat) {
		/* filtering requested on catalog name */
		sprintf(query_end, " and %s", cat);
		query_end += strlen(query_end);
		free(cat);
	}
	if (sch) {
		/* filtering requested on schema name */
		sprintf(query_end, " and %s", sch);
		query_end += strlen(query_end);
		free(sch);
	}
	if (tab) {
		/* filtering requested on table name */
		sprintf(query_end, " and %s", tab);
		query_end += strlen(query_end);
		free(tab);
	}

	/* add the ordering */
	strcpy(query_end,
	       " order by \"table_cat\", \"table_schem\", \"table_name\", \"privilege\", \"grantee\"");
	query_end += strlen(query_end);

	/* query the MonetDB data dictionary tables */
        rc = SQLExecDirect_(stmt, (SQLCHAR *) query,
			    (SQLINTEGER) (query_end - query));

	free(query);

	return rc;
}
コード例 #5
0
ファイル: SQLColumns.c プロジェクト: Clay-Birkett/monetdb
static SQLRETURN
SQLColumns_(ODBCStmt *stmt,
	    SQLCHAR *CatalogName,
	    SQLSMALLINT NameLength1,
	    SQLCHAR *SchemaName,
	    SQLSMALLINT NameLength2,
	    SQLCHAR *TableName,
	    SQLSMALLINT NameLength3,
	    SQLCHAR *ColumnName,
	    SQLSMALLINT NameLength4)
{
	RETCODE rc;

	/* buffer for the constructed query to do meta data retrieval */
	char *query = NULL;
	char *query_end = NULL;
	char *cat = NULL, *sch = NULL, *tab = NULL, *col = NULL;

	/* null pointers not allowed if arguments are identifiers */
	if (stmt->Dbc->sql_attr_metadata_id == SQL_TRUE &&
	    (SchemaName == NULL || TableName == NULL || ColumnName == NULL)) {
		addStmtError(stmt, "HY090", NULL, 0);
		return SQL_ERROR;
	}

	fixODBCstring(CatalogName, NameLength1, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(SchemaName, NameLength2, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(TableName, NameLength3, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(ColumnName, NameLength4, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);

#ifdef ODBCDEBUG
	ODBCLOG(" \"%.*s\" \"%.*s\" \"%.*s\" \"%.*s\"\n",
		(int) NameLength1, (char *) CatalogName,
		(int) NameLength2, (char *) SchemaName,
		(int) NameLength3, (char *) TableName,
		(int) NameLength4, (char *) ColumnName);
#endif

	if (stmt->Dbc->sql_attr_metadata_id == SQL_FALSE) {
		if (NameLength1 > 0) {
			cat = ODBCParseOA("e", "value",
					  (const char *) CatalogName,
					  (size_t) NameLength1);
		}
		if (NameLength2 > 0) {
			sch = ODBCParsePV("s", "name",
					  (const char *) SchemaName,
					  (size_t) NameLength2);
		}
		if (NameLength3 > 0) {
			tab = ODBCParsePV("t", "name",
					  (const char *) TableName,
					  (size_t) NameLength3);
		}
		if (NameLength4 > 0) {
			col = ODBCParsePV("c", "name",
					  (const char *) ColumnName,
					  (size_t) NameLength4);
		}
	} else {
		if (NameLength1 > 0) {
			cat = ODBCParseID("e", "value",
					  (const char *) CatalogName,
					  (size_t) NameLength1);
		}
		if (NameLength2 > 0) {
			sch = ODBCParseID("s", "name",
					  (const char *) SchemaName,
					  (size_t) NameLength2);
		}
		if (NameLength3 > 0) {
			tab = ODBCParseID("t", "name",
					  (const char *) TableName,
					  (size_t) NameLength3);
		}
		if (NameLength4 > 0) {
			col = ODBCParseID("c", "name",
					  (const char *) ColumnName,
					  (size_t) NameLength4);
		}
	}

	/* construct the query now */
	query = malloc(6500 + (cat ? strlen(cat) : 0) +
		       (sch ? strlen(sch) : 0) + (tab ? strlen(tab) : 0) +
		       (col ? strlen(col) : 0));
	assert(query);
	query_end = query;

	/* SQLColumns returns a table with the following columns:
	   VARCHAR      table_cat
	   VARCHAR      table_schem
	   VARCHAR      table_name NOT NULL
	   VARCHAR      column_name NOT NULL
	   SMALLINT     data_type NOT NULL
	   VARCHAR      type_name NOT NULL
	   INTEGER      column_size
	   INTEGER      buffer_length
	   SMALLINT     decimal_digits
	   SMALLINT     num_prec_radix
	   SMALLINT     nullable NOT NULL
	   VARCHAR      remarks
	   VARCHAR      column_def
	   SMALLINT     sql_data_type NOT NULL
	   SMALLINT     sql_datetime_sub
	   INTEGER      char_octet_length
	   INTEGER      ordinal_position NOT NULL
	   VARCHAR      is_nullable
	 */

	sprintf(query_end,
		"select e.\"value\" as table_cat,"
		      " s.\"name\" as table_schem,"
		      " t.\"name\" as table_name,"
		      " c.\"name\" as column_name,"
		      " case c.\"type\""
			   " when 'bigint' then %d"
			   " when 'blob' then %d"
			   " when 'boolean' then %d"
			   " when 'char' then %d"
			   " when 'clob' then %d"
			   " when 'date' then %d"
			   " when 'decimal' then %d"
			   " when 'double' then %d"
			   " when 'int' then %d"
			   " when 'month_interval' then"
				" case c.type_digits"
				     " when 1 then %d"
				     " when 2 then %d"
				     " when 3 then %d"
				" end"
			   " when 'real' then %d"
			   " when 'sec_interval' then"
				" case c.type_digits"
				     " when 4 then %d"
				     " when 5 then %d"
				     " when 6 then %d"
				     " when 7 then %d"
				     " when 8 then %d"
				     " when 9 then %d"
				     " when 10 then %d"
				     " when 11 then %d"
				     " when 12 then %d"
				     " when 13 then %d"
				" end"
			   " when 'smallint' then %d"
			   " when 'time' then %d"
			   " when 'timestamp' then %d"
			   " when 'timestamptz' then %d"
			   " when 'timetz' then %d"
			   " when 'tinyint' then %d"
			   " when 'varchar' then %d"
			   " when 'wrd' then"
				" case c.type_digits"
				     " when 32 then %d"
				     " when 64 then %d"
				" end"
		      " end as data_type,"
		      " case c.\"type\""
			   " when 'bigint' then 'BIGINT'"
			   " when 'blob' then 'BINARY LARGE OBJECT'"
			   " when 'boolean' then 'BOOLEAN'"
			   " when 'char' then 'CHARACTER'"
			   " when 'clob' then 'CHARACTER LARGE OBJECT'"
			   " when 'date' then 'DATE'"
			   " when 'decimal' then 'DECIMAL'"
			   " when 'double' then 'DOUBLE'"
			   " when 'int' then 'INTEGER'"
			   " when 'month_interval' then"
				" case c.type_digits"
				     " when 1 then 'INTERVAL YEAR'"
				     " when 2 then 'INTERVAL YEAR TO MONTH'"
				     " when 3 then 'INTERVAL MONTH'"
				" end"
			   " when 'real' then 'REAL'"
			   " when 'sec_interval' then"
				" case c.type_digits"
				     " when 4 then 'INTERVAL DAY'"
				     " when 5 then 'INTERVAL DAY TO HOUR'"
				     " when 6 then 'INTERVAL DAY TO MINUTE'"
				     " when 7 then 'INTERVAL DAY TO SECOND'"
				     " when 8 then 'INTERVAL HOUR'"
				     " when 9 then 'INTERVAL HOUR TO MINUTE'"
				     " when 10 then 'INTERVAL HOUR TO SECOND'"
				     " when 11 then 'INTERVAL MINUTE'"
				     " when 12 then 'INTERVAL MINUTE TO SECOND'"
				     " when 13 then 'INTERVAL SECOND'"
				" end"
			   " when 'smallint' then 'SMALLINT'"
			   " when 'time' then 'TIME'"
			   " when 'timestamp' then 'TIMESTAMP'"
			   " when 'timestamptz' then 'TIMESTAMP'"
			   " when 'timetz' then 'TIME'"
			   " when 'tinyint' then 'TINYINT'"
			   " when 'varchar' then 'VARCHAR'"
			   " when 'wrd' then"
				" case c.type_digits"
				     " when 32 then 'INTEGER'"
				     " when 64 then 'BIGINT'"
				" end"
		      " end as type_name,"
		      " case c.\"type\""
			   " when 'date' then 10"
			   " when 'month_interval' then"
				" case c.type_digits"
				     " when 1 then 26"
				     " when 2 then 38"
				     " when 3 then 27"
				" end"
			   " when 'sec_interval' then"
				" case c.type_digits"
				     " when 4 then 25"
				     " when 5 then 36"
				     " when 6 then 41"
				     " when 7 then 47"
				     " when 8 then 26"
				     " when 9 then 39"
				     " when 10 then 45"
				     " when 11 then 28"
				     " when 12 then 44"
				     " when 13 then 30"
				" end"
			   " when 'time' then 12"
			   " when 'timestamp' then 23"
			   " when 'timestamptz' then 23"
			   " when 'timetz' then 12"
			   " else c.type_digits"
		      " end as column_size,"
		      " case c.\"type\""
			   " when 'bigint' then 20"
			   " when 'char' then 2 * c.type_digits"
			   " when 'clob' then 2 * c.type_digits"
			   " when 'date' then 10"
			   " when 'double' then 24"
			   " when 'int' then 11"
			   " when 'month_interval' then"
				" case c.type_digits"
				     " when 1 then 26"
				     " when 2 then 38"
				     " when 3 then 27"
				" end"
			   " when 'real' then 14"
			   " when 'sec_interval' then"
				" case c.type_digits"
				     " when 4 then 25"
				     " when 5 then 36"
				     " when 6 then 41"
				     " when 7 then 47"
				     " when 8 then 26"
				     " when 9 then 39"
				     " when 10 then 45"
				     " when 11 then 28"
				     " when 12 then 44"
				     " when 13 then 30"
				" end"
			   " when 'smallint' then 6"
			   " when 'time' then 12"
			   " when 'timestamp' then 23"
			   " when 'timestamptz' then 23"
			   " when 'timetz' then 12"
			   " when 'tinyint' then 4"
			   " when 'varchar' then 2 * c.type_digits"
			   " when 'wrd' then"
				" case c.type_digits"
				     " when 32 then 11"
				     " when 64 then 20"
				" end"
			   " else c.type_digits"
		      " end as buffer_length,"
		      " case c.\"type\""
			   " when 'bigint' then 19"
			   " when 'decimal' then c.type_scale"
			   " when 'double' then"
				" case when c.type_digits = 53 and c.type_scale = 0 then 15"
				" else c.type_digits"
				" end"
			   " when 'int' then 10"
			   " when 'month_interval' then 0"
			   " when 'real' then"
				" case when c.type_digits = 24 and c.type_scale = 0 then 7"
				" else c.type_digits"
				" end"
			   " when 'sec_interval' then 0"
			   " when 'smallint' then 5"
			   " when 'time' then c.type_digits - 1"
			   " when 'timestamp' then c.type_digits - 1"
			   " when 'timestamptz' then c.type_digits - 1"
			   " when 'timetz' then c.type_digits - 1"
			   " when 'tinyint' then 3"
			   " when 'wrd' then"
				" case c.type_digits"
				     " when 32 then 10"
				     " when 64 then 19"
				" end"
			   " else cast(null as smallint)"
		      " end as decimal_digits,"
		      " case c.\"type\""
			   " when 'bigint' then 2"
			   " when 'decimal' then 10"
			   " when 'double' then"
				" case when c.type_digits = 53 and c.type_scale = 0 then 2"
				" else 10"
				" end"
			   " when 'int' then 2"
			   " when 'real' then"
				" case when c.type_digits = 24 and c.type_scale = 0 then 2"
				" else 10"
				" end"
			   " when 'smallint' then 2"
			   " when 'tinyint' then 2"
			   " when 'wrd' then 2"
			   " else cast(null as smallint)"
		      " end as num_prec_radix,"
		      " case c.\"null\""
			   " when true then cast(%d as smallint)"
			   " when false then cast(%d as smallint)"
		      " end as nullable,"
		      " cast('' as varchar(1)) as remarks,"
		      " c.\"default\" as column_def,"
		      " case c.\"type\""
			   " when 'bigint' then %d"
			   " when 'blob' then %d"
			   " when 'boolean' then %d"
			   " when 'char' then %d"
			   " when 'clob' then %d"
			   " when 'date' then %d"
			   " when 'decimal' then %d"
			   " when 'double' then %d"
			   " when 'int' then %d"
			   " when 'month_interval' then %d"
			   " when 'real' then %d"
			   " when 'sec_interval' then %d"
			   " when 'smallint' then %d"
			   " when 'time' then %d"
			   " when 'timestamp' then %d"
			   " when 'timestamptz' then %d"
			   " when 'timetz' then %d"
			   " when 'tinyint' then %d"
			   " when 'varchar' then %d"
			   " when 'wrd' then"
				" case c.type_digits"
				     " when 32 then %d"
				     " when 64 then %d"
				" end"
		      " end as sql_data_type,"
		      " case c.\"type\""
			   " when 'date' then %d"
			   " when 'month_interval' then"
				" case c.type_digits"
				     " when 1 then %d"
				     " when 2 then %d"
				     " when 3 then %d"
				" end"
			   " when 'sec_interval' then"
				" case c.type_digits"
				     " when 4 then %d"
				     " when 5 then %d"
				     " when 6 then %d"
				     " when 7 then %d"
				     " when 8 then %d"
				     " when 9 then %d"
				     " when 10 then %d"
				     " when 11 then %d"
				     " when 12 then %d"
				     " when 13 then %d"
				" end"
			   " when 'time' then %d"
			   " when 'timestamp' then %d"
			   " when 'timestamptz' then %d"
			   " when 'timetz' then %d"
			   " else cast(null as smallint)"
		      " end as sql_datetime_sub,"
		      " case c.\"type\""
			   " when 'char' then 2 * c.type_digits"
			   " when 'varchar' then 2 * c.type_digits"
			   " when 'clob' then 2 * c.type_digits"
			   " when 'blob' then c.type_digits"
			   " else cast(null as integer)"
		      " end as char_octet_length,"
		      " cast(c.\"number\" + 1 as integer) as ordinal_position,"
		      " case c.\"null\""
			   " when true then cast('YES' as varchar(3))"
			   " when false then cast('NO' as varchar(3))"
		      " end as is_nullable"
		" from sys.\"schemas\" s,"
		     " sys.\"tables\" t,"
		     " sys.\"columns\" c,"
		     " sys.\"env\"() e"
		" where s.\"id\" = t.\"schema_id\" and"
		      " t.\"id\" = c.\"table_id\" and"
		      " e.\"name\" = 'gdk_dbname'",
		/* data_type: */
		SQL_BIGINT, SQL_LONGVARBINARY, SQL_BIT, SQL_WCHAR,
		SQL_WLONGVARCHAR, SQL_TYPE_DATE, SQL_DECIMAL, SQL_DOUBLE,
		SQL_INTEGER, SQL_INTERVAL_YEAR, SQL_INTERVAL_YEAR_TO_MONTH,
		SQL_INTERVAL_MONTH, SQL_REAL, SQL_INTERVAL_DAY,
		SQL_INTERVAL_DAY_TO_HOUR, SQL_INTERVAL_DAY_TO_MINUTE,
		SQL_INTERVAL_DAY_TO_SECOND, SQL_INTERVAL_HOUR,
		SQL_INTERVAL_HOUR_TO_MINUTE, SQL_INTERVAL_HOUR_TO_SECOND,
		SQL_INTERVAL_MINUTE, SQL_INTERVAL_MINUTE_TO_SECOND,
		SQL_INTERVAL_SECOND, SQL_SMALLINT, SQL_TYPE_TIME,
		SQL_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, SQL_TYPE_TIME,
		SQL_TINYINT, SQL_WVARCHAR, SQL_INTEGER, SQL_BIGINT,
		/* nullable: */
		SQL_NULLABLE, SQL_NO_NULLS,
		/* sql_data_type: */
		SQL_BIGINT, SQL_LONGVARBINARY, SQL_BIT, SQL_WCHAR,
		SQL_WLONGVARCHAR, SQL_DATETIME, SQL_DECIMAL, SQL_DOUBLE,
		SQL_INTEGER, SQL_INTERVAL, SQL_REAL, SQL_INTERVAL,
		SQL_SMALLINT, SQL_DATETIME, SQL_DATETIME, SQL_DATETIME,
		SQL_DATETIME, SQL_TINYINT, SQL_WVARCHAR, SQL_INTEGER,
		SQL_BIGINT,
		/* sql_datetime_sub: */
		SQL_CODE_DATE, SQL_CODE_YEAR, SQL_CODE_YEAR_TO_MONTH,
		SQL_CODE_MONTH, SQL_CODE_DAY, SQL_CODE_DAY_TO_HOUR,
		SQL_CODE_DAY_TO_MINUTE, SQL_CODE_DAY_TO_SECOND,
		SQL_CODE_HOUR, SQL_CODE_HOUR_TO_MINUTE,
		SQL_CODE_HOUR_TO_SECOND, SQL_CODE_MINUTE,
		SQL_CODE_MINUTE_TO_SECOND, SQL_CODE_SECOND,
		SQL_CODE_TIME, SQL_CODE_TIMESTAMP, SQL_CODE_TIMESTAMP,
		SQL_CODE_TIME);
	assert(strlen(query) < 6300);
	query_end += strlen(query_end);

	/* depending on the input parameter values we must add a
	   variable selection condition dynamically */

	/* Construct the selection condition query part */
	if (cat) {
		/* filtering requested on catalog name */
		sprintf(query_end, " and %s", cat);
		query_end += strlen(query_end);
		free(cat);
	}
	if (sch) {
		/* filtering requested on schema name */
		sprintf(query_end, " and %s", sch);
		query_end += strlen(query_end);
		free(sch);
	}
	if (tab) {
		/* filtering requested on table name */
		sprintf(query_end, " and %s", tab);
		query_end += strlen(query_end);
		free(tab);
	}
	if (col) {
		/* filtering requested on column name */
		sprintf(query_end, " and %s", col);
		query_end += strlen(query_end);
		free(col);
	}

	/* add the ordering */
	strcpy(query_end,
	       " order by table_cat, table_schem, "
	       "table_name, ordinal_position");
	query_end += strlen(query_end);

	/* query the MonetDB data dictionary tables */
	rc = SQLExecDirect_(stmt, (SQLCHAR *) query,
			    (SQLINTEGER) (query_end - query));

	free(query);

	return rc;
}
コード例 #6
0
ファイル: SQLSpecialColumns.c プロジェクト: MonetDB/MonetDB
static SQLRETURN
MNDBSpecialColumns(ODBCStmt *stmt,
		   SQLUSMALLINT IdentifierType,
		   SQLCHAR *CatalogName,
		   SQLSMALLINT NameLength1,
		   SQLCHAR *SchemaName,
		   SQLSMALLINT NameLength2,
		   SQLCHAR *TableName,
		   SQLSMALLINT NameLength3,
		   SQLUSMALLINT Scope,
		   SQLUSMALLINT Nullable)
{
	RETCODE rc;

	/* buffer for the constructed query to do meta data retrieval */
	char *query = NULL;
	char *query_end = NULL;
	char *cat = NULL, *sch = NULL, *tab = NULL;

	fixODBCstring(CatalogName, NameLength1, SQLSMALLINT, addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(SchemaName, NameLength2, SQLSMALLINT, addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(TableName, NameLength3, SQLSMALLINT, addStmtError, stmt, return SQL_ERROR);

#ifdef ODBCDEBUG
	ODBCLOG("\"%.*s\" \"%.*s\" \"%.*s\" %s %s\n",
		(int) NameLength1, (char *) CatalogName,
		(int) NameLength2, (char *) SchemaName,
		(int) NameLength3, (char *) TableName,
		translateScope(Scope), translateNullable(Nullable));
#endif

	/* check for valid IdentifierType argument */
	switch (IdentifierType) {
	case SQL_BEST_ROWID:
	case SQL_ROWVER:
		break;
	default:
		/* Column type out of range */
		addStmtError(stmt, "HY097", NULL, 0);
		return SQL_ERROR;
	}

	/* check for valid Scope argument */
	switch (Scope) {
	case SQL_SCOPE_CURROW:
	case SQL_SCOPE_TRANSACTION:
	case SQL_SCOPE_SESSION:
		break;
	default:
		/* Scope type out of range */
		addStmtError(stmt, "HY098", NULL, 0);
		return SQL_ERROR;
	}

	/* check for valid Nullable argument */
	switch (Nullable) {
	case SQL_NO_NULLS:
	case SQL_NULLABLE:
		break;
	default:
		/* Nullable type out of range */
		addStmtError(stmt, "HY099", NULL, 0);
		return SQL_ERROR;
	}

	/* check if a valid (non null, not empty) table name is supplied */
	if (TableName == NULL) {
		/* Invalid use of null pointer */
		addStmtError(stmt, "HY009", NULL, 0);
		return SQL_ERROR;
	}
	if (NameLength3 == 0) {
		/* Invalid string or buffer length */
		addStmtError(stmt, "HY090", NULL, 0);
		return SQL_ERROR;
	}

	/* SQLSpecialColumns returns a table with the following columns:
	   SMALLINT     scope
	   VARCHAR      column_name NOT NULL
	   SMALLINT     data_type NOT NULL
	   VARCHAR      type_name NOT NULL
	   INTEGER      column_size
	   INTEGER      buffer_length
	   SMALLINT     decimal_digits
	   SMALLINT     pseudo_column
	 */
	if (IdentifierType == SQL_BEST_ROWID) {
		/* Select from the key table the (smallest) primary/unique key */
		if (stmt->Dbc->sql_attr_metadata_id == SQL_FALSE) {
			if (NameLength1 > 0) {
				cat = ODBCParseOA("e", "value",
						  (const char *) CatalogName,
						  (size_t) NameLength1);
				if (cat == NULL)
					goto nomem;
			}
			if (NameLength2 > 0) {
				sch = ODBCParseOA("s", "name",
						  (const char *) SchemaName,
						  (size_t) NameLength2);
				if (sch == NULL)
					goto nomem;
			}
			if (NameLength3 > 0) {
				tab = ODBCParseOA("t", "name",
						  (const char *) TableName,
						  (size_t) NameLength3);
				if (tab == NULL)
					goto nomem;
			}
		} else {
			if (NameLength1 > 0) {
				cat = ODBCParseID("e", "value",
						  (const char *) CatalogName,
						  (size_t) NameLength1);
				if (cat == NULL)
					goto nomem;
			}
			if (NameLength2 > 0) {
				sch = ODBCParseID("s", "name",
						  (const char *) SchemaName,
						  (size_t) NameLength2);
				if (sch == NULL)
					goto nomem;
			}
			if (NameLength3 > 0) {
				tab = ODBCParseID("t", "name",
						  (const char *) TableName,
						  (size_t) NameLength3);
				if (tab == NULL)
					goto nomem;
			}
		}

		/* first create a string buffer (1000 extra bytes is plenty */
		query = (char *) malloc(5000 + NameLength1 + NameLength2 + NameLength3);
		if (query == NULL)
			goto nomem;
		query_end = query;

		/* Note: SCOPE is SQL_SCOPE_TRANSACTION */
		/* Note: PSEUDO_COLUMN is SQL_PC_NOT_PSEUDO */
		sprintf(query_end,
			"with sc as ("
			"select t.id as table_id, k.type as type, "
			       "cast(%d as smallint) as scope, "
			       "c.name as column_name, "
			DATA_TYPE(c) ", "
			TYPE_NAME(c) ", "
			COLUMN_SIZE(c) ", "
			BUFFER_LENGTH(c) ", "
			DECIMAL_DIGITS(c) ", "
			       "cast(%d as smallint) as pseudo_column "
			 "from sys.schemas s, "
			      "sys.tables t, "
			      "sys.columns c, "
			      "sys.keys k, "
			      "sys.objects kc, "
			      "sys.env() e  "
			 "where s.id = t.schema_id and "
			       "t.id = c.table_id and "
			       "t.id = k.table_id and "
			       "c.name = kc.name and "
			       "kc.id = k.id and "
			       "k.type = 0 and "
			       "e.name = 'gdk_dbname'",
			/* scope: */
			SQL_SCOPE_TRANSACTION,
#ifdef DATA_TYPE_ARGS
			DATA_TYPE_ARGS,
#endif
#ifdef TYPE_NAME_ARGS
			TYPE_NAME_ARGS,
#endif
#ifdef COLUMN_SIZE_ARGS
			COLUMN_SIZE_ARGS,
#endif
#ifdef BUFFER_SIZE_ARGS
			BUFFER_SIZE_ARGS,
#endif
#ifdef DECIMAL_DIGITS_ARGS
			DECIMAL_DIGITS_ARGS,
#endif
			/* pseudo_column: */
			SQL_PC_NOT_PSEUDO);
		assert(strlen(query) < 4300);
		query_end += strlen(query_end);
		/* TODO: improve the SQL to get the correct result:
		   - only one set of columns should be returned, also
		     when multiple primary keys are available for this
		     table.
		   - when the table has NO primary key it should
		     return the columns of a unique key (only from ONE
		     unique key which is also the best/smallest key)
		   TODO: optimize SQL:
		   - when no SchemaName is set (see above) also no
		     filtering on SCHEMA NAME and join with table
		     SCHEMAS is needed!
		 */

		/* add the selection condition */
		if (cat) {
			/* filtering requested on catalog name */
			sprintf(query_end, " and %s", cat);
			query_end += strlen(query_end);
			free(cat);
		}
		if (sch) {
			/* filtering requested on schema name */
			sprintf(query_end, " and %s", sch);
			query_end += strlen(query_end);
			free(sch);
		}
		if (tab) {
			/* filtering requested on table name */
			sprintf(query_end, " and %s", tab);
			query_end += strlen(query_end);
			free(tab);
		}

		/* add an extra selection when SQL_NO_NULLS is requested */
		if (Nullable == SQL_NO_NULLS) {
			strcpy(query_end, " and c.\"null\" = false");
			query_end += strlen(query_end);
		}

		strcpy(query_end,
		       "), "
			"tid as ("
			   "select t.id as tid "
			    "from sys._tables t, sys.keys k "
			    "where t.id = k.table_id and k.type = 0"
		       ") "
			"select sc.scope, sc.column_name, sc.data_type, "
			       "sc.type_name, sc.column_size, "
			       "sc.buffer_length, sc.decimal_digits, "
			       "sc.pseudo_column "
			"from sc "
			"where (sc.type = 0 and "
			       "sc.table_id in (select tid from tid)) or "
			      "(sc.type = 1 and "
			       "sc.table_id not in (select tid from tid))");
		query_end += strlen(query_end);

		/* ordering on SCOPE not needed (since it is constant) */
	} else {
		assert(IdentifierType == SQL_ROWVER);
		/* The backend does not have such info available */
		/* create just a query which results in zero rows */
		/* Note: pseudo_column is sql_pc_unknown is 0 */
		query = strdup("select cast(null as smallint) as scope, "
				      "cast('' as varchar(1)) as column_name, "
				      "cast(1 as smallint) as data_type, "
				      "cast('char' as varchar(4)) as type_name, "
				      "cast(1 as integer) as column_size, "
				      "cast(1 as integer) as buffer_length, "
				      "cast(0 as smallint) as decimal_digits, "
				      "cast(0 as smallint) as pseudo_column "
			       "where 0 = 1");
		if (query == NULL)
			goto nomem;
		query_end = query + strlen(query);
	}

	/* query the MonetDB data dictionary tables */
	rc = MNDBExecDirect(stmt,
			    (SQLCHAR *) query,
			    (SQLINTEGER) (query_end - query));

	free(query);

	return rc;

  nomem:
	/* note that query must be NULL when we get here */
	if (cat)
		free(cat);
	if (sch)
		free(sch);
	if (tab)
		free(tab);
	/* Memory allocation error */
	addStmtError(stmt, "HY001", NULL, 0);
	return SQL_ERROR;
}
コード例 #7
0
ファイル: SQLPrimaryKeys.c プロジェクト: cswxu/monetdb-mcs
static SQLRETURN
MNDBPrimaryKeys(ODBCStmt *stmt,
		SQLCHAR *CatalogName,
		SQLSMALLINT NameLength1,
		SQLCHAR *SchemaName,
		SQLSMALLINT NameLength2,
		SQLCHAR *TableName,
		SQLSMALLINT NameLength3)
{
	RETCODE rc;

	/* buffer for the constructed query to do meta data retrieval */
	char *query = NULL;
	char *query_end = NULL;	/* pointer to end of built-up query */
	char *cat = NULL, *sch = NULL, *tab = NULL;

	/* deal with SQL_NTS and SQL_NULL_DATA */
	fixODBCstring(CatalogName, NameLength1, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(SchemaName, NameLength2, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);
	fixODBCstring(TableName, NameLength3, SQLSMALLINT,
		      addStmtError, stmt, return SQL_ERROR);

	/* check if a valid (non null, not empty) table name is supplied */
	if (TableName == NULL) {
		/* Invalid use of null pointer */
		addStmtError(stmt, "HY009", NULL, 0);
		return SQL_ERROR;
	}
#ifdef ODBCDEBUG
	ODBCLOG("\"%.*s\" \"%.*s\" \"%.*s\"\n",
		(int) NameLength1, (char *) CatalogName,
		(int) NameLength2, (char *) SchemaName,
		(int) NameLength3, (char *) TableName);
#endif

	if (stmt->Dbc->sql_attr_metadata_id == SQL_FALSE) {
		if (NameLength1 > 0) {
			cat = ODBCParseOA("e", "value",
					  (const char *) CatalogName,
					  (size_t) NameLength1);
			if (cat == NULL)
				goto nomem;
		}
		if (NameLength2 > 0) {
			sch = ODBCParseOA("s", "name",
					  (const char *) SchemaName,
					  (size_t) NameLength2);
			if (sch == NULL)
				goto nomem;
		}
		if (NameLength3 > 0) {
			tab = ODBCParseOA("t", "name",
					  (const char *) TableName,
					  (size_t) NameLength3);
			if (tab == NULL)
				goto nomem;
		}
	} else {
		if (NameLength1 > 0) {
			cat = ODBCParseID("e", "value",
					  (const char *) CatalogName,
					  (size_t) NameLength1);
			if (cat == NULL)
				goto nomem;
		}
		if (NameLength2 > 0) {
			sch = ODBCParseID("s", "name",
					  (const char *) SchemaName,
					  (size_t) NameLength2);
			if (sch == NULL)
				goto nomem;
		}
		if (NameLength3 > 0) {
			tab = ODBCParseID("t", "name",
					  (const char *) TableName,
					  (size_t) NameLength3);
			if (tab == NULL)
				goto nomem;
		}
	}

	/* construct the query */
	query = malloc(1000 + (cat ? strlen(cat) : 0) +
		       (sch ? strlen(sch) : 0) + (tab ? strlen(tab) : 0));
	if (query == NULL)
		goto nomem;
	query_end = query;

	/* SQLPrimaryKeys returns a table with the following columns:
	   VARCHAR      table_cat
	   VARCHAR      table_schem
	   VARCHAR      table_name NOT NULL
	   VARCHAR      column_name NOT NULL
	   SMALLINT     key_seq NOT NULL
	   VARCHAR      pk_name
	 */
	strcpy(query_end,
	       "select e.value as table_cat, "
		      "s.name as table_schem, "
		      "t.name as table_name, "
		      "kc.name as column_name, "
		      "cast(kc.nr + 1 as smallint) as key_seq, "
		      "k.name as pk_name "
	       "from sys.schemas s, sys.tables t, "
		    "sys.keys k, sys.objects kc, "
		    "sys.env() e "
	       "where k.id = kc.id and "
		     "k.table_id = t.id and "
		     "t.schema_id = s.id and "
		     "k.type = 0 and "
		     "e.name = 'gdk_dbname'");
	assert(strlen(query) < 800);
	query_end += strlen(query_end);

	/* Construct the selection condition query part */
	if (cat) {
		/* filtering requested on catalog name */
		sprintf(query_end, " and %s", cat);
		query_end += strlen(query_end);
		free(cat);
	}
	if (sch) {
		/* filtering requested on schema name */
		sprintf(query_end, " and %s", sch);
		query_end += strlen(query_end);
		free(sch);
	}
	if (tab) {
		/* filtering requested on table name */
		sprintf(query_end, " and %s", tab);
		query_end += strlen(query_end);
		free(tab);
	}

	/* add the ordering */
	strcpy(query_end, " order by table_schem, table_name, key_seq");
	query_end += strlen(query_end);

	/* query the MonetDB data dictionary tables */
	rc = MNDBExecDirect(stmt,
			    (SQLCHAR *) query,
			    (SQLINTEGER) (query_end - query));

	free(query);

	return rc;

  nomem:
	/* note that query must be NULL when we get here */
	if (cat)
		free(cat);
	if (sch)
		free(sch);
	if (tab)
		free(tab);
	/* Memory allocation error */
	addStmtError(stmt, "HY001", NULL, 0);
	return SQL_ERROR;
}