sqlfirebirdibexpert

IBExpert insert into with column value given by variable

Within IBExpert I'm looping over Dates within a "Master Procedure" and try to set the currently looped date as value within the "Date" Column of the Table I'm inserting into.

The Table I'm inserting into is very simple:

Table Name: FTE_TABLE
|---------------------|------------------|------------------|
|         GESCHST     |        DATUM     |       FTE        |
|---------------------|------------------|------------------|
|         Integer     |        Date      |      Integer     |
|---------------------|------------------|------------------|

I've tried numerous approaches. My understanding though is that it should look somewhat like this:

FOR EXECUTE STATEMENT
('
SELECT
    geschst,
    :XDATUM_FILTER as DATUM,
    count(personalnr)

FROM personal

WHERE
    eintritt1 is not null
    and (austritt1 is null or austritt1 >=  :XDATUM_FILTER)

GROUP BY geschst, DATUM
')
(XDATUM_FILTER := XDATUM_FILTER)

          on external 'xxx'
          as user 'xxx' password 'xxx'

          into :XGESCHST, :XDATUM, :XFTE

 do
  begin

    execute statement

    ('insert into FTE (GESCHST, DATUM, FTE_TABLE)

  values

(:GESCHST, :DATUM, :FTE)
')

   (GESCHST:= XGESCHST, DATUM := XDATUM, FTE:=XFTE)

        on external 'xxx'
        as user 'xxx' password 'xxx';

  end

I get this Errormessage:

Error Message:
----------------------------------------
Unsuccessful execution caused by system error that does not preclude 
successful execution of subsequent statements.
Execute statement error at isc_dsql_prepare :
335544569 : Dynamic SQL Error
335544436 : SQL error code = -804
335544573 : Data type unknown

The expected result would be that the the columns GESCHST and FTE are filled with the return values of the SELECT Statement while the DATUM Column in filled with the variable XDATUM_FILTER

Thanks in advance for any hints!

Solution

  • If you use a parameter in the select clause (like you do with :XDATUM_FILTER), then Firebird doesn't know the datatype of that parameter. This causes the Data type unknown error.

    To fix this, you need to explicitly cast the parameter to the desired type, so use:

    SELECT
    geschst,
    cast(:XDATUM_FILTER as DATE) as DATUM,
    count(personalnr)

    This only works in recent Firebird 3.0 and higher, in earlier versions casting parameters is not possible.

    The reason is that Firebird needs to know the type of the parameter at prepare time, and it cannot use the variable to determine this.

    However, you could also just leave this column out: you don't need to select this column in the remote database to get the desired results.

    Also, if the remote database you're selecting from and the remote database you're inserting to are the same database, it might be better (faster and simpler) to use INSERT ... SELECT instead of selecting, iterating over the result and inserting for each row.