Wincc报警记录的相关操作SQL Dim DNRT,DSN Dim con,conn,oRs,oCom,sSQL Dim con1,conn1,oRs1,oCom1,sSQL1 Dim BH Dim rc Dim i DNRT = HMIRuntime.Tags("@DatasourceNameRT").Read BH = ScreenItems("bianhao").InputValue con= "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog="&DNRT&";Data Source=.\wincc" Set conn= CreateObject("ADODB.Connection") conn.ConnectionString=con conn.CursorLocation=3 conn.Open Set oRs=CreateObject("ADODB.Recordset") Set oCom= CreateObject("ADODB.Command") Set oCom.ActiveConnection=conn oCom.CommandType=1 sSQL="SELECT DSN FROM AMT WHERE AppName='ALG' " oCom.CommandText=sSQL Set oRS=oCom.Execute rc=oRS.RecordCount oRS.movefirst For i = 1 To rc DSN=oRs.fields(0).Value con1= "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog="&DSN&";Data Source=.\wincc" Set conn1= CreateObject("ADODB.Connection") conn1.ConnectionString=con1 conn1.CursorLocation=3 conn1.Open Set oRs1=CreateObject("ADODB.Recordset") Set oCom1= CreateObject("ADODB.Command") Set oCom1.ActiveConnection=conn1 oCom1.CommandType=1 sSQL1="DELETE FROM [MsArcLong] WHERE MsgNr = "&BH oCom1.CommandText=sSQL1 Set oRS1=oCom1.Execute Set oRS1=Nothing oRS.movenext Next Set oRS=oCom.Execute Set oRS=Nothing conn.close Set conn=Nothing conn1.close Set conn1=Nothing Msgbox("对应编号报警已经删除，重新打开此画面查看。")

