Board index » delphi » MS SQL 7 - #temp_table - Problem with D5 TQuery.Edit

MS SQL 7 - #temp_table - Problem with D5 TQuery.Edit

I've migrated a program from Informix to MS SQL server 7.
I'm accessing MSSQL with MS ODBC driver 3.70.09.61 with Delphi 5 BDE 5.01.
The problem is that when using temporary tables (#local) after openning a
TQuery upon that temporary table the Edit method fails with an error from
the server saying it can not find #temp_table_name.

More strange, if I use a DBGrid I see the data as expected, if I try to edit
it I get the same error, but, if first I scroll all the records (fetching
them all) in the grid I can then edit anyone without problems. In any case
the SQL statement revealed by SQL Monitor is the same. By the way I Insert
and Append records without problems.

Would apreciate any help or hint on this problem. I'm new to MS SQL (and not
getting to be a fan i guess...).
Thank You for Your time.

                Andre' Carvalho

P.S. - by the way, I noticed that using :

  SELECT 'xxx' AS field1, 'xxx' AS field2
  INTO #my_temp
  FROM some_table
  WHERE 1=2

makes the resulting attributes NOT NULL in the temporary table. Is this the
expected behaviour ? It makes not much sense as we are talking about
temporary tables... is there a way of changing it ?

 

Re:MS SQL 7 - #temp_table - Problem with D5 TQuery.Edit


Hi. The BDE and data aware controls do not work very well with temporary
tables. You have to code up update and insert statements and use non-data
aware components. Alternatively use ADO - works nice.

--
Adroit Software Ltd
------------------------------
Remove the nospam. to mail me direct

Quote
"Andre' carvalho" <ac...@mail.pt> wrote in message news:3b8630a9_1@dnews...
> I've migrated a program from Informix to MS SQL server 7.
> I'm accessing MSSQL with MS ODBC driver 3.70.09.61 with Delphi 5 BDE 5.01.
> The problem is that when using temporary tables (#local) after openning a
> TQuery upon that temporary table the Edit method fails with an error from
> the server saying it can not find #temp_table_name.

> More strange, if I use a DBGrid I see the data as expected, if I try to
edit
> it I get the same error, but, if first I scroll all the records (fetching
> them all) in the grid I can then edit anyone without problems. In any case
> the SQL statement revealed by SQL Monitor is the same. By the way I Insert
> and Append records without problems.

> Would apreciate any help or hint on this problem. I'm new to MS SQL (and
not
> getting to be a fan i guess...).
> Thank You for Your time.

>                 Andre' Carvalho

> P.S. - by the way, I noticed that using :

>   SELECT 'xxx' AS field1, 'xxx' AS field2
>   INTO #my_temp
>   FROM some_table
>   WHERE 1=2

> makes the resulting attributes NOT NULL in the temporary table. Is this
the
> expected behaviour ? It makes not much sense as we are talking about
> temporary tables... is there a way of changing it ?

Re:MS SQL 7 - #temp_table - Problem with D5 TQuery.Edit


I have had problems a few times with #Temp tables and D5.

On one customer's project I worked on, using a #Temp table in a stored
procedure would not work.  No matter how I coded it as soon as I
called OPEN for the SP it would just hang and do nothing. I resorted
to using a static table as a temp table and purging it each time it
was used.

I was never able to use a #Temp table with a TQuery though.

My suspicion was that customer's SQL server's configuration.  Since
then I have never had a problem with #Temp tables and D5.

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com

Re:MS SQL 7 - #temp_table - Problem with D5 TQuery.Edit


Temp tables on an SQL Server are unique in the following ways:
1) The name begins with a # as in this statement CREATE TABLE #MyTempTable
2) They belong to tempdb database (a system database)
3) Although you think the tables name is #MyTempTable, the SQL Server gives
it its own uique name, using your name as the prefix.
4) Thinking more on the above. This SQL statement will return 0 records:
        CREATE TABLE #MyTempTable (TestField int)
        SELECT name FROM tempdb.dbo.sysobjects WHERE name = '#MyTempTable'
        DROP TABLE #MyTempTable
5) Another version of the above select:
        CREATE TABLE #MyTempTable (TestField int)
        SELECT name FROM tempdb.dbo.sysobjects WHERE name like
'#MyTempTable%'
        DROP TABLE #MyTempTable

        Sample result set from Query Ananlyzer:
        name
        --------------------------------------------------------
        #MyTempTable__ .... ________00000000000D

6)  The SQL Server's name for your temp table changes everytime you drop it
and create it, even though you always create it with the same name.
7) You will not be able to update any SQL Server temp table fields from
within Delphi, unless you know the tables real name. Think of them as
ReadOnly. You can however, possibly update joined fields (depends on how you
join them).

I hope this was not too long and I hope enough information was provided.

Sam C.

Quote
Reid Roman <re...@futuregenerationsoftware.com> wrote in message

news:3b86bf96_2@dnews...
Quote
> I have had problems a few times with #Temp tables and D5.

