UDRs: ROWNUM in Informix / ROWNUM em Informix

This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português

English version

ROWNUM again?!

This is more or less a FAQ. If you search the Internet for "rownum informix" you'll get a lot of links and several possible answers. I don't plan to give you a final answer, but I'll take advantage of this frequent topic to go back to something I do enjoy: User Defined functions in C language.
Most of the questions regarding ROWNUM appear in the form "does Informix support XPTO database system's ROWNUM", or "does Informix support ROW_NUMBER like database XPTO?" or even "does Informix allow the retrieval of the top N rows?" So, if you ever need something that resembles ROWNUM, the first thing you should do it to establish a clear understanding of what you need. Because the above three questions can represent three different needs, and for each need there may be a different answer. Let's see:

1. Does Informix support ROWNUM?
   Quick answer would be no. But usually ROWNUM referes to an Oracle "magic" column that's added to the result set and that represents the position of the row in the result set. Note that this number is associated before doing ORDER BY and other clauses. So the result may not be very intuitive. Many times the purpose of using it is just to restrict the number of rows returned. Something that in ANSI (2008) SQL would be done by using the FETCH FIRST n ROWS clause. And this can be done in Informix very simply by puting a "FIRST n" before the select list:
   
   SELECT FIRST n * FROM customer
   
   But if you want to associate an incremental number to each row we can implement other solutions.
   
   
2. Does Informix allow the retrieval of the top N rows?
   Yes. I just showed how to do it in the previous section. Just use the FIRST n clause. Also note that you can also use the SKIP n clause. This options are applied after all the other clauses in the SELECT. So you could use them for pagination (although it would require running the same query several times, which is not efficient).
   
   
3. Does Informix support row_number()?
   No. And there's not much we could do. The row_number clause or function is a complex construct that can associate an incremental number to a result set, but with very flexible options that allows the numbering to restart on specific conditions etc. In order to achieve this we would need to be able to change how the query is solved. And we can't. But read on...

So, the initial purpose of this article is to explain how we can create a simple function that will generate an incremental number for each row of the result set. Sometimes this can be useful, and you'll possibly be amazed by how simple it is. There are a few challenges though... And you must be aware of how it works, or the results may surprise you.

The implementation in C

You probably heard that we can create functions in C, Java and SPL (Stored Procedure Language), but most of us only used SPL. Informix extensibility through the user defined routines (UDR) is one of it's greatest strengths, but unfortunately it's also one of the least used features. This is unfortunate not only because we're wasting a lot of potential, but also because a greater usage would probably lead to greater improvement. I'm taking this opportunity to show you how simple it can be to create a function.
In order to do it, we must follow some rules, and we should know a bit about the available API. A good place to start understanding how we can create UDRs is the User's Defined Routines and Datatypes Developer's Guide. This explains the generic concepts and the kind of UDRs we can create. Then we can check the Datablade API Programer's Guide. This has a more technical description of several aspects (like memory management, processing input and output etc.). Finally we have the Datablade API Function Reference, for specific function help and description. But of course.... reading all this without practice is more or less useless. We could use a list of examples to get us going...

So, what I propose is to create a very simple C function that can be embedded in the engine. It's use is as easy as if it was a native function. And it's creation is really simple. The language used will be C. SPL is less flexible (although it can be very handy, useful and quick). Java doesn't have easy access to the internal API, but can eventually be even more flexible for certain tasks, although a bit more complex and slower. But it really depends on your background and needs.

Before we start I should mention a few points which are in fact the hardest part of the process:

1. IBM bundles a few scripts with the engine that are necessary to get us started. Inside $INFORMIXDIR/incl/dbdk there are a few scripts that are simple makefiles. We may need to adapt these to the platform or compiler we're using. In my system I have:
   
   • makeinc.gen
   This is a generic cross platform makefile used by the next one
   • makeinc.linux
   This is a makefile specific for your platform which includes the previous one
   • makeinc.rules
   This is a makefile containing basic compilation rules These scripts can and should be used by your own makefile
2. The function code needs to include some files and follow several rules.
3. After you create the function code, we need to compile it to object code and generate a dynamic loadable library. This is the way we make it available for the engine
4. After installing the library we have to create the function, telling the engine where it is available

Let's start by creating the code. As this is a crash course, I'll try to keep explanations to a minimum...:

1    /*
2    ------------------------------------------
3     include section
4    ------------------------------------------
5    */
6    #include <stdio.h>
7    #include <milib.h>
8    #include <sqlhdr.h>
9    #include <value.h>
10    
11    mi_integer ix_rownum( MI_FPARAM *fp)
12    {
13     mi_integer *my_udr_state, ret;
14    
15     /*
16     ----------------------------------------------------------
17     check to see if we've been called before on this statement
18     ----------------------------------------------------------
19     */
20     my_udr_state = (mi_integer *) mi_fp_funcstate(fp);
21     if ( my_udr_state == NULL )
22     {
23      // No... we haven't... Let's create the persistent structure
24      my_udr_state = (mi_integer *)mi_dalloc(sizeof(mi_integer),PER_STMT_EXEC);
25      if ( my_udr_state == (mi_integer *) NULL)
26      {
27       ret = mi_db_error_raise (NULL, MI_EXCEPTION, "Error in ix_rownum: Out of memory");
28       return(ret);
29      }
30      // We created it, so let's register it and initialize it to 1
31      mi_fp_setfuncstate(fp, (void *)my_udr_state);
32      (*my_udr_state)=1;
33     }
34     else
35     {
36      // If it's not the first time, then just increment the counter...
37      (*my_udr_state)++;
38     }
39     // return the counter...
40     return(*my_udr_state);
41    }

The important points are:

• Lines 1-10 are just the normal and required includes
• Line 11 is the function header. We define it as returning an mi_integer (on this functions we should use the mi_* datatypes). We accept one parameter which is a pointer to a function context
• Line 13 where we define auxiliary variables
• Line 20, we try to retrieve the previous value we kept stored in a persistent memory area. For that we use a datablade API function called mi_fp_funcstate
• Lines 21-30, if the previous call returned a NULL pointer we try to allocate (mi_dalloc) memory for keeping the counter. This may be one of the most important steps. We define that the persistence criteria is PER_STMT_EXEC. This means we're keeping the context only while we're executing the same statement. We test the result and raise an error if the allocation fails
• Line 31 we register the memory we have allocated as the function automatic parameter by calling mi_fp_setfuncstate()
• Line 32 is the counter initialization. On the first call we define it as 1
• Line 37 is the just the case for all the calls except the first. And in the generic case we just increment the counter
• Line 40 is just the return of the value after initializing or incrementing it

Except for some strange but powerful functions, the code is trivial. But how do we make it available to the SQL layer? First we need to compile it and generate the dinamic library. For that I created a simple makefile:

