6

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

Improve this question

5 Answers 5

Reset to default
16

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

From my experience, the URL should be: url = 'jdbc:postgresql://<yourhost>:<yourport>/<yourdb>'; (the backslash between <yourport> and <yourdb> should be a forward slash). This is while running Matlab and the PostgreSQL server on the same Windows 7 computer.
Same for me on Linux. Backslash does not work, forwardslash does.
It is recommended to close the ResultSet after you are done reading the values. Just add rs.close(); to do so
7

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.

Improve this answer

Comments

1

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 !

Improve this answer

Comments

0

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

Improve this answer

1 Comment

Unfortunately this doesn't work with postgresql. But probably there is a more general solution via ODBC/JDBC?
-1

(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)

Improve this answer

3 Comments

Downvoted as the question explicitly states without matlab database toolbox.
not fair, i clearly stated it in the beginning and added for completion. People might be looking for it, too
I am sorry you feel this is unfair, but if my question is How can I do A, without using B and your answer is Use B while there are clearly other ways, I think your answer is not useful, hence the downvote. Regarding People might be looking for it: sure. But not when they're searching for a method without the toolbox. If you're eager to share your knowledge (which is great!) you can ask a question yourself (i.e. How can I connect to PostgreSQL from MATLAB) and provide an answer to your own question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.