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

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

...

r.Close();

conn.Close();

---------------------------------------------------------------

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")?

Thanks!

Answer 1

[Mrutyunjaya Hiremath]

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.

SQL

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

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.

MATLAB

conn = database('myDatabase', 'username', 'password', 'Vendor', 'PostgreSQL', 'Server', 'serverAddress');
curs = exec(conn, 'SELECT a, x FROM tableName');
curs = fetch(curs);
data = curs.Data;
close(curs);
close(conn);

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.

SQL

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.