Transcription
ANV Database Toolkitby ANV s.r.o.User ManualFor more of our products, see: o contact our online support, please send an email to: support@anv-tech.com
Table of ContentsAbout This Manual . 4Abbreviations . 4Requirements . 4Installation . 4Licensing and activation . 5Toolkit Description. 7Introduction . 7Query features . 7Configuration . 7Configure Data Storage. 7Configuration of databases . 9MS SQL . 10Access . 10MySQL . 11Database Toolkit Configuration . 11API Manager . 12Configuring API VIs . 13Select Query . 14Insert Query . 15Update Query . 15Delete Query . 15Data types . 15Filtering . 16Database Tree . 17Viewer . 18Testing . 18API VIs . 19Sample Project . 22Configuration Files. 22Configuration Scope . 22Configuration File Types . 22DBT paths.ini . 22DBT Configuration.ini . 23DBT Storage Cfg.ini . 23
Configuration Files Location . 23DBT paths.ini . 23DBT Configuration.ini . 23DBT Storage Cfg.ini . 23Configuration Files Content. 23DBT paths.ini . 23DBT Configuration.ini . 23DBT Storage Cfg.ini . 24Distributing applications . 24Appendix A: List of available databases . 24Appendix B: Available aggregations . 25Appendix C: Available filter comparisons . 25
About This ManualThis manual serves as thorough introduction for usage of ANV Database Toolkit. Users of the toolkit arerecommended to carefully inspect this manual before using the toolkit.Information in this manual is accurate for the current version of toolkit to the best knowledge of theauthors. We reserve rights for any errors which might appear in this manual.This document is property of ANV s.r.o. All rights for the document are reserved.AbbreviationsAPI – Application programming interfaceDBT – Database ToolkitVIPM – VI Package ManagerRequirementsThese are the minimum software requirements for proper functionality of toolkit:Windows 7, 8, 10 32 or 64 bit.LabVIEW 2015 SP 1 or higher.Following 3rd party VI packages available through JKI VI package manager are used by the toolkit:#TBDAll packages mentioned above are included in toolkit installer by default.InstallationToolkit is installed as package via JKI VI package manager. You can download it either through VIPM orfrom our website. After installation, 30-day evaluation period begins. Please see our website fordownload links.DBT also uses Tree API toolkit, which needs to be downloaded separately from our package. Pleasedownload it from: Tree-API/tdp/3690049?profile.language en or use direct link for the package 105/1128/46/ni tree-1.10.1.54.vip.During the installation/un-installation process, both LabVIEW and VIPM should be launched withadministrator rights, otherwise installation may not work.4
Licensing and activationIMPORTANT: During activation of the toolkit, it is necessary that LabVIEW is run with administrator rights.Otherwise, activation process uses your license without activating the toolkit.ANV DBT is available with single, development licensing option. After the toolkit is installed through VIPM,it comes with 30-day evaluation period. Note that with evaluation version of DBT, user cannot buildLabVIEW executables – this requires activation of the toolkit. You will be prompted about license statuswhen you launch LabVIEW, unless your license is already activated.LabVIEW third party Add-on windowTo activate license, either click Activate Add-ons button, or use LabVIEW menu. Select Help ActivateAdd-ons from LabVIEW Toolbar. Dialog box with the list of available toolkits appears. Select ANV DatabaseToolkit from the list and follow the activation wizard in order to activate toolkit.You have two options to activate your license – either automatically or manually. Automatic activation isdone online. You enter License ID and Password you received when you purchased license intoappropriate cells and click activate.Manual activation is used, when your machine cannot connect to the internet. When you choose manualactivation, user codes appear in dialog. On another machine, open browser and accesshttps://secure.softwarekey.com/solo/unlock. Enter License ID and password that you received when youpurchased your license and submit them. Then, enter user codes from the dialog of the machine, whereyou want to activate the license, into appropriate columns. You will then receive activation code, which5
you may enter below user code fields inside “Activation code 1” cell. Click next. Your license should beactivated now.IMPORTANT: Make sure that user codes you entered online are correct and do not cancel activation dialoguntil you enter activation code. Your license is used when you enter it online, so if you entered incorrectuser codes online, or you cancelled the dialog, you will not be able to activate workstation with the license.Automatic license activation dialogManual license activation dialog6
Toolkit DescriptionIntroductionANV Database Toolkit (ANV DBT) is a toolkit, which helps developers design LabVIEW API for queryingvarious databases. It allows to create VIs which can be used as API with the help of graphical user interface.When using these VIs, toolkit handles connection with the database, thus relieving developers of thisburden in their applications.Query featuresANV DBT allows developers to create various queries, which can handle 4 main query types – SELECT,INSERT, UPDATE and DELETE, so various types of CRUD (CREATE, READ, UPDATE, DELETE) applicationscould be easily created.SELECT queries allow to create common SELECT SQL statements. Single query can perform selection ofcolumns from one or more tables. When several tables are used, JOIN statements are createdautomatically when possible from the database relations.Standard clauses, such as WHERE, HAVING, GROUP BY and ORDER BY are available for configuration inSelect queries.INSERT queries handle inserting of data for specific table in the database. One or more columns from atable can be selected for this query.UPDATE serve the purpose of updating existing columns in a single table with specific values. To updateonly specific values, WHERE filters can be configured.DELETE clause allow to remove existing table records from a single table. WHERE filters specify, whichrecords are to be removed from the table.ConfigurationToolkit has the following menu options:1)2)3)4)Configure Data StorageDatabase Toolkit ConfigurationAPI ManagerManualConfigure Data StorageIn order to use one of available databases (See Appendix A, list of available databases), configurationneeds to be performed for selected database as the first step. This configuration is saved and could bedistributed with the projects, thus making it unnecessary to perform additional configuration inLabVIEW afterwards.Note that configuration is project based. If more than one LabVIEW project is loaded in memory, projectselector will be launched, in order to select for which of the loaded projects is the configuration beingcreated.7
When you would like to add new database configuration, just start type new alias into DB Alias control.Configuration of particular database depends on database alias thus it’s not possible to change it foralready created database configuration.Example of configuration window when configuring connection to MS SQL database.Example of configuration window when configuring connection to SQLite1. Configuration Storage – Path to storage configuration folder.2. DB Alias – Database Alias selector. You can write here new one and configure than.8
3.4.5.6.7.8.9.10.11.12.13.14.15.Connection String – Connection string to the database (LabVIEW native DB specific).UserID (optional) – (LabVIEW native DB specific).Password (optional) – (LabVIEW native DB specific).Save Button – Save currently displayed database configuration.Save and Close Button – Save currently displayed database configuration and closeconfiguration window.Delete Button – Delete currently displayed database configuration.Cancel Button – Close configuration window without saving.Edit Button – Call Data Link Properties window for configuration of connection string. No UDLfile is created (LabVIEW native DB specific).Test Connection Button – Try to connect to the configured database. Test of connection isexecuted also when configuration window is launched. If connection failed, connection stringwill be shown as red.Active? Checkbox – Enable or disable access to configured database for rest of toolkit and yourapplication.DB type Selector – Specify type of database to be configured.SQLite File Path – Specify path to the SQLite database file (SQLite DB specific).Use Relative Path? – this option is available for those databases, which require absolute path todatabase to be specified (like SQLite or MS Access). When this option is active, then absolutepath will be analyzed whether it contains some known system path – user documents, userdesktop, project root folder path, etc. This know system path will be stored as mask (template),so target file could be found on another PC based on this mask. For example, if target file path is“C:\Users\Dummy User\Documents\database.sqlite”, then it will be stored as “%UserDocuments%\database.sqlite”. Then, if configuration file is read from another Windows account(on different PC) by user “Dummy User 2”, file path will be restored as “C:\Users\Dummy User2\Documents\database.sqlite”.Configuration of databasesThis section is intended to describe setup of data link when user selected target database type (viaselector “Select DB type”) and pressed “Edit” button to call system “Data Link Properties” window.9
MS SQLOn the “Provider” tab select “Microsoft OLE DB Provider for SQL Server”. Then, on the “Connection” tabspecify Server Name, Log On information, and select Database from the drop-down list. Also, use button“Test Connection” to verify, that data link is configured properly.AccessOn the “Provider” tab select “Microsoft Office XX Access Database Engine OLE DB Provider” option,where XX – is version of Access Database Engine. Then, on the “Connection” tab specify path todatabase file. Note: this path is not possible to select, one need to copy and paste it manually into “DataSource” field. Then, configure Log On information, and use button “Test Connection” to verify, that datalink is configured properly.10
MySQLOn the “Provider” tab select “Microsoft OLE DB Provider for ODBC Drivers”. Then, on the “Connection”tab select data source name from the drop-down menu, Log On information, and select Database fromthe drop-down list. Also, use button “Test Connection” to verify, that data link is configured properly.Note: MySQL requires to have the following installed/configured. MySQL – install 32bit ODBC Driver, Workbench app, MySQL Server;Open ODBC Data Source Administrator (32bit), verify existence of MySQL ODBC X.XAnsi/Unicode drivers on Drivers Tab;On User DSN tab click ADD and select MySQL UNICODE Driver - OK.Now it should be possible to create data link in LV via Microsoft OLE DB Provider for ODBC Drivers.Database Toolkit ConfigurationDatabase toolkit configuration window is used for configuration of API VI lvlib File Path and loggingfeature.11
Example of Database Toolkit Configuration1. API VI lvlib File Path – Path to the library file which contains scripted user’s APIs.2. Severity Threshold Selector – Specify threshold of logging.3. Save and Close Button – Save currently displayed database configuration and closeconfiguration window.4. Logger Enabled checkbox – enable/disable logging feature.5. Log File Base Path – Specify path where log wiles have to be stored.6. Cancel Button – Close configuration window without saving.API ManagerAfter selecting this option, API Manager UI is launched. Toolkit automatically loads configuration fordatabase which was configured in Data Storage Configuration window and any API previously configuredfor this database.Note that database needs to be set as active in Data Storage Configuration in order to be used by APIManager. If multiple databases are set as active, the one which is stored as first in config file will beused.API Manager has three main windows for working with API VIs – Configurator, Viewer and Testing.Configurator window is used to create, modify and remove API VIs. When a VI from VIs list is selected,configuration is parsed to Query editor window.Viewer is used to display created VIs in a subpanel, without need to manually open these VIs and checkwhether their inputs/outputs were created as desired.Testing window is used for testing of created VIs.12
API Manager with configurator view1.2.3.4.5.Settings – Contains settings for queries.API VIs list – All VIs, which have already been created are displayed here.Query selector – Allows to switch between different query types.New – Adds new VI to VIs list.Discard – When changes are performed upon query, user has either to discard thechanges or save the by using “Generate” button. This is done to prevent accidentallyswitching to another VI with unsaved changes, thus losing all performed changes.6. Delete – Use this button to remove selected VI from VIs list.7. Generate – Generates VI based on query configuration and saves it.8. Query window – this window displays generated SQL query. Note that this querymay contain placeholders, for example “%s”. These placeholders are used toindicate, where will Query use inputs/outputs from VI, where it is used.9. Query editor – this part of configurator is used to create queries in simple mannerwithout the need of manually writing SQL query.10. Different views – switch between pages to use different functionalities of APIManager.11. Database Tree View – Displays hierarchically structure of currently selecteddatabase, with datatypes for each column.Configuring API VIsConfiguration of new or existing VIs is done mainly through Database tree and Query editor ofconfiguration window. Here we provide tutorial for creation of each available query type.Removing and Adding API VIs – To add new API VI, right-click VIs list or select New button and writedown name of new VI. To remove an existing VI, either press delete button when a VI is selected in VIslist, or right-click a VI from the list and select appropriate option. You can also Delete all VIs from list byright-clicking VIs list and selecting delete all option.13
Note that deleting a VI from the list is permanent and irreversible.Adding Columns/Tables to query – to add new column or table to query, double-click or drag and dropan item from Database tree to Query editor. Depending on the query type, column(s)/table might beadded to query.Note that queries except select allow to use only one table in query. If you want to use different tablethan currently selected in these queries, you need to remove all the columns from the query first.To remove column/table from query, either remove it from list of selected columns for the query, or byright-clicking Database tree and selecting appropriate option.Editing column parameters – to edit column parameters in Query editor, edit appropriate column ofrow where the column is located.Editing filters – to edit filters used in query, switch to filtering page in query editor and edit specificwindow from the list. See filtering section of this manual for more information about filter editing.Editing query settings – to edit settings related to query, use settings option from menu. Note thatsettings are available
Configuration Toolkit has the following menu options: 1) Configure Data Storage 2) Database Toolkit Configuration 3) API Manager 4) Manual Configure Data Storage In order to use one of available databases (See Appendix A, list of available databases), configuration needs to be performed for selected database as the first step.
