IBA ibaPDA-Interface-MySQL Owner's manual

Brand: IBA

Model: ibaPDA-Interface-MySQL

Type: Owner's manual

ibaPDA- Interface- MySQL

Write/read access to databases

Manual

Issue 1.3

Measurement Systems for Industry and Energy

www.iba- ag.com

Manufacturer

iba AG

Koenigswarterstr. 44

90762 Fuerth

Germany

Contacts

Main oce +49 911 97282-0

Support +49 911 97282-14

Engineering +49 911 97282-13

E- mail iba@iba- ag.com

Web www.iba- ag.com

Unless explicitly stated to the contrary, it is not permied to pass on or copy this document, nor

to make use of its contents or disclose its contents. Infringements are liable for compensaon.

The content of this publicaon has been checked for compliance with the described hardware

and soware. Nevertheless, discrepancies cannot be ruled out, and we do not provide guaran-

tee for complete conformity. However, the informaon furnished in this publicaon is updated

regularly. Required correcons are contained in the following regulaons or can be downloaded

on the Internet.

The current version is available for download on our web site www.iba- ag.com.

Version Date Revision Author Version SW

1.3 11-2023 GUI new rm 8.4.0

Windows® and SQL Server® are brands and registered trademarks of Microso Corporaon.

Oracle® and MySQL are registered trademarks of Oracle and/or its partners. MariaDB® is a regis-

tered trademark of MariaDB.

Other product and company names menoned in this manual can be labels or registered trade-

marks of the corresponding owners.

Issue 1.3 3

ibaPDA- Interface- MySQL Contents

Contents

1 Aboutthisdocumentaon ................................................................................................4

1.1 Target group and previous knowledge ..................................................................... 4

1.2 Notaons .................................................................................................................. 4

1.3 Used symbols ............................................................................................. ...............5

2 System requirements ........................................................................................................6

3 SQL database interface ......................................................................................................7

3.1 General informaon ................................................................................................. 7

3.2 Comparison to the DB/cloud data storage ............................................................... 8

3.3 System topologies ..................................................................................................... 8

3.4 Basic procedure ........................................................................................................ 9

4 ConguraonandengineeringibaPDA ............................................................................ 10

4.1 Interface sengs .................................................................................................... 10

4.2 Creang a database connecon ............................................................................. 12

4.2.1 MySQL/MariaDB DB connecon ............................................................................. 13

4.3 SQL modules ........................................................................................................... 14

4.3.1 Adding an SQL module ........................................................................................... 14

4.3.2 General module sengs ......................................................................................... 15

4.3.3 Buer ...................................................................................................................... 17

4.4 SQL statements ............................................................................................ ........... 20

4.4.1 Tesng SQL statements ........................................................................................... 21

4.4.2 Linking SQL queries with input signals.................................................................... 22

4.4.3 Using parameters .................................................................................................... 25

5 Diagnoscs ......................................................................................................................27

5.1 License .................................................................................................................... 27

5.2 Visibility of the interface .. ....................................................................................... 27

5.3 Log les ............................................................................................ .......................28

5.4 Connecon diagnoscs with PING .......................................................................... 29

5.5 Diagnosc modules ................................................................................................30

6 Support and contact ........................................................................................................ 35

4 Issue 1.3

About this documentaon ibaPDA- Interface- MySQL

1 Aboutthisdocumentaon

This documentaon describes the funcon and applicaon of the soware interface

ibaPDA- Interface- MySQL.

This documentaon is a supplement to the ibaPDA manual. Informaon about all the other

characteriscs and funcons of ibaPDA can be found in the ibaPDA manual or in the online help.

1.1 Targetgroupandpreviousknowledge

This documentaon is aimed at qualied professionals who are familiar with handling electrical

and electronic modules as well as communicaon and measurement technology. A person is

regarded as professional if he/she is capable of assessing safety and recognizing possible con-

sequences and risks on the basis of his/her specialist training, knowledge and experience and

knowledge of the standard regulaons.

This documentaon in parcular addresses persons, who are concerned with the conguraon,

