Firebird News

[See remaining text...] 

On Linux world, of course, C langage rules ! Much, in terms of recompilation of source code, can achieved by rewritting and improving functions
without major difficulties. You just use gcc.

I had a needed to add functions to rFunc library by Polaris Software rfunc.sourceforge.net

For example, let’s create a function ISNULLDEF. To facilitate understanding C, here is the code in Delphi:

library mtz;
   function ISNULLDEF( s,c: pchar ): PChar; cdecl; export;
   begin
   if s[0] = #0 then result := c
   else result := s;
   end;

 exports
   ISNULLDEF;

 begin
 end.

Delphi 7 works beautfully, generates the .Dll and export function names.

On small libraries, be careful to use capital letters to facilitate the sql declare statment:

 declare external function ISNULLDEF
   cstring(254), cstring(254)
   returns cstring(254)
   entry_point 'ISNULLDEF' module_name 'mtz';

In C code is even shorter:

 char* EXPORT fn_isnulldef(ARG(char*,s), ARG(char*,c))
   ARGLIST(char* s)
   ARGLIST(char* c)
   { return( (*s) ? s : c); }

I have been using interbase 6 for a long time, but Polaris staff has already changed to Firebird.

Many customers still use IB6, since it’s free. We also know it’s behavior, capabilities and limitations. Migration Interbase 6 -> firebird can be complicated when the finished product is already stable.

If ain’t broke, why fix it ? We can extend the life of the database before the ‘inevitable’ evolution.

On Linux it’s easy to add new code to rFunc C source (in this case rdatetime.c). Recompile with gcc. Place the results
in /opt/interbase/UDF and declare the sql. It runs perfectly.

On windows it was troublesome !

I have downloaded Borland C 55, configured the paths, modified the makefile, but  it always gave the error unresolved external ib_util_malloc(long), or other problems at the time of the linking.

Polaris uses Ib_util (the firebird dll) in their build, and it seems they have no problems, but when we use the ib_util.dll from Interbase 6, strange erros appear.

I tried several tlibs, implibs and tdumps, but could not the make the link step correctly.

I tried cygwin, mingw without success.

Borland and Microsoft use internally different naming schemes for their libraries, and Interbase 6 was originally compiled in Microsoft environment.

Well, as I had access to Microsoft Visual C++ 5.00, and had already written Dlls for  MS Excel in 90′s, I started working the tool. The pro’s for VC++ is that it is native compatible  with microsoft products, and of course, you have the IDE, that free Borland C command line does not.

I also thought about using Borland C++ Builder or CodeGear, but they are ‘heavy’ apps, and these environments require new investments by purchasing licenses, as well as time to adapt, not to mention the possibility of failure in terms of compatibility.

My goal was to create a method of recompiling libraries of UDF’s for Linux within the Windows environment with minimal changes to the original source.

Before you start:

Actually this article is for those who want or need to create UDF’s and DLL’s in Visual C. If you have good knowledge of Delphi and does not use C, this can be challenging.

The pro’s about UDFs, when written with compatibility in mind (which is not very difficult on intel framework), is that they run both in Linux and Windows. In fact, this was  the main reason that led me to rebuild and expand the rfunc2 in windows environment.

Some customers insist on using IB/Win server and does not allocate new Linux boxex to the databases, or, as in my case, I have customers who use laptop with windows xp and IB6 local databases while travelling. So we had to run functions on both Linux and IB6/Win environments.

You can also try using FreeUDFlib for windows (made in Delphi, get the source code which is an excellent reference). There is a port to Linux (by Rob Schieck), but is limited to dialect 1 and 60 functions. Another one is FreeUDFLibC (by Greg Deatz) but in all those cases there is the problem that the orginal source is in Delphi and the port is in C. On any new feature, you must rewrite Delphi code in another language. Let’s be honest: gcc is Linux. Developing code in parallel with two languages generates overhead. Another point: xxxlib has a new version for Linux. I want to use it.
How can I do it if my windows lib is in Delphi ? Another time we will talk about Kylix.

Finally, many people use UDFs, but FEW create and disseminate new libraries. The workload required to install the environment, develop and test leads to a minority of  code writers. This can be changed.

Here are the steps:

