How can I access a postgresql database from matlab with without matlabs database toolbox?

Question

I tried already to use pgmex. Unfortunately it doesn't work with libpq5 (matlab immediately crashes).

Answer 1

To connect to postgres from matlab without the database toolbox do something similar to:

% 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', '<your_postgres_username>');
props.setProperty('password', '<your_postgres_password>');

% Create the database connection (port 5432 is the default postgres chooses
% on installation)
driver=org.postgresql.Driver;
url = 'jdbc:postgresql://<yourhost>:<yourport>\<yourdb>';
conn=driver.connect(url, props);

% A test query
sql='select * from <table>'; % Gets all records
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();

% Read the results into an array of result structs
count=0;
result=struct;
while rs.next()
    count=count+1;
    result(count).var1=char(rs.getString(2));
    result(count).var2=char(rs.getString(3));
    ...
end

Answer 2

As a general solution, you can just use JDBC directly. Modern Matlabs all have a JVM embedded in them. Get the Postgresql JDBC driver JAR file on your Java CLASSPATH in Matlab and you can construct JDBC connection and statement objects. See "help javaclasspath".

There are a couple gotchas. Automatic registration of JDBC driver classes from JARs on the dynamic classpath in Matlab seems a little quirky, maybe because it uses a separate URL classloader and the core JDBC classes are in the system classloader. So you may need to explicitly construct instances of the JDBC driver class and pass them to the JDBC methods, instead of using the implicit driver construction that you see in all the JDBC tutorials. Also, there's performance overhead with each Java method call made from Matlab, which can become expensive if you're looping over a result set cursor in Matlab code. It's worthwhile to write a thin wrapper layer in Java that will wrap JDBC's iterative interface in a block-oriented Matlab-style interface, reading in the result sets and buffer them in arrays in Java, and passing the whole arrays back to Matlab.

You could use ODBC, too, but that requires writing MEX files linked against ODBC or working with ADO. More difficult and less portable.

EDIT: You can probably get the automatic driver registration stuff to work right if you get the JARs on your static Java classpath by using a custom classpath.txt.

Answer 3

I had a problem connecting to a pgsql database with matlab with SSL mode. Using the database toolbox it should be something like this: conn = database('dbname','username','password','org.postgresql.Driver','jdbc:postgresql:databaseURL:dbname:ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&')

but I had the error: 'FATAL: password authentication failed for user "username"'

So I use your script and get the same error.

I had to add the line

props.setProperty('ssl','true');

and the normal url, not with sslfactory ... as said in the matlab help.

So it's nice, but I cannot use the function of the database toolbox... well, not that much of a big deal!

Took me some time to find this out, so maybe it could be useful for other to know that if they have also problem connecting to a distant database in SSL mode on.

thanks !

Answer 4

Would MYSQL (additional link) work for you, at least as a starting point?

Answer 5

(DISCLAIMER: needs database toolbox)
Here is a full example to a ready setup postgresql server from a matlab script, adjust database parameters accordingly:

%Set preferences with setdbprefs.
setdbprefs('DataReturnFormat', 'cellarray');
setdbprefs('NullNumberRead', 'NaN');
setdbprefs('NullStringRead', 'null');


%Make connection to database.
%Using JDBC driver.
conn = database('mydb', 'USERNAME', 'YOURPASSWORD', 'Vendor',... 
  'POSTGRESQL', 'Server', 'SERVERIP', 'PortNumber', 5432);

%Read data from database, just an example on weather table in mydb database
curs = exec(conn, ['SELECT  weather.city'...
    ' , weather.temperature'...
    ' FROM  "mydb"."public".weather ']);

curs = fetch(curs);
close(curs);

%Assign data to output variable
untitled = curs.Data;

%Close database connection.
close(conn);

%Clear variables
clear curs conn

Your user needs LOGIN rights role and be able to access the tables (GRANT)