Database Connectivity in MATLAB 2010B picks up only one row from Oracle

There are two problems I'm experiencing with the Database Toolbox:

I'm using the 11g client on Win 7 64 bit.

1.) When it does work, it only pulls one row. 2.) It only works once each time MATLAB is opened.

clear all

% create a database connection object

conn = database('mydb','user','pass')

pingconn = ping(conn)

% define a query

disp('Executing query...') tic; sqlQuery = 'SELECT ALL * FROM MY_TABLE';

%setdbprefs('DataReturnFormat','structure')

setdbprefs('DataReturnFormat','cellarray')

curs = exec(conn, sqlQuery)

fetch = (conn, sqlQuery)

toc;

disp('Completed.')

The strange thing is that is communicates with the database and the query times match up with Oracle PLSQL developer exactly, but after the first run with MATLAB open, MATLAB will not store any variables. Also, it only stroes one row when it does work. I've tried many modifications of the same code with no solutions. This is using ODBC connectivity and the connection was tested successfully.

Answer 1

[Martijn]

Please consider using JDBC drivers instead of ODBC:

1. They are more reliable on 64-bit Windows.
2. They should perform better than the ODBC drivers.

[Dude]

This worked.

You must include the ocijdbc11.dll in the Java path for MATLAB.

Check the output of:

>> java.lang.System.getProperty('java.library.path')

and put the ocijdbc11.dll in there.

[Tristan Stöber]

Where do I get 'ocijdbc11.dll' from?

Answer 2

[Walter Roberson]

The line

fetch = (conn, sqlQuery)

has no syntactic meaning.

With regard to it only working once: do you perhaps need to do something to "close" the connection after use?

[Dude]

I've tried closing the connection object and clearing all variables before the next run, both to no avail.

I have also tried writing the query directly into the fetch statement as such:

fetch = (conn, 'SELECT * FROM MY_TABLE')

and that did not work either. I'm going to try to recreate the error on a 32 bit machine.

[Walter Roberson]

fetch = (conn, 'SELECT * FROM MY_TABLE')

has no meaning. () around an expression is used only for prioritization of the expression, but a database object followed by a comma followed by a string is not a valid expression.

>> ('hello','there')

??? ('hello','there')

|

Error: Expression or statement is incorrect--possibly unbalanced (, {, or [.

>> (3,5)

??? (3,5)

|

Error: Expression or statement is incorrect--possibly unbalanced (, {, or [.

[Oleg Komarov]

shouldn't it be: out = fetch(conn, sqlQuery)

[Dude]

Yes Oleg that is correct. I typed that line in manually in the forum and didn't double check myself. That's why it didn't throw errors in the code.