Insert statement inside a Server routine

[sumesh.abraham]

Hello all,

I am facing issue with Insert statement within a Server routine.

Cmd1 = 'sqlplus -s ':parmDBName:'/':parmDBPass:'@':parmDBUser:' << EOF'
Cmd2='set heading off'

If parmActionCd='I' Then

Cmd3 ='INSERT INTO XXXXX(col-name1,col-name2) values(parm1,parm2);'
Cmd4 = 'EOF'
Cmd = Cmd1:Char(10):Cmd2:Char(10):Cmd3:Char(10):Cmd4
Call DSExecute('UNIX',Cmd,Output1,ReturnCode)
End

When I test teh routine, I get the return value from the DSExecute as 1 and no error is shown, but the record does not get inserted in to the table.

Can you please let me know what could be the issue here?

[ray.wurlod]

sqlplus has not returned an exit status of 0, which means it has had a problem. Try redirecting stderr from the sqlplus command to a file that you can inspect subsequently.

[sumesh.abraham]

Thanks Ray.

I am using the statement as
'INSERT INTO XXXXX(col1,col2) values (:param1,:parm2);'

DSExceute command returns 0, but teh following error is logged

Message to be logged is...
> SP2-0552: Bind variable "parm2" not declared.

Parameter names are correct. What could be causing the issue here?

[chulett]

You need to 'bind' the values for them in when you build the Cmd3 string.

[sumesh.abraham]

Thanks Ray. It would be great if you could tell how to bind them, I am quite new to using Routines for Database operations. Thanks.

[chulett]

Not all who respond are Rays. And do it just like you did for Cmd1 where you bound in the value of parmDBPass for example.

[sumesh.abraham]

Thanks Craig and sorry for not addressing you properly in the previous reply.

Your suggestion for the Cmd variable worked and there is no error from DSExecute command and it returns 0. Interestingly delete and update statements worked perfectly!

But for some reason, the insert is not happening.

In the below code in server Routine , the value coming from source for the 2 date values is in the format YYYY-MM-DD HH:MI:SS and in the target table they are of Date Data types.

Cmd1 = 'sqlplus -s ':parmDBUser:'/':parmDBPass:'@':parmDBName:' << EOF'
Cmd2='set heading off'
Cmd3 ='INSERT INTO curve_term_value(upd_ts,snap_date_time,term_id,value) values(':SQUOTE(parmUpdTs):',':SQUOTE(parmSnapDateTime):',':parmTermId:',':parmValue:')';
Cmd4 = 'EOF'
Cmd = Cmd1:Char(10):Cmd2:Char(10):Cmd3:Char(10):Cmd4
Call DSExecute('UNIX',Cmd,Output1,ReturnCode)
Call DSLogInfo(Output1,"")

Output1 is not getting displayed when teh routine is executed.
I tried to do a to_date on the parmUpdTs and parmSnapDateTime as well, but the insert is not happening.