What is the method to retrieve a list of namespaces from Intersystems Caché using SQL?

Data and Databases
1

I am attempting to connect to an Intersystems Caché database using C# and I want to fetch all available namespaces. My current code snippet looks as follows:

namespace SampleApp
{
    internal class Program
    {
        static void Main(string[] args)
        {
            var connectionString = "Server=XXXXX;User=system;Password=sys;";
            var connection = new InterSystems.Data.CacheClient.CacheConnection(connectionString);

            connection.Open();

            var sqlCommand = connection.CreateCommand();
            sqlCommand.CommandText = "SELECT * FROM %SYS.Namespace_List()";

            var reader = sqlCommand.ExecuteReader();

            while (reader.Read())
            {
                // Process results
            }
        }
    }
}

Unfortunately, this leads to an error:

InterSystems.Data.CacheClient.CacheException: '[SQLCODE: <-99>:<Privilege violation>] [Location: <Prepare>] [%msg: <User system is not privileged for the operation>]'

It appears that I lack the necessary permissions. However, when I specify a namespace in my connection string, the query executes successfully. Can anyone suggest how I might retrieve a list of user-available namespaces using SQL?

2

To resolve the privilege issue, you would need to ensure that the user account you are using has adequate permissions to access system-level information such as the list of namespaces. As an alternative, if granting such privileges is not feasible or appropriate, consider using a custom stored procedure or class method that runs under a more privileged user account. This method can fetch and return the list of namespaces, which can then be queried from your limited access account. Implementing such an approach would require assistance from the database administrator to set up securely.

3

Persistent users often face this privilege prob when dealing with Intersystems Caché. You might ask your DBA to grant temporary permissions just to test, then revoke. Also, if you store usernames/namespaces elsewhere, could fetch it from there as part of a workaround.