西门子CPU读写MSSQL 开放性源代码： TYPE "LSql_typeLoginInformation" VERSION : 0.1 STRUCT hostName : String; userName : String; password : String; appName : String; serverName : String; libraryName : String; local : String; databaseName : String; sspi : String; attachDbfile : String; changePassword : String; END_STRUCT; END_TYPE TYPE "LSql_typeDiagnostics" VERSION : 0.1 STRUCT status { ExternalVisible := 'False'; ExternalWritable := 'False'} : Word; subfunctionStatus { ExternalVisible := 'False'; ExternalWritable := 'False'} : Word; stateNumber { ExternalVisible := 'False'; ExternalWritable := 'False'} : DInt; END_STRUCT; END_TYPE TYPE "LSql_typeReceivedData" VERSION : 0.1 STRUCT len : UDInt; bytes : Array[0..1023] of Byte; END_STRUCT; END_TYPE TYPE "LSql_typeSqlBatchData" VERSION : 0.1 STRUCT header : Struct "type" : Byte := 16#01; status : Byte := 16#01; length : Word := 16#001E; channel : Word := 16#0000; packet : Byte := 16#01; window : Byte := 16#00; END_STRUCT; sqlBatch : Struct totalLength : DWord := 16#1600_0000; header : Struct headerLength : DWord := 16#1200_0000; headerType : Word := 16#0200; mars : Struct transactionDescriptor : Array[0..7] of Byte; outstandingRequestCount : DWord := 16#0100_0000; END_STRUCT; END_STRUCT; sqlText : Array[0..1023] of Byte; END_STRUCT; END_STRUCT; END_TYPE TYPE "LSql_typeLoginData" VERSION : 0.1 STRUCT header : Struct "type" : Byte := 16#10; status : Byte := 16#01; length : Word := 16#0090; spid : Word := 16#0000; packet : Byte := 16#01; window : Byte := 16#00; END_STRUCT; login : Struct length : DWord := 16#8800_0000; tdsVersion : DWord := 16#0200_0972; packetSize : DWord := 16#0010_0000; clientProgVer : DWord := 16#0000_0007; clientId : DWord := 16#0001_0000; conncetionId : DWord := 16#0000_0000; optionFlags1 : Byte := 16#E0; optionFlags2 : Byte := 16#03; typeFlags : Byte := 16#00; optionFlags3 : Byte := 16#00; clientTimeZone : DWord := 16#E001_0000; clientLCID : DWord := 16#0904_0000; offsetLength : Struct hostName : Struct offset : Word := 16#5E00; length : Word := 16#0800; END_STRUCT; userName : Struct offset : Word := 16#6E00; length : Word := 16#0200; END_STRUCT; password : Struct offset : Word := 16#7200; length : Word := 16#0000; END_STRUCT; appName : Struct offset : Word := 16#7200; length : Word := 16#0700; END_STRUCT; serverName : Struct offset : Word := 16#8000; length : Word := 16#0000; END_STRUCT; unused : Struct offset : Word := 16#8000; length : Word := 16#0000; END_STRUCT; libraryName : Struct offset : Word := 16#8000; length : Word := 16#0400; END_STRUCT; local : Struct offset : Word := 16#8800; length : Word := 16#0000; END_STRUCT; databaseName : Struct offset : Word := 16#8800; length : Word := 16#0000; END_STRUCT; clientId : Array[0..5] of Byte := 16#000D, 16#007D, 16#0095, 16#0036, 16#0063, 16#0028; sspi : Struct offset : Word := 16#8800; length : Word := 16#0000; END_STRUCT; atchDbFile : Struct offset : Word := 16#8800; length : Word := 16#0000; END_STRUCT; changePassword : Struct offset : Word := 16#8800; length : Word := 16#0000; END_STRUCT; cbSspiLong : DWord := 16#0000_0000; END_STRUCT; data : Array[0..1023] of Byte; END_STRUCT; END_STRUCT; END_TYPE TYPE "LSql_typePreloginData" VERSION : 0.1 STRUCT header : Struct "type" : Byte := 16#12; status : Byte := 16#01; length : Word := 16#005E; channel : Word := 16#0000; packet : Byte := 16#01; window : Byte := 16#00; END_STRUCT; data : Struct option : Struct "version" : Struct token : Byte := 16#00; offset : Word := 16#0024; length : Word := 16#0006; END_STRUCT; encryption : Struct token : Byte := 16#01; offset : Word := 16#002A; length : Word := 16#0001; END_STRUCT; instOpt : Struct token : Byte := 16#02; offset : Word := 16#002B; length : Word := 16#0001; END_STRUCT; threadId : Struct token : Byte := 16#03; offset : Word := 16#002C; length : Word := 16#0004; END_STRUCT; mars : Struct token : Byte := 16#04; offset : Word := 16#0030; length : Word := 16#0001; END_STRUCT; traceId : Struct token : Byte := 16#05; offset : Word := 16#0031; length : Word := 16#0024; END_STRUCT; fedAuthRequired : Struct token : Byte := 16#06; offset : Word := 16#0055; length : Word := 16#0001; END_STRUCT; terminator : Byte := 16#FF; END_STRUCT; data : Array[0..49] of Byte := 16#000C, 16#0000, 16#0013, 16#0088, 2(16#0000), 16#0002, 3(16#0000), 16#00B8, 16#000D, 16#0000, 16#00DD, 16#00DA, 16#0047, 16#001C, 16#006C, 16#0079, 16#00D4, 16#004D, 16#00AC, 16#0052, 16#007F, 16#0033, 16#0086, 16#00C7, 16#0042, 16#006A, 16#00BB, 16#00F2, 16#0026, 16#00EF, 16#0011, 16#0072, 16#000C, 16#0048, 16#00A8, 16#0066, 16#004B, 16#00F4, 16#004A, 16#00CD, 16#00E2, 16#00A4, 16#0001, 3(16#0000), 3(16#0001); END_STRUCT; END_STRUCT; END_TYPE FUNCTION "LSql_WriteData" : Void { S7_Optimized_Access := 'TRUE' } VERSION : 0.1 VAR_INPUT inString : String; offsetOwn : Word; END_VAR VAR_OUTPUT error : Bool; status : Word; offsetNext : Word; length : Word; END_VAR VAR_IN_OUT data : Array[0..1023] of Byte; writer : Int; END_VAR VAR_TEMP tempStatus : Word; tempBytes : Array[0..123] of Byte; tempBytesFinal : Array[0..123] of Byte; tempWriter : Int; tempLen : DInt; tempCounter : DInt; tempOffsetNext : Word; tempLength : Word; tempOffsetAsDInt : DInt; tempLenAsUInt : UInt; END_VAR VAR CONSTANT STATUS_FINISHED_NO_ERROR : Word := 16#0000; ERR_UNDEFINED : Word := 16#8000; ERR_IN_BLOCK_OPERATION : Word := 16#8001; ERR_PARAMETRIZATION : Word := 16#8200; ERR_PROCESSING_EXTERN : Word := 16#8400; ERR_PROCESSING_INTERN : Word := 16#8600; ERR_AREA_RESERVED : Word := 16#8800; ERR_USER_DEFINED_CLASSES : Word := 16#9000; END_VAR BEGIN REGION BLOCK INFO HEADER //=============================================================================== // (Siemens AG (c)Copyright (2020) //------------------------------------------------------------------------------- // Title: LSql_WriteData // Comment/Function: serialize input string to array of bytes // Library/Family: LSql // Author: Siemens SUP Applications //Target: S7-1500 / FW 2.6 // Engineering: TIA Portal (V16) // Restrictions: no // Requirements: CPU 1500 //------------------------------------------------------------------------------- // Change log table: // Version | Date | Expert in charge | Changes applied //----------|------------|--------------------------|------------------------------ // 01.00.00 | 2020-02-19 | Siemens SUP Applications | First released version //=============================================================================== END_REGION REGION INITIALISATION // TODO: Initialize functionality: reset of variables, diagnostics, etc. #tempLenAsUInt := 0; #tempLen := 0; #tempCounter := 0; #tempOffsetAsDInt := 0; #tempOffsetNext := 16#0000; #tempStatus := #STATUS_FINISHED_NO_ERROR; END_REGION REGION PROGRAM LOGIC //convert string to char array Strg_TO_Chars(Strg := #inString, pChars := 0, Cnt => #tempLenAsUInt, Chars := #tempBytes); #tempLen := UINT_TO_DINT(#tempLenAsUInt); //fill in zero bytes between each letter FOR #tempCounter := 0 TO #tempLen - 1 DO #data[#writer] := #tempBytes[#tempCounter]; #writer += 1; #data[#writer] := 16#00; #writer += 1; END_FOR; //set offset to length * 2 cause of filled in zero bytes #tempOffsetAsDInt := WORD_TO_DINT(#offsetOwn); #tempOffsetAsDInt := ROR(IN := #tempOffsetAsDInt, N := 8) + #tempLen * 2; #tempOffsetNext := DINT_TO_WORD(#tempOffsetAsDInt); #tempOffsetNext := ROL(IN := #tempOffsetNext, N := 8); //set length to the number of letters(low Byte, high Byte) #tempLength := ROL(IN := DINT_TO_WORD(#tempLen), N := 8); // so lassen END_REGION REGION OUTPUTS #offsetNext := #tempOffsetNext; #length := #tempLength; #error := #tempStatus.%X15; #status := #tempStatus; // if ENO mechanism is not used / not wanted replace the following line by --> ENO := TRUE; ENO := NOT #tempStatus.%X15; END_REGION END_FUNCTION FUNCTION "LSql_WritePassword" : Void { S7_Optimized_Access := 'TRUE' } VERSION : 0.1 VAR_INPUT inString : String; offsetOwn : Word; END_VAR VAR_OUTPUT error : Bool; status : Word; offsetNext : Word; length : Word; END_VAR VAR_IN_OUT data : Array[0..1023] of Byte; writer : Int; END_VAR VAR_TEMP tempStatus : Word; tempConvBytes : Array[0..123] of Byte; tempLen : DInt; tempCounter : DInt; tempLowBits : Byte; tempHighBits : Byte; tempLenAsUInt : UInt; tempOffsetNext : Word; tempOffsetAsDInt : DInt; tempLength : Word; END_VAR VAR CONSTANT STATUS_FINISHED_NO_ERROR : Word := 16#0000; ERR_UNDEFINED : Word := 16#8000; ERR_IN_BLOCK_OPERATION : Word := 16#8001; ERR_PARAMETRIZATION : Word := 16#8200; ERR_PROCESSING_EXTERN : Word := 16#8400; ERR_PROCESSING_INTERN : Word := 16#8600; ERR_AREA_RESERVED : Word := 16#8800; ERR_USER_DEFINED_CLASSES : Word := 16#9000; END_VAR BEGIN REGION BLOCK INFO HEADER //=============================================================================== // (Siemens AG (c)Copyright (2020) //------------------------------------------------------------------------------- // Title: LSql_WriteData // Comment/Function: serialize input string (password) to array of bytes // Library/Family: LSql // Author: Siemens SUP Applications //Target: S7-1500 / FW 2.6 // Engineering: TIA Portal (V16) // Restrictions: no // Requirements: CPU 1500 //------------------------------------------------------------------------------- // Change log table: // Version | Date | Expert in charge | Changes applied //----------|------------|--------------------------|------------------------------ // 01.00.00 | 2020-02-19 | Siemens SUP Applications | First released version //=============================================================================== END_REGION REGION INITIALISATION // TODO: Initialize functionality: reset of variables, diagnostics, etc. #tempLenAsUInt := 0; #tempLen := 0; #tempCounter := 0; #tempOffsetAsDInt := 0; #tempOffsetNext := 16#0000; #tempStatus := #STATUS_FINISHED_NO_ERROR; END_REGION REGION PROGRAM LOGIC Strg_TO_Chars(Strg := #inString, pChars := 0, Cnt => #tempLenAsUInt, Chars := #tempConvBytes); #tempLen := UINT_TO_DINT(#tempLenAsUInt); //convert password (switch low<->high bits and xor 16#A5) FOR #tempCounter := 0 TO #tempLen - 1 DO #tempConvBytes[#tempCounter] := ROR(IN := #tempConvBytes[#tempCounter], N := 4) XOR 16#A5; END_FOR; //fill in 16#A5 bytes between each letter FOR #tempCounter := 0 TO #tempLen - 1 DO #data[#writer] := #tempConvBytes[#tempCounter]; #writer += 1; #data[#writer] := 16#A5; #writer += 1; END_FOR; //set offset to length * 2 cause of filled in zero bytes #tempOffsetAsDInt := WORD_TO_DINT(#offsetOwn); #tempOffsetAsDInt := ROR(IN := #tempOffsetAsDInt, N := 8) + #tempLen * 2; #tempOffsetNext := DINT_TO_WORD(#tempOffsetAsDInt); #tempOffsetNext := ROL(IN := #tempOffsetNext, N := 8); //set length to the number of letters(low Byte, high Byte) #tempLength := ROL(IN := DINT_TO_WORD(#tempLen), N := 8); END_REGION REGION OUTPUTS #length := #tempLength; #offsetNext := #tempOffsetNext; #error := #tempStatus.%X15; #status := #tempStatus; // if ENO mechanism is not used / not wanted replace the following line by --> ENO := TRUE; ENO := NOT #tempStatus.%X15; END_REGION END_FUNCTION FUNCTION_BLOCK "LSql_Microsoft" { S7_Optimized_Access := 'TRUE' } AUTHOR : '(department/personInCharge/contact)' FAMILY : '(family)' VERSION : 0.1 VAR_INPUT enable : Bool; connSettings {InstructionName := 'TCON_IP_v4'; LibVersion := '1.0'} : TCON_IP_v4; loginInfo : "LSql_typeLoginInformation"; executeSqlCommand : Bool; sqlCommand : String; enableArchive : Bool; "db" : DB_ANY; dbmax : DInt; END_VAR VAR_OUTPUT valid { ExternalWritable := 'False'} : Bool; busy { ExternalWritable := 'False'} : Bool; error { ExternalWritable := 'False'} : Bool; status { ExternalWritable := 'False'} : Word := #STATUS_NO_CALL; diagnostics { ExternalWritable := 'False'} : "LSql_typeDiagnostics"; lastArchivElement { ExternalWritable := 'False'} : DInt; sqlCommandDone : Bool; END_VAR VAR statEnableOld { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Bool; statEnable { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Bool; statValid { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Bool; statBusy { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Bool; statError { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Bool; statErrorUserCleared { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Bool; statErrorAutoCleared { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Bool; statStatus { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Word := #STATUS_NO_CALL; statSubfunctionStatus { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Word; statFBErrorState { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : DInt; statFBState { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : DInt := #FB_STATE_NO_PROCESSING; statDisablingCompleted { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Bool; statEmptyDiagnostics { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : "LSql_typeDiagnostics"; statArchiveRequest { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Bool; statRcvData { ExternalWritable := 'False'} : "LSql_typeReceivedData"; statExecuteSqlBatchOld { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Bool; statArchiveIndex { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : DInt; statWriteToArchiveStatus { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Word; statSqlBatchData { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : "LSql_typeSqlBatchData"; statSqlBatch { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Array[0..2047] of Byte; statSqlBatchLen { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : DInt; statWriter { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Int; statStatusSqlBatchIntToWord { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Word; statLoginData { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : "LSql_typeLoginData"; statLogin { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Array[0..2047] of Byte; statLoginLen { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : DInt; statStatusLoginIntToWord { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Word; statStatusPreLoginIntToWord { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Word; statPreLogin { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Array[0..2047] of Byte; statPreLoginLen { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : DInt; statPreLoginCache { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Array[0..2047] of Byte; statPreLoginData { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : "LSql_typePreloginData"; instTsend {InstructionName := 'TSEND'; LibVersion := '4.0'; ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : TSEND; instTcon {InstructionName := 'TCON'; LibVersion := '4.0'; ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : TCON; instTrcv {InstructionName := 'TRCV'; LibVersion := '4.0'; ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : TRCV; instTdiscon {InstructionName := 'TDISCON'; LibVersion := '2.1'; ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : TDISCON; statErrorWriteData { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Bool; statWriteData { ExternalAccessible := 'False'; ExternalVisible := 'False'; ExternalWritable := 'False'} : Word; statSqlCommandDone : Bool; statTransmissionSqlCommand : Bool; END_VAR VAR_TEMP tempPos : DInt; tempLength : Word; tempCounter : DInt; tempGaps : Int; tempLengthAsInt : DInt; tempLengthRor : DInt; END_VAR VAR CONSTANT ERR_TDISCON_REMOTE_TERMINATION : Word := 16#80A3; FB_STATE_NO_PROCESSING : DInt := 0; FB_STATE_DISABLING : DInt := 1; FB_STATE_INITIAL_DISCON : DInt := 2; FB_STATE_CONNECT : DInt := 3; FB_STATE_PRELOGIN : DInt := 4; FB_STATE_LOGIN : DInt := 5; FB_STATE_SENDLOGIN : DInt := 6; FB_STATE_SQLBATCH : DInt := 7; STATUS_NO_CALL : Word := 16#7000; STATUS_FIRST_CALL : Word := 16#7001; STATUS_SUBSEQUENT_CALL : Word := 16#7002; STATUS_TDISCON_SUCCESSFUL : Word := 16#7011; SUB_STATUS_NO_ERROR : Word := 16#0000; ERR_UNDEFINED_STATE : Word := 16#8600; ERR_IN_BLOCK_OPERATION : Word := 16#8001; ERR_PARAMETRIZATION : Word := 16#8200; ERR_PROCESSING_EXTERN : Word := 16#8400; ERR_PROCESSING_INTERN : Word := 16#8600; ERR_AREA_RESERVED : Word := 16#8800; ERR_USER_DEFINED_CLASSES : Word := 16#9000; ERR_DISCONNECT : Word := 16#8601; ERR_CONNECT : Word := 16#8602; ERR_PRELOGIN : Word := 16#8603; ERR_PRELOGIN_DATA : Word := 16#8607; ERR_LOGIN : Word := 16#8604; ERR_LOGIN_DATA : Word := 16#8608; ERR_TRCV : Word := 16#8605; ERR_ARCHIVE : Word := 16#8606; ERR_SQLBATCH_DATA : Word := 16#8609; ERR_SQLBATCHSEND : Word := 16#8610; END_VAR BEGIN REGION BLOCK INFO HEADER //============================================================================= // Siemens AG (c)Copyright (2019) //----------------------------------------------------------------------------- // Library: SQL // Target: S7-1500 / FW 2.6 // Engineering: TIA Portal V15.1 // Restrictions: no // Requirements: CPU 1500 // Functionality: For database access: // set up connection to SQL server, send batch and save answers from server //----------------------------------------------------------------------------- // Change log table: // Version Date Expert in charge Changes applied // 01.00.00 14.01.2019 Siemens SUP Applications First released version //============================================================================= END_REGION #statEnable := #enable; // Work with temporary value / create process image REGION ENABLING/DISABLING IF (#statEnable = TRUE) AND (#statStatus = #STATUS_NO_CALL) THEN // Enable FB // First call; initialize FB #statValid := TRUE; #statBusy := TRUE; #statError := FALSE; #statErrorUserCleared := FALSE; #statErrorAutoCleared := FALSE; #statStatus := #STATUS_FIRST_CALL; #statSubfunctionStatus := #SUB_STATUS_NO_ERROR; #statFBErrorState := 0; #diagnostics := #statEmptyDiagnostics; #statDisablingCompleted := FALSE; #lastArchivElement := 0; // State machine - start functionality #statFBState := #FB_STATE_CONNECT; REGION Initialize functionality: call subsidiary FBs with FALSE #instTcon(REQ := FALSE, ID := #connSettings.ID, CONNECT := #connSettings); #instTrcv(EN_R := FALSE, ID := #connSettings.ID, DATA := #statRcvData); #instTdiscon(REQ := FALSE, ID := #connSettings.ID); #instTsend(REQ := FALSE, ID := #connSettings.ID, LEN := DINT_TO_UINT(#statPreLoginLen), DATA := #statPreLogin); END_REGION // BG TODO - IEC Konforme programmierung - in den baustein eigenschaften aktivieren // ein fehler taucht auf der mit Datntypen zusammenhängt ELSIF (#statEnable = FALSE) AND (#statEnableOld = TRUE) THEN // Disable FB #statFBState := #FB_STATE_DISABLING; ELSIF (#statStatus = #STATUS_FIRST_CALL) THEN // Set status identifier of subsequent call #statStatus := #STATUS_SUBSEQUENT_CALL; END_IF; // Edge detection 'enable' input #statEnableOld := #statEnable; END_REGION ENABLING/DISABLING IF (#statStatus = #STATUS_NO_CALL) THEN // Nothing to do -> End here to reduce "system load" RETURN; END_IF; REGION STATE_MACHINE CASE #statFBState OF // State machine of FB #FB_STATE_NO_PROCESSING: REGION NO Processing // No processing active (Note: this state must always be present and left empty) ; END_REGION NO Processing #FB_STATE_INITIAL_DISCON: REGION make sure no connection is open before connecting #instTdiscon(REQ := TRUE, ID := #connSettings.ID); IF #instTdiscon.ERROR THEN #statStatus := #ERR_DISCONNECT; #statSubfunctionStatus := #instTdiscon.STATUS; #statFBErrorState := #statFBState; IF #ERR_TDISCON_REMOTE_TERMINATION = #instTdiscon.STATUS THEN // disconnect done - proceed with wait for setup // Error can be fixed by FB #statStatus := #STATUS_TDISCON_SUCCESSFUL; #instTdiscon(REQ := FALSE, ID := #connSettings.ID); #statErrorAutoCleared := TRUE; #statFBState := #FB_STATE_CONNECT; ELSE #statErrorUserCleared := TRUE; #instTdiscon(REQ := FALSE, ID := #connSettings.ID); END_IF; ELSIF #instTdiscon.DONE THEN #statStatus := #STATUS_TDISCON_SUCCESSFUL; #instTdiscon(REQ := FALSE, ID := #connSettings.ID); #statFBState := #FB_STATE_CONNECT; END_IF; END_REGION ; #FB_STATE_CONNECT: REGION connecting //if error was set in state before because of tdsicon error: #ERR_TDISCON_REMOTE_TERMINATION, reset #statErrorAutoCleared := FALSE; #instTcon(REQ := TRUE, ID := #connSettings.ID, CONNECT := #connSettings); IF #instTcon.ERROR THEN #statStatus := #ERR_CONNECT; #statSubfunctionStatus := #instTcon.STATUS; #statFBErrorState := #statFBState; #statErrorUserCleared := TRUE; #instTcon(REQ := FALSE, ID := #connSettings.ID, CONNECT := #connSettings); ELSIF #instTcon.DONE THEN #instTcon(REQ := FALSE, ID := #connSettings.ID, CONNECT := #connSettings); #statFBState := #FB_STATE_PRELOGIN; END_IF; END_REGION connecting #FB_STATE_PRELOGIN: REGION Prelogin active //copy data from data to cache #statStatusPreLoginIntToWord := INT_TO_WORD(Serialize(SRC_VARIABLE := #statPreLoginData, DEST_ARRAY => #statPreLoginCache, POS := #tempPos)); IF #statStatusPreLoginIntToWord > 16#0000 THEN #statStatus := #ERR_PRELOGIN_DATA; #statSubfunctionStatus := #statStatusPreLoginIntToWord; #statFBErrorState := #statFBState; #statErrorUserCleared := TRUE; END_IF; //copy data from cache to preLogin (without unwanted filled zero bytes) FOR #tempCounter := 0 TO #tempPos DO //when serializing prelogin data a zero is added in between types //delete these zeros CASE #tempCounter OF 9, 15, 21, 27, 33, 39, 45, 51: #tempGaps += 1; ELSE #statPreLogin[#tempCounter - #tempGaps] := #statPreLoginCache[#tempCounter]; END_CASE; END_FOR; //write number of bytes, that have to be transmitted, to preLoginLen (adjusted by the deleted gaps) #statPreLoginLen := #tempPos - #tempGaps; #instTsend(REQ := TRUE, ID := #connSettings.ID, LEN := DINT_TO_UINT(#statPreLoginLen), DATA := #statPreLogin); IF #instTsend.ERROR THEN #statStatus := #ERR_PRELOGIN; #statErrorUserCleared := TRUE; #statSubfunctionStatus := #instTsend.STATUS; #statFBErrorState := #statFBState; #instTsend(REQ := FALSE, ID := #connSettings.ID, DATA := #statPreLogin); ELSIF #instTsend.DONE THEN #instTsend(REQ := FALSE, ID := #connSettings.ID, DATA := #statPreLogin); #statFBState := #FB_STATE_LOGIN; END_IF; END_REGION Prelogin #FB_STATE_LOGIN: // Login active REGION Login REGION write login data to DB #statWriter := 0; //varible for iterating through data array #tempPos := 0; REGION write String TO data AND set length AND offset in offsetLength (hostName) "LSql_WriteData"(inString := #loginInfo.hostName, offsetOwn := #statLoginData.login.offsetLength.hostName.offset, offsetNext => #statLoginData.login.offsetLength.userName.offset, length => #statLoginData.login.offsetLength.hostName.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); //write string to data and set length and offset in offsetLength (userName) "LSql_WriteData"(inString := #loginInfo.userName, offsetOwn := #statLoginData.login.offsetLength.userName.offset, offsetNext => #statLoginData.login.offsetLength.password.offset, length => #statLoginData.login.offsetLength.userName.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); //write string to data and set length and offset in offsetLength (password) "LSql_WritePassword"(inString := #loginInfo.password, offsetOwn := #statLoginData.login.offsetLength.password.offset, offsetNext => #statLoginData.login.offsetLength.appName.offset, length => #statLoginData.login.offsetLength.password.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); //write string to data and set length and offset in offsetLength (appName) "LSql_WriteData"(inString := #loginInfo.appName, offsetOwn := #statLoginData.login.offsetLength.appName.offset, offsetNext => #statLoginData.login.offsetLength.serverName.offset, length => #statLoginData.login.offsetLength.appName.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); //write string to data and set length and offset in offsetLength (serverName) "LSql_WriteData"(inString := #loginInfo.serverName, offsetOwn := #statLoginData.login.offsetLength.serverName.offset, offsetNext => #statLoginData.login.offsetLength.unused.offset, length => #statLoginData.login.offsetLength.serverName.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); //write string to data and set length and offset in offsetLength (unused) "LSql_WriteData"(inString := '', offsetOwn := #statLoginData.login.offsetLength.unused.offset, offsetNext => #statLoginData.login.offsetLength.libraryName.offset, length => #statLoginData.login.offsetLength.unused.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); //write string to data and set length and offset in offsetLength (libraryName) "LSql_WriteData"(inString := #loginInfo.libraryName, offsetOwn := #statLoginData.login.offsetLength.libraryName.offset, offsetNext => #statLoginData.login.offsetLength.local.offset, length => #statLoginData.login.offsetLength.libraryName.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); //wrtie string to data and set length and offset in offsetLength (local) "LSql_WriteData"(inString := #loginInfo.local, offsetOwn := #statLoginData.login.offsetLength.local.offset, offsetNext => #statLoginData.login.offsetLength.databaseName.offset, length => #statLoginData.login.offsetLength.local.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); //write string to data and set length and offset in offsetLength (databaseName) "LSql_WriteData"(inString := #loginInfo.databaseName, offsetOwn := #statLoginData.login.offsetLength.databaseName.offset, offsetNext => #statLoginData.login.offsetLength.sspi.offset, length => #statLoginData.login.offsetLength.databaseName.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); //string string to data and set length and offset in offsetLength (sspi) "LSql_WriteData"(inString := #loginInfo.sspi, offsetOwn := #statLoginData.login.offsetLength.sspi.offset, offsetNext => #statLoginData.login.offsetLength.atchDbFile.offset, length => #statLoginData.login.offsetLength.sspi.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); //string to data and set length and offset in offsetLength (atchDbFile) "LSql_WriteData"(inString := #loginInfo.attachDbfile, offsetOwn := #statLoginData.login.offsetLength.atchDbFile.offset, offsetNext => #statLoginData.login.offsetLength.changePassword.offset, length => #statLoginData.login.offsetLength.atchDbFile.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); //string to data and set length and offset in offsetLength (changePassword) "LSql_WritePassword"(inString := #loginInfo.changePassword, offsetOwn := #statLoginData.login.offsetLength.changePassword.offset, offsetNext => #statLoginData.login.length, length => #statLoginData.login.offsetLength.changePassword.length, error => #statErrorWriteData, status => #statWriteData, data := #statLoginData.login.data, writer := #statWriter); END_REGION ; //set length parameters according to added data #tempLengthAsInt := DWORD_TO_DINT(#statLoginData.login.length); //rotate by 8 because word #tempLengthRor := ROR(IN := #tempLengthAsInt, N := 8) + 8; #statLoginData.header.length := DINT_TO_WORD(#tempLengthRor); //shift by 16 dword #statLoginData.login.length := SHL(IN := #statLoginData.login.length, N := 16); END_REGION //copy standard data to login #statStatusLoginIntToWord := INT_TO_WORD(Serialize(SRC_VARIABLE := #statLoginData, DEST_ARRAY => #statLogin, POS := #tempPos)); //check for error in INT_TO_WORD IF #statStatusLoginIntToWord > 16#0000 THEN #statStatus := #ERR_LOGIN_DATA; #statSubfunctionStatus := #statStatusLoginIntToWord; #statFBErrorState := #statFBState; #statErrorUserCleared := TRUE; END_IF; //write length to loginLen (adjusted by the length of the data (-1024+writer) and deleted gaps in the header) #statLoginLen := #tempPos - 1024 + #statWriter; // BG TODO - warum 1024 - nicht 2048? // Keine magic numbers ;) //deletes last zero byte, if necessary // BG TODO ein kommentar dazu der aussagt warum modulo 2 addiert wird wäre dann antürlich notwendig #statLoginLen := #statLoginLen + #tempPos MOD 2; #statFBState := #FB_STATE_SENDLOGIN; END_REGION ; #FB_STATE_SENDLOGIN: REGION send login data #instTsend(REQ := #enable, ID := #connSettings.ID, LEN := DINT_TO_UINT(#statLoginLen), DATA := #statLogin); IF #instTsend.ERROR THEN #statStatus := #ERR_LOGIN; #statSubfunctionStatus := #instTsend.STATUS; #statFBErrorState := #statFBState; #statErrorUserCleared := TRUE; #instTsend(REQ := FALSE, ID := #connSettings.ID, DATA := #statLogin); ELSIF #instTsend.DONE THEN #instTsend(REQ := FALSE, ID := #connSettings.ID, DATA := #statLogin); #statFBState := #FB_STATE_SQLBATCH; END_IF; END_REGION send login data #FB_STATE_SQLBATCH: REGION sqlbatch execution REGION call trcv #instTrcv(EN_R := TRUE, ID := #connSettings.ID, DATA := #statRcvData); IF #instTrcv.ERROR THEN #statStatus := #ERR_TRCV; #statErrorUserCleared := TRUE; #statSubfunctionStatus := #instTrcv.STATUS; #statFBErrorState := #statFBState; END_IF; END_REGION REGION Archive IF #enableArchive AND #statRcvData.len > 0 THEN //write data to the arrayDB #statWriteToArchiveStatus := INT_TO_WORD(WriteToArrayDB(db := #db, index := #statArchiveIndex, value := #statRcvData)); #lastArchivElement := #statArchiveIndex; //raise the index for the next call #statArchiveIndex += 1; IF #statArchiveIndex > #dbmax THEN #statArchiveIndex := 0; END_IF; END_IF; IF #statArchiveRequest AND (#statWriteToArchiveStatus > 16#0000) THEN #statStatus := #ERR_ARCHIVE; #statSubfunctionStatus := #statWriteToArchiveStatus; #statFBErrorState := #statFBState; #statErrorUserCleared := TRUE; END_IF; END_REGION Archive REGION SqlBatch //get positive edge of executeSqlBatch IF #executeSqlCommand AND NOT #statExecuteSqlBatchOld THEN #statWriter := 0; //write string to data and set length parameter accoding to the added data "LSql_WriteData"(inString := #sqlCommand, offsetOwn := 16#1E00, offsetNext => #statSqlBatchData.header.length, error => #statErrorWriteData, status => #statWriteData, length => #tempLength, data := #statSqlBatchData.sqlBatch.sqlText, writer := #statWriter); //transform (lowByte highByte) to (highByte lowByte) #statSqlBatchData.header.length := ROR(IN := #statSqlBatchData.header.length, N := 8); //copy data from data to cache #statStatusSqlBatchIntToWord := INT_TO_WORD(Serialize(SRC_VARIABLE := #statSqlBatchData, DEST_ARRAY => #statSqlBatch, POS := #tempPos)); //check for error in Int_To_Word Function IF #statStatusSqlBatchIntToWord > 16#0000 THEN #statStatus := #ERR_SQLBATCH_DATA; #statSubfunctionStatus := #statStatusSqlBatchIntToWord; #statFBErrorState := #statFBState; #statErrorUserCleared := TRUE; END_IF; //write length to DB, adjusted by the length of the data (-1024+writer) #statSqlBatchLen := #tempPos - 1024 + #statWriter; //deletes last zero byte, if necessary #statSqlBatchLen += #tempPos MOD 2; #statTransmissionSqlCommand := TRUE; #statExecuteSqlBatchOld := #executeSqlCommand; END_IF; //finished creating stream //get the negative edge IF NOT #executeSqlCommand AND #statExecuteSqlBatchOld THEN #statExecuteSqlBatchOld := #executeSqlCommand; #statSqlCommandDone := FALSE; END_IF; //send data IF #statTransmissionSqlCommand THEN #instTsend(REQ := TRUE, ID := #connSettings.ID, LEN := DINT_TO_UINT(#statSqlBatchLen), DATA := #statSqlBatch); //check for error in Tsend IF #instTsend.ERROR THEN #statStatus := #ERR_SQLBATCHSEND; #statSubfunctionStatus := #instTsend.STATUS; #statFBErrorState := #statFBState; #statErrorUserCleared := TRUE; #statTransmissionSqlCommand := FALSE; ELSIF #instTsend.DONE THEN #instTsend(REQ := FALSE, ID := #connSettings.ID, LEN := DINT_TO_UINT(#statSqlBatchLen), DATA := #statSqlBatch); #statTransmissionSqlCommand := FALSE; #statSqlCommandDone := TRUE; END_IF; END_IF; END_REGION SqlBatch END_REGION sqlbatch execution #FB_STATE_DISABLING: // Disconnect active REGION Disabling #instTrcv(EN_R := FALSE, ID := #connSettings.ID, DATA := #statRcvData); END_REGION ; REGION Disconnect //disconnect function, closes the connection to the server #instTdiscon(REQ := TRUE, ID := #connSettings.ID); IF #instTdiscon.ERROR THEN #statStatus := #ERR_DISCONNECT; #statSubfunctionStatus := #instTdiscon.STATUS; #statFBErrorState := #statFBState; IF #ERR_TDISCON_REMOTE_TERMINATION = #instTdiscon.STATUS THEN #statDisablingCompleted := TRUE; #instTdiscon(REQ := FALSE, ID := #connSettings.ID); ELSE #statErrorUserCleared := TRUE; #instTdiscon(REQ := FALSE, ID := #connSettings.ID); END_IF; ELSIF #instTdiscon.DONE THEN #instTdiscon(REQ := FALSE, ID := #connSettings.ID); END_IF; // BG TODO - hier noch mal schauen wann die Tags gesetzt werden - nach fehler oder done? // Disabling completed beendet den baustein komplet, und statStatus wird auf "No Call" gesetzt, // daurch kommt man hier nie mehr vorbei für die asynchrionen bedinungen #statFBState := #FB_STATE_NO_PROCESSING; #statDisablingCompleted := TRUE; END_REGION Disconnect ELSE // Undefined state in state machine reached REGION undefined state #statStatus := #ERR_UNDEFINED_STATE; #statErrorUserCleared := TRUE; END_REGION undefined state END_CASE; END_REGION STATE_MACHINE REGION OUTPUTS // Write outputs IF (#statDisablingCompleted = TRUE) THEN REGION disabling // Reset outputs if disabling completed #statValid := FALSE; #statBusy := FALSE; #statError := FALSE; #statErrorUserCleared := FALSE; #statErrorAutoCleared := FALSE; #statStatus := #STATUS_NO_CALL; END_REGION disabling ELSIF (#statErrorUserCleared = TRUE) AND (#statError = FALSE) THEN REGION error user cleared // Error can only be cleared by user; rising edge at enable input is needed to continue #statValid := FALSE; #statBusy := FALSE; #statError := TRUE; // Write diagnostics #diagnostics.status := #statStatus; #diagnostics.subfunctionStatus := #statSubfunctionStatus; #diagnostics.stateNumber := #statFBErrorState; // execution aborted --> set state no processing #statFBState := #FB_STATE_NO_PROCESSING; END_REGION error user cleared ELSIF (#statErrorAutoCleared = TRUE) AND (#statError = FALSE) THEN REGION error auto cleared // Error can be reset by FB automatically #statValid := FALSE; #statBusy := TRUE; #statError := TRUE; // Write diagnostics #diagnostics.status := #statStatus; #diagnostics.subfunctionStatus := #statSubfunctionStatus; #diagnostics.stateNumber := #statFBErrorState; END_REGION error auto cleared ELSIF (#statErrorAutoCleared = FALSE) AND (#statErrorUserCleared = FALSE) AND (#statError = TRUE) THEN REGION after user/auto cleared // If autocleared error is acknowledged #statValid := TRUE; #statBusy := TRUE; #statError := FALSE; // TODO: remove this line if more detailed status information is used instead of "#STATUS_SUBSEQUENT_CALL" #statStatus := #STATUS_SUBSEQUENT_CALL; END_REGION after user/auto cleared END_IF; REGION Write static values to outputs #valid := #statValid; #busy := #statBusy; #error := #statError; #status := #statStatus; #sqlCommandDone := #statSqlCommandDone; // if ENO mechanism is not used / not wanted replace the following line by --> ENO := TRUE; ENO := NOT #statError; END_REGION Write static values to outputs END_REGION OUTPUTS END_FUNCTION_BLOCK

此内容被密码保护 加密文章，请前往内页查看详情

Wincc读写MSSQL '定义变量，注意VBS一条命令占据一行，换行后无效！ Dim conn Dim con Dim sSQL Dim oRS Dim oCom con= "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=数据库名称;Data Source=服务器" Set conn= CreateObject("ADODB.Connection") conn.ConnectionString=con conn.CursorLocation=3 conn.Open Set oRs=CreateObject("ADODB.Recordset") Set oCom= CreateObject("ADODB.Command") Set oCom.ActiveConnection=conn oCom.CommandType=1 sSQL="填写查询写入语句" '写入数据库：sSQL="INSERT INTO [表名称] ([字段名称]) VALUES ('写入的值');" '更新数据库：sSQL= "UPDATE [表名称] Set [字段名称]='写入的值' where [字段名称]='值'" oCom.CommandText=sSQL Set oRS=oCom.Execute Set oRS=Nothing conn.close Set conn=Nothing