How can I retrieve a PostgreSQL double precision[] from within MatLab from an actxserver​("ADODB.Co​nnection") connection?

13 views (last 30 days)
Hi! I am querying a PostgreSQL database from within MatLab. I seem to have no problem retrieving columns with integers, and varchar values, for example, but it crashes when I try to retrieve double precision array values.
That is, this works, where column "a" is an integer, or varchar, or something:
SELECT a FROM tableName;
This crashes, where x is a double precision[]
SELECT a, x FROM tableName;
The code looks like this:
conn = actxserver("ADODB.Connection");
conn.Open('driver={PostgreSQL ODBC Driver(UNICODE)}; server=....;');
r = conn.Execute(sql); %% <- CRASH SITE
rows = r.GetRows();
The arrays typically have ~10^5 double precision elements.
The error is:
Error using COM.ADODB_Connection/Execute
Invoke Error, Dispatch Exception: Incorrect function.
Error in QuickTest (line 14)
r = conn.Execute(sql);
The code crashes here: r = conn.Execute(sql);
It crashes even if do not attempt to do anything with the double precision[] column besides include its name in the SQL being passed to the statement: r = conn.Execute(sql);
I've run the SQL from Python, DBeaver, and pgAdmin, and those all work.
Would anyone know how to retrieve a PostgreSQL double precision[] value from within MatLab from an actxserver("ADODB.Connection")?

Answers (1)

Mrutyunjaya Hiremath
Mrutyunjaya Hiremath on 24 Oct 2023
Arrays in PostgreSQL, especially of type `double precision[]`, may not be directly supported or might be causing issues when fetched via ADODB in MATLAB.
However, you can try a few workarounds to retrieve the data:
1. Convert Arrays to Strings: Modify your query to convert the double precision array to a string format, and then in MATLAB, parse this string back into an array.
SELECT a, ARRAY_TO_STRING(x, ',') as x_string FROM tableName;
After fetching this in MATLAB, you can convert the `x_string` back into a MATLAB array using `str2double` and `strsplit`.
matlab_array = str2double(strsplit(x_string, ','));
2. Use MATLAB's Database Toolbox: MATLAB has a dedicated database toolbox that provides functionality to connect and retrieve data from SQL databases, including PostgreSQL. This might handle data types and arrays more gracefully.
conn = database('myDatabase', 'username', 'password', 'Vendor', 'PostgreSQL', 'Server', 'serverAddress');
curs = exec(conn, 'SELECT a, x FROM tableName');
curs = fetch(curs);
data = curs.Data;
If you use this approach, make sure you have the appropriate JDBC driver for PostgreSQL.
3. Reduce Fetch Size: Since the arrays have a large size (~10^5 elements), there might be a buffer or memory issue. Try limiting the number of rows you fetch in a single query to see if the problem persists. This will help determine if the issue is strictly related to the data type or if it's a combination of data type and data size.
SELECT a, x FROM tableName LIMIT 100;
If none of the above solutions work, you might need to consider alternate methods for data retrieval, such as exporting the data from PostgreSQL to a file format that MATLAB can read easily (like CSV) and then importing it into MATLAB.




Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!