Problem running a 4GL program with an stored procedure
We had a system that used STORED PROCEDURES(Launched via an ODBC
connection) to execute 4GL programs that generate TXT files (data
delimited with "|") that were loaded then into another program.
We have migrated to the new INFORMIX 11.50 and when we try to run the
stored procedures there is an error produced:
Error: The system command cannot be executed or it exited with a non-
zero status. (State:S1000, Native Code: FFFFFD64)
The stored procedure that we create is:
CREATE PROCEDURE test()SYSTEM 'fglgo /disco2/bexe/sgfreversiones.4gi';
END PROCEDURE;
And we execute it:
EXECUTE PROCEDURE test();
Information on our previous system:
INFORMIX 9.4 running on a Sun SOLARIS
and now:
INFORMIX 11.50 running on Linux RED HAT
Any hints and help would be appreciated
Roger Camargo
Cochabamba - Bolivia
roger.camargo@gmail.com wrote:
> We had a system that used STORED PROCEDURES(Launched via an ODBC
> connection) to execute 4GL programs that generate TXT files (data
> delimited with "|") that were loaded then into another program.
>
> We have migrated to the new INFORMIX 11.50 and when we try to run the
> stored procedures there is an error produced:
>
> Error: The system command cannot be executed or it exited with a non-
> zero status. (State:S1000, Native Code: FFFFFD64)
>
> The stored procedure that we create is:
>
> CREATE PROCEDURE test()> SYSTEM 'fglgo /disco2/bexe/sgfreversiones.4gi';
> END PROCEDURE;
>
> And we execute it:
> EXECUTE PROCEDURE test();>
> Information on our previous system:
> INFORMIX 9.4 running on a Sun SOLARIS
>
> and now:
> INFORMIX 11.50 running on Linux RED HAT
>
> Any hints and help would be appreciated
This is probably the environment
Drop the fglgo line into a script and run the script. Within the script
you can set a 'good' environment or just add a 'set -x' to see the errors
Cheers
Paul
On May 22, 5:41 am, "Paul Watson (Oninit)" <p...@oninit.com> wrote:
> roger.cama...@gmail.com wrote:
> > We had a system that used STORED PROCEDURES(Launched via an ODBC
> > connection) to execute 4GL programs that generate TXT files (data
> > delimited with "|") that were loaded then into another program.
>
> > We have migrated to the new INFORMIX 11.50 and when we try to run the
> > stored procedures there is an error produced:
>
> > Error: The system command cannot be executed or it exited with a non-
> > zero status. (State:S1000, Native Code: FFFFFD64)
>
> > The stored procedure that we create is:
>
> > CREATE PROCEDURE test()> > SYSTEM 'fglgo /disco2/bexe/sgfreversiones.4gi';
> > END PROCEDURE;
>
> > And we execute it:
> > EXECUTE PROCEDURE test();>
> > Information on our previous system:
> > INFORMIX 9.4 running on a Sun SOLARIS
>
> > and now:
> > INFORMIX 11.50 running on Linux RED HAT
>
> > Any hints and help would be appreciated
>
> This is probably the environment
>
> Drop the fglgo line into a script and run the script. Within the script
> you can set a 'good' environment or just add a 'set -x' to see the errors
>
> Cheers
> Paul- Hide quoted text -
>
> - Show quoted text -
As Paul says this is likely environemnt related
the fglgo needs to know INFIRMIXDIR etc of the rds software
replace the fglgo in the stored procedure with a script and in the
script set the appropriate environment variables prior to running the
fglgo statement
You may want to export the stored procedure from the "old" system as
this would have had to implement a similar solution
On 23 mayo, 06:44, scottishpoet <drybur...@yahoo.com> wrote:
> On May 22, 5:41 am, "Paul Watson (Oninit)" <p...@oninit.com> wrote:
>
>
>
>
>
> > roger.cama...@gmail.com wrote:
> > > We had a system that used STORED PROCEDURES(Launched via an ODBC
> > > connection) to execute 4GL programs that generate TXT files (data
> > > delimited with "|") that were loaded then into another program.
>
> > > We have migrated to the new INFORMIX 11.50 and when we try to run the
> > > stored procedures there is an error produced:
>
> > > Error: The system command cannot be executed or it exited with a non-
> > > zero status. (State:S1000, Native Code: FFFFFD64)
>
> > > The stored procedure that we create is:
>
> > > CREATE PROCEDURE test()> > > SYSTEM 'fglgo /disco2/bexe/sgfreversiones.4gi';
> > > END PROCEDURE;
>
> > > And we execute it:
> > > EXECUTE PROCEDURE test();>
> > > Information on our previous system:
> > > INFORMIX 9.4 running on a Sun SOLARIS
>
> > > and now:
> > > INFORMIX 11.50 running on Linux RED HAT
>
> > > Any hints and help would be appreciated
>
> > This is probably the environment
>
> > Drop the fglgo line into a script and run the script. Within the script
> > you can set a 'good' environment or just add a 'set -x' to see the errors
>
> > Cheers
> > Paul- Hide quoted text -
>
> > - Show quoted text -
>
> As Paul says this is likely environemnt related
>
> the fglgo needs to know INFIRMIXDIR etc of the rds software
>
> replace the fglgo in the stored procedure with a script and in the
> script set the appropriate environment variables prior to running the
> fglgo statement
>
> You may want to export the stored procedure from the "old" system as
> this would have had to implement a similar solution- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -
As scottishpoet suggested it is the exported one from the "old" system
that is currently executed.
To know if the enviroment was the correct I made this SP :
CREATE PROCEDURE sgftest()SYSTEM 'echo $INFORMIXDIR > informixdir.txt';
SYSTEM 'echo $INFORMIXSERVER > informixserver.txt';
SYSTEM 'echo $ONCONFIG > onconfig.txt';
END PROCEDURE;
and when I executed I checked the files generated and they had the
correct values, that shows that the enviroment is correct.
Now I'll try to make an script and then execute this script with an
SP, but I'm afraid that the result will be the same, the script would
be like this:
test.sh
#!/bin/sh
export INFORMIXDIR=/home/informix
export INFORMIXSERVER=segunda
export ONCONFIG=onconfig.segundafglgo /disco2/bexe/roger/sgfreversiones.4gi
then I'll create an SP
CREATE PROCEDURE test()SYSTEM 'sh test.sh;
END PROCEDURE;
This would be correct or I need to modify my script, maybe add
something more or delete something?
Thanks for your help.
On 23 mayo, 09:32, "roger.cama...@gmail.com" <roger.cama...@gmail.com>
wrote:
> On 23 mayo, 06:44, scottishpoet <drybur...@yahoo.com> wrote:
>
>
>
>
>
> > On May 22, 5:41 am, "Paul Watson (Oninit)" <p...@oninit.com> wrote:
>
> > > roger.cama...@gmail.com wrote:
> > > > We had a system that used STORED PROCEDURES(Launched via an ODBC
> > > > connection) to execute 4GL programs that generate TXT files (data
> > > > delimited with "|") that were loaded then into another program.
>
> > > > We have migrated to the new INFORMIX 11.50 and when we try to run the
> > > > stored procedures there is an error produced:
>
> > > > Error: The system command cannot be executed or it exited with a non-
> > > > zero status. (State:S1000, Native Code: FFFFFD64)
>
> > > > The stored procedure that we create is:
>
> > > > CREATE PROCEDURE test()> > > > SYSTEM 'fglgo /disco2/bexe/sgfreversiones.4gi';
> > > > END PROCEDURE;
>
> > > > And we execute it:
> > > > EXECUTE PROCEDURE test();>
> > > > Information on our previous system:
> > > > INFORMIX 9.4 running on a Sun SOLARIS
>
> > > > and now:
> > > > INFORMIX 11.50 running on Linux RED HAT
>
> > > > Any hints and help would be appreciated
>
> > > This is probably the environment
>
> > > Drop the fglgo line into a script and run the script. Within the script
> > > you can set a 'good' environment or just add a 'set -x' to see the errors
>
> > > Cheers
> > > Paul- Hide quoted text -
>
> > > - Show quoted text -
>
> > As Paul says this is likely environemnt related
>
> > the fglgo needs to know INFIRMIXDIR etc of the rds software
>
> > replace the fglgo in the stored procedure with a script and in the
> > script set the appropriate environment variables prior to running the
> > fglgo statement
>
> > You may want to export the stored procedure from the "old" system as
> > this would have had to implement a similar solution- Ocultar texto de la cita -
>
> > - Mostrar texto de la cita -
>
> As scottishpoet suggested it is the exported one from the "old" system
> that is currently executed.
>
> To know if the enviroment was the correct I made this SP :
>
> CREATE PROCEDURE sgftest()> SYSTEM 'echo $INFORMIXDIR > informixdir.txt';
> SYSTEM 'echo $INFORMIXSERVER > informixserver.txt';
> SYSTEM 'echo $ONCONFIG > onconfig.txt';
> END PROCEDURE;
>
> and when I executed I checked the files generated and they had the
> correct values, that shows that the enviroment is correct.
>
> Now I'll try to make an script and then execute this script with an
> SP, but I'm afraid that the result will be the same, the script would
> be like this:
>
> test.sh
> #!/bin/sh
> export INFORMIXDIR=/home/informix
> export INFORMIXSERVER=segunda
> export ONCONFIG=onconfig.segunda> fglgo /disco2/bexe/roger/sgfreversiones.4gi
>
> then I'll create an SP
>
> CREATE PROCEDURE test()> SYSTEM 'sh test.sh;
> END PROCEDURE;
>
> This would be correct or I need to modify my script, maybe add
> something more or delete something?
>
> Thanks for your help.- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -
I did exactly as I said in the previous entry and the same error
message happened when I executed the SP.
Error: The system command cannot be executed or it exited with a non-
zero status. (State:S1000, Native Code: FFFFFD64)
Executing the script directly from command line works as expected.
The sgfreversiones.4gl is like this (maybe there is something wrong
with it):
database tbase
define
t1 record like adaud.*,
g_spool char(20),
l_argu char(60),
### DATOS DE LA TABLA ###
fecha date,
horatransaccion char(8),
horaautorizacion char(8),
horareversion char(8),
inicusuariorever char(3),
inicusuarioautor char(3),
tipotransac char(70),
monto like adaud.adaudimpo,
cliente char(50),
### DATOS DE LA TABLA ###
fdia date,
cfdia char(10),
fini date,
cfini char(10),
x char,
tipocambio like gbhtc.gbhtctcof,
tcof like gbpmt.gbpmttcof
main
let g_spool="sgfreversiones.txt"
start report sgfreversiones to g_spool
select gbpmtfdia into fdia from gbpmt
select gbpmttcof into tcof from gbpmt
let cfdia=fdia
let cfdia=cfdia clipped
let cfini="01/",cfdia[4,10]
let cfini=cfini clipped
let fini=cfini
declare curs cursor for
select * from adaud
where adaudfpro between fini and fdia
foreach curs into
t1.*
error "Procesando el numero de autorizacion: ...",t1.adaudnaut
call nulos()
select gbhtctcof into tipocambio from gbhtc
where gbhtcfech=t1.adaudfpro
if tipocambio is null then
let tipocambio=0
end if
let fecha=t1.adaudfpro
let horatransaccion=t1.adaudshor
let horaautorizacion=t1.adaudahor
let horareversion=null # no puedo relacionar esta hora
let inicusuariorever=t1.adaudsusr
let inicusuarioautor=t1.adaudausr
let tipotransac=t1.adaudglos
if t1.adaudcmon=1 then
let monto=t1.adaudimpo/tipocambio
else
let monto=t1.adaudimpo
end if
select gbagenomb into cliente from gbage
where gbagecage=t1.adaudcage
output to report sgfreversiones()
end foreach
end main
report sgfreversiones()output
page length 1
top margin 0
bottom margin 0
left margin 0
right margin 0
format
on every row
print
fecha,"|",horatransaccion,"|",horaautorizacion,"|",horareversion,
"|",inicusuariorever,"|",inicusuarioautor,"|",tipotransac,"|",
monto,"|",cliente
end report
function nulos()
let fecha=null
let horatransaccion=null
let horaautorizacion=null
let horareversion=null
let inicusuariorever=null
let inicusuarioautor=null
let tipotransac=null
let monto=null
let cliente=null
let tipocambio=null
end function
Any more hints? Maybe the output of the 4gl program is terminating the
program when running via an SP?