SQL Anywhere ultralite - UL.NET too many temporary tables in connection

Author

UL.NET too many temporary tables in connection

Dave Joyner

2005-12-06, 1:23 pm

9.0.2.3137

This error is cropping up periodically. What does it mean ? I saw a post
from 11/22 that seemed to indicate that GUID handling was the problem --
well, either that or the schema painter.

We're not using GUIDs so that can't be it.

Thanks
Dave

David Fishburn

2005-12-07, 9:23 am

"Dave Joyner" <d4ljoyn@yahoo.com> wrote in news:4395cfb3@forums
-2-dub of
sybase.public.sqlanywhere.ultralite:

DJ> 9.0.2.3137
DJ>
DJ> This error is cropping up periodically. What does it mean ? I saw a
DJ> post from 11/22 that seemed to indicate that GUID handling was the
DJ> problem -- well, either that or the schema painter.
DJ>
DJ> We're not using GUIDs so that can't be it.

How about giving us some context.

When are you getting this error?
During synchronization?
If so, how much data is syncing?
How many rows?

When performing a lot of inserts?

The more detail you provide, the better we are able to help.
--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Dave Joyner

2005-12-07, 9:23 am

David Fishburn wrote:
> How about giving us some context.
>
> When are you getting this error? During synchronization? If so, how
> much data is syncing? How many rows?
>
> When performing a lot of inserts?
>
> The more detail you provide, the better we are able to help.

I'm using a datareader to populate lists. Below is an example. Calling
this code repeatedly -- say every 5 seconds

What I wanted to know is : what does the error mean? There is almost no
documentation.

