Problems with stored procedures

[flzanini]

Hi,

I'm having a problem using the stored procedure component when using this to retrieve a dataset from database. The component ALWAYS return 1 row when there are no records in the table.

When using the query component, this problem does not occurr.

Below I will try to show how to produce this scenario with some screenshots..

This is my stored procedure:


This is my form:


The connection object is setted to use the transaction.

The DefaultConnection of the transaction component is setted to connection component with ReadCommited isolation level.

Properties of StoredProcedure component:
Connection: The connection component
Transaction: The transaction component
StoredProcName: P_TEST (The stored procedure of the first image)
IsQuery: TRUE

When right clicking on the stored procedure component and select Data Editor item, 1 row is returned, see below:


I did a test with a query component like this:

Query properties
Connection: The connection component
Transaction: The transaction component
SQL: SELECT * FROM T_USER WHERE CODE = 10000

When right clicking on the query component and select Data Editor item, 0 row is returned, see below:

[Plash]

Your stored procedure is written in such case that one row is always returned.

The TIBCQuery component will also return one row if you remove WHERE clause:

Code: Select all

SELECT * FROM T_USER

[flzanini]

Even is there no records in the table the query component will return 1 row? Why?

[Plash]

Change the code of your stored procedure to the following:

Code: Select all

FOR SELECT CODE, ID, NAME, DESCRIPTION, "PASSWORD", REMARKS
  FROM T_USER
  WHERE CODE = 10000
  INTO :CODE, :ID, :NAME, :DESCRIPTION, :"PASSWORD", :REMARKS
DO
  SUSPEND;