Name:	Medialon Database
Version:	6.7.2
Available for:	Manager V7 and Manager V6 Pro
Limitation In:
Device Brand:	Medialon
Positrack Compatible:	No
Resources type:	TCP/IP Network

Overview

This MxM allows to access SQL compatible databases. It allows to read, modify, create and send SQL requests to the database.

The use of this MxM requires understanding of SQL language.

SQL is a very common language and there is a lot of documentation available on the internet about it. If you are a beginner, try to make a research with keywords like “SQL basics” or “SQL tutorial”, there are plenty. This document describes the basic SQL statements: SQLRef.htm

This MxM replaces the Medialon SQL MxM which is deprecated. This MxM doesn’t require the installation of the Borland Database Engine and relies only upon ODBC. Projects created with the Medialon SQL MxM must be manually edited to use the Medialon Database as there is no direct match between the commands of both MxMs.

Note that this MxM does not provide commands which directly create or delete records or tables. Standard SQL requests like “INSERT”, “DELETE”, “CREATE”, “DROP” must be used instead.

Note about date/time format: date/time column data are returned in default ODBC format (which is: “yyyy-mm-dd HH:mm:ss:fff”), however SQL commands (like “CONVERT” for Microsoft SQL Server) can be used to format date/time data in the desired format if needed.

MxM Installation

This MXM deals with ODBC. ODBC is the Microsoft supported, industry standard, which defines the method by which Windows tools can access data from a variety of database management systems. Most databases have an ODBC driver that can be used to access them. When the device is created (see next paragraph), the setup dialog will give you access to the ODBC drivers installed.

Of course, the fact that a specific driver is installed does not give you access to the database if the database needs also the installation of a server. For example, Microsoft SQL Server databases need a server to be installed.

Example of database installation: installation of MySQL

• Download the package for Windows from http://www.mysql.com/downloads/mysql–4.0.html
• Download the drivers ODBC from: http://www.mysql.com/downloads/api-myodbc–3.51.html
• After installing both, launch winmysqladmin.exe
• Go in the ODBC dialog (through the setup of a device or by the ODBC icon in the Control Panel) and create an alias for the database that you want to use.

Example on how to use an Excel spreadsheet as a database

• Create a spreadsheet.
• In the first row, enter the names of the fields.
• Select the cells that you have created with the mouse and give a name to this selection ( in the upper left of the spreadsheet ).
• Your first table is created.

Do it for every table and save your file.

In Manager, create a device. Go in the ODBC dialog through the setup of the device. Create an alias using the Excel ODBC driver and select the file that you have just created. Use this alias to connect to this database.

Device Setup

The setup of the device is done through this dialog box:

The setup dialog allows the user to define the default Database, User Name and Password for this device. These are not mandatory and can be specified via the Connect command.

To setup default device properties:

• Select the default database in the database list (Click on the “Setup ODBC” button to manage the installed ODBC drivers if needed) or type the default database name in the Database edit box.
• Type the default User Name
• Type the default Password

The connection panel allows to specify whether the connection to the database is regularly checked and how often this check is performed.

Graphical User Interface

Device Control Panel

This MxM provides a Device Control Panel can be displayed either as a stand alone window or inside a User Screen Contenair object with the “Device Control” Manager command. Note: See the Manager Help menu for details about using this command.

The Device Control Panel allows testing SQL queries against the Database connected to the device (via the “Connect” command).

Type the required SQL query and click on the “Execute Query” button.

Error messages are displayed in the status bar at the bottom of the control panel.

Device Commands

The commands marked with * work only if a table is active (i.e. a previous request has returned a valid result).

Connect Database

Connects to a database.

• Database [String] The database (ODBC Data Source) to connect to.
• User name [String] The login name for this database.
• Password [String] The login password for this database.

Usage: If the Database, User Name or Password are empty, the default values defined in the device setup are used to establish the connection. Note also that with some Databases, it is not necessary to specify the login and the user name which are either set up in the ODBC Data Source definition or implied by the target database engine.

Note: A wizard allows to select one of the existing ODBC DataSource.

