Solved

How to Insert NULL into MYSQL

Posted on 2011-03-03
24
1,022 Views
Last Modified: 2012-05-11
I have a foreign key field in a tablein MySQL.  its of type BIGINT . This accepts NULL in MySQL.

How Do I insert a Null   into a foreign key field which is of type bigint ?

I'm using PreparedStatement.

I tried this way ....The following code does not work .
....................................
ps.setNull(8, java.sql.Types.INTEGER)
....................................

thorws error

Cannot add or update a child row: a foreign key constraint  fails.  I'm not sure why this error is coming .
0
Comment
Question by:cofactor
  • 9
  • 9
  • 3
  • +2
24 Comments
 
LVL 40

Expert Comment

by:gurvinder372
ID: 35025527
try

 pstmt.setNull(3, java.sql.Types.INTEGER)

 pstmt.setNull(3, java.sql.Types.BIGINT)

http://download.oracle.com/javase/6/docs/api/java/sql/Types.html
0
 
LVL 40

Expert Comment

by:gurvinder372
ID: 35025529
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35025536
But if it is a foreign key - can it be NULL? Probaly not
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 40

Expert Comment

by:gurvinder372
ID: 35025546
i guess you can set null, as long as the child table's foreign key column is not set as NOT NULL
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35025553
Yes, so maybe it is set NOT NULL - it is worth checking, as the format of inserting NULL seems to have been correct
0
 
LVL 92

Expert Comment

by:objects
ID: 35025615
your code is correct, but you have a constraint in your database that requires it to be set
ie. problems in the database, not your code
0
 
LVL 20

Expert Comment

by:Sathish David Kumar N
ID: 35025651
If its forgien key ....

please reffer any relationship with other table(one to one or one to many ) if its present you cant set the NULL value .
0
 
LVL 20

Expert Comment

by:Sathish David Kumar N
ID: 35025661
That relationship table colum may not be null so it wont allow you insert null value .
0
 

Author Comment

by:cofactor
ID: 35025758
>>>your code is correct, but you have a constraint in your database that requires it to be set

see  script for the foreign key . This allows NULL





foreignkey.png
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35025784
Can you try to insert this row not from java, but from SQL prompt?

If not that then at least try to insert from Java/JDBC but without PreparedStatemnt - at least this experiment
will give some additional information  
0
 

Author Comment

by:cofactor
ID: 35025848
>>>Can you try to insert this row not from java, but from SQL prompt?

plenty !  see the records
 project
0
 

Author Comment

by:cofactor
ID: 35025853
>>>If not that then at least try to insert from Java/JDBC but without PreparedStatemnt - at least this >>>experiment will give some additional information  

Not understood.  Code please
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35025859
can you insert the same command from JDBC without PreparedStatement but with null explicitly
among the values ?
0
 

Author Comment

by:cofactor
ID: 35025901
>>can you insert the same command

I'm using a Editor to test it out but not  INSERT SQL. the SQLyog  GUI  for MYSQL helps  to insert a record

>>>from JDBC without PreparedStatement but with null explicitly
>>>among the values ?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35025925
Sorry, I never used MySQL - that's why I probably cannot understand.
I thought once you can use PreparedStatemnt in Java code, you should also
be able to use just plain sql without PreparedStatement from the same code.
Myabe it is different with MySQL...
0
 

Author Comment

by:cofactor
ID: 35025984
I have checked the SQL. The SQL insert  query  has NULL  in the foreign key field , then it can insert the record.

Is it possible to avail database  NULL from java ?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35026053
So, still there is no reason why you need to use PreparedStatement (it may be a bit quicker
on bulk insert, but otherwise...)

Did you try something like that:

Statement stmt = conn.createStatement();
stmt.executeUpdate("insert into table_name (col1_name, col2_nam2,col3_nam3) values ('a',null,'b')

Of course you can use some strings instead of actual values a and b

0
 
LVL 47

Expert Comment

by:for_yan
ID: 35026064
Of course I forgot the closing parenthesis in th eendo of the line
0
 

Author Comment

by:cofactor
ID: 35026097
>>>stmt.executeUpdate("insert into table_name (col1_name, col2_nam2,col3_nam3) values ('a',null,'b')

I can not keep always null  .  

I have this ...

if(project_activity_serial_no==null||"null".equals(project_activity_serial_no)||"".equals(project_activity_serial_no)) {
	
	System.out.println("before setting null "+java.sql.Types.INTEGER);
	ps.setNull(8, java.sql.Types.INTEGER);  // set NULL
	System.out.println("after setting null");

}else
{	System.out.println("inside  setting null");
	ps.setString(8,project_activity_serial_no); // set Value
}

Open in new window




0
 
LVL 47

Expert Comment

by:for_yan
ID: 35026154
You don't have to keep it always null
you can have it:

if(...)stmt.executeUpdate(.....one string)
else
smtm.executeUpdate(another string)

or you can prepare that string upfromt:

String s = "insert into ...."

if(...) s+= "null"
else
s+=  (some value)

and in the end you'll have stmt.executeUpdate(s)
but value of s will be different
 
0
 

Author Comment

by:cofactor
ID: 35026230
but I'm doing a Batch insert .  I'm doing such multiple  inserts in a loop and doing
ps.addBatch();  and at the last I'm doing    ps.executeBatch();

0
 
LVL 47

Expert Comment

by:for_yan
ID: 35026280
Well, at least I suggest to try and to see if it works - after that you can think and compare if it ois really slower, etc. But at least tou maybhave some way which works
0
 

Accepted Solution

by:
cofactor earned 0 total points
ID: 35026905
problem resolved.

reason: silly mistake

code should be

ps.setNull(7, java.sql.Types.INTEGER)  instead of  ps.setNull(8, java.sql.Types.INTEGER)

I'm sorry for wasting your time for such silly mistakes.  I'm accepting this as my answer.
0
 

Author Closing Comment

by:cofactor
ID: 35067807
solved myself
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
firstswap challenge 20 77
VB Script to add site to Java Exception List 4 77
servlet example 11 40
Custom Java Application connects to database: Which one of the 20? 4 28
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

772 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