Connecting to a distant postgreSQL database using Matlab
My problem is, when I connect to a distant psql database using matlab, I have the following error:
FATAL: password authentication failed for user "[username]"
But the password is correct. I have checked with the database manager.
I can connect to the database through PSQL shell, it works. It's just matlab. I need to connect to SSL, so I am using the following line of code:
conn = database('dbname','username','password','org.postgresql.Driver',
'jdbc:postgresql:dm-userdb.geomar.de:users:ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&');
Following the Matlab documentation.
The database manager thinks this is a problem with the syntax I use in Matlab, since the connection works with the shell, but he doesn't own the text, so he can't help me.
Also, when I create a local database on my computer, I can connect to it using matlab. It's only connecting to this remote database in SSL mode using matlab (in SSL Off it doesn't work).
Connecting to this database using R or python works. With R code:
install.packages("rpostgres")
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host = "dm-userdb.geomar.de", dbname="users", tty = "NULL", user="****", password = "******", port = "5432")
But since we will have users of this DB using different software, we have to make it work with Matlab as well and I don't know what is wrong with my script.
I have mac book air with maverick and matlab R2013a.
It will be very nice if someone tells you what is wrong.
source to share
You can of course use JDBC as shown in the answer below. But it's better to speed up your queries. For example, there is an interesting article titled "Speeding up Matlab-JDBC SQL Queries" on the MATLAB documentless website. You can do something like what is suggested there. But IMHO JDBC is not the best way for big data or data types more complex than scalars (like arrays). One of the reasons is the overhead of converting data to / from Matlab built-in formats (Java objects in Matlab and vice versa). Therefore, it is better to use solutions based on libpq (the official C API for PostgreSQL). For example, you can try the open source mexPostgres package (it can be downloaded from the central Matlab portal) written in C ++.This library parses data based on its textual representation (viaPQgetvalue
function from libpq) and only for a very limited list of data types (they are actually scalar numbers and booleans, times, dates, timestamps and intervals, as well as strings, more complex types such as arrays are out of scope). But transmission via text representation is IMHO very slow and can only be used for not very large datasets. And there is at least one commercial solution called PgMexwritten in C and also based on libpq. This library implements a very efficient binary data transfer channel between Matlab and PostgreSQL without any text parsing. Moreover, everything is done in Matlab-friendly and native way (in the form of matrices, multidimensional arrays, structures and arbitrary other Matlab formats). You can also see the following
"Performance Comparison of PostgreSQL Connectors in Matlab" for a better understanding of the reasons that do not allow you to efficiently connect Matlab via JDBC.
Connecting to PostgreSQL from Matlab via PgMex is very simple, see the following code (we assume that under the values of all the parameters marked with signs <>
are filled in correctly):
dbConn = com.allied.pgmex.pgmexec('connect',[...
'host=<yourhost> dbname=<yourdb> port=<yourport> '...
'user=<your_postgres_username> password=<your_postgres_password>']);
PgMex provides many commands that allow import and export data (some references, see. exec
, paramExec
, batchParamExec
To perform queries getf
to analyze the results of the query putf
to put data into a set of parameters that can be used in the parameterized queries).
source to share
I finally found a solution without using database instrumentation:
% Add jar file to classpath (ensure it is present in your current dir)
%javaclasspath('postgresql-9.0-801.jdbc4.jar');
% Username and password you chose when installing postgres
props=java.util.Properties;
props.setProperty('user', 'username');
props.setProperty('password', 'password');
props.setProperty('ssl','true');
% Create the database connection
driver=org.postgresql.Driver;
url = 'jdbc:postgresql://databaseURL/dbname';
conn=driver.connect(url, props);
But I cannot use other features of the database instrumentation ...
I've tried countless possibilities with the Matlab database function, none of them ever worked!
source to share