sql = new StringBuilder("select * from diagnosis_categories
dc");
sql.Append(" join diagnosis d on dc.diagnosis_ih_code = d.ih_code");
sql.Append(" where dc.category_id = ");
sql.Append(categoryID);
this.listCodes.Clear();
this.listCodes.Columns.Add("Diag", 50, HorizontalAlignment.Left);
this.listCodes.Columns.Add("Description", 185, HorizontalAlignment.Left);
IDataReader row = null;
try
{

log.Info("LoadDiagnosis before query " + sql.ToString());
row = ULHelper. ExecuteReader(PdaMai
nApp.Connection, sql.ToString());
log.Info("LoadDiagnosis after query");
while (row.Read())
{
ListViewItem item = this.listCodes.Items.Add( new
ListViewItem(row["Diagnosis.IH_Code"].ToString() ) );
item.SubItems.Add(row["d.Description"].ToString());
}
log.Info("LoadDiagnosis list complete");
}
catch(Exception exc)
{
log.Error(exc.Message);
}
finally
{
if (row != null)
{
row.Close();
}
}

Joshua Savill

2005-12-07, 11:23 am

Try adding a dispose() call after executing the SELECT statement.

--
Joshua Savill
iAnywhere Solutions - Product Support Analyst

"Dave Joyner" <d4ljoyn@gmail.com> wrote in message
news:4396f2ef$1@foru
ms-1-dub...
> David Fishburn wrote:
>
> I'm using a datareader to populate lists. Below is an example. Calling
> this code repeatedly -- say every 5 seconds
>
> What I wanted to know is : what does the error mean? There is almost no
> documentation.
>
>
> sql = new StringBuilder("select * from diagnosis_categories
dc");
> sql.Append(" join diagnosis d on dc.diagnosis_ih_code = d.ih_code");
> sql.Append(" where dc.category_id = ");
> sql.Append(categoryID);
> this.listCodes.Clear();
> this.listCodes.Columns.Add("Diag", 50, HorizontalAlignment.Left);
> this.listCodes.Columns.Add("Description", 185, HorizontalAlignment.Left);
> IDataReader row = null;
> try
> {
>
> log.Info("LoadDiagnosis before query " + sql.ToString());
> row = ULHelper. ExecuteReader(PdaMai
nApp.Connection, sql.ToString());
> log.Info("LoadDiagnosis after query");
> while (row.Read())
> {
> ListViewItem item = this.listCodes.Items.Add( new
> ListViewItem(row["Diagnosis.IH_Code"].ToString() ) );
> item.SubItems.Add(row["d.Description"].ToString());
> }
> log.Info("LoadDiagnosis list complete");
> }
> catch(Exception exc)
> {
> log.Error(exc.Message);
> }
> finally
> {
> if (row != null)
> {
> row.Close();
> }
> }

Dave Joyner

2005-12-07, 11:23 am

Joshua Savill wrote:
> Try adding a dispose() call after executing the SELECT statement.
>
Yes. I did that. No difference.

Is there a way I can detect when this condition is going to occur ?

I also tried changing the code to use a DataAdapter/DataSet with the same result.

Philippe Bertrand

2005-12-07, 11:23 am

This error typically occurs when one relies on garbage collection to clean
up ULCommand's. As stated in the docs, (and the docs for the Component
class which ULCommand is derived from), ULCommands should be explicitly
disposed of when you are done with them.

In C# it's as easy as pie:

using( ULCommand cmd = new ULCommand() ) {
do stuff with cmd
} // automatically calls cmd.Dispose() even when there are exceptions or
return statements

The only reason for keeping a ULCommand around is if you are executing a
query multiple times (no change to the SQL) - then there is some advantage
to this. Instead of dynamically altering the SQL, you use positional
parameters to make the SQL more flexible.

p.s. The post you were referring to had multiple problems - one with the
Guid due to a corrupt schema file and another with too many temporary tables
(which was fixed by proper disposal/closing of commands and results sets)

--
- Philippe Bertrand
iAnywhere Solutions, Inc.

Please include in your subject line what UltraLite API are you using
(I.E. UltraLite.NET, Native UltraLite for Java, Embedded SQL, C++,
(pure) Static Java UltraLite, etc.). Please include version and BUILD
numbers in your post.

Dave Joyner

2005-12-07, 11:23 am

Philippe Bertrand wrote:
> This error typically occurs when one relies on garbage collection to clean
> up ULCommand's. As stated in the docs, (and the docs for the Component
> class which ULCommand is derived from), ULCommands should be explicitly
> disposed of when you are done with them.
>
> In C# it's as easy as pie:
>
> using( ULCommand cmd = new ULCommand() ) {
> do stuff with cmd
> } // automatically calls cmd.Dispose() even when there are exceptions or
> return statements
>
> The only reason for keeping a ULCommand around is if you are executing a
> query multiple times (no change to the SQL) - then there is some advantage
> to this. Instead of dynamically altering the SQL, you use positional
> parameters to make the SQL more flexible.
>
> p.s. The post you were referring to had multiple problems - one with the
> Guid due to a corrupt schema file and another with too many temporary tables
> (which was fixed by proper disposal/closing of commands and results sets)
>
Thanks

Does ULDataAdapter.Dispose() properly dispose of the underlying ULCommands
that it is managing ?

Philippe Bertrand

2005-12-07, 1:23 pm

"Dave Joyner" <d4ljoyn@gmail.com> wrote in message
news:439719f0$1@foru
ms-1-dub...
>
> Does ULDataAdapter.Dispose() properly dispose of the underlying ULCommands
> that it is managing ?
>
No, it doesn't! It can't assume that you aren't sharing the commands with
something else.

I'll talk it over with my colleague

--
- Philippe Bertrand
iAnywhere Solutions, Inc.

Please include in your subject line what UltraLite API are you using
(I.E. UltraLite.NET, Native UltraLite for Java, Embedded SQL, C++,
(pure) Static Java UltraLite, etc.). Please include version and BUILD
numbers in your post.

Dave Joyner

2005-12-07, 8:24 pm

Philippe Bertrand wrote:
> "Dave Joyner" <d4ljoyn@gmail.com> wrote in message news:439719f0$1@foru
ms-1-dub...
>
>
> No, it doesn't! It can't assume that you aren't sharing the commands with something else.
>
> I'll talk it over with my colleague
>
Exactly. And likewise an arbitrary client of the DataAdapter
shouldn't be required to Dispose() the Select,Insert,Delete
commands (?)

Thank you for your time, I am able to code around this issue.

Sponsored Links