Win a copy of Practical SVG this week in the HTML/CSS/JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

simple select returns null

 
B Bruford
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Very new at this. I have an HTML form getting inserted into an Oracle table. After the insert, I redirect to a JSP that sends an email. This much of it works. However - before the email gets sent, I need to do a select on the table to get the ID of the inserted record (the ID field has a sequence) so that I can put the ID number in the email. I'm getting this error:
Problem with Tags: java.lang.NumberFormatException: null jdbc racle:thin:air/air@172.20.96.10:1521:sunfire oracle.jdbc.driver.OracleDriver Select max(accident_id) id from air_accidents null
There is data in the table, but I always get a null. I even tried selecting different fields - same result.
Here is how I'm doing the select:
<%
Long id = null;
String sql = "";
try {
%>
<req:existsParameter name="id">
<%id = new Long(request.getParameter("id"));
%>
</req:existsParameter>
<%
} catch (Exception e) {
out.print("Problem with Tags: " + e);
}
%>
<sql:connection id="conn1">
<sql:url>jdbc racle:thin:air/air@172.20.96.10:1521:sunfire</sql:url>
<sql river>oracle.jdbc.driver.OracleDriver</sql river>
</sql:connection>
<%
sql = "Select max(accident_id) id from air_accidents";
%>
<sql :p reparedStatement id="stmt1" conn="conn1">
<sql:query>
<%=sql%>
</sql:query>
<sql:execute>
<sql:setColumn position="1"><%=id%></sql:setColumn>
</sql:execute>
<sql:resultSet id="rs">
<sql:getColumn position="1"/>
<sql:wasNull>null</sql:wasNull>
</sql:resultSet>
</sql :p reparedStatement>
What gives? What am I missing/doing wrong?
[ January 12, 2004: Message edited by: B Bruford ]
 
Bear Bibeault
Author and ninkuma
Marshal
Posts: 65535
108
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
B, it would be advisable to click the 'disable smilies' checkbox on your posts that contain code. In fact, I'd advise you to go back and edit your original post to eliminate the 'smilies'.
bear
 
B Bruford
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you.
 
Eric Pascarello
author
Rancher
Posts: 15385
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your code is still messed up since the smiles also reformat the code.
Now how often is this page being hit, because you can run into a problem if you have two people using it at the same time by using MAX.
You could have two emails going out with the same id...
I personaly would return a value from the time that you do an insert so you do not run into this problem (My 2 cents)
Is this what you meant for your select statement?
sql = "SELECT MAX(accident_id) AS max_id FROM air_accidents";
 
B Bruford
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I actually had the idea of doing the select after the insert, but I couldn't get it to work there either. I would rather do it that way.
Here's the non-smiley code:
<%
Long id = null;
String sql = "";
try {
%>
<req:existsParameter name="id">
<%id = new Long(request.getParameter("id"));
%>
</req:existsParameter>
<%
} catch (Exception e) {
out.print("Problem with Tags: " + e);
}
%>
<sql:connection id="conn1">
<sql:url>jdbcracle:thin:air/air@172.20.96.10:1521:sunfire</sql:url>
<sql:driver>oracle.jdbc.driver.OracleDriver</sql:driver>
</sql:connection>
<%
sql = "Select max(accident_id) id from air_accidents";
%>
<sql:preparedStatement id="stmt1" conn="conn1">
<sql:query>
<%=sql%>
</sql:query>
<sql:execute>
<sql:setColumn position="1"><%=id%></sql:setColumn>
</sql:execute>
<sql:resultSet id="rs">
<sql:getColumn position="1"/>
</sql:resultSet>
</sql:preparedStatement>
 
B Bruford
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I slightly modified the code (and moved it to the end of the JSP that does the insert) and got a slighty modified error message, but I'm still no closer to figuring out why I'm getting a null:
<%
Long max_id = null;
String sql2 = "";
try {
%>
<req:existsParameter name="max_id">
<%max_id = new Long(request.getParameter("max_id"));
%>
</req:existsParameter>
<%
sql2 = "SELECT MAX(accident_id) max_id FROM air_accidents";
%>
<sql:preparedStatement id="stmt2" conn="conn1">
<sql:query>
<%=sql2%>
</sql:query>
<sql:execute>
<sql:setColumn position="1"><%=max_id%></sql:setColumn>
</sql:execute>
<sql:resultSet id="rs">
<sql:getColumn position="1"/>
</sql:resultSet>
</sql:preparedStatement><%
} catch (Exception e) {
out.print("Problem with Tags: " + e);
}
%>

Problem with Tags: java.lang.NumberFormatException: null
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!