Loading ...
Sorry, an error occurred while loading the content.

Re: [firebird-support] UDF assistance

Expand Messages
  • BogusÅ‚aw Brandys
    ... It may be not relevant, but must TIMESTAMP be released with FREE_IT ? I see you are calling it as var param. Regards Boguslaw
    Message 1 of 7 , Dec 1, 2008
    View Source
    • 0 Attachment
      Adam pisze:
      >
      >
      > Hello Group,
      >
      > I am having some difficulty with a small number of UDF calls with
      > Firebird 2.1.
      >
      > These have worked for years alongside Firebird 1.5. Furthermore, the
      > problem is intermittent and difficult to reduce to a simple test case,
      > as even removing an unrelated join from the query stops the error from
      > occurring. I do have a reproducable test case, but it involves a 30
      > line SQL statement and a 1GB database. I think it is better to rather
      > start with the code I am using to see if there are any obvious errors.
      > I have not been able to reproduce the error using "select
      > FB_Addmins(' [some time literal]', [some integer]) from RDB$DATABASE; "
      > query (even using the same values that result in the error.
      >
      > * 99.9% of the time, it works fine even under 2.1
      > * The error does not occur until after the function has completed, as
      > I receive FB_AddMins End in the debugger.
      > * I have also tried changing the isc_decode_timestam p and
      > isc_encode_timestam p to cdecl but it made no difference.
      >
      > The exact error message is:
      > Statement failed, SQLCODE = -902
      > Unable to complete network request to host "lappy".
      > -Error reading data from the connection.
      > -An existing connection was forcibly closed by the remote host.
      >
      > The function's purpose is to take a timestamp and a number of minutes,
      > and to return a timestamp that many minutes later. (Yes, I know this
      > particular function can also be done in PSQL). Can anyone see anything
      > glaringly wrong with the code?
      >
      > TIA
      > Adam
      >
      > (I have extracted the function into a new dll to simplify the required
      > code, but I have confirmed the problem still occurs with this separate
      > dll).
      >
      > Declaration:
      > ============
      >
      > DECLARE EXTERNAL FUNCTION FB_ADDMINS
      > TIMESTAMP,INTEGER
      > RETURNS TIMESTAMP FREE_IT
      > ENTRY_POINT 'FB_AddMins' MODULE_NAME 'MyUDF';
      >
      > UDF Code (Delphi 7):
      > ============ ======
      >
      > library MyUDF;
      >
      > uses
      > SysUtils,
      > Classes,
      > uMyUDF in 'uMyUDF.pas' ;
      >
      > {$R *.res}
      >
      > exports
      >
      > FB_AddMins;
      >
      > begin
      > end.
      >
      > ------------ --------- --------- --------- ------
      >
      > unit uMyUDF;
      >
      > interface
      >
      > type
      > Long = LongInt;
      > ULong = Cardinal;
      >
      > ISC_TIMESTAMP = record
      > timestamp_date : Long;
      > timestamp_time : ULong;
      > end;
      >
      > PISC_TIMESTAMP = ^ISC_TIMESTAMP;
      >
      > TM = record
      > tm_sec : integer; // Seconds
      > tm_min : integer; // Minutes
      > tm_hour : integer; // Hour (0--23)
      > tm_mday : integer; // Day of month (1--31)
      > tm_mon : integer; // Month (0--11)
      > tm_year : integer; // Year (calendar year minus 1900)
      > tm_wday : integer; // Weekday (0--6) Sunday = 0)
      > tm_yday : integer; // Day of year (0--365)
      > tm_isdst : integer; // 0 if daylight savings time is not in effect)
      > end;
      >
      > PTM = ^TM;
      >
      > function FB_AddMins(var DT: ISC_TIMESTAMP; var Minutes: Integer):
      > PISC_TIMESTAMP; cdecl;
      >
      > implementation
      >
      > uses
      > ibexternals,
      > Windows,
      > DateUtils;
      >
      > procedure isc_decode_timestam p(ib_date: PISC_TIMESTAMP; tm_date: PTM);
      > stdcall; external 'gds32.dll';
      > procedure isc_encode_timestam p(tm_date: PTM; ib_date: PISC_TIMESTAMP) ;
      > stdcall; external 'gds32.dll';
      >
      > function TimeStampToDateTime (TimeStamp: ISC_TIMESTAMP) : TDateTime;
      > var
      > TempTM: TM;
      > begin
      > isc_decode_timestam p(@TimeStamp,
      > @TempTM);
      >
      > Result := EncodeDateTime( TempTM.tm_ year+1900,
      > TempTM.tm_mon+ 1,
      > TempTM.tm_mday,
      > TempTM.tm_hour,
      > TempTM.tm_min,
      > 0,
      > 0);
      > end;
      >
      > function DateTimeToTimeStamp (DateTime: TDateTime): ISC_TIMESTAMP;
      > var
      > Y,M,D,H,N,Dummy: Word;
      > TempTM: TM;
      > begin
      > DecodeDateTime( DateTime,
      > Y,
      > M,
      > D,
      > H,
      > N,
      > Dummy,
      > Dummy);
      >
      > TempTM.tm_year := Y - 1900;
      > TempTM.tm_mon := M - 1;
      > TempTM.tm_mday := D;
      > TempTM.tm_hour := H;
      > TempTM.tm_min := N;
      > TempTM.tm_sec := 0;
      >
      > isc_encode_timestam p(@TempTM, @Result);
      > end;
      >
      > {Export Functions}
      >
      > function FB_AddMins(var DT: ISC_TIMESTAMP; var Minutes: Integer):
      > PISC_TIMESTAMP; cdecl;
      > var
      > DTConverted: TDateTime;
      > begin
      > OutputDebugString( 'FB_AddMins Start');
      > DTConverted := TimeStampToDateTime (DT);
      > DTConverted := DTConverted + Minutes/1440;
      > DT := DateTimeToTimeStamp (DTConverted) ;
      > Result := @DT;
      > OutputDebugString( 'FB_AddMins End');
      > end;
      >
      > end.
      >

      It may be not relevant, but must TIMESTAMP be released with FREE_IT ? I
      see you are calling it as var param.


      Regards
      Boguslaw
    • Paul Hope
      Hi Adam Cant pretend to know what it all means but my udf procedure declarations end in cdecl; export; could there be something there? Regards Paul
      Message 2 of 7 , Dec 1, 2008
      View Source
      • 0 Attachment
        Hi Adam

        Cant pretend to know what it all means but my udf procedure declarations end
        in 'cdecl; export;'
        could there be something there?

        Regards
        Paul

        > -----Original Message-----
        > From: firebird-support@yahoogroups.com
        > [mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
        > Sent: 01 December 2008 03:19
        > To: firebird-support@yahoogroups.com
        > Subject: [firebird-support] UDF assistance
        >
        > Hello Group,
        >
        > I am having some difficulty with a small number of UDF calls
        > with Firebird 2.1.
        >
        > These have worked for years alongside Firebird 1.5.
        > Furthermore, the problem is intermittent and difficult to
        > reduce to a simple test case, as even removing an unrelated
        > join from the query stops the error from occurring. I do have
        > a reproducable test case, but it involves a 30 line SQL
        > statement and a 1GB database. I think it is better to rather
        > start with the code I am using to see if there are any obvious errors.
        > I have not been able to reproduce the error using "select
        > FB_Addmins('[some time literal]', [some integer]) from RDB$DATABASE;"
        > query (even using the same values that result in the error.
        >
        > * 99.9% of the time, it works fine even under 2.1
        > * The error does not occur until after the function has
        > completed, as I receive FB_AddMins End in the debugger.
        > * I have also tried changing the isc_decode_timestamp and
        > isc_encode_timestamp to cdecl but it made no difference.
        >
        > The exact error message is:
        > Statement failed, SQLCODE = -902
        > Unable to complete network request to host "lappy".
        > -Error reading data from the connection.
        > -An existing connection was forcibly closed by the remote host.
        >
        > The function's purpose is to take a timestamp and a number of
        > minutes, and to return a timestamp that many minutes later.
        > (Yes, I know this particular function can also be done in
        > PSQL). Can anyone see anything glaringly wrong with the code?
        >
        > TIA
        > Adam
        >
        > (I have extracted the function into a new dll to simplify the
        > required code, but I have confirmed the problem still occurs
        > with this separate dll).
        >
        > Declaration:
        > ============
        >
        > DECLARE EXTERNAL FUNCTION FB_ADDMINS
        > TIMESTAMP,INTEGER
        > RETURNS TIMESTAMP FREE_IT
        > ENTRY_POINT 'FB_AddMins' MODULE_NAME 'MyUDF';
        >
        > UDF Code (Delphi 7):
        > ==================
        >
        > library MyUDF;
        >
        > uses
        > SysUtils,
        > Classes,
        > uMyUDF in 'uMyUDF.pas';
        >
        > {$R *.res}
        >
        > exports
        >
        > FB_AddMins;
        >
        > begin
        > end.
        >
        > ---------------------------------------------
        >
        > unit uMyUDF;
        >
        > interface
        >
        > type
        > Long = LongInt;
        > ULong = Cardinal;
        >
        > ISC_TIMESTAMP = record
        > timestamp_date : Long;
        > timestamp_time : ULong;
        > end;
        >
        > PISC_TIMESTAMP = ^ISC_TIMESTAMP;
        >
        > TM = record
        > tm_sec : integer; // Seconds
        > tm_min : integer; // Minutes
        > tm_hour : integer; // Hour (0--23)
        > tm_mday : integer; // Day of month (1--31)
        > tm_mon : integer; // Month (0--11)
        > tm_year : integer; // Year (calendar year minus 1900)
        > tm_wday : integer; // Weekday (0--6) Sunday = 0)
        > tm_yday : integer; // Day of year (0--365)
        > tm_isdst : integer; // 0 if daylight savings time is not
        > in effect)
        > end;
        >
        > PTM = ^TM;
        >
        > function FB_AddMins(var DT: ISC_TIMESTAMP; var Minutes: Integer):
        > PISC_TIMESTAMP; cdecl;
        >
        > implementation
        >
        > uses
        > ibexternals,
        > Windows,
        > DateUtils;
        >
        >
        > procedure isc_decode_timestamp(ib_date: PISC_TIMESTAMP;
        > tm_date: PTM); stdcall; external 'gds32.dll'; procedure
        > isc_encode_timestamp(tm_date: PTM; ib_date: PISC_TIMESTAMP);
        > stdcall; external 'gds32.dll';
        >
        >
        > function TimeStampToDateTime(TimeStamp: ISC_TIMESTAMP): TDateTime; var
        > TempTM: TM;
        > begin
        > isc_decode_timestamp(@TimeStamp,
        > @TempTM);
        >
        > Result := EncodeDateTime(TempTM.tm_year+1900,
        > TempTM.tm_mon+1,
        > TempTM.tm_mday,
        > TempTM.tm_hour,
        > TempTM.tm_min,
        > 0,
        > 0);
        > end;
        >
        > function DateTimeToTimeStamp(DateTime: TDateTime): ISC_TIMESTAMP; var
        > Y,M,D,H,N,Dummy: Word;
        > TempTM: TM;
        > begin
        > DecodeDateTime(DateTime,
        > Y,
        > M,
        > D,
        > H,
        > N,
        > Dummy,
        > Dummy);
        >
        > TempTM.tm_year := Y - 1900;
        > TempTM.tm_mon := M - 1;
        > TempTM.tm_mday := D;
        > TempTM.tm_hour := H;
        > TempTM.tm_min := N;
        > TempTM.tm_sec := 0;
        >
        > isc_encode_timestamp(@TempTM, @Result); end;
        >
        > {Export Functions}
        >
        > function FB_AddMins(var DT: ISC_TIMESTAMP; var Minutes: Integer):
        > PISC_TIMESTAMP; cdecl;
        > var
        > DTConverted: TDateTime;
        > begin
        > OutputDebugString('FB_AddMins Start');
        > DTConverted := TimeStampToDateTime(DT);
        > DTConverted := DTConverted + Minutes/1440;
        > DT := DateTimeToTimeStamp(DTConverted);
        > Result := @DT;
        > OutputDebugString('FB_AddMins End');
        > end;
        >
        > end.
        >
        >
        >
        >
        > ------------------------------------
        >
        > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        >
        > Visit http://www.firebirdsql.org and click the Resources item
        > on the main (top) menu. Try Knowledgebase and FAQ links !
        >
        > Also search the knowledgebases at http://www.ibphoenix.com
        >
        > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        > Yahoo! Groups Links
        >
        >
        >
      • Ivan Prenosil
        ... Memory for the result must be allocated using: function ib_util_malloc(size: LongWord): Pointer; external ib_util.dll ; Ivan
        Message 3 of 7 , Dec 1, 2008
        View Source
        • 0 Attachment
          > Declaration:
          > ============
          >
          > DECLARE EXTERNAL FUNCTION FB_ADDMINS
          > TIMESTAMP,INTEGER
          > RETURNS TIMESTAMP FREE_IT
          > ENTRY_POINT 'FB_AddMins' MODULE_NAME 'MyUDF';
          >

          > UDF Code (Delphi 7):
          > ==================
          > {Export Functions}
          >
          > function FB_AddMins(var DT: ISC_TIMESTAMP; var Minutes: Integer):
          > PISC_TIMESTAMP; cdecl;
          > var
          > DTConverted: TDateTime;
          > begin
          > OutputDebugString('FB_AddMins Start');
          > DTConverted := TimeStampToDateTime(DT);
          > DTConverted := DTConverted + Minutes/1440;
          > DT := DateTimeToTimeStamp(DTConverted);
          > Result := @DT;
          > OutputDebugString('FB_AddMins End');
          > end;


          Memory for the result must be allocated using:

          function ib_util_malloc(size: LongWord): Pointer; external 'ib_util.dll';

          Ivan
        • Adam
          Hello Boguslaw, ... According to something I read on one of the (many) sites that google returned, timestamps should be declared this way. That said, I can not
          Message 4 of 7 , Dec 1, 2008
          View Source
          • 0 Attachment
            Hello Boguslaw,

            >
            > It may be not relevant, but must TIMESTAMP be released with FREE_IT ?

            According to something I read on one of the (many) sites that google
            returned, timestamps should be declared this way. That said, I can not
            find that link this morning so I may have been imagining things.

            > I
            > see you are calling it as var param.

            Actually it is code that I inherited, but if this is a problem, then I
            can change it to a normal parameter.

            Adam
          • Adam
            ... declarations end ... Hello Paul, Maybe. The function is explicitly exported in the dpr, and I do use cdecl for the calling convention for *MY* functions,
            Message 5 of 7 , Dec 1, 2008
            View Source
            • 0 Attachment
              --- In firebird-support@yahoogroups.com, "Paul Hope" <paulhope@...> wrote:
              >
              > Hi Adam
              >
              > Cant pretend to know what it all means but my udf procedure
              declarations end
              > in 'cdecl; export;'
              > could there be something there?

              Hello Paul,

              Maybe. The function is explicitly exported in the dpr, and I do use
              cdecl for the calling convention for *MY* functions, but it has not
              escaped me that the isc_decode_timestamp and isc_encode_timestamp are
              declared using stdcall. I thought this a mistake, but then I noticed
              that FreeAdHocUDF does pretty much the same thing.

              Adam
            • Adam
              ... ib_util.dll ; ... Hello Ivan, Do you mean something like: .... function FB_AddMins(var DT: ISC_TIMESTAMP; var Minutes: Integer): PISC_TIMESTAMP; cdecl;
              Message 6 of 7 , Dec 1, 2008
              View Source
              • 0 Attachment
                --- In firebird-support@yahoogroups.com, "Ivan Prenosil"
                <Ivan.Prenosil@...> wrote:
                >
                > > Declaration:
                > > ============
                > >
                > > DECLARE EXTERNAL FUNCTION FB_ADDMINS
                > > TIMESTAMP,INTEGER
                > > RETURNS TIMESTAMP FREE_IT
                > > ENTRY_POINT 'FB_AddMins' MODULE_NAME 'MyUDF';
                > >
                >
                > > UDF Code (Delphi 7):
                > > ==================
                > > {Export Functions}
                > >
                > > function FB_AddMins(var DT: ISC_TIMESTAMP; var Minutes: Integer):
                > > PISC_TIMESTAMP; cdecl;
                > > var
                > > DTConverted: TDateTime;
                > > begin
                > > OutputDebugString('FB_AddMins Start');
                > > DTConverted := TimeStampToDateTime(DT);
                > > DTConverted := DTConverted + Minutes/1440;
                > > DT := DateTimeToTimeStamp(DTConverted);
                > > Result := @DT;
                > > OutputDebugString('FB_AddMins End');
                > > end;
                >
                >
                > Memory for the result must be allocated using:
                >
                > function ib_util_malloc(size: LongWord): Pointer; external
                'ib_util.dll';
                >

                Hello Ivan,

                Do you mean something like:

                ....
                function FB_AddMins(var DT: ISC_TIMESTAMP; var Minutes: Integer):
                PISC_TIMESTAMP; cdecl;
                var
                DTConverted: TDateTime;
                begin
                OutputDebugString('FB_AddMins Start');
                DTConverted := TimeStampToDateTime(DT);
                DTConverted := DTConverted + Minutes/1440;
                DT := DateTimeToTimeStamp(DTConverted);
                Result := ib_util_malloc(SizeOf(DT));
                Result.timestamp_date := DT.timestamp_date;
                Result.timestamp_time := DT.timestamp_time;
                OutputDebugString('FB_AddMins End');
                end;
                ...

                In the 5 minutes of testing I have had a chance to do so far, it seems
                to work (ie has not crashed).

                Also, is there any documentation that you are aware of for writing
                UDFs in Firebird using Delphi? I have been working so far with a
                number of FAQs, short guides and example code.

                Adam
              Your message has been successfully submitted and would be delivered to recipients shortly.