Transcription

TECHNICAL WHITEPAPERRaima Database API for LabVIEWBy Wayne Warren, CTO – December 2013IntroductionRaima Database API for LabVIEW is an interface package to Raima Database Manager (RDM), which is a highperformance database management system optimized for operating systems commonly used within the embeddedmarket. Windows, NI Linux Real-Time and RT VxWorks (on the CompactRIO-9068, -9024 and Single-Board RIO) aresupported in this package. The database engine has been developed to fully utilize multi-core processors andnetworks of embedded computers. It runs with minimal memory and supports both in-memory and on-disk storage.RDM provides Embedded SQL that is suitable for running on embedded computers with requirements to store livestreaming data or sets of configuration parameters.Current version:Raima Database API for LabVIEW 2.0 - supports LabVIEW 2013, Raima Database Manager 12.0Earlier versions:Raima Database API for LabVIEW 1.0 - supports LabVIEW 2011 and 2012, Raima Database Manager 11.0Where to BuyThe Raima Database API for LabVIEW can be downloaded and purchased at the LabVIEW Tools Networkwww.ni.com/labviewtools/raima, or directly from your NI or Raima sales representative.Software Development Kit (SDK) and Distribution Licenses are priced separately.This package is compatible with RDM 12.0 Workgroup Edition for Windows, which may be downloaded from theRaima web site: www.raima.com/downloads.Page 1 of 26 2013 Raima, Inc.

TECHNICAL WHITEPAPERCONTENTS1.Operational Overview .32.Programming with the Database API .32.1 Allocate Handles .32.2 Create or Open a Database .42.3 Populate and Read a Database .52.4 Close a Database .52.5 Share/Use a Database .6Sharing a Database .6Using a Database .62.6 Connecting Real-time Programs to Windows Databases .72.7 Arrays .83.Technical Details .93.1 Windows . 93.2 Real-Time Installation using MAX . 103.3 NI Linux Real-Time . 123.4 CompactRIO VxWorks . 133.5 The Complete API . 133.4 Error Codes . 184.Additional Resources . 264.1 Support . 264.2 LabVIEW Page . 26Page 2

TECHNICAL WHITEPAPER1. OPERATIONAL OVERVIEWThe Database API consists of a set of primitive functions that are generally consistent with the ODBC standard.Databases are defined and manipulated in the SQL language. Each Database API function calls a Native C functionthrough the Call Library Function Node VI. The Native C function will then call the RDM API (the same functionsavailable directly to the C programmer using RDM 12.0 Workgroup Edition).The basic call stack is shown in Figure 1:Figure 1: Basic Call StackThe RDM ODBC API contains all of the database manipulation logic. The basic call stack is the same whetherdeployed in Windows, Linux or VxWorks.Section 4 will discuss how to implement database sharing, but the basic concept of sharing is shown below:Figure 2: Sharing/Using a Database2. PROGRAMMING WITH THE DATABASE APIThose familiar with the ODBC API will recognize the steps needed to work with databases. The following sectionsshow the basic operations.2.1 Allocate HandlesThe following figure shows preparation work necessary for the rest of the steps. The Connection and/or StatementHandles are required inputs for the rest of the functions.Page 3

TECHNICAL WHITEPAPERFigure 3: Allocate HandlesFrom left to right, Figure 3 goes through the following steps: Allocate a Connection Handle (SQL HANDLE DBC).Set the location for storage of the database (SQL ATTR RDM TFSINIT DOCROOT). This example showshow to specify the “LabVIEW Data” directory. If this is not specified, the current directory of the executingprogram will be used.Connect.Allocate a Statement Handle (SQL HANDLE STMT). One connection can support many statements, but itis typical to use one.2.2 Create or Open a DatabaseCreating a database in ODBC can be accomplished by executing a minimum of two SQL statements. In the followingfigure, two Execute SQL Statement Now (SQLExecDirect) functions are used to do just that:Figure 4: Create a New DatabaseHere is a description of the steps: Execute “create database” statement. Database named HelloWorld.Execute “create table” statement. Table is named HelloTable containing one character string column namedhello.Commit the transaction. It is during the commit where the database is physically created on the storagemedia.Page 4

TECHNICAL WHITEPAPERNote that when a database should always be created (rather than opening an existing database), it is best to deletethe database before creating it.Figure 5: Dropping a Database2.3 Populate and Read a DatabaseInsert and commit two rows:Figure 6: Populate a DatabaseDatabase population frequently occurs in loops, but this simple example performs two inserts: Insert the value “Hello World!” into the string column in the table, creating one row.Insert the value “Hi Back!” into the string column in the table.Commit the two rows to the database.2.4 Close a DatabaseThe clean way to close a database is to make sure of the following: All transactions have been committed or aborted.All Statement Handles have been freed.The Connection Handle has been freed.In this example, the transaction we committed after inserting the two rows, so there is nothing to complete in regardsto transactions.Figure 7: Clean Up / Close DatabasePage 5

