interaction with relational databases n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Interaction with Relational Databases PowerPoint Presentation
Download Presentation
Interaction with Relational Databases

Loading in 2 Seconds...

play fullscreen
1 / 28

Interaction with Relational Databases - PowerPoint PPT Presentation


  • 138 Views
  • Uploaded on

Interaction with Relational Databases. Bodo Bachmann, Gregg Le Blanc. PRESENTATION OVERVIEW. Available options to connect to RDBMS’s 5min New version of PI-ODBC 5min, detailed list of new features, demo here or below, Gregg

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Interaction with Relational Databases' - callie


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
interaction with relational databases

Interaction with Relational Databases

Bodo Bachmann, Gregg Le Blanc

presentation overview
PRESENTATION OVERVIEW
  • Available options to connect to RDBMS’s5min
  • New version of PI-ODBC5min, detailed list of new features, demo here or below, Gregg
  • From PI-ODBC via ADO/RDS to the Web10min, r/w example showing support of new data types and UPDATE/INSERT, Gregg
  • PI-OLEDB prototype10min, demo MMC snap-in, PB DataControl/DataGrid, Bodo
  • PI RDBMS Interface10min, demo point configuration log, edit Tags via MS Query, log in Access, Bodo
  • Questions5min
slide3

MANY OPTIONS...

PI-ProcessBOOK &PI-PC DATALINK(EXCEL, LOTUS 123)

OTHER PC TOOLS(POWER BUILDER, ACCESS,VISUAL BASIC, Q+E, EXCEL,…)

ActiveX

PI-API

RDBMS ODBCRDBMS NET

PI-ODBCPI-OLE DB

RDBMS ODBCRDBMS NET

ODBCOLE DB

PI SERVERPI SYSTEMREAL-TIME DATAMANUAL / PROCESS / CALCULATED

RDBMS NETRELATIONAL DATABASE(RDBMS)NON REAL-TIME DATAPRODUCT, EQUIPMENT,SPECIFICATION, SAFETY, ETC.

RDBMSINTERFACE

MANUAL / LABINPUTS

AUTOMATIC SCANNED DATATEXT / DATA / CONFIG

COMCONNECTOR

slide4

PI-ProcessBOOK

PI-API

RDBMS ODBCRDBMS NET

ODBCOLE DB

ProcessBook and RDBMS’s

  • Embedded ODBC support (DataSet)
  • ActiveX Data Controls
  • ADO via VBA
  • Other Libraries via VBA (OLE DB, RDS,...)
  • Special developped ProcessBook AddIn
  • PI-UDA Tags via OLE DB Com Connector
slide5

OTHER PC TOOLS(POWER BUILDER, ACCESS,VISUAL BASIC, Q+E, EXCEL,…)

PI-ODBC

RDBMS ODBCRDBMS NET

PI-ODBC

  • Present PI Data as it would be a RDBMS
  • Link PI tables into MS Access and use MS Access as Report Writer
  • Use OLE DB Gateway and link into MS SQL Server 7
  • Use ODBC Clients for Data Analysis,...
  • New Version PI-ODBC 1.1.8:
    • Support PI3 data types and milliseconds
    • Read/Write access for data archive
pi odbc and you

PI-ODBC and You

Gaining Wealth and Wisdom Through PI-ODBC and the Web

ado and recordsets
ADO and Recordsets
  • ActiveX data objects (ADO)
  • Use ADO to:
    • Create connections
    • Execute queries
    • Populate Recordsets
  • Use Recordsets to:
    • Present data
    • Manipulate query results
serving data using asp s
Serving Data Using ASP’s
  • Use IIS and Active Server Pages
    • Written in HTML and VBScript
    • Database access is on server side
  • Install PI-ODBC on web server
  • Configure and name system DSN
grabbing data
Grabbing Data
  • Open the connection & recordset:
    • Set Conn = Server.CreateObject(“ADODB.Connection”)
    • Conn.Open “PI”
    • Set Rs = CreateObject(“ADODB.RecordSet”)
    • Set Rs = Conn.Execute(strQry)
  • Retrieve Values:

