一、概述
虽然Codesys有付费的mysql操作库，但是为了更大的自由度，我利用Codesys的socket自己去实现Mysql的操作。
操作Mysql的程序的逻辑很简单：建立连接->接收并解析认证包->发送数据库登录验证信息包->获得授权->发送Query命令->接收并解析结果->断开连接。其中认证过程，用到了SHA1加密。
Codesys上，需要用到"CAA Net Base Services" 和 "CAA Memory"两个公共库。
二、程序组织
三、代码展示
3.1AuthPacket
//作者:AlongWu
TYPE AuthPacket :
STRUCT
FrameDataLen :UDINT:=0;
Sqid :BYTE:=1;
ClientCap1 :WORD:=16#A68D;
ClientCap2 :WORD:=16#006F;
MaxSize :UDINT:=16#00FFFFFF;
CharSetCode :BYTE:=16#21;
UserName :STRING(50);
DataBaseName :STRING(50);
Password :ARRAY[0..19] OF BYTE; //加密后的认证摘要
clientAuthPlugin :STRING(50):='mysql_native_password';
END_STRUCT
END_TYPE
3.2HandShakePacket
//作者:AlongWu
TYPE HandShakePacket :
STRUCT
FrameDataLen :UINT; //packet的内容区字节数
DataBaseMsg :STRING(50); //当前服务的mysql版本信息
Sqid :BYTE; //交互id
ProtoolId :BYTE; //协议号
ServerThreadId :UDINT; //服务器线程id
AuthBuffer :ARRAY[0..19] OF BYTE; //20Byte的随机认证码
ServerCap :WORD; //当前服务器权能组(16位，每位代表1项能力)
CharSetCode :BYTE; //字符编码
ServerState :WORD; //当前服务器状态
END_STRUCT
END_TYPE
3.3MysqlConnectInfo
//作者:AlongWu
TYPE MysqlConnectInfo :
STRUCT
ServerIp :NBS.IP_ADDR:=(sAddr:='192.168.0.105'); //mysql服务器ip地址
Port :UINT:=3306; //服务器端口号
UserName :STRING(50):='SpinMachine'; //登录账号
Password :STRING(50):='123456'; //登录密码
DataBase :STRING(50):='spinmachine3'; //数据库名称
ip1 :BYTE:=192;
ip2 :BYTE:=168;
ip3 :BYTE:=0;
ip4 :BYTE:=105;
END_STRUCT
END_TYPE
3.4MysqlRowInfo
//作者:AlongWu
TYPE MysqlRowInfo :
STRUCT
ColumnCount :BYTE; //列数量
RowData :ARRAY[0..999] OF BYTE; //行的字节数组
IsNull :BOOL; //数据表为空
END_STRUCT
END_TYPE3.5MysqlFirstRowData_FB
//作者:AlongWu
FUNCTION_BLOCK MysqlFirstRowData_FB
VAR CONSTANT
abyEmpty :ARRAY [0..999] OF BYTE; //复位buffer
END_VAR
VAR_INPUT
InputRxBytes :ARRAY[0..999] OF BYTE; //接收字节数组
END_VAR
VAR_OUTPUT
FirstRowInfo :MysqlRowInfo; //解析后首行数据，结构体
END_VAR
VAR
columnCount :BYTE; //列数量
Addr :UINT; //地址
i :UINT;
TmpfieldSize :BYTE; //当前field字节数
END_VAR
(*
Result Set Header 返回数据的列数量
Field 返回数据的列信息（多个）
EOF 列结束
Row Data 行数据（多个）
EOF 数据结束
举例：
返回：3行3列的表格数据。
返回的数据帧1次， 该帧里面有 9个packet，即9个packet是在同一个报文里面。
第1个packet =Result Set Header
第2个packet =列1的field
第3个packet =列2的field
第4个packet =列3的field
第5个packet =EOF packet
第6个packet =行1的packet
第7个packet =行2的packet
第8个packet =行3的packet
第9个packet =EOF packet
*)
(*
Result Set Header 返回数据的列数量
packlen :3B
packid :1B
columnCount :1B
*)
(*
Field packet
packlen :3B
packid :1B
LengthEncodedString 目录名称
LengthEncodedString 数据库名称
LengthEncodedString 数据表名称
LengthEncodedString 数据表原始名称
LengthEncodedString 列（字段）名称
LengthEncodedString 列（字段）原始名称
int<1> 填充值
int<2> 字符编码
int<4> 列（字段）长度
int<1> 列（字段）类型
int<2> 列（字段）标志
int<1> 整型值精度
int<2> 填充值（0x00）
LengthEncodedString 默认值
row Packet
packlen :3B
packid :1B
data1_len :1B = data的字节数
data :data1_len B
data2_len :1B = data的字节数
data :data2_len B
...... 多个字段值
*)
//列数量
columnCount := GVL.Mysql_abyRx[4];
//录入列数量
FirstRowInfo.ColumnCount :=columnCount;
//清空buffer
FirstRowInfo.RowData := abyEmpty;
//复位isNull标志
FirstRowInfo.IsNull := FALSE;
//初始化地址
Addr := 5;
columnCount := columnCount-1;
FOR i:=0 TO columnCount BY 1 DO
Addr := Addr + GVL.Mysql_abyRx[Addr]+4;
END_FOR
//EOF packet
Addr := Addr + GVL.Mysql_abyRx[Addr]+4;
//首个row的字符复制
IF GVL.Mysql_abyRx[Addr] = 5 AND GVL.Mysql_abyRx[Addr+4] = 16#FE THEN
//如果field的eof后面还是 eof，即行数据为空
FirstRowInfo.IsNull := TRUE;
ELSE
MEM.MemMove(pSource:= ADR(GVL.Mysql_abyRx)+Addr+4, pDestination:= ADR(FirstRowInfo.RowData), uiNumberOfBytes:=BYTE_TO_UINT(GVL.Mysql_abyRx[Addr]));
END_IF
3.6MysqlQueryCmdPack_FB
//作者:AlongWu
FUNCTION_BLOCK MysqlQueryCmdPack_FB
VAR CONSTANT
EmptyResult : ARRAY[0..999] OF BYTE;
END_VAR
VAR_INPUT
Cmd :BYTE; //命令码
InputBytes :ARRAY[0..998] OF BYTE; //请求指令的内容
END_VAR
VAR_OUTPUT
Result :ARRAY[0..999] OF BYTE;
ResultSize :UINT;
END_VAR
VAR
PacketLen :UDINT; //包的内容的字节长度 (总字节数 - 4)
tmp4byte :ARRAY[0..3] OF BYTE;
i :UINT;
END_VAR
(*
命令码列表
0x00 COM_SLEEP （内部线程状态)
0x01 COM_QUIT 关闭连接
0x02 COM_INIT_DB 切换数据库
0x03 COM_QUERY SQL查询请求
0x04 COM_FIELD_LIST 获取数据表字段信息
0x05 COM_CREATE_DB 创建数据库
0x06 COM_DROP_DB 删除数据库
0x07 COM_REFRESH 清除缓存
0x08 COM_SHUTDOWN 停止服务器
0x09 COM_STATISTICS 获取服务器统计信息
0x0A COM_PROCESS_INFO 获取当前连接的列表
0x0B COM_CONNECT （内部线程状态)
0x0C COM_PROCESS_KILL 中断某个连接
0x0D COM_DEBUG 保存服务器调试信息
0x0E COM_PING 测试连通性
0x0F COM_TIME （内部线程状态）
0x10 COM_DELAYED_INSERT （内部线程状态）
0x11 COM_CHANGE_USER 重新登陆（不断连接）
0x12 COM_BINLOG_DUMP 获取二进制日志信息
0x13 COM_TABLE_DUMP 获取数据表结构信息
0x14 COM_CONNECT_OUT （内部线程状态)
0x15 COM_REGISTER_SLAVE 从服务器向主服务器进行注册
0x16 COM_STMT_PREPARE 预处理SQL语句
0x17 COM_STMT_EXECUTE 执行预处理语句
0x18 COM_STMT_SEND_LONG_DATA 发送BLOB类型的数据
0x19 COM_STMT_CLOSE 销毁预处理语句
0x1A COM_STMT_RESET 清除预处理语句参数缓存
0x1B COM_SET_OPTION 设置语句选项
0x1C COM_STMT_FETCH 获取预处理语句的执行结果
*)
//先复位
Result := EmptyResult;
//获取string的内容的字节长度,不含0结束符
i:=0;
WHILE InputBytes[i] <> 0 AND i<999 DO
i:=i+1;
END_WHILE
//包的内容 = cmd + string ;
PacketLen :=UINT_TO_UDINT(1+i+1);
ResultSize :=1+i+1;
//包的内容长度赋值
MEM.MemMove(pSource:= ADR(PacketLen), pDestination:= ADR(Result), uiNumberOfBytes:=3 );
//sid
Result[3] := 0;
//cmd
Result[4] := Cmd;
//cmd内容赋值
MEM.MemMove(pSource:= ADR(InputBytes), pDestination:= ADR(Result)+5, uiNumberOfBytes:=ResultSize);
//包总字节数
ResultSize := ResultSize +4;
3.7SHA1_FB
//作者:AlongWu
FUNCTION_BLOCK SHA1_FB
VAR CONSTANT
Kt :ARRAY[0..3] OF DWORD:=[16#5A827999,16#6ED9EBA1,16#8F1BBCDC,16#CA62C1D6]; //固定K常量
Ht :ARRAY[0..4] OF DWORD:=[16#67452301,16#EFCDAB89,16#98BADCFE,16#10325476,16#C3D2E1F0]; //固定H常量
emptpBuff :ARRAY[0..63] OF BYTE;
END_VAR
VAR_INPUT
InputBytes :ARRAY[0..49] OF BYTE; //输入字节数组
END_VAR
VAR_OUTPUT
ResultCode :ARRAY[0..19] OF BYTE; ;
END_VAR
VAR
strSize :UINT;
i :UINT;
addr :UINT;
tmpBuffer :ARRAY[0..63] OF BYTE;
tmp2Byte :ARRAY[0..1] OF BYTE;
Ht_temp :ARRAY[0..4] OF DWORD:=[16#67452301,16#EFCDAB89,16#98BADCFE,16#10325476,16#C3D2E1F0]; //缓冲的H常量
Wt :ARRAY[0..79] OF DWORD;
HtTmp :DWORD;
END_VAR
//Ht_temp,复位
Ht_temp := Ht;
tmpBuffer := emptpBuff;
i:=0;
//找出字符串字节长度
WHILE InputBytes[i] <> 0 AND i<50 DO
i:=i+1;
END_WHILE
strSize:=i;
addr := i;
MEM.MemMove(pSource:= ADR(InputBytes), pDestination:= ADR(tmpBuffer), uiNumberOfBytes:=addr );
//字符最后byte的后面补 0x10
tmpBuffer[addr] := 16#80;
//字符的总位数， 字节数*8
strSize := strSize *8;
MEM.MemMove(pSource:= ADR(strSize), pDestination:= ADR(tmp2Byte), uiNumberOfBytes:=2 );
//在最后的2个byte，录入 输入字符的位数。
//高位在前，低位在后
tmpBuffer[62]:= tmp2Byte[1];
tmpBuffer[63]:= tmp2Byte[0];
//Wt 前 16个 32位，为 tmpBuffer 64个8位
FOR i:=0 TO 15 BY 1 DO
addr := i*4;
MEM.MemMove(pSource:= ADR(tmpBuffer)+addr, pDestination:= ADR(Wt[i]), uiNumberOfBytes:=4 );
Wt[i] := MEM.ReverseBYTEsInDWORD (Wt[i]);
END_FOR
//扩展到 80个32位
FOR i:=16 TO 79 BY 1 DO
addr := i*4;
Wt[i] := Wt_Func(Wt[i-3],Wt[i-8],Wt[i-14],Wt[i-16]);
END_FOR
FOR i:=0 TO 19 BY 1 DO
HtTmp :=Htemp_Func(Ht_temp[0],Ft1_Func(Ht_temp[1],Ht_temp[2],Ht_temp[3]),Ht_temp[4],Wt[i],Kt[0]);
Ht_temp[4] := Ht_temp[3];
Ht_temp[3] := Ht_temp[2];
Ht_temp[2] := ROL(Ht_temp[1],30);
Ht_temp[1] := Ht_temp[0];
Ht_temp[0] := HtTmp;
END_FOR
FOR i:=20 TO 39 BY 1 DO
HtTmp :=Htemp_Func(Ht_temp[0],Ft2_Func(Ht_temp[1],Ht_temp[2],Ht_temp[3]),Ht_temp[4],Wt[i],Kt[1]);
Ht_temp[4] := Ht_temp[3];
Ht_temp[3] := Ht_temp[2];
Ht_temp[2] := ROL(Ht_temp[1],30);
Ht_temp[1] := Ht_temp[0];
Ht_temp[0] := HtTmp;
END_FOR
FOR i:=40 TO 59 BY 1 DO
HtTmp :=Htemp_Func(Ht_temp[0],Ft3_Func(Ht_temp[1],Ht_temp[2],Ht_temp[3]),Ht_temp[4],Wt[i],Kt[2]);
Ht_temp[4] := Ht_temp[3];
Ht_temp[3] := Ht_temp[2];
Ht_temp[2] := ROL(Ht_temp[1],30);
Ht_temp[1] := Ht_temp[0];
Ht_temp[0] := HtTmp;
END_FOR
FOR i:=60 TO 79 BY 1 DO
HtTmp :=Htemp_Func(Ht_temp[0],Ft2_Func(Ht_temp[1],Ht_temp[2],Ht_temp[3]),Ht_temp[4],Wt[i],Kt[3]);
Ht_temp[4] := Ht_temp[3];
Ht_temp[3] := Ht_temp[2];
Ht_temp[2] := ROL(Ht_temp[1],30);
Ht_temp[1] := Ht_temp[0];
Ht_temp[0] := HtTmp;
END_FOR
FOR i:=0 TO 4 BY 1 DO
Ht_temp[i] := Ht_temp[i] + Ht[i];
Ht_temp[i] := MEM.ReverseBYTEsInDWORD (Ht_temp[i]);
END_FOR
MEM.MemMove(pSource:= ADR(Ht_temp), pDestination:= ADR(ResultCode), uiNumberOfBytes:=20 );
3.7.1Ft1_Func
Ft1_Func := (B AND C) OR ((NOT B) AND D);3.7.2Ft2_Func
Ft2_Func := B XOR C XOR D;3.7.3Ft3_Func
Ft3_Func :=(B AND C) OR (B AND D) OR (C AND D);3.7.4Htemp_Func
Htemp_Func := ROL(A,5) +Ft + E + W + K;3.7.5Wt_Func
temp := Wi_3 XOR Wi_8 XOR Wi_14 XOR Wi_16;
//循环左移1位
Wt_Func := ROL(temp,1);3.8MysqlClient
//作者:AlongWu
PROGRAM MysqlClient
VAR CONSTANT
abyEmpty :ARRAY [0..999] OF BYTE; //复位abyRx
ClearSha1Input :ARRAY[0..49] OF BYTE;
END_VAR
VAR
CloseOrder :ARRAY[0..4] OF BYTE:=[1,0,0,0,1]; //关闭连接命令
Mysql_Init_Step :BYTE:=0; //mysql通信步骤，0-认证，1-登录，2-query
Enable_F_TRIG :F_TRIG; //mysql通信使能下降沿
Enable_R_TRIG :R_TRIG; //mysql通信使能上升沿
Recei_R_TRIG :R_TRIG; //接收上升沿
Close_R_TRIG :R_TRIG; //关闭连接上升沿
Send_R_TRIG :R_TRIG; //发送上升沿
HSP :HandShakePacket; //mysql认证报文
AuthP :AuthPacket; //client回复的申请报文
Daddr :UINT; //解码byte地址
i :UINT;
TempFrameLen :UDINT; //帧的内容区字节数
TempAddr :UINT; //缓冲地址
TempUint1 :UINT; //中间变量1,UINT
//TempInt1 :INT; //中间变量1，INT
tempSha1 :ARRAY[0..19] OF BYTE; ;
tempSha2 :ARRAY[0..19] OF BYTE; ;
tempSha3 :ARRAY[0..19] OF BYTE; ;
tmpStrBytes :ARRAY[0..49] OF BYTE;
LongSha1Bytes :ARRAY[0..39] OF BYTE;
TempDw :DWORD;
SHA1 :SHA1_FB; //实例SHA1算法
ReplyFirstByte :BYTE; //mysql回复的内容的首个字节
WaitCount :UINT:=0; //空闲计时
TryConnect :UINT;
TryAuth :UINT;
END_VAR
3.9MysqlUserLogin
//作者:AlongWu3.1
PROGRAM MysqlUserLogin
VAR CONSTANT
EmptyResult :ARRAY[0..998] OF BYTE;
EmptyAuthPStr :STRING(4):='';
END_VAR
VAR
WB_UserLogin AT %IX115.7 :BOOL; //账号登录
WB_UserExit AT %IX116.0 :BOOL; //账号注销
WHMI_Account AT %IW4850 :STRING(25); //输入操作者账号，utf-8编码，25个英文，25个byte
WHMI_Password AT %IW4900 :STRING(25); //输入操作者密码，utf-8编码，25个英文，25个byte
WB_UserLogin_R_TRIG :R_TRIG; //账号登录
WB_UserExit_R_TRIG :R_TRIG; //账号注销
B_Login_F_TRIG :F_TRIG; //账号下降沿
B_Login :BOOL:=FALSE; //启动登录
B_LoginDeny :BOOL; //登录账号密码错误
B_Login_R_TRIG :R_TRIG; //登录上升沿
LoginStep :BYTE; //登录步骤
QueryStr :STRING(100); //查询命令字符串
QueryBytes :ARRAY[0..998] OF BYTE; //查询命令buffer
MysqlCmdPack :MysqlQueryCmdPack_FB; //Mysql指令实例
MysqlFirstRowInfo :MysqlRowInfo; //Mysql首行数据结构体
MysqlFirstRowDecode :MysqlFirstRowData_FB; //Mysql解析回复表格信息并提取首行数据
AuthpStr :STRING(4);
tmpUint :UINT;
END_VAR
WB_UserLogin_R_TRIG(CLK:=WB_UserLogin , Q=> ); //账号登录
WB_UserExit_R_TRIG(CLK:=WB_UserExit , Q=> ); //账号注销
IF WB_UserLogin_R_TRIG.Q THEN
//Mysql启动登录流程
B_Login := TRUE;
END_IF
IF WB_UserExit_R_TRIG.Q THEN
//注销账号
B_Login := FALSE;
END_IF
B_Login_R_TRIG(CLK:=B_Login , Q=> ); //账号登录上升沿
B_Login_F_TRIG(CLK:=B_Login , Q=> ); //账号登录下降沿
IF B_Login_F_TRIG.Q OR GVL.B_Mysql_AuthFalure THEN
GVL.Mysql_AccountAutp := 0;
GVL.RB_Mysql_Logining:=FALSE;
GVL.B_Mysql_Login := FALSE;
B_Login := FALSE;
END_IF
IF B_Login_R_TRIG.Q THEN
LoginStep := 0;
B_LoginDeny :=FALSE;
GVL.B_Mysql_Login := FALSE;
END_IF
IF B_Login THEN
CASE LoginStep OF
0:
GVL.RB_Mysql_Logining:=TRUE;
QueryBytes := EmptyResult; //指令buffer复位
IF GVL.B_Mysql_Inited THEN
LoginStep := 10; //已连接数据库
ELSE
LoginStep := LoginStep +1; //未连接数据库,下一步
END_IF
1:
GVL.B_Mysql_Enable :=TRUE;
LoginStep := LoginStep +1; //下一步
2:
IF GVL.B_Mysql_Inited THEN
LoginStep := 10; //已连接数据库
END_IF
IF GVL.B_Mysql_AuthFalure THEN //连接失败，推出登录
B_Login := FALSE;
END_IF
10:
GVL.B_Mysql_Result :=FALSE; //复位接收解析标志
// '要用$27来转义，特殊的符号，用$+16进制的acsii值
QueryStr :='SELECT AUTP FROM `login` WHERE STATE=1 AND USER=$27';
QueryStr := CONCAT(QueryStr,WHMI_Account);
QueryStr := CONCAT(QueryStr,'$27 AND PASSWORD=$27');
QueryStr := CONCAT(QueryStr,WHMI_Password);
QueryStr := CONCAT(QueryStr,'$27 LIMIT 1');
MEM.MemMove(pSource:= ADR(QueryStr), pDestination:= ADR(QueryBytes), uiNumberOfBytes:=INT_TO_UINT(LEN(QueryStr)));
MysqlCmdPack(Cmd:=3 , InputBytes:= QueryBytes, Result=> GVL.Mysql_abyTx, ResultSize=>GVL.Mysql_WriteSize );
GVL.B_Mysql_Send :=TRUE;
LoginStep := LoginStep +1; //下一步
11:
IF GVL.B_Mysql_FF OR GVL.B_Mysql_FE THEN
B_Login := FALSE; //数据库错误，退出登录
END_IF
IF GVL.B_Mysql_Result THEN
MysqlFirstRowDecode(InputRxBytes:=GVL.Mysql_abyRx , FirstRowInfo=>MysqlFirstRowInfo );
IF MysqlFirstRowInfo.IsNull THEN
//回复为空，即账号密码错误
B_Login := FALSE;
LoginStep:=99;
B_LoginDeny :=TRUE;
ELSE
//回复为真，账号密码正确，并获取授权值
GVL.B_Mysql_Login := TRUE;
LoginStep:=99;
AuthpStr :=EmptyAuthPStr;
tmpUint :=MysqlFirstRowInfo.RowData[0];
MEM.MemMove(pSource:= ADR(MysqlFirstRowInfo.RowData)+1, pDestination:= ADR(AuthpStr), uiNumberOfBytes:=tmpUint);
GVL.Mysql_AccountAutp :=INT_TO_BYTE( STRING_TO_INT(AuthpStr));
END_IF
END_IF
GVL.RB_Mysql_Logining:=FALSE;
ELSE
//NTHONG
END_CASE
ELSE
GVL.RB_Mysql_Logining:=FALSE;
END_IF
//登录提示
IF B_LoginDeny OR GVL.B_Mysql_FF OR GVL.B_Mysql_AuthFalure THEN
B_LoginDeny :=FALSE;
END_IF
四、重要提示
这里要特别提醒Codesys的2个bug。
1，多次修改结构体的变量顺序（struct）后，其内部的变量的字节排列是不确定的。需要及时通过CleanAll再GenerateCode，来保证结构体的变量的字节顺序是根据最新的顺序。当结构体字节顺序不正确，不仅使用 MEM.MemMove内存复制操作会出现字节顺序错误，会发现明显数据错乱，同时在日常普通程序运行过程，也会有出现不易发现的数据和位的错乱。请务必及时CleanAll。
2，Codesys的socket在连接状态下，如果出现断电，会导致Codesys的掉电保存动作失效。也就是PtVars的变量是没有及时更新为断电前一刻。所以，需要每次操作完mysql，及时断开socket。
五、总结
自己写Mysql操作库获取更大自由度在于以下2个方面。
1，Codesys的String操作函数所能操作的字符数为255个字符。变量的字符数超过255，无法正确实施字符操作。而实际应用过程，255个字符对于Insert,Update命令太少了。
2，对Query查询结果的解析和利用，可以更直接。
439
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
