0

I have a script to generate the structure of a database for Firebird and use ISQL

I have problems with some of the statements I use due to the type of date TIMESTAMP, for example with the external functions:

DECLARE EXTERNAL FUNCTION F_CUTTIME
TIMESTAMP
RETURNS TIMESTAMP FREE_IT
ENTRY_POINT 'fn_cuttime' MODULE_NAME 'SISUDFIB';

Or for example with the creation of domains:

CREATE DOMAIN D_DATE AS TIMESTAMP
CHECK ((VALUE IS NULL) OR (VALUE = F_CUTTIME (VALUE)));

It gives me error and from what I see is by the type TIMESTAMP.

When I create the structure of my database I generate a script and I get errors in the declarations of external functions, but only in those I use TIMESTAMP tells me this error: 

Stament failed, sqlstate = 39000
 invalid request BLR offset 13 
-function F_CUTTIME is not defined

I get in the functions where TIMESTAMP is, the rest of functions generates me well.

How do I create my script so that I do not get errors in Firebird with ISQL?

I give an example:

SET SQL DIALECT 1;

CREATE DATABASE 'C: \ SISCONIBSCT.fdb' PAGE_SIZE 8192

USER 'SISCONIB' PASSWORD 'telecoman'

DEFAULT CHARACTER SET WIN1252;

/ * External Function declarations * /

DECLARE EXTERNAL FUNCTION F_CUTTIME
TIMESTAMP
RETURNS TIMESTAMP FREE_IT
ENTRY_POINT 'fn_cuttime' MODULE_NAME 'SISUDFIB';

/ * Domain definitions * /
CREATE DOMAIN D_BOOLEAN AS CHAR (1)
DEFAULT 'T'
CHECK (VALUE IN ('F', 'T')) NOT NULL;
CREATE DOMAIN D_DATE AS TIMESTAMP
CHECK ((VALUE IS NULL) OR (VALUE = F_CUTTIME (VALUE)));
CREATE DOMAIN D_DATETIME AS TIMESTAMP;
CREATE DOMAIN D_TIME AS TIMESTAMP
CHECK ((VALUE IS NULL) OR (F_CUTTIME (VALUE) = F_TIMEBASE ()));


CREATE TABLE SISFASE
(
  CODSCT SMALLINT NOT NULL,
  CODEST CHAR (2) NOT NULL,
  CODPRO SMALLINT NOT NULL,
  CODFAS SMALLINT NOT NULL,
  DESCREAS VARCHAR (30) COLLATE PXW_SPAN,
  PERESP D_TIME,
  CODPLA CHAR (1) NOT NULL,
  CODETG SMALLINT NOT NULL,
 PRIMARY KEY (CODSCT, CODEST, CODPRO, CODFAS)
);

Giving errors both in the declaration of the external function as in domains as in the creation of the table and always giving me error in the dates.

Do not understand the problem.

Arioch 'The
  • 15,799
  • 35
  • 62
