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 
7    #include 
8    #include 
9    #include 
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 ...

Author: 
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
  (Fernando Nunes)
Find the whole article UDRs: ROWNUM in Informix / ROWNUM em Informix on the website Informix technology.

{mosgoogle}

{mos_sb_discuss:4}



			

			

				

					< Prev
				
				

					 
				
				

					Next >