info.c

关系型数据库 Postgresql 6.5.2

			pfExists[SQL_API_SQLSTATISTICS]       = TRUE;
			pfExists[SQL_API_SQLTABLES]           = TRUE;

			// ODBC level 2 functions
			pfExists[SQL_API_SQLBROWSECONNECT]    = FALSE;
			pfExists[SQL_API_SQLCOLUMNPRIVILEGES] = FALSE;
			pfExists[SQL_API_SQLDATASOURCES]      = FALSE;  // only implemented by DM
			pfExists[SQL_API_SQLDESCRIBEPARAM]    = FALSE;	// not properly implemented
			pfExists[SQL_API_SQLDRIVERS]          = FALSE;  // only implemented by DM
			pfExists[SQL_API_SQLEXTENDEDFETCH]    = TRUE;
			pfExists[SQL_API_SQLFOREIGNKEYS]      = TRUE;
			pfExists[SQL_API_SQLMORERESULTS]      = TRUE;
			pfExists[SQL_API_SQLNATIVESQL]        = TRUE;
			pfExists[SQL_API_SQLNUMPARAMS]        = TRUE;
			pfExists[SQL_API_SQLPARAMOPTIONS]     = FALSE;
			pfExists[SQL_API_SQLPRIMARYKEYS]      = TRUE;
			pfExists[SQL_API_SQLPROCEDURECOLUMNS] = FALSE;
			pfExists[SQL_API_SQLPROCEDURES]       = FALSE;
			pfExists[SQL_API_SQLSETPOS]           = TRUE;
			pfExists[SQL_API_SQLSETSCROLLOPTIONS] = TRUE;	// odbc 1.0
			pfExists[SQL_API_SQLTABLEPRIVILEGES]  = FALSE;
		}
    } else {

		if (globals.lie)
			*pfExists = TRUE;

		else {

			switch(fFunction) {
			case SQL_API_SQLALLOCCONNECT:     *pfExists = TRUE; break;
			case SQL_API_SQLALLOCENV:         *pfExists = TRUE; break;
			case SQL_API_SQLALLOCSTMT:        *pfExists = TRUE; break;
			case SQL_API_SQLBINDCOL:          *pfExists = TRUE; break;
			case SQL_API_SQLCANCEL:           *pfExists = TRUE; break;
			case SQL_API_SQLCOLATTRIBUTES:    *pfExists = TRUE; break;
			case SQL_API_SQLCONNECT:          *pfExists = TRUE; break;
			case SQL_API_SQLDESCRIBECOL:      *pfExists = TRUE; break;  // partial
			case SQL_API_SQLDISCONNECT:       *pfExists = TRUE; break;
			case SQL_API_SQLERROR:            *pfExists = TRUE; break;
			case SQL_API_SQLEXECDIRECT:       *pfExists = TRUE; break;
			case SQL_API_SQLEXECUTE:          *pfExists = TRUE; break;
			case SQL_API_SQLFETCH:            *pfExists = TRUE; break;
			case SQL_API_SQLFREECONNECT:      *pfExists = TRUE; break;
			case SQL_API_SQLFREEENV:          *pfExists = TRUE; break;
			case SQL_API_SQLFREESTMT:         *pfExists = TRUE; break;
			case SQL_API_SQLGETCURSORNAME:    *pfExists = TRUE; break;
			case SQL_API_SQLNUMRESULTCOLS:    *pfExists = TRUE; break;
			case SQL_API_SQLPREPARE:          *pfExists = TRUE; break;
			case SQL_API_SQLROWCOUNT:         *pfExists = TRUE; break;
			case SQL_API_SQLSETCURSORNAME:    *pfExists = TRUE; break;
			case SQL_API_SQLSETPARAM:         *pfExists = FALSE; break; // odbc 1.0
			case SQL_API_SQLTRANSACT:         *pfExists = TRUE; break;

				// ODBC level 1 functions
			case SQL_API_SQLBINDPARAMETER:    *pfExists = TRUE; break;
			case SQL_API_SQLCOLUMNS:          *pfExists = TRUE; break;
			case SQL_API_SQLDRIVERCONNECT:    *pfExists = TRUE; break;
			case SQL_API_SQLGETCONNECTOPTION: *pfExists = TRUE; break;  // partial
			case SQL_API_SQLGETDATA:          *pfExists = TRUE; break;
			case SQL_API_SQLGETFUNCTIONS:     *pfExists = TRUE; break;
			case SQL_API_SQLGETINFO:          *pfExists = TRUE; break;
			case SQL_API_SQLGETSTMTOPTION:    *pfExists = TRUE; break;  // partial
			case SQL_API_SQLGETTYPEINFO:      *pfExists = TRUE; break;
			case SQL_API_SQLPARAMDATA:        *pfExists = TRUE; break;
			case SQL_API_SQLPUTDATA:          *pfExists = TRUE; break;
			case SQL_API_SQLSETCONNECTOPTION: *pfExists = TRUE; break;  // partial
			case SQL_API_SQLSETSTMTOPTION:    *pfExists = TRUE; break;
			case SQL_API_SQLSPECIALCOLUMNS:   *pfExists = TRUE; break;
			case SQL_API_SQLSTATISTICS:       *pfExists = TRUE; break;
			case SQL_API_SQLTABLES:           *pfExists = TRUE; break;

				// ODBC level 2 functions
			case SQL_API_SQLBROWSECONNECT:    *pfExists = FALSE; break;
			case SQL_API_SQLCOLUMNPRIVILEGES: *pfExists = FALSE; break;
			case SQL_API_SQLDATASOURCES:      *pfExists = FALSE; break;  // only implemented by DM
			case SQL_API_SQLDESCRIBEPARAM:    *pfExists = FALSE; break;  // not properly implemented
			case SQL_API_SQLDRIVERS:          *pfExists = FALSE; break;  // only implemented by DM
			case SQL_API_SQLEXTENDEDFETCH:    *pfExists = TRUE; break;
			case SQL_API_SQLFOREIGNKEYS:      *pfExists = TRUE; break;
			case SQL_API_SQLMORERESULTS:      *pfExists = TRUE; break;
			case SQL_API_SQLNATIVESQL:        *pfExists = TRUE; break;
			case SQL_API_SQLNUMPARAMS:        *pfExists = TRUE; break;
			case SQL_API_SQLPARAMOPTIONS:     *pfExists = FALSE; break;
			case SQL_API_SQLPRIMARYKEYS:      *pfExists = TRUE; break;
			case SQL_API_SQLPROCEDURECOLUMNS: *pfExists = FALSE; break;
			case SQL_API_SQLPROCEDURES:       *pfExists = FALSE; break;
			case SQL_API_SQLSETPOS:           *pfExists = TRUE; break;
			case SQL_API_SQLSETSCROLLOPTIONS: *pfExists = TRUE; break;	// odbc 1.0
			case SQL_API_SQLTABLEPRIVILEGES:  *pfExists = FALSE; break;
			}
		}
    }

    return SQL_SUCCESS;
}



