Temp table inside SP

aextra · #1 (**permalink**) 09-05-06, 18:03

I need to know if a given sp is using temp table without the keyword "with no log", I tried to write the following;

select count(*)
from sysprocbody s
where s.procid = (select procid from sysprocedures where lower(trim(procname)) = "test_sp")
and s.datakey = "T"
and (s.data like "%into%temp%" or s.data like "%create%temp%")
and not exists (Select * from sysprocbody where datakey = "T" and data like "%with%no%log" and procid = s.procid)

but the problem here is that if test_sp uses 2 or more temp table and in one of the temp table created he uses the keyword "with no log" it should return 1(to tell me that a temp table without with no log is created).

I would love to have this done in plain sql/sp if posible.

Thanks,
Aris

Tyveleyn · #2 (**permalink**) 09-06-06, 16:16

What do you want to accomplish with it? Do you want to use the query inside an application for or do you just want to check out one time only which SP's create these tables?
Because for the latter maybe you'd better use the dbschema utility to check it out. In a UNIX environment for example like:

Code:

dbschema -d database -f all | \
egrep -i 'into temp|create temp' | grep -i 'with no log'

In a DOS environment without PERL or grep for Windows it will be a bit more difficult.

But since you preferred a SQL query I think the only thing that can be done is to use a stored procedure to count the number of create/into statements and the number of 'with no log's. I can give the idea how I probably would do it, but that'll be in my next post, cause first I must log off to get something through VPN.

aextra · #3 (**permalink**) 09-06-06, 17:04

I need to put this inside an application(probably creating an SP is better).
I am currently creating an application that will monitor all the object being checkedin by the developers to see if they are following the standards that we put in place.

Thanks in advance for helping me on this.

Aris

Tyveleyn · #4 (**permalink**) 09-06-06, 17:17

Hi, here's the solution I had in mind. It's not impeccable though, unless you change the "source LVARCHAR" declaration into "source CHAR(x)" where x is the maximum length of the concatenated datakey 'T' data columns for each relevant stored procedure.

Code:

CREATE FUNCTION "informix".cnt(search LVARCHAR, source LVARCHAR)
    RETURNING SMALLINT;

    DEFINE i        SMALLINT;
    DEFINE j        SMALLINT;
    DEFINE l1       SMALLINT;
    DEFINE l2       SMALLINT;
    DEFINE nr	    SMALLINT;

    LET nr = 0;
    LET l1 = LENGTH(search);
    LET l2 = LENGTH(source);
    IF l1 > 0 AND l2 >= l1 THEN
        FOR i IN (1 TO l2)
            IF substr(search,1,1) = substr(source,i,1) THEN
                LET j = 2;
                IF l1 > 1 THEN
                    IF l1 - 1 > l2 - i THEN
                        RETURN nr;
                    END IF
                    WHILE j <= l1
                        IF substr(search,j,1) != substr(source,i+j-1,1) THEN
                            EXIT WHILE;
                        END IF
                        LET j = j + 1;
                    END WHILE
                END IF
                IF j > l1 THEN
                    LET nr = nr + 1;
                    LET i = i + j - 2;
                END IF
            END IF
        END FOR
    END IF
    RETURN nr;

END FUNCTION
DOCUMENT '';

Don't know whether this will work absolutely well; I altered there original a little, which was meant to return the first position of a substr in a string. It runs on an IDS 9.2 server and maybe the builtin "substr" function doesn't exist in an IDS 7.x, don't know right now.
But inside a query like yours this stored function can be invoked like:

Code:

AND cnt("temp",s.data) > cnt("with no log",s.data)

As I said there has to be done something more with the 'data' columns since usually stored procedures are written in more than one datarecord (of 256 bytes) in sysprocbody.

Hope this gives any useful hints anyway,
regards

BTW: If you're creating the application in a language with the ability of dynamical memory allocation for variables I think you should build a routine like this one in that application. Everything can be fixed then in a simple way.

PMASchmed · #5 (**permalink**) 10-04-06, 17:10

Quote:

Originally Posted by Tyveleyn

What do you want to accomplish with it? Do you want to use the query inside an application for or do you just want to check out one time only which SP's create these tables?
Because for the latter maybe you'd better use the dbschema utility to check it out. In a UNIX environment for example like:

Code:

dbschema -d database -f all | \
egrep -i 'into temp|create temp' | grep -i 'with no log'

In a DOS environment without PERL or grep for Windows it will be a bit more difficult.

But since you preferred a SQL query I think the only thing that can be done is to use a stored procedure to count the number of create/into statements and the number of 'with no log's. I can give the idea how I probably would do it, but that'll be in my next post, cause first I must log off to get something through VPN.

With DOS you can pipe | find or a findstr.

Internet Services

Web Development

Digital Marketing

Consumer Tech