MATLAB: Does sqlwrite function not support BINARY_FLOAT and BINARY_DOUBLE datatypes provided by Oracle

binary_doublebinary_floatDatabase Toolboxsqlwrite

When sqlwrite function is used to insert BINARY_FLOAT or BINARY_DOUBLE datatypes as given in following code snippet

>>sqlquery = ['CREATE TABLE MyOracleTable(myBinaryFloatValue BINARY_FLOAT)'];
>>exec(conn,sqlquery);  % "conn" is the Database Connection object
>>myMLTable = table(1,'VariableNames',{'myBinaryFloatValue'});
>>sqlwrite(conn,'MyOracleTable',myMLTable);

MATLAB throws following error:

Error using database.jdbc.connection/sqlwrite (line 172)

JDBC JDBC/ODBC Error: java.lang.Double cannot be cast to oracle.sql.BINARY_FLOAT.

Best Answer

Converting MATLAB numeric datatypes to BINARY_FLOAT or BINARY_DOUBLE is not currently supported by "sqlwrite", mainly because these data types are not part of the ANSI-supported SQL types.

As a workaround, directly convert the data to BINARY_FLOAT before passing it to "sqlwrite" as follows:

>>myMLTable = table([oracle.sql.BINARY_FLOAT(3);oracle.sql.BINARY_FLOAT(4)] ,'VariableNames',{'myBinaryFloatValue'});
>>sqlwrite(conn,'MyOracleTable',myMLTable );

Please note that for this workaround to work, the table 'MyOracleTable' should already exist in the database.

Related Solutions

MATLAB: Am I getting the error “Beyond SQL_ACTIVE_STATEMENTS limit” when querying from SQL database with Database Toolbox

"Beyond SQL_ACTIVE_STATEMENTS limit" means that your application has tried to create more "hStmt"s by ODBC driver than the database is allowed.

It probably means that you are creating a new "hStmt" each time you submit a new query, but you are not releasing these "hStmt"s after the queries complete.

Please make sure to close the cursor object after the the query is completed with the "close" function as shown in the example below:

>> sqlquery = 'SELECT * FROM inventoryTable ORDER BY productNumber';
>> curs = exec(conn,sqlquery,'CursorType','scrollable');
>> curs = fetch(curs,'AbsolutePosition',10);
>> data = curs.Data;
>> close(curs);

MATLAB: Do I get “JDBC Driver Error: ERROR: cross-database references are not implemented” with a PostgreSQL database

Please try renaming your database to use all lower-case letters. The lower-case naming convention is suggested by PostgreSQL, and should resolve the error you are seeing in MATLAB.

This error is due to a difference in naming conventions between SQL and PostgreSQL.

According to PostgreSQL's standards, any unquoted names in queries are converted to lower-case letters, so when myDB is a part of the query, PostgreSQL interprets it as mydb instead. Since Database Explorer prepends the database name and schema name to the table name, and since the database name is not all lowercase, PostgreSQL interprets Database Explorer's query for myDB.public.person as mydb.public.person. This raises an error because you are connected to myDB but PostgreSQL thinks you are trying to query mydb, which it interprets as a different database.

The way to enforce capitalization in PostgreSQL and SQL is to surround each case-sensitive name in quotes. For example, if you change the query from

SELECT * FROM myDB.public.person

SELECT * FROM "myDB".public.cats

The query should be successful.

Note that this forcing to lower-case is a PostgreSQL-specific behavior. Database Explorer is generally agnostic to the type of database being used, so this behavior is not being accounted for.

You can read more about this behavior in the PostgreSQL documentation:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html

The relevant section is 4.1.1, particularly the last paragraph.

Best Answer

Related Solutions

MATLAB: Am I getting the error “Beyond SQL_ACTIVE_STATEMENTS limit” when querying from SQL database with Database Toolbox

MATLAB: Do I get “JDBC Driver Error: ERROR: cross-database references are not implemented” with a PostgreSQL database

Related Question