include $(INFORMIXDIR)/incl/dbdk/makeinc.linux

MI_INCL = $(INFORMIXDIR)/incl
CFLAGS = -DMI_SERVBUILD $(CC_PIC) -I$(MI_INCL)/public $(COPTS)
LINKFLAGS = $(SHLIBLFLAG) $(SYMFLAG)

all: ix_rownum

clean:
 rm *.udr *.o

ix_rownum: ix_rownum.udr
 @echo "Library genaration done"

ix_rownum.o: ix_rownum.c
 @echo "Compiling..."
 $(CC) $(CFLAGS) -o $@ -c $?

ix_rownum.udr: ix_rownum.o
 @echo "Creating the library..."
 $(SHLIBLOD) $(LINKFLAGS) -o $@ $?

Note the inclusion of the Linux makefile I mentioned earlier. If everything goes well, after I run make I will have a dynamic loadable library called ix_rownum.udr

panther@pacman.onlinedomus.com:informix-> make
Compiling...
cc -DMI_SERVBUILD -fpic -I/usr/informix/srvr1170uc4/incl/public -g -o ix_rownum.o -c ix_rownum.c
Creating the library...
gcc -shared -Bsymbolic -o ix_rownum.udr ix_rownum.o
Library genaration done
panther@pacman.onlinedomus.com:informix-> 

Having done this we need to create the funcion using SQL, as an external function. The syntax can be:

CREATE FUNCTION rownum() RETURNING INTEGER
WITH (VARIANT)
EXTERNAL NAME '/home/informix/udr_tests/ix_rownum.udr(ix_rownum)'
LANGUAGE C;

We're telling the engine to create a function called rownum, which does not receive any parameter and returns an INTEGER. We specify that it's written in C and the location. Note that I'm giving it the full dynamic library path (/home/informix/udr_tests/ix_rownum.udr) and the function name inside that library (a single library can contain more than one function). And I left the explanation for "WITH(VARIANT)" for last... The external function creation allows us to specify several properties for the functions. This one, VARIANT, tells the engine that the function may return different values when called with the same parameters. This is critical since we're not passing any parameters. If we told the engine it was NOT VARIANT it would only call it once. After that it would assume the return value was 1. This is an optimization, but in our case we don't want it, since it would break the function logic. VARIANT is the default and I just include it for clarity. You can find more about the function properties here.

Working with it

Well, after the above we are able to use ROWNUM() in SQL. Let's see a few examples:

-- Example 1:
SELECT customer_num, rownum() row_num
FROM customer;
customer_num     row_num
         101           1
         102           2
         103           3
         104           4
         105           5
         106           6
         107           7
         108           8
         109           9
[...]
-- Example 2
SELECT customer_num, rownum()
FROM customer
WHERE rownum() < 5;
customer_num     row_num

         101           1
         102           2
         103           3
         104           4

-- Example 3
SELECT
        FIRST 4
        customer_num, lname
FROM
        customer
ORDER BY lname DESC;

customer_num lname

         106 Watson
         121 Wallack
         105 Vector
         117 Sipes

-- Example 4
SELECT
        customer_num , lname, rownum() row_num
FROM
        customer
WHERE rownum() < 5
ORDER BY lname DESC;

customer_num lname               row_num

         102 Sadler                    2
         101 Pauli                     1
         104 Higgins                   4
         103 Currie                    3

-- Example 5
SELECT
        t1.*, rownum() row_num
FROM (SELECT customer_num, lname FROM customer ORDER BY lname DESC) as t1 
WHERE rownum() <5;

customer_num lname               row_num

         106 Watson                    1
         121 Wallack                   2
         105 Vector                    3
         117 Sipes                     4

Let's comment the above examples. There are very important aspects to consider.

• Example 1
  This is the simplest example. And works as expected
• Example 2
  Here we are using ROWNUM() also as a filter for the WHERE clause.
• Example 3
  This is a auxiliary example to show a possible problem. It's just a select of customer_num and lname ordered by this in a decremental order.
• Example 4
  Here we're trying the same thing, but using ROWNUM() to limit the number of rows. Note that this alters the result set. Why? Because ROWNUM() is applied immediately on the full table scan. The first 4 rows are retrieved and then the ORDER BY is applied. So using ROWNUM changes the the result set, because it's applied (in the WHERE clause) before the ORDER BY.
• Example 5
  If we wanted to reproduce the result set from example 3, but still add a row number we could use the syntax presented here

I mentioned above that we could not reproduce the functionality of the ROW_NUMBER() construct of the SQL standard. This allows us to specify an ORDER BY clause and a PARTITION BY clause.
The ORDER BY inside the ROW_NUMBER() tells the database to order the sequence numbers by the specified filed(s). The PARTITION BY tells it to "restart" the count each time the field specified changes (similar to the effect of GROUP BY and aggregate functions). Note that this ORDER BY does not influence the order of the result set.
If you're wondering if we could implement the same functionality using functions, the answer is "sort of..." But I'll leave that for another article.


Versão Portuguesa

ROWNUM outra vez?!

Isto é uma questão que pode fazer parte dos FAQs. Se pesquisar na Internet por "rownum informix" vai obter uma série de links e algumas possíveis respostas. Não espero dar uma resposta definitiva, mas vou aproveitar este tema para voltar a um assunto que me agrada bastante: Funções definidas pelo utilizador em C.
Muitas das questões em torno do ROWNUM parecem numa das formas "o Informix suporta o ROWNUM tal como a base de dados XPTO?", ou "o Informix suporta o ROW_NUM como a base de dados XPTO?" ou ainda "o Informix suporta obter apenas as N primeiras linhas de uma query?". Assim se as suas necessidades parecem ir ao encontro do ROWNUM a primeira coisa a fazer é perceber exactamente o que se pretende. Necessidades diferentes podem ter soluções diferentes. Vamos ver:

1. O Informix suporta o ROWNUM?
   A resposta rápida seria não. Mas habitualmente a referência a ROWNUM diz respeita a uma coluna "mágica" do Oracle, que é adicionada ao conjunto de resultados e que representa a posição de cada linha nesse mesmo conjunto. Note que este número é adicionado antes do processamento do ORDER BY e outras cláusulas e isso pode tornar o resultado pouco intuitivo. Muitas vezes é usado apenas para limitar o número de linhas obtido. Algo que em ANSI (2008) SQL seria feito com a cláusula FETCH FIRST n ROWS. E isto pode ser feito em Informix muito simplesmente com um FIRST n antes da lista de colunas:
   
   SELECT FIRST n * FROM customer
   
   Mas se o que pretende é associar um número incremental a cada linha podemos implementar outras soluções.
   
   