Disconnect

Terminates the communication with the database and closes the connection.

Get Database List

Gets the list of Databases (ODBC Data Sources) available on this workstation.

Usage: After this command, the Databases system variable contains the list of the available Databases.

Get Table List

Description Get the list of tables available with the opened Database.

Usage: After this command, the Tables system variable contains the list of the available Databases.

Get Table

To get the content of a table.

• Name [String] Name of the table to get

Usage: The name of the table must be an existing table. The existing tables are listed into the variable Tables exported by the MxM.

Note: A wizard allows to select one of the available tables in the connected database.

SQL Request

To send a SQL command to the current database.

• Request [String] String containing the SQL command
• Return Result [String] Optional return string which will contain the SQL Result Set (Fields are separated by a TAB character (09) and records are separated by CR/LF (0D, 0A) character pair ).

Usage: The request must comply with the SQL syntax.

The record set returned by the command (if any) is stored internally in the device and the Current Record index is set to the first record of the record set (0).The record set is displayed in the Device Control Panel.

Set Current Record *

Sets the current record index.

• Mode [Enum] Record access mode
  • “By Index”: go to the record whose index is specified in the record index parameter
  • “Next Record”: go to the next record in the record set
  • “First Record”: go to the first record of the record set
• Record index [Integer] The index of the record to set (0 for the first record of the record set).

Usage: This command can be used to navigate through the current record set. The RecordIndex and FieldValue system variables are updated after this command is executed.

Get Field *

Reads a field from the current record.

• Mode [Enum] Field access mode
  • “By Index”: gets the field whose index is specified in the index parameter
  • “By Name”: gets the field whose name (column name) is specified in the Field Name parameter
• Field Name [String] Name of the field (column name)
• Field Index [Integer] The index of the field to read (0 for the first field)
• Return String [String] String that will contain in return the content of the field (optional).

Usage: The FieldValue system variable is also set to the content of the specified field after this command is executed.

Note: A wizard allows to select one of the available field names (column names) for the current record.

Device Variables

Status

[Enum] Status of the last request

• “Not Connected”: the database is not connected
• “Ready”: the database is connected and ready to receive requests
• “Error”: there was an error while executing the last command
• “Busy”: the device is executing a command

LastError

[String] Last error message, when Status value is Error.

Databases

[String] List of the databases (ODBC Data Sources) available on the system.

Tables

[String] List of the names of the tables present into the current database.

FieldNames

[String] List of the names of the fields of the current table. (Names of the columns).

FieldCount

[Integer] Count of fields in each record of the current table. (Count of columns)

RecordCount

[Integer] Count of records of the current record set returned by the last SQL request. (Count of rows)

RecordIndex

[Integer] Current Record index. (Row).

FieldIndex

[Integer] Current Field index. (Column)

FieldValue

[String] Content of the current selected cell of the current table.

Revisions

V 1.0.1

• Bug fixed: SQL requests are limited to 1024 characters

V 1.0.2

• Improvement: column widths of the control panel are not reset for each query

V 1.0.3

• Internal Changes

V 1.0.4

• Bug fixed: refresh a big database can freeze Manager during a long time

V 6.0.0

• Modified: Support of Manager V6/Showmaster V2.

V 6.0.1

• Fixed: Setup dialog doesn’t open on the correct screen on a multi-screen configuration.

V 6.0.2

• Fixed: Device variables are not reset after a project load (Requires Manager 6.0.3+).

V 6.0.3

• Modified: Internal Change

V 6.0.4

• Modified: Internal Change

V 6.0.5

• Fixed: The Setup dialog box is not always visible at the device creation time.

V 6.0.6

• Fixed: The Wizard dialog boxes can be displayed outside of the screen where Manager is shown.

V 6.1.0

• Added: Compatibility for Overture CS.

V 6.7.0

• Improved: Support for High DPI displays (require Manager 6.7.0+ or Showmaster Editor 2.7.0+)
• Changed: Compatibility for Manager 6.7.0 and Showmaster Editor 2.7.0