- 论坛徽章:
- 0
|
我的存储过程是这样的,且创建成功
drop procedure pur_rev @
CREATE PROCEDURE pur_rev(in return_code int,in merchant char(16) ,in term char(9),in batch char(9),in trace char(9),out re_code int)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
declare count_t int default 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION,NOT FOUND
BEGIN
set re_code=SQLCODE;
rollback;
END;
select count into count_t from pur_rev where batch_no=batch and merchant_no=merchant and term_id=term and cups_no=trace;
if (count_t+1=1) then
if (return_code!=0) then
update pur_rev set count=1 where batch_no=batch and merchant_no=merchant and term_id=term and cups_no=trace;
end if;
if(return_code=0) then
update cups_flows set count=count_t+1,flag='01',resp_no='00' where batch_no=batch and merchant_no=merchant and term_id=term and cups_no=trace;
delete from pur_rev where batch_no=batch and merchant_no=merchant and term_id=term and cups_no=trace;
end if;
end if;
if (count_t+1=2) then
if(return_code!=0) then
update pur_rev set count=count_t+1 where batch_no=batch and merchant_no=merchant and term_id=term and cups_no=trace;
end if;
if(return_code!=0) then
update cups_flows set count=count_t+1,flag='01',resp_no='00' where batch_no=batch and merchant_no=merchant and term_id=term and cups_no=trace;
delete from pur_rev where batch_no=batch and merchant_no=merchant and term_id=term and cups_no=trace;
end if;
end if;
if (count_t+1=3) then
if(return_code!=0) then
update cups_flows set count=count_t+1,flag='01',resp_no='00' where batch_no=batch and merchant_no=merchant and term_id=term and cups_no=trace;
delete from pur_rev where batch_no=batch and merchant_no=merchant and term_id=term and cups_no=trace;
end if;
if(return_code!=0) then
update cups_flows set count=count_t+1,flag='01',resp_no='00' where batch_no=batch and merchant_no=merchant and term_id=term and cups_no=trace;
delete from pur_rev where batch_no=batch and merchant_no=merchant and term_id=term and cups_no=trace;
end if;
end if;
commit;
set re_code=SQLCODE;
END @
嵌入SQC是这样的
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <string.h>
#include <math.h>
#include <sqlca.h>
#include "DB_Trans.h"
EXEC SQL INCLUDE SQLCA;
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char sybserver[20],sybuser[20],sybpasswd[20];
struct
{
char batch_no[9];
char pos_no[9];
char bank_card[20];
char merchant_no[16];
char term_id[9];
char amount[9];
char track_2_data[38];
char track_3_data[105];
} rev;
long code;
long pp_code;
char procname[254] = "branch_pur_rev";
char stmt[1200];
EXEC SQL END DECLARE SECTION;
int s_fd;
int conn;
char send[1024];
char receive[256];
char cups_ret[4];
memset(sybserver,0,sizeof(sybserver));
memset(sybuser,0,sizeof(sybuser));
memset(sybpasswd,0,sizeof(sybpasswd));
memset(&rev,0,sizeof(rev));
memset(send,0,sizeof(send));
memset(receive,0,sizeof(receive));
signal(SIGTERM,termproc);
signal(SIGINT,termproc);
signal(SIGCHLD,SIG_IGN);
signal(SIGHUP, SIG_IGN);
struct sqlda *inout_sqlda = (struct sqlda *)
malloc(SQLDASIZE(1));
sprintf(stmt, "CALL %s (?,?,?)", procname);
sprintf(send,"%s","<F500></F500><F501></F501><F503></F503><F504></F504><F95></F95><F125></F125>");
if(getdbconfig(sybserver,sybuser,sybpasswd))
{
GClog_print(__FILE__,__LINE__,"get server,user,passwd \n");
return -1;
}
EXEC SQL CONNECT TO :sybserver USER :sybuser USING :sybpasswd;
if (sqlca.sqlcode != 0)
{
GClog_print(__FILE__,__LINE__,"connect to ehm_db failed \n");
return -2;
}
exec sql declare rev_5 cursor for select batch_no,pos_no,bank_card,merchant_no,term_id,amount,track_2_data,track_3_data from pur_rev where count<3;
if(sqlca.sqlcode!=0)
{
GClog_print(__FILE__,__LINE__,"declare cursor failed \n");
return -3;
}
exec sql open rev_5;
if(sqlca.sqlcode!=0)
{
GClog_print(__FILE__,__LINE__,"open cursor failed \n");
return -4;
}
exec sql fetch rev_5 into :rev;
while(sqlca.sqlcode == 0)
{
if((s_fd=fork())==0)
{
if(tcp_connect("127.0.0.1",8888,&conn))
{
GClog_print(__FILE__,__LINE__,"connect to cupsp failed_rev_trade \n");
tcp_close(conn);
goto END;
}
ABC_XmlChgValue(send,"F500",rev.merchant_no);
ABC_XmlChgValue(send,"F501",rev.term_id);
ABC_XmlChgValue(send,"F503",rev.batch_no);
ABC_XmlChgValue(send,"F504",rev.pos_no);
ABC_XmlChgValue(send,"F95",rev.track_2_data);
ABC_XmlChgValue(send,"F125",rev.track_3_data);
if(tcp_write(conn,send,strlen(send),20))
{
GClog_print(__FILE__,__LINE__,"send to cupsp failed_rev_trade \n");
tcp_close(conn);
goto END;
}
if(tcp_read(conn,receive,sizeof(receive),60))
{
GClog_print(__FILE__,__LINE__,"receive from cupsp failed_rev_trade \n");
tcp_close(conn);
goto END;
}
ABC_XmlGetValue(receive,"F61",cups_ret);
pp_code=atoi(cups_ret+1);
EXEC SQL prepare st from :stmt;
EXEC SQL execute st INTO :code USING :pp_code :rev.merchant_no :rev.term_id :rev.batch_no :rev.pos_no;
if(sqlca.sqlcode==0)
{
GClog_print(__FILE__,__LINE__,"update ehm_db sucessful\n");
EXEC SQL COMMIT;
}
else
{
EXEC SQL ROLLBACK;
}
GClog_print(__FILE__,__LINE__,"Rev_trade_over,cups_ret:[%s]\n",cups_ret);
tcp_close(conn);
goto END;
}
exec sql fetch rev_5 into :rev;
}
END:
exec sql close rev_5;
exec sql connect reset;
return 0;
}
void termproc()
{
signal(SIGINT,SIG_IGN);
signal(SIGTERM,SIG_IGN);
exit(0);
}
但是编译的时候报:
db2inst1@linux-jyt:~/EHome/cupsp/rev> make
db2 connect to ehm_db user db2inst1 using 123456
Database Connection Information
Database server = DB2/LINUX 8.2.0
SQL authorization ID = DB2INST1
Local database alias = EHM_DB
db2 prep rev_trade.sqc bindfile
LINE MESSAGES FOR rev_trade.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
105 SQL0324N The "host" variable ":rev.merchant_no" is the
wrong type.
SQL0095N No bind file was created because of previous
errors.
SQL0091W Precompilation or binding was ended with "2"
errors and "0" warnings.
make: *** [rev_trade] Error 4
db2inst1@linux-jyt:~/EHome/cupsp/rev>
不知道是为什么,请高手告之 |
|