Maintain 1 session/connection with ADO

[WILLIEWANKA]

Hey everyone, i'm currently working on an application for Teradata that will enable business users to reduce the size of their tables.

The problem is i'm using volatile tables, and ADO.net does not use the same connection for each statement I execute. A volatile table is dropped by the system when a user closes their session. Even though I only open the connection once, after a few statements it will say the volatile table doesn't exist, meaning it has changed the session and the table has been dropped.

I don't want to use a normal user table because I don't want to accidentally drop a table with the same name (it's a very unique name, but hey....you never know)


Is there any way to use the same connection/session for every statement executed in ADO.net?





Here is a example of the code i'm running

Dim conn As New ADODB.Connection
Dim strConn as String
Dim strSQL as String

strConn = "my connection string"

conn.open(strConn)

strSQL = "create set volatile table mytable (" & vbCrLf & _
"nbr_values decimal(10)," & vbCrLf & _
"column_value varchar(300))" & vbCrLf & _
"primary index (column_value)" & vbCrLf & _
"on commit preserve rows;"


for I = 0 to mydatatable.Rows.Count - 1

strSQL = "insert into mytable select count(*) ," & _
mydatatable.Rows(I).Item("Columnname") & "' from oldtable group by 2;"

conn.Execute (strSQL)

'This is where it fails, saying table mytable cannot be found

Next

 

[Korach]

You forgot the execute the sql that creates the table.

 

[WILLIEWANKA]

Thanks, i can't make changes, so just use it as an example of the logic.

 

[chiph]

If the table is temp ("volatile"), then you need to use the same connection object that created the table to query it. Temp tables are tied to the lifetime of the connection object.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[WILLIEWANKA]

The connection object does not close until the end of the sub.

I'm thinking connection pooling will open a different session within the connection....meaning the volatile table is not usable by any session other than the one that created the table.