Test environment (‘simple one’):

Machine 1 testbed Linux Mandriva 2007 and IB6

Machine 2 Windows XP and IB6

Machine 3 production Linux 2.6.17-13mdv # 1 SMP Fri Mar 23 19:03:31 UTC 2007 i686 Intel (R) Pentium (R) 4 CPU 3.00GHz GNU / Linux also IB6

1. Download rfunc2.tar.gz (Linux) or rfunc-2.1.3.1-RC1-win32.zip (windows) from rfunc.sourceforge.net.

I have only tested with these two for now.

Had not tried to recompile Linux v2.1.3.1 in windows, but rFunc 2.0.1.2 Linux recompiles ok in windows.

2. Install MS VC++ with default options. I preferred directory c:\progra~1\ devstudio

3. Open VC and select: File / New / Project / Win32 Dynamic-Link Library

4. Choose a name for your project: rfunc2131 (will be created in \DevStudio\my projects).

5. Extract all files from the rfunc you downloaded into directory \DevStudio\my projects\rfunc2131

6. Will appear on Workspace ‘rfunc2131′: 1 projects at left side.

7. Select from main menu: Project / Add to project / Files

8. Choose source directory of rfunc2131

9. Add all files compatible with VC (just check everything and hit ok)

10. Select Tools / Options / Directories

11. Select platform win32, directories include files

12. Include IB_base_Directory\sdk\include (in my case C:\Program Files\BORLAND\INTERBASE\SDK\INCLUDE)

13. Select platform win32 directories, library files

14. Include IB_base_Directory\sdk\lib (in my case C:\Program Files\BORLAND\INTERBASE\SDK\LIB)

15. There may be problems with the random number generator and the constants M_PI and M_E, but they are easy to solve:

Select build / rebuild all. If you see:

rmath.obj : error LNK2001: unresolved external symbol _srandom
rmath.obj : error LNK2001: unresolved external symbol _random

  e/ou

rmath.c: error C2065: 'M_E' : undeclared identifier
rmath.c: error C2065: 'M_PI' : undeclared identifier

16. Copy code below and paste over the functions fn_initRandom and fn_getRandom existing in rmath.c:

#include <time.h>

#ifndef M_PI
   #define M_PI           3.14159265358979323846
   #endif

#ifndef M_E
   #define M_E            2.71828182845904523536
   #endif

long EXPORT fn_initRandom(ARG(long *, num))
   ARGLIST(long *num)
   {
   #if(defined(_MSC_VER) && defined(_WIN32))
   srand( (unsigned)time( NULL ) );
   #elif __WIN32__
   randomize();
   #else
   srandom(*num);
   #endif
   return 1;
   }

long EXPORT fn_getRandom(ARG(long *, num))
   ARGLIST(long *num)
   {
   #if(defined(_MSC_VER) && defined(_WIN32))
   div_t x;
   x = div(rand(), *num);
   return x.rem;
   #elif __WIN32__
   return random(*num);
   #else
   div_t x;
   x = div(random(), *num);
   return x.rem;
   #endif
   }

Or open rmath.c and kill the functions fn_initRandom and fn_getRandom with / * …. * / // (easier)

Don’t forget to remove those two from the .Def shown below

17. To see if everything is going ok, select build / rebuild all

18. There will be some warnings and an error “unresolved external symbol _isc_decode_date@8″

19. To solve the problem of isc_decode_date@8, get in Project / Settings / C/C++ / Object/library modules.

There you will see: kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib uuid.lib odbc32.lib odbccp32.lib

Include gds32_ms.lib: gds32_ms.lib kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32.lib advapi32.lib shell32.lib ole32.lib oleaut32.lib uuid.lib odbc32.lib odbccp32.lib

20. Select build / rebuild all: result shows rfunc2131.dll – 0 error (s), 6 warning (s) or something alike (no errors).

21. The Dll is not yet complete, no export is being performed.

22. If you open the Dll with tdump or “Dll Export Viewer”, or similar ones, there will be nothing there to see !

23. To export the functions, select project / add to project / files

24. Type *.def, you will see rfunc

If you do not find the rfunc.def search the same source in 2131 version and modify it yourself

(inserting new functions or by putting ; in front of the existing names).