2.  O Informix suporta obter apenas as primeiras N linhas?
   Sim. Mostrei como no parágrafo anterior. Basta usar a cláusula FIRST N. Note-se que podemos também usar a cláusula SKIP n. Estas opções são aplicadas após todas as outras cláusulas do SELECT, nomeadamente o ORDER BY. Podem portanto ser usadas para paginação de resultados, embora isso leve à execução da mesma query várias vezes, o que não será muito eficiente
   
   
3. O Informix suporta ROW_NUMBER?
   Não. E não há muito que possamos fazer. A cláusula ROW_NUMBER é complexa. Permite associar um sequência incremental de valores a um conjunto de resultados, mas com opções muito flexíveis que permitem ordenar a sequência segundo um critério e recomeçar do valor 1 sempre que certas colunas mudam. Para conseguir fazer isto teríamos de conseguir controlar a forma como o motor resolve as queries. E tal não é possível... Mas já vamos ver o que se pode fazer...

Sendo assim, o proprósito inicial deste artigo é explicar como criar uma função simples que gera un número sequencial para cada linha do conjunto de resultados. Isto pode ser útil e possivelmente ficará admirado com a simplicidade de o fazer. No entanto existem alguns desafios.... E tem de estar atento à forma como funciona, ou os resultados podem parecer inesperados.

A implementação em C

Já deve ter ouvido ou lido que podemos criar funções em C, Java e SPL (Stored Procedure Language), mas a maioria de nós apenas lidou com SPL. A capacidade de extensão do Informix através das funções definidas pelo utilizador (UDRs) é uma das suas melhores vantagens, mas infelizmente é também uma das menos usadas. Isto é mau não só porque estamos a desperdiçar muito potencial, mas também porque uma maior utilização levaria certamente a mais melhorias e desenvolvimentos. Vou aproveitar esta oportunidade para mostrar o quão simples pode ser criar uma função.
Para o fazer, temos de seguir algumas regras e devemos saber alguma coisa sobre a API disponível. Um bom sítio para começar a entender como podemos criar UDRs é o User's Defined Routines and Datatypes Developer's Guide. Isto explica os conceitos genéricos e os tipos de UDRs que podemos criar. Depois podemos consultar o Datablade API Programer's Guide. Este contém uma descrição mais técnica sobre vários aspectos (como gestão de memória, processamento de input e output etc.). Por último temos o Datablade API Function Reference, para informação e ajuda em funções específicas. Mas claro... ler isto tudo sem praticar é mais ou menos inútil. Seria bom termos uma lista de exemplos que nos permitissem arrancar....

Assim o que proponho é criar uma função muito simples em C que possa ser embebida no motor. O seu uso é tão fácil como se fosse uma função nativa do motor. E a sua criação é bastante simples. A linguagem usada será C, pois SPL é menos fléxivel (embora possa ser bastante prática, útil e rápida). O Java não tem o acesso tão fácil às funções da API interna, mas pode ainda ser mais fléxivel para algumas tarefas, embora possa ser mais complexo e lento. Mas a escolha deverá depender sempre das nossas necessidades e mesmo do nosso background com cada uma das linguagens.

Antes de começar devo referir alguns pontos que na verdade serão os mais difícieis do processo:

1. A IBM inclui alguns scripts no motor que são necessários para arrancarmos. Dentro de $INFORMIXDIR/incl/dbdk existem alguns makefiles simples. Poderá ser necessário adaptá-los à plataforma e/ou compilador que vamos usar.No meu sistema tenho:
   
   • makeinc.gen
   Um makefile genérico (várias paltaformas) usado pelo próximo
   • makeinc.linux
   Makefile específico para Linux que referencia o anterior
   • makeinc.rules
   Makefile com regras genéricas de compilação Estes scripts podem e devem ser usados pelo nosso próprio makefile
2. O código da função tem de incluir alguns ficheiros e seguir determinadas regras
3. Depois de criarmos o código da função temos de a compilar para código objecto e a partir deste gerar uma biblioteca dinâmica. Esta será a forma de disponibilizar a função ao motor
4. Depois de instalar a biblioteca temos de usar SQL para criar a função indicando ao motor onde a mesma se encontra

Vamos começar por criar o código. Como isto é um algo do tipo "mãos na massa", vou tentar manter as explicações no mínimo...:

1    /*
2    ------------------------------------------
3     secao de includes
4    ------------------------------------------
5    */
6    #include <stdio.h>
7    #include <milib.h>
8    #include <sqlhdr.h>
9    #include <value.h>
10    
11    mi_integer ix_rownum( MI_FPARAM *fp)
12    {
13     mi_integer *my_udr_state, ret;
14    
15     /*
16     ----------------------------------------------------------
17     Ver se já fomos chamados antes nesta instrução SQL
18     ----------------------------------------------------------
19     */
20     my_udr_state = (mi_integer *) mi_fp_funcstate(fp);
21     if ( my_udr_state == NULL )
22     {
23      // Não... não fomos... Vamos criar a estrutura persistente
24      my_udr_state = (mi_integer *)mi_dalloc(sizeof(mi_integer),PER_STMT_EXEC);
25      if ( my_udr_state == (mi_integer *) NULL)
26      {
27       ret = mi_db_error_raise (NULL, MI_EXCEPTION, "Erro em ix_rownum: Memória insuficiente");
28       return(ret);
29      }
30      // Já criámos, portanto vamos registar e inicializar a 1
31      mi_fp_setfuncstate(fp, (void *)my_udr_state);
32      (*my_udr_state)=1;
33     }
34     else
35     {
36      // Se não é a primeira vez vamos incrementar o contador...
37      (*my_udr_state)++;
38     }
39     // retornamos o contador...
40     return(*my_udr_state);
41    }

Os pontos importantes são:

• Linhas 1-10 são os includes normais e necessários
• Linha 11 é o cabeçalho da função. Definimos como retornando um mi_integer (nestas funções devemos usar os tipos de dados mi_*). Aceitamos um parâmetro que será um ponteiro para uma estrutura de contexto da função. Este parâmetro não será visível na assinatura "externa" da função (ao nível do SQL)
• Linha 13 onde definimos variáveis auxiliares
• Linha 20, tentamos obter o valor anterior que mantivemos na estrutura persistente de memória. Para isso usamos uma função da API dos datblades chamada mi_fp_funcstate
• Linhas 21-30, se a chamada anterior devolver um ponteiro NULL, tentamos alocar (mi_dalloc) memória para manter o contador. Este será um dos passos mais importantes. Definimos que o critério de persistência é PER_STMT_EXEC. Isto significa que mantemos o contexto apenas durante a execução da mesma instrução SQL. Testamos o resultado e criamos uma excepção de a alocação falhar.
• Linha 31 registamos a memória alocada anteriormente como o parâmetro automático da função através da chamada mi_fp_setfuncstate()
• Linha 32 é a inicialização do contador. Na primeira chamada definimo-lo como 1
• Linha 37 é apenas o caso geral, para todas as chamadas excepto a primeira. E no caso geral apenas incrementamos o contador
• Linha 40 é o retorno da função, ou seja o valor do contador após inicialização ou incremento