test, commissioning or maintenance of supported databases, cloud or cluster storage technolo-

gy. For the handling of ibaPDA- Interface- MySQL the following basic knowledge is required and/

or useful:

■ Basic knowledge of ibaPDA

■ Basic knowledge of databases, cloud or cluster storage technology

■ Applicaon of SQL statements

1.2 Notaons

In this manual, the following notaons are used:

Acon Notaon

Menu command Menu Logic diagram

Calling the menu command Step 1 – Step 2 – Step 3 – Step x

Example:

Select the menu Logic diagram – Add – New funcon

block.

Keys <Key name>

Example: <Alt>; <F1>

Press the keys simultaneously <Key name> + <Key name>

Example: <Alt> + <Ctrl>

Buons <Key name>

Example: <OK>; <Cancel>

Filenames, paths Filename, Path

Example: Test.docx

Issue 1.3 5

ibaPDA- Interface- MySQL About this documentaon

1.3 Usedsymbols

If safety instrucons or other notes are used in this manual, they mean:

Danger!

Thenon-observanceofthissafetyinformaonmayresultinanimminentrisk

ofdeathorsevereinjury:

■ Observe the specied measures.

Warning!

Thenon-observanceofthissafetyinformaonmayresultinapotenalriskof

deathorsevereinjury!

■ Observe the specied measures.

Cauon!

Thenon-observanceofthissafetyinformaonmayresultinapotenalriskof

injuryormaterialdamage!

■ Observe the specied measures

Note

A note species special requirements or acons to be observed.

Tip

Tip or example as a helpful note or insider p to make the work a lile bit easier.

Otherdocumentaon

Reference to addional documentaon or further reading.

6 Issue 1.3

System requirements ibaPDA- Interface- MySQL

2 System requirements

The following system requirements are required to use the SQL interface to a database of the

MySQL or MariaDB type:

■ ibaPDA v8.4.0 or higher

■ License for ibaPDA- Interface- MySQL

For further requirements for the used computer hardware and the supported operang sys-

tems, refer to the ibaPDA documentaon.

Licenseinformaon

Order no. Product name Descripon

31.001116 ibaPDA- Interface- MySQL Extension license for an ibaPDA system for

reading from and wring to databases of

the MySQL or MariaDB type. License for

8 SQL modules according to 8 SQL state-

ments.

31.101116 one- step- up- Interface- MySQL Extension license for an addional 8 SQL

modules. Max. 3 permissible (32 SQL mod-

ules total)

Issue 1.3 7

ibaPDA- Interface- MySQL SQL database interface

3 SQL database interface

3.1 Generalinformaon

The SQL interface of ibaPDA forms the basis for a series of database interfaces via which ibaPDA

can read data both from databases as well as write data in databases.

sing the SQL interface you congure and manage the connecons to one or more databases.

You can congure one or more connecons per database. All DB connecons can be used simul-

taneously. The databases can be the same or dierent systems.

Database systems of dierent vendors are supported:

■ Maria DB

■ MySQL

■ Oracle

■ PostgreSQL

■ SAP HANA1)

■ SQL Server

The data exchange with the databases occurs via corresponding SQL modules, which are cong-

ured according to the specic database system.

The data is accessed with SQL statements.

■ Input direcon (read)

For the reading access, custom SQL queries (e.g. SELECT) are used and their results are

mapped to input signals in ibaPDA.

In the queries, you an oponally use parameters, which are assigned to the signals, in order

to change the queries dynamically during the ongoing acquision

■ Output direcon (write)

SQL commands (e.g. UPDATE, INSERT) are used for wring access in order to write data

from ibaPDA into the database.

In the statements, you can oponally use parameters, which are assigned to the signals, in

order to write signal actual values.

You can execute queries and commands cyclically or triggered.

1) DB client installaon ".NET data provider" required on ibaPDA server

8 Issue 1.3

SQL database interface ibaPDA- Interface- MySQL

3.2 ComparisontotheDB/clouddatastorage

In addion to quick measured values that are acquired via other interfaces, the SQL interface

serves mainly to acquire addional data about a process, a plant or a product or to output

current values, operang statuses or calculated values to a database. The access may occur by

request or cyclically, whereby you must always take the response me of the database into con-

sideraon.

The corresponding data storage (e.g. ibaPDA- Data- Store- SQL- Server, ibaPDA- Data- Store- SQL- Or-

acle etc.) is to be used for faster connuous data wring in databases or cloud systems.

SQL interfaces and DB data storage support the same database systems so that you can choose.

SQL interface DB/cloud data storage

■ Conguraon in the I/O Manager

(read/write)

■ Conguraon as data storage

(write only)

■ Access to any exisng custom table/view is

possible

■ The user must provide tables, e.g. by SQL

statements

■ Fix table structure, specied by the data

storage prole

■ Table is automacally generated by

ibaPDA.

■ Cyclical/triggered reading with custom SQL

queries

■ Cyclical/triggered inserng or updang

with custom SQL commands

■ Connuous/triggered wring of signal

trends as me series data

■ Reading the signal trends from the data-

base with SQL trend queries in ibaAnalyzer

■ Reading the signal trends from the data-

base with ibaDaVIS

Table 1: Dierences between SQL interface and DB/cloud data storage

3.3 Systemtopologies

The connecons between databases and ibaPDA can be established via standard Ethernet inter-

faces of the computer.

Note

If highly cyclical accesses or very large data volumes are expected, it is recom-

mended to implement TCP/IP communicaon on a separate network segment in

order to rule out a mutual interference by other network components, such as

PLC systems.

Issue 1.3 9

ibaPDA- Interface- MySQL SQL database interface

3.4 Basicprocedure

1. Check whether addional soware and/or licenses of the database manufacturer are re-

quired for the planned database connecon.

2. If necessary, install the required soware on the ibaPDA computer, e.g. the soware

"ADO.NET Data Provider" for SAP HANA connecons.

3. In ibaPDA, open the I/O Manager, Inputs tab, and congure one or more database connec-

ons on the SQL interface.

See ì Creang a database connecon, page 12

For database queries (read access)

1. Under the SQL interface on the Inputs tab, add a module SQL query and select one of the

previously congured database connecons for the module.

2. Formulate an SQL statement for the database query with or without parameters.

3. If you use parameters: Assign the corresponding analog and/or digital signals to the parame-

ters whose values you want to use for the query.

4. Congure the analog and/or digital input signals.

Fordatabasecommands(writeaccess)

1. Under the SQL interface on the Outputs tab, add a module SQL command and select one of

the previously congured database connecons for the module.

2. Formulate an SQL statement as an SQL command with or without parameters.

3. If you use parameters: Assign the corresponding analog and/or digital signals to the parame-

ters whose values you want to write in the database.

Cauon

ThenetworkandDBcanbeoverloadedbyhighlycyclicalDBaccessesand/

orverylargedatavolumes.IntheSQLstatements,anySQLcommandscanbe

usedandthereforecausedamage(e.g."deletetable","dropdatabase",etc.).

Therefore...

■ Only have qualied users create/change SQL statements (→ user manage-

ment, security administraon).

■ Involve your DB administrator for quesons about conguring the SQL state-

ments. For example, the DB user used in ibaPDA for the DB connecon should

only have the rights that are actually required.

■ Set an appropriate update me (as short as necessary, as long as possible).

■ Assess and test the results of your SQL statement.

10 Issue 1.3

Conguraon and engineering ibaPDA ibaPDA- Interface- MySQL

4 ConguraonandengineeringibaPDA

The engineering for ibaPDA is described in the following. If all system requirements are fullled,

ibaPDA displays the SQL database interface in the interface tree of the I/O Manager.

Note

The SQL interface is visible as soon as one of the SQL interface licenses (SQL

Server, Oracle, PostgreSQL, MySQL, SAP HANA) is available on the license con-

tainer (dongle or so license). Even if only one database system is licensed, all

supported DB systems appear in the SQL interface conguraon. Nevertheless,

you can only use the licensed syst mfor data acquision.

4.1 Interfacesengs

The interface has the following funcons and conguraon opons.

Setallvaluestozerowhentheconnecontoadatabaseislost

If this opon is enabled, all signal values in the modules are set to zero as soon as the connec-

on is lost. ibaPDA regularly aempts to restore the database connecon unl it either suc-

ceeds or the acquision stops.

If this opon is disabled, ibaPDA keeps the last values in memory when the connecon is lost.

Note

It may take some me unl the signal values are set to zero. A longer response

me of a database, even in the range of several seconds, is not necessarily an

indicaon of an interrupted connecon.

Startacquisionevenifadatabaseisnotaccessible

If this opon is enabled, the acquision starts even if one or more of the congured databases

are not accessible. A warning is prompted in the validaon dialog, not an error. If the system

was started without a connecon to a database, ibaPDA periodically tries to connect to the da-

tabase.

Issue 1.3 11

ibaPDA- Interface- MySQL Conguraon and engineering ibaPDA

DBconnecons

This area displays the congured database connecons. Each database connecon is clearly

idened with an ID number, the database system and the connected database. You can use the

buons to create, edit and delete database connecons. The coming chapters describe the con-

guraon of the database connecons.

<Openlogle>

If connecons to controllers have been established, all connecon specic acons are recorded

in a text le. Using this buon, you can open and check this le. In the le system on the hard

disk, you nd the log les of the ibaPDA server (…\ProgramData\iba\ibaPDA\Log).

The le name of the current log le is InterfaceLog.txt; the name of the archived log

les is InterfaceLog_yyyy_mm_dd_hh_mm_ss.txt.

<Resetstascs>

Click this buon to reset the calculated mes and error counters in the table to 0.

Tablewithconneconstascs

The boom area of the dialog contains a table for the purpose of connecon diagnoscs. Stas-

cal informaon, such as counter values and mes, are displayed for each congured SQL mod-

ule.

Note

Due to the nature of relaonal databases, error counters may increment more

slowly than success counters. Note this when diagnosing connecon problems.

Column Meaning

Module Name of the SQL module (query and command modules)

Statements Processed: Number of executed SQL statements

Error: Number of SQL statements where failures occurred. Either SQL state-

ments failed (database responds with error) or statements could not be

executed, because the database was not accessible and the buer was full.

An empty result for a statement is not considered an error.

Rows Max: The highest number of rows that was returned since the last start of

acquision or the last reset of the counter aer a statement.

Last: The number of rows that was returned aer the last statement.

Response me Time values for the duraon between the execuon of the SQL statement

and the conrmaon of the execuon by the database.

Min, Max, Avg: These values relate to the executed statements since the

start of the acquision or the last stascs reset.

Last: Response me of the last executed statement.

Table 2: Columns of the connecon diagnoscs

12 Issue 1.3

Conguraon and engineering ibaPDA ibaPDA- Interface- MySQL

4.2 Creangadatabaseconnecon

Before you can exchange data with a database, you must rst congure a connecon.

1. Select the interface SQL Database in the interface tree and click on the buon

<Create new>.

2. Select the desired database system.

The lower part of the dialog changes depending on which system type you select.

3. In the lower part of the dialog, enter the informaon about the database or select it (e.g. DB

server, database name, authencaon method, possibly user name and password).

You can nd more details in the next chapter.

By specifying this informaon, a name is automacally generated for this connecon and dis-

played in the eld Connecon name (user name@database). You can overwrite the name.

In the case of mulple connecons to the same system type, you can disnguish the connec-

on using this name. In addion, you select the DB connecon later using this name during the

module conguraon.

The connecon name is displayed in the table with the DB connecons in the DB name column.

You can edit DB connecons at any me once they have been created by selecng the respec-

ve connecon in the table and clicking on the <Edit> buon.

Use the <Remove> buon to delete the selected DB connecon.

Note

If a database connecon is to be deleted that is already assigned to one or more

modules, a warning appears.

Because SQL modules are linked to a DB connecon in the SQL interface, you

must make sure that the modules do not lose their reference to the connecon.

