gdatabase for mysql user guide - saphir for mysql user manual.pdf · gdatabase for mysql toolkit is...

P.1.1

GDataBase for MySQL

USER GUIDE

P.1

Table of contents

1. Foreword ......................................................................................................... 3

1.1 Overview ............................................................................................................. 3

1.2 Features ............................................................................................................. 3

1.3 Requirement ...................................................................................................... 4

2. LabVIEW components ..................................................................................... 5

2.1 Function palette ................................................................................................. 5

2.1.1 Open database ............................................................................................... 5

2.1.2 Close database .............................................................................................. 6

2.1.3 Create database ............................................................................................ 6

2.1.4 Use database ................................................................................................. 7

2.1.5 Drop database ............................................................................................... 7

2.1.6 Create table ................................................................................................... 8

2.1.7 Insert .............................................................................................................. 9

2.1.8 Update .......................................................................................................... 10

2.1.9 Select ........................................................................................................... 10

2.1.10 Drop table .................................................................................................... 11

2.2 Advanced palette ............................................................................................. 12

2.2.1 Execute SQL query (1 resultset) ................................................................. 12

2.2.2 Execute SQL query (n resultsets) ............................................................... 13

2.2.3 Multi Statements option ............................................................................. 13

2.2.4 Begin transaction ........................................................................................ 14

2.2.5 Commit transaction .................................................................................... 14

2.2.6 Rollback transaction ................................................................................... 15

2.3 Utility palette.................................................................................................... 15

2.3.1 Get table information .................................................................................. 15

2.3.2 Get columns information ............................................................................ 16

2.3.3 Ping server................................................................................................... 17

2.3.4 Get server statistics .................................................................................... 17

2.3.5 Timestamp to date string ........................................................................... 17

2.3.6 Date string to timestamp ............................................................................ 18

2.3.7 UTF8 to string .............................................................................................. 19

2.3.8 String to UTF8 ............................................................................................. 19

2.3.9 Timeout property ......................................................................................... 19

P.2

2.4 Examples ......................................................................................................... 20

2.5 Tools ................................................................................................................. 22

3. Activation ...................................................................................................... 23

4. Support ......................................................................................................... 24

P.3

1. Foreword

1.1 Overview

GDataBase for MySQL toolkit is an add-on to NI LabVIEW that adds capabilities

to connect MySQL databases from any LabVIEW program.

GDataBase for MySQL is compatible with the LabVIEW Real-Time Module.

With GDataBase for MySQL you can easily connect your test and measurement

applications to "the world's most popular open source database".

GDataBase for MySQL is really easy to install. It does not require additional

component to be installed but the toolkit itself.

1.2 Features

GDataBase for MySQL toolkit supports the following features:

Easy to use function to interact with MySQL database

Advanced SQL queries

Database communication tools allowing quick communication checking and

database content display

The toolkit also includes simple request and performance examples

Figure 1: GDataBase for MySQL use case

http://www.mysql.com/

P.4

1.3 Requirement

LabVIEW version : 2010 or later

Operating System : Windows

Compatible with the LabVIEW Real-Time Module

VI Package Manager from JKI - Community Edition For installation process

At least one MySQL database server version 5.x

(Note that GDataBase for MySQL does not provide any component of MySQL)

P.5

2. LabVIEW components

2.1 Function palette

2.1.1 Open database

Figure 3: Open.vi

This VI attempts to establish a connection to a MySQL database engine running on

host. This VI must complete successfully before you can execute any other API VI.

host: the address of the MySQL database engine that hosts the database to use.

If the database is local, you have to set this parameter to localhost

port: the number of the TCP/IP port that the server monitors for connections.

The default value is 3306.

Figure 2: GDataBase

P.6

clientAuthentification:

username: the name of the user account to use to access the database.

password: the password associated with the user account use to access

the database. You might not need to specify a password.

database: the name of the database to use.

maxPacketSize: the maximum communication packet size (in bytes). The

default value is 10 Mega bytes.

timeout-ms: the maximum time to wait in milliseconds to connect to the MySQL

server successfully. the default value is 20000 ms.

error in: describes error conditions that occur before this VI runs. The default is

no error. If an error occurred before this VI runs, the VI passes the «error in»

value to error out. This VI runs normally only if no error occurred before this VI

runs.

MySQL out: reference to a MySQL Connection object.

error out: contains error information. If «error in» indicates that an error

occurred before this VI ran, «error out» contains the same error information.

