Click here to Skip to main content
Click here to Skip to main content
 
Add your own
alternative version
Go to top

Writing UDFs for Firebird Embedded SQL Server

, , 23 Oct 2009
We will describe how to create your own native Firebird extension and show some approaches how to use it in managed code applications
UDFSample.zip
UDFSample
Firebird
fbembed.dll
firebird.msg
FirebirdSql.Data.FirebirdClient.dll
GDS32.DLL
ib_util.dll
icudt30.dll
icuin30.dll
icuuc30.dll
include
lib
ib_util_ms.lib
MainApp
Properties
SampleUdf
using System;
using System.IO;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Data;

using FirebirdSql.Data.FirebirdClient;

namespace MainApp
{
    public class SampleProvider : IDisposable
    {
        private FbConnection connection;

        private delegate void MessageCallbackDelegate([MarshalAs(UnmanagedType.LPStr)] string message);
        private static MessageCallbackDelegate messageCallback;
        private static MessageCallbackDelegate errorCallback;
        [DllImport("udf/SampleUdf")]
        private static extern void RegisterCallbacks(MessageCallbackDelegate messageCallback, MessageCallbackDelegate errorCallback);

        static SampleProvider()
        {
            messageCallback = MessageCallback;
            errorCallback = ErrorCallback;
            RegisterCallbacks(messageCallback, errorCallback);
        }

        public static void MessageCallback(string message)
        {
            Trace.WriteLine(message, "Message");
        }
        public static void ErrorCallback(string message)
        {
            Trace.WriteLine(message, "Error");
        }

        public SampleProvider(string dbPath)
        {
            string conString = "ServerType=1;User=SYSDBA;Password=masterkey;Dialect=3;Database=" + dbPath;
            FbConnection.CreateDatabase(conString); //creating blank database
            File.Move(dbPath, dbPath); //small fix - FbConnection always create uppercase filename
            this.connection = new FbConnection(conString);
            this.connection.Open();
        }

        public void Run()
        {
            this.CreateDatabaseStructure();
            this.FillRawData();
            this.TransferData();
            this.GetParsedData();
        }

        private void CreateDatabaseStructure()
        {
            using (Stream resourceStream = this.GetType().Assembly.GetManifestResourceStream("MainApp.batch.sql"))
            {
                using (StreamReader reader = new StreamReader(resourceStream))
                {
                    string content = reader.ReadToEnd();
                    foreach (string block in content.Split('#'))
                    {
                        if (block != string.Empty)
                        {
                            using (FbCommand command = this.connection.CreateCommand())
                            {
                                command.CommandText = block;
                                command.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
        }

        private void FillRawData()
        {
            int count = 0;
            DirectoryInfo directoryInfo = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory);
            foreach (FileInfo fi in directoryInfo.GetFiles())
            {
                using (MemoryStream memStream = new MemoryStream())
                {
                    using (BinaryWriter writer = new BinaryWriter(memStream))
                    {

                        writer.Write(Encoding.ASCII.GetBytes(fi.FullName));
                        writer.Write((byte)0); //for 0-based string
                        writer.Write(fi.CreationTime.ToFileTime());
                        writer.Write((int)fi.Attributes);
                        writer.Write(fi.Length);
                        //MemoryStream contains buffer larger than writen
                        byte[] buffer = new byte[memStream.Position];
                        Array.Copy(memStream.GetBuffer(), buffer, memStream.Position);

                        using (FbCommand command = this.connection.CreateCommand())
                        {
                            command.CommandText = "AddRawData";
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add("@Data", FbDbType.Binary).Value = buffer;
                            command.ExecuteNonQuery();
                        }
                        count += 1;
                    }
                }
            }
            Console.WriteLine("Added {0} raw records", count);
        }

        private void TransferData()
        {
            int count = 0;
            using (FbCommand command = this.connection.CreateCommand())
            {
                command.CommandText = "TransferData";
                command.CommandType = CommandType.StoredProcedure;
                count = (int)command.ExecuteScalar();
            }
            Console.WriteLine("Transferred {0} records", count);
        }

        private void GetParsedData()
        {
            Console.WriteLine("Parsed data:");
            using (FbCommand command = this.connection.CreateCommand())
            {
                command.CommandText = "select * from FSTable";
                using (FbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}"
                            , reader["FileName"]
                            , reader["FullPath"]
                            , reader["CreationTime"]
                            , reader["Attributes"]
                            , reader["FileSize"]
                            );
                    }
                }
            }

        }

        #region IDisposable Members

        public void Dispose()
        {
            this.connection.Dispose();
        }

        #endregion
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Authors

Ivan_Komarov

Ukraine Ukraine
No Biography provided

Apriorit Inc
Apriorit Inc.
Ukraine Ukraine
ApriorIT is a Software Research and Development company that works in advanced knowledge-intensive scopes.
 
Company offers integrated research&development services for the software projects in such directions as Corporate Security, Remote Control, Mobile Development, Embedded Systems, Virtualization, Drivers and others.
 
Official site http://www.apriorit.com
Group type: Organisation

31 members

Follow on   LinkedIn

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 24 Oct 2009
Article Copyright 2009 by Ivan_Komarov, Apriorit Inc
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid