Hi,

> I have a simple SP that just tries to extract one row of data from
> table based on a string ref... But I always get an empty result when
> calling "SELECT * FROM SP_GET_CONTAINER(REF)" where REF is an already
> existing string in the CONTAINERREF column...

In order to make a procedure select-able, you need to add the keyword
SUSPEND on the place where you want to return the output parameters
to the client.

For executable procedure, use a StoredProc component of some kind.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> Anyone that can help me with what I am doing wrong?
>
> // Kind Regards
> Jonas Olofsson
>
> PROCEDURE SP_GET_CONTAINER (
> REF Varchar(50) CHARACTER SET NONE )
> RETURNS (
> CONTAINERID Integer,
> CONTAINERREF Varchar(50) CHARACTER SET NONE,
> STOCKLOCATIONID Integer,
> LOCATIONID Integer,
> NAME Varchar(50) CHARACTER SET NONE,
> DESCRIPTION Varchar(100) CHARACTER SET NONE,
> TAREWEIGHT Integer,
> STATUS Integer,
> LOTSALLOWED Integer )
> AS
> BEGIN
> SELECT CONTAINERID,CONTAINERREF,STOCKLOCATIONID,LOCATIONID,
> NAME,DESCRIPTION,TAREWEIGHT,STATUS,LOTSALLOWED
> FROM CONTAINERS
> WHERE CONTAINERREF = :REF AND DELETED IS NULL
> INTO :CONTAINERID,:CONTAINERREF,:STOCKLOCATIONID,
> :LOCATIONID,:NAME,:DESCRIPTION,:TAREWEIGHT,:STATUS,
> :LOTSALLOWED;
> END;
>
> TABLE CONTAINERS(
> CONTAINERID Integer NOT NULL,
> CONTAINERREF Varchar(50) CHARACTER SET NONE,
> STOCKLOCATIONID Integer,
> LOCATIONID Integer,
> NAME Varchar(50) CHARACTER SET NONE,
> DESCRIPTION Varchar(100) CHARACTER SET NONE,
> TAREWEIGHT Integer,
> STATUS Integer,
> LOTSALLOWED Integer,
> DELETED Timestamp,
> CONSTRAINT PK_CONTAINERS_1 PRIMARY KEY (CONTAINERID),
> CONSTRAINT UNQ_CONTAINERS_1 UNIQUE (CONTAINERREF)
> );
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>