If you prefer to create your own rfunc.def. You must have at least:

 EXPORTS
   nomedafuncao1
   nomedafuncao2
   nomedafuncao3
   ; funcaoquenaoentra1
   ; funcaoquenaoentra2
   etc.

25. Well, select the rfunc.def, hit ok and rebuild all.

  ;Math Functions
  ;fn_dividezero
  ;fn_initRandom

26. Linking will appear…

 Creating library Debug/rfunc2131.lib and object Debug/rfunc2131.exp
   rfunc2131.exp : warning LNK4070: /OUT:rfunc.dll directive in .EXP differs from output filename "Debug/rfunc2131.dll"; ignoring directive
   Creating browse info file...

 rfunc2131.dll - 0 error(s), 7 warning(s)

27. Your upgraded Dll is ready to work ! Copy it to InterBase UDF directory (in my case C:\Program Files\BORLAND\INTERBASE\UDF);

If a copying error happen, shutdown interbase, copy the Dll and hit start again (InterBase Manager on Control Panel)

28. Register one UDF for testing:

DECLARE EXTERNAL FUNCTION EXTRACTYEAR
   TIMESTAMP
   RETURNS INTEGER BY VALUE
   ENTRY_POINT 'fn_year'  MODULE_NAME 'rfunc2131';

The sql directory of rfunc has files to declare everything automatically.

29. Test the UDF:

select TABLE.DATE, EXTRACTYEAR (TABLE.DATE) from TABLE

30. All is working, you can now register all the dll’s UDFs and use them at will !

Congratulations, you have understoond the creation of new UDF’s from sources in C.

With the examples presented here, it is possible to use both Linux code and port it to windows (the case of rfunc2.tar.gz) or use windows code directly (rfunc2131.zip).

Modify your UDFs as you want, don’t forget to notify the developer about new routines you’ve created, or corrections you find important.

Regards,
Marcelo Martenetz / Curitiba-PR / BRAZIL

/ *** ANNEX I: About the recompilation of rFunc version 2.1.3.1 on Linux: *** /

When trying to recompile version 2.1.3.1 on older Linux distros, some problems may arise, especially the lack of uuid lib (among others), addressed below.

1) Problems with RBlob. They are caused by the structure declaration BLOB.

In rlob.h put the #undef IB_FIREBIRDSQL to compile correctly:

/* Blob passing structure */

  // Remark included
  #undef IB_FIREBIRDSQL
  #if defined(IB_FIREBIRDSQL)

  typedef BLOBCALLBACK BLOB;
  #else
  (...)

2) Problems rmisc. Caused by lack of uuid lib.

If you do not have uuid installed in your distro, put the #undef RUSE_GUID in rmisc.c

#include
“rmisc.h”

 // Remark included
 #undef RUSE_GUID

 #if defined WIN32
   (...)

3) Problems while linking. Linker tries to include uuid and gds32

Change gds32 to gds in rfunc.conf:

 #GDS_NAME       = fbclient
 GDS_NAME        = gds

Remove LIB_LINK_FLAGS with the UUID and GDS, if not present

on your machine:

 Line #54 of makefile.linux
 #Remark by MM LIB_LINK_FLAGS += -luuid

4) Terminate your build:

 vi rfunc.conf
  vi rlob.h
  vi rmisc.c
  vi makefile.linux

  make -f makefile.linux
  cp rfunc /opt/interbase/UDF

5) Declare the UDF(s)

 DECLARE EXTERNAL FUNCTION ISNULLDEF 

  CSTRING (255) CHARACTER SET NONE
  CSTRING (255) CHARACTER SET NONE
  RETURNS CSTRING (255) CHARACTER SET NONE
  ENTRY_POINT 'fn_isnulldef' MODULE_NAME 'rfunc';

6) Testing

  select TABLE.STRINGSwithANDwithoutNULL
  from TABLE
  order by 1
  // Here nulls comes last, even using asceding / descending

  select TABLE.STRINGSwithANDwithoutNULL, ISNULLDEF( TABLE.STRINGSwithANDwithoutNULL, '_THISISNULL' )
  from TABLE
  order by 2
  // Here nulls comes first

Run the select statments and see the diference !

