PostGreSQL database hosted in Google cloud: is JMP Cloud the solution?

Question

Hi all,

I have a question that I received from the IT head for one of our customers. I'm not too familiar with database connections and would like to hear some opinions from those who have experience. This is the message:

"We have a PostGreSQL database hosted in Google cloud. It is directly accessible from the office so you can (for example) connect a Python script to it without needing any extra infrastructure.

I would like all my JMP users to be able to connect to this database, but right now this would mean installing the ODBC driver on every single one of those machines, which is a bit laborious. It feels more onerous because even after half an hour of trying I couldn’t get it working on a Mac, and I’m an experienced technical professional. If there was a way I could get the database connection set up just once somewhere central, and then all the JMP users could somehow make use of that central setup, that sounds like it could be easier for us to get set up and managed."

Based on the scenario above, would JMP on the Cloud be the solution? Or is there any other recommendation?

Jed_Campbell · Answer

I stumbled across the online LIMS Database. William Corley gets all the credit. Here's his journal for setting it up.

Working with LIMS.jrn

Jed_Campbell · Answer

This is perhaps only 20% of an answer, but for what it's worth:

I would like all my JMP users to be able to connect to this database, but right now this would mean installing the ODBC driver on every single one of those machines, which is a bit laborious. It feels more onerous because even after half an hour of trying I couldn’t get it working on a Mac

I believe Windows ships with ODBC for PostgreSQL built in, and the JMP LIMS Demo connects to it fairly easily. I can't seem to remember where/when/who created that database, but I suspect @Byron_JMP was involved somehow. (I'm hoping to bookmark whatever the source is when it crops up).

Deploying an ODBC connection script is something that can mostly be done with JSL, with a little PowerShell. In my previous life, I made the following part of any database connection script, so if there wasn't an ODBC connection, the user could create it quickly. Most computers won't allow PowerShell scripts made on other computers, so I used JSL to set the clipboard and asked the user to paste the clipboard into Powershell. There are likely more elegant ways to do this, but this way worked without anyone needing to contact IT. Pseudo-JSL below:

Try(
	//get 1 data point from database 
	//this one is for the JMP LIMS database, truncated to not accidentally share it publicly
	New SQL Query(
		Connection( "ODBC:auto-generated connection script here" ),
		QueryName( "LIMS_Results" ),
		Select( Column( "Sample_ID", "t1" ) ),
		From( Table( "LIMS_Results", Schema( "LIMS" ), Alias( "t1" ) ), Sample( FirstN( 1 ), Block ) )
	) << Run Foreground
,
	Caption( "Hit Ctrl-V, then enter in the PowerShell window that pops up to create a database connection." );
	Set Clipboard( "Powershell ODBC Connection string command" ); //instructions here: web ("https://learn.microsoft.com/en-us/powershell/module/wdac/add-odbcdsn?view=windowsserver2022-ps")
	Web( "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" );

)

Daniel_Valente · Answer

Thanks @mia_stephens for sharing the dev demo information on the configurable data connectors. This capability in JMP 18 could offer a lot of relief for this case. It makes it easier to share the connection information for a DSN-less connection. The ongoing work to match drivers between device types and systems will be helpful as well. This is a capability that will continue to develop in the 18.X and 19 cycle.

There is more information in the JMP 18 Sales and Marketing Toolkit.