Error calling DSSetParam(ST_DATE), code=-4 [ParamVal]

[SURA]

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

[ray.wurlod]

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).

[SURA]

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

[chulett]

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

[ray.wurlod]

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

[SURA]

Hi Ray / craig

I tried with Ereplace and it is working fine.

Thanks for your time and guidance,

DS User

[SURA]

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

[ray.wurlod]

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.

[SURA]

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