Otherwise the SQL statements will have no eect.

Issue 1.3 13

ibaPDA- Interface- MySQL Conguraon and engineering ibaPDA

4.2.1 MySQL/MariaDBDBconnecon

Databasetype

In the drop- down menu, select your database type. Here it is MySQL, MariaDB.

Timeout

Here you can specify a value for the meout in seconds for establishing the connecon. If the

me set here is exceeded, ibaPDA aborts the connecon aempt and displays an error mes-

sage.

Note that once the connecon is made, each command sent to the database has an addional

meout, which can be set by the applicaon that makes use of it. There is no generic congura-

on for the command meout.

Conneconname

This line contains the name of the database connecon. The name is automacally formed and

entered according to the scheme username@database as soon as you have lled in the elds

below. You can also overwrite the connecon name.

You can use this name to idenfy the database connecon in the overview table at the interface

level. The connecon name is there in the DB name column.

Serveraddress

Enter the IP address or the host name of the database server.

Port

The DB server communicates via this port. Usually, you can keep the default port 3306.

If another port is required, you can enter this port here.

Username/Password

Enter here the required login data for the database. If necessary, inquire the correct data from

your DB administrator.

Database

Enter the database here. When you have entered the server address, authencaon or user

name and password, the drop- down list shows all available databases for the connecon and

you can select one.

<Testconnecon>

ibaPDA checks the access to the selected database on the set DB server. If the connecon is suc-

cessful, the message includes the version of the database.

14 Issue 1.3

Conguraon and engineering ibaPDA ibaPDA- Interface- MySQL

4.3 SQLmodules

For the SQL modules under the SQL interface, there are input modules and output modules.

An SQL module is either a reading module with an SQL query or a wring module with an SQL

command. That is why the terms SQL query module and SQL command module are used in the

following text as well.

A module cannot be both simultaneously. That is why there is also no SQL module that can be

seen simultaneously in the I/O Manager on both tabs Inputs and Outputs.

However, the modules are very similar in their structure and components.

The following applies to all SQL modules:

■ The SQL modules have a reference to a database connecon in the SQL interface.

■ The SQL modules contain an SQL statement (query or command).

■ The SQL modules oer an assignment table for parameters used in the SQL statements.

■ The SQL modules can work cyclically or triggered.

4.3.1 AddinganSQLmodule

1. Click on the blue command Click to add module… located under each data interface in the

Inputs or Outputs tab.

2. Select the desired module type in the dialog box and assign a name via the input eld if re-

quired.

3. Conrm the selecon with <OK>.

Table 3: Add an SQL query module (le) or SQL command module (right)

Issue 1.3 15

ibaPDA- Interface- MySQL Conguraon and engineering ibaPDA

4.3.2 Generalmodulesengs

Table 4: General module sengs for input modules (le) and output modules (right)

Basicsengs

ModuleType(informaononly)

Indicates the type of the current module.

Locked

You can lock a module to avoid unintenonal or unauthorized changing of the module sengs.

Enabled

Enable the module to record signals.

Name

You can enter a name for the module here.

ModuleNo.

This internal reference number of the module determines the order of the modules in the signal

tree of ibaPDA client and ibaAnalyzer.

Timebase

All signals of the module are sampled on this mebase.

Usemodulenameasprex

This opon puts the module name in front of the signal names.

Modulestructure(onlyinputmodules)

No.ofanalogsignals/digitalsignals

Dene the number of congurable analog and digital signals in the signal tables. The default

value is 32 for each. The maximum value is 1000. The signal tables are adjusted accordingly.

16 Issue 1.3

Conguraon and engineering ibaPDA ibaPDA- Interface- MySQL

Trigger

Updateme

You can use the update me in milliseconds to determine the following:

■ In Cyclic send mode: execuon interval of the SQL statements

■ In the On change and On trigger mode: dead me aer execung a statement

Send mode

Set here when to execute the SQL statements (queries and commands).

■ Cyclic: The SQL statement is executed cyclically at the set update me.

■ On change: When selecng this mode, the line Trigger signal appears. Aer a triggering, all