RETCODE SQL_API SQLTables(
                          HSTMT       hstmt,
                          UCHAR FAR * szTableQualifier,
                          SWORD       cbTableQualifier,
                          UCHAR FAR * szTableOwner,
                          SWORD       cbTableOwner,
                          UCHAR FAR * szTableName,
                          SWORD       cbTableName,
                          UCHAR FAR * szTableType,
                          SWORD       cbTableType)
{
static char *func = "SQLTables";
StatementClass *stmt = (StatementClass *) hstmt;
StatementClass *tbl_stmt;
TupleNode *row;
HSTMT htbl_stmt;
RETCODE result;
char *tableType;
char tables_query[MAX_STATEMENT_LEN];
char table_name[MAX_INFO_STRING], table_owner[MAX_INFO_STRING], relhasrules[MAX_INFO_STRING];
ConnInfo *ci;
char *prefix[32], prefixes[MEDIUM_REGISTRY_LEN];
char *table_type[32], table_types[MAX_INFO_STRING];
char show_system_tables, show_regular_tables, show_views;
char regular_table, view, systable;
int i;

mylog("%s: entering...stmt=%u\n", func, stmt);

	if( ! stmt) {
		SC_log_error(func, "", NULL);
		return SQL_INVALID_HANDLE;
	}

	stmt->manual_result = TRUE;
	stmt->errormsg_created = TRUE;

	ci = &stmt->hdbc->connInfo;

	result = SQLAllocStmt( stmt->hdbc, &htbl_stmt);
	if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errornumber = STMT_NO_MEMORY_ERROR;
		stmt->errormsg = "Couldn't allocate statement for SQLTables result.";
		SC_log_error(func, "", stmt);
		return SQL_ERROR;
	}
	tbl_stmt = (StatementClass *) htbl_stmt;

	// **********************************************************************
	//	Create the query to find out the tables
	// **********************************************************************

	strcpy(tables_query, "select relname, usename, relhasrules from pg_class, pg_user");
	strcat(tables_query, " where relkind = 'r'");

	my_strcat(tables_query, " and usename like '%.*s'", szTableOwner, cbTableOwner);
	my_strcat(tables_query, " and relname like '%.*s'", szTableName, cbTableName);


	//	Parse the extra systable prefix 
	strcpy(prefixes, globals.extra_systable_prefixes);
	i = 0;
	prefix[i] = strtok(prefixes, ";");
	while (prefix[i] && i<32) {
		prefix[++i] = strtok(NULL, ";");
	}

	/*	Parse the desired table types to return */
	show_system_tables = FALSE;
	show_regular_tables = FALSE;
	show_views = FALSE;

	/*	make_string mallocs memory */
	tableType = make_string(szTableType, cbTableType, NULL);
	if (tableType) {
		strcpy(table_types, tableType);
		free(tableType);
		i = 0;
		table_type[i] = strtok(table_types, ",");
		while (table_type[i] && i<32) {
			table_type[++i] = strtok(NULL, ",");
		}

		/*	Check for desired table types to return */
		i = 0;
		while (table_type[i]) {
			if ( strstr(table_type[i], "SYSTEM TABLE"))
				show_system_tables = TRUE;
			else if ( strstr(table_type[i], "TABLE"))
				show_regular_tables = TRUE;
			else if ( strstr(table_type[i], "VIEW"))
				show_views = TRUE;

			i++;
		}
	}
	else {
		show_regular_tables = TRUE;
		show_views = TRUE;
	}

	/*  If not interested in SYSTEM TABLES then filter them out
		to save some time on the query.  If treating system tables
		as regular tables, then dont filter either.
	*/
	if ( ! atoi(ci->show_system_tables) && ! show_system_tables) {
		strcat(tables_query, " and relname !~ '^" POSTGRES_SYS_PREFIX);

		/*	Also filter out user-defined system table types */
		i = 0;
		while(prefix[i]) {
			strcat(tables_query, "|^");
			strcat(tables_query, prefix[i]);
			i++;
		}

		strcat(tables_query, "'");
	}


	/*	filter out large objects unconditionally (they are not system tables) and match users */
	strcat(tables_query, " and relname !~ '^xinv[0-9]+'");
	strcat(tables_query, " and int4out(usesysid) = int4out(relowner)");
	strcat(tables_query, "order by relname");

	// **********************************************************************

	result = SQLExecDirect(htbl_stmt, tables_query, strlen(tables_query));
	if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errormsg = SC_create_errormsg(htbl_stmt);
		stmt->errornumber = tbl_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(htbl_stmt, SQL_DROP);
		return SQL_ERROR;
	}

    result = SQLBindCol(htbl_stmt, 1, SQL_C_CHAR,
                        table_name, MAX_INFO_STRING, NULL);
    if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errormsg = tbl_stmt->errormsg;
		stmt->errornumber = tbl_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(htbl_stmt, SQL_DROP);
        return SQL_ERROR;
    }

    result = SQLBindCol(htbl_stmt, 2, SQL_C_CHAR,
                        table_owner, MAX_INFO_STRING, NULL);
    if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errormsg = tbl_stmt->errormsg;
		stmt->errornumber = tbl_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(htbl_stmt, SQL_DROP);
        return SQL_ERROR;
    }
    result = SQLBindCol(htbl_stmt, 3, SQL_C_CHAR,
                        relhasrules, MAX_INFO_STRING, NULL);
    if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errormsg = tbl_stmt->errormsg;
		stmt->errornumber = tbl_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(htbl_stmt, SQL_DROP);
        return SQL_ERROR;
    }

	stmt->result = QR_Constructor();
	if(!stmt->result) {
		stmt->errormsg = "Couldn't allocate memory for SQLTables result.";
		stmt->errornumber = STMT_NO_MEMORY_ERROR;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(htbl_stmt, SQL_DROP);
		return SQL_ERROR;
	}

    // the binding structure for a statement is not set up until
    // a statement is actually executed, so we'll have to do this ourselves.
	extend_bindings(stmt, 5);
	
    // set the field names
	QR_set_num_fields(stmt->result, 5);
	QR_set_field_info(stmt->result, 0, "TABLE_QUALIFIER", PG_TYPE_TEXT, MAX_INFO_STRING);
	QR_set_field_info(stmt->result, 1, "TABLE_OWNER", PG_TYPE_TEXT, MAX_INFO_STRING);
	QR_set_field_info(stmt->result, 2, "TABLE_NAME", PG_TYPE_TEXT, MAX_INFO_STRING);
	QR_set_field_info(stmt->result, 3, "TABLE_TYPE", PG_TYPE_TEXT, MAX_INFO_STRING);
	QR_set_field_info(stmt->result, 4, "REMARKS", PG_TYPE_TEXT, 254);

    // add the tuples
	result = SQLFetch(htbl_stmt);
	while((result == SQL_SUCCESS) || (result == SQL_SUCCESS_WITH_INFO)) {

		/*	Determine if this table name is a system table.
			If treating system tables as regular tables, then 
			no need to do this test.
		*/		
		systable = FALSE;
		if( ! atoi(ci->show_system_tables)) {

			if ( strncmp(table_name, POSTGRES_SYS_PREFIX, strlen(POSTGRES_SYS_PREFIX)) == 0)
				systable = TRUE;

			else {			/* Check extra system table prefixes */
				i = 0;
				while (prefix[i]) {
					mylog("table_name='%s', prefix[%d]='%s'\n", table_name, i, prefix[i]);
					if (strncmp(table_name, prefix[i], strlen(prefix[i])) == 0) {
						systable = TRUE;
						break;
					}
					i++;
				}
			}
		}

		/*	Determine if the table name is a view */
		view = (relhasrules[0] == '1');

		/*	It must be a regular table */
		regular_table = ( ! systable && ! view);


		/*	Include the row in the result set if meets all criteria */
		/*	NOTE: Unsupported table types (i.e., LOCAL TEMPORARY, ALIAS, etc)
					will return nothing */
		if ( (systable && show_system_tables) ||
			 (view && show_views) || 
			 (regular_table && show_regular_tables)) {

			row = (TupleNode *)malloc(sizeof(TupleNode) + (5 - 1) * sizeof(TupleField));

			set_tuplefield_string(&row->tuple[0], "");

			// I have to hide the table owner from Access, otherwise it
			// insists on referring to the table as 'owner.table'.
			// (this is valid according to the ODBC SQL grammar, but
			// Postgres won't support it.)
			// set_tuplefield_string(&row->tuple[1], table_owner);

			mylog("SQLTables: table_name = '%s'\n", table_name);

			set_tuplefield_string(&row->tuple[1], "");
			set_tuplefield_string(&row->tuple[2], table_name);
			set_tuplefield_string(&row->tuple[3], systable ? "SYSTEM TABLE" : (view ? "VIEW" : "TABLE"));
			set_tuplefield_string(&row->tuple[4], "");

			QR_add_tuple(stmt->result, row);
		}
		result = SQLFetch(htbl_stmt);
    }
	if(result != SQL_NO_DATA_FOUND) {
		stmt->errormsg = SC_create_errormsg(htbl_stmt);
		stmt->errornumber = tbl_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(htbl_stmt, SQL_DROP);
		return SQL_ERROR;
	}

    // also, things need to think that this statement is finished so
    // the results can be retrieved.
	stmt->status = STMT_FINISHED;

    // set up the current tuple pointer for SQLFetch
	stmt->currTuple = -1;
	stmt->rowset_start = -1;
	stmt->current_col = -1;

	SQLFreeStmt(htbl_stmt, SQL_DROP);
	mylog("SQLTables(): EXIT,  stmt=%u\n", stmt);
	return SQL_SUCCESS;
}




RETCODE SQL_API SQLColumns(
                           HSTMT        hstmt,
                           UCHAR FAR *  szTableQualifier,
                           SWORD        cbTableQualifier,
                           UCHAR FAR *  szTableOwner,
                           SWORD        cbTableOwner,
                           UCHAR FAR *  szTableName,
                           SWORD        cbTableName,
                           UCHAR FAR *  szColumnName,
                           SWORD        cbColumnName)
{
static char *func = "SQLColumns";
StatementClass *stmt = (StatementClass *) hstmt;
TupleNode *row;
HSTMT hcol_stmt;
StatementClass *col_stmt;
char columns_query[MAX_STATEMENT_LEN];
RETCODE result;
char table_owner[MAX_INFO_STRING], table_name[MAX_INFO_STRING], field_name[MAX_INFO_STRING], field_type_name[MAX_INFO_STRING];
Int2 field_number, result_cols;
Int4 field_type, the_type, field_length, mod_length;
char not_null[MAX_INFO_STRING];
ConnInfo *ci;