Board index » delphi » Syntax problem

Syntax problem


2007-05-10 11:37:12 PM
delphi227
TIA for your assistance and expertise. I spent a couple of Google
hours on this and am stumped.
I have this code for an SP:
SET TERM ^ ;
CREATE PROCEDURE name
AS
DECLARE VARIABLE variable_name DATE ;
BEGIN
variable_name = EXTRACT(DAY FROM current_date) + 1 ;
SELECT * FROM Mytable WHERE CAST(datetime AS DATE) < :variable_name ;
END
^
SET TERM ; ^
And am getting the following error:
SQL Message : -104
Invalid token
Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 9, char 68
;
The error occurs at the semicolon which ends the SELECT statement. It
seems I can not get these terminators in the right place
Any corrections, pointers, etc. are appreciated.
 
 

Re:Syntax problem

philhege writes:
Quote

CREATE PROCEDURE name
AS
DECLARE VARIABLE variable_name DATE ;
BEGIN
variable_name = EXTRACT(DAY FROM current_date) + 1 ;
SELECT * FROM Mytable WHERE CAST(datetime AS DATE) < :variable_name ;
END

And am getting the following error:
Token unknown - line 9, char 68
;
That's because, in the context of a stored procedure, the Select statement
is incomplete. I assume you are expecting one or more rows to be returned
from this procedure? The procedure must declare return variables, and the
select statement must place values into those variables.
CREATE PROCEDURE name
returns (
fld1 integer,
fld2 varchar(10),
fld3 <whatever ...>
)
AS
[...]
SELECT fldA, fldB, fldC FROM Mytable WHERE CAST(datetime AS DATE) <
:variable_name
INTO fld1, fld2, fld2;
That will return *ONE* row (and if the select results in more than one, an
error).
If you need to allow for more than one row then it needs to be:
FOR
SELECT fldA, fldB, fldC FROM Mytable WHERE CAST(datetime AS DATE) <
:variable_name
INTO fld1, fld2, fld2
DO BEGIN
SUSPEND;
END;
And the calling application needs to select from this procedure rather than
simply executing it:
select * from name;
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"If there is any principle of the Constitution that more imperatively
calls for attachment than any other, it is the principle of free
thought - not free thought for those who agree with us, but freedom for
the thought that we hate." - Oliver Wendell Holmes
 

Re:Syntax problem

Thanks for the reply, Wayne. I am just having a bit of cognitive
dissonance between SQL dialects. In MS SQL, I can construct a stored
procedure that returns a query result without declaring or populating
any return parameters. IOW
CREATE PROCEDURE MyProc
AS
BEGIN
Select * from MyTable
END
is perfectly legitimate and returns a result set to the caller.
The IB procedure I am writing will ultimately DELETE records from a
table, not SELECT them, so will the approach be any different?
Furthermore, where can I find decent online guidance for writing IB
SPs? The docs at www.firebirdsql.org/index.php leave a
lot to be desired.
Tks again,
PH
On May 10, 12:37 pm, "Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>
writes:
Quote
That's because, in the context of a stored procedure, the Select statement
is incomplete. I assume you are expecting one or more rows to be returned
from this procedure? The procedure must declare return variables, and the
select statement must place values into those variables.

CREATE PROCEDURE name
returns (
fld1 integer,
fld2 varchar(10),
fld3 <whatever ...>
)
AS
[...]
SELECT fldA, fldB, fldC FROM Mytable WHERE CAST(datetime AS DATE) <
:variable_name
INTO fld1, fld2, fld2;

That will return *ONE* row (and if the select results in more than one, an
error).

If you need to allow for more than one row then it needs to be:

FOR
SELECT fldA, fldB, fldC FROM Mytable WHERE CAST(datetime AS DATE) <
:variable_name
INTO fld1, fld2, fld2
DO BEGIN
SUSPEND;
END;

And the calling application needs to select from this procedure rather than
simply executing it:

select * from name;

--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"If there is any principle of the Constitution that more imperatively
calls for attachment than any other, it is the principle of free
thought - not free thought for those who agree with us, but freedom for
the thought that we hate." - Oliver Wendell Holmes