Database Connection To SQL Database - CX-Supervisor

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. 

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
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.

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.

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" )  It will delete the current record displayed
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)

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    SQL command 
Data_Plant1 Integer  Memory  0 to 99999999 [0] Description 
Data_Plant2 Integer Memory  0 to 99999999 [0] Description 
Data_Plant3 Integer Memory  0 to 99999999 [0] Description 
Data_Plant4 Integer  Memory  0 to 99999999 [0] Description 
DB_Plant Integer Memory  0 to 99999999 [0] Description 
DB_Plant_Active Boolean  Memory  Off / On [FALSE] Description 
DB_Plant_Active1 Boolean Memory  Off / On [FALSE] Description 
DB_Plant_Active2 Boolean Memory  Off / On [FALSE] Description 
DB_Plant_Active3 Boolean Memory  Off / On [FALSE] Description 
DB_Plant_Active4 Boolean Memory  Off / On [FALSE] Description 
DB_Data_Plant1 Integer  Memory  9 0 to 99999999 [0] Description 
DB_Data_Plant2 Integer Memory  0 to 99999999 [0] Description 
DB_Data_Plant3 Integer Memory  0 to 99999999 [0] Description 
DB_Data_Plant4 Integer Memory  0 to 99999999 [0] Description 
DB_Data1 Integer Memory  -99999999 to 99999999 [0] Description 
DB_Data2 Integer Memory  -99999999 to 99999999 [0] Description 
DB_Data3 Integer Memory  1 -99999999 to 99999999 [0] Description 
DB_Data4 Integer Memory  -99999999 to 99999999 [0] Description 
DB_Data5 Integer Memory  -99999999 to 99999999 [0] Description 
DB_Data6 Integer Memory  -99999999 to 99999999 [0] Description 
DB_Data7 Integer Memory  -99999999 to 99999999 [0] Description 
loop1 Integer Memory  0 to 99999999 [0] Loop counter 
Pointerpoint Text  Memory    Description 
sError_DB Text Memory    Error message 
sValue Text Memory    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)

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


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
Status_SQL = DBGetLastError( "DAS_DB_PLants", TRUE )

DBExecute( "DAS_DB_Plants.SQL_Text", "Requery" )
DBRead( "DAS_DB_Plants.SQL_Text" )