SELECT Time, currentstat=DIGSTRING(status), Value, Tag

FROM picomp WHERE Tag = “Sinusoid”

AND Time >= DATE(“17-Mar-00 11:00”);

  • Input Values:

INSERT INTO picomp (tag, time, value)

VALUES (“Sinusoid”, “*”, 42);

applications
Applications
  • Lab data entry.
  • Query PI data via a web site.
  • An example:
    • Uses any web browser.
    • Uses standard web building tools.
    • Does not compromise security.
    • All processing done on server side.
      • Display recordset contents: <%=Rs(“value”)%>.
demonstration
Demonstration
  • Sample lab data entry page.
    • Regular tags.
    • String tags.
  • Uses standard HTML forms.
  • VBScript uses common operations:
    • Do… Loop
    • If… Then
    • ADO etc.
summary
Summary
  • Use IIS and PI-ODBC to make simple pages that use PI data.
  • Results are browser independent.
  • Can be effective as a lab entry terminal.
  • PI-ODBC now supports string tags.
slide13

OLE DB - THE NEXT ODBC GENERATION

  • OLE DB is based on COM
  • OLE DB defines an open and extensible set of OLE interfaces
  • OLE DB provides uniform access to tabular data, not limited to RDB’s
  • Rowset Interface and optional Command Interface
  • OLE DB is the native communication technology for MS SQL Server 7
  • Via OLE DB, MS SQL Server 7 can link foreign tables, as MS Access can do for ODBC data sources
  • Less sophisticated data applications (non SQL) can become data providerse.g. Spreadsheets. E-Mail, Directory Services..
slide14

CONNECTIVITY

Consumer

Services

Data

Providers

Application or Tool

Active X Data Objects (ADO)

OLE DB

Microsoft Transaction Server

COM / DCOM

Cursor

Engine

Dist. Query

Engine

Rel. Query

Engine

OLE DB

ODBC

SQL

Spread-

sheet

ISAM

Spatial

File

slide15

PI-OLEDB

PI-OLEDB

PI-SDK

PI-SDK

PI Server

Real Time Data

NT, UNIX, VMS

PI-OLEDB PROTOTYPEONE OF MANY POSSIBLE IMPLEMENTATIONS

  • One can think of many ways to implement a PI- OLEDB driver
  • We have chosen to build the prototype on top of PI-SDK
    • This complements PI-API and PI-ODBC functionality
    • PI-SDK functionality is available for non-programmers
    • PI-SDK uses the same technology, COM
slide16

FUNCTIONALITY OF PI-OLEDB PROTOTYPE

  • Major PI Configuration Data is made available via catalogs (databases)
    • PIPOINT - all configured pointclasses appear as tables
    • PIDS - all digital state tables, that are configured in PI, are tables
    • PIUSER - one table that contains PI users defined
    • PIGROUP - one table per group
  • SQL Language supported via Command Interface
    • CREATE and DROP tables
    • UPDATE, INSERT, SELECT, DELETE”SELECT * FROM PIPOINT:CLASSIC”
    • Support for WHERE clause“… WHERE TAG=’x*’ AND ARCHIVING=1 OR SCAN=0”
    • Support for ORDER BYSELECT * FROM CLASSIC ORDER BY CREATIONDATE DESC
slide17

Client ApplicationsMS Access, MS Query,Other ODBC Clients

SQL Calls

ODBC Driver Manager

ODBC Gateway

(ISG Navigator)

PI-OLEDB

PI Server

Real Time Data

NT, UNIX, VMS

PI-SDK

BIG VARIETY OF ODBC CLIENTS CAN BE USED

  • An ODBC Gateway makes OLE DB available for ODBC Clients
  • We have successfully tested ISG Navigator
slide19

PI-OLEDB and ADO

  • ADO (ActiveX Data Objects ) is a programming interface that wraps the OLE DB API
  • ADO is easier to use than OLE DB, since more compact and fewer objects
  • ADO especially made for the VB/VBA environment
  • Several Data Binding Controls allow data access without programming
  • Controls that encapsulate database queries and returned rowsets are for example:MS ADO DC, Apex True DataControl
  • Data aware Grid Controls that bind to data controls are for example:MS DataGrid Control, Apex True DBGrid Control
