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?
Insert statement inside a Server routine
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 153
- Joined: Thu May 11, 2006 1:52 am
- Location: Bangalore
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 153
- Joined: Thu May 11, 2006 1:52 am
- Location: Bangalore
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?
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?
-
- Participant
- Posts: 153
- Joined: Thu May 11, 2006 1:52 am
- Location: Bangalore
-
- Participant
- Posts: 153
- Joined: Thu May 11, 2006 1:52 am
- Location: Bangalore
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.
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.