Problems with views from Firebird

[Cecil Westerhof]

I have to maintain code written by someone else. It makes extensive use of
dynamic views and stored procedures. The strange thing is that when we
execute the statement "DROP VIEW MV_22_", that it never gives an error, but
is not always done. When I then try to delete it with Database Workbench, it
gives an error of in use. So it looks like sometimes connections are not
terminated correctly. Is there a way to find out where a connection is not
terminated correctly? Is there a way to make the timeout of a connection
shorter?

[Bob Barrows [MVP]]

Cecil Westerhof wrote:

> I have to maintain code written by someone else. It makes extensive
> use of dynamic views and stored procedures. The strange thing is that
> when we execute the statement "DROP VIEW MV_22_", that it never gives
> an error, but is not always done.

First of all, you need to figure this out. No error? That's just not right!
Are you using On Error Resume Next? If so, are you checking the Err object
immediately after attempting to execute this line? Are you checking the
connection's Errors collection?

> When I then try to delete it with
> Database Workbench, it gives an error of in use. So it looks like
> sometimes connections are not terminated correctly. Is there a way to
> find out where a connection is not terminated correctly? Is there a
> way to make the timeout of a connection shorter?

Are you closing and destroying connection explicitly instead of depending on
automatic garbage collection?

By default, connections are kept open for 60 seconds in order to allow them
to be used by new processes instead of spawning new connections every time a
process needs one. This default time can be shortened but it is not really
recommended that you do so: you will reduce the scalability of your app due
to forcing new connections to be created every time a process needs one.
Each connection takes time to open, and it also uses memory both on the web
server and on the database server.

I question the need for all this dynamic stuff. As you are discovering, it
is impairing the scalability of your application.I don't know what kind of
task it would be to rewrite it, but it sounds as if it would be worth doing.
Perhaps you can do it in small steps. Start with the page where this problem
is occurring. Figure out a different way to perform its task without needing
to create and drop a view. It should be possible. Fix this issue then move
onto the next issue ...

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Cecil Westerhof]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OWosEXVIEHA.3444@TK2MSFTNGP11.phx.gbl...

> Cecil Westerhof wrote:

> > I have to maintain code written by someone else. It makes extensive
> > use of dynamic views and stored procedures. The strange thing is that
> > when we execute the statement "DROP VIEW MV_22_", that it never gives
> > an error, but is not always done.

>
> First of all, you need to figure this out. No error? That's just not

right!

> Are you using On Error Resume Next? If so, are you checking the Err object
> immediately after attempting to execute this line? Are you checking the
> connection's Errors collection?

That is what I would think. I use On Error Resume Next, but after this I
check the Err object. The code:

on error resume next
oOrgDbConn.BeginTrans
DropStr = "DROP VIEW " & arrViewNames(arrIndex)
f.WriteLine Now() & " " & "trying to drop view "
f.WriteLine Now() & " " & DropStr
oOrgDbConn.execute DropStr, adExecuteNoRecords
If(Err.number <> 0) Then
'Execution failed
f.WriteLine Now() & " " & "Did NOT succeed"
f.WriteLine Err.Description & vbCrLf & Err.number
closeConnections true
errorRaised "S000402",""
else 'Execution succeeded
on error goto 0
f.WriteLine Now() & " " & "Did succeed"
oOrgDbConn.CommitTrans
End If
f.WriteLine ""

In my log this gives:
4/13/2004 2:52:59 PM trying to drop view
4/13/2004 2:52:59 PM DROP VIEW SV_22_
4/13/2004 2:52:59 PM Did succeed

So it should be okay, but it is not. I even did put a 'on Error goto 0' to
get the possibility that the CommitTrans is not succesfull. At the moment a
create view is executed, I get:

unsuccessful metadata update
Table MV_22_ already exists

> > When I then try to delete it with
> > Database Workbench, it gives an error of in use. So it looks like
> > sometimes connections are not terminated correctly. Is there a way to
> > find out where a connection is not terminated correctly? Is there a
> > way to make the timeout of a connection shorter?

>
> Are you closing and destroying connection explicitly instead of depending

on

> automatic garbage collection?

It is code from someone else, but I am afraid that he is depending on
garbage collection. I have allready found a lot off problems I had to
change.

> By default, connections are kept open for 60 seconds in order to allow

them