slide20

PI-OLEDB and Data Controls

Demo

  • ADO DC + DataGrid Control + 3 Lines VBA = PI User Admin
  • SnapIn for Microsoft Management Console (MMC)
slide21

LIMITATIONS OF PI-OLEDB PROTOTYPE

  • Only configuration data (PI-SDK)
  • Not for PI2 Systems (yet)
  • SELECT … WHERE clause limited to specify fields which are supported by PI-SDK query
  • Command interface supports basic SQL, but no JOIN, UNION,... and no Functions
  • Performance slower than PI-ODBC
slide22

FUTURE DEVELOPMENT OF PI-OLEDB

  • Rewrite to use PI-SQLSSEnhancements required for PI-SQLSS and PI-SDK
  • Support for PI Module DB and PI Data Archive
  • Performance optimisation
  • Free-threaded version (linked tables for MS SQL Server 7)
  • Next level SQL language
slide23

PI RDBMS INTERFACE

PI-ProcessBOOK &PI-PC DATALINK(EXCEL, LOTUS 123)

OTHER PC TOOLS(POWER BUILDER, ACCESS,VISUAL BASIC, Q+E, EXCEL,…)

ActiveX

PI-API

RDBMS ODBCRDBMS NET

PI-ODBCPI-OLE DB

RDBMS ODBCRDBMS NET

ODBCOLE DB

PI SERVERPI SYSTEMREAL-TIME DATAMANUAL / PROCESS / CALCULATED

RDBMS NETRELATIONAL DATABASE(RDBMS)NON REAL-TIME DATAPRODUCT, EQUIPMENT,SPECIFICATION, SAFETY, ETC.

RDBMSINTERFACE

MANUAL / LABINPUTS

AUTOMATIC SCANNED DATATEXT / DATA / CONFIG

COMCONNECTOR

slide24

RDBMSINTERFACE

PI-API

RDBMS ODBCRDBMS NET

ODBCOLE DB

PI SERVERPI SYSTEMREAL-TIME DATAMANUAL / PROCESS / CALCULATED

RDBMS NETRELATIONAL DATABASE(RDBMS)NON REAL-TIME DATAPRODUCT, EQUIPMENT,SPECIFICATION, SAFETY, ETC.

PI RDBMS INTERFACE STRUCTURE

  • Interface runs on Windows NT / 2000
  • Works for ANY Relational Databasewhich has an ODBC driver
  • Tested by OSI explicitly:MS SQL Server 6.5/7, Oracle 7.1/8, Oracle RDB 6.1, MS Access 95/97, dBase III/IV
slide25

PI RDBMS INTERFACE FUNCTIONALITY

  • Query data (read) for single tag, tag groups and distributed tags
  • Query one value or time series per scan
  • Scan or Event based SELECT queries (PI Input)
  • Event based UPDATE, DELETE and INSERT queries (PI Output)
  • Support multiple statements per query and Stored Procedures
  • Support of ‘runtime’ placeholders
  • Support of classic ‘point attribute’ placeholders
  • Support of ‘batch’ placeholders to replicate PI batch records
  • Historize Tag Database changes in RDBMS tables
  • Recovery for Output data after interface down
slide26

PI RDBMS INTERFACE

Demo

  • Modify PI Tags via PI-OLEDB
  • Historize changes automatically in RDBMS table
slide27

PI RDBMS INTERFACE - NEXT DEVELOPMENT

  • Version 2.14 in Beta, bug fixes and DB2 tests
    • Optional replacement of archive data
    • Timezone handling when interface in different TZ than PI Server
  • Version 3 development starts now
    • Automate login configuration via connection dialog
    • Overcome 80 char limit of Extended Descriptor
    • Support all pointclass attributes for placeholder AT.ATTRIBUTE
    • Tag configuration and ODBC testtool
    • Scan based output
    • Output of aggregate data (piar_calculation instead of sourcetag)
slide28

PI AND RDBMS’s

QUESTIONS ?