Database Connection To SQL Database - CX-Supervisor
Introduction Installation
The following programs / drivers must be installed: 1. The SQL server mysql-3.23.51-win.
2. The ODBC driver for mysql MyODBC 3.51.03.exe. This is the draw dsn file and is very simple.
3. If no database exists then MySql-Front will be installed. In the program, the databases are created and tested.
SQL Server
If the SQL Server is already running then you can see a green traffic light, otherwise it must be started manually (C: \ mysql \ bin WinMysqladmin). The server can also be configured (user) in this program.
Then start MySQL front and a new connection. For a local Database gives you Hostname = localhost, user = root port and 3306th
The connection is via TCP / IP on the local host. The root user is a standard user without a password. To be users and get passwords you have to create and configure the dsn file with these
specifications. my.ini File
WinMysqladmin with the program will include an ini file (tab - Choose Save and modification). In this are the settings of the SQL Server and computer settings.Network
Both computers must be installed with MySQL. Configuration of the local SQL Server, see above. To get from one machine to the computer with the Database the hostname or IP address must be known. Furthermore you need the username and password of the data bank on which you want access (if it is not root). To connect to MySQL start up and go to Connect. In this window, you can set the parameters for the new connection
A. Connection name, host / IP = IP address (eg 192.168.120.30)
User = username or root, password = if needed and the database which one wants to read. After the entries has been made these are stored, and then connect to the server.
Creating a dsn file
The dsn file contains the connection data to the SQL database.
Settings \ Administration \ ODBC sources, the database contains a dsn file allocated on which the data is realized. For the dsn file tab and select the ODBC driver (MYSqlODBC) and a file name. This applies only to the MySQL ODBC driver Version 3.51. With another driver, it is difficult to create a dsn file.
Creating a database
When starting MySqlFront must be connected with the SQL Server and the data base. If another user name other than "root" is given then use this, same applies for the password. If you have a
successful connection - create the front end with MYSQL database. Table, fields and set the datatypes of the fields. Set the structure of the database.
CX-Supervisor
Open the editor on the job (Workspace) tab and select Database add a link (right mouse button). Choose the dsn path (it is located C: \ Program Files \ Common Files \ ODBC \ Data Source). After the connection, you can assign variables to the record and the fields.
Append records to enable a second row with the same structure to be created. Only the fields with the property ADD will get the variables assigned in the record to be written.
To work properly, the database and dataset (recordset) must always be specified in SQL in the DB commands.
Limitations
The current record number read through Supervisor taken from the SQL database, is always 1 higher than the actual data displayed. A better method is currentPage, which agrees with the database entries match.
The same applies to 'next record' wherein the Next pointer jumps 2 rows further, not to NextPage or Previous, PreviousPage. Headings are not supported with the SQL database. To learn what functions of the database are used 'dbsupport'.
The returned function is either 1 supported or 0 is not supported.
The Record function change does not alter the currently displayed but the next record. When you append data to the SQL database the sorting appears illogical. In my example were all appended records are 5sec the Counter 1 indicates the current number. In the Access database they are all in ascending order, with the SQL records less than 700 descending order and then the rest in
ascending order.CX-Supervisor Settings
The setting is analogous to an Access database, just select the dsn file that you previously created. Select the datasource and then for selecting the dsn file. You should be in the folder C: \ Program Files \ Common Files \ ODBC \ Data Sources \ stand. When compiling the file, you can specify this folder.
After these settings, you can connect to the database (SQL server must be running).
Command Description DBOpen( „DB Name" ) Without restriction DBClose( „DB Name" ) Without restriction DBProperty->BOF
Not true if the database is in its early stages
DBProperty->EOF Without restriction
DBProperty->Current Record
Always displays a set one more than actual data DBProperty->Record count Without restriction DBProperty->Current page
Gives the actual number of the displayed entry again DBProperty->Page count DBProperty->Page size ? DBProperty->Field count
Always shows 0 even though the database has 13 fields DBStatus->Open Without restriction DBStatus->Close
Always true even if the database is open DBMove->First Without restriction DBMove->Last Without restriction DBMove->Next
2 sets of jumps return value works correctly
DBMove->NextPage
Return value is 0 when the function was executed correctly DBMove->Previous No reaction DBMove->PreviousPage
Also jumps to the next sequence, like Next and always returns 0
DBMove->Position
Also jumps to the next sequence, like Next and always returns 0 DBRead Without restriction DBDelete( „DB Name" )
DBWrite
Has the same problem it will be the next row changed, not the displayed DBAddNew Without restriction DBUpdate
Only after a record added DBExecute->Requery Without restriction DBExcute->SQL
SQL commands can be sent to the database
comandSQL = "UPDATE Table1 SET Counter_1 =" Value = ValueToText (Count_1_SQL) comandSQL + value = comandSQL
comandSQL = comandSQL + "WHERE position = 0"
bResult = DBExecute ( "Connection_SQL_dsn", "SQL", comandSQL) IF! bResult THEN
Status_SQL = DBGetLastError ( "Connection_SQL_dsn", TRUE) ENDIF
SQL command in Table1 in the column counter to change the value SET Supervisor into a point in the text and convert the command to add.
SQL command to set the value in each row to 0, by the column heading is entered.
SQL command to send to the database with the Execute method.
If the return value = 0 then last query error.
Column 1 indicates the facility from which the plant data comes. A record is always assigned to a facility.
Enabled column 2 indicates whether the plant data exists and thus whether the data is current. Type E/A Type Array Size Plant_Active Boolean Memory 10 Off / On [FALSE] Write into the DB cmdSQL Text Memory 1 SQL command Data_Plant1 Integer Memory 9 0 to 99999999 [0] Description Data_Plant2 Integer Memory 9 0 to 99999999 [0] Description Data_Plant3 Integer Memory 9 0 to 99999999 [0] Description
Data_Plant4 Integer Memory 9 0 to 99999999 [0] Description DB_Plant Integer Memory 1 0 to 99999999 [0] Description DB_Plant_Active Boolean Memory 1 Off / On [FALSE] Description DB_Plant_Active1 Boolean Memory 1 Off / On [FALSE] Description DB_Plant_Active2 Boolean Memory 1 Off / On [FALSE] Description DB_Plant_Active3 Boolean Memory 1 Off / On [FALSE] Description DB_Plant_Active4 Boolean Memory 1 Off / On [FALSE] Description DB_Data_Plant1 Integer Memory 9 0 to 99999999 [0] Description DB_Data_Plant2 Integer Memory
9 0 to 99999999 [0] Description DB_Data_Plant3 Integer Memory 9 0 to 99999999 [0] Description DB_Data_Plant4 Integer Memory 9 0 to 99999999 [0] Description DB_Data1 Integer Memory 1 -99999999 to 99999999 [0] Description DB_Data2 Integer Memory 1 -99999999 to 99999999 [0] Description DB_Data3 Integer Memory 1 -99999999 to 99999999 [0] Description DB_Data4 Integer Memory 1 -99999999 to 99999999 [0] Description DB_Data5 Integer Memory 1 -99999999 to 99999999 [0] Description DB_Data6 Integer Memory 1 -99999999 to 99999999 [0] Description
DB_Data7 Integer Memory 1 -99999999 to 99999999 [0] Description loop1 Integer Memory 1 0 to 99999999 [0] Loop counter Pointerpoint Text Memory 1 Description sError_DB Text Memory 1 Error message sValue Text Memory 1
ValueToText data conversion
Writing the Data into the SQL Database
Reading the Data from a SQL Database REM Data Plant 1
DBMove ( "DAS_DB_Plants.Table1", "FirstPage") bResult = DBRead ( "DAS_DB_Plants.Table1") IF! BResult THEN
Status_SQL = DBGetLastError ( "DAS_DB_Plants", TRUE) ENDIF DB_Plant_active1 = DB_Plant_active IF THEN DB_Plant_active1 FOR Loop1 = 0 to 6 IntTest = Loop1 +1
SValue = ValueToText (intTest) Points pointer = "DB_Data" + sValue DB_Data_Plant1 [Loop1] = ^ pointer points NEXT
ENDIF
Reading queries from the SQL Database
Within the Database tab, a table with SQL text appears, here you can create all the desired points to add.Reading queries from the SQL Database
Here are the points-based array points (eg SQL_Data1) from the displayed query. This is accessed through the index.
comandSQL = "SELECT * FROM table1 WHERE " comandSQL = comandSQL + Query_Column + " = " textTest = ValueToText(Query_Value)
comandSQL = comandSQL + textTest
bResult = DBExecute( "DAS_DB_Plants.SQL_Text", "Source", comandSQL ) IF !bResult THEN
ENDIF
DBExecute( "DAS_DB_Plants.SQL_Text", "Requery" ) DBRead( "DAS_DB_Plants.SQL_Text" )
