How can I retrieve a PostgreSQL double precision[] from within MatLab from an actxserver("ADODB.Connection") connection?
10 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
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!
0 comentarios
Respuestas (1)
Mrutyunjaya Hiremath
el 24 de Oct. de 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.
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.
0 comentarios
Ver también
Categorías
Más información sobre Database Toolbox en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!