cancel
Showing results for 
Search instead for 
Did you mean: 

To insert null in JDBC using stored procedures

Former Member
0 Kudos

Hi,

I am facing a big issue with my interface.

I need to insert a " NULL " when no data goes for a particular record, but it is not inserting null. It is inserting a blank space.

Since the field is a decimal field, i am not able to set a constant " NULL" also. I am using MS SQL Server 2005 for the Database.

We are actually inserting data through Stored Procedures.

The Null option in Communication channel works only in case of insert or update directly in . since we are using SP, we are using EXECUTE statement in the mapping. This doesnt works in case of Stored Procedures.

Kindly help us in this.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

a

Former Member
0 Kudos

are you able to insert NULL in decimal type field by firing insert query from sql server ide (UI),

if yes,

here you can put an if condition before insert statement in SP and replace NULL there.

Former Member
0 Kudos

Hi,

Thanks for your responses.

Is there any possibility to handle it at the XI side. Making changs in DB side is a good option, but we are looking for a solution in XI side itself. If is can be achieved in PI itself, then it would be really cool.

Regards,

Sherin Jose

prateek
Active Contributor
0 Kudos

In the stored procedure, define a variable with value = NULL. Add a condition that when you find empty field in PI, pass that variable to INSERT statement.

Regards,

Prateek

Former Member
0 Kudos

Hi..

Can you send the SQL query?

Former Member
0 Kudos

Hi,

This is the SQL query im using.

-- Creating Procedure USP_EMP --

ALTER PROCEDURE [dbo].[USP_EMP](

@EmpName varchar(15) ,

@EmpID varchar(10),

@Salary decimal(18, 3),

@DOB datetime,

@returncode int output)

AS

Declare @error int

Begin

Begin TRANSACTION

set @returncode=0

If Exists (select * from EMP

where EmpName = @EmpName and

EmpID = @EmpID )

Begin

UPDATE EMP

SET Salary = @Salary,

DOB = @DOB,

ZSTGPOPDATIME = getdate()

WHERE EmpName = @EmpName and

EmpID = @EmpID

End

Else

Begin

INSERT INTO EMP (

EmpName,

EmpID,

Salary,

DOB,

ZSTGPOPDATIME)

VALUES (

@EmpName,

@EmpID,

@Salary,

@DOB,

getdate())

set @error=@@ERROR

If (@error<>0)set @returncode=@error

End

commit

End

Former Member
0 Kudos

a

Edited by: SherinJose1985 on May 20, 2009 8:01 AM

Former Member
0 Kudos

as of my knowledge, decimal datatype will not take NULL ..

then also if you are able to insert from sql server IDE using simeple insert statement ,

you can do it here also by put an if condition before your insert statement. and replace the field by NULL if there is no value.