aspose file tools*
The moose likes JDBC and the fly likes how can i insert a null value in a table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how can i insert a null value in a table" Watch "how can i insert a null value in a table" New topic
Author

how can i insert a null value in a table

saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
i want to insert a null value in one column

how can i do it?


A = HARDWORK B = LUCK/FATE If C=(A+B) then C=SUCCESSFUL IN LIFE else C=FAILURE IN LIFE
SCJP 1.4
amod gole
Ranch Hand

Joined: Dec 07, 2005
Posts: 83
in which colum ......

which database
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

... if indeed this is a database related question!

Please have a read of this and this.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
the database i am using is oracle 9i .
and there is one emp table which has got 3 columns name , empcode,salary

so here i want to insert null value in salary

what can i do using prepared statement in java program?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Use the appropriate set method on your PreparedStatement passing null. You don't tell us the type of the salary field, so I can't say which method to use.
amod gole
Ranch Hand

Joined: Dec 07, 2005
Posts: 83
hi saikrishna cinux,

and sorry for late reply....,

when you are firing (Insert)query on your table,
specify only two column
like
insert into emp (name,empcode) values("Xyz",1001)
implicitly oracle will assign null value to your salary column



saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
ok for now you just think it as a number datatype


if it is in string format then i would have just use this
setString("");//i think by placing empty quotes it works fine

what should i do for number datatype?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


if it is in string format then i would have just use this
setString("");//i think by placing empty quotes it works fine

It may work fine - in Oracle's case I think it will, since I don't think Oracle supports the insertion of empty String values. But an empty String is not the same as a null string. Why use that when you could use:


A NUMBER datatype can store both fixed and floating point values. So it could be a number of methods, depending on your data. Read the JavaDocs for PreparedStatement and try it. You should be able to work this out.

One more point - you have been round here long enough to know we like to keep topics in the forums that are most appropriate to their subject. This is a JDBC question. We have a JDBC forum. Next time you have a database related question, post it in the JDBC forum please.
[ September 15, 2006: Message edited by: Paul Sturrock ]
amod gole
Ranch Hand

Joined: Dec 07, 2005
Posts: 83
if you want null value for particular colum(what ever type) then why you are specifying that column in your insert query neglect that column.

and one more "" => empty string dosen't means null value
Ernest Friedman-Hill
author and iconoclast
Marshal

Joined: Jul 08, 2003
Posts: 24187
    
  34

Moving to JDBC.


[Jess in Action][AskingGoodQuestions]
Eitan Levi
Greenhorn

Joined: Jun 14, 2006
Posts: 14
You can use the setNull() function in PreparedStatement, which takes the column number and a constant representing the type to set (from java.sql.Types). Like this:



Technically, setNull can set *any* kind of field value, not just null. So IMO, it has a silly name.

(Edit: fixed a typo)
[ September 15, 2006: Message edited by: Eitan Levi ]
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
If you are using a stored procedure you can always specify that in SQL as NULL in place where the value goes. This will work only if the table is created such that it accepts null values.

One thing I am curious on is why would you want to store null for a salary??? If the person employee record is in the process of being create it should read 0 in my mind until everything is complete NULL's are bad for data as you need to check for them and they can be overused which is bad because they can signify 2 or more conditions i.e. if you were looking for all employees that are not set up yet, you could select all that have null for salary but what if someone was temporarily suspended for example and his/her salary was set to null in the process ... you would get the wrong result.

It is always best to choose a value like -0.0001 for example for the suspended guy and 0 for the guy in progress.

Just my 2 cents with all my assumptions
George
[ September 15, 2006: Message edited by: George Stoianov ]
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518

One thing I am curious on is why would you want to store null for a salary???


Null in database terms means "unknown value". If I store a null in a salary field, it means I don't know what the salary is.


It is always best to choose a value like -0.0001 for example for the suspended guy and 0 for the guy in progress


Using a zero might be a valid salary for an intern or volunteer. How would you distinguish between a "junk" value and a correct value?

And a negative could be a valid salary as well. I can think of a few jobs I'd pay to do.

Also, what if you had a report that showed the average (or sum) of the salaries of all employees in a department? A odd value like 0 or -.0001 could skew the results. Sum() and average() will ignore a null value.
[ September 15, 2006: Message edited by: Scott Johnson ]
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
Hi Scott,

