Hi All,

I am getting the same result set even by using stored procedures also.

Actually my requirement is Like this.

I have two tables

UserInfo
======
user_id varchar(80) ** not an INTEGER, can be any
character string ***
user_name varchar(80)
password varchar(80)

The second table is

UserPropertyInfo
============
user_id varchar(80) Foreign Key UserInfo
property_name varchar(50)
property_value varchar(50)

the corresponding bean class is User.java
---------------------------------------------------------------
public class User
{
public String user_id;
public String user_name;
public String password;

public List listUserProperties;

// and the corresponding getters and setters.
}

I have one API which retrives the records between specified range from
the database.
Here I am using Ibatis as data mapper between firebird and java
classes.

public List getProperties(int fromRecord, int toRecord)
{
List list =
SqlMapClient.queryForList("selectUsersInRange",null,fromRecord,
toRecord);
}

Datatabase Data
=================

UserInfo
----------
userId userName password
===== ======= =======
ABC Test Test
DEF Test2 Test2


UserPropertyInfo
------------------------
userId propertyName propertyValue
==== ========== ==========
ABC City XXXX
ABC Country YYYY
DEF null null


When I use firebird query to join the two tables



Thanks n Regards,
=============================
Rambabu Piridi.
Software Engineer,
Wipro Technologies,
Madhapur,
Hyderabad.

e-Mail : rambabu.piridi@... <mailto:rambabu.piridi@...>
Mobile: 99491 83303.
==============================


________________________________

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Wednesday, July 26, 2006 12:22 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] FW: ===Problem in creating stored
procedure===



At 04:11 PM 26/07/2006, you wrote:

>
>
>
>I am trying to create a stored procedure with the following
>information , But it is showing exception at first key word.
>
>Here is the structute
>
> CREATE PROCEDURE GetUserInfo(i_first integer, i_skip integer)
> RETURNS (usr_id VARCHAR(80), usr_name VARCHAR(80), usr_pwd
>VARCHAR(80), role_id VARCHAR(1), usr_code VARCHAR(20), email_id VARCHAR
>256), description VARCHAR(300)
> )
> AS
> BEGIN
> for select first:i_first, skip :i_skip usr_id, usr_name, usr_pwd,
>role_id, usr_code, email_id, description
> FROM usr_basic_info
> INTO :usr_id, :usr_name, :usr_pwd, :role_id, :usr_code, :email_id,
>:description
> DO
> BEGIN
> SUSPEND;
> END
> END
>
>ERROR
>======
> 10:46:50 [CREATE - 0 row(s), 0.000 secs] [Error Code: 335544569, SQL
>State: HY000] GDS Exception. 335544569. Dynamic SQL Error
>SQL error code = -104
>Token unknown - line 7, char 18
>:
>... 1 statement(s) executed, 0 row(s) affected, execution time 0.000

sec

>
>Can we use FIRST/SKIP inside a StoredProcedure.

It is STUPID to use FIRST/SKIP inside a SP, since you can just run
through the set and output whatever rows you like, with 1000% more
efficiency. So I won't even bother to analyse your various syntax
errors.....FIRST/SKIP is a rather dopey invention mimicking what
people have to do in database engines that don't support outputting
sets directly from cursor operations.

Do something like this:

CREATE PROCEDURE GetUserInfo(i_first integer, i_skip integer)
RETURNS (usr_id VARCHAR(80), usr_name VARCHAR(80), usr_pwd
VARCHAR(80), role_id VARCHAR(1), usr_code VARCHAR(20), email_id VARCHAR
256), description VARCHAR(300)
)
AS
declare current_count integer = 0;
declare output_count integer = 0;
BEGIN
for select
usr_id, usr_name, usr_pwd,
role_id, usr_code, email_id, description
FROM usr_basic_info
INTO :usr_id, :usr_name, :usr_pwd, :role_id, :usr_code, :email_id,
:description
DO
BEGIN
current_count = current_count + 1;
if (current_count > i_skip) then
begin
if (output_count >= i_first) then
Exit;
else
SUSPEND;
current_output = current_output + 1;
end
END
END

./heLen






[Non-text portions of this message have been removed]