一、Vb6 通过ADO 连接MYSQL数据库
Dim cn As New ADODB.Connection
'hostserver = "127.0.0.1"
'user = "root"
'pwd = "123456"
'dbserver = "backup"
'If cn.State = 1 Then
' cn.Close
strcn = " DRIVER={MySQL ODBC 5.2 unicode Driver};" & "SERVER=" & hostserver & "; DATABASE=" & dbserver & ";UID=" & user & ";PWD=" & pwd & "; OPTION=3"
cn.Open strcn
endif
二、Vb6 调用Mysql存储过程
sqlstr = "call Stor_InOutMat('" & selck & "'," & Val(Label20.Caption) & "," & Val(Text10) & ",'" & czymc & "','" & Trim(Text11) & "'," & czyid & ")"
cn.Execute sqlstr
三、带参数的Mysql存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `Stor_InOutMat`(IN indeportCode varchar(30), IN inmateId int,IN Num int,IN InName varchar(20) charset utf8,IN InRemark varchar(50) charset utf8,IN InUseId int)
BEGIN
set @ServerDT=sysdate();
select count(mateid) into @aa from materialios where trim(Depotcode)=trim(indeportCode) and mateid=inmateId;
if @aa=0 then /* 无库存记录,直接加一条入仓或出仓记录 */
begin
if num>0 then insert into materialios(mateid,depotcode,IOSDateTime,AddNum,SubNum,OverNum,UserName,Remark,UserId) values(inmateId,indeportCode,@ServerDT,Num,0,Num,InName,InRemark,InUseId); /* 入仓 */
else
insert into materialios(mateid,depotcode,IOSDateTime,AddNum,SubNum,OverNum,UserName,Remark,UserId) values(inmateId,indeportCode,@ServerDT,0,0-Num,Num,InName,InRemark,InUseId); -- 出仓--
end if;
end;
else /* 以前有库存记录,查询库存量后再出入仓及更改库存量 */
begin
select max(ID) into @lasttime from materialios where trim(Depotcode)=trim(indeportCode) and mateid=inmateId;
select OverNum into @OverNum from materialios where ID=@lasttime;
/*select max(IOSDateTime) into @lasttime from materialios where trim(Depotcode)=trim(indeportCode) and mateid=inmateId; /* 最后的出入仓时间*/
/*select OverNum into @OverNum from materialios where Depotcode=indeportCode and mateid=inmateId and IOSDateTime=@lasttime; /* 现有库存量*/
if num>0 then insert into materialios(mateid,depotcode,IOSDateTime,AddNum,SubNum,OverNum,UserName,Remark,UserId) values(inmateId,indeportCode,@ServerDT,Num,0,@OverNum+Num,InName,InRemark,InUseId); /* 入仓 */
else
insert into materialios(mateid,depotcode,IOSDateTime,AddNum,SubNum,OverNum,UserName,Remark,UserId) values(inmateId,indeportCode,@ServerDT,0,0-Num,@OverNum+Num,InName,InRemark,InUseId); -- 出仓--
end if;
end;
end if;
DROP TEMPORARY TABLE IF EXISTS lstjb;
CREATE TEMPORARY TABLE lstjb SELECT mateid,depotcode,max(iosdatetime) as maxti from materialios group by mateid,depotcode;
select sum(overnum) into @LastNum from lstjb,materialios where materialios.mateid=lstjb.mateid and materialios.depotcode=lstjb.depotcode and materialios.iosdatetime=lstjb.maxti and materialios.mateid=inmateId;
update mate_inf set numbers=@LastNum where Id= inmateId;
END;
四、Mysql
游标的使用
DECLARE rs_cursor CURSOR FOR SELECT MateInfId,Numbers from View_slip_mater where trim(OddNumber)=trim(OutSlip) and SumMater=0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
set done=0;
open rs_cursor;
cursor_loop:loop
FETCH rs_cursor into _MateInfId, _Numbers;
if done=1 then
leave cursor_loop;
else
set _Numbers=0-_Numbers;
call stor_inoutMat(indeportCode,_MateInfId,_Numbers,InName,OutSlip,InUseId);
end if;
end loop cursor_loop;
close rs_cursor;