Otherwise, it describes the error status that this VI produces. Right-click the

«error out» front panel indicator and select Explain Error from the shortcut

menu for more information about the error.

2.1.2 Close database

Figure 4: close.vi

This VI closes a connection to a MySQL database engine previously opened.

MySQL in: reference to the MySQL Connection object.




runs.






2.1.3 Create database

Figure 5: CreateDatabase.vi

This VI allow to create a database if not exist.

P.7


databaseName: name of the database to create.

dropDatabaseIfExists?: add the "DROP IF EXISTS" option in the creation syntax.

The default value is FALSE.




runs.

MySQL out: reference to the MySQL Connection object.






2.1.4 Use database

Figure 6: UseDatabase.vi

This VI allow to select a database before execute some queries on it.


databaseName: name of the database to use for executing queries.




runs.







2.1.5 Drop database

Figure 7: DropDatabase.vi

This VI executes a drop database query.

P.8


databaseName: indicates the name of the database to drop.

ifExists: add the "IF EXISTS" option in the query. The default value is FALSE.




runs.







2.1.6 Create table

Figure 8: CreateTable.vi

This VI defines and executes a SQL table creation.


tableName: indicates the name of the table to create.

tableDefinition: array of clusters where each cluster configure one table's field:

fieldDefinition: cluster which define a field configuration:

fieldName: name of the field.

fieldType: MySQL data type of the field.

autoIncrement: add the AUTO_INCREMENT option on the field.

The default value is FALSE. Warning: only one field could be

configured with this option. If more than one is configured with this

option, an error is generated

primaryKey: indicates if the field is a part of the primary key of the

table. The default value is FALSE.

notNull: indicates if the field could have NULL value. The default

value is FALSE.

uniqueIndex: indicates to the server to create a unique index for

this field. The default field is FALSE.

binary: add the BINARY option on the field. The default value is

FALSE. Warning: only CHAR and VARCHAR data type could be

configured this option. If another type implements this option, an

error is generated.

zeroFill: add the ZEROFILL option on the field. The default value is

FALSE. Warning: only INTEGER data types could be configured this

P.9

option. If another type implements this option, an error is

generated.

defaultValue: indicates the default value for the field in string

type. The default value is empty.

fieldProperties: contains the properties to configure some field

type. For VARCHAR type, this field contains an integer which

represents the length (in bytes) of the field. For ENUM type, this

field contains the different values of the enumeration separated by

";" character. The default value is empty.

dropTableIfExists?: add the "DROP IF EXISTS" option in the

creation syntax. The default value is FALSE.

error in: describes error conditions that occur before this VI runs.

The default is no error. If an error occurred before this VI runs, the

VI passes the «error in» value to error out. This VI runs normally

only if no error occurred before this VI runs.


createTableQuery: returns the executed SQL queries.

error out: contains error information. If «error in» indicates that

an error occurred before this VI ran, «error out» contains the

same error information. Otherwise, it describes the error status

that this VI produces. Right-click the «error out» front panel

indicator and select Explain Error from the shortcut menu for

more information about the error.

2.1.7 Insert

Figure 9: Insert.vi

. This VI formats and executes an INSERT statement.


tableName: name of the table where the configured record would be inserted.

The default value is empty. If empty, the table name is the label of clusterData

input.

clusterData: cluster of data which represents fields of table where the record

would be inserted. Every elements of the cluster must correspond to the

name of the table.

numericFormat: number of digits for decimal values. The default value is "%f".




runs.


P.10

insertQuery: returns the INSERT SQL query.






2.1.8 Update

Figure 10: Update.vi

This VI formats and executes an UPDATE statement.


tableName: name of the table where the configured record would be updated.

The default value is empty. If empty, the table name is the label of clusterData

input.

clusterData: cluster of data which represents fields of table where the record

would be updated. Every elements of the cluster must correspond to the

name of the table.

whereClause: define the WHERE clause of the SQL query. The default value is

empty.

numericFormat: number of digits for decimal values. The default value is "%f".




runs.


insertQuery: returns the UPDATE SQL query.






2.1.9 Select

Figure 11: Select.vi

P.11

This VI formats and executes an SELECT statement.


tableName: name of the table where select some records.

fieldsToSelect: array of fields name to select in the SQL query. The default

value is an empty array. If empty array, the query will select all field of the table.

whereClause: define the WHERE clause of the SQL query. The default value is

empty.

options: several options to add to the query :

orderbyColumnName: define the ORDER BY column in the SQL query.

