Roy,

<< Just to make sure you're not heading down the wrong road things exploded
when I went to run the script not outside of it, and it seemed to be only
after another running the mark as read procedure. This is what took me so
long to track the little beastie down. I could run the procedure calling the
script several times one after the other and nothing went wrong. As soon as
it ran as part of the mark as read procedure kerpow. >>

That's what I'm looking into.  The result set should be nil, and should stay
nil, so that when you try to use Open it should cause an "Error creating
table handle" error message.

<< This will teach me to cut'n'paste from an example where I don't
understand it - guess who's example I was following <vbg> >>

Well, as I said above, it should be harmless to do what you did, with the
worse being that you get the above error if you actually try to use Open.
It should be the same as using Open on an INSERT statement in a TEDBQuery
component.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I think I've just identified another learning opportunity for me

>That's what I'm looking into. The result set should be nil, and should stay
>nil, so that when you try to use Open it should cause an "Error creating
>table handle" error message.

What open are you referring to? Not the table that's created?

Roy Lambert [Team Confused]

Roy,

<< What open are you referring to? Not the table that's created? >>

Actually, in this case it's the ExecScript, not an Open call.  Internally,
it's the same thing if the script returns a result set.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

I still cannot reproduce this here with 2.01 B1.  I'm using this script
(Roy6 is my database):

SCRIPT(IN USERID VARCHAR)
BEGIN

DECLARE SQLStatement VARCHAR;
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name =
''emCounts''';
OPEN InfoCursor;

IF (ROWCOUNT(InfoCursor) > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLE emCounts';
END IF;

CLOSE InfoCursor;

SET SQLStatement = 'CREATE TABLE emCounts AS
SELECT _BoxNo ,
_BoxName,
(SELECT COUNT(_fkMailBoxes) from "Roy6".EMails WHERE _fkMailBoxes =
MailBoxes._Boxno
AND
(EMails._Private = FALSE OR EMails._fkUsers = '''+USERID+ ''')) AS _OnFile ,
(SELECT COUNT(_fkMailBoxes) from "Roy6".emReadStatus WHERE _fkUsers =
'''+USERID+ ''' AND _fkMailboxes = MailBoxes._Boxno) AS _Read,
0 AS _Unread
FROM "Roy6".MailBoxes
WHERE _Monitored = TRUE
WITH DATA';

PREPARE ResultStmt FROM SQLStatement;

EXECUTE ResultStmt USING USERID, USERID;

EXECUTE IMMEDIATE 'UPDATE emCounts SET _Unread = _OnFile - _Read';
EXECUTE IMMEDIATE 'CREATE INDEX PK ON emCounts (_BoxNo)';

END

and this code:

  with EDBScript1 do
     begin
     ParamByName('USERID').AsString := 'TIM';
     ExecScript;
     Close;
     end;

Of course, I don't have any data in the source tables, but that shouldn't
make any difference in this case.

Are you using the same database for the TEDBScript component that you're
using for the $Disk database name in the script ?  I tried it both ways, and
it works every time.  I'm also making sure to use FastMM4 with my test
project, and it reports no issues at all.

Do you have the capability to reproduce this in a test project ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I've just spent a chunk of time stripping stuff out so I can run that form stand alone.I thought I'd use the exact same sql as you so went to cut'n'paset.

Before I spend any more time can you confirm that you tried running the SQL with the "WITH RETURN" clause. Not as you've posted which doesn't create the error.

Roy Lambert

Roy,

<< Before I spend any more time can you confirm that you tried running the
SQL with the "WITH RETURN" clause. Not as you've posted which doesn't create
the error. >>

This is the SQL straight out of the TEDBScript component on the main form:

SCRIPT(IN USERID VARCHAR)
BEGIN
DECLARE SQLStatement VARCHAR;
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR WITH RETURN FOR ResultStmt;
PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name =
''emCounts''';
OPEN InfoCursor;
IF (ROWCOUNT(InfoCursor) > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLE emCounts';
END IF;
CLOSE InfoCursor;
SET SQLStatement = 'CREATE TABLE emCounts AS
SELECT _BoxNo ,
_BoxName,
(SELECT COUNT(_fkMailBoxes) from "Roy6".EMails WHERE _fkMailBoxes =
MailBoxes._Boxno
AND
(EMails._Private = FALSE OR EMails._fkUsers = '''+USERID+ ''')) AS _OnFile ,
(SELECT COUNT(_fkMailBoxes) from "Roy6".emReadStatus WHERE _fkUsers =
'''+USERID+ ''' AND _fkMailboxes = MailBoxes._Boxno) AS _Read,
0 AS _Unread
FROM "Roy6".MailBoxes
WHERE _Monitored = TRUE
WITH DATA';
PREPARE ResultStmt FROM SQLStatement;
EXECUTE ResultStmt USING USERID, USERID;
EXECUTE IMMEDIATE 'UPDATE emCounts SET _Unread = _OnFile - _Read';
EXECUTE IMMEDIATE 'CREATE INDEX PK ON emCounts (_BoxNo)';
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Subtly different from the last post

I'll let you know if I can create a testbed for you without all the other 3rd party stuff.

Roy Lambert

Tim


The score so far

Bug: 97 Roy: 0

I thought I'd work in the app until I reached a point where I had something I could use outside. I've spent the last 4 - 6 hours removing each 3rd party component and testing for the presence of the bug still being there, and since is a complex form its taken a while.

Two of the components are Dave Baldwin's HTMLViewer and ElTree lite. Until I removed HTMLViewer I still had the bug. Remove that and I still had the bug but it had SHIFTED from when I move around the emails to when I close the app somewhere in my log user out procedure.

Its there. I can create it consistently within the app, but not yet in a form you can use.

Roy Lambert

Tim


I've now also tried outside the app with just enough changes to the form to make it work as a stand alone app and I don't get the error.

Roy Lambert

Roy,

<< I've now also tried outside the app with just enough changes to the form
to make it work as a stand alone app and I don't get the error. >>

Hmmm.  Okay.  I'll see if I can get FastMM to detect a memory overwrite here
using full-debug mode.

--
Tim Young
Elevate Software
www.elevatesoft.com