Why does the Visual Query Builder fail when I try to access my SAS database with the Database Toolbox 3.0 (R14)?

I am able to access my database from the command line. When using the Visual Query Builder to select a table from my SAS database, however, I receive the following error message:

 ??? Function 'closeSqlStatement' is not defined for a firstargument of class 'double'.

 Error in ==> cursor.close at 20

 message = closeSqlStatement(cursor.Cursor,cursor.Statement);

 Error in ==> querybuilder>tablesvqb at 2476

   close(ex); 

 ??? Error while evaluating uicontrol Callback.

When I modify the preferences of QUERYBUILDER, I receive the following error:

 ??? Error using ==> database.exec

 [SAS][SAS ODBC Driver]Base table not found

 Error in ==> querybuilder>tablesvqb at 2472

   ex = exec(conn,['select * from ' table]);

 ??? Error while evaluating uicontrol Callback.

Why are these errors occurring?

Answer 1

[MathWorks Support Team]

This behavior might be due to the privileges associated with the login you are using and the differences between the command line and Visual Query Builder access methods.

When using the command line, you provide a schema name when referencing a table in the database, which allows you to access any table in the schema. To do this, use the following syntax in your SQL query:

SELECT * FROM SCHEMA_NAME.TABLE_NAME

The Visual Query Builder does not allow you to specify a schema. You can see all tables, but without a schema, you can only access tables that your privileges allow. For many database systems, the system administrator can configure a VIEW for you to access the tables (without providing a schema) in the Visual Query Builder. At the current time, there is no way to do that in SAS.

To work around this issue, ask your database or SAS administrator to give you the privileges needed to see and access the tables in the Visual Query Builder. Another workaround is to use the command line to access the database.