|
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); |
|
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:
It seems that SQL Anywhere’s 'implementation-defined returned value' is an empty string 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:
(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
|
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.
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.