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.
LVL 2
sky_kirkhamAsked:
Who is Participating?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.