other triggering value changes are ignored unl the update me has expired. Only then can

the SQL statement be executed again.

■ On trigger: When selecng this mode, the line Trigger signal appears. Aer a triggering, all

other triggering triggers are ignored unl the update me has expired. Only then can the SQL

statement be executed again.

Triggersignal(forOnchangeorOntriggersendmode)

■ In the On change send mode: You can select any analog or digital signal here to serve as the

trigger. In case of a change to the signal value, the SQL statement is executed.

■ In the On trigger send mode: You can select any digital signal here to serve as the trigger. In

case of a rising edge of the signal, the SQL statement is executed.

Queuesize

The queue serves as a buer for SQL statements in the following cases:

■ The database connecon is interrupted.

■ The execuon of an SQL statement takes long while new SQL statements are already trig-

gered in ibaPDA before the execuon has been nished.

Use the queue size to determine how many SQL statements are intermediately stored as a maxi-

mum. If this number is exceeded, the oldest SQL statements are deleted.

■ With input modules, the queue is 1 by default:

Because the results of an SQL query are mapped to signals, in most cases it is not useful

to catch up on queries that were not executed due to an interrupted connecon. The SQL

queries buered in the queue would then be executed in quick succession and the results

transmied to the signals. With the queue size 1, only the last triggered SQL query is always

kept available.

Issue 1.3 17

ibaPDA- Interface- MySQL Conguraon and engineering ibaPDA

■ With output modules, the queue size is 1000 by default:

Producon data, for example, can be wrien via SQL commands. It is important here that

these are also buered and made up for in case of a connecon breakdown.

Commandmeout

By seng the command meout, you determine the maximum me for a statement to be exe-

cuted. If the query or command is not completed before the command meout has elapsed, the

execuon is aborted. The default sengs of query and command dier:

■ SQL query: 5 s

■ SQL command: 30 s

4.3.3 Buer

In output direcon the interface uses a memory buer and addionally a le buer that can be

enabled oponally.

Data to be sent to the target system always passes through the internal ibaPDA memory buer.

If the connecon to the target system exists, the data is sent there immediately. If the connec-

on is lost, or the data cannot be sent out fast enough, the data is temporarily stored in the

memory buer. The memory buer is located in the RAM of the ibaPDA computer and is there-

fore limited and volale. If, for example, the acquision is restarted, the buered data will be

lost. If the memory buer grows beyond the congured size during ongoing acquision, the old-

est values are deleted and thus lost.

To improve this, a le buer can addionally be enabled, which can buer much larger amounts

of data. The data is stored in les in a directory on a local drive of the ibaPDA computer. When

the le buer is enabled, data is transferred from the overowing memory buer to the le buf-

fer. If the acquision is stopped or restarted (e.g. by applying a modied I/O conguraon), data

that may be in the memory buer at this me is also transferred to the le buer.

Aer reconnecng to the target system, the oldest data is always sent rst. Newer values are

added to the buer in the meanme. If there is sll buered data in the le buer when the

acquision is started, it will be handled and processed in the same way.

In terms of SQL statements this means that the statements are buered according to the con-

guraon which was valid at the me of buering. If you change the SQL statement during an

ongoing buering (change and apply in the I/O Manager), the system will sll execute the old

statements as generated at the me of buering aer the connecon has been reestablished.

You congure the buering on the Buer tab in the Outputs secon of the SQL interface.

18 Issue 1.3

Conguraon and engineering ibaPDA ibaPDA- Interface- MySQL

Memorybuer

The memory buer is always enabled. You cannot deacvate it because data to be transmied

always passes through the buer before being forwarded to the target system.

Maximumsize

Enter here the maximum total size for items buered in memory. If the maximum size is exceed-

ed, there are 2 opons:

■ When le buering is disabled, the oldest item in memory is deleted (and is lost forever).

■ When le buering is enabled, the oldest part of the buer memory is moved to a buer le.

Periodicallypersistmemorybuerevery…s

You can only enable this opon if le buering is enabled. If the opon is enabled, the enre

