Simple Firebird Query

Question

I am trying to do a while loop in Firebird to execute all the values from an SP using FlameRobin tool. However this is not working. Any suggestion?

declare i int = 0;
while ( i <= 2 ) do BEGIN
   SELECT p.SOD_AUTO_KEY, p.CURRENCY_CODE, p.SO_CATEGORY_CODE, p.SO_NUMBER, p.INVC_NUMBER,  p.ENTRY_DATE, p.SHIP_DATE, p.NEXT_SHIP_DATE, p.CONDITION_CODE, p.QTY_ORDERED,       p.QTY_PENDING_INVOICE, p.QTY_INVOICED, p.UNIT_PRICE, p.EXCHANGE_RATE, p.UNIT_COST,     p.ITEM_NUMBER, p.CONSIGNMENT_CODE, p.NOTES, p.STOCK_LINE, p.STM_AUTO_KEY, p.SERIAL_NUMBER,     p.REMARKS, p.PN, p.PNM_AUTO_KEY, p.GR_CODE, p.CUSTOMER_PRICE, p.OPEN_FLAG, p.ROUTE_CODE,     p.ROUTE_DESC, p.COMPANY_CODE, p.SITE_CODE, p.COMPANY_NAME, p.COMPANY_REF_NUMBER, p.CUST_REF,     p.HOT_PART
   FROM SPB_SALESHISTORY(i) p
   i = i + 1;
end

Error Message I get:

Preparing query: declare i int = 0
Error: *** IBPP::SQLException ***
Context: Statement::Prepare( declare i int = 0 )
Message: isc_dsql_prepare failed

SQL Message : -104
can't format message 13:896 -- message file C:\Windows\firebird.msg not found

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 9
i


Total execution time: 0.004s

This is what I tried but it only says "Script Execution Finished" and does not return any results:

    set term !! 
EXECUTE BLOCK returns(p) AS
declare i integer = 0
BEGIN
while ( i <= 1000 ) do BEGIN
   SELECT p.SOD_AUTO_KEY, p.CURRENCY_CODE, p.SO_CATEGORY_CODE, p.SO_NUMBER, p.INVC_NUMBER,  p.ENTRY_DATE, p.SHIP_DATE, p.NEXT_SHIP_DATE, p.CONDITION_CODE, p.QTY_ORDERED,p.QTY_PENDING_INVOICE, p.QTY_INVOICED, p.UNIT_PRICE, p.EXCHANGE_RATE, p.UNIT_COST,     p.ITEM_NUMBER, p.CONSIGNMENT_CODE, p.NOTES, p.STOCK_LINE, p.STM_AUTO_KEY, p.SERIAL_NUMBER,     p.REMARKS, p.PN, p.PNM_AUTO_KEY, p.GR_CODE, p.CUSTOMER_PRICE, p.OPEN_FLAG, p.ROUTE_CODE,     p.ROUTE_DESC, p.COMPANY_CODE, p.SITE_CODE, p.COMPANY_NAME, p.COMPANY_REF_NUMBER, p.CUST_REF,     p.HOT_PART
   FROM SPB_SALESHISTORY(i) p
   i = i + 1
end
END !!

Mark,

I tried your suggestion however I got the following error:

 set term!!;
EXECUTE BLOCK RETURNS (
    SOD_AUTO_KEY Integer,
    CURRENCY_CODE Char(3),
    SO_CATEGORY_CODE Char(10),
    SO_NUMBER Char(12),
    INVC_NUMBER Char(12),
    ENTRY_DATE Timestamp,
    SHIP_DATE Timestamp,
    NEXT_SHIP_DATE Timestamp,
    CONDITION_CODE Varchar(10),
    QTY_ORDERED Double precision,
    QTY_PENDING_INVOICE Double precision,
    QTY_INVOICED Double precision,
    UNIT_PRICE Double precision,
    EXCHANGE_RATE Double precision,
    UNIT_COST Double precision,
    ITEM_NUMBER Integer,
    CONSIGNMENT_CODE Char(10),
    NOTES Blob sub_type 1,
    STOCK_LINE Integer,
    STM_AUTO_KEY Integer,
    SERIAL_NUMBER Varchar(40),
    REMARKS Varchar(50),
    PN Varchar(40),
    PNM_AUTO_KEY Integer,
    GR_CODE Varchar(10),
    CUSTOMER_PRICE Double precision,
    OPEN_FLAG Char(1),
    ROUTE_CODE Char(1),
    ROUTE_DESC Varchar(20),
    COMPANY_CODE Varchar(10),
    SITE_CODE Varchar(10),
    COMPANY_NAME Varchar(50),
    COMPANY_REF_NUMBER Varchar(30),
    CUST_REF Varchar(15),
    HOT_PART Char(1) 
    )
     AS
