如何在没有 matlabs 数据库工具箱的情况下从 matlab 访问 postgresql 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2698159/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I access a postgresql database from matlab with without matlabs database toolbox?
提问by Philipp der Rautenberg
回答by user574057
To connect to postgres from matlab without the database toolbox do something similar to:
要在不使用数据库工具箱的情况下从 matlab 连接到 postgres，请执行以下类似操作：
% 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
回答by Andrew Janke
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".
作为通用解决方案，您可以直接使用 JDBC。现代 Matlabs 都嵌入了 JVM。在 Matlab 中的 Java CLASSPATH 上获取 Postgresql JDBC 驱动程序 JAR 文件，您可以构建 JDBC 连接和语句对象。请参阅“帮助 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.
有几个问题。在 Matlab 的动态类路径上从 JAR 自动注册 JDBC 驱动程序类似乎有点古怪，可能是因为它使用单独的 URL 类加载器，而核心 JDBC 类位于系统类加载器中。因此，您可能需要显式构造 JDBC 驱动程序类的实例并将它们传递给 JDBC 方法，而不是使用您在所有 JDBC 教程中看到的隐式驱动程序构造。此外，从 Matlab 进行的每个 Java 方法调用都有性能开销，如果您在 Matlab 代码中循环结果集游标，这可能会变得昂贵。值得用 Java 编写一个薄的包装层，它将 JDBC 的迭代接口包装在面向块的 Matlab 风格的接口中，在 Java 中读取结果集并将它们缓冲在数组中，
You could use ODBC, too, but that requires writing MEX files linked against ODBC or working with ADO. More difficult and less portable.
您也可以使用 ODBC，但这需要编写针对 ODBC 链接的 MEX 文件或使用 ADO。更难，更不便携。
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.
编辑：如果您通过使用自定义 classpath.txt 在静态 Java 类路径上获取 JAR，您可能可以使自动驱动程序注册工作正常工作。
回答by Pieter V.
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&')
我在使用带有 SSL 模式的 matlab 连接到 pgsql 数据库时遇到问题。使用数据库工具箱应该是这样的： 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');
props.setProperty('ssl','true');
and the normal url, not with sslfactory ... as said in the matlab help.
和正常的 url，而不是 sslfactory ...如 matlab 帮助中所述。
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.
我花了一些时间才发现这一点，所以也许其他人知道他们是否在 SSL 模式下连接到远程数据库时也有问题可能会很有用。
thanks !
谢谢 ！
回答by Jonas
Would MYSQL(additional link)work for you, at least as a starting point?
回答by qrtLs
(DISCLAIMER: needs database toolbox)
Here is a full example to a ready setup postgresql serverfrom a matlab script, adjust database parameters accordingly:
（免责声明：需要数据库工具箱）
这是一个从 matlab 脚本准备好的 postgresql 服务器的完整示例，相应地调整数据库参数：
%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)
您的用户需要 LOGIN 权限角色并能够访问表 ( GRANT)
