[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Setting column names in a <sql:query> statement on the fly

Hi, I was wondering if anyone knows a way to set the column names in a query on the fly in a <sql:query> statement.

When I try using the <sql:param> method, I get an error message due to the ' ' which are put around a String parameter.

For example I want to have the following work:

<c:set var="column2" value="cl2"/>
<c:set var="column3" value="cl3"/>
<c:set var="tblName" value="tblTest"/>

<sql query datasource="${xxx}" sql="SELECT ?, ?, ? FROM ?;">
   <sql:param value="column1"/>
   <sql:param value="${column2}"/>
   <sql:param value="${column3}"/>
   <sql:param value="${tblName}"/>
</sql:query>

But instead I get a SQL error (using MySQL 4.1) due to the SQL sent looking like:
Select 'column1', 'cl2', 'cl3' FROM 'tblTest';

Is there another way of setting the parameter values, or alternatively of stripping the ' ' from the text.

Thanks,
Sky.
0
sky_kirkham
Asked:
sky_kirkham
  • 2
1 Solution
 
manuel_mCommented:
Maybe you can set the column names with <c:out value=""/> ? (did not tested it and have no possibility to test it now!)

try this:

<c:set var="column1" value="cl1"/>
<c:set var="column2" value="cl2"/>
<c:set var="column3" value="cl3"/>
<c:set var="tblName" value="tblTest"/>

<sql query datasource="${xxx}" sql="SELECT <c:out value='${column1}'/>, <c:out value='${column2}'/>, <c:out value='${column3}'/> FROM <c:out value='${tblName}'/>;">
   <sql:param value="column1"/>
   <sql:param value="${column2}"/>
   <sql:param value="${column3}"/>
   <sql:param value="${tblName}"/>
</sql:query>

Manuel
0
 
sky_kirkhamAuthor Commented:
It looks like that's not the way either unfortunately. As far as I can tell jstl doesn't like nested statements. I tried setting the <sql:param value = "${<c:out value=column1/>}"/> but it gives an "Expected =" error. Similar error when you add the statement into the SQL, ie: SELECT ${<c:out value=column1/>},

I tried something closer to the original suggestion, but without encasing the <c:out> statement in ${} it isn't interpreted at all and is simply passes through as that string.

Thanks for the suggestion though.
Sky.
0
 
manuel_mCommented:
maybe this can help you:

select.tag

<%@ tag body-content="scriptless" %>
<%@ attribute name="var" required="true" %>
<%@ variable name-from-attribute="var"
    alias="v_row" scope="NESTED" %>
<%@ attribute name="table"   required="true"  %>
<%@ attribute name="columns" required="false" %>
<%@ attribute name="where"   required="false" %>
<%@ attribute name="groupBy" required="false" %>
<%@ attribute name="having"  required="false" %>
<%@ attribute name="orderBy" required="false" %>
<%@ include file="init.tagf" %>

<sql:query dataSource="${tags_db_dataSource}" var="v_result">
    SELECT ${empty columns ? "*" : columns} FROM ${table}
    <c:if test="${!empty where}">   WHERE    ${where}   </c:if>
    <c:if test="${!empty groupBy}"> GROUP BY ${groupBy} </c:if>
    <c:if test="${!empty having}">  HAVING   ${having}  </c:if>
    <c:if test="${!empty orderBy}"> ORDER BY ${orderBy} </c:if>
</sql:query>

<c:forEach var="v_row" items="${v_result.rows}">
    <jsp:doBody/>
</c:forEach>

at the bottom of this page: http://www.oracle.com/technology/pub/articles/cioroianu_tagfiles.html
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now