/ *** ANNEX II: on fn_CreateGUID / CreateGUID *** /

If you need uuid and it’s not on your distribution:

You can use an UDF that has precompiled UUID (uuidUDF for example), or
Use UUID library that comes with e2fsprogs.

1) Download the e2fsprogs-1.41.9.tar.gz from SourceForge

http://sourceforge.net/projects/e2fsprogs/

2) Run as root:

tar -zxvf e2fsprogs-1.41.9.tar.gz
cd e2fsprogs-1.41.9
. / configure
make
make & & 

make install & &
make install-libs & &
install-info / usr/share/info/libext2fs.info / usr / share / info / dir

Some recommend changing the parameters of the CONFIGURE

http://www.faqs.org/docs/linux_scratch/chapter06/e2fsprogs.html

{ 6.45.1. Installation of E2fsprogs

Install E2fsprogs by running the following commands:

mkdir ../e2fsprogs-build &&
  cd ../e2fsprogs-build &&
  ../e2fsprogs-1.27/configure --prefix=/usr --with-root-prefix="" \
    --enable-elf-shlibs &&
  make &&
  make install &&

  make install-libs &&
  install-info /usr/share/info/libext2fs.info /usr/share/info/dir

6.45.2. Command explanations

–with-root-prefix=”": The reason for supplying this option is because of the setup of the e2fsprogs Makefile. Some programs are essential for system use when, for example, /usr isn’t mounted (like the e2fsck program). These programs and libraries, therefore, belong in directories like /lib and /sbin. If this option isn’t passed to E2fsprogs’s configure, it places these programs in /usr, which is not what we want.

–enable-elf-shlibs: This creates shared libraries that some programs in this package can make use of.

make install-libs: This installs the shared libraries that are built.
}

3) Testing GUID (uuid):

Create a test table:

CREATE TABLE M (
  ID   CHAR(32) NOT NULL PRIMARY KEY,
  TXT  VARCHAR(30));

Run the script below:

CREATE PROCEDURE initdata (ANZ INTEGER)
  AS
  declare variable m varchar (40);
  begin
    while (anz> 0) of
    begin 

      m = CreateGUID ();
      strreplace m = (m ,'-','');
      insert into m (id, txt) values (: m, CURRENT_TIMESTAMP);
      anz = anz-1;
    end
  end

Execute the SQL:

  EXECUTE PROCEDURE initdata (100)

Open table M and see the results !

There is a discussion about if IDs must be int32, int64 or VarChar (30) (GUIDs without ‘-’).
On average we can say that varchar takes twice the time of integers for INITDATA, backup / restore and select. The size of the database is also doubled.  http://www.firebirdnews.org/?p=998

I prefer to use integer ID’s. Type is int64 (and not smallint), because you never know how much your database can grow, and changes in type forces changes all over the source either in Delphi or C, which is a hassle. Migrate the database is easy, but the sources…

There is also the fact that it is much easier to debug a database with integers (1,2,3) instead of typing SQL’s with huge ID’s of 30/34 bytes (’0c6649b781c1de11b36e0050da0b1688 ‘).

However, when you need to generate thousands of ID’s without using GEN_ID or MAX(ID)+1, or when there is competition between process and the solution goes by semaphores ou mutexes, its better to stick with GUID.

Never tried yet to run 100 (or 1000) processes on 50 (or 500) machines inserting thousands of records on the same table with the same one primary key. If you do so, please tell me how IB6 behaves and/or if FireBird is
up for the job. Please send sources and conclusions if possible (the processes must compete, and start with ms difference).

For contact, “mtz-development” uses that hot e-mail from microsoft.

The solution presented here is ‘as-is’, without commitment on our part.

Have a nice coding !

« Hide it

[See remaining text...] 

Database Workbench supports:
- Borland InterBase ( 4.x – 9.x )
- Firebird ( 1.x, 2.x )
- MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, 2008, MSDE 1 & 2, SQL Express )
- MySQL 4.x, 5.x
- Oracle Database ( 8i, 9i, 10g, 11g )
- Sybase SQL Anywhere ( 9, 10 and 11 )
- NexusDB ( 2.08.x, 3.0.x )

–
Upscene Productions
www.upscene.com

« Hide it