Roman345
  • 145
  • 7
  • 1
    Please specify the full error message you get, don't make us guess. – Mark Rotteveel Sep 06 '17 at 12:46
  • you said you not only had problems with domains, but with functions too, so was there any error when function declaration statement run? // maybe you should `commit` after significant schema changes, for example after you created all UDFs. I do not think iSQL would auto-commit DDL statements when doing scripts. – Arioch 'The Sep 06 '17 at 13:16
  • Please **edit** your question with the error you get on `DECLARE EXTERNAL FUNCTION`, and post the header (or full code) of this `fn_cuttime`, as it doesn't seem to be a publicly available library. The error you get on creation of the domain is probably a follow up error. – Mark Rotteveel Sep 06 '17 at 13:18
  • Also, why to create yet another `D_DATE` domain out of TIMESTAMP, when FIREBIRD already does provide built-in DATE datatype ? https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes-datetime.html – Arioch 'The Sep 06 '17 at 13:21
  • `SET SQL DIALECT 1;` WHAT ??? That is `Interbase 5.x` legacy language of 1990-s. It even pre-dates first beta-versions of Firebird! Why not using normal Dialect 3 ??? And there already is `DATE` data type in dialect 3 so you would not have to re-invent it! READ: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-background.html#fblangref25-structure-dialects – Arioch 'The Sep 06 '17 at 13:29
  • And again, try adding explicit `commit;` instructions after significant actions done, like after all UDF's declared, then after all domains declared, etc. – Arioch 'The Sep 06 '17 at 13:31
  • Also, Firebird 3 introduced native BOOLEAN data type, so think about removing `D_BOOLEAN` domain too. // also, what is EXACT (full) version of your Firebird server? – Arioch 'The Sep 06 '17 at 13:35
  • Please post the header file or full code of this `fn_cuttime` in library `SISUDFIB`; that doesn't seem to be a publicly available library – Mark Rotteveel Sep 06 '17 at 13:38
  • @MarkRotteveel he better just to remove that function completely. There is no point to retain it in Dialect 3. Actually almost all UDFs of 1990-s are obsolete and redundant now. – Arioch 'The Sep 06 '17 at 13:44
  • experienced guys support the suggestion to do explicit `commit`s after important schema changes, if to keep the SQL script compatible. Or - if to bind oneself to ISQL tool specifics - to engage the AUTO-DDL option https://firebirdsql.org/manual/isql-interactive.html#d0e37753 – Arioch 'The Sep 06 '17 at 13:57
  • Then I recommend removing the declaration of the data type D_BOOLEAN AND THE D_DATE AS TIMESTAMP no? Regarding my version of Firebird is 3.0.2. I explain, my script that generates the structure of the database comes from Interbase 5 as a minimum. I can put the complete script and maybe they can advise me better. – Roman345 Sep 07 '17 at 06:08
  • D_DATE should be replaced with native DATE data type, not with timestamp. Read the links above about date/time data types. `F_CUTTIME` function to be dropped and replaced with `CAST( AS DATE)`. This may require revising your legacy apps and checking all queries to remove the function form them too. – Arioch 'The Sep 07 '17 at 08:51
  • As of Boolean - again, your legacy database an applications tends to use "T" an "F" for values. In case your new language/library support native FB boolean type, your app to be rewritten to use `True` and `False` instead. – Arioch 'The Sep 07 '17 at 08:53
  • Now, your immediate problem with script is that you do not commit DDL-type statements. So the function does not exist yet, when you try to create domains. However.... IB5 was VERY long ago. And there can be a lot of changes. Download Release Notes of Firebird 2.1.x and read section about compatibility with FB 1.x for example. You try to jump over MANY holes at once: IB5 -> IB6 -> FB1 -> FB2 -> FB3. High chance to fall down. You should read very attentively into what changed and how it affects compatibility!!!! Maybe you'd hire commercial consultants on it. – Arioch 'The Sep 07 '17 at 08:55
  • you have both obligation and opportunity to heavily modernize your program. Trying to get away with only minor patches would most probably leave you with many small undetected problems, kind of a quagmire. – Arioch 'The Sep 07 '17 at 08:57
  • at very least scan through all documents that mention "migration", "enhancement", "upgrade" and such at [ https://www.firebirdsql.org/en/reference-manuals/ ] and at [ https://www.firebirdsql.org/en/white-papers-presentations/ ] – Arioch 'The Sep 07 '17 at 09:01

1 Answers1

1

About function F_CUTTIME - if it was taken from InterBase 5, I mean, it was compiled for InterBase 5, then it MUST be recompiled for any new InterBase or Firebird. If it cannot be recompiled, then the only way is to move to Dialect 3, and use basic Firebird functions and CAST to work with separate DATE and TIME variables.

Dmitry Kuzmenko
  • 36
  • 1
  • "MUST be recompiled" - why? ib_maloc? but the bogus here `free_it` only affects `CSTRING` type vars, no? – Arioch 'The Sep 07 '17 at 11:49
  • well, FREE_IT for timestamp is suspicious, right. But 1. if author uses 64bit Firebird, definitely 32bit udf will not work 2. ib_util.dll is portable, and can save situation, but we do not know how F_CUTTIME was compiled - is it really uses ib_malloc, or not. – Dmitry Kuzmenko Sep 09 '17 at 11:01
  • `how F_CUTTIME was compiled - is it really uses ib_malloc, or not` - I do not think that matters a slightest: no CSTRING is used in this funciton – Arioch 'The Sep 11 '17 at 08:56