DSXchange

Hi

I am getting a return value from a Server Routine as 170|NULL|NULL|1

170 - INT
NULL - DATETIME
NULL - DATETIME
1 - INT

I am going to use these values as a PARAMETER to load into SQL Server. In the job activity stage i used the below code and getting error "Error calling DSSetParam(ST_DATE), code=-4 [ParamVal]" for col 2 and 3.

COL1 --> field(GetIDs.$ReturnValue, '|' ,1)
COL2 --> field(GetIDs.$ReturnValue, '|' ,2)
COL3 --> field(GetIDs.$ReturnValue, '|' ,3)
COL4 --> field(GetIDs.$ReturnValue, '|' ,4)

I searched and found some info in this fourm, but still i cant resolve the issue.

Example:
viewtopic.php?t=136747&highlight=ParamV ... ppropriate

It will be great if any one can help me in this.

Thanks
DS User

You can not supply NULL as the value of a date/time/timestamp parameter.
You need to think of an in-band null equivalent (e.g. 9999-12-31).

Hi Ray

Thanks for your reply.

As per the logic, sometime it may return NULL value for DATETIME column from the Stored Procedure. In that case, can i able to use NullToValue (field(GetIDs.$ReturnValue, '|' ,2) ,'9999-12-31') ? - (I will try to use like this)

Or the procedure need to return the value as 9999-12-31 if it is null?

Please guide me the best way to handle the situation.

Thanks.
DS User

That should work fine I would think, give it a shot and let us know.

You can intercept the null in the routine and convert it to an in-band value before invoking DSSetParam() function.

Hi Ray / craig

I tried with Ereplace and it is working fine.

Thanks for your time and guidance,

DS User

Hi Ray / craig

Apoligies for marked it as resolved.

Now i am getting the value concatenate with NULL and i fails to notice it in time.

170|9999-12-31NULL|NULL|1


A = Field ( out, ' ', 1)
B = Field( out, ' ', 2)
C = Field ( out, ' ', 3)
D = Field ( out, ' ', 4)
Rtn = Trim (A) : '|' : Ereplace(Trim (B),'','9999-12-31') :'|': Trim (C) :'|':TRim (D)
Ans = Rtn
Return(Ans)

I will try set param and update this post.

If you find anything wrong in my code or any suggestion, please let me know.

Thanks
DS User

Is this the word "NULL" or a true NULL ? Because anything concatenated with a true NULL leads to NULL as the result. If it's a word, then your Ereplace() solution should still work.

Hi Ray

It may be NULL and not "NULL". The reason is; It is the retrun value of the Stored procedure (170|NULL|NULL|1).

Thanks
DS User