Thread: Table Aliases

Table Aliases

From
"John C. Frickson"
Date:
Assume the following query:

  SELECT * FROM some_schema_name.some_table_name a
    LEFT JOIN other_schame_name.some_table_name b
    ON a.Foo=b.Bar

Notice that the schemas are different, but the table names are the same.
Now suppose both tables have an "Id" field. As I go through the columns,
I call (among other things):

  SQLColAttribute(hstmt, colnum, SQL_DESC_BASE_TABLE_NAME, (UCHAR*)tablename, sizeof(tablename), <h, NULL);
  SQLColAttribute(hstmt, colnum, SQL_DESC_TABLE_NAME, (UCHAR*)alias, sizeof(alias), <h, NULL);

The first call (SQL_DESC_BASE_TABLE_NAME) would return "some_table_name"
for the Id columns in both schemas.

When MySQL is the backend database, the second call (SQL_DESC_TABLE_NAME)
would return "a" and "b" for the to Id columns, i.e. the table aliases.
With PostgreSQL, the second call also returns "some_table_name". I can't
find any attribute that has the table alias.

Is there a way to get the table alias, or do I just have to code around it
somehow?

Thanks,
John




Re: Table Aliases

From
Hiroshi Inoue
Date:
(2014/06/10 5:30), John C. Frickson wrote:
> Assume the following query:
>
>    SELECT * FROM some_schema_name.some_table_name a
>      LEFT JOIN other_schame_name.some_table_name b
>      ON a.Foo=b.Bar
>
> Notice that the schemas are different, but the table names are the same.
> Now suppose both tables have an "Id" field. As I go through the columns,
> I call (among other things):
>
>    SQLColAttribute(hstmt, colnum, SQL_DESC_BASE_TABLE_NAME, (UCHAR*)tablename, sizeof(tablename), <h, NULL);
>    SQLColAttribute(hstmt, colnum, SQL_DESC_TABLE_NAME, (UCHAR*)alias, sizeof(alias), <h, NULL);
>
> The first call (SQL_DESC_BASE_TABLE_NAME) would return "some_table_name"
> for the Id columns in both schemas.
>
> When MySQL is the backend database, the second call (SQL_DESC_TABLE_NAME)
> would return "a" and "b" for the to Id columns, i.e. the table aliases.
> With PostgreSQL, the second call also returns "some_table_name". I can't
> find any attribute that has the table alias.
>
> Is there a way to get the table alias, or do I just have to code around it
> somehow?

It's not that easy to get the information for the driver and you can't
get if using the current driver unfortunately.
Servers return various infromations like column alias, table oid etc
in response to DESCRIBE request but don't return table alias.

regards,
Hiroshi Inoue