Informix error when processing routines about schemaspy HOT 6 CLOSED

I'm just eyeballing from my phone but it seems that the routine_name would be null.

I'm not super knowledgeable about Informix but it does SELECT p.procname || '(' || ifx_param_types(p.procid) || ')' AS routine_name

Which contains concatenation so it should never be null.

If you manually run

SELECT p.procname || '(' || ifx_param_types(p.procid) || ')' AS routine_name, 
case p.isproc when "f" then "Function" when "t" then "Procedure" end as routine_type, 
ifx_ret_types(p.procid) as dtd_identifier, 
l.langname as routine_body, 
b.data as routine_definition, b.seqno, 'N/A' as sql_data_access, case LOWER (p.mode) when "d" then "DBA" when "o" then "Owner" when "p" then "Protected" when "r" then "Restricted" when "t" then "trigger" end as security_type, 
p.variant as is_deterministic, "N/A" as routine_comment 
from sysprocedures p 
join sysroutinelangs l on p.langid = l.langid 
join sysprocbody b on p.procid = b.procid and b.datakey = 'T' 
order by p.procid, b.seqno

You don't get any rows with an empty routine_name column?

Wonder if it could be a strange join or anything related to access, if the queries are run with different accounts.

Since it seems that the query executes correctly but there is an issue with the returned data.

from schemaspy.

I had already ran the query manually and it works fine.
I has no procedure with procname or procid = NULL.
I'm currently reviewing the procedures to see if anything gives me a clue to which one might be the culprit.
I'm also going to try a different DB on the same server.
I can also try to drop all the procedures and recreate them by sets of 50 at a time to narrow it down to the offending code.

from schemaspy.

I've found the problem.
Several procedures had variables defined "like db:table.column".
This was messing with ifx_param_types and ifx_ret_types which returned null for the param_types

from schemaspy.

Could you create procedure that has the issue and post here so that I can have a look at how we can handle it. I assume that it's supported by informix so we should handle it in SchemaSpy.

from schemaspy.

I can create two situations:
In this case, table contrato does not exist. This makes ifx_param_types return null

create procedure proctest1(oTipoProposta like contrato.ctotipo, aProposta like contrato.ctonum )
        returning int, char(50), char(255), char(512) ;

        return 0, " ", "", "";

end procedure ;

In this case its a combination of 2 things:

• user "usr does not exist on the databse
• there's a variable defined with like to another database

create procedure "usr".proctest2( aTabela char(18), aDescricao integer)
        define oModulo     like dbadm:tabela.modulo;

        let oModulo = "leaseDBAdm";
end procedure;

from schemaspy.

Nice, thanks

from schemaspy.