Custom procedure fails to collect properties of a class parameter; why?

Question

OK, first of all, I'm a rookie with Caché, so the code will probably be poor, but...

I need to be able to query the Caché database in Java in order to rebuild source files out of the Studio.

I can dump methods etc without trouble, however there is one thing which escapes me... For some reason, I cannot dump the properties of parameter EXTENTQUERYSPEC from class Samples.Person (namespace: SAMPLES).

The class reads like this in Studio:

Class Sample.Person Extends (%Persistent, %Populate, %XML.Adaptor)
{

Parameter EXTENTQUERYSPEC = "Name,SSN,Home.City,Home.State";

// etc etc
} 

Here is the code of the procedure:

CREATE PROCEDURE CacheQc.getParamDesc(
    IN className VARCHAR(50),
    IN methodName VARCHAR(50),
    OUT description VARCHAR(8192),
    OUT type VARCHAR(50),
    OUT defaultValue VARCHAR(1024)
) RETURNS NUMBER LANGUAGE COS {
    set ref = className _ "||" _ methodName
    set row = ##class(%Dictionary.ParameterDefinition).%OpenId(ref)

    if (row = "") {
        quit 1
    }

    set description = row.Description
    set type = row.Type
    set defaultValue = row.Default

    quit 0
}

And the Java code:

private void getParamDetail(final String className, final String paramName)
    throws SQLException
{
    final String call
        = "{ ? = call CacheQc.getParamDesc(?, ?, ?, ?, ?) }";

    try (
        final CallableStatement statement = connection.prepareCall(call);
    ) {
        statement.registerOutParameter(1, Types.INTEGER);

        statement.setString(2, className);
        statement.setString(3, paramName);

        statement.registerOutParameter(4, Types.VARCHAR);
        statement.registerOutParameter(5, Types.VARCHAR);
        statement.registerOutParameter(6, Types.VARCHAR);

        statement.executeUpdate();

        final int ret = statement.getInt(1);

        // HERE
        if (ret != 0)
            throw new SQLException("failed to read parameter");

        System.out.println("    description: " + statement.getString(4));
        System.out.println("    type       : " + statement.getString(5));
        System.out.println("    default    : " + statement.getString(6));
    }
}

Now, for the aforementioned class/parameter pair the condition marked // HERE is always triggered and therefore the exception thrown... If I comment the whole line then I see that all three of OUT parameters are null, even defaultValue!

I'd have expected the latter to have the value mentioned in Studio...

So, why does this happen? Is my procedure broken somewhat?

Answer 1

In first you should check that you send right value for className and paramName, full name and in right case and. Why you choose storage procedures, when you can use select? And you can call your procedure in System Management Portal to see about probable errors.

select description, type,_Default "Default" from %Dictionary.ParameterDefinition where id='Sample.Person||EXTENTQUERYSPEC'

Your example, works well for me.

package javaapplication3;

import com.intersys.jdbc.CacheDataSource;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

public class JavaApplication3 {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws SQLException {

        CacheDataSource ds = new CacheDataSource();
        ds.setURL("jdbc:Cache://127.0.0.1:56775/Samples");
        ds.setUser("_system");
        ds.setPassword("SYS");
        Connection dbconnection = ds.getConnection();

        String call = "{ ? = call CacheQc.getParamDesc(?, ?, ?, ?, ?)}";
        CallableStatement statement = dbconnection.prepareCall(call);
        statement.registerOutParameter(1, Types.INTEGER);

        statement.setString(2, "Sample.Person");
        statement.setString(3, "EXTENTQUERYSPEC");

        statement.registerOutParameter(4, Types.VARCHAR);
        statement.registerOutParameter(5, Types.VARCHAR);
        statement.registerOutParameter(6, Types.VARCHAR);

        statement.executeUpdate();

        int ret = statement.getInt(1);

        System.out.println("ret = " + ret);

        System.out.println("     description: " + statement.getString(4));
        System.out.println("     type       : " + statement.getString(5));
        System.out.println("     default    : " + statement.getString(6));

    }

}

end result

ret = 0
     description: null
     type       : null
     default    : Name,SSN,Home.City,Home.State

UPD: try to change code of your procedure and add some debug like here

Class CacheQc.procgetParamDesc Extends %Library.RegisteredObject [ ClassType = "", DdlAllowed, Owner = {UnknownUser}, Not ProcedureBlock ]
{

ClassMethod getParamDesc(className As %Library.String(MAXLEN=50), methodName As %Library.String(MAXLEN=50), Output description As %Library.String(MAXLEN=8192), Output type As %Library.String(MAXLEN=50), Output defaultValue As %Library.String(MAXLEN=1024)) As %Library.Numeric(SCALE=0) [ SqlName = getParamDesc, SqlProc ]
{
    set ref = className _ "||" _ methodName
    set row = ##class(%Dictionary.ParameterDefinition).%OpenId(ref)
    set ^debug($i(^debug))=$lb(ref,row,$system.Status.GetErrorText($g(%objlasterror)))
    if (row = "") {
        quit 1
    }
    set description = row.Description
    set type = row.Type
    set defaultValue = row.Default
    quit 0
}

}

and after some test from java, check zw ^debug

SAMPLES>zw ^debug
^debug=4
^debug(3)=$lb("Sample.Person||EXTENTQUERYSPEC","31@%Dictionary.ParameterDefinition","ERROR #00: (no error description)")

Answer 2

Well, uh, I found the problem... Talk about stupid.

It happens that I had the Samples.Person class open in Studio and had made a "modification" to it; and deleted it just afterwards. Therefore the file was "as new"...

But the procedure doesn't seem to agree with this statement.

I closed the Studio where that file was, selected not to modify the "changes", reran the procedure again, and it worked...

Strangely enough, the SQL query worked even with my "fake modification". I guess it's a matter of some cache problem...