Metadata update statement is not allowed by the current database SQL dialect 1

Question

I'm working with a Firebird 2.5 database and need a way to count weekdays within a month. To achieve this, I wanted to create a stored procedure:

CREATE PROCEDURE GetWorkingDays (Start_Date DATE, End_Date DATE)
RETURNS (Working_Days INTEGER)
AS
BEGIN
  Working_Days = 0;
  WHILE (Start_Date <= End_Date) DO
  BEGIN
    IF ((EXTRACT(WEEKDAY FROM Start_Date) NOT IN (0, 6))) THEN
    BEGIN
      Working_Days = Working_Days + 1;
    END
    Start_Date = Start_Date + 1;
  END
  SUSPEND;
END

Unfortunately, I'm encountering the following error and I'm not sure how to proceed. Can anyone help?

Error: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -817
Metadata update statement is not allowed by the current database SQL dialect 1
SQLState:  42000
ErrorCode: 335544569

Answer 1

The problem is that you're currently connecting to a dialect 1 database with a dialect 3 connection, or using a driver that prepares statements as dialect 3, even when using a dialect 1 connection.

The dialect 1 DATE data type is actually a date+time, and was renamed to TIMESTAMP in dialect 3 (to match the SQL standard name). The dialect 3 DATE is a date only, which doesn't exist in dialect 1. This is what causes the error.

There are two possible options:

1. Make sure you connect with dialect 1 (e.g. some drivers need an explicit connection property)
2. or replace occurrences of DATE with TIMESTAMP in your script.

That said, dialect 1 was deprecated 25 years ago in InterBase 6.0, and you should not be using it. You need to upgrade your database to dialect 3. The InterBase 6.0 Getting Started Guide (available from https://firebirdsql.org/en/reference-manuals/ under "InterBase 6.0 Manuals" in the "Full set, cross-referenced") provides instructions how to upgrade.

Answer 2

As I can understand in your error you are using dialect 1 with your database and it is deprecated for a long time.

You can check your database dialect using this query

SELECT MON$SQL_DIALECT FROM MON$DATABASE 

you can use gfix utility to change your dialect

gfix -SQL_dialect 3 dynamo -user SYSDBA -password XXX

Answer 3

Would it solve it?

Create Procedure GetWorkingDays (
    Start_Date DATE,
    End_Date DATE)
RETURNS (
    Working_Days INTEGER)
AS
DECLARE VARIABLE Current_Date DATE;
BEGIN
    Working_Days = 0;
    Current_Date = :Start_Date;
    WHILE (Current_Date <= End_Date) DO
    BEGIN
        IF (EXTRACT(WEEKDAY FROM Current_Date) NOT IN (0, 6)) THEN
        BEGIN
            Working_Days = Working_Days + 1;
        END
        Current_Date = Current_Date + 1;
    END
    SUSPEND;
END;