Message Boards Message Boards

Connect Database Link to ESXI hosted database to push/pull data?

Posted 3 years ago

I have a hosted database in CENT OS on an ESXI host with VM ware. I need to be able to connect to this to push pull data. If there is any documentation for this specifically on prior users doing this, I would love to have it. I have read all through DatabaseLink documentation and probably need to read through it again, but I believe the connection to the server need to occur with IP, Port, Socket connection, etc. I may be wrong, but any help is good.

POSTED BY: Luke Jennings
4 Replies
Posted 3 years ago

Hi Luke,

If you need to read/write and are familiar with SQL, use DatabaseLink. If you just need to read, and are familiar with the WL entity framework, use RelationalDatabase.

You did not mention the type of database server you are connecting to (PostgreSQL, MySQL, SQLServer, ...). If it is one that DatabaseLink already has an installed JDBC driver for then the easiest way to connect is to use it. For a list of installed JDBC drivers

Needs["DatabaseLink`"]
JDBCDriverNames[]

Assuming the JDBC driver for your database is already installed, here is an example of opening a connection using a public RNA sequence PostgreSQL database hosted by RNACentral.

conn = OpenSQLConnection[
  JDBC["PostgreSQL", "hh-pgsql-public.ebi.ac.uk/pfmegrnargs"],
  "Username" -> "reader", "Password" -> "NWDMCE5xdipIjRrp"
  ]

List of tables

tables = SQLTableNames[conn]

Execute query using SQL string queries (WL style SQL queries would be an alternative)

SQLExecute[conn, "select * from xref where ac = 'OTTHUMT00000106564.1'"]

Close the connection when you are done

CloseSQLConnection[conn]
POSTED BY: Rohit Namjoshi

We are using Postgres db for our database. I will try this. In the meantime can you give me some more detailed info or documentation on JDBC driver ?

POSTED BY: Luke Jennings
Posted 3 years ago

PostgreSQL JDBC driver documentation. WL JDBC tutorial.

POSTED BY: Rohit Namjoshi

Thank you!

POSTED BY: Luke Jennings

Group Abstract Group Abstract