Solved

NULL value problem (URGENT please!)

Posted on 2003-12-04
6
216 Views
Last Modified: 2010-04-01
From my JSP program I need to execute an SQL insert statement:

***JSP file**
<%
String st1;
...
...//some work done on st1
..

count = stmt.executeUpdate("insert into mytab (f1, f2,..) values ('st1', ...,..)");

Here when I execute above statement, I do not know if string "st1" has some value or if it is null. If "st1" has some value, no problem.But if "st1" has no value, the above statement is entering the string "nu;;" in the above table:
SQL>select f1 from mytab:
f1
==
null

How can I change my JSP to avoid entering "null" when st1 does not have any content?

please help!
0
Comment
Question by:tooki
  • 2
6 Comments
 
LVL 4

Expert Comment

by:vk33
Comment Utility
What value do you want to be inserted if st1 is null or empty? If you need the null-value (not the 'null' string) you should exclude the f1 parameter from the list:

if (st1 == null)
   count = stmt.executeUpdate("INSERT INTO mytab (f2,...) VALUES (...)");
else
   count = stmt.executeUpdate("INSERT INTO mytab (f1,f2,...) VALUES ('" + st1 + "',...");

And I would suggest using PreparedStatement instead:
PreparedStatement st = conn.prepareStatement("INSERT INTO mytab (f1,f2,...) VALUES (?,?,...)";
st.setString(1,st1);
st.setString(2,...);
count = st.executeUpdate();

Regards!
0
 

Author Comment

by:tooki
Comment Utility
Thanks!
But is was ok if I was ambigupus about only one field (if it is null) in that satement. but if in the insert sql statement has say 5 fields and I know that any of them can be null, then it becomes difficult.

And when a string is null, I would like to enter no value (In oracle it is NULL) for that field insead of the string "null".

Isn't there any better way of this null check?just wonder..

-tooki
0
 
LVL 4

Accepted Solution

by:
vk33 earned 25 total points
Comment Utility
yes, sure! Use PreparedStatement, it will solve the problem:

PreparedStatement st = conn.prepareStatement("INSERT INTO mytab (f1,f2,f3) VALUES (?,?,?)";
st.setString(1,st1);
st.setString(2,st2);
st.setString(3,st3);
count = st.executeUpdate();

If any of these strings are null, you'll have a NULL value in the database.

Regards!
0
 
LVL 30

Assisted Solution

by:mayankeagle
mayankeagle earned 25 total points
Comment Utility
Or maybet that you can write a utility function like:

public String getValidString ( String sCheck )
{
  return ( sCheck == null ) ? "" : sCheck.trim () ; // if not null, then it also trims it - optional

} // end of getValidString ()

Now you can use this method everywhere in your code, like:

count = stmt.executeUpdate("insert into mytab (f1, f2,..) values ('" + getValidString (st1) + "','" + getValidString (st2) + "',  ...,..)");

Mayank.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

10 Experts available now in Live!

Get 1:1 Help Now