memory buer is periodically swapped to a buer le.

Enter a duraon aer which the memory buer is periodically stored. It must be between 10 s

and 600 s.

With this opon you can ensure that as lile data as possible is lost in case of a system failure.

Currentmemoryconguraon

Display of the approximate me period that can be temporarily stored in the memory buer

with the congured sengs. Specicaon in d.hh:mm:ss.

Filebuer

Uselebuering

By default, the le buer is not used. Here you can enable le buering.

Issue 1.3 19

ibaPDA- Interface- MySQL Conguraon and engineering ibaPDA

Currentleconguraon

Display of the approximate me period that can be temporarily stored in the le buer with the

congured sengs. Specicaon in d.hh:mm:ss.

Filestoragepath

In the File storage path eld you can select a locaon for the les. You can enter the directory

directly into the text eld, or select it via the browse buon <…>. The congured le directory

must be located on a local hard disk of the ibaPDA server computer.

You can use the same le directory for several data stores because the buer les of a data

store have a unique name. Files from dierent data stores can thus be disnguished by their

name.

Maximumsize

You can congure the maximum total size of the buer les of an SQL command module. The

buer les themselves have the le extension .buf, the index le for managing the buer les

has the extension .info. The maximum size is the total size of all these les. If the maximum buf-

fer size is exceeded, the oldest buer le is deleted.

Otherbuersengs

Maximumme

Stored data older than the maximum me will not be transferred to the target system. Files old-

er than the maximum me can be deleted. You may enter any value between 1 and 1000 hours.

Maximumqueuesize

Here you can set the limit for the number of buered SQL statements. Depending on the appli-

caon it may be more reasonable to give a limited number of statements instead of a me limit.

However, the buer size is determining.

Memorybuerdiagnoscs/Filebuerdiagnoscs

Lastitemremoved

Indicates when the last item was taken from this part of the buer.

Filllevel

The ll level indicates what percentage of the buer size is currently lled with buered data.

Unprocessedlevel

Items transferred to the target system are not deleted immediately in the le buer. Only when

a buer le is completely read, it is deleted. Therefore, it is possible that only a part of a buer

le contains data that has not yet been transferred. The ll level refers to the exisng buer

les, while the "unprocessed level" indicates the percentage of data in the le buer that has

not yet been transferred.

Tip

If you congure a diagnosc module for an SQL command module in the I/O con-

guraon, you can monitor and record the ll level values of the memory buer

and le buer.

See also ì Diagnosc modules, page 30.

20 Issue 1.3

Conguraon and engineering ibaPDA ibaPDA- Interface- MySQL

4.4 SQL statements

SQL statements used for SQL queries are primarily SELECT statements and for SQL commands

INSERT, UPDATE or DELETE statements.

Enter the SQL statement in the le window of the SQL query tab or SQL command tab of the

module. You can write the statement directly in the window or paste using copy & paste.

Only use statements that the connected database server can execute. The syntax of the SQL

statement must be correct and wrien in the SQL dialect of the database system. ibaPDA does

not oer any plausibility check of the SQL statement!

ibaPDA highlights some syntax elements in color to provide support:

■ Keywords/statements/operaons: blue

■ Comments: green

■ Oponal SQL parameters: magenta

The highlighng diers for each DB system type because every SQL dialect has dierent key-

words or usage forms of parameters. For example, parameters are marked with a colon ":" for

Oracle and they are marked with an at sign "@" for SQL Server.

One SQL statement is permied per module.

A disncon is made between SQL statements with and without parameters. With an SQL query

without parameters, you get the current values of the respecve queried columns from the da-

tabase as the result and assign these values directly to the module signals.

You can use parameters to dynamically link the query to certain condions in order to create a

dierenated query.

Page is loading ...

IBA ibaPDA-Interface-MySQL Owner's manual

Brand: IBA

Model: ibaPDA-Interface-MySQL

Type: Owner's manual

Download PDF

report

IBA ibaPDA-Interface-MySQL Owner's manual

IBA ibaPDA-Interface-MySQL Owner's manual

Related papers