6 messages in com.mysql.lists.javaRe: Temporary tables in a JDBC connec...
FromSent OnAttachments
Héctor A. Abreu17 Aug 2003 16:27 
Christopher Taylor18 Aug 2003 00:53 
Héctor A. Abreu18 Aug 2003 04:53 
Jaap Stelwagen18 Aug 2003 05:17 
Bo Do18 Aug 2003 05:31 
Héctor A. Abreu18 Aug 2003 06:50 
Subject:Re: Temporary tables in a JDBC connection
From:Jaap Stelwagen (cont@jaap.nl)
Date:08/18/2003 05:17:09 AM
List:com.mysql.lists.java

Hector,

If you use jsp or servlets for your application you can get the session id by:

String tablename = request.getSession().getId();

I believe this will always give a valid table name.

If you have a compulsive or neurotic personality disorder, you might want to add some logic to clean up unused tables.

Jaap

On Mon, 2003-08-18 at 13:54, Héctor A. Abreu wrote:

Thank you.

I have another question: is it possible to create these tables so that every
table has a unique name depending on the session or user that creates them? I'm
afraid that if the table name is a fixed one on the CREATE TABLE statement,
there could be conflict betwen two different users creating the same table from
different sessions.

I found the following information in a JDBC forum: "the syntax for creating the temporary table by appending the # in front of the
table name. remeber though You can create local and global temporary tables. Local temporary tables are
visible only in the current session; global temporary tables are visible to all
sessions. Prefix local temporary table names with single number sign (#table_name), and
prefix global temporary table names with a double number sign (##table_name). CREATE TABLE #MyTempTable (cola INT PRIMARY KEY) INSERT INTO #MyTempTable VALUES (1) 2) In your java program have the sequence of a) create a table. b) do your stuff with the table c) drop the table at the end of the program. "

But when I try to test that syntax on MySQL (not JDBC yet), it prompts me to the
next line as if it was missing a semicolon (;), and when I add a semicolon or
whatever I do to complete the statement, it throws a syntax error. I'm wondering
why this #MyTempTable stuff is not mentioned on the CREATE TABLE section of the
MySQL manual. Is not MySQL syntax? I will appreciate any suggestion or advise so I can properly group the items of
my session bean shopping cart, either by using temporary tables or by
manipulating a Java Hash Table or Vector, as long as the data remains for that
particular session.

Regards.

----- Original Message -----

From: "Christopher Taylor" <csta@nanshu.com> To: "Héctor A. Abreu" <hect@comcast.net>; <ja@lists.mysql.com> Sent: Monday, August 18, 2003 3:54 AM Subject: Re: Temporary tables in a JDBC connection

http://www.mysql.com/doc/en/CREATE_TABLE.html

----- Original Message ----- From: "Héctor A. Abreu" <hect@comcast.net> To: <ja@lists.mysql.com> Sent: Monday, August 18, 2003 8:28 AM Subject: Temporary tables in a JDBC connection

Hi, there.

Can anybody tell me what's the syntax for the SQL statements to create temporary tables?

I'm developing a shopping cart, and at the end of every purchase (checkout) I want to organize the items of the Session Bean Hash Table that contains the items, grouping them by category, brand, price and showing the serial number of each item. I was thinking to do SQL-style operations like GROUP BY on the Hash Table itself, but it's kind of difficult, so that's why I thought that filling a temporary database table with the Hash Table data could be the solution to the problem of grouping and ordering the items contained in the Shopping cart Bean.

Does it make sense? If so, what's the SQL syntax to create temporary-session-like tables so I can include it in my JDBC code?

Thank you in advance for any information or alternative suggestion.