> to be used by new processes instead of spawning new connections every time

a

> process needs one. This default time can be shortened but it is not really
> recommended that you do so: you will reduce the scalability of your app

due

> to forcing new connections to be created every time a process needs one.
> Each connection takes time to open, and it also uses memory both on the

web

> server and on the database server.

Then this is not the problem. Even five minutes later I still get an in use
error.

> I question the need for all this dynamic stuff. As you are discovering, it
> is impairing the scalability of your application.I don't know what kind of
> task it would be to rewrite it, but it sounds as if it would be worth

doing.

> Perhaps you can do it in small steps. Start with the page where this

problem

> is occurring. Figure out a different way to perform its task without

needing

> to create and drop a view. It should be possible. Fix this issue then move
> onto the next issue ...

I did this also. But they opted for something with Crystal Report. This
needs a recordset. Because the way the data is organised, it is only to get
a recordset with a view. I allready wanted to rewrite the code, but I am not
allowed, because 'It works'. The only problem is that the system has to be
rebooted after every report. Another problem is that there are more then 20
reports, so my boss is afraid about the time it will take to rewrite
everything. (He does not see how much time I allready spend on it.)

[Bob Barrows [MVP]]

Cecil Westerhof wrote:

> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:OWosEXVIEHA.3444@TK2MSFTNGP11.phx.gbl...

>> Cecil Westerhof wrote:

>>> I have to maintain code written by someone else. It makes extensive
>>> use of dynamic views and stored procedures. The strange thing is
>>> that when we execute the statement "DROP VIEW MV_22_", that it
>>> never gives an error, but is not always done.

>>
>> First of all, you need to figure this out. No error? That's just not
>> right! Are you using On Error Resume Next? If so, are you checking
>> the Err object immediately after attempting to execute this line?
>> Are you checking the connection's Errors collection?

>
> That is what I would think. I use On Error Resume Next, but after
> this I check the Err object. The code:
>
> on error resume next
> oOrgDbConn.BeginTrans
> DropStr = "DROP VIEW " & arrViewNames(arrIndex)
> f.WriteLine Now() & " " & "trying to drop view "
> f.WriteLine Now() & " " & DropStr
> oOrgDbConn.execute DropStr, adExecuteNoRecords

That's strange, you are missing an argument. It should be:
oOrgDbConn.execute DropStr, ,adExecuteNoRecords

> If(Err.number <> 0) Then
> 'Execution failed
> f.WriteLine Now() & " " & "Did NOT succeed"
> f.WriteLine Err.Description & vbCrLf & Err.number
> closeConnections true
> errorRaised "S000402",""
> else 'Execution succeeded
> on error goto 0
> f.WriteLine Now() & " " & "Did succeed"
> oOrgDbConn.CommitTrans

You should check for an error here. The error might not occur until the
transaction is committed.

I don't understand why he used an explicit transaction for a single
statement's execution, anyways. It is not needed: there will be an implicit
transaction. Instead of adding more error-checking, just comment out the
begintrans and committrans statements.

> I did this also. But they opted for something with Crystal Report.
> This needs a recordset. Because the way the data is organised, it is
> only to get a recordset with a view.

Again, I question this statement. Not with the idea that a view is needed.
It's the "dynamic" part I question, but you know the application ...

> I allready wanted to rewrite the
> code, but I am not allowed, because 'It works'. The only problem is
> that the system has to be rebooted after every report. Another
> problem is that there are more then 20 reports, so my boss is afraid
> about the time it will take to rewrite everything. (He does not see
> how much time I allready spend on it.)

That is a problem. Start documenting your time, I guess. And the server's
downtime.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Cecil Westerhof]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:um9T2GWIEHA.2688@tk2msftngp13.phx.gbl...

> > That is what I would think. I use On Error Resume Next, but after
> > this I check the Err object. The code:
> >
> > on error resume next
> > oOrgDbConn.BeginTrans
> > DropStr = "DROP VIEW " & arrViewNames(arrIndex)
> > f.WriteLine Now() & " " & "trying to drop view "
> > f.WriteLine Now() & " " & DropStr
> > oOrgDbConn.execute DropStr, adExecuteNoRecords

>
> That's strange, you are missing an argument. It should be:
> oOrgDbConn.execute DropStr, ,adExecuteNoRecords

If I do this I get:

Wrong number of arguments or invalid property assignment

> I don't understand why he used an explicit transaction for a single
> statement's execution, anyways. It is not needed: there will be an

implicit

> transaction. Instead of adding more error-checking, just comment out the
> begintrans and committrans statements.

I will do that.

>

> > I did this also. But they opted for something with Crystal Report.
> > This needs a recordset. Because the way the data is organised, it is
> > only to get a recordset with a view.

>
> Again, I question this statement. Not with the idea that a view is needed.
> It's the "dynamic" part I question, but you know the application ...

I am only updating the application. Certainly the part with the views is a
little in the dark for me. It is a kind of spagetti.

[Bob Barrows [MVP]]

Cecil Westerhof wrote:

> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:um9T2GWIEHA.2688@tk2msftngp13.phx.gbl...

>>> oOrgDbConn.execute DropStr, adExecuteNoRecords

>>
>> That's strange, you are missing an argument. It should be:
>> oOrgDbConn.execute DropStr, ,adExecuteNoRecords

>
> If I do this I get:
>
> Wrong number of arguments or invalid property assignment
>
>

Very strange. I assumed he had the ADO constants defined since he used the
adExecuteNoRecords constant. Does he have Option Explicit at the top of the
script block?

Here is a good way to get the ADO constants defined in all your pages:
[url]http://www.aspfaq.com/show.asp?id=2112[/url]


In the meantime, just add this line to your page to verify that this is the
problem:
Const adExecuteNoRecords = &H00000080

Actually, the command type should also be specified:

Const adCmdText = &H0001
Const adExecuteNoRecords = &H00000080
....
oOrgDbConn.execute DropStr, , _
adExecuteNoRecords + adCmdText

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Cecil Westerhof]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ukDwZvWIEHA.716@TK2MSFTNGP12.phx.gbl...

> Cecil Westerhof wrote:

> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:um9T2GWIEHA.2688@tk2msftngp13.phx.gbl...

>

> >>> oOrgDbConn.execute DropStr, adExecuteNoRecords
> >>
> >> That's strange, you are missing an argument. It should be:
> >> oOrgDbConn.execute DropStr, ,adExecuteNoRecords

> >
> > If I do this I get:
> >
> > Wrong number of arguments or invalid property assignment
> >
> >

> Very strange. I assumed he had the ADO constants defined since he used the
> adExecuteNoRecords constant. Does he have Option Explicit at the top of

the

> script block?
>
> Here is a good way to get the ADO constants defined in all your pages:
> [url]http://www.aspfaq.com/show.asp?id=2112[/url]

In the code there is:
<!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common
Files\System\adamsado15.dll" -->

As I understood it, this does the same?

The code starts with TRANSACTION=REQUIRED. When using Option Explicit this
gives problems. When I comment this out I got a lot errors, but not in the
code that is used to drop the views. So I removed it again. Strange thing is
that now it als works with:
oOrgDbConn.execute DropStr, ,adExecuteNoRecords
Most of the times the created view is dropped and when not I now get the
message that the view is in use. From the 22 places where a view is created
only in 4 it still goes wrong. Now I have to find out what is done different
on these 4 places.

> oOrgDbConn.execute DropStr, , _
> adExecuteNoRecords + adCmdText

I changed this also.

Thanks for the help.

[Bob Barrows [MVP]]

Cecil Westerhof wrote:

>
> In the code there is:
> <!-- METADATA TYPE="typelib" FILE="C:\Program Files\Common
> Files\System\adamsado15.dll" -->
>
> As I understood it, this does the same?

It should

>
> The code starts with TRANSACTION=REQUIRED. When using Option Explicit
> this gives problems.

That's because TRANSACTION is not declared. Add:
dim TRANSACTION

> When I comment this out I got a lot errors, but
> not in the code that is used to drop the views. So I removed it
> again. Strange thing is that now it als works with:
> oOrgDbConn.execute DropStr, ,adExecuteNoRecords
> Most of the times the created view is dropped and when not I now get
> the message that the view is in use. From the 22 places where a view
> is created only in 4 it still goes wrong. Now I have to find out what
> is done different on these 4 places.
>

>> oOrgDbConn.execute DropStr, , _
>> adExecuteNoRecords + adCmdText

>
> I changed this also.
>
> Thanks for the help.

You're welcome
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.