I am curious why would you not know the salary?? It is not set yet when the person record is created?? You would offer someone a job they would accept it without knowing the salary?? HR would enter someone without a salary and Finance will be OK with that cause it is good for the budget.

I mean it is possible I have seen things. And how much would .001 change an average on numerical values with points precision of 2 (we are talking money here right??) ... is kind of questionable in my mind ... and yes those were just examples .

I am not doubting the fact that you are doing the right think or saying my approach is best - I am just offering a different view to the problem ... I find that helpful myself sometimes. Thanks for listening.

Good luck!
George
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
I forgot to mention what the point of not having null was :

and it is namely the fact that someone will ask: why the heck is this strange number there and what does it mean and if they are lucky they would find out. On the other hand if there was a null, what is there to ask it is a null = nothing, no meaning it could have been entered from anyone/anything by mistake ...

If I am to do a query to distinguish interns and some new or invalid records using that same table, if I did not have that same type of field, and had to resort to salary ( I know this is highly unlikely this is all hypothetical right ) how would I do that if they were all nulls.

Can you make joins on nulls??? aren't they bad when you are analysing data?

Thanks.
George
[ September 18, 2006: Message edited by: George Stoianov ]
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
I am curious why would you not know the salary??


Well that's not really the point. Null means unknown. Nulls may not make business sense in some scenarios, but I would use it before I'd use some arbitrary nonsense value.

And how much would .001 change an average on numerical values


Average these numbers: 10,000 15,000 and 20,000 (answer: 15,000)

Now do it again except include 0.01 (answer: 11250.0025)

Nonsense values could skew the results of some queries. (That's the point I was trying to make anyway.)

I forgot to mention what the point of not having null was : and it is namely the fact that someone will ask: why the heck is this strange number there


Yes, I agree. And I think it's preferrable for someone to say, "Whoa, what's this?" Than use a nonsense value assuming it's a valid value.


Can you make joins on nulls???


No, NULL does not equal NULL. Null means "unknown" so the database does not consider two null values equal. A join of two tables on columns that are null will not return any rows.

aren't they bad when you are analysing data?


No, NULLs are a good thing.
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
Nulls may not make business sense in some scenarios, but I would use it before I'd use some arbitrary nonsense value.

And that is exactly my point from a business perspective it does not make sense or is bad to have them in there, and at the end that is what provides my check ;). Yes your average results will not be right numerically but not counting a substantial amount of money that should have been taken into consideration would be bad either.

Average these numbers: 10,000 15,000 and 20,000 (answer: 15,000)

Now do it again except include 0.01 (answer: 11250.0025)

Nonsense values could skew the results of some queries. (That's the point I was trying to make anyway.)


I agree you have a point with the average. Now lets just say your boss says that all the nulls should actually have been 12000's the result will be different again or better only 4 people with nulls from the second floor in the painting department should have a 35 dollars added to that amount 'cause they are very nice .


No, NULL does not equal NULL.


And that is my point if you are analysing data you would just miss these values wouldn't you even though you put a semantic meaning in the null - salary not known ...

I completely realize that as every other thing with computers and programming more so, the best solution is usually determined by the situation/requirements/business conditions.

I think I have learned something and it is that NULLs can have their good uses and if I am expecting to get averages and other aggregate functions right I am well advised to stick with them .

My business experience so far has proven to me that people do things without rhyme or reason despite the efforts you/the programmer may put in preventing them from choosing to go down hill, that is why I elect to put safety nets for myself by leaving pieces of data to guide me through the jungle ;) I mean how much of a problem would be to remove the dummy 0.001 values when they are all the same and still get the right average??? Now tell me how would you differentiate between the intern and the slacker if they both had Nulls, without the use of another field, and your boss said give the slackers a raise of 25 and the interns a raise of 100 and give me the averages ....

What databases are all about is sql and if sql cannot work with nulls you can't either.

Thanks,
George
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
If you get a chance, pick up a copy of Joe Celko's book SQL For Smarties. It does a great job covering some advanced SQL topics. Chapter 6 is dedicated to nulls. It does a far better job explaining the theory behind nulls than I could ever do.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how can i insert a null value in a table