2

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!

Martin Schapendonk
  • 12,893
  • 3
  • 19
  • 24
sHooP
  • 173
  • 1
  • 8
  • AFAIK IBExpert/Firebird does not use PL/SQL. I changed your labels a bit and hope someone else will pick this up now. – Martin Schapendonk Sep 26 '19 at 09:04
  • In your "insert to FTE" statement you are inserting the integer into FTE_TABLE not FTE. Is that a typo? – Ed Mendez Sep 26 '19 at 14:22
  • just make your first query `order by austritt1 descending nulls first` and go on iterating. As soon as the value falls behind the threshold parameter - cancel the loop. This of course would preclude chance to avoid iterating and using `insert-select`. But if the source and target database is the same - you can just make it a `Stored Procedure` on that database to be called for your master DB, or even issue `execute block` to that database. – Arioch 'The Sep 26 '19 at 16:25

1 Answers1

2

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197