1 / 28

Interaction with Relational Databases

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

callie
Download Presentation

Interaction with Relational Databases

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Interaction with Relational Databases Bodo Bachmann, Gregg Le Blanc

  2. 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

  3. 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

  4. 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

  5. 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

  6. PI-ODBC and You Gaining Wealth and Wisdom Through PI-ODBC and the Web

  7. ADO and Recordsets • ActiveX data objects (ADO) • Use ADO to: • Create connections • Execute queries • Populate Recordsets • Use Recordsets to: • Present data • Manipulate query results

  8. 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

  9. 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);

  10. 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”)%>.

  11. Demonstration • Sample lab data entry page. • Regular tags. • String tags. • Uses standard HTML forms. • VBScript uses common operations: • Do… Loop • If… Then • ADO etc.

  12. 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.

  13. 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..

  14. 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

  15. 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

  16. 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

  17. 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

  18. MS QUERY VIA ISG NAVIGATOR

  19. 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

  20. PI-OLEDB and Data Controls Demo • ADO DC + DataGrid Control + 3 Lines VBA = PI User Admin • SnapIn for Microsoft Management Console (MMC)

  21. 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

  22. 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

  23. 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

  24. 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

  25. 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

  26. PI RDBMS INTERFACE Demo • Modify PI Tags via PI-OLEDB • Historize changes automatically in RDBMS table

  27. 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)

  28. PI AND RDBMS’s QUESTIONS ?

More Related