Home

48354880_WinAC_SQL_DB_Doc_V12_en

1. 14 3 2 3 Installation of GUI Tools a 15 3 2 4 DEINE q sehen 17 3 2 5 Transaction Safe Data Transmission to MySQL Server 19 3 3 Installation WinAC Driver on Runtime Computer 19 3 3 1 Ins alla 0m OR lee E ere eRe nee are ieee 19 3 3 2 Installation and Parameterisation of the ODBC Driver 19 3 4 Installation WinAC Driver on SIMATIC Engineering Computer 22 4 Installation MSSOL uuu acta u uQ n 5 us 23 4 1 Quickstart with Example Project 23 4 2 Installation of MSSQL Server and Recovery of the Schematics 23 4 2 1 Installation of MSSQL Database 23 4 2 2 Installation of Microsoft SQL Server Management Studio Express 26 4 2 3 Recovery of Example Schematic using the SQL Server Management SWIIO EXPTESS an n kunu a imata etait eat ie ciaai tes sala Geel a he 26 4 2 4 Important Server Settings r 27 4 2 5 Create a Logon to the SQL Server 29 4 3 Installation WinAC Driver on Runtime Computer 31 4 3 1 Installation ol DEI ee eu 31 4 3 2 Installation und Parameterisation of ODBC Driver
2. 31 4 4 Installation WinAC Driver on SIMATIC Engineering Computer 36 5 FuncuonalbiDesoriBtibikuuuu uyu z k u ara Eee shiisushu 37 5 1 FI asua orn wasa atis ae a saa ee nee eect anasu mata aaa asss 37 6 Detailed Description Of FBs 39 6 1 ODK Initialisation Block 39 6 2 ODBC Communication Build up to the SQL Datenbase 42 6 3 ODBC Read and Write Block for SQL Database 46 6 4 ODBC Block for Closing the Communication 53 7 Application Examples n n ee u 57 7 1 The Use of the STEP 7 Example Project 57 7 1 1 Structure of an Application Programmes 57 7 1 2 Sending of a SELECT Statement using the variable table Control 58 1 2 Adaptation of STEP 7 Example to User s Own Requirements 58 7 2 1 Other SQL statements than in example project 58 8 Error MCSSAGECS uuu area ee 59 WinAC Link to an SQL Database V1 20 Entry ID 48354880 5 Copyright Siemens AG 2009 All rights reserved Table of Contents 10 8 1 Error Messages of WinAC ODK 4 1 59 8 1 1 Error Messages for SF
3. BEA Aufruf des Cmd7 Kopieren der Fehler meldungen R cksetzen von TRUE Schrittkette FALSE y gt ODBCInit Kopieren der Fehler meldungen R cksetzen TRUE von Schrittkette FALSE BEA BEA R cksetzen BEA von Schrittkette BEA WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 7 Application Examples 7 Application Examples 7 1 The Use of the STEP 7 Example Project The supplied STEP 7 Example Project has been laid out for the following configuration e SQL database installed e Recovery of Scales Machine Schematic in SQL database e Parameterisation of ODBC driver e Datenbank GUI to be installed It may possibly be required to insert the used communications interface WinAC RTX into the HW configuration and to then parameterise it 7 1 1 Structure of an Application Programme OB100 Complete Restart In OB100 there is only the initialisation of a flag for the First Call OB1 CYCL_EXEC In OB1 you skip into the appropriate function which has been selected in the control variable table under calling individual function blocks FC1 CALL SQL_INIT Calling SQL_INIT FC2 CALL SQL_CON Calling SQL_CON FC3 CALL SQL_EXEC Calling SQL_EXEC FC4 CALL SQL_DISCON Calling SQL_DISCON DB10 ODBC LOGIN DATA Parameterisation of OD
4. De archive MSSQL_BspPrj STEP 7 project on SIMATIC Engineering computer Adapt ODBC connection data in DB10 Transfer STEP 7 project in WinAC RTX Runtime System For graphic parameterisaton of the MsSQL database you can use the SQL Server Management Studio Express Installation of MSSQL Server and Recovery of the Schematics Installation of MSSQL Database The database system Microsoft SQL 2005 Server Express Edition is installed on the database server In order to install the database run the file SQLEXPR_GER exe The programme features as illustrated in figure 4 1 are selected in the window feature selection WinAC Link to an SQL Database V1 20 Entry ID 48354880 23 Copyright Siemens AG 2009 All rights reserved 4 Installation MSSQL Figure 4 1 Feature selection ie Setup f r Microsoft SOL Server 005 Express Edition Featureauswahl Wahlen Sie die zu inst llierenden Programmfeatures aus Klicken Sie auf ein Symbol in der Folgenden Liste um die Installationsart eines Features zu ndern a Featurebeschreibung ha fDatenbankdienste Installiert das SOL oo E Datendateien Server Datenbankmodul Tools zum X Replikation verwalten von relationalen Daten und oe ES v Gemeinsame Tools H X Clientkomponenten ML Daten sowie der Replikation epoch Eis Konnektivitatskorponenten Kr Software Development Kit Dieses Feature erfordert 117 ME auf der Festplatte Es sind 2 won 3 SubFeatures au
5. Kennwort Kennwort best tigen Kennwartrichtlinie erzwingen Anmeldename Suchen Pe eesse ee ss sC 17 Ablauf des Rennwortes erzwingen 7 Benutzer muse das Kennwort beider n chsten Anmeldung ndern Zugeordiiet zu ertilkal lt ertiikatsname m 2ugeordnet zu asurnrmetrizelierri Schlussel Schlusselname Standarddatenbank Standardsprache master Abbrechen Z WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 4 3 4 3 1 4 3 2 4 Installation MSSQL Under the tab page User Assignment you need to assign the appropriate authentications for the standard database in order to obtain access This is followed by OK to create the new logon E Anmeldungseigenschaften winacsql Fl Seite ausw hlen 5 Skript 4 Hilfe R Allgemein Serverrollen Benutzerzuordnung A Sicherungsf hige Elemente Datenbank Benutzer Standardschema Benutzer die dieser Anmeldung zugeordnet sind A Status master model i winacsagl tempdb FT Gastkonte aktiviert fur scales machine mig Mitgliedschaft in Datenbankrolle fur scales_machine_mig db_accessadmin Server db_backupoperator DESOOTOC SGLESPRESS el db datareader ke db_datawriter Verbindung C db_ddladmin WW DIZ DESB0AAD C db_denydatareader Verbindungseigenschatten db_derydatawriter anzeigen C db_owner C db_securityad
6. V1 20 Entry ID 48354880 63 Copyright Siemens AG 2009 All rights reserved 8 Error Messages 22002 Indicator variable required but not supplied 22003 Numeric value out of range 22007 Invalid datetime format 22008 Datetime field overflow 22012 Division by zero 22015 Interval field overflow 22018 Invalid character value for cast specification 22019 Invalid escape character 22025 Invalid escape sequence 22026 String data length mismatch 23000 Integrity constraint violation 24000 Invalid cursor state 25000 Invalid transaction state 25501 Transaction state 25502 Transaction is still active 25503 Transaction is rolled back 28000 Invalid authorization specification 34000 Invalid cursor name 3C000 Duplicate cursor name 3D000 Invalid catalog name 3F 000 Invalid schema name 40001 Serialization failure 40002 Integrity constraint violation 40003 Statement completion unknown 42000 Syntax error or access violation 42501 Base table or view already exists 42502 Base table or view not found 42511 Index already exists 42512 Index not found 42521 Column already exists 42522 Column not found 44000 WITH CHECK OPTION violation HYOOO General error HYOO1 Memory allocation error HYOO3 Invalid application buffer type HY004 Invalid SQL data type HY007 Associated statement is not prepared HY008 Operation canceled HY009 Invalid use of null pointer HY010 Function sequence error HY011 Attribute cannot be set now W
7. however limited to the foreseeable damage typical for the type of contract except in the event of intent or gross negligence or injury to life body or health The above provisions do not imply a change of the burden of proof to your detriment Any form of duplication or distribution of these Application Examples or excerpts hereof is prohibited without the expressed consent of Siemens Industry Sector WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved Table of Contents Table of Contents Warranty and Liability anne 4 INSIFTUCHON y uz Zuna sp aus asua aispa a qspaTasSuisua axa a aspas auipusa 7 1 B sie I forliati lix uu 2 ae ern u denenee ee Ge carseat eons 8 1 1 O 6 SXe1 SSR RENNEN ashaupaukasa Q assthuspakysq hasqa HEN SEFENEREREAUNER 8 1 2 Required Expertise 8 1 3 Reference System 8 2 OVEIVIEW see rasen 9 2 1 FUNCION Scope ee 9 2 2 Version OF the Diver 2a 9 3 Installation MYSQL ui uuu u y u u u T S sa asa 11 3 1 Quickstart with Example Project 11 3 2 Installation of MySQL Server and Recovery of Schematic 11 3 2 1 Installation of MySQL Database 11 3 2 2 Recovery of Example Schematic
8. 9013 FREEING ENVIRONMENT HANDLE 8 2 3 ODBC Function Errors Table 8 5 ODBC Function Errors WinAC Link to an SQL Database 62 V1 20 Entrys ID 48354880 8 Error Messages 8552 Still executing 8 2 4 SQL Statements Table 8 6 SQL Statements SQLSTATE Copyright Siemens AG 2009 All rights reserved 01000 01001 01002 01003 01004 01006 01007 01S00 01501 01502 01506 01507 01508 01S09 07001 07002 07005 07006 07009 07S01 08001 08002 08003 08004 08007 08S01 21501 21502 22001 General warning Cursor operation conflict Disconnect error NULL value eliminated in set function String data right truncated Privilege not revoked Privilege not granted Invalid connection string attribute Error in row Option value changed Attempt to fetch before the result set returned the first row set Fractional truncation Error saving File DSN Invalid keyword Wrong number of parameters COUNT field incorrect Prepared statement not a cursor specification Restricted data type attribute violation Invalid descriptor index Invalid use of default parameter Client unable to establish connection Connection name in use Connection does not exist Server rejected the connection Connection failure during transaction Communication link failure Insert value list does not match column list Degree of derived table does not match column list String data right truncated WinAC Link to an SQL Database
9. If there is a logical One at the input the block is processed e Timeout An integer value is set at this input which indicates the maximum asynchronous processing time of the block in seconds e ODBC Parameter Transfer A pointer to a DB is transferred at ODBCConnDATA This pointer contains the connection parameters for the ODBC interface Figure 6 4 ODBC Connection Data in a DB Mr ramen angen UdEcSystenDSN STRING ZS4 weighting machine As can be seen in Figure 5 4 the parameters are transferred in string format whereby each individual parameter is written in a string of its own which is 254 characters long The naming of the individual parameters is therefore limited to a length of 254 characters max character length in STEP 7 The actual length of the individual string variable amounts to two more bytes because the information for the maximum string variable length and the actual string variable length are stored in a character string first and second byte Please note that for the parameterisation of the pointer the entire length of the string is specified because otherwise characters are chopped off during transmission As a reference value you can use the specified address in the DB under the last string WinAC Link to an SQL Database V1 20 Entry ID 48354880 43 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs 44 Output Parameters SQL_CON e Status of EXEC _ COM In STAT
10. UDTs contain the data type as a defined number whereby 1 corresponds to INTEGER 2 to SMALLINT 4 to REAL and 5 to CHAR STRING 3 is reserved for type DOUBLE 64Bit but as this is not supported by WinAC it is converted to REAL 32Bit The second piece of information is the value of the argument which is stored in the appropriate data format The following picture illustrates the internally defined data type UDT STRING Figure 6 7 UDT4 String END STROCT When a SELECT statement is executed returned data are expected by the block lt is necessary to make a DB available which has to be of a suitable data type The data types correspond to the UDTs of the arguments and are therefore used for saving the data The UDTs in the DB must be available in the same order as the data are supplied by the block l e the block buffers the required data in a certain order which must be known If the required data do not match the data which has been made available there will be an error message Error messages are also output in the event of too few or too many provided UDTs An SQL database supports several data types The following SQL data types are supported by the application e CHAR e NUMERIC e DECIMAL e VARCHAR e INTEGER e SMALLINT WinAC Link to an SQL Database V1 20 Entry ID 48354880 47 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs e FLOAT e REAL e DOUBLE The data t
11. commands please refer to Chapter Transactional and Off Commands of MySQL in the MySQL documentation 3 3 Installation WinAC Driver on Runtime Computer 3 3 1 Installation of DLL The installation of the WinAC driver for the SQL database connection is limited to the execution of the DLL Install bat During the installation the DLL must be located in the same directory as the bat files In addition the MyODBC driver must be installed and parameterised 3 3 2 Installation and Parameterisation of the ODBC Driver The ODBC driver is used to establish the connection to the SQL database The driver is installed by means of the mysql connector odbc 3 51 14 win32 installation file During the installation you choose between the setup types Typical or Complete After the installation of the MyODBC driver has been completed it is required to parameterise the database interface The ODBC administration is located under system control administration data sources ODBC This is located on an English language operating system under Start gt Settings gt Control Panel gt Administrative Tools gt Data Sources ODBC New interfaces can be created in the category System DSN It is recommended to carry out the ODBC parameterisation after the SQL database has been created Please find below a description of how ODBC is parameterised WinAC Link to an SQL Database V1 20 Entry ID 48354880 19 Copyright Siemens AG 2009 All
12. establishes the connection to the SQL server have been separated on purpose The advantage of this separation is an improved evaluation of any errors which may occur in the step sequence It may happen that the DLL has already been loaded into the address area of WinAC but the SQL server link has been interrupted and has to be re established The DLL does not have to be re loaded into the address space after the Handle has been successfully returned to CREA_COM the only thing which needs to be re established is the connection to the SQL server To ensure that SQL_INIT is only processed once at the beginning this block should be called from OB100 responsible for the warm re start in WinAC Figure 6 1 ODK Initialisation SQL_INIT STATUS_ ODK_CREA_ WinAC Link to an SQL Database V1 20 Entry ID 48354880 39 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs Prior to examining the syntactical part of the block let us first look at the interfaces Input parameter SQL_INIT e Transfer of instance data block number The DI numbers of the three other function blocks which belong to the ODBC blocks must be allocated to the following DI inputs so that the SQL_INIT recognises which Dis must be initialised e First Call The First_Call determines whether a Handle for communication build up must be generated or whether only the data blocks must be re initialised Output parameter SQL_INIT e Status of C
13. scales machine localhost MySQL ODBC 3 51 Driver scales machine T EFP_IP MySQL ODBC 3 51 Driver An ODBC System data source stores information about how to connect to the indicated data provider A System data source Is visible to all users on this machine including MT services OF Abbrechen bernehmen Hilfe As can be seen in the view above several parameterised interfaces are already available Press Add to add a new one WinAC Link to an SQL Database 32 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 4 Installation MSSQL The different drivers can be selected in the following menu In our application we are using the SQL Server Figure 4 7 Select ODBC Driver Create New Data Source gs x Select a driver for which you want to set up a data source Microsoft Parados Driver db Microgott Parados Treiber db Microsoft Text Driver tst caw Microsoft Text Treiber tat cey Microsoft Visual FoxPro Driver Microsoft Visual FoxPro Treiber MySQL ODBC 3 51 Driver SOIL Native Client SOL Server 4 4 4 4 B B 3 2 2 ki amp ZUTICH Fertig stellen Abbrechen The required parameterisations are carried out in the following window Here you enter the Data Source Name the description and the server Figure 4 8 Parameterise ODBC Driver TCP_IP Microsoft SOL Server DSN Konfiguration E l X Dieser Assistent ist Ihnen beim Erstellen einer ODB
14. z Buns no ul l TOS pee 198P ORE 3S1TV3 Anu CPWD SEP JNINY ser l L S TVd 1 lulOd x INUuL Ld3lS ISIV4 INUuL 23X3 10S UOA JnujnV p m s s uBl Iy 6002 OV suswels YyBUAdOD V1 20 Entrys ID 48354880 WinAC Link to an SQL Database 52 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs 6 4 ODBC Block for Closing the Communication The last block in the step sequence is SQL_DISCON It is responsible for the secure closing of the database When a connection to an SQL database is no longer required it must be closed again with this block If the connection is not closed again the established connections to the SQL server are not terminated This may lead to errors such as Too Many Client Conenctions Active It can also result in a buffer overflow because several allocated storage areas are required for the connections which are not properly closed without processing SQL_DISCON should be called in any case Figure 6 14 ODBC CLOSE Block SQL_DISCON STATUS ODK_ Prior to examining the syntactical part of the block let us first look at the input and output parameters again WinAC Link to an SQL Database V1 20 Entry ID 48354880 53 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs Attention The EN Input and the ENO Output are not parameterised Input SQL_DISCON 54 REQ Input REQ is the On Off switch on the FB If there is a
15. 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 3 Installation MySQL 3 2 4 Define a User To establish a connection to the database via TCP IP it is required to define a user with assigned authorizations Prior to being able to define a user first you need to have installed the MySQL GUI Tools on the development environment see Chapter 3 2 3 First start the MySQL Administrator The key define new user under user administration enables you to define a new user Figure 3 9 Define user MySQL Administrator Connection root localhe Datei Bearbeiten View Extras Fenster MySQL Enterprise Hilfe E Serverinformation 5 Benutzerinformation Schema Berechtigungen Ressourcen Ge Dienstverwaltung gt Neuer Benutzer By Startyariablen Login und weitere Informationen ber den Benutzer 8a Benutzerverwaltung z A r Login Information kapka us Si MySQL Benutzer WinAC Der Benutzer muss seinen MySOL Benutzernamen ar Serverstatus eingeben um sich mit dem Server zu verbinden ar Serverprotokolle Passwort Tragen Sie in dieses Feld das Benutzerpasswort ein 3 Replikationsstatus us _ I 7 i Te Passwort best tigen Geben Sie zur Best tigung das Passwort nochmals ein QE Backup i s C Wiederherstellung Zusatzinformationen J Kataloge U 9 Voller Name winac RTX Der vollst ndige Name des Benutzers Benutzerkonten Beschreibung Soft PLC Zus tzliche Beschreibung des Be
16. 32 Installation of MySQL Server and Recovery of Schematic Installation of MySQL Database The database system mysaql 5 0 37 win32 is installed on the database server In order to install the database unpack the packed file mysqI 5 0 37 win32 zip and run Setup exe During the installation select one of the setup types Typical or Complete After the installation it is decided whether a MySQL account should be generated Select Skip Sign Up WinAC Link to an SQL Database V1 20 Entry ID 48354880 11 Copyright Siemens AG 2009 All rights reserved 3 Installation MySQL Figure 3 1 Skip MySQL Account MySQL com Sign Up Setup Wizard MySoL com Sign Up Login or create a new MySQL com account Please log in or select the option to create a new account Create a new free MySQL com account IF you do not yet have a MySQL corm account select this option and complete the Following three steps Login to MySQL com Select this option if you already have a MySQL com account Please specify your login information below Email address Password ie Skip Sign Up ca This is followed by the configuration of the MySQL server For the application example select the standard configuration Figure 3 2 MySQL Configuration MySQL Server Instance Configuration Wi MySQL Server Instance Configuration Configure the MySQL Server 5 server instance Please select a configuration type Det
17. B65001 STATUS_ODK_CREA_CON 59 8 1 2 Error Messages for SFB65002 STATUS ODK_CON EXEGC 60 8 2 Special Error Messages of SQL DB Driver 60 8 2 1 ODK FUNGCHON Reiifnsanmne see er 60 8 2 2 Funetion Gode Numbers anna aha in 62 8 2 3 ODBC FUnCHOM EMO S uo 2 y amas avid incenaderaeiet 62 8 2 4 SOL S LATCH IS Sites u er 63 List of Abbreviations 67 HiStQiyuu m aan uum Sa ua E San uyan 68 WInAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 1 Basic information Instruction Content This document describes the WinAC driver for an SQL database link to WinAC RTX via an ODBC driver WinAC Link to an SQL Database V1 20 Entry ID 48354880 T Copyright Siemens AG 2009 All rights reserved 1 Basic information 1 1 Note 1 2 1 3 Basic information Objective Develop of a WinAC RTX driver for a direct connection to a SQL database Four SQL statements SELECT UPDATE INSERT DELETE can be transmitted in any reasonable order This document describes how by means of this driver SQL statements can be transmitted to the SQL server and how data can be collected Basically the driver can be used for databases with an ODBC driver Required Expertise In order to understand this document you need to be conversant with the followi
18. BC Data Source Name DSN User Name Password Note Input of the LOGIN DATA is case sensitive WinAC Link to an SQL Database V1 20 Entry ID 48354880 57 Copyright Siemens AG 2009 All rights reserved 7 Application Examples Control 7 2 7 2 1 58 This variable table is used to start the individual calls For a regular sequence the blocks are processed in the following order e CALL FLAG SQL_INIT e CALL FLAG SQL_CON e CALL FLAG SQL _ EXEC e Selection of SQL statement to be transferred CALL SELECT CALL UPDATE CALL INSERT CALL DELETE e CALL FLAG SQL_DISCON After SQL_CON SQL_EXEC may be called as often as required in order to send off various SQL statements To process an SQL_EXEC call you must first select an SQL statement Sending of a SELECT Statement using the variable table Control e Open variable table Control in SIMATIC MANAGER e When SQL_INIT First Call is TRUE DLL and DBIs are initialised by SQL FBs when it is FALSE only DBIs are initialised e Set CALL FLAG SQL_INIT e Set CALL FLAG SQL_CON e Select CALL SELECT e Set CALL FLAG SQL EXEC SELECT statement is sent off data is received e Set CALL FLAG SQL _DISCON Adaptation of STEP 7 Example to User s Own Requirements Other SQL statements than in example project If other SQL statements are required which are not in the example project the user needs to make the following changes e The SQL strings must be adapted
19. C Datenguelle behilflich anhand derer Sie sich mit einem SQL Server verbinden konnen Welchen Hamen mochten Sie verwenden um auf die Datenquelle zu VENEEN Hame EISELE machine TCP IP Wie mochten Sie die Datenquelle beschreiben Beschreibung Migration MySQL f r MS SQL Mit welchem SQL Server mochten Sie sich verbinden Server 1 32 168 130 133WMWW3REDOTSSOLESPRE w Weiter gt Abbrechen Hilfe In the next picture you select the user name codeword logon as the authentication for the SQL Server This is followed by input of the user name and the codeword to allow logon Use the logon name created in Para 3 2 5 as the user name WinAC Link to an SQL Database V1 20 Entry ID 48354880 33 Copyright Siemens AG 2009 All rights reserved 4 Installation MSSQL As a fixed port was entered previously in the TCP IP protocol settings in the SQL Server Configuration Manager you also need to specify Port 2301 in the Client Configurations Figure 4 9 Server logon Microsoft SQL Server DSN Konfiguration X Wie soll SQL Server die Authentizit t des Benutzernamens best tigen Mit Windows MT Authentifizienng anhand des Benutzernamen im Netzwerk Mit SQL Server Authentifizierung anhand des vom Benutzer eingegebenen Benutzernamens und K eriruwuorts Klicken Sie auf Clentkonfiguration um die Netzwerkbibliothek fur die kommunikation mit dem SQL Server zu andern Clentkonfiguration zum SOL Se
20. C_COM the DONE output is set to TRUE This also happens when it is interrupted by an error Due to the fact that the last cycle was completed in a defined way DONE is set to TRUE nevertheless with the additional information that ERROR equals TRUE BUSY As several block calls are necessary the block must indicate whether it is still in the processing phase i e the BUSY Bit is set to TRUE during the entire processing of the block and is only reset to FALSE until the block has been definitely completed It is also required to ensure that only one SQL statement at a time is being processed It is essential to wait for the BUSY Bit until the next SQL Statement can be sent off Error Buffer for ODBC Error Messages WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs The output OdkSqlErrorMsg corresponds to a structure which may contain several error codes from the DLL In order to forward this data to a DB a User Defined data type UDT is required This UDT is structured as illustrated in Figure 5 12 Figure 6 12 UDT Structure of Error Struct Adresse Anfangswert Name Trp ol opecsqibetinenrrorses jsrammers o Imscssipet menezosser jeramers ol osesinssinenrzorses jsrammers ol sessunesinenrrorses jsrammers o Ippscssipetsnenezorsein jerames END STRUCT Now if this UDT was created in a DB SQL_EXEC is pa
21. D Ts It is important to note that the correct length is transferred to the pointer As a reference value you can use the address which is listed last in the address column of the DB The following picture illustrates a Receive which is provided for four values The UDTs have been arranged in the following order INTEGER CHAR INTEGER CHAR The data is expected in exactly that order The maximum amount of values which may be returned in one query is limited due to the maximum data storage in one DB 65 Kbytes and by a maximum amount of 1000 values Figure 6 11 DB for SQL Receive Adresse ee Typ D STEUCT O ma field Data Type Integer nn fieldzl Data Type Integer S0L_field4l field4l Data Type Char END STRUCT Bp m sit fieldd Data Type Char Output Parameter SQL_EXEC Status of EXEC COM Error messages from EXEC_COM are returned in STATUS ODK_CON In the event of successful calling and processing a Zero is displayed by the output in the event of an error an appropriate error code is returned The error code is listed in the WinAC ODK User Manual and can be looked up there The data format of the output variable must comply with WORD ERROR The ERROR Bit indicates whether the EXEC_COM was processed successfully or whether it was interrupted with error If a One is output there has been an error during processing DONE As soon as all cycles have been completed by EXE
22. Data source name too long IMO11 Driver name too long IMO12 DRIVER keyword syntax error IMO13 Trace file error IMO14 Invalid name of File DSN IMO15 Corrupt file data source WinAC Link to an SQL Database 66 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 9 List of Abbreviations DB Data block FB Function block OB Organisation block RTX Real Time eXtension for Windows UDT User defined type data type definition in STEP 7 WinAC Link to an SQL Database V1 20 Entry ID 48354880 9 List of Abbreviations 67 Copyright Siemens AG 2009 All rights reserved 10 History 10 68 History Table 10 1 V1 20 11 02 09 Tested with WinAC RTX 2009 WinAC Link to an SQL Database V1 20 Entrys ID 48354880
23. EXEC is responsible for all functions which are required for sending SQL manipulation commands and for receiving possibly requested data After successful connection to the SQL database SQL_EXEC is executed in order to process the database This block may be called as often as required to carry out SELECT UPDATE INSERT or DELETE statements A statement is processed asynchronously to avoid exceeding the cycle time of WinAC Several cycles are processed to execute and complete SQL_EXEC In order to define a maximum waiting time for the processing of the asynchronous Threads a Timeout at the block is defined After the specified time has elapsed the block is interrupted and an error message is sent SQL statements consist of two parts the SQL string and the SQL argument The actual commands are packed in the SQL String such as SELECT lt lt oOl mmnr t bel G Llu umnt it lez FROM tabletitle As it should be possible to change or read certain areas from the database the SQL string must be variable to enable the user to select from different criteria For instance you may only look for data which were saved within a certain period of time The first query would search between the months of May and July and the second between October and December This is possible by means of the arguments which are input separately from the SQL string and are combined with the SQL string in SQL_EXEC As an additional liberty it is also possible to use diff
24. Edition Dienstkonto Dienstkonten definieren die 4nmeldekonten tL v fa F r jedes Dienstkonto anpassen Dienst f Integriertes Systemkonto verwenden Netzwerkdienst Gom nenbenutzerkonto verwenden Benutzername Renneore Dom ne Dienste am Ende der Installation starten WM SOL Server 7 SOL Browser Zur ck weiter gt Abbrechen Select mixed as the authentication mode to ensure that both Windows and Server authentications are possible You now need to assign a codeword for the standard user name sa Figure 4 4 Authentication mode i Setup fir Microsoft SOL Server 2005 Express Edition Authentifizierungsmodus Der Authentifizierungsmodus gibt die Sicherheit an die beim Herstellen der Yerbindung mit SQL Server verwendet wird d Wahlen Sie den Authentifizierungsmodus f r diese Installation aus windows Authentifizierungsmodus ie Gemischter Modus Windows Authentifizierung und SOL Server Guthentifizierung Geben Sie unten das Anmeldekennwort f r sa an Kennwort eingeben Perser Kennwort best tigen lt Zur ck weiter gt Abbrechen WinAC Link to an SQL Database V1 20 Entry ID 48354880 25 Copyright Siemens AG 2009 All rights reserved 4 Installation MSSQL 4 2 2 4 2 3 26 The following installation steps may be adopted unchanged After the installation you can open a console via a DOS prompt using the SQLCMD tool This consol
25. P IP fal Connector ODBC 3 51 14 Connector ODBC Login Connect Options Advanced Connector ODBC Configuration Data Source Name scales_machine_T LF_IF This dialog is used to edit a Data Description Source Name CSM Gemer 182 168 1 100 User Password Database scales machine ki Diagnostics gt gt Ok Cancel Help Installation WinAC Driver on SIMATIC Engineering Computer This documentation as well as the STEP 7 example project is required on the SIMATIC Engineering computer The required FBs for the user s STEP 7 programme may be taken from this demo project The ODBC connection data in the STEP 7 project must be adapted in DB10 DSN User Password These parameters must be specified because several ODBC connections may be parameterised When assigning a name please be aware that input is case sensitive WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 4 2 4 2 1 4 Installation MSSQL Installation MSSQL Quickstart with Example Project e Datenbase server Install database system MsSQL on database server Install Microsoft SQL Server Management Studio Express Recover Scales Machine Schematics in MSSQL database Create an SQL server logon e Runtime System Install DLL with the DLL_Install bat file on Runtime System Parameterise SQL Server ODBC driver on Scales Machine database e SIMATIC Engineering Computer
26. REA_COM The output STATUS_ODK_CREA_COM supplies the handle number or if the build up was unsuccessful it returns an appropriate error code The error code is listed in the WinAC ODK User Manual and can be looked up there The data format of the output variable must comply with WORD e ERROR The ERROR Bit specifies whether the SQL_INIT was processed successfully or whether it was interrupted with error If TRUE is output there has been an error WinAC Link to an SQL Database 40 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs The following block diagram illustrates the syntactical sequence Figure 6 2 Flowchart of SQL_INIT Aufruf von SQL_INIT TRUE FALSE Init Handles Alle Daten der von allen Dis Les Dis initialisieren l l Init Status von CREA COM BEA Handle erzeugen DLL initialisieren Status Handle erzeugt CREA COM Handle Error y y Handles auf Dis verteilen BEN Alle Daten der Dis initialisieren BEA WinAC Link to an SQL Database V1 20 Entry ID 48354880 41 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs 6 2 Note 42 ODBC Communication Build up to the SQL Datenbase The ODBC Con_SQL_DB function block is responsible for the build up of the ODBC interface It is called to esta
27. US ODK_CON error messages are returned by EXEC_COM In the event of successful calling and processing a Zero is displayed by the output in the event of an error an appropriate error code is returned The error code is listed in the WinAC ODK User Manual and can be looked up there The data format of the output variable must comply with WORD e ERROR The ERROR Bit indicates whether the SQL_CON was processed successfully or whether it was interrupted with error If a One is output there has been an error during processing e DONE As soon as all cycles in SQL_CON have been completed the DONE output is set to TRUE This also happens when it is interrupted by an error Due to the fact that the last cycle was completed in a defined way DONE is set to TRUE nevertheless with the additional information that ERROR equals TRUE e BUSY As several block calls are necessary the block must indicate whether it is still in the processing phase i e the BUSY Bit is set to TRUE during the entire processing of the block and is only reset to FALSE until the block has been definitely completed e Error buffers for ODBC error messages The output OdkSqlErrorMsg corresponds to a structure which may contain several error codes from the DLL In order to forward this data to a DB a User Defined data type UDT is required This UDT is structured as illustrated in Figure 5 5 Figure 6 5 UDT Structure of Error Struct Name Trp fen E aruru rt _ jm O z
28. WinAC Link to an SQL Database to WinAC RTX User documentation V1 20 e November 2009 Applikationen amp Tools Answers for industry SI E M E N S Copyright Siemens AG 2009 All rights reserved Industry Automation and Drives Technologies Service amp Support Portal This article is taken from the Service Portal of Siemens AG Industry Automation and Drives Technologies The following link takes you directly to the download page of this document http support automation siemens com WW view en 48354880 If you have any questions concerning this document please e mail us to the following address online support automation siemens com WinAC Link to an SQL Database 2 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved SIEMENS SIMATIC WinAC Link to an SQL Database WinAC Link to an SQL Database V1 20 Entry ID 48354880 Basic Information Overview Installation MySQL Installation MSSQL Functional Description Detailed Description of FBs Application Examples Error Messages List of Abbreviations History O O N O Q A WO N Copyright Siemens AG 2009 All rights reserved Warranty and Liability Warranty and Liability Note The Application Examples are not binding and do not claim to be complete regarding the circuits shown equipping and any eventuality The Application Examples do not represent customer specific solutions They are only intended to
29. Y019 Non character and non binary data sent in pieces HYO2O Attempt to concatenate a null value HYO21 Inconsistent descriptor information HYO24 Invalid attribute value HY090 Invalid string or buffer length HY091 Invalid descriptor field identifier HY092 Invalid attribute option identifier HY095 Function type out of range HY096 Invalid information type HY097 Column type out of range HY098 Scope type out of range HY099 Nullable type out of range HY100 Uniqueness option type out of range HY101 Accuracy option type out of range HY103 Invalid retrieval code HY104 Invalid precision or scale value HY105 Invalid parameter type HY106 Fetch type out of range HY107 Row value out of range HY109 Invalid cursor position HY110 Invalid driver completion HY111 Invalid bookmark value HYCOO Optional feature not implemented HYTOO Timeout expired HYTO1 Connection timeout expired IMOO1 Driver does not support this function IMOO2 Data source name not found and no default driver specified IMOO3 Specified driver could not be loaded IM004 Drivers SQLAIlocHandle on SQL_HANDLE_ENV failed IM005 Drivers SQLAllocHandle on SQL_HANDLE_DBC failed IMOO6 Drivers SQLSetConnectAttr failed IMOO7 No data source or driver specified dialog prohibited IMOO3 Dialog failed IMOO9 Unable to load translation DLL WinAC Link to an SQL Database V1 20 Entry ID 48354880 O 5 Copyright Siemens AG 2009 All rights reserved 8 Error Messages IMO10
30. ailed Configuration gt 2 Choose this configuration type to create the optimal server setup For this machine Standard Configuration s gt Use this only on machines that do not already have a MySQL server installation This will use a general purpose configuration For the server that can be tuned manually cook e gt e This is followed by the server instance configuration The appropriate settings for the example application are shown in the figure below WinAC Link to an SQL Database 12 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 3 Installation MySQL Figure 3 3 Server Instance Configurations MySOL Server Instance Confi gura fon V MySQL Server Instance Configuration Configure the MySQL Server 5 0 server instance Please set the Windows options z Install As Windows Service Ss This is the recommended way to run the MySQL server on Windows Service Mame soL J Launch the MySQL Server automatically Include Bin Directory in Windows PATH Check this option to include the directory containing the server client executables in the Windows PATH variable so they can be called From the command line cook Trees ret Refer to the next picture for the assignment of a root password for the server MySOL Server Instance Configura ion Wi MySQL Server Instance Configuration Configure the MySQL Server 5 0 server instance Please set the security opt
31. allname status O OoOo SQL Server 2005 Dienste Shared Memory Aktiviert E g SQL Server 2005 Netzwerkkonfiguration le Protokolle f r SQLEXPRESS 8 SQL Native Client Konfiguration Deaktiviert Aktiviert Deaktiviert Create a Logon to the SQL Server To establish a connection to the SQL server you can either go via the Windows authentication or via a SQL server authentication For a remote connection via TCP IP you need the SQL server authentication For this purpose you must create a user logon In order to do this open the SQL Server Management Studio Express and log on with the Windows authentication In the left hand part of the window open the Saved folder and create a new logon by right clicking on Logon gt New Logon In the opened window select the SQL server authentication This is followed by the assignment of a logon name and code word In our example project the recovered database is chosen as the standard database WinAC Link to an SQL Database V1 20 Entry ID 48354880 29 Copyright Siemens AG 2009 All rights reserved 4 Installation MSSQL 30 E Anmeldung Neu Filz 5 a B Allgemein A Serverallen A Benutzerzuordrurng 4 Sicherungsf hige Elemente 4 Status Yerbindung Server DESOOTOCSSQLEAPRESS Verbindung Wi OO SDE 8B 0440 a Verbindungseigenschatten anzeigen Bereit L Skript 4 Hilfe Anmeldename windows Authentifizierung ts SUL Server Authentifizierung
32. blish a connection to a MySQL DBMS Prior to the build up to DBMS it is called only once in the step sequence After the connection has been successfully established the block is not processed again until the connection to DBMS has either been interrupted or broken In order to establish a connection to a DBMS system DSN user name and password must be transferred This happens via a DB which has saved this data in string format The block is processed asynchronously to the running PLC cycle i e a thread is triggered which must have established the connection following a specified time Timeout Due to the fact that there may be considerable time fluctuations with a connection which is established via a TCP IP connection but the PLC cycle must not be interrupted for longer periods of time it is recommended to process the DQL database connection call synchronously to the PLC cycle An SQL database connection is supported If a new connection is established there will be an error message Figure 6 3 ODBC Connection Block SQL_CON STATUS_ Timeout ODBCConn DATA WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs Prior to examining the syntactical part of the block let us first look at the interfaces Attention The EN input and the ENO output are not parameterised Input Parameters SQL_CON e REQ Input REQ is the On Off switch on the FB
33. e serves to administer the database If graphic administrations such as create and manage are required use SQL Server Management Studio Express Installation of Microsoft SQL Server Management Studio Express For the installation of Microsoft SQL Server Management Studio Express you will need the file SQLServer2005_SSMSEE msi In order to process the installation run the file and carry out the following installation steps Recovery of Example Schematic using the SQL Server Management Studio Express Start the SQL Server Management Studio Express to recover the saved database Localhost is logged on Windows authentication mode a codeword is not required Initially you need to define a new database named scales_machine_mig This is followed by the recovery of the archived database by a right mouse click on the folder of the newly defined database under Tasks gt Recover gt Database The picture below illustrates the settings Figure 4 5 Database Recovery r Datenbank wiederherstellen scales_machine_mig Sisa SB Skipt A Hilfe R Allgemein A Opti Laaa PERNE Ziel fur die Wiederherstellung Wahlen Sie den Namen einer neuen oder vorhandenen Datenbank fur den Wiederherstellungsvworgang aus oder geben Sie ihn ein In Datenbank scales_machine_mig j Bis zu Zeitpunkt ktuellster m glicher Zeitpunkt Quelle f r die Wiederherstellung Geben Sie die Quelle und den Speicherort der wiederherzustellenden S cherungss tze a
34. erent data formats for the arguments The following are possible SMALLINT INTEGER REAL and CHAR data types Example a statement may look like this SELECT year Gest FROM 5s WHERE BIT leid lt za Ss and d are placeholders for the coming arguments which may be UDT STRING 254 gt titles UDT SMALLINT gt D The complete statement for the database may be interpreted as follows SELECT year Lest FROM titles WHERE titleid lt 5 The arguments are inserted in the respective placeholders whereby the length of the individual arguments is limited to the appropriate data type A special case is STRING where up to 254 characters per argument may be used Please note that the arguments must be in the same order as the placeholders You will get an error message when the data type does not correspond to the placeholder The following table illustrates corresponding placeholders and data types WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs Table 6 1 corresponding data types and placeholders Defined Data types f Placeholders Data type number INTEGER 1 SMALLINT 2 REAL 4 CHAR STRING The amount of placeholders must also match the arguments In order to achieve conformity is necessary to use four pre defined UDTs which correspond to the above mentioned data types As the first piece of information the
35. etwork configuration As a connection should be established via TCP IP the appropriate utility must be parameterised This is done by right clicking gt property Er SQL Server Configuration Manager Datei Aktion Ansicht gt 2 SQL Server Konfigurations Manager Lokal Protokallname status O oOo SQL Server 2005 Dienste Y Shared Memory Aktiviert E 4 SQL Server 2005 Netzwerkkonfiguration f Named Pipes Deaktiviert le Protokolle f r SQLEXPRESS 8 SQL Native Client Konfiguration Ya Aktivieren Deaktivieren Eigenschaften Hilfe ffnet die Eigenschaften des aktuellen Objekts Under tab page IP Addresses all fields for Dynamic TCP ports are deleted and port 2301 is entered into fields TCP Ports Eigenschaften von TCP IP Protokoll IP Adressen Ja Aktiviert Nein Dynamische TCP Paorks IP Adresse 192 169 204 1 TCP Pork IPS Aktiv Ja Aktiviert Nein Dynamische TIP Ports IP Adresse 127 0 0 1 TCP Pore IPAll Dynamische TIP Ports TCP Pork Aktiv ibt an ob die ausgew hlte IP Adresse aktiv ist abbrechen libernetiien HilF WinAC Link to an SQL Database 28 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 4 2 5 4 Installation MSSQL After confirming the settings activate the protocol and re start the SQL server Tr SQL Server Configuration Manager Datei Aktion Ansicht B SQL Server Konfigurations Manager Lokal Protok
36. ext picture for an illustration of a connection via TCP IP Please note that certain administrations are only possible via localhost Figure 3 5 MySQL Administrator TCP IP connection MySQL Administrator 1 2 11 Administrator Mit MySQL Servernnstanz verbinde Gesp Verbindungen EI Server Host fi 32 168 10 100 Fort 3206 Hutzermame winar Passwort a Details gt gt Loschen Abbrechen WinAC Link to an SQL Database 14 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 3 Installation MySQL The next step is a window for database administration Use the appropriate tab page for recovery Then use Open File to select the file Scales _MachineV1 0 sql from the example project Recovery is initiated by clicking Start Figure 3 6 MySQL Recovery MySQL Administrator Connection root localho N re Datei Bearbeiten View Extras Fenster MySOL Enterprise Hilfe 3 Serverinformation a Allqernein Inhalt wiederherstellen Dienstverwaltung ns i Wiederherstellungseinstellungen H Startvariablen 9 Bitte w hlen Sie wie das Backup wiederhergestellt werden soll 33 Benutzerverwaltung m Allgemein rJ Aktive Verbindungen wiederherz Datei Name der wiederher zustellenden a Serverstatus Backup Datei J Serverprotokolle Backup T yp SOL Dateien Typ der Backup Datei Replikationsstatus k Zielschema a Backu a p f Ori
37. ginal schema tm G Wiederherstellung Another schema Kataloge Einstellungen Fehler ignorieren Datenbanken anlegen wenn sie noch nicht existieren Zeichensatz Ba 1 Beim Import einer SOL Datei die nicht mit MySQL Administrator Datei Zeichensatz ut gt erstellt wurde m ssen Sie den richtigen Zeichensatz der Datei angeben Wenn Sie das Backup mit MySQL Administrator erstellt haben wurde die Datei in UTF 8 geschrieben A 3 2 3 Installation of GUI Tools To ensure you can use the example project it is required to install additional administration tools of the MySQL database For this purpose you need to execute the file mysql gui toolsMySQL 5 0 r1 1a win32 msi Once installation is complete the following four programmes for the database administration are available e MySQL Administrator e MySQL Migration Toolkit e MySQL Query Browser e MySQL System Tray Monitor The MySQL Administrator serves to carry out the administration of the database Use MySQL Migration Toolkit to migrate various schematics and data into MySQL databases Amongst other things the browser is for the graphic illustration of data in a table With the System Tray Monitor you can display various runtime events and switch the server instance on and off In order to install the GUI Tools execute the file mysql gui toolsMySQL 5 0 r11a win32 msi After selecting the installation path you can choose either a co
38. glDatabaselon DLL Copyright Copyright C 2004 Versi ansinformationen Elementname Beschreibung Besondere Beschreibi Firma Interner Name Kommentare Mark OD rken K Version Order Information Originaldateiname Fro duct Description _ OF Abbrechen bernehmen WinAC Link to an SQL Database V1 20 Entry ID 48354880 Copyright Siemens AG 2009 All rights reserved 2 Overview Determine Driver Version in STEP 7 Project Determine the version of the STEP 7 blocks in the instance DB of SQL_CON DBL SQL CON C_IF STEP 7_VERSION Version of STEP 7driver SW WinAC Link to an SQL Database 10 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 3 2 3 2 1 3 Installation MySQL Installation MySQL Quickstart with Example Project e Database Server Install database system MySQL on database server Install mysql gui tools Recover Scales Machine Schematic in MySQL database Create an SQL server logon e Runtime System Install DLL with the DLL_Install bat file on Runtime System Install MyODBC driver on Runtime System Parameterise ODBC driver installed on Runtime System on Scales Machine database e SIMATIC Engineering Computer De archive MySQL_BspPrj STEP 7 project on SIMATIC Engineering computer Adapt ODBC connection data in DB10 Transfer STEP 7 project in WinAC RTX Runtime System Optionally it is possible to install MySQL 5 0 r1 Ta win
39. inAC Link to an SQL Database 64 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 8 Error Messages HY012 Invalid transaction operation code HY013 Memory management error HY013 Memory management error HYO ______Limitonthenumberofhandies exceeded U I HY015 No cursor name available eaa HY016 Cannot modify an implementation row descriptor III HY017 Invalid use of an automatically allocated descriptor handle HYO Server declined cancel request Y O Yo Non characterandnon binary data sent in pieces x UI HYO Column type out of range S 5 HYO Scope type out of range O HY099 __Nullable type out of range _ HY100 Uniqueness option type out of range S HY101 Accuracy option type out of range S _ HY103 Invalid retrieval code S 5 m precision or scale value S _ HY105 Invalid parameter type HY106 Fetch type out of range 5 1M002 Data source name not found and no default driver specified M003 Specified driver could not be loaded S MOO Diver s SQLAIlocHandle on SQL_HANDLE_ENV failed 1M005 Driver s S LAllocHandle on SQL_HANDLE_DBC failed MOO Driver s SQLSetConnectAttr files S 1M007 No data source or driver specified dialog prohibited MOO An m aao HY014 Limit on the number of handles exceeded HY015 No cursor name available HY016 Cannot modify an implementation row descriptor HY017 Invalid use of an automatically allocated descriptor handle HY018 Server declined cancel request H
40. ions Y Modify Security Settings 2 New rook password Rua Enter the root password root Confirm aa Retype the password Enable root access from remote machines Create An Anonymous Account This option will create an anonymous account on this server Please note that this can lead to an insecure system cook see cont The next step is the execution of the completed configurations by the database After the installation a MySQL Command Line Client is made available in order to transfer the database administrations and SQL statements Use the MySQL GUI Tools if graphic administrations such as create and manage databases are required see installation on SIMATIC Engineering computer WinAC Link to an SQL Database V1 20 Entry ID 48354880 Copyright Siemens AG 2009 All rights reserved 3 Installation MySQL 3 2 2 Recovery of Example Schematic Execute the MySQL Administrator in order to re insert the saved project into the database In order to create a connection to the MySQL Server first the login data are required Please refer to the following picture for an illustration of a localhost connection Figure 3 4 MySQL Administrator localhost connection MySQL Administrator 1 2 11 I k MySQL Administrator Mit MvSOL Servernnstanz verbinden Gesp Verbindungen fi eal Server Host localhost Port 3206 Hutzername root Passwort a Details gt gt Loscheri Abbrechen Refer to the n
41. l error codes from the DLL In order to forward this data to a DB a User Defined data type UDT is required This UDT is structured as illustrated in Figure 5 12 WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs Figure 6 15 UDT Structure of Error Struct Name Trp Anfangswert 52 jem _ ol opecsqibetinerrorses jsrammers o Imscssipet menezosser jeramers ol osessunesinensrorses jsrammers ol osessuesinenrrorses jsramers o Impscssipetsnenezorsein jeramers END STRUCT Now if this UDT was created in a DB SQL_DISCON is parameterised with the appropriate UTD This is followed by the transmission of the error codes to the DB For the individual error numbers and error statements please refer to tables 7 3 to 7 6 in the appendix where you will find the necessary explanations The following block diagram illustrates the syntactical sequence WinAC Link to an SQL Database V1 20 Entry ID 48354880 55 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs Figure 6 16 Flow Chart of SQL_DISCON 56 FALSE BEA TRUE FALSE STEP1 TRUE Aufruf von SQL_DISCON REQ FALSE Ruck TRUE setzen der Statusbits Pointer Zuweisung Laden der Timeout Zeit Aufruf des Cmd8 gt ODBCClose Event_Trace
42. logical One at the input the block is processed Timeout An integer value is set at this input which indicates the maximum asynchronous processing time of the block in seconds Output Parameter SQL_DISCON Status des EXEC_COM In STATUS _ ODK_CON error messages are returned by EXEC COM In the event of successful calling and processing a Zero is displayed by the output in the event of an error an appropriate error code is returned The error code is listed in the WinAC ODK User Manual and can be looked up there The data format of the output variable must comply with WORD ERROR The ERROR Bit indicates whether the SQL_DISCON was processed successfully or whether it was interrupted with error If a One is output there has been an error during processing DONE As soon as all cycles in SQL_DISCON have been completed the DONE output is set to TRUE This also happens when it is interrupted by an error Due to the fact that the last cycle was completed in a defined way DONE is set to TRUE nevertheless with the additional information that ERROR equals TRUE BUSY As several block calls are necessary the block must indicate whether it is still in the processing phase i e the BUSY Bit is set to TRUE during the entire processing of the block and is only reset to FALSE until the block has been definitely completed Error Buffer for ODBC Error Messages The output OdkSqlErrorMsg corresponds to a structure which may contain severa
43. min Iw public Bereit Abbrechen 4A ae Installation WinAC Driver on Runtime Computer Installation of DLL The installation of the WinAC driver for the SQL database connection is limited to the execution of DLL Install bat During installation the DLL must be in the same directory as the bat file In addition the SQL Server ODBC driver must be parameterised Installation und Parameterisation of ODBC Driver The ODBC driver is used to establish the connection to the SQL database The ODBC Administration is located under system control administration data sources ODBC This is located under an English language operating system under Start gt Settings gt Control Panel gt Administrative Tools gt Data Sources ODBC New interfaces can be created in the category System DSN It is recommended to carry out the ODBC parameterisation after the SQL database has been created Please find below the description for ODBC parameter assignment WinAC Link to an SQL Database V1 20 Entry ID 48354880 31 Copyright Siemens AG 2009 All rights reserved 4 Installation MSSQL Figure 4 6 Create ODBC interface ODBC Data Source Administrator User DSN System DSN File DS H Drivers Tracina Connection Pooling About System Data Sources Hame Driver Adr Microsoft Text Treiber txt ce ExtBooks MySQL ODBC 3 51 Driver Remove MySQL MySG L ODBC 3 51 Driver MySGLbooks MySG L ODBC 3 51 Driver Configure
44. mplete installation or a user defined one WinAC Link to an SQL Database V1 20 Entry ID 48354880 15 Copyright Siemens AG 2009 All rights reserved 3 Installation MySQL Figure 3 7 Choose setup type EZ MySQL Tools for 5 0 Setup Wizard Setup Type Choose the setup type that best suits your needs Please select a setup type Complete 4ll program Features will be installed Requires the most disk space Choose which program Features you want installed and where they Will be installed Recommended for advanced users lt Back Cancel When the user defined installation has been chosen certain tools can be deselected It is however recommended to install all tools Figure 3 8 GUI Components 2 MySQL Tools for 5 0 Setup Wizare Custom Setup Select the program Features you want installed Click on an icon in the list below to change how a Feature is installed Feature Description Common components For all Sul tools Common Components My SOL Administrator MySQL Query Browser MySQL Migration Toolkit Language Support This Feature requires 18M6 on vour hard drive Install to Ci Program Files MySOL MySQL Tools for 5 0 Change Help Space lt Back Cancel Once installation has been completed it is recommended to put the MySQL System Tray Monitor in Autostart to enable you to start and stop the database via the icon in the task bar WinAC Link to an SQL Database 16 V1
45. n Aus Datenbank Yon Medium D scales_machine_MSSOL fe Wahlen Ste die wiederherzustellenden S cherungss tze aus Wiederherstellen Name Komponente Typ Server DESOOTOCSSGQLEAPRESS Verbindung Wi O02 desb0aall ay Verbindungseigenschatten anzeigen Status Bereit Abbrechen E WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 4 Installation MSSQL 4 2 4 Important Server Settings After installing MSSQL Server Express you need to carry out settings in the SQL Server Configuration Manager The Configuration manager is started under Start gt Programmes gt Microsoft SQL Server 2005 gt Configuration Tools You can view in SQL Server 2005 utilities which utilities have been started In the case of our example programme the only utility required is SQL Server utility Tr SQL Server Configuration Manager Datei Aktion Ansicht a E E2 2 G Gp m SQL Server Konfigurations Manager Lokal i SQL Server 2005 Dienste ASSQL Server SQLEXPRESS Wird ausgef hrt Automatisch NT AUTHOR i m SQL Server 2005 Netzwerkkonfiguration sal Server Browser Beendet Andere Boot Syste NT AUTHOR SQL Native Client Konfiguration WinAC Link to an SQL Database V1 20 Entry ID 48354880 27 Copyright Siemens AG 2009 All rights reserved 4 Installation MSSQL The available protocols appear under the SQL Server 2005 n
46. n SQL Database 20 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 3 Installation MySQL The required parameterisations are carried out in the following window Here you assign Data Source Name Server User Password and select the database Now you can see the reason why we recommend creating the database first because the driver checks straight away which databases have been made available This is followed by checking whether the connection can be established Please find below an example parameter assignment for a localhost application Figure 3 14 ODBC Parameterise driver localhost EN Connector ODBC 3 51 14 Configure Connector ODBC Login Connect Options Advanced Connector ODBC Configuration Data Source Name scales_machine_localhost This dialog is used to edit a Data Description Source Name CSM Server localhost User 2uL1 Password GE Database scales_machine ss Diagnostics gt gt Ok Cancel Help In the next picture we parameterise the ODBC driver for a TCP IP connection In order to access the SQL database via TCP IP it is required to previously define a user in DBMS which in this case has been created under the name WinAC see Chapter 3 2 3 WinAC Link to an SQL Database V1 20 Entry ID 48354880 21 Copyright Siemens AG 2009 All rights reserved 3 Installation MySQL 3 4 22 Figure 3 15 Parameterise ODBC driver TC
47. ng documents Table 1 1 Required expertise Sem cmon STEP 7 S7prv54_d pdf MsSQL http technet microsoft com de de library ms165706 aspx MySQL DokuMySQL 5 1 de a4 pdf Reference System e SIMATIC Microbox PC 427B 1 GHz 512 MB RAM 1 GB Flash with Windows XP embedded SP2 e WinAC RTX 2009 e SIMATIC NET V6 3 HF1 e STEP 7 V5 4 SP4 e SQL Server odbc 03 85 1117 e Microsoft SQL 2005 Server Express Edition Version 9 00 1399 06 06 e SQL Server Management Studio Express e mysql connector odbc 3 51 14 win32 e mysql 5 0 37 win32 e mysql gui tools 5 0 r11a win32 WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 2 Overview 2 Overview 2 1 Function Scope The following functions of the SQL data link are supported by means of this driver e to transmit SQL statements with flexible arguments possible statements SELECT INSERT UPDATE DELETE e to receive read data from the SQL database 2 2 Version of the Driver Determine Driver Version Under Windows The regi C W stered driver DLL is located in the system directory e g INNT system32 dll or C Windows system32 dll Determine the driver DLL version by viewing the file properties in Windows Explorer right mouse click gt properties Figure 2 1 Version of Driver DLL Eigenschaften von SqiDa Allgemei h Yersion Sicherheit Dateinfo Daterversion 1 0 1 0 Beschreibung S
48. ngaben verwenden Abfragen mit langer Laufzeit in dieser Protokolldatei speichern lc SDORUME TSUSERTSLORSLE 1 Temp Qu Durchsuchen Mindestlaufzeit f r Protokollierung in Millzekunden 30000 ODEBC Treiberstatistik in dieser Protokolldatei protokollieren C DOKUME 1 SUSERUSLORALE1TTempssT4 amp Durchsuchen lt Zur ck Abbrechen Hilfe After parameterisation has been completed a summary of the ODBC configurations is displayed where the connection to the SQL server can be tested Figure 4 12 DSN Summary ODBC Microsoft SQL Server Setup i 2 x Eine neue DEL Datenguelle wird mit folgender Konfiguration erstellt Microsott SOL Server DBL Treiber Version 03 55 1117 Datenquellenname scales machine T EP IP Datenquellenbeschreibung Migration MySQL tur MS SQL Server 41792 166 7 90 7 33 Ve WAR EOOTSOLESPRESS Datenbank Default Sprache Default zeichen konvertieren Yes Abfragen mit langer Laufzeit protokolleren Mo Protokolltreiberstatistik Mo Integrierte Sicherheit verwenden No L ndereinstellungen verwenden No Option fur vorbereitete Anwelungen Temporare Prozeduren beim Trennen loschen FalloverServer verwenden Mo AMSI Anfuhrungszeichen verwenden Y es AMSI Mullen Leerzeichen und Warmnungen verwenden Yes Datenverschlusselung Mo ok _Atbeehen WinAC Link to an SQL Database V1 20 Entry ID 48354880 35 Copyright Siemens AG 2009 All rights reserved 4 Installa
49. nutzers Email Die Email Adresse des Benutzers Kontaktinformation Wahlweise Kontaktinformation SB Neuer Benutzer v gt All Jeuen Nutzer anlege Anwenden Verwerfen Ai Use the Apply key to save the user in the database The access authorisations for the respective user are parameterised under the tab page Schematics Authorizations WinAC Link to an SQL Database V1 20 Entry ID 48354880 17 Copyright Siemens AG 2009 All rights reserved 3 Installation MySQL Figure 3 10 Schematics Authorizations MySQL Administrator Connection root localhost 3306 Datei Bearbeiten View Extras Fenster MySQL Enterprise Hilfe 38 Serverinformation x Dienstverwaltung By Startvariablen 8a Benutzerverwaltung WE Aktive Verbindungen WinAC winAC RTX Vw Serverstatus 3 books Serverprotokolle B information_ schema F Replikationsstatus mysql F J scales _machine C Backup ee RA Wiederherstellung g Kataloge Benutzerkonten as root B winac Schemata Zugewiesene Berechtigungen Benutzerinformation chema Berechtigungen Ressourcen Diesem Benutzer zugewiesene Schema Berechtigungen Verf gbare Berechtigungen A SELECT A INSERT A UPDATE A DELETE A CREATE A DROP A GRANT A REFERENCES TA INDEX A ALTER A CREATE_ TM S LOCK_TABL S CREATE_VI A SHOW VIEW A CREATE_RO A ALTER_ROU A EXECUTE Yeuen Nutzer anlegen Anwenden Yerwerfen A User resou
50. of the DB Figure 6 9 DB for SQL String Typ Anfangswert 0 0 SELECT year test FROM titles WHERE titleid lt s d 512 0 SQL String3 STRING Z54 3 768 0 SQL_ String4 STRING Z54 G e SQL_ARGUMENTS The arguments are transferred to a pointer which points to a DB Please note that the arguments must be arranged in the same order as the placeholders The amount of placeholders must also match the arguments An unlimited number of arguments may be entered It is also allowed to have various data types in a string It is important to note that the correct length is transferred to the pointer As a reference value you can use the address which is listed last in the address column of the DB The maximum amount which may be used on arguments is limited due to the maximum data storage in one DB 65 Kbytes and by a maximum amount of 1000 arguments Figure 6 10 DB for SQL Arguments END STRUCT e SQL RECEIVED DATA A pointer is transferred which points to a DB which contains a certain amount of UDTs where read data can be stored Therefore the SQL_RECEIVE DATA is in its actual sense an output because read data are being made available WinAC Link to an SQL Database V1 20 Entry ID 48354880 49 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs 50 The UDTs in the DB must exist in the same order as data is supplied by the block The amount of supplied data must also match the provided U
51. on Returns ODK Error Function Description Code 9500 Wrong format specification in STRING for STATEMENT 9501 Read S7 STRING failed 9502 Buffer Overflow in STATEMENT read 9503 Read S7 ARGUMENT TYPE failed WinAC Link to an SQL Database 60 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 8 Error Messages 9533 Non conforming VALUETYPE from SQLSERVER to S7 DATABLOCK expected STRING WinAC Link to an SQL Database V1 20 Entry ID 48354880 61 Copyright Siemens AG 2009 All rights reserved 8 Error Messages 9552 9553 9554 9555 9556 9557 9558 9559 ODBC INIT CLOSEDB not yet ready WAIT FOR SINGLE OBJ FAILED for ODBC CLOSEDB EVENT Not defined WAITFORSINGLEOBJ RETURN for ODBC CLOSEDB EVENT Not defined PRINTF ARGUMENT in STRING Cannot find DLL PATH and FILENAME Cannot read VERSION INFO SIZE Cannot read VERSION INFO Cannot verify query the value Version Nr does not match poe different versions STEP 7 to DLL 8 2 2 Function Code Numbers Table 8 4 Function Code Numbers Function Code Function Description 9000 SQL ALLOC ENVIRONMENT HANDLE 9001 ODBC VERSION ENVIRONMENT ATTRIBUTE 9002 SQL ALLOC CONNECTION HANDLE 9003 CONNECT DATA SOURCE 9004 SQL ALLOC ARGUMENT HANDLE 9005 BUILT SQL STRING 9006 COUNT OF COL 9007 DESCRIBE COL TYPE 9008 BIND COLUMN WHITH VALUETYPE 9009 FETCH ARGUMENT 9010 FREEING ARGUMENT HANDLE 901 1 CLOSE CONECTION 9012 FREEING CONNECTION HANDLE
52. or OB is invalid 0x8109 E_INVALID_INSTANCEID Instance ID for SFB65001 call is invalid 0x810A E_START_ODKPROXY_FAILED Controller could not load proxy DLL 0x810B E CREATE SHAREMEM FAILED The WinAC controller could not create or initialize shared memory area 0x810C E OPTION NOT AVAILABLE Attempt to access unavailable option occurred WinAC Link to an SQL Database V1 20 Entry ID 48354880 59 Copyright Siemens AG 2009 All rights reserved 8 Error Messages 8 1 2 Error Messages for SFB65002 STATUS ODK_CON EXEC These error messages are returned by all FBs except SQL_INIT Table 8 2 WinAC ODK Error Messages for STATUS ODK_CON EXEC oo oero sos 0x8005 E OUTPUT RANGE_INVALID Output the ANY pointer range is invalid Ox8006 E OUTPUT OVERFLOW More bytes were written into the output buffer by the extension object than were allocated Ox8007 E NOT_INITIALIZED ODK system has not been initialized no previous call to SFB65001 CREA_COM 0x8008 E HANDLE OUT_OF_RANGE The supplied handle value does not correspond to a valid extension object Ox8009 E INPUT OVERFLOW More bytes were written into the input buffer by the extension object than were allocated 8 2 Special Error Messages of SQL DB Driver In addition to the general error bit of the FBs several special error codes and error statements are supplied in OdkSqlErrorMsg which describe the cause in more detail 8 2 1 ODK Function Returns Table 8 3 ODK Functi
53. provide support for typical applications You are responsible for ensuring that the described products are used correctly These application examples do not relieve you of the responsibility to use safe practices in application installation operation and maintenance When using these Application Examples you recognize that we cannot be made liable for any damage claims beyond the liability clause described We reserve the right to make changes to these Application Examples at any time without prior notice If there are any deviations between the recommendations provided in these application examples and other Siemens publications e g Catalogs the contents of the other documents have priority We do not accept any liability for the information contained in this document Any claims against us based on whatever legal reason resulting from the use of the examples information programs engineering and performance data etc described in this Application Example shall be excluded Such an exclusion shall not apply in the case of mandatory liability e g under the German Product Liability Act Produkthaftungsgesetz in case of intent gross negligence or injury of life body or health guarantee for the quality of a product fraudulent concealment of a deficiency or breach of a condition which goes to the root of the contract wesentliche Vertragspflichten The damages for a breach of a substantial contractual obligation are
54. rameterised with the appropriate UTD This is followed by the transmission of the error codes to the DB For the individual error numbers and error statements please refer to tables 7 3 to 7 6 in the appendix where you will find the necessary explanations The following block diagram illustrates the syntactical sequence WinAC Link to an SQL Database V1 20 Entry ID 48354880 51 6 Detailed Description of FBs 13 Flow Chart of ODBC READ_WRITE Figure 6 Vad i Vad A JS TVd SPHOAPYIS Jap u z s SNL JONY Vad i Vad SHOyWUYIS Jap u z s Ina JONY SNSAYUUSS Vad Jap u SNL AS WA Vad ezjesyony sliqsn Je S Jop u z s JMH JS Vd ueBunpjew 19149 1 Jap usseidoy Buljpueusouy uabunpjew Vad Vid Vad SAISNBYOREdO lt 19 y9 JEP QpWD sep Jniinv uaJaidoy A rN A ISTVJ S TVd s u uun6iv 1OS pe i lt SPSAPUYIS SNSYNUYIS EPWO sap jnyny Anal J p u zl s Joep u z s ANAL JONY yony Buns omnz ee us unpjsw us unpjew 19 u j Jap i 19 49 1 p u i idoy ast uaseldoy z e eq TOS SANSISAUIITAO lt e0el JUSAZ GPW sep JNINYy 8INIEXFIIAO lt u Bunp uu PPWD SEP JNIJNY 19 49 Jap Bunsiemnz us191doy vad J9 UIOA 4
55. rces can be limited under the tab page Resources Figure 3 11 User resources MySQL Administrator Connection root localhost 3306 Datei Bearbeiten View Extras Fenster MySQL Enterprise Hilfe 38 Serverinformation ey Dienstverwaltung By Startvariablen 8a Benutzerverwaltung g Aktive Verbindungen WB Serverstatus Serverprotokolle F Replikationsstatus lt Backup at he ea Wiederherstellung Kataloge Benutzerkonten root A winac 18 Benutzerinformation Schema Berechtigungen Ressourcen WinAC WinAC RTX Ressourcenverwaltung Benutzerressourcen beschr nke max_questions max_updates max_connections max_user_connections Anzahl Abfragen die der Benutzer innerhalb einer Stunde ausf hren kann Anzahl von Aktualisierungen der Nutzer je Stunde ausf hren kann Anzahl Verbindungen zum Server die der Nutzer je Stunde ffnen kann Anzahl simultaner Verbindungen zum Server von diesem Konto Yeuen Nutzer anlegen Anwenden Verwerfen Z WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 3 Installation MySQL 3 2 5 Transaction Safe Data Transmission to MySQL Server MySQL runs in Autocommit mode as standard This means that as soon as a statement is processed which updates i e changes a table MySQL saves this change on the hard disk If however it is required to work with transaction safe data transmission and off
56. rights reserved 3 Installation MySQL Figure 3 12 Create ODBC Interface ODBC Data Source Administrator I User DSN System DSN File DSH Drivers Tracing Connection Pooling About System Data Sources Name Driver Adr Microsoft Test Treiber txt ce ExtBooks MySQL ODBC 3 51 Driver Remove MySQL MM SUL ODBC 3 51 Driver MySGLbooks MySG L ODBC 3 51 Driver Configure scales machine localhost MySQL ODBC 3 51 Driver scales machine T EPFP_IP MySQL ODBC 3 51 Driver An ODBC System data source stores information about how to connect to the indicated data provider A System data source Is visible to all users on this machine including MT services OF Abbrechen bernehmen Hilfe As can be seen in the view above several parameterised interfaces are already available Press Add to add a new one The different drivers can be selected in the following menu In our application we are using a MySQL ODBC 3 51 Driver Figure 3 13 Select ODBC driver Create New Data Source u X Select a driver For which you want to set up a data source Microsoft Parados Driver db Microgott Parados Treiber db Microsoft Text Driver Est cev Microsoft Text Treiber tat cey Microsoft Visual FoxPro Driver Microsoft Visual FoxPro Treiber MySQL ODBC 3 51 Driver SOIL Native Client SOL Server Mm PO Od Ti Ti F fF Fa J ki lt ZUTICH Fertig stellen Abbrechen WinAC Link to a
57. rver verbinden um Standardeinstellungen fur die r Dr zus tzlichen F orifigurratiorizsoiptiorieri zu erhalten Benutzername winacsal sae eae ee Kennwort 1 lt Zur ck Abbrechen Hilfe In order to access the SQL database via TCP IP you must previously define a user in DBMS In the next two steps the settings for the example project may be adopted unchanged Figure 4 10 DSN Configurations Microsoft SOL Server DSN Konfiguration X Die Standarddatenbank ndem auf scales_machine_mig Datenbank Dateinamen anf gen u ___ F Temporar gespeicherte Prozeduren f r vorbereitete SUL Anweisungen erstellen und gespeicherte Prozeduren loschern Hur beim Tremen Beim rennen und bei geeigneter Situation wahrend der verbindung M AMSI Anf hrungszeichen verwenden z AMSI Nullen Leerstellen und w amungen verwenden a Faloyer SOL Server versenden Wenn der primare SUL Semeri nieht verugbar iet lt Zur ck Abbrechen Hilfe WinAC Link to an SQL Database 34 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 4 Installation MSSQL Figure 4 11 DSN Language Settings Microsoft SOL Server DSN Konfiguration X Sprache der SOL Server Systemmeldungen ndern auf Geman j Starke Werschl sselung f r Daten verwenden Z Konvertierung f r Zeichendaten ausf hren L ndereinstellungen bei der Anzeige von w ahrurugs Zahlen Datums und Zeita
58. sgew hlt Die Subfeatures erfordern 99 MB auf Ihrer Festplatte Installationsprad CiProgram Files Microsoft SQL Server Durchsuchen Speicherplatz Zur ck Weiter gt Abbrechen The instance name is maintained for the example programme Figure 4 2 Instance name j Setup f r Microsoft SQL Server 2005 Express Edition Instanzname Sie k nnen eine Standardinstanz installieren oder eine benannte Instanz angeben lt eben Sie einen Mamen F r die Instanz an Klicken Sie f r eine Standardinstallation auf skandardinstanz und klicken Sie dann auf Weiber Wenn Sie eine vorhandene Standardinstanz aktualisieren m chten klicken Sie auf Standardinstanz Wenn Sie eine vorhandene benannte Instanz aktualisieren m chten klicken Sie auf Benannte Instanz und geben Sie dann den Instanznamen an Standardinstanz Benannte Instanz 5QLExpress Klicken Sie auf Installierte Instanzen um eine Liste der vorhandenen Instanzen und komponenten anzuzeigen Installierte Instanzen Hilfe lt Zur ck weiter gt abbrechen When setting the utility account select the network utility For the utilities to be started you need the SQL server only for the example project WinAC Link to an SQL Database 24 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 4 Installation MSSQL Figure 4 3 Utility account jis Setup f r Microsoft SQL Server 2005 Express
59. tion MSSQL The test results are now displayed in a window Figure 4 13 DSN Test SOL Server ODBC Datenquelle testen I Testergebnisse Microsoft SUL Server DEL Treiber Version 03 85 1117 Verbindungstests laufen Versuch die Verbindung aufzubauen Yerbindung aufgebaut best tige Uptionseinstellungen Verbindung mit dem Server wird getrennt TESTS ERFOLGREICH ABGESCHLOSSEN 4 4 Installation WinAC Driver on SIMATIC Engineering Computer This documentation as well as the STEP 7 example project is required on the SIMATIC Engineering computer The required FBs for the user s STEP 7 programme may be taken from this demo project The ODBC connection data in the STEP 7 project must be adapted in DB10 DSN User Password These parameters must be specified because several ODBC connections may be parameterised When assigning a name please be aware that input is case sensitive WinAC Link to an SQL Database 36 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 5 Functional Description 5 Functional Description After the installation this chapter gives a rough description of how the connection to the SQL database server is established and the possible functions 5 1 Basics There are four function blocks available for the use of an SQL database in WinAC RTX These function blocks serve to load the DLL into the address space in WinAC RTX initialise required data establish the connec
60. tion to the SQL server transmit SQL statements make available any data which have possibly been retrieved close the database connection In order to avoid any buffer overflow always close the SQL database with the appropriate function block This also applies in the event of an error The listed functions are allocated to the function blocks as illustrated in the table below WinAC Link to an SQL Database V1 20 Entry ID 48354880 37 Copyright Siemens AG 2009 All rights reserved 5 Functional Description Table 5 1 Overview of FBs Screen Name Function e load DLL into address SQL_INIT space of WinAC RTX e initialise required data establish connection to Soho SQL server transmit SQL statements SQL _ EXEC make available any possibly retrieved data SQL DISCON close database u connection WinAC Link to an SQL Database 38 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs 6 Detailed Description of FBs 6 1 ODK Initialisation Block The ODK initialisation block is the first block which must be processed in the step sequence It loads the DLL file to the address space in WinAC RTX and distributes the returned Handle to the other ODBC FBs It also initialises all error memories and retentive data in the different instance data blocks DI of the ODBC FBs including its own DI The initialisation block and the FB which
61. to the respective usages e The UDT s in Argument DB must match the placeholders in the SQL string The pointer transfers lengths of data to be transferred must match the data lengths in the DB WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 8 Error Messages 8 Error Messages The WinAC SQL DB Driver can supply different classes of error messages e Code in FB output STATUS ODK CREA CON STATUS ODK_CON in accordance with WinAC ODK e Special error messages of SQL DB driver OdbcSalDefineErrorCode ODBC Error Code 8 1 Error Messages of WinAC ODK 4 1 The WinAC SQL DB driver was developed with WinAC ODK Open Development Kit ODK can also generate error codes which are returned in OdkSqlErrorMsg of the FBs 8 1 1 Error Messages for SFB65001 STATUS ODK_CREA_CON These error messages can only be returned by FB SQL_INIT Table 8 1 WinAC ODK Error messages fof STATUS ODK_CREA_CON O C NOERRORS Stews 0x8102 E CLSID_FAILED The call to CLSIDFromProgID failed 0x8103 E COINITIALIZE FAILED The call to ColnitializeEx failed 0x8104 E_CREATE_INSTANCE_FAILED The call to CoCreatelnstance failed 0x8105 E_LOAD_LIBRARY_FAILED The library failed to load 0x8106 E_NT_RESPONSE_TIMEOUT A Windows response timeout occurred 0x8107 E_INVALID_OB_STATE Controller is in an invalid state for scheduling an OB 0x8108 E_INVALID_OB_SCHEDULE Schedule information f
62. ypes DOUBLE and REAL relating to SQL consist of 64Bit These are not supported by STEP 7 and are converted to STEP 7 REAL 32Bit Due to the conversion of a 64Bit number to a 32Bit number the value loses accuracy When using data type DOUBLE or REAL on SQL level the values are converted to STEP 7 REAL in WinAC The consequence is loss of accuracy of the converted value Note The data type FLOAT in SQL level corresponds to data type REAL on der STEP 7 level Figure 6 8 ODBC READ and WRITE Block SQL_EXEC STATUS Prior to examining the syntactical part of the block let us first look at the input and output parameters again Attention The EN input and the ENO output are not parameterised WinAC Link to an SQL Database 48 V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs Input Parameter SQL_ EXEC e REQ Input REQ is the On Off switch on the FB If there is a logical One at the input the block is processed e Timeout An integer value is set at this input which indicates the maximum asynchronous processing time of the block in seconds e SQL_STRING A pointer to a DB is transferred to a DB This contains four Strings in which the SQL String is written The maximum length of an SQL String is 1016 characters Please note that the correct length is transferred to the pointer As a reference value you can use the address which is listed last in the address column
63. zi 2 2 i l i END STRUCT 0 0 l 0 0 0 0 0 0 0 E a 0 gt E 0 l Now if this UDT was created in a DB SQL CON is parameterised with the appropriate UTD This is followed by the transmission of the error codes to the DB WinAC Link to an SQL Database V1 20 Entrys ID 48354880 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs For the individual error numbers and error statements please refer to tables 7 3 to 7 6 in the appendix where you will find the necessary explanations The following block diagram illustrates the syntactical sequence Figure 6 6 Flowchart of SQL_CON Aufruf von SQL_CON TRUE R ck FALSE TRUE setzen der Statusbits FALSE STEP2 TRUE Pointer Zuweisung BEA BEA RA Aufruf des Cmd0 gt ODBtClnit Zeit Aufruf des Cmd1 Kopieren gt ODBCInit_Event der Fehler _ Trace meldungen Kopieren R cksetzen der Fehler TRUE von meldungen Schrittkette FALSE R cksetzen von TRUE BEA BEA Schrittkette FALSE R cksetzen BEA von Schrittkette BEA WinAC Link to an SQL Database V1 20 Entry ID 48354880 45 Copyright Siemens AG 2009 All rights reserved 6 Detailed Description of FBs 6 3 46 ODBC Read and Write Block for SQL Database The central block SQL_

48354880_WinAC_SQL_DB_Doc_V12_en

Contents

Download Pdf Manuals

Related Search

Related Contents