info.c

关系型数据库 Postgresql 6.5.2

	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;

	// **********************************************************************
	//	Create the query to find out the columns (Note: pre 6.3 did not have the atttypmod field)
	// **********************************************************************
	sprintf(columns_query, "select u.usename, c.relname, a.attname, a.atttypid,t.typname, a.attnum, a.attlen, %s, a.attnotnull from pg_user u, pg_class c, pg_attribute a, pg_type t where "
		"int4out(u.usesysid) = int4out(c.relowner) and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0)",
		PROTOCOL_62(ci) ? "a.attlen" : "a.atttypmod");

	my_strcat(columns_query, " and c.relname like '%.*s'", szTableName, cbTableName);
	my_strcat(columns_query, " and u.usename like '%.*s'", szTableOwner, cbTableOwner);
	my_strcat(columns_query, " and a.attname like '%.*s'", szColumnName, cbColumnName);

    // give the output in the order the columns were defined
    // when the table was created
    strcat(columns_query, " order by attnum");
	// **********************************************************************

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

	mylog("SQLColumns: hcol_stmt = %u, col_stmt = %u\n", hcol_stmt, col_stmt);

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

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

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

    result = SQLBindCol(hcol_stmt, 3, SQL_C_CHAR,
                        field_name, MAX_INFO_STRING, NULL);
    if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errormsg = col_stmt->errormsg;
		stmt->errornumber = col_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(hcol_stmt, SQL_DROP);
        return SQL_ERROR;
    }

    result = SQLBindCol(hcol_stmt, 4, SQL_C_LONG,
                        &field_type, 4, NULL);
    if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errormsg = col_stmt->errormsg;
		stmt->errornumber = col_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(hcol_stmt, SQL_DROP);
        return SQL_ERROR;
    }

    result = SQLBindCol(hcol_stmt, 5, SQL_C_CHAR,
                        field_type_name, MAX_INFO_STRING, NULL);
    if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errormsg = col_stmt->errormsg;
		stmt->errornumber = col_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(hcol_stmt, SQL_DROP);
        return SQL_ERROR;
    }

    result = SQLBindCol(hcol_stmt, 6, SQL_C_SHORT,
                        &field_number, MAX_INFO_STRING, NULL);
    if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errormsg = col_stmt->errormsg;
		stmt->errornumber = col_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(hcol_stmt, SQL_DROP);
        return SQL_ERROR;
    }

    result = SQLBindCol(hcol_stmt, 7, SQL_C_LONG,
                        &field_length, MAX_INFO_STRING, NULL);
    if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errormsg = col_stmt->errormsg;
		stmt->errornumber = col_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(hcol_stmt, SQL_DROP);
        return SQL_ERROR;
    }

    result = SQLBindCol(hcol_stmt, 8, SQL_C_LONG,
                        &mod_length, MAX_INFO_STRING, NULL);
    if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errormsg = col_stmt->errormsg;
		stmt->errornumber = col_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(hcol_stmt, SQL_DROP);
        return SQL_ERROR;
    }

    result = SQLBindCol(hcol_stmt, 9, SQL_C_CHAR,
                        not_null, MAX_INFO_STRING, NULL);
    if((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
		stmt->errormsg = col_stmt->errormsg;
		stmt->errornumber = col_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(hcol_stmt, SQL_DROP);
        return SQL_ERROR;
    }

    stmt->result = QR_Constructor();
    if(!stmt->result) {
		stmt->errormsg = "Couldn't allocate memory for SQLColumns result.";
        stmt->errornumber = STMT_NO_MEMORY_ERROR;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(hcol_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.
	result_cols = 14;
    extend_bindings(stmt, result_cols);

    // set the field names
    QR_set_num_fields(stmt->result, result_cols);
    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, "COLUMN_NAME", PG_TYPE_TEXT, MAX_INFO_STRING);
    QR_set_field_info(stmt->result, 4, "DATA_TYPE", PG_TYPE_INT2, 2);
    QR_set_field_info(stmt->result, 5, "TYPE_NAME", PG_TYPE_TEXT, MAX_INFO_STRING);
    QR_set_field_info(stmt->result, 6, "PRECISION", PG_TYPE_INT4, 4);
    QR_set_field_info(stmt->result, 7, "LENGTH", PG_TYPE_INT4, 4);
    QR_set_field_info(stmt->result, 8, "SCALE", PG_TYPE_INT2, 2);
    QR_set_field_info(stmt->result, 9, "RADIX", PG_TYPE_INT2, 2);
    QR_set_field_info(stmt->result, 10, "NULLABLE", PG_TYPE_INT2, 2);
    QR_set_field_info(stmt->result, 11, "REMARKS", PG_TYPE_TEXT, 254);

	//	User defined fields
    QR_set_field_info(stmt->result, 12, "DISPLAY_SIZE", PG_TYPE_INT4, 4);
	QR_set_field_info(stmt->result, 13, "FIELD_TYPE", PG_TYPE_INT4, 4);

	
	result = SQLFetch(hcol_stmt);


	/*	Only show oid if option AND there are other columns AND 
		its not being called by SQLStatistics .
		Always show OID if its a system table
	*/

	if (result != SQL_ERROR && ! stmt->internal) {

		if (atoi(ci->show_oid_column) || strncmp(table_name, POSTGRES_SYS_PREFIX, strlen(POSTGRES_SYS_PREFIX)) == 0) {

			/*	For OID fields */
			the_type = PG_TYPE_OID;
			row = (TupleNode *)malloc(sizeof(TupleNode) +
									  (result_cols - 1) * sizeof(TupleField));

			set_tuplefield_string(&row->tuple[0], "");
			// see note in SQLTables()
			//      set_tuplefield_string(&row->tuple[1], table_owner);
			set_tuplefield_string(&row->tuple[1], "");
			set_tuplefield_string(&row->tuple[2], table_name);
			set_tuplefield_string(&row->tuple[3], "oid");
			set_tuplefield_int2(&row->tuple[4], pgtype_to_sqltype(stmt, the_type));
			set_tuplefield_string(&row->tuple[5], "OID");

			set_tuplefield_int4(&row->tuple[7], pgtype_length(stmt, the_type, PG_STATIC, PG_STATIC));
			set_tuplefield_int4(&row->tuple[6], pgtype_precision(stmt, the_type, PG_STATIC, PG_STATIC));

			set_nullfield_int2(&row->tuple[8], pgtype_scale(stmt, the_type));
			set_nullfield_int2(&row->tuple[9], pgtype_radix(stmt, the_type));
			set_tuplefield_int2(&row->tuple[10], SQL_NO_NULLS);
			set_tuplefield_string(&row->tuple[11], "");

			set_tuplefield_int4(&row->tuple[12], pgtype_display_size(stmt, the_type, PG_STATIC, PG_STATIC));
			set_tuplefield_int4(&row->tuple[13], the_type);

			QR_add_tuple(stmt->result, row);
		}

	}

    while((result == SQL_SUCCESS) || (result == SQL_SUCCESS_WITH_INFO)) {
        row = (TupleNode *)malloc(sizeof(TupleNode) +
                                  (result_cols - 1) * sizeof(TupleField));


        set_tuplefield_string(&row->tuple[0], "");
        // see note in SQLTables()
        //      set_tuplefield_string(&row->tuple[1], table_owner);
        set_tuplefield_string(&row->tuple[1], "");
        set_tuplefield_string(&row->tuple[2], table_name);
        set_tuplefield_string(&row->tuple[3], field_name);
        set_tuplefield_int2(&row->tuple[4], pgtype_to_sqltype(stmt, field_type));
        set_tuplefield_string(&row->tuple[5], field_type_name);


		/*	Some Notes about Postgres Data Types:

			VARCHAR - the length is stored in the pg_attribute.atttypmod field
			BPCHAR  - the length is also stored as varchar is

		*/
        if((field_type == PG_TYPE_VARCHAR) ||
		   (field_type == PG_TYPE_BPCHAR)) {

			if (mod_length >= 4)
				mod_length -= 4;			// the length is in atttypmod - 4

			if (mod_length > globals.max_varchar_size || mod_length <= 0)
				mod_length = globals.max_varchar_size;

			mylog("SQLColumns: field type is VARCHAR,BPCHAR: field_type = %d, mod_length = %d\n", field_type, mod_length);

            set_tuplefield_int4(&row->tuple[7], mod_length);
			set_tuplefield_int4(&row->tuple[6], mod_length);
			set_tuplefield_int4(&row->tuple[12], mod_length);
        } else {
			mylog("SQLColumns: field type is OTHER: field_type = %d, pgtype_length = %d\n", field_type, pgtype_length(stmt, field_type, PG_STATIC, PG_STATIC));

            set_tuplefield_int4(&row->tuple[7], pgtype_length(stmt, field_type, PG_STATIC, PG_STATIC));
			set_tuplefield_int4(&row->tuple[6], pgtype_precision(stmt, field_type, PG_STATIC, PG_STATIC));
			set_tuplefield_int4(&row->tuple[12], pgtype_display_size(stmt, field_type, PG_STATIC, PG_STATIC));

        }

		set_nullfield_int2(&row->tuple[8], pgtype_scale(stmt, field_type));
		set_nullfield_int2(&row->tuple[9], pgtype_radix(stmt, field_type));
		set_tuplefield_int2(&row->tuple[10], (Int2) (not_null[0] == '1' ? SQL_NO_NULLS : pgtype_nullable(stmt, field_type)));
		set_tuplefield_string(&row->tuple[11], "");
		set_tuplefield_int4(&row->tuple[13], field_type);

        QR_add_tuple(stmt->result, row);


        result = SQLFetch(hcol_stmt);

    }
    if(result != SQL_NO_DATA_FOUND) {
		stmt->errormsg = SC_create_errormsg(hcol_stmt);
		stmt->errornumber = col_stmt->errornumber;
		SC_log_error(func, "", stmt);
		SQLFreeStmt(hcol_stmt, SQL_DROP);
        return SQL_ERROR;
    }

	//	Put the row version column at the end so it might not be
	//	mistaken for a key field.
	if ( ! stmt->internal && atoi(ci->row_versioning)) {
		/*	For Row Versioning fields */
		the_type = PG_TYPE_INT4;

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

		set_tuplefield_string(&row->tuple[0], "");
		set_tuplefield_string(&row->tuple[1], "");
		set_tuplefield_string(&row->tuple[2], table_name);
		set_tuplefield_string(&row->tuple[3], "xmin");
		set_tuplefield_int2(&row->tuple[4], pgtype_to_sqltype(stmt, the_type));
		set_tuplefield_string(&row->tuple[5], pgtype_to_name(stmt, the_type));
		set_tuplefield_int4(&row->tuple[6], pgtype_precision(stmt, the_type, PG_STATIC, PG_STATIC));
		set_tuplefield_int4(&row->tuple[7], pgtype_length(stmt, the_type, PG_STATIC, PG_STATIC));
		set_nullfield_int2(&row->tuple[8], pgtype_scale(stmt, the_type));
		set_nullfield_int2(&row->tuple[9], pgtype_radix(stmt, the_type));
		set_tuplefield_int2(&row->tuple[10], SQL_NO_NULLS);
		set_tuplefield_string(&row->tuple[11], "");
		set_tuplefield_int4(&row->tuple[12], pgtype_display_size(stmt, the_type, PG_STATIC, PG_STATIC));
		set_tuplefield_int4(&row->tuple[13], the_type);

		QR_add_tuple(stmt->result, row);
	}

    // 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(hcol_stmt, SQL_DROP);
	mylog("SQLColumns(): EXIT,  stmt=%u\n", stmt);
    return SQL_SUCCESS;
}

RETCODE SQL_API SQLSpecialColumns(
                                  HSTMT        hstmt,
                                  UWORD        fColType,
                                  UCHAR FAR *  szTableQualifier,
                                  SWORD        cbTableQualifier,
                                  UCHAR FAR *  szTableOwner,
                                  SWORD        cbTableOwner,
                                  UCHAR FAR *  szTableName,
                                  SWORD        cbTableName,
                                  UWORD        fScope,
                                  UWORD        fNullable)
{
static char *func = "SQLSpecialColumns";
TupleNode *row;
StatementClass *stmt = (StatementClass *) hstmt;
ConnInfo *ci;


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

    if( ! stmt) {
		SC_log_error(func, "", NULL);
        return SQL_INVALID_HANDLE;
    }
	ci = &stmt->hdbc->connInfo;

	stmt->manual_result = TRUE;
    stmt->result = QR_Constructor();
    extend_bindings(stmt, 8);

    QR_set_num_fields(stmt->result, 8);
    QR_set_field_info(stmt->result, 0, "SCOPE", PG_TYPE_INT2, 2);
    QR_set_field_info(stmt->result, 1, "COLUMN_NAME", PG_TYPE_TEXT, MAX_INFO_STRING);
    QR_set_field_info(stmt->result, 2, "DATA_TYPE", PG_TYPE_INT2, 2);
    QR_set_field_info(stmt->result, 3, "TYPE_NAME", PG_TYPE_TEXT, MAX_INFO_STRING);
    QR_set_field_info(stmt->result, 4, "PRECISION", PG_TYPE_INT4, 4);
    QR_set_field_info(stmt->result, 5, "LENGTH", PG_TYPE_INT4, 4);
    QR_set_field_info(stmt->result, 6, "SCALE", PG_TYPE_INT2, 2);
    QR_set_field_info(stmt->result, 7, "PSEUDO_COLUMN", PG_TYPE_INT2, 2);

    /* use the oid value for the rowid */
    if(fColType == SQL_BEST_ROWID) {
        row = (TupleNode *)malloc(sizeof(TupleNode) + (8 - 1) * sizeof(TupleField));

        set_tuplefield_int2(&row->tuple[0], SQL_SCOPE_SESSION);
        set_tuplefield_string(&row->tuple[1], "oid");
        set_tuplefield_int2(&row->tuple[2], pgtype_to_sqltype(stmt, PG_TYPE_OID));
        set_tuplefield_string(&row->tuple[3], "OID");
        set_tuplefield_int4(&row->tuple[4], pgtype_precision(stmt, PG_TYPE_OID, PG_STATIC, PG_STATIC));
        set_tuplefield_int4(&row->tuple[5], pgtype_length(stmt, PG_TYPE_OID, PG_STATIC, PG_STATIC));
        set_tuplefield_int2(&row->tuple[6], pgtype_scale(stmt, PG_TYPE_OID));
        set_tuplefield_int2(&row->tuple[7], SQL_PC_PSEUDO);

        QR_add_tuple(stmt->result, row);

    } else if(fColType == SQL_ROWVER) {

		Int2 the_type = PG_TYPE_INT4;

		if (atoi(ci->row_versioning)) {
			row = (TupleNode *)malloc(sizeof(TupleNode) + (8 - 1) * sizeof(TupleField));

			set_tuplefield_null(&row->tuple[0]);
			set_tuplefield_string(&row->tuple[1], "xmin");
			set_tuplefield_int2(&row->tuple[2], pgtype_to_sqltype(stmt, the_type));
			set_tuplefield_string(&row->tuple[3], pgtype_to_name(stmt, the_type));
			set_tuplefield_int4(&row->tuple[4], pgtype_precision(stmt, the_type, PG_STATIC, PG_STATIC));
			set_tuplefield_int4(&row->tuple[5], pgtype_length(stmt, the_type, PG_STATIC, PG_STATIC));
			set_tuplefield_int2(&row->tuple[6], pgtype_scale(stmt, the_type));
			set_tuplefield_int2(&row->tuple[7], SQL_PC_PSEUDO);

			QR_add_tuple(stmt->result, row);
		}