Geek Answers Handbook

How can I access the postgresql database from Matlab without the Matlabs database toolkit?

I have already tried using pgmex . Unfortunately, it does not work with libpq5 (matlab will work immediately).

+5
source share
6 answers

To connect to postgres from Matlab without the database toolkit, 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
+13
source

JDBC . Matlabs JVM. JAR Postgresql JDBC Java CLASSPATH Matlab, JDBC- -. . " javaclasspath".

gotchas. JDBC JAR Matlab , , , URL, JDBC . JDBC JDBC , JDBC. , Java Matlab, , Matlab. Java, JDBC - Matlab, Java Matlab.

ODBC, MEX, ODBC ADO. .

: , , , , JAR Java classpath classpath.txt.

+7

, . , pgmex . PostgreSQL PgMex 100% C PostgreSQL 9.6 libpq.

, JDBC, IMHO . , - " SQL- Matlab-JDBC", - Undocumented Matlab. PostgreSQL JDBC / Matlab ( Java Matlab ). JDBC , . , , . , datainsert Matlab Database Toolbox ( PostgreSQL, JDBC, JDBC-) batchParamExec PgMex :

+-----------+-----------+--------------+------------------+
| Number of | Data size |   Time for   |     Time for     |
|   tuples  |           |  datainsert  |  batchParamExec  |
|           |           |    (sec.)    |      (sec.)      |
+-----------+-----------+--------------+------------------+
|   20000   |    23Mb   |    37.0255   |      1.1217      |
+-----------+-----------+--------------+------------------+
|   40000   |    46Mb   |    72.4008   |      2.2669      |
+-----------+-----------+--------------+------------------+
|   60000   |    69Mb   |   112.4428   |      3.2055      |
+-----------+-----------+--------------+------------------+
|   80000   |    92Mb   |      n/a     |      4.2073      |
+-----------+-----------+--------------+------------------+
|   100000  |   115Mb   |      n/a     |      5.5277      |
+-----------+-----------+--------------+------------------+
|   300000  |   346Mb   |      n/a     |      14.3530     |
+-----------+-----------+--------------+------------------+
|   600000  |   691Mb   |      n/a     |      28.3156     |
+-----------+-----------+--------------+------------------+
|   800000  |   922Mb   |      n/a     |      38.2579     |
+-----------+-----------+--------------+------------------+
|  1000000  |   1152Mb  |      n/a     |      47.8714     |
+-----------+-----------+--------------+------------------+
|  1200000  |   1382Mb  |      n/a     |      56.6258     |
+-----------+-----------+--------------+------------------+
|  1400000  |   1613Mb  |      n/a     |      65.9764     |
+-----------+-----------+--------------+------------------+
|  1750000  |   2016Mb  |      n/a     |      82.1829     |
+-----------+-----------+--------------+------------------+
|  2000000  |   2304Mb  |      n/a     |      93.5854     |
+-----------+-----------+--------------+------------------+

n/a , " Java" , Java 939Mb. , , , . PostgreSQL Matlab).

, , , JDBC . IMHO libpq, PgMex, . PgMex , , mexPostgres ( - Matlab Central), ++. ( PQgetvalue libpq) ( , , , , , , , ). . PgMex, Matlab PostgreSQL - . , Matlab- ( , , Matlab).

, , , PgMex. , ( , , <>, , ):

% Create the database connection
dbConn=com.allied.pgmex.pgmexec('connect',[...
    'host=<yourhost> dbname=<yourdb> port=<yourport> '...
    'user=<your_postgres_username> password=<your_postgres_password>']);

% A test query
sql='select * from <table>'; % Gets all records
pgResult=com.allied.pgmex.pgmexec('exec',dbConn,sql); % Perform this test query

% Read the results
nFields=com.allied.pgmex.pgmexec('nFields',pgResult);
outCVec=cell(nFields,1);
fieldSpecStr='%<field_type_1> %<field_type_2> ...';
inpCVec=num2cell(0:nFields-1);
[outCVec{:}]=com.allied.pgmex.pgmexec('getf',pgResult,...
    fieldSpecStr,inpCVec{:});

, "getf" - PgMex ( fieldSpecStr). outCVec , valueVec, isNullVec isValueNullVec. , , valueVec , isNullVec isValueNullVec - NULL.

+6

pgsql matlab SSL. , : conn = database ('dbname', 'username', 'password', 'org.postgresql.Driver', 'jdbc: postgresql: databaseURL: dbname: ssl = true & sslfactory = org.postgresql.ssl.NonValidatingFactory &')

: 'FATAL: "username" '

, script .

props.setProperty( 'SSL', '');

url, sslfactory... matlab.

, , ... , !

, , , , , SSL.

!

+1

MYSQL ( ) , , ?

0

( : )
postgresql matlab script, :

%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

LOGIN (GRANT)

0

More articles:


All Articles