The default value is empty.

orderbySortingMode: define the ORDER BY sorting mode (ascending,

descending) in the SQL query. The default value is ASC (ascending).

LIMIT index: define the first index of the resultset to return. The default

value is 0.

LIMIT n: define the number of rows of the resultset to return. The default

value is -1, means return all rows.




runs.


selectQuery: returns the SELECT SQL query.

fieldsNames: array of strings that contains the name of the selected columns.

results: array of strings that contains the result of the query.






2.1.10 Drop table

Figure 12: DropTable.vi

This VI executes a drop table query on a database.


tableName: indicates the name of the table to drops.

ifExists: add the "IF EXISTS" option in the query. The default value is FALSE.



P.12


runs.







2.2 Advanced palette

Figure 13: Advanced palette

2.2.1 Execute SQL query (1 resultset)

Figure 14: Query.vi

This VI executes an SQL query.


query: SQL query to evaluate.

resultsetIndex: index of the resultset to return output. It's useful in case of the

query return more than one resultset. The value -1 returns the last resultset of

the query. The default value is -1.



value to «error out». This VI runs normally only if no error occurred before this

VI runs.


fieldsNames: array of strings that contains the name of the selected columns.

results: array of strings that contains the result of the query.



P.13




2.2.2 Execute SQL query (n resultsets)

Figure 15: QueryAllResultSet.vi

This VI executes an SQL query.


query: SQL query to evaluate.




runs.


fieldsNames: array of cluster of strings that contains the name of the selected

columns of each resultset.

results: array of cluster of strings that contains the result of the query of each

resultset.






2.2.3 Multi Statements option

Figure 16: MultiStatementOption.vi

This VI enables/disables the multiple statements option for Query.vi.


multiStatementOption: option of the Query.vi. Set 0 to allow multiple

statements on Query.vi. Set 1 to inactive it.




runs.

P.14







2.2.4 Begin transaction

Figure 17: BeginTransaction.vi

This VI creates a new transaction. Such transactions usually persist until the next

COMMIT or ROLLBACK command. But a transaction wills also ROLLBACK if the

database is closed or if an error occurs.





runs.







2.2.5 Commit transaction

Figure 18: CommitTransaction.vi

This VI commits all modifications that occurred on the database since the last call of

the method BeginTransaction.





runs.


P.15






2.2.6 Rollback transaction

Figure 19: RollbackTransaction.vi

This VI cancels all modifications that occurred on the database since the last call of the

method BeginTransaction.





runs.







2.3 Utility palette

Figure 20: Utility palette

2.3.1 Get table information

Figure 21: ShowTableStatus.vi

P.16

This VI provides information about each non-temporary table.


Database: the name of the database to process. Note: if empty the default

database define during the open is used.

options: information to retrieve.




runs.


table information: information about each non-temporary table.






2.3.2 Get columns information

Figure 22: ShowColumns.vi

This VI provides information about each column of a given table.


database: the name of the database to process. Note: if empty the default

database define during the open is used.

table: the name of the table to process.

option (All): information to retrieve.




runs.

column information: information about each column






P.17

2.3.3 Ping server

Figure 23: ping.vi

This VI tests the connection and reset the connection inactivity counter in the MySQL

server.





runs.







2.3.4 Get server statistics

Figure 24: Statistics.vi

This VI returns some MySQL server statistics.





runs.


statistics: MySQL server statistics.






2.3.5 Timestamp to date string

P.18

Figure 25: TimestampToDateString.vi

This VI formats a timestamp in string from SQL type.

timestamp in: timestamp to format.

dateType: SQL date type.

syntaxDateType: add quotes configuration.




runs.

encodingString: timestamp in string formatted.






2.3.6 Date string to timestamp

Figure 26: DateStringToTimestamp.vi

This VI formats a string in timestamp from SQL type.

dateString: string containing timestamp to decode.

dateType: SQL date type.




runs.

timestamp out: timestamp formatted.






P.19

2.3.7 UTF8 to string

Figure 27: UTF8ToString.vi

This VI converts a UTF-8 encoding string in a LabVIEW string.

stringUTF8: UTF-8 encoding string




runs.

stringLV: string LabVIEW.






2.3.8 String to UTF8

Figure 28: StringToUTF8.vi

This VI converts a LabVIEW string in a UTF-8 encoding string.

stringLV: string LabVIEW.




runs.

stringUTF8: UTF-8 encoding string






2.3.9 Timeout property

