Re: Using a variable in the select clause of a CURSOR

It is supported. In this case you have a DECLARE for the variable,
followed by a DECLARE for the cursor. The two are compiled together and
cannot reference one another.
You can separate the declarations like this:

CREATE PROCEDURE MyProcedure ( IN guid LONGVARCHAR, IN type INTEGER,
OUT result_code LONGVARCHAR) READS SQL DATA DYNAMIC RESULT SETS 1 BEGIN
ATOMIC DECLARE product_id LONGVARCHAR;
BEGIN ATOMIC
DECLARE client_products CURSOR WITH RETURN FOR SELECT * FROM MY_TABLE
WHERE product = product_id FOR READ ONLY;
SELECT accessLevel, product INTO result_code, product_id FROM
LOOKUP_TABLE WHERE ID = guid;
OPEN client_products;
END;
END;


Fred Toussi


On Fri, Jan 10, 2014, at 22:20, mmbc wrote:

  Is using a variable in the select clause of a CURSOR currently
  supported? It seems according to this link that it is:
  [1]http://hsqldb.10974.n7.nabble.com/DECLARE-result-CURSOR-WITH-RETU
  RN-throws-error-when-accessing-parameter-td1766.html but according
  to this link it is not? :
  [2]http://hsqldb.10974.n7.nabble.com/user-lacks-privilege-or-object-
  not-found-td3565.html currently I am attempting to create junit
  tests using hsqldb in-memory. CREATE PROCEDURE MyProcedure ( IN guid
  LONGVARCHAR, IN type INTEGER, OUT result_code LONGVARCHAR) READS SQL
  DATA DYNAMIC RESULT SETS 1 BEGIN ATOMIC DECLARE product_id
  LONGVARCHAR; DECLARE client_products CURSOR WITH RETURN FOR SELECT *
  FROM MY_TABLE WHERE product = product_id FOR READ ONLY; SELECT
  accessLevel, product INTO result_code, product_id FROM LOOKUP_TABLE
  WHERE ID = guid; OPEN client_products; END; When I run my program I
  get java.sql.SQLSyntaxErrorException: user lacks privilege or object
  not found: PRODUCT_ID but if I replace product_id in the procedure
  with a valid value in the table say 123456 it runs fine. Is using a
  variable in the where clause of a cursor supported? Am I doing this
  correctly? Is there another way to create and run a cursor? If it
  cannot be done this way would using a table return type from the
  procedure do the trick/be seen as a ResultSet? I need a work around
  to continue my planned path of using hsqldb. I believe I am using
  2.3.1 my ivy.xml is as follows: Thanks Mary
  _______________________________________________________________

  View this message in context: [3]Using a variable in the select
  clause of a CURSOR
  Sent from the [4]HSQLDB - User mailing list archive at Nabble.com.

-----------------------------------------------------------------------
-------

CenturyLink Cloud: The Leader in Enterprise Cloud Services.

Learn Why More Businesses Are Choosing CenturyLink Cloud For

Critical Workloads, Development Environments & Everything In Between.

Get a Quote or Start a Free Trial Today.

[5]http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ost
g.clktrk

_______________________________________________

Hsqldb-user mailing list

[6][email protected]

[7]https://lists.sourceforge.net/lists/listinfo/hsqldb-user

References

1. http://hsqldb.10974.n7.nabble.com/DECLARE-result-CURSOR-WITH-RETURN-throws-error-when-accessing-parameter-td1766.html
2. http://hsqldb.10974.n7.nabble.com/user-lacks-privilege-or-object-not-found-td3565.html
3. http://hsqldb.10974.n7.nabble.com/Using-a-variable-in-the-select-clause-of-a-CURSOR-tp3997.html
4. http://hsqldb.10974.n7.nabble.com/HSQLDB-User-f3.html
5. http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
6. mailto:[email protected]
7. https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user