nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to get columns list from the goiven SQL query?

littleoak

Hello all,

I need to create a report from the given SQL query. Is somehow possible to extract column names from my query(by DE API) and then dynamically create a report table composed with those columns?

Thanks so much

OD

Find more posts tagged with

Comments

littleoak

Hi

could you please give me an advice about the best approach how to create the dynamic report in the sense of passing a user defined SQL query? (How to dynamically create table with variable number of columns and names selected in the query etc.). I have studied a lot of stuff about BiRT but in the particular example(on birt-exchange.org) there is needed to pass at least arraylist with column names to show.

Thanks a lot and sorry for my beginner's question and sorry for my English.

kclark

You can modify the sql query in the beforeOpen() of the dataset doing something like this 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = "select * from CLASSICMODELS.CUSTOMERS";</pre>
 
If you wanted to add parameters to the query you could modify the above to look like this 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = "select " + params["SOMEPARAM"].value + " from CLASSICMODELS.CUSTOMERS";</pre>
 
Assuming the value of the parameter is a valid value for the query.

littleoak

First, thanks for your reply.

Yet another question: how to extract queried column names from the query (SELECT * FROM ...) which I need for the report table construction(set the table column names and databinding)? Is there a option how to create table automatically according to the dataSet?

Thanks in advance.

kclark

I haven't tried this yet but you could use <a class='bbc_url' href='http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/ResultSetMetaData.html'>ResultSetMetaData</a> to query the DB and then get the column names doing somthing like this 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>ResultSet rs = stmt.executeQuery("SELECT * FROM table");
ResultSetMetaData rsmd = rs.getMetaData();
String firstColumnName = rsmd.getColumnName(1);</pre>
 
You could create your own java class that takes the query in the constructor and then you could use this with the stmt.executeQuery.someQueryString