pb使用odbc调用mysql存储过程的例子并且获得返回值

sbigwolf 2020-08-10 10:52:04

pb使用odbc调用mysql存储过程的例子
给了一个小的mysql 存储过程的例子。同时给出了一个在pb9中调用的例子.本例子是给出的mysql8.0的存储过程。对于5.7一下的现在odbc很难有支持

string ls_errtext
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='DSN=test;UID=root;PWD=123456;SERVER=192.168.10.111;DATABASE=MY_TEST;Option=8'"
connect using SQLCA ;
IF sqlca.sqlcode <> 0 then
ls_errtext = SQLCA.SQLErrtext
messagebox("提示信息","无法连接上mysql服务器的数据库~r~n" + ls_errtext)
RETURN
END IF

string ls_sql
ls_sql = "SET SQL_MODE='CLIENT_IGNORE_SPACE'"
EXECUTE IMMEDIATE :ls_sql ;
open(w_main_test)

CREATE DEFINER=`root`@`%` PROCEDURE `SP_PRO_TEST`( IN V_IDENTITY_TABLE VARCHAR(40) ,
IN V_TABLENAME VARCHAR(40),IN V_COUNT int,OUT VS_DQZ VARCHAR(40))

begin

DECLARE V_SQL VARCHAR(500);

DECLARE V_SQLU VARCHAR(500);

DECLARE V_UPPER_V_TABLENAME VARCHAR(40) ;

DECLARE V_ERROMSG VARCHAR(1024);

DECLARE V_DQZ INT ;

DECLARE no_row_found INT ;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN

-- GET DIAGNOSTICS CONDITION 1 ;
--通过GET DIAGNOSTICS这样的方式获取sqlstate message_text(当然错误信息错误编号可以自己定义)

SET V_ERROMSG = message_text ;

ROLLBACK ;
SET V_DQZ = -1 ;

SELECT '-1';

-- MYSQL_ERRNO 错误代码

END ;
-- 处理类似 oracle的 no_data_find异常

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;

DECLARE CONTINUE HANDLER FOR 1062

begin
Select '错误：重复键值';

end ;

IF V_COUNT IS NULL OR V_COUNT <= 0 OR V_IDENTITY_TABLE IS NULL OR V_TABLENAME IS NULL THEN

set V_DQZ = 0;

END IF;

SET V_UPPER_V_TABLENAME = UPPER(V_TABLENAME) ;

START TRANSACTION;

V_DQZ = V_COUNT * 5
SET VS_DQZ =CONCAT( '',V_DQZ);

SELECT VS_DQZ;

commit ;

END

--PB的按钮里面的代码：
string ls_identi_name ,ls_table_name ,ls_sqlerrtext ,ls_v_dqz
int li_V_COUNT = 2
long ll_V_DQZ
ll_V_DQZ = 33
ls_identi_name = "GY_IDENTI_SEED"
ls_table_name = 'T_STUDENT'

string ls_sql
ls_sql = "SET SQL_MODE='CLIENT_IGNORE_SPACE'"
EXECUTE IMMEDIATE :ls_sql ;
messagebox("", string(ls_identi_name) )
Is_identi_name =ls_identi_name
DECLARE SP_PRO_TEST PROCEDURE FOR BSOFT_PRO_TEST
:ls_identi_name, :ls_table_name,:li_V_COUNT ,:ls_v_dqz ;
EXECUTE SP_MYSQL_TEST ;
ls_sqlerrtext = SQLCA.SQLerrtext
messagebox("", ls_sqlerrtext )
FETCH SP_MYSQL_TEST INTO :ls_v_dqz ;
CLOSE SP_MYSQL_TEST ;
messagebox("", string(ls_v_dqz) )

...全文