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

Posted on 2005-05-02
Last Modified: 2010-04-01
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}"/>

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.

Question by:sky_kirkham
    LVL 6

    Expert Comment

    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}"/>

    LVL 2

    Author Comment

    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.
    LVL 6

    Accepted Solution

    maybe this can help you:


    <%@ 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>

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

    at the bottom of this page:

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now