Maintain 1 session/connection with ADO

[WILLIEWANKA]

Hey everyone, i'm currently working on an application that will enable business users to reduce the size of their tables. It a two step process, the 1st step evaluates the data types, and the ranges within each data type and reccomends a new datatype (which the user can change if they don't want the reccomended datatype). The second step takes the finalized datatype and goes through a compression routine to pick the values that would realize the most savings.

The problem is i'm using volatile tables, and ADO.net does not use the same connection for each statement I execute. 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 acheive any of the following:

a. Find a tool that already does what I want it to do (very doubtful)

b. Create tables that exist as long as a user has at least 1 session open (doubtful)

c. Use the same connection/session for every statement executed in ADO.net (less doubtful)



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

 

[tdatgod]

Hi,
It sounds like your connection is going through a CONNECTION POOL. is there anyway to access the Teradata Box directly?

 

[WILLIEWANKA]

I doubt it, the only way I can do it is through ODBC. I checked and there seems to be a way to set pooling to false.


When making the connection string, just add "Pooling=False;

 

[WILLIEWANKA]

Okay, I lied...that doesn't work. Still getting the same error.

 

[tdatgod]

Hi,
How Stable is your Teradata System? Volatile tables are deleted during System restarts? Global Temp Tables will live over system restarts.

 

[WILLIEWANKA]

The system is very stable.

It was my understanding that Global Temp Tables will still exist after a session has closed. Is that not correct? Are they similar to volatile tables, except other users can see and use them too?