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

databaseDatabase Toolboxsqlsql_active_statements

I am using the database toolbox to query from a SQL database with the "exec" and "fetch" functions. I sometimes can get the data back successfully but occasionally get empty data after fetch.

I print out the message from the cursor object after using the "exec" command and obtained the following error:

[Teradata][ODBC Teradata Driver] Beyond SQL_ACTIVE_STATEMENTS limit

Why am I getting this error?

Best Answer

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

Related Solutions

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.

MATLAB: Sql query find match

Mark - I suspect that your query could be more like

 sqlquery = ['select count(*) from db1 '...
             'where cname = ''' cname '''' ];

Note that since the name is a string, you should wrap it in quotes. For example, if cname were Mark, then the above SQL query would become

 sqlquery =
    select count(*) from db1 where cname = 'Mark'

Note that we use count to determine the number of records in the database that match on the name Mark. You could then execute this query as

 curs = exec(conn,sqlquery);
 curs = fetch(curs);
 curs.Data

where curs.Data would be an integer value that you would use to determine whether you should add the information to the database (if zero) or not (if non-zero).

Best Answer

Related Solutions

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

MATLAB: Sql query find match

Related Question