Quantcast

Re: Temporary table in a procedure

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Temporary table in a procedure

Paul Slater

Hello Fred,

 

Sorry to be so long replying. Thank you for this. It is good to know that the engine is being developed. In the meantime I got around it the Oracle way and declared a global temporary table.

 

Many thanks, Paul

 

From: Fred Toussi [[hidden email]]
Sent: Tuesday, 31 March 2015 3:17 AM
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] FW: Temporary table in a procedure

 

The DECLARE CURSOR feature did not support selecting from tables that are declared inside the procedure. The latest SVN code supports this feature. You can build the jar and use it until the final 2.3.3 release (date as yet unknown)

 

Fred

 

On Sun, Mar 29, 2015, at 05:36, Paul Slater wrote:

Hello,

 

I want to declare a table in a procedure, populate it, manipulate the data and return a result set. Unfortunately I can’t get even the most basic attempt to compile. Please can someone help me with this….

 

create procedure sp_Balances(IN argAccount varchar(32))

modifies sql data dynamic result sets 1

begin atomic

declare table tbal(tnum integer,tdate date,trow integer);

declare resout cursor for select * from tbal for read only;     ç Fails saying tbal doesn’t exist ??

/* Get raw data */

insert into tbal(select TxnNum,TxnDate,RowNum() from XL order by TxnDate,TxnNum);

/* Manipulate data and send it back */

open resout;

end

 

Many Thanks, Paul

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

Dive into the World of Parallel Programming The Go Parallel Website, sponsored

by Intel and developed in partnership with Slashdot Media, is your hub for all

things parallel software development, from weekly thought leadership blogs to

news, videos, case studies, tutorials and more. Take a look and join the

_______________________________________________

Hsqldb-user mailing list


------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Temporary table in a procedure

Fred Toussi-2
You should build the latest SVN /base/trunk and test.
 
Fred
 
On Fri, Apr 3, 2015, at 12:14, Paul Slater wrote:

Hello Fred,

 

Sorry to be so long replying. Thank you for this. It is good to know that the engine is being developed. In the meantime Igot around it the Oracle way and declared a global temporary table.

 

Many thanks, Paul

 

From: Fred Toussi [[hidden email]]
Sent: Tuesday, 31 March 2015 3:17 AM
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] FW: Temporary table in a procedure

 

The DECLARE CURSOR feature did not support selecting from tables that are declared inside the procedure. The latest SVN code supports this feature. You can build the jar and use it until the final 2.3.3 release (date as yet unknown)

 

Fred

 

On Sun, Mar 29, 2015, at 05:36, Paul Slater wrote:

Hello,

 

I want to declare a table in a procedure, populate it, manipulate the data and return a result set. Unfortunately I can’t get even the most basic attempt to compile. Please can someone help me with this….

 

create procedure sp_Balances(IN argAccount varchar(32))

modifies sql data dynamic result sets 1

begin atomic

declare table tbal(tnum integer,tdate date,trow integer);

declare resout cursor for select * from tbal for read only;     ç Fails saying tbal doesn’t exist ??

/* Get raw data */

insert into tbal(select TxnNum,TxnDate,RowNum() from XL order by TxnDate,TxnNum);

/* Manipulate data and send it back */

open resout;

end

 

Many Thanks, Paul

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

Dive into the World of Parallel Programming The Go Parallel Website, sponsored

by Intel and developed in partnership with Slashdot Media, is your hub for all

things parallel software development, from weekly thought leadership blogs to

news, videos, case studies, tutorials and more. Take a look and join the

_______________________________________________

Hsqldb-user mailing list

------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
_______________________________________________
Hsqldb-user mailing list

------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Temporary table in a procedure

Paul Slater

Hello Fred,

 

Well that was an adventure! My very first Ant build J

 

I found that the procedure will compile and run but the table is not dropped after the procedure ends. This means that the result set from a previous call is returned as well as the current; as written this procedure’s first call gets 20 rows, second call gets 40, third gets 60 &c.

 

It can be fixed by adding a ‘truncate table tbal’ into the code, but I couldn’t get the procedure to compile if I tried ‘drop table tbal’. Commit or Rollback after the procedure call didn’t help. I also found that the temporary table could not be accessed or dropped outside the procedure, even though it must have been hanging around in memory.

 

FWIW I’m using the HSQLDB server, with Squirrel SQL 3.6 client and JDK 1.8.0_40.

 

HTH, Paul

 

From: Fred Toussi [mailto:[hidden email]]
Sent: Saturday, 4 April 2015 4:28 AM
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] Temporary table in a procedure

 

You should build the latest SVN /base/trunk and test.

 

Fred

 

On Fri, Apr 3, 2015, at 12:14, Paul Slater wrote:

Hello Fred,

 

Sorry to be so long replying. Thank you for this. It is good to know that the engine is being developed. In the meantime Igot around it the Oracle way and declared a global temporary table.

 

Many thanks, Paul

 

From: Fred Toussi [[hidden email]]
Sent: Tuesday, 31 March 2015 3:17 AM
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] FW: Temporary table in a procedure

 

The DECLARE CURSOR feature did not support selecting from tables that are declared inside the procedure. The latest SVN code supports this feature. You can build the jar and use it until the final 2.3.3 release (date as yet unknown)

 

Fred

 

On Sun, Mar 29, 2015, at 05:36, Paul Slater wrote:

Hello,

 

I want to declare a table in a procedure, populate it, manipulate the data and return a result set. Unfortunately I can’t get even the most basic attempt to compile. Please can someone help me with this….

 

create procedure sp_Balances(IN argAccount varchar(32))

modifies sql data dynamic result sets 1

begin atomic

declare table tbal(tnum integer,tdate date,trow integer);

declare resout cursor for select * from tbal for read only;     ç Fails saying tbal doesn’t exist ??

/* Get raw data */

insert into tbal(select TxnNum,TxnDate,RowNum() from XL order by TxnDate,TxnNum);

/* Manipulate data and send it back */

open resout;

end

 

Many Thanks, Paul

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

Dive into the World of Parallel Programming The Go Parallel Website, sponsored

by Intel and developed in partnership with Slashdot Media, is your hub for all

things parallel software development, from weekly thought leadership blogs to

news, videos, case studies, tutorials and more. Take a look and join the

_______________________________________________

Hsqldb-user mailing list

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

Dive into the World of Parallel Programming The Go Parallel Website, sponsored

by Intel and developed in partnership with Slashdot Media, is your hub for all

things parallel software development, from weekly thought leadership blogs to

news, videos, case studies, tutorials and more. Take a look and join the

_______________________________________________

Hsqldb-user mailing list


------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Temporary table in a procedure

Fred Toussi-2
Thanks for reporting. Issue has been fixed and committed. It no longer keeps the rows from the last invocation.
 
The rest of your observations show expected behavior as the table cannot be accessed outside the procedure.
 
Fred
 
On Sat, Apr 4, 2015, at 03:22, Paul Slater wrote:

Hello Fred,

 

Well that was an adventure! My very first Ant build J

 

I found that the procedure will compile and run but the table is not dropped after the procedure ends. This means that the result set from a previous call is returned as well as the current; as written this procedure’s first call gets 20 rows, second call gets 40, third gets 60 &c.

 

It can be fixed by adding a ‘truncate table tbal’ into the code, but I couldn’t get the procedure to compile if I tried ‘drop table tbal’. Commit or Rollback after the procedure call didn’t help. I also found that the temporary table could not be accessed or dropped outside the procedure, even though it must have been hanging around in memory.

 

FWIW I’m using the HSQLDB server, with Squirrel SQL 3.6 client and JDK 1.8.0_40.

 

HTH, Paul

 

From: Fred Toussi [mailto:[hidden email]]
Sent: Saturday, 4 April 2015 4:28 AM
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] Temporary table in a procedure

 

You should build the latest SVN /base/trunk and test.

 

Fred

 

On Fri, Apr 3, 2015, at 12:14, Paul Slater wrote:

Hello Fred,

 

Sorry to be so long replying. Thank you for this. It is good to know that the engine is being developed. In the meantime Igot around it the Oracle way and declared a global temporary table.

 

Many thanks, Paul

 

From: Fred Toussi [[hidden email]]
Sent: Tuesday, 31 March 2015 3:17 AM
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] FW: Temporary table in a procedure

 

The DECLARE CURSOR feature did not support selecting from tables that are declared inside the procedure. The latest SVN code supports this feature. You can build the jar and use it until the final 2.3.3 release (date as yet unknown)

 

Fred

 

On Sun, Mar 29, 2015, at 05:36, Paul Slater wrote:

Hello,

 

I want to declare a table in a procedure, populate it, manipulate the data and return a result set. Unfortunately I can’t get even the most basic attempt to compile. Please can someone help me with this….

 

create procedure sp_Balances(IN argAccount varchar(32))

modifies sql data dynamic result sets 1

begin atomic

declare table tbal(tnum integer,tdate date,trow integer);

declare resout cursor for select * from tbal for read only;     ç Fails saying tbal doesn’t exist ??

/* Get raw data */

insert into tbal(select TxnNum,TxnDate,RowNum() from XL order by TxnDate,TxnNum);

/* Manipulate data and send it back */

open resout;

end

 

Many Thanks, Paul

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

Dive into the World of Parallel Programming The Go Parallel Website, sponsored

by Intel and developed in partnership with Slashdot Media, is your hub for all

things parallel software development, from weekly thought leadership blogs to

news, videos, case studies, tutorials and more. Take a look and join the

_______________________________________________

Hsqldb-user mailing list

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

Dive into the World of Parallel Programming The Go Parallel Website, sponsored

by Intel and developed in partnership with Slashdot Media, is your hub for all

things parallel software development, from weekly thought leadership blogs to

news, videos, case studies, tutorials and more. Take a look and join the

_______________________________________________

Hsqldb-user mailing list

------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
_______________________________________________
Hsqldb-user mailing list

------------------------------------------------------------------------------
BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
Develop your own process in accordance with the BPMN 2 standard
Learn Process modeling best practices with Bonita BPM through live exercises
http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual- event?utm_
source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Loading...