declare i integer;
BEGIN
i=0;
while ( i <= 2 ) do 
BEGIN
   for SELECT SOD_AUTO_KEY,CURRENCY_CODE,SO_CATEGORY_CODE, SO_NUMBER,INVC_NUMBER,ENTRY_DATE, SHIP_DATE, NEXT_SHIP_DATE, CONDITION_CODE, QTY_ORDERED,QTY_PENDING_INVOICE, QTY_INVOICED, UNIT_PRICE, EXCHANGE_RATE, UNIT_COST,ITEM_NUMBER, CONSIGNMENT_CODE, NOTES, STOCK_LINE, STM_AUTO_KEY, SERIAL_NUMBER,REMARKS, PN, PNM_AUTO_KEY, GR_CODE, CUSTOMER_PRICE, OPEN_FLAG, ROUTE_CODE,ROUTE_DESC, COMPANY_CODE, SITE_CODE, COMPANY_NAME, COMPANY_REF_NUMBER, CUST_REF, HOT_PART
   FROM SPB_SALESHISTORY (i)
   into :SOD_AUTO_KEY, :CURRENCY_CODE, :SO_CATEGORY_CODE, :SO_NUMBER, :INVC_NUMBER,
   :ENTRY_DATE, :SHIP_DATE, :NEXT_SHIP_DATE, :CONDITION_CODE, :QTY_ORDERED,:QTY_PENDING_INVOICE,
   :QTY_INVOICED, :UNIT_PRICE, :EXCHANGE_RATE, :UNIT_COST,     :ITEM_NUMBER, :CONSIGNMENT_CODE, :NOTES, :STOCK_LINE,
   :STM_AUTO_KEY, :SERIAL_NUMBER,     :REMARKS, :PN, :PNM_AUTO_KEY, :GR_CODE, :CUSTOMER_PRICE, :OPEN_FLAG, :ROUTE_CODE,:ROUTE_DESC,
   :COMPANY_CODE, :SITE_CODE, :COMPANY_NAME, :COMPANY_REF_NUMBER, :CUST_REF,:HOT_PART
   DO
    suspend;
   i = i + 1;
    end
END!!
SET TERM;!!

Error:

Message: isc_dsql_prepare failed

SQL Message : -206
can't format message 13:794 -- message file C:\Windows\firebird.msg not found

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -206
Column unknown
I
At line 46, column 27


Total execution time: 0.005s

Because I think it would be the same syntax. Should I remove it? — user3314399, Mar 17 '14 at 17:42
The error message indicates that you are missing the `firebird.msg` file in the appropriate location (by default the folder above the folder containing `fbclient.dll`, or set the environment `FIREBIRD_MSG` to the folder containing `firebird.msg`. As a result you don't get the actual error message(s). — Mark Rotteveel, Mar 18 '14 at 08:03
Thanks Mark, however it looks like it does not like the I on "FROM SPB_SALESHISTORY (i)" what do you think? — user3314399, Mar 18 '14 at 17:23
And I got raid of the firebird.msg error by copying the file to windows folder. But, I still get the "Column unknown I". — user3314399, Mar 18 '14 at 17:36

Mark Rotteveel · Accepted Answer · 2020-12-31T14:53:37.473

Based on your comments on the answer of Ain, it looks like you also want to return the selected values from the EXECUTE BLOCK. Your RETURNS (p) is invalid and will not work. You need to explicitly declare all columns you want to return, and you need to SUSPEND each row.

In addition you are also forgetting several statement terminators (;), and you can't declare the variable and its value together. The resulting execute block would be something like:

set term !!;
EXECUTE BLOCK returns (
    SOD_AUTO_KEY INTEGER,
    /* ... */
    HOT_PART VARCHAR(255)
) AS
    declare i integer;
BEGIN
    i = 0;
    while ( i <= 1000 ) do 
    BEGIN
       FOR SELECT SOD_AUTO_KEY, /* ... */ HOT_PART
           FROM SPB_SALESHISTORY(i) 
           INTO :SOD_AUTO, /* ... */ :HOT_PART
       DO
           SUSPEND;
       i = i + 1;
    end
END!!
SET TERM ;!!

I have left out some of the columns for brevity and guessed at their types.

Thank you Mark. I tired your suggestion and now I am very close to the solution. However I received an error for the (i). I edited my post including the newest code and the error message. — user3314399, Mar 18 '14 at 15:29

score 0 · Answer 2 · answered Mar 17 '14 at 20:03

0

No, you can't execute such scripts directly in Flamerobin's query window. I think simplest way would be to wrap your script into an stored procedure which you would drop after you're done with the result. To create the temp SP right-click on the Procedures node in the Flamerobin's database tree and select Create new - this creates the SP sceleton for you where you can insert your code.

answered Mar 17 '14 at 20:03

ain

22,394
3
54
74

1

Youre missing an terminating `;` in the end of the select but otherwise looks OK. Also, I think Firebird doesn't recognise `int` as a type, use `integer`. – ain Mar 17 '14 at 20:54
I tried the execute block but it does not return any result. Please refer to my update above. – user3314399 Mar 17 '14 at 22:09
@ain You can use `EXECUTE BLOCK` from the flamerobin query window – Mark Rotteveel Mar 18 '14 at 07:43
@user3314399 you are not correctly declaring the return value(s) of the execute block. You need to specify all columns that you want to return (including type); and when returning multiple rows you need to use suspend after each row. – Mark Rotteveel Mar 18 '14 at 07:46
@MarkRotteveel Right... however, the `EXECUTE BLOCK` way is basically as much work as wraping the script into SP (you have to drop the SP later which you don't have to do with EB but there is menuitems for creating SP sceleton and droppin SP, so it is less typing). And it is easier to keep SP around until youre done with testing. But EB is good option too so I'm +1 it :) – ain Mar 18 '14 at 15:00

score 0 · Answer 3 · answered Mar 17 '14 at 22:07

0

You will need to wrap your stored procedure like code in a EXECUTE BLOCK statement.

answered Mar 17 '14 at 22:07

nater

374
1
7

I tried the execute block but it does not return any result. Please refer to my update above. – user3314399 Mar 17 '14 at 22:10

score 0 · Answer 4 · answered Jan 13 '22 at 20:43

0

Your sql script may be corrupted

answered Jan 13 '22 at 20:43

user17927859

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 14 '22 at 02:51

Simple Firebird Query

4 Answers4