title: Simple Firebird Query
body: <p>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?</p>
  
  <pre><code>declare i int = 0;
  while ( i &lt;= 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
  </code></pre>
  
  <p>Error Message I get:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This is what I tried but it only says "Script Execution Finished" and does not return any results:</p>
  
  <pre><code>    set term !! 
  EXECUTE BLOCK returns(p) AS
  declare i integer = 0
  BEGIN
  while ( i &lt;= 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 !!
  </code></pre>
  
  <p>Mark,</p>
  
  <p>I tried your suggestion however I got the following error:</p>
  
  <pre><code> 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 &lt;= 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;!!
  </code></pre>
  
  <p>Error:</p>
  
  <pre><code>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
  </code></pre>