À excepção de algumas funções estranhas, mas poderosas, o código é trivial. Mas como o disponibilizamos à camada de SQL? Antes de mais necessitamos de o compilar e gerar a biblioteca dinâmica. Para isso criamos um makefile simples:

include $(INFORMIXDIR)/incl/dbdk/makeinc.linux

MI_INCL = $(INFORMIXDIR)/incl
CFLAGS = -DMI_SERVBUILD $(CC_PIC) -I$(MI_INCL)/public $(COPTS)
LINKFLAGS = $(SHLIBLFLAG) $(SYMFLAG)

all: ix_rownum

clean:
 rm *.udr *.o

ix_rownum: ix_rownum.udr
 @echo "Geração da biblioteca completa..."

ix_rownum.o: ix_rownum.c
 @echo "Compilando..."
 $(CC) $(CFLAGS) -o $@ -c $?

ix_rownum.udr: ix_rownum.o
 @echo "Creando a biblioteca..."
 $(SHLIBLOD) $(LINKFLAGS) -o $@ $?

Note-se a inclusão do makefile Linux que mencionei anteriormente. Se tudo correr bem, após corrermos make teremos uma biblioteca dinâmica chamada  ix_rownum.udr

panther@pacman.onlinedomus.com:informix-> make
Compilando...
cc -DMI_SERVBUILD -fpic -I/usr/informix/srvr1170uc4/incl/public -g -o ix_rownum.o -c ix_rownum.c
Creando a biblioteca...
gcc -shared -Bsymbolic -o ix_rownum.udr ix_rownum.o
Geração da biblioteca completa...
panther@pacman.onlinedomus.com:informix-> 

Após termos feito isto, necessitamos de criar a função usando SQL, como uma função externa. A sintaxe será:

CREATE FUNCTION rownum() RETURNING INTEGER
WITH (VARIANT)
EXTERNAL NAME '/home/informix/udr_tests/ix_rownum.udr(ix_rownum)'
LANGUAGE C;

Estamos a dizer ao motor para criar uma função chamada rownum, a qual não recebe nenhum parâmetro, e retorna um INTEGER. Especificamos que é escrita em C e qual a localização. Note-se que estou a dar o caminho completo da biblioteca (/home/informix/udr_tests/ix_rownum.udr) e o nome da função dentro da biblioteca (uma única biblioteca pode conter mais que uma função). Deixei a explicação para "WITH(VARIANT)" para último lugar... A criação de uma função externa permite-nos especificar várias propriedades para as funções. Esta, VARIANT, indica ao motor que a função pode retornar valores diferentes quando chamada duas ou mais vezes com os mesmos parâmetros. Isto é critico dado que não estamos a passar nenhum parâmetro. Se indicássemos ao motor que a função era NOT VARIANT apenas a chamaria uma vez. Depois disso assumiria que o valor de retorno era 1. Isto é uma optimização, mas no nosso caso não queremos que tal aconteça, dado que quebraria a lógica da função. VARIANT é o valor pré-definido, e apenas o incluí por clareza. Pode aprender mais sobre as propriedades das funções aqui:

Trabalhando com a função

Depois do exposto acima, podemos usar ROWNUM() no SQL. Vejamos alguns exemplos:

-- Exemplo 1:
SELECT customer_num, rownum() row_num
FROM customer;
customer_num     row_num
         101           1
         102           2
         103           3
         104           4
         105           5
         106           6
         107           7
         108           8
         109           9
[...]
-- Exemplo 2
SELECT customer_num, rownum()
FROM customer
WHERE rownum() < 5;
customer_num     row_num

         101           1
         102           2
         103           3
         104           4

-- Exemplo 3
SELECT
        FIRST 4
        customer_num, lname
FROM
        customer
ORDER BY lname DESC;

customer_num lname

         106 Watson
         121 Wallack
         105 Vector
         117 Sipes

-- Exemplo 4
SELECT
        customer_num , lname, rownum() row_num
FROM
        customer
WHERE rownum() < 5
ORDER BY lname DESC;

customer_num lname               row_num

         102 Sadler                    2
         101 Pauli                     1
         104 Higgins                   4
         103 Currie                    3

-- Exemplo 5
SELECT
        t1.*, rownum() row_num
FROM (SELECT customer_num, lname FROM customer ORDER BY lname DESC) as t1 
WHERE rownum() <5;

customer_num lname               row_num

         106 Watson                    1
         121 Wallack                   2
         105 Vector                    3
         117 Sipes                     4

Vamos comentar os exemplos acima. Há aspectos muito importantes a considerar:

• Exemplo1
  Este é o exemplo mais simples. Funciona como se esperaria
• Exemplo 2
  Aqui estamos a usar o ROWNUM() também como filtro da cláusula WHERE
• Exemplo 3
  Este é um exemplo auxiliar para ajudar a demonstrar um possível problema. É apenas um SELECT do customer_num e lname ordenado por este de forma descrescente
• Exemplo 4
  Aqui estamos a tentar a mesma coisa, mas usando o ROWNUM() para limitar o número de linhas. Note-se que isto altera o conjunto de resultados. Porquê? Porque o ROWNUM() é aplicado imediatamente durante o full table scan que é feito para resolver a query. As primeiras quatro linhas são obtidas, e depois o ORDER BY é aplicado. Portanto o uso do ROWNUM() altera o resultado porque é aplicado (na cláusula WHERE) antes do ORDER BY
• Exemplo 5
  Se quiséssemos reprodudir o resultado do exemplo 3, mas ainda assim adicionar um número de linha a cada elemento dos resultados, poderíamos usar a sintaxe apresentada aqui

Referi acima que não poderíamos reproduzir a funcionalidade da instrução ROW_NUMBER(), do standard SQL. Esta permite-nos especificar uma cláusula de ORDER BY e outra de PARTITION BY.
O ORDER BY dentro do ROW_NUMBER() indica ao motor que deve ordenar a sequência de números pelos campos indicados. O PARTITION BY diz-lhe para recomeçar a numeração cada vez que o campo indicado mudar de valor (semelhante ao efeito de um GROUP BY em agregados). Note-se que este ORDER BY não afecta a ordem dos resultados apresentados.
Se está a imaginar se não poderíamos implementar uma funcionalidade semelhante usando funções, a resposta é "mais ou menos...". Mas vou deixar isso para outro possível artigo.

UDRs: In transaction? / Em transacção?

This article is written in English and Portuguese
Este artigo está escrito em Inglês e Português

English version:

Introduction

I just checked... This will be post 100!!!... I've never been so active in the blog... We have Panther (full of features that I still haven't covered), I did some work with OAT and tasks that I want to share, and besides that I've been trying some new things... Yes... Although I've been working with Informix for nearly 20 years (it's scaring to say this but it's true...) there are aspects that I usually don't work with. I'd say the one I'm going to look into today is not used by the vast majority of the users. And that's a shame because:

