Message Boards Message Boards

0
|
2060 Views
|
2 Replies
|
1 Total Likes
View groups...
Share
Share this post:

PostgreSQL connection via Unix sockets in DatabaseLink?

Posted 1 year ago

Hello, Is it possible to connect to a PostgreSQL database via unix sockets?

I see that DatabaseReference does allow sockets to be specified in the "Host" parameter.

However, I want to execute raw SQL queries and apparently this is possible only with Needs["DatabaseLink"]

The option

OpenSQLConnection[JDBC["org.postgresql.Driver", "jdbc:postgresql://" <> dbHost"]]

doesn't work (I get Connection to :5432 refused). There is no "Database" option there as well.

POSTED BY: Przemyslaw K.
2 Replies
Posted 1 year ago

My dbHost is a unix socket, i.e. /var/lib/postgres. The connection string postgresql:///var/lib/postgres/somedb doesn't work apparently.

POSTED BY: Przemyslaw K.

There are many reasons why a connection can be refused. Is dbHost a localhost?

Try

conn = OpenSQLConnection[
    JDBC["PostgreSQL", "<host_name>/<db_name>"],
    "Username" -> "<user_name>", "Password" -> "<password>"
    ]

E.g to connect to a public RNA sequence PostgreSQL database named pfmegrnargs hosted by RNACentral

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

tables = SQLTableNames[conn]

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

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

Close connection

CloseSQLConnection[conn]
POSTED BY: Rohit Namjoshi
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract