Using a variable in the select clause of a CURSOR

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Using a variable in the select clause of a CURSOR

mmbc
This post was updated on .
Is using a variable in the select clause of a CURSOR currently supported?
It seems according to this link that it is:

http://hsqldb.10974.n7.nabble.com/DECLARE-result-CURSOR-WITH-RETURN-throws-error-when-accessing-parameter-td1766.html

but according to this link it is not? :

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:

        <dependency org="org.hsqldb" name="hsqldb" rev="2.3.1" conf="test->master">
            <artifact name="hsqldb" ext="jar"/>
        </dependency>

Thanks

Mary


Reply | Threaded
Open this post in threaded view
|

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

Fred Toussi-2
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: http://hsqldb.10974.n7.nabble.com/DECLARE-result-CURSOR-WITH-RETURN-throws-error-when-accessing-parameter-td1766.html but according to this link it is not? : 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: Using a variable in the select clause of a CURSOR
Sent from the 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.
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

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

mmbc
  That worked!

Thank you so much Fred!!!