Solved

NULL value problem (URGENT please!)

Posted on 2003-12-04
6
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 4

Expert Comment

by:vk33
ID: 9880461
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
ID: 9880724
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
ID: 9880785
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:Mayank S
Mayank S earned 25 total points
ID: 9926311
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
JSONSerializer.toJSON in JSP generating error 8 100
How can I   ajax html table  rows? 20 88
jdbc error in jsp application 20 86
dao vs facade design patterns 2 65
A lot of things can happen during a presentation, worst of which is “death by PowerPoint.” Here are a few mistakes to avoid to make your slides clean.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

738 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