1. It can solve problems that we aren't able to solve in any other way
2. If it was more used, it would be improved faster

Also, many people think that this is what marked the decline of the Informix company. You probably already figured out that I'm talking about extensibility. To recall a little bit of history, in 1995, Informix had the DSA architecture in version 7. And it acquired the Illustra company, founded by Michael Stonebraker and others. Mr. Stonebraker already had a long history of innovation (which he kept improving up to today) and he stayed with Informix for some years. All the technology around Datablades and extensibility in Informix comes from there... Informix critics say that the company got so absorbed in the extensibility features (that it believed would be the "next wave") that it loosed the market focus. Truth is that the extensibility never became a mainstream feature neither in Informix or in other databases, and all of them followed Informix launch of Universal Server (1996): Oracle, IBM DB2 etc.

But, this article will not focus on the whole extensibility concept. It would be impossible and tedious to try to cover it in one blog article. Instead I'll introduce one of it's aspects: User Defined Routines (UDRs), and in particular routines written using the C language.

There is a manual about UDRs, and I truly recommend that you read it. But here I'll follow another approach: We'll start with a very simple problem that without C UDRs would be impossible to solve, define a solution for it, and go all the way to implement it and use it with an example.

The problem

Have you ever faced a situation where you're writing a stored procedure in SPL, and you want to put some part of it inside a transaction, but you're afraid that the calling code is already in a transaction?
You could workaround this by initiating the transaction and catching the error (already in transaction) with an ON EXCEPTION block.
But this may have other implications (ON EXCEPTION blocks are tricky when the procedure is called from a trigger). So it would be nice to check if the session is already in a transaction. A natural way to do that would be a call to DBINFO(...), but unfortunately current versions (up to 11.7.xC1) don't allow that. Meaning there is no DBINFO() parameter that makes it return that information.

Solution search

