Table name is not shown to end user if result set contains a query with alias in stored procedure. for example procedure contains a result set with some alias for e.g select count(*) as count1 from tab;

code snippet:(After procedure execution and got the resutlset) ResultSetMetaData rsMetadata = rs.getMetaData(); String tableName = rsMetadata.getTableName(column_index);

asked 06 Aug '13, 05:49

suraj786's gravatar image

suraj786
31113
accept rate: 0%

edited 07 Aug '13, 05:52

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676

Do you expect the client interface to peer inside a stored procedure to see how the result set is constructed? A stored procedure can be arbitrarily complex.

(06 Aug '13, 08:44) Breck Carter

Yes, I am working on an ETL tool where its a requirement to show the schema of stored procedure.What ever I can get ,w.r.t. stored procedure, through JDBC driver, I need to show that.

(07 Aug '13, 00:25) suraj786

There are two points to note here:


Table names associated with columns are not preserved when the table is composed by the stored procedure.

Normally, a result set from a stored procedure will not have table names attached to the columns. However, if the stored procedure always results in the same, single select statement; the optimizer may inline the select statement. In this case the associated table names will be preserved (for normal, non-expression, columns).


If the column is an expression, no table name should be associated it

The table name of an aliased column can be determined, but any column made up of an expression does not return a table name (aliased or not). Your example “select count(*) as count1 from tab” returns no table name (empty string) for column 1, even when the alias is removed.

I think we are seeing expected behavior here. JDBC is supposed to have a ‘behavior consistent with ODBC’, and the corresponding ODBC SQLColAttribute ‘SQL_DESC_TABLE_NAME’ is described as:

"The name of the table that contains the column. The returned value is implementation-defined if the column is an expression or if the column is part of a view. If the table name cannot be determined, an empty string is returned."

It seems that SQL Anywhere’s 'implementation-defined returned value' is an empty string


permanent link

answered 06 Aug '13, 11:19

Mikel%20Rychliski's gravatar image

Mikel Rychliski
2.1k1641
accept rate: 30%

edited 07 Aug '13, 11:58

Does this discussion apply to a stored procedure call?

(06 Aug '13, 12:01) Breck Carter

When I tested this I was able to get the correct table name (on normal, non-count columns) back through the stored procedure. Although I only tested with a very simple stored procedure, containing only a select statement. I'm not sure how it would behave with more complex result sets created by the stored procedure.

(06 Aug '13, 12:16) Mikel Rychliski
Replies hidden

@Mikel: Yes it only occurs when select statement have the expression or build in function as shown in the example.For simple select statements having column names, it is working fine.

(07 Aug '13, 00:38) suraj786
Replies hidden
1

If the meta data returned by JDBC are the same as when using sa_describe_query(), it seems that table names are returned when a stored procedure just consists of one single SELECT statement.

When using any logic (say, an IF statement) or further statements (say, declarations) or using multiple SELECT statements, the table names are not returned.


Note: One has to use "SELECT FROM MyStoredProc()" to use a stored procedure with sa_describe_query() - for example, used in the sample database:

call sa_describe_query('select * from ShowSalesOrders(101)')

This is a stored procedure consisting of a single SELECT statement, so it returns values in row "base_table_name" for most columns (except the computed ones) from the select list...

(07 Aug '13, 04:42) Volker Barth
Replies hidden
2

Hm, looks like this test result fits the definition of procedures that can be "inlined" - cf. this doc page:

You can inline a stored procedure that is defined only as a single SELECT statement when calling it in the FROM clause of a query. When a procedure is inlined, it is rewritten as a derived table. This tip does not apply to procedures that use default arguments, that contain anything other than a single SELECT statement in the body.

(07 Aug '13, 05:50) Volker Barth

This seems to be the case. Although, at least on 16, you can have slightly more than a single select statement in the procedure. But if anything could affect the result set, it does not preserve the table name. For example, an IF statement that always performs same select on both branches works, but if the result could be one of two select statements it does not. I agree with Volker that this is only working because the optimizer is reducing the procedure down to a single select statement and evaluating it inline

(07 Aug '13, 09:29) Mikel Rychliski
1

Because the column is generated by the expression, and didn't originate from a specific table, the correct result is for no table name to be attached to it. Also as Breck and Volker pointed out, you should be cautious checking the table names of columns from a stored procedure. If the procedure gets even a little more complicated, you will lose access to the table name metadata.

(07 Aug '13, 09:33) Mikel Rychliski

@Volker/Mikel: You should get together and post this material as an answer. Kudos to Volker, it's hard to find that material if you don't remember the keyword "inlined"... and AFAIK there is no mention of this affecting functionality, just performance.

(07 Aug '13, 11:32) Breck Carter

@Mikel: No need to make the answer a "community wiki" one - we do feel to edit answers anyway - and you should accept your rep points:)

(07 Aug '13, 12:11) Volker Barth

Okay, feel free to add any other detail you see fit!

(07 Aug '13, 12:41) Mikel Rychliski
3

A stored procedure with a single SELECT statement is inlined. This will not work with any non-inlinable stored procedure, and inlining is implementation-defined.

(08 Aug '13, 11:14) Elmi Eflov
showing 4 of 11 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×67
×17

question asked: 06 Aug '13, 05:49

question was seen: 2,285 times

last updated: 08 Aug '13, 11:14