How to pass null value to mysql stored procedure using java

Asked May 15 '13 at 06:23

Active May 15 '13 at 06:26

Viewed 947 times

Below is my stored procedure

DROP PROCEDURE `addRecord`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `addRecord`(company varchar(3000),name                 
varchar(3000),title varchar(3000),metro varchar(3000),city varchar(3000),email  
varchar(3000),phone varchar(3000),industry varchar(3000))

BEGIN

    insert ignore into record_data (company,name,title,metro,city,email,phone,industry)        
            values(company,name,title,metro,city,email,phone,industry);
END

My main task is to check duplicate entries. It works fine if i provide some values to all the fields. But if i provide certain field as null from java it inserts an new row.Rather than eliminating duplicate. For e.g.

   if i use following statement it dosent insert an entry if it exists

   record.addRecord("abc", "a", "abc", "a","b","ostwal@gmail.com", "518- 220", "intl");

   but if use 
   record.addRecord("abc", "a", "abc", null,null,"ostwal@gmail.com", "518- 220", null);
   it inserts another row even if it already exists.

edited May 15 '13 at 06:26

asked May 15 '13 at 06:23

Ankit Ostwal

1,033
3
14
32

allow null to the fields in which you are inserting null valus..alter your table – Rahul May 15 '13 at 06:26
i have allowed null values... – Ankit Ostwal May 15 '13 at 06:27
are you assigning default values as well or not – Rahul May 15 '13 at 06:28
by default each column of table is set to null – Ankit Ostwal May 15 '13 at 06:29
use `NULL` not `null` – Rahul May 15 '13 at 06:31
or tell me the fileds in which you are assiging `null` having what kind of datatype. – Rahul May 15 '13 at 06:33
@rahul :: in java u cannot pass NULL , u need to use null as its an keyword specifying null values... – Ankit Ostwal May 15 '13 at 06:34
@rahul :: all the columns are varchar() with default value null – Ankit Ostwal May 15 '13 at 06:36
1

you can do one thing the columns having `null` value don't include them in your insert query. – Rahul May 15 '13 at 06:40
thank you all for your valuable suggestions. & sparing time for this post.. – Ankit Ostwal May 15 '13 at 11:23

How to pass null value to mysql stored procedure using java

0 Answers0