> On one customer's project I worked on, using a #Temp table in a stored
> procedure would not work.  No matter how I coded it as soon as I
> called OPEN for the SP it would just hang and do nothing. I resorted
> to using a static table as a temp table and purging it each time it
> was used.

> I was never able to use a #Temp table with a TQuery though.

> My suspicion was that customer's SQL server's configuration.  Since
> then I have never had a problem with #Temp tables and D5.

> --
> Reid Roman
> Future Generation Software
> http://www.futuregenerationsoftware.com

Re:MS SQL 7 - #temp_table - Problem with D5 TQuery.Edit


Quote
"Sam Changtum" <schang...@ahsminn.com> wrote in message

news:3b871d33_2@dnews...

Quote
> 5) Another version of the above select:
>         CREATE TABLE #MyTempTable (TestField int)
>         SELECT name FROM tempdb.dbo.sysobjects WHERE name like
> '#MyTempTable%'
>         DROP TABLE #MyTempTable

>         Sample result set from Query Ananlyzer:
>         name
>         --------------------------------------------------------
>         #MyTempTable__ .... ________00000000000D

Just a short observation: I don't think this could work properly. If more
then one user runs the same procedure, creating the temp table with the same
name, there will be more then one #MyTempTable____* instance in tempdb. The
output may be

         #MyTempTable__ .... ________00000000000D

         #MyTempTable__ .... ________000000000010

         #MyTempTable__ .... ________000000000012

and neither of these may belong to the process that ran the query. In that
case, DROP TABLE would raise an error.

rb

Re:MS SQL 7 - #temp_table - Problem with D5 TQuery.Edit


I also wanted to mention that you do not need to drop #Temp tables..
SQL Server does it automatically when it is out of scope like in Java.

--
Reid Roman
Future Generation Software
http://www.futuregenerationsoftware.com

Re:MS SQL 7 - #temp_table - Problem with D5 TQuery.Edit


You are most likely losing the scope
#temp tables are droped by the server when the scope changes.
Check to see how you connect to the server.
All so ther are global temp tables  ##temp, that can be referance outside the
scope
  where it is created.
But does open up problems of stepping on other users.  We use unique computer
Id and place the computer Id in the global temp table name  ##puterIDTempname
You also have to besure to drop the ##temp after your done.  They do not
automaticly
drop like #temp until SQL server is shut down.
Quote
Andre' carvalho wrote:
> I've migrated a program from Informix to MS SQL server 7.
> I'm accessing MSSQL with MS ODBC driver 3.70.09.61 with Delphi 5 BDE 5.01.
> The problem is that when using temporary tables (#local) after openning a
> TQuery upon that temporary table the Edit method fails with an error from
> the server saying it can not find #temp_table_name.

> More strange, if I use a DBGrid I see the data as expected, if I try to edit
> it I get the same error, but, if first I scroll all the records (fetching
> them all) in the grid I can then edit anyone without problems. In any case
> the SQL statement revealed by SQL Monitor is the same. By the way I Insert
> and Append records without problems.

> Would apreciate any help or hint on this problem. I'm new to MS SQL (and not
> getting to be a fan i guess...).
> Thank You for Your time.

>                 Andre' Carvalho

> P.S. - by the way, I noticed that using :

>   SELECT 'xxx' AS field1, 'xxx' AS field2
>   INTO #my_temp
>   FROM some_table
>   WHERE 1=2

> makes the resulting attributes NOT NULL in the temporary table. Is this the
> expected behaviour ? It makes not much sense as we are talking about
> temporary tables... is there a way of changing it ?

Re:MS SQL 7 - #temp_table - Problem with D5 TQuery.Edit


Thanks for Your interest

I don't think that's the case here. I can access the data in the temporary
table and althought MS server gives it a new name You can refer to the table
by the name You gave it including #

I managed to edit the data by first looping through all records with
QRYmyquery.Next. After fetching all records of the query the problem is gone
and I can use .Edit normally.

It's a strange thing and not good for performance too, but it works ...

I could'nt figure a way to solve the problem of the NOT NULL fields in
#temptable yet...

Andre'

Quote
"Vania" <Mich...@winvotes.com> wrote in message

news:3B8852E5.E4D05B0A@winvotes.com...
Quote
> You are most likely losing the scope
> #temp tables are droped by the server when the scope changes.
> Check to see how you connect to the server.
> All so ther are global temp tables  ##temp, that can be referance outside
the
> scope
>   where it is created.
> But does open up problems of stepping on other users.  We use unique
computer
> Id and place the computer Id in the global temp table name
##puterIDTempname
> You also have to besure to drop the ##temp after your done.  They do not
> automaticly
> drop like #temp until SQL server is shut down.

Re:MS SQL 7 - #temp_table - Problem with D5 TQuery.Edit


Yes this is true.
A temp table created by your connection belongs to you as the name given by
SQL server does too.
I myself have only used temp tables as a means to pass arrays of data to
stored procedures (i.e. calling sub stored procedures).
Also to compile data from many different tables into one, then doing a
select * from it in order to make a result set for a TADOStoredProcedure.

They are also dropped automatically when the process that created them loses
scope.

Other Threads