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 |
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.
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.
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.
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:
The connection panel allows to specify whether the connection to the database is regularly checked and how often this check is performed.
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.
The commands marked with * work only if a table is active (i.e. a previous request has returned a valid result).
Connects to a 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.
Terminates the communication with the database and closes the connection.
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.
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.
To get the content of a table.
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.
To send a SQL command to the current database.
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.
Sets the current record index.
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.
Reads a field from the current record.
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.
[Enum] Status of the last request
[String] Last error message, when Status value is Error.
[String] List of the databases (ODBC Data Sources) available on the system.
[String] List of the names of the tables present into the current database.
[String] List of the names of the fields of the current table. (Names of the columns).
[Integer] Count of fields in each record of the current table. (Count of columns)
[Integer] Count of records of the current record set returned by the last SQL request. (Count of rows)
[Integer] Current Record index. (Row).
[Integer] Current Field index. (Column)
[String] Content of the current selected cell of the current table.