BookmarkSubscribeRSS Feed
stan_koprowski

Community Manager

Joined:

Aug 8, 2012

OLE Database Tools Addin

This addin will connect to an OLEDB and convert it to a SQLite database so that it can be used with Query Builder.  The addin uses a combination of JMP Scripting Language and a Python script to do most of the work.

The addin executes a Python script (thanks for the help on writing the Python script from bryan.boone) that in turn creates a snapshot of the OLEDB and saves it out as a SQLite DB.  The SQLite DB can then be directly accessed from QB as shown.  External programs such as Python can be executed by using the JMP scripting language or JSL.  JSL has a function called Run Program.

Options for submitting a custom query or limiting the number of rows are available from the menu.11582_Screenshot 2016-05-17 22.34.52.png

The addin was used to access Proficy Historian and Microsoft Access OLE databases.  See my blog post for additional details for connecting to a Proficy Historian database.

11584_Screenshot 2016-05-17 23.01.03.png

11583_Screenshot 2016-05-17 22.14.16.png

Comments
BrianGSC

Hi Stan.

You write: 'See my for additional details for connecting to a Proficy Historian database.'

Do you have some aditional setup documentation?

Do I need to install a SQLite DB?

Should I configure it in a special way?

Should I setup the ODBC connection?

And how do I add other collectors to the selection box (you use VML21424D)?

Regards Brian

Hi Brian,

Have a look at my blog. This will have the additional details that you asked about. 

Also, there is help documentation included with the add-in.

best,

Stan

BrianGSC

Thanks.

I use the linked server solution.

It works.

Can you add the link to your 'my blog' in the article above, please.

Regards Brian


Santanu

Hello

 

How do we connect OSIsoft PI DB to JMP using PIOLEDB?

ih

@Santanu,

 

I use instances of the PI ODBC Driver (one DSN for each PI Archive Server and one for each PI AF Server) to connect to PI SQL DAS which then connects to to the PI OLE DB Provider (for the PI Data Archives) or to PI LOEDB Enterprise (for the PI AF Servers).  Then, PI values are called using JSL similar to the example below.  An Add-In is used to to generate this code, if you are interested I can try to post a 'public' version of this Add-in.

 

Spoiler
dtLong = Open Database( "DSN=ODBC Connection Name",
	"SELECT TOP 1000000 tag, time, value, svalue, DIGSTRING(status) status, status as vstatus
FROM piarchive..piinterp
WHERE (tag IN ('tag1','tag2'))
	AND time BETWEEN 't - 2d + 6h' AND 't + 6h' AND timestep = '1h'"
);

//Check for too many values
If( N row( dtLong ) >= 1000000,
	New Window(
		"Max Rows Exceeded",
		Modal,
		Text Box( "Met or exceeded the maximum number of rows for " ||
			"this query. Increase the allowed maxrows or restrict the query. " ||
			"Some results might be missing." )
	)
);

//Convert to wide format
dtWide = dtLong << Split(
	Split By( :tag ),
	Split( :value, :svalue, :status, :vstatus ),
	Group( :time ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);
dtLong << Close Window;

PI Add-In.png