OLE Database Tools Addin

[#1]

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.

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.

[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

[stan_koprowski]

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 OLEDB 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, check it out here: Aveva/OSISoft PI Tools 

View more...

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;

Edited to include a link to the Add-in.