Figure 29: Timeout property node

P.20

This property node gets/sets the timeout in milliseconds.

Timeout: Maximum time to access to the database

2.4 Examples

GDataBase for MySQL toolkit is provided with comprehensive examples demonstrating

how to access MySQL database. You can find them browsing "NI Example Finder".

Simple request to MySQL database: This example demonstrates how you can

open/create, insert and retrieve data from MySQL database.

Figure 30: Simple request to MySQL database example

P.21

INSERT to MySQL databaseBenchmark: This example allows you to make INSERT

performance test into MySQL database.

Figure 31: INSERT to MySQL database Benchmark

P.22

2.5 Tools

GDataBase for MySQL is provided with a useful tool allowing to quickly test the

communication with any MySQL databases. You can find it by the menu:Tools >>

SAPHIR >> GDataBase for MySQL >> MySQL database connection test...

Figure 32: Database connection tool

To test connection:

1. Fill-in the following fields:

MySQL server host: MySQL server computer name or IP address

Port: TCP port used by MySQLServer connections

User: Login name

Password: User password

2.

3. Navigate into MySQL server databases

P.23

3. Activation After the download and installation of GDataBase for MySQL toolkit, an activation

window will pop up at LabVIEW launching. You can also go to help > Activate Add-ons,

ll get the following window. Follow the steps of the add-ons activation.

Figure 33: Third Party Add-on

You can try GDataBase for MySQL during 30 days for free.

VIs will become broken. To activate the toolkit after this period, simply go to Help menu

and select Activate Add-

P.24

4. Support License purchase provide you 90 days of e-mail support during activation and initial

use of the toolkit.

Anyway you can access community support using .

It opens the following SAPHIR community page:

http://decibel.ni.com/content/groups/saphir-toolkit

http://decibel.ni.com/content/groups/saphir-toolkit

P.25

INDEX

Figure 1: GDataBase for MySQL use case ........................................................................... 3

Figure 3: Open.vi .................................................................................................................... 5

......................................................................... 5

Figure 4: close.vi .................................................................................................................... 6

Figure 5: CreateDatabase.vi .................................................................................................. 6

Figure 6: UseDatabase.vi ...................................................................................................... 7

Figure 7: DropDatabase.vi ..................................................................................................... 7

Figure 8: CreateTable.vi ........................................................................................................ 8

Figure 9: Insert.vi ................................................................................................................... 9

Figure 10: Update.vi ............................................................................................................. 10

Figure 11: Select.vi .............................................................................................................. 10

Figure 12: DropTable.vi ....................................................................................................... 11

Figure 13: Advanced palette ................................................................................................ 12

Figure 14: Query.vi ............................................................................................................... 12

Figure 15: QueryAllResultSet.vi .......................................................................................... 13

Figure 16: MultiStatementOption.vi .................................................................................... 13

Figure 17: BeginTransaction.vi ........................................................................................... 14

Figure 18: CommitTransaction.vi ....................................................................................... 14

Figure 19: RollbackTransaction.vi ...................................................................................... 15

Figure 20: Utility palette ...................................................................................................... 15

Figure 21: ShowTableStatus.vi ............................................................................................ 15

Figure 22: ShowColumns.vi ................................................................................................. 16

Figure 23: ping.vi .................................................................................................................. 17

Figure 24: Statistics.vi ......................................................................................................... 17

Figure 25: TimestampToDateString.vi ................................................................................ 18

Figure 26: DateStringToTimestamp.vi ................................................................................ 18

Figure 27: UTF8ToString.vi .................................................................................................. 19

Figure 28: StringToUTF8.vi .................................................................................................. 19

Figure 29: Timeout property node ...................................................................................... 19

Figure 30: Simple request to MySQL database example .................................................. 20

Figure 31: INSERT to MySQL database Benchmark ......................................................... 21

Figure 32: Database connection tool .................................................................................. 22

Figure 33: Third Party Add-on ............................................................................................. 23

Other add-ons that could be helpful

http://www.saphir.fr/en/produits/gmodbus-over-tcp-6.html

http://www.saphir.fr/en/produits/gmodbus-over-serial-line-8.html

http://www.saphir.fr/en/produits/gdatabase-for-sqlite-5.html

http://www.saphir.fr/en/produits/vibox---probes-10.html

http://www.saphir.fr/en/produits/vibox---xcontrols-9.html

gdatabase for mysql user guide - saphir for mysql user manual.pdf · gdatabase for mysql toolkit is...

Documents