VB.NET create/insert/select From Temp table

[tnguyen]

Hi all,
Google search engine brought me here. I am so desperated and hope you guys could give me some help. This is my first .NET project to connect to AS400 database. I used Microsoft.Data.Odbc, and was able to connect to the database with no problem. Next I want to generate a report where I need to query many different tables and do pivotting on the dataset ... So I decide to save some of the dataset to a temp table, and I was able to do so. However, I couldn't access any of those temp tables. I know they are saved in QTemp database, but I don't know how to connect to them.
Could you guys give me some hints, please? Thanks so much.


Here is my code. The create table part works, but I can't insert or select
-------------------------------------------------------------------------------------------------------
oCon = New OdbcConnection("Dsn=PRODUCTION")
oCmd = New OdbcCommand()
oCmd.Connection = oCon
oCmd.CommandText = "Declare Global Temporary Table MyTest (" & _
"IEITM NUMERIC(8,0), " & _
"IELEDG CHARACTER(2)) on commit preserve rows"
oCon.Open()
oCmd.ExecuteNonQuery()

[docdiesel]

Hi,

if I understand you right, you connect to the db a first time, create the temp. table, write some date to it and subsequently disconnect. Then another script connects to the very same db, and it's not able to read from the temp. table.

Have a look at http://publib.boulder.ibm.com/infoce...n/r0003272.htm for documentation about the DECLARE GLOBAL TEMPORARY TABLE statement. The temp. tables are not persistent and will be dropped after disconnecting. So you'll have to run your SQL statements right after filling the table and before disconnecting, or use a real table to store your results. I hope this information did help? Or did I guess wrong?

Regards,

Bernd