I have to read an xml file, display its content in the form of a table and need to store the data in database table. Firstly I tried to only display the data using jstl1.2 tags in my jsp. It is working fine. But when I tried to implement some code to insert the records I am getting this error..
Can't infer the SQL type to use for an instance of org.apache.taglibs.standard.tag.common.xml.JSTLNodeList. Use setObject() with an explicit Types value to specify the type to use.
My Jsp code is as:
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/xml" prefix="x" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<sql:setDataSource var="dataSource" driver="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5432/postgres" user="postgres" password="shail"
scope="session" />
<html>
<head>
<title>cust_xml</title>
</head>
<body>
<h1>Customer Information</h1>
<c:import var="xmlFile" url="cust.xml" charEncoding="UTF-8"/>
<x:parse var="myDoc" xml="${xmlFile}" />
<table border="1">
<tr>
<th>Customer Number</th>
<th>First Name</th>
<th>Last Name</th>
<th>Street</th>
<th>City</th>
<th>State</th>
<th>Zipcode</th>
<th>Balance</th>
</tr>
<x:forEach select="$myDoc/CUSTOMERS/Customer" var="cust">
<tr>
<td>
<x:out select="$cust/CustNo" />
<x:set var="custno" select="$cust/CustNo" scope="session"/>
</td>
<td>
<x:out select="$cust/CustFirstName" />
<x:set var="custfn" select="$cust/CustFirstName" scope="session"/>
</td>
<td>
<x:out select="$cust/CustLastName" />
<x:set var="custln" select="$cust/CustLastName" scope="session"/>
</td>
<td>
<x:out select="$cust/CustStreet" />
<x:set var="custst" select="$cust/CustStreet" scope="session"/>
</td>
<td>
<x:out select="$cust/CustCity" />
<x:set var="custcity" select="$cust/CustCity" scope="session"/>
</td>
<td>
<x:out select="$cust/CustState" />
<x:set var="custstate" select="$cust/CustState" scope="session"/>
</td>
<td>
<x:out select="$cust/CustZip" />
<x:set var="custz" select="$cust/CustZip" scope="session"/>
</td>
<td>
<x:out select="$cust/CustBal" />
<x:set var="custbal" select="$cust/CustBal" scope="session"/>
</td>
</tr>
</x:forEach>
</table>
<sql:update dataSource="${dataSource}" var="updatedTable">
INSERT INTO data VALUES (?,?,?,?,?,?,?,?);
<sql:param value="${custno}" />
<sql:param value="${custfn}" />
<sql:param value="${custln}" />
<sql:param value="${custst}" />
<sql:param value="${custcity}" />
<sql:param value="${custstate}" />
<sql:param value="${custz}" />
<sql:param value="${custbal}" />
</sql:update>
<c:if test="${updatedTable>=1}">
<font size="5" color='green'> Congratulations ! Data inserted successfully.</font>
</c:if>
</body>
</html>
Please provide your suggestion which tag shall I use to set the values to insert query parameters? How to get the xml elements data to set in query parameters?
Thanks & Regards
I tried hard to get the solution of my problem but unfortunately couldn't get any help.
While googling I got some basic idea to cope with this. I tried this
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/xml" prefix="x" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<sql:setDataSource var="dataSource" driver="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5432/postgres" user="postgres" password="shail"
scope="session" />
<html>
<head>
<title>cust_xml</title>
</head>
<body bgcolor="skyblue">
<h1 align="center">Customer Information Stored In XMl File</h1>
<c:import var="xmlFile" url="cust.xml" charEncoding="UTF-8"/>
<x:parse var="myDoc" xml="${xmlFile}" />
<table border="1" cellspacing="1" cellpadding="1" bgcolor="pink" align="center">
<tr>
<th>Customer Number</th>
<th>First Name</th>
<th>Last Name</th>
<th>Street</th>
<th>City</th>
<th>State</th>
<th>Zipcode</th>
<th>Balance</th>
</tr>
<x:forEach select="$myDoc/CUSTOMERS/Customer" var="cust">
<tr>
<td>
<c:set var="custno" >
<x:out select="$cust/CustNo" />
</c:set>
<x:out select="$cust/CustNo" />
</td>
<td>
<c:set var="custfn" >
<x:out select="$cust/CustFirstName" />
</c:set>
<x:out select="$cust/CustFirstName" />
</td>
<td>
<c:set var="custln" >
<x:out select="$cust/CustLastName" />
</c:set>
<x:out select="$cust/CustLastName" />
</td>
<td>
<c:set var="custst" >
<x:out select="$cust/CustStreet" />
</c:set>
<x:out select="$cust/CustStreet" />
</td>
<td>
<c:set var="custcity" >
<x:out select="$cust/CustCity" />
</c:set>
<x:out select="$cust/CustCity" />
</td>
<td>
<c:set var="custstate" >
<x:out select="$cust/CustState" />
</c:set>
<x:out select="$cust/CustState" />
</td>
<td>
<c:set var="custz" >
<x:out select="$cust/CustZip" />
</c:set>
<x:out select="$cust/CustZip" />
</td>
<td>
<c:set var="custbal" >
<x:out select="$cust/CustBal" />
</c:set>
<x:out select="$cust/CustBal" />
</td>
</tr>
<sql:update dataSource="${dataSource}" var="updatedTable">
INSERT INTO data VALUES (?,?,?,?,?,?,?,?);
<sql:param value="${custno}" />
<sql:param value="${custfn}" />
<sql:param value="${custln}" />
<sql:param value="${custst}" />
<sql:param value="${custcity}" />
<sql:param value="${custstate}" />
<sql:param value="${custz}" />
<sql:param value="${custbal}" />
</sql:update>
<c:set var="rowcount" value="${updatedTable+rowcount}"/>
</x:forEach>
<c:if test="${rowcount>=1}">
<tr>
<td colspan="8" align="center">
<font size="5" color='red' align="center">
Congratulations ! <c:out value="${rowcount}" /> Rows inserted successfully in DB Table.</font>
</td>
</tr>
</c:if>
</table>
</body>
</html>
But I got this error:
org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException:
INSERT INTO data VALUES (?,?,?,?,?,?,?,?);
ERROR: column "balance" is of type integer but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
As in my table balance(last column) is integer type.
When I googled I got that jstl tags automatically take care of casting. But it is showing error as by default <c:set > tag places value for variable as string.
Any idea? Any help? Any suggestion? Please let me know.
You can enclose the ? with CAST function and still use setObject() method.
Let me give you an example
While storing IP address in MySQL my query use to be like this
INSERT INTO log (ip,logged_on) VALUES(inet6_aton(?),no());
PS: inet6_aton() for MySQL 5.6+ else use inet4_aton() or just inet_aton()
Now I shifted to Postgres and it doesn't have inet_aton function but inet as a datatype. Unfortunately, there is not setInet() method so I'd to do this
INSERT INTO log (ip,looged_on) VALUES(CAST(? AS INET),now());
With this I can use setString() or setObject() while passing the parameters in JDBC or with JSTL
INSERT INTO data VALUES (?,?,?,?,?,?,?,?);
Since you haven't mentioned the column names like I did in my example I don't know which of the column needs to be typecast but you can try by writing it like this
INSERT INTO data VALUES (CAST(? AS INTEGER),?,?,?,?,?,?,?);
I'm just assuming the first column of your table as balance please do this for the appropriate parameter depending upon the position of balance column in your table.
