Examples of pb mysql stored procedures using odbc call
Gave an example of a small mysql stored procedures, at the same time gives a pb9 invokes the example. This example is given mysql8.0 stored procedure, have very hard now for 5.7 the odbc support

String ls_errtext
The SQLCA. DBMS="ODBC"
The SQLCA. The AutoCommit=False
The SQLCA. DBParm="ConnectString='DSN=test; UID=root; The PWD=123456; SERVER=192.168.10.111; The DATABASE=MY_TEST; The Option "
=8 'Connect using the SQLCA;
IF the sqlca. Sqlcode & lt;> 0 then
Ls_errtext=SQLCA. SQLErrtext
Messagebox (" message ", "unable to connect to mysql server the database ~ r ~ n" + ls_errtext)
RETURN
END the IF

String ls_sql
='CLIENT_IGNORE_SPACE ls_sql="SET SQL_MODE'"
The EXECUTE IMMEDIATE: ls_sql;
Open (w_main_test)


The 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))

The 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 the EXIT HANDLER FOR SQLEXCEPTION
The BEGIN

- GET DIAGNOSTICS and CONDITION 1;
- through the GET DIAGNOSTICS and this way GET sqlstate message_text (of course error message error Numbers can define your own)


The SET V_ERROMSG=message_text;

The ROLLBACK.
The SET V_DQZ=1;

SELECT '1'.

- MYSQL_ERRNO error code

The END;
- similar to oracle no_data_find exception handling

DECLARE the CONTINUE HANDLER FOR the NOT FOUND SET no_row_found=1;


DECLARE the CONTINUE HANDLER FOR 1062

The begin
Select 'error: duplicate key values;

The end;

IF V_COUNT IS NULL OR V_COUNT & lt;=0 OR V_IDENTITY_TABLE IS NULL OR V_TABLENAME IS NULL THEN

The set V_DQZ=0;


END the IF;

The SET V_UPPER_V_TABLENAME=UPPER (V_TABLENAME);

START the TRANSACTION;

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

The SELECT VS_DQZ;

Commit;

END

- PB button inside the code:
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
='CLIENT_IGNORE_SPACE ls_sql="SET SQL_MODE'"
The 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;
The EXECUTE SP_MYSQL_TEST;
Ls_sqlerrtext=SQLCA. SQLerrtext
Messagebox (" ", ls_sqlerrtext)
The FETCH SP_MYSQL_TEST INTO: ls_v_dqz;
The CLOSE SP_MYSQL_TEST;
Messagebox (" ", string (ls_v_dqz))

CodePudding user response:

Thanks for sharing, headache, pb 32-bit can't connect mysql in the webservice