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.
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.
[-PAGE-]
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.
[-PAGE-]
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 |
[-PAGE-]
|
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.
[-PAGE-]
| |
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
[-PAGE-]
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.
[-PAGE-]
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 )
ENDIF
DBExecute( "DAS_DB_Plants.SQL_Text", "Requery" )
DBRead( "DAS_DB_Plants.SQL_Text" )