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.