TECHNICAL WHITEPAPER2.5 Share/Use a DatabaseStandard connection to the Raima API is standalone. To allow a database to be shared, you must properly set someconnection attributes.Sharing a DatabaseA database that is used by one program can be made sharable to other programs by identifying the Server as “self” inthe “Connect to a Data Source.vi”.Figure 8: Sharing a DatabaseOnce the “self” connection is established and a database open, this program must continue running in order for otherprograms to use the database. In the loop shown below, other work may be done on the database, but a soon as theloop terminates, the shared database will also be unavailable:Figure 9: Keeping a Database Alive for UsersA rule about sharing is that a database must be “used” by another program running on the same architecture, e.g.cRIO-9024 to cRIO-9024 or Windows to Windows. Note below that another method exists to make databasessharable between cRIO devices and Windows. This requires running an external utility on either Windows or Linux.Using a DatabaseThis example assumes that two (or more) different computers are being used, with one LabVIEW program running oneach. More advanced methods will allow multiple programs to run and share databases in the Windows environment,but that will not be covered here.Once a database has been shared by another program, you need to know the name or IP address of the computeron which that program is running. Then, before the “Connect to a Data Source.vi” you need to set theSQL ATTR RDM TFSINIT TYPE to 1 and identify the other computer with the SQL ATTR RDM TFS NAMEattribute:Page 6

TECHNICAL WHITEPAPERFigure 10: Using a Shared DatabaseNote that the SQL ATTR RDM TFSINIT DOCROOT is not necessary when using a database because thedatabase location is established by the sharing program. Note also that it may be set in this program without negativeconsequences.2.6 Connecting Real-time Programs to Windows DatabasesThere is yet-another connection method that has the following advantage/disadvantage: Advantage – Database compatibility between RT cRIO and Windows.Disadvantage – Database cannot be stored on the VxWorks-based cRIO.On Windows and Linux, a program named RDMSQLSERVER.EXE (or just ‘rdmsqlserver’) is available. When thisprogram is running on a Windows or Linux computer within the domain of a specified document root, it can beaccessed by LabVIEW programs running on RT systems concurrently with any database program running onWindows (LabVIEW or otherwise). This is because the communication method between the RDM runtime system oncRIO is heterogeneous.On Windows or Linux, run the utility, specifying the document root:Page 7

TECHNICAL WHITEPAPERFigure 11: Starting RDMSQLSERVER on Windows and LinuxThen from cRIO, name the Server input in “Connect to Data Source.vi”. The name is the computer’s domain name orIP address.Figure 12: Connect to RDMSQLSERVER from cRIO2.7 ArraysArrays of values are supported in the Database API, but they follow different rules.1.2.3.Regardless of the basic data type, the column is defined as “long varbinary”.A “Prepare” is required (cannot input array data with the “Execute SQL Statement Now” function).The array is input after the “Execute SQL Statement” function using the “Set a Parameter Array” function.The default length is the length of the array. You may enter a specific length.When there are other non-array columns in the row, they must be input before the “Execute SQL Statement” function.See below for the general flow:Figure 13: SQL DDL for Array StoragePage 8

TECHNICAL WHITEPAPERFigure 14: Storing Scalar and Array Columns3. TECHNICAL DETAILSThis secton is meant as a quick look under the hood:3.1 WindowsAdvanced technical details. When installed, Raima Database API for LabVIEW will have a complete SDK for LabVIEW development onWindows, allowing programs to be run on Windows, cRIO-9068 or cRIO-9024. Here are the locations of severalkey directories:Toolkit VIs: LabVIEW \vi.lib\addons\ Raima Inc\Raima Database API for LabVIEW\Example VIs: LabVIEW \examples\Raima Inc\Raima Database API for LabVIEW\Error Codes (Raima-Database API-errors.txt): LabVIEW \project\errors\Help file (Raima-Database API for LabVIEW.chm): LabVIEW \help\RT Images: NI Home \RT Images\RaimaDatabaseAPI\2.0\ Several Windows DLLs are required by LabVIEW programs running Raima VIs. The DLL named rdmNative12.dll is the native interface module specifically created for the LabVIEW VIs to use. The remainder of theDLLs are part of the RDM 12.0 product. All of the Windows DLLs are stored in the ToolkitVIs \source\Private\ directory, for example:C:\Program Files (x86)\National Instruments\LabVIEW 2013\vi.lib\addons\ Raima Inc\RaimaDatabase API for LabVIEW\source\Private Also in the directory containing the Windows DLLs are some executable programs. The most important aretfserver.exe, rdmsqlserver.exe and rdmsql.exe. Other utility programs have been included for convenience.Documentation for all of these programs can be found online on the Raima site. If you want to use these from acommand prompt, be sure to include the above directory in your path.C/C program development is possible by downloading the complete RDM SDK from the Raima web site.Since RDM is a multi-user database management system, it is possible to create additional executable programsoutside of the LabVIEW environment that interoperate with the LabVIEW programs at the database level.Page 9

TECHNICAL WHITEPAPER3.2 Real-Time Installation using MAXAll of the binary code files needed for RT operation on the cRIO-9024 or cRIO-9068 is are stored in the RT Images directory identified above, in the Windows installation.Before you can run Real-Time VIs on the CompactRIO/Single-Board RIO, you must make sure the RT modules havebeen in