One important part of Informix extensibility is the so called Datablade API. It's a set of programmable interfaces that we can use inside Datablades code and also inside C UDRs. The fine infocenter has a reference manual for the Datablade API functions. A quick search there for "transaction" makes a specific function come up: mi_transaction_state()
The documentation states that when calling it (no parameters needed) it will return an mi_integer (let's assume integer for now) type with one of these values:

• MI_NO_XACT
  meaning we don't have a transaction open
  
• MI_IMPLICIT_XACT
  meaning we have an implicit transaction open (for example if we're connected to an ANSI mode database)
  
• MI_EXPLICIT_XACT
  meaning we have an explicit transaction open

This is all we need conceptually.... Now we need to transform ideas into runnable code!

Starting the code

In order to implement a C UDR function we should proceed through several steps:

1. Create the C code skeleton
2. Create the C code function using the Datablade API
3. Create a makefile that has all the needed instructions to generate the executable code in a format the engine can use
4. Compile the code
5. Use SQL to define the new function, telling the engine where it can find the function and the interface to call it, as well as the language and other function attributes
6. Test it!

Create the C code skeleton

Informix provides a tool called DataBlade Developers Kit (DBDK) which includes several components: Blade Manager, Blade Pack and Bladesmith. Blade Manager allows us to register the datablades against the databases, the Blade Pack does the "packaging" of all the Datablades files (executable libraries, documentation files, SQL files etc.) that make up a datablade. Finally Bladesmith helps us to create the various components and source code files. It's a development tool that only runs on Windows but can also be used to prepare files for Unix/Linux. For complex projects it may be a good idea to use Bladesmith, but for this very simple example I'll do it by hand. Also note I'm just creating a C UDR. These tools are intended to deal with much more complex projects. A Datablade can include new datatypes, several functions etc.
So, for our example I took a peek at the recent Informix Developer's Handbook to copy the examples.

Having looked at the examples above, it was easy to create the C code:

/*
This simple function returns an integer to the calling SQL code with the following meaning:
0 - We're not within a transaction
1 - We're inside an implicit transaction
2 - We're inside an explicit (BEGIN WORK...) transaction
-1 - Something unexpected happened!
*/

#include <milib.h>

mi_integer get_transaction_state_c( MI_FPARAM *fp)
{
mi_integer i,ret;
i=mi_transaction_state();
switch(i)
{
  case MI_NO_XACT:
          ret=0;
          break;
  case MI_IMPLICIT_XACT:
          ret=1;
          break;
  case MI_EXPLICIT_XACT:
          ret=2;
          break;
  default:
          ret=-1;
}
return (ret);
}

I've put the above code in a C source file called get_transaction_state_c.c

Create the makefile

Again, for the makefile I copied some examples and came up with the following. Please consider this as an example only. I'm not an expert on makefile building and this is just a small project.

include $(INFORMIXDIR)/incl/dbdk/makeinc.linux

MI_INCL = $(INFORMIXDIR)/incl
CFLAGS = -DMI_SERVBUILD $(CC_PIC) -I$(MI_INCL)/public $(COPTS)
LINKFLAGS = $(SHLIBLFLAG) $(SYMFLAG)


all: get_transaction_state.so

clean:
 rm -f get_transaction_state.so get_transaction_state_c.o


get_transaction_state_c.o: get_transaction_state_c.c
 $(CC) $(CFLAGS) -o $@ -c $?

get_transaction_state.so: get_transaction_state_c.o
 $(SHLIBLOD) $(LINKFLAGS) -o $@ $?

Note that this is a GNU Make makefile. The first line includes a makefile that IBM supplies with Informix. It basically contains variables or macro definitions. You should adapt the include directive to your system (the name of the makefile can vary with the platform) and make sure that the variables I use are also defined in your system base makefile.
After that I define some more variables and I create the makefile targets. I just want it to build the get_transaction_state.so dynamically loadable library and for that I'm including the object (get_transaction_state_c.o) generated from my source code (get_transaction_state_c.c). Pretty simple if you have basic knowledge about makefiles

Compile the code

Once we have the makefile we just need to run a simple command to make it compile:

cheetah@pacman.onlinedomus.net:informix-> make
cc -DMI_SERVBUILD -fpic -I/usr/informix/srvr1150uc7/incl/public -g -o get_transaction_state_c.o -c get_transaction_state_c.c
gcc -shared -Bsymbolic -o get_transaction_state.so get_transaction_state_c.o
cheetah@pacman.onlinedomus.net:informix->

The two commands run are the translation of the macros/variables defined in the makefile(s) and they simply compile the source code (1st command) and then generate the dynamic loadable library. If all goes well (as it naturally happened in the output above), we'll have a library on this location, ready for usage by Informix:

cheetah@pacman.onlinedomus.net:informix-> ls -lia *.so
267913 -rwxrwxr-x 1 informix informix 5639 Nov 23 22:06 get_transaction_state.so
cheetah@pacman.onlinedomus.net:informix-> file *.so
get_transaction_state.so: ELF 32-bit LSB shared object, Intel 80386, version 1 (GNU/Linux), dynamically linked, not stripped
cheetah@pacman.onlinedomus.net:informix->

Use SQL to define the function

Now that we have executable code, in the form of a dynamic loadable library, we need to instruct Informix to use it. For that we will create a new function, telling the engine that it's implemented in C language and the location where it's stored. For that I created a simple SQL file:

cheetah@pacman.onlinedomus.net:informix-> ls *.sql
get_transaction_state_c.sql
cheetah@pacman.onlinedomus.net:informix-> cat get_transaction_state_c.sql
DROP FUNCTION get_transaction_state_c;

CREATE FUNCTION get_transaction_state_c () RETURNING INTEGER
EXTERNAL NAME '/home/informix/udr_tests/get_transaction_state/get_transaction_state.so'
LANGUAGE C;
cheetah@pacman.onlinedomus.net:informix->

So, let's run it...:

cheetah@pacman.onlinedomus.net:informix-> dbaccess stores get_transaction_state_c.sql

Database selected.


674: Routine (get_transaction_state_c) can not be resolved.

111: ISAM error:  no record found.
Error in line 1
Near character position 36

Routine created.


Database closed.

cheetah@pacman.onlinedomus.net:informix->

Note that the -674 error is expected, since my SQL includes a DROP FUNCTION. If I were using 11.7 (due to several tests I don't have it ready at this moment) I could have used the new syntax "DROP IF EXISTS...".

So, after this step I should have a function callable from the SQL interface with the name get_transaction_state_c(). It takes no arguments and returns an integer value.

Test it!

Now it's time to see it working. I've opened a session in stores database and did the following:

1. Run the function. It returned "0", meaning no transaction was opened.
2. Than I opened a transaction and run it again. It returned "2", meaning an explicit transaction was opened
3. I closed the transaction and run the function by the third time. As expected it returned "0"

Here is the output:

cheetah@pacman.onlinedomus.net:informix-> dbaccess stores -

Database selected.

> EXECUTE FUNCTION get_transaction_state_c();


(expression)

     0

1 row(s) retrieved.

> BEGIN WORK;

Started transaction.

> EXECUTE FUNCTION get_transaction_state_c();


(expression)

     2

1 row(s) retrieved.

> ROLLBACK WORK;

Transaction rolled back.

> EXECUTE FUNCTION get_transaction_state_c();


(expression)

     0

1 row(s) retrieved.

>

We haven't seen it returning "1". That happens when we're inside an implicit transaction. This situation can be seen if we use the function in an ANSI mode database. For that I'm going to use another database (stores_ansi), and naturally I need to create the function there (using the previous SQL statements). Then I repeat more or less the same steps and the result is interesting:

cheetah@pacman.onlinedomus.net:informix-> dbaccess stores_ansi -

Database selected.

> EXECUTE FUNCTION get_transaction_state_c();


(expression)

      0

1 row(s) retrieved.

> SELECT COUNT(*) FROM systables;


 (count(*))

         83

1 row(s) retrieved.

> EXECUTE FUNCTION get_transaction_state_c();


(expression)

      1

1 row(s) retrieved.

>

If you notice it, the first execution returns "0". Since I have not done any operations there is no transaction opened. But just after a simple SELECT, the return is "1", meaning an implicit transaction is opened. This has to do with the nature and behavior of ANSI mode databases.
If you use them and you intend to use this function you must take that into consideration. Or you could simply map the "1" and "2" output of the mi_transaction_state() function return into simply a "1". This would signal that a transaction is opened (omitting the distinction between implicit and explicit transactions).

Final considerations

Please keep in mind that this article serves more as a light introduction to the C language UDRs than to solve a real problem. If you need to know if you're already in transaction (inside a stored procedure for example) you can use this solution, but you could as well try to open a transaction and capture and deal with the error inside an ON EXCEPTION block.

Also note that if this is a real problem for your applications, even if you're inside an already opened transaction, you can make your procedure code work as a unit, by using the SAVEPOINT functionality introduced in 11.50. So, in simple pseudo-code it would be done like this:

1. Call get_transaction_state_c()
2. If we're inside a transaction then set TX="SVPOINT" and create a savepoint called "MYSVPOINT" and goto 4)
3. If we're not inside a transaction than set TX="TX" and create one. Goto 4
4. Run our procedure code
5. If any error happens test TX variable. Else goto 8
   
6. If TX=="TX" then ROLLBACK WORK. Return error
7. Else, if TX=="SVPOINT" then ROLLBACK WORK TO SAVEPOINT 'MYSVPOINT'. Return error
8. Return success

After this introduction, I hope to be able to write a few more articles related to this topic. The basic idea is that sometimes it's very easy to extend the functionality of Informix. And I feel that many customers don't take advantage of this.
Naturally, there are implications on writing C UDRs. The example above is terribly simple, and it will not harm the engine. But when you're writing code that will be run by the engine a lot of questions pop up.... Memory usage, memory leaks, security stability... But there are answers to this concerns. Hopefully some of them (problems and solutions) will be covered in future articles.


Versão Portuguesa:

Introdução

Acabei de verificar.... Este será o centésimo artigo!!! Nunca estive tão activo no blog... Temos a versão Panther (11.7) (cheia de funcionalidades sobre as quais ainda não escrevi), fiz algum trabalho com tarefas do OAT que quero partilhar, e para além disso tenho andado a testar coisas novas... Sim... Apesar de já trabalhar com Informix há perto de 20 anos (é assustador dizer isto, mas é verdade...) há áreas de funcionalidade com as quais não lido habitualmente. Diria que aquela sobre a qual vou debruçar-me hoje não é usada pela maioria dos utilizadores. E isso é uma pena porque:

1. Permite resolver problemas que não têm outra solução
2. Se fosse mais usada seria melhorada mais rapidamente

Adicionalmente, existe muita gente que pensa que isto foi o que marcou o declínio da empresa Informix. Possivelmente já percebeu que estou a falar da extensibilidade. Para relembrar um pouco de história, em 1995, a Informix tinha a arquitectura DSA (Dynamic Scalable Architecture) na versão 7. E adquiriu a empresa Illustra, fundada por Michael Stonebraker e outros. O senhor Stonebraker já tinha um longo passado de inovação (que prossegue ainda actualmente) e permaneceu na Informix durante alguns anos. Toda a tecnologia à volta dos datablades e extensibilidade teve aí a sua origem.... Os críticos da Informix dizem que a companhia ficou tão absorvida pelas funcionalidades de extensibilidade (que acreditava serem a próxima "vaga") que perdeu o foco do mercado. A verdade é que a extensibilidade nunca se tornou em algo generalizado nem no Informix nem em outras bases de dados, e todas elas seguiram o lançamento do Informix Universal Server (1996): Oracle, IBM DB2 etc.

Mas este artigo não irá focar todo o conceito de extensibilidade. Seria impossível e entediante tentar cobrir tudo isso num artigo de blog. Em vez disso vou apenas introduzir um dos seus aspectos: User Defined Routines (UDRs), e em particular rotinas escritas usando linguagem C.

Existe um manual que cobre os UDRs, e eu recomendo vivamente a sua leitura. Mas aqui seguirei outra abordagem: Começarei com um problema muito simples, que sem um UDR em C seria impossível de resolver, definirei uma solução para o mesmo, e prosseguirei até à implementação e utilização da solução com um exemplo.

O problema

Alguma vez esteve numa situação em que estivesse a escrever um procedimento em SPL, e quisesse colocar parte dele dentro de uma transacção, mas tivesse receio que o código que chama o procedimento já estivesse com uma transacção aberta?

Poderia contornar o problema iniciando uma transacção e apanhando o erro (already in transaction) com um bloco de ON EXCEPTION

Mas isto teria outras implicações (os blocos de ON EXCEPTION podem criar problemas se o procedimento for chamado de um trigger). Portanto seria bom poder verificar se a sessão já está no meio de uma transacção. Uma forma natural de o fazer seria recorrer à função DBINFO(...), mas infelizmente as versões actuais (até à 11.7.xC1) não permitem isso. Ou seja, não há nenhum parâmetro desta função que permita obter a informação que necessitamos.

Pesquisa da solução

Uma parte importante da extensibilidade no Informix é a chamada Datable API. É um conjunto de interfaces programáveis que podemos usar dentro de datablades e também dentro de UDRs em C. O infocenter tem um manual de referência das funções do Datablade API. Uma pesquisa rápida por "transaction" faz aparecer uma função: mi_transaction_state()

A documentação indica que quando a chamamos (não requer parâmetros) irá retornar um valor do tipo mi_integer (equivale a um inteiro) com um destes valores:

• MI_NO_XACT
  significa que não temos uma transacção aberta
  
• MI_IMPLICIT_XACT
  significa que temos uma transacção implícita aberta (por exemplo se estivermos conectados a uma base de dados em modo ANSI)
  
• MI_EXPLICIT_XACT
  significa que temos uma transacção explícita aberta

Isto é tudo o que necessitamos conceptualmente.... Agora precisamos de transformar uma ideia em código executável!

Começando o código

Para implementarmos um UDR em C necessitamos de efectuar vários passos:

1. Criar o esqueleto do código C
2. Criar a função com código C usando o datablade API
3. Criar um makefile que tenha todas as instruções necessárias para gerar o código executável num formato que possa ser usado pelo motor
4. Compilar o código
   
5. Usar SQL para definir uma nova função, indicando ao motor onde pode encontrar a função, o interface para a chamar bem como a linguagem usada e outros atributos da função
6. Testar!

Criar o código em C

O Informix fornece uma ferramenta chamada DataBlade Developers Kit (DBDK) que incluí vários componentes: Blade Manager, Blade Pack e Bladesmith. O Blade Manager permite-nos registar datablades em bases de dados, o Blade Pack faz o "empacotamento" de todos os ficheiros de um datablade (bibliotecas executáveis, ficheiros de documentação, ficheiros SQL, etc.). Finalmente o Bladesmith ajuda-nos a criar vários componentes e código fonte. É uma ferramenta de desenvolvimento que apenas corre em Windows mas que pode ser usado para preparar ficheiros para Unix/Linux. Para projectos complexos será boa ideia usar o Bladesmith mas para este exemplo simples farei tudo à mão. Apenas estou a criar um UDR em C. Estas ferramentas destinam-se a lidar com projectos muito mais complexos. Um Datablade pode incluir novos tipos de dados, várias funções etc.
Assim, para o nosso exemplo dei uma espreitadela ao recente Informix Developer's Handbook para copiar alguns exemplos.

Depois de ver os exemplos referidos, foi fácil criar o código em C:

/*
This simple function returns an integer to the calling SQL code with the following meaning:
0 - We're not within a transaction
1 - We're inside an implicit transaction
2 - We're inside an explicit (BEGIN WORK...) transaction
-1 - Something unexpected happened!
*/

#include <milib.h>

mi_integer get_transaction_state_c( MI_FPARAM *fp)
{
mi_integer i,ret;
i=mi_transaction_state();
switch(i)
{
  case MI_NO_XACT:
          ret=0;
          break;
  case MI_IMPLICIT_XACT:
          ret=1;
          break;
  case MI_EXPLICIT_XACT:
          ret=2;
          break;
  default:
          ret=-1;
}
return (ret);
}

Coloquei o código acima num ficheiro chamado get_transaction_state_c.c


Criar o makefile

Também para o makefile, limitei-me a copiar alguns exemplos e gerei o seguinte. Por favor considere isto apenas como um exemplo. Não sou especialista em construção de makefiles e isto é apenas um pequeno projecto.

include $(INFORMIXDIR)/incl/dbdk/makeinc.linux

MI_INCL = $(INFORMIXDIR)/incl
CFLAGS = -DMI_SERVBUILD $(CC_PIC) -I$(MI_INCL)/public $(COPTS)
LINKFLAGS = $(SHLIBLFLAG) $(SYMFLAG)


all: get_transaction_state.so

clean:
 rm -f get_transaction_state.so get_transaction_state_c.o


get_transaction_state_c.o: get_transaction_state_c.c
 $(CC) $(CFLAGS) -o $@ -c $?

get_transaction_state.so: get_transaction_state_c.o
 $(SHLIBLOD) $(LINKFLAGS) -o $@ $?

Este makefile destina-se ao GNU Make. A primeira linha incluí um makefile fornecido pela IBM com o Informix. Este, basicamente, contém definições de variáveis e macros. Deve adaptar a directiva include ao seu sistema (o nome do makefile pode variar com a plataforma) e garanta que as variáveis que usei estão definidas no makefile base do seu sistema.
Depois disso defini mais algumas variáveis e criei os targets. Apenas quero que gere a biblioteca dinâmica get_transaction_state.so e para isso estou a incluir o objecto (get_transaction_state_c.o) gerado a partir do meu código fonte (get_transaction_state_c.c). Bastante simples se tiver conhecimentos básicos de makefiles.

Compilar o código

Depois de termos o makefile apenas necessitamos de um comando simples para executar a compilação:

cheetah@pacman.onlinedomus.net:informix-> make
cc -DMI_SERVBUILD -fpic -I/usr/informix/srvr1150uc7/incl/public -g -o get_transaction_state_c.o -c get_transaction_state_c.c
gcc -shared -Bsymbolic -o get_transaction_state.so get_transaction_state_c.o
cheetah@pacman.onlinedomus.net:informix->

Os dois comandos executados são a tradução dos macros/variáveis definidos no(s) makefiles(s), e apenas compilam o código fonte (primeiro comando) e depois geram a biblioteca dinâmica. Se tudo correr bem (como naturalmente aconteceu no output acima), teremos a biblioteca nesta localização, pronta a ser usada pelo Informix:

cheetah@pacman.onlinedomus.net:informix-> ls -lia *.so
267913 -rwxrwxr-x 1 informix informix 5639 Nov 23 22:06 get_transaction_state.so
cheetah@pacman.onlinedomus.net:informix-> file *.so
get_transaction_state.so: ELF 32-bit LSB shared object, Intel 80386, version 1 (GNU/Linux), dynamically linked, not stripped
cheetah@pacman.onlinedomus.net:informix->

Usar SQL para definir a função

Agora que temos o código executável, na forma de uma biblioteca dinâmica, precisamos de instruir o Informix para usá-la. Para isso vamos criar uma nova função, dizendo ao motor que está implementada em linguagem C e qual a localização onde está guardada. Faremos isso com um script SQL simples:

cheetah@pacman.onlinedomus.net:informix-> ls *.sql
get_transaction_state_c.sql
cheetah@pacman.onlinedomus.net:informix-> cat get_transaction_state_c.sql
DROP FUNCTION get_transaction_state_c;

CREATE FUNCTION get_transaction_state_c () RETURNING INTEGER
EXTERNAL NAME '/home/informix/udr_tests/get_transaction_state/get_transaction_state.so'
LANGUAGE C;
cheetah@pacman.onlinedomus.net:informix->

Vamos executá-lo...:

cheetah@pacman.onlinedomus.net:informix-> dbaccess stores get_transaction_state_c.sql

Database selected.


674: Routine (get_transaction_state_c) can not be resolved.

111: ISAM error:  no record found.
Error in line 1
Near character position 36

Routine created.


Database closed.

cheetah@pacman.onlinedomus.net:informix->

Repare que o erro -674 é expectável, dado que o meu SQL incluí a instrução DROP FUNCTION (e ela ainda não existe). Se estivesse a usar a versão 11.7 (devido a vários testes não a tenho operacional agora) podia ter usado a nova sintaxe "DROP IF EXISTS".

Portanto depois deste passo, devo ter uma função que pode ser chamada pela interface SQL com o nome get_transaction_state_c(). Não recebe argumentos e retorna um valor inteiro.


Testar!

Agora é tempo de a ver a trabalhar. Abri uma sessão na base de dados stores e fiz o seguinte:

1. Corri a função. Retornou "0", o que significa que não havia transacção aberta
2. Depois abri uma transacção e executei a função novamente. Retornou "2", o que significa que uma transacção explícita estava aberta
3. Fechei a transacção e corri a função pela terceira vez. Como esperado retornou "0"

Aqui está o output:

cheetah@pacman.onlinedomus.net:informix-> dbaccess stores -

Database selected.

> EXECUTE FUNCTION get_transaction_state_c();


(expression)

     0

1 row(s) retrieved.

> BEGIN WORK;

Started transaction.

> EXECUTE FUNCTION get_transaction_state_c();


(expression)

     2

1 row(s) retrieved.

> ROLLBACK WORK;

Transaction rolled back.

> EXECUTE FUNCTION get_transaction_state_c();


(expression)

     0

1 row(s) retrieved.

>

Não vimos o retorno "1". Isso acontece quando estamos dentro de uma transacção implícita. Esta situação pode ser vista se a função estiver a ser executada numa base de dados em modo ANSI. Para isso vou usar uma outra base de dados (stores_ansi), e naturalmente necessito de criar a função aqui (usando as instruções SQL anteriores). Depois repito mais ou menos os mesmos passos e o resultado é interessante:

cheetah@pacman.onlinedomus.net:informix-> dbaccess stores_ansi -

Database selected.

> EXECUTE FUNCTION get_transaction_state_c();


(expression)

      0

1 row(s) retrieved.

> SELECT COUNT(*) FROM systables;


 (count(*))

         83

1 row(s) retrieved.

> EXECUTE FUNCTION get_transaction_state_c();


(expression)

      1

1 row(s) retrieved.

>

Se reparar, a primeira execução retornou "0". Como ainda não tinha efectuado nenhuma operação não havia transacção aberta. Mas logo a seguir a um simples SELECT já retorna "1", o que significa que uma transacção implícita estava aberta. Isto prende-se com a natureza e comportamento das bases de dados em modo ANSI.
Se as usa e pretende usar esta função terá de ter isto em consideração. Ou poderá simplesmente mapear o retorno "1" e "2" da função mi_transaction_state() no retorno "1" da função criada por si. Isto sinalizaria que uma transacção estava aberta (omitindo a distinção entre transacção implícita e explícita).

Considerações finais

Relembro que este artigo serve mais como introdução aos UDRs na linguagem C que propriamente para resolver um problema real.
Se necessitar de saber se já está numa transacção (dentro de uma stored procedure por exemplo) pode usar esta solução, mas também podia tentar abrir uma transacção e capturar e gerir o erro dentro de um bloco ON EXCEPTION.

Chamo a atenção também para que se isto é um problema real das suas aplicações, mesmo que esteja já dentro de uma transacção, pode fazer com que o código da sua stored procedure trabalhe como uma unidade, usando a funcionalidade dos SAVEPOINTs, introduzida na versão 11.50. Em pseudo-código seria feito assim:

1. Chamar a get_transaction_state_c()
2. Se estamos dentro de uma transacção então estabelecer TX="SVPOINT" e criar um savepoint chamado "MYSVPOINT". Ir para 4)
3. Se não estamos dentro de uma transacção então estabelecer TX="TX" e criar uma. Ir para 4)
4. Correr o código da procedure
5. Se ocorrer algum erro testat a variável TX. Ir para 8)
   
6. Se TX =="TX" então ROLLBACK WORK. Retornar erro.
   
7. Senão, SE TX=="SVPOINT" então ROLLBACK WORK TO SAVEPOINT 'MYSVPOINT'. Retornar erro
   
8. Retornar sucesso
   

Depois desta introdução espero conseguir escrever mais alguns artigos relacionados com este tópico. A ideia base é que algumas vezes é muito fácil estender a funcionalidade base do Informix. E sinto que muitos clientes não tiram partido disto.
Naturalmente há implicações em escrever UDRs em C. O exemplo acima é terrivelmente simples, e não trará prejuízo ao motor. Mas quando escrevemos código que será executado pelo motor surgem uma série de questões... Utilização de memória, fugas de memória, segurança, estabilidade.... Mas existem respostas para estas preocupações. Espero que alguns (problema e soluções) sejam cobertos em artigos futuros.