Support Forums - View Thread

View Thread

The following is the text of the current message along with any replies.

Messages 1 to 7 of 7 total

Tue, Nov 13 2007 7:22 AM	Permanent Link
"Harry de Boer"	Tim, I encounter a few things when using scripts: 1) error messages don't reflect the right row. Each 'execute immediate' is taken into consideration as a part. For large scripts I think this is difficult. 2) the sql text is sometimes half visble when after scrolling, the cursor is not visible sometimes. I guess this is what you mentioned earlier. When clicking another tab and returning it's ok. When using -and rewriting- your example -see below- : 3) I don't see a resulttable tab (which I expected). However I do see an empty execution plan tab. 4) When executing the second time the error comes up that the temporary table tmpJTOV already exists Regards, Harry SCRIPT BEGIN DECLARE TempCursor CURSOR FOR stmt; PREPARE stmt FROM 'SELECT * FROM Information.Tables WHERE Name = ?'; OPEN TempCursor USING 'TmpJTOV'; IF (ROWCOUNT(TempCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE tmpJTOV'; END IF; CLOSE TempCursor; EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE tmpJTOV AS SELECT extract(month from Datum) AS maand, SUM(totaal)AS bedr FROM contracten GROUP BY maand, bedr WITH DATA'; END;
Tue, Nov 13 2007 9:25 PM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	Harry, << 1) error messages don't reflect the right row. Each 'execute immediate' is taken into consideration as a part. For large scripts I think this is difficult. >> I noticed that also and it will be fixed in the next build. << 2) the sql text is sometimes half visble when after scrolling, the cursor is not visible sometimes. I guess this is what you mentioned earlier. When clicking another tab and returning it's ok. >> Yep, it's a simple display issue with the syntax editor. << When using -and rewriting- your example -see below- : 3) I don't see a resulttable tab (which I expected). However I do see an empty execution plan tab. >> Are you sure that you're executing the script in a script window and not a statement window ? A statement window will have check boxes for an execution plan, etc. and a script window won't. << 4) When executing the second time the error comes up that the temporary table tmpJTOV already exists >> See here: http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=17&msg=781&page=1 I forgot that temp tables don't show up in the catalog information. -- Tim Young Elevate Software www.elevatesoft.com
Wed, Nov 14 2007 4:13 AM	Permanent Link
"Harry de Boer"	Tim, 3>Are you sure that you're executing the script in a script window and not a >statement window ? A statement window will have check boxes for an >execution plan, etc. and a script window won't. I'm sure. Also, in the left blue pane you see the word script. 4> I forgot that temp tables don't show up in the catalog information. Ok. Could you please show the script statement as it should be then, I can't figure it out. Regards, Harry "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> schreef in bericht news:B2E1ECC1-1E30-4FB5-8256-3546E18CC9C9@news.elevatesoft.com... > Harry, > > << 1) error messages don't reflect the right row. Each 'execute immediate' > is taken into consideration as a part. For large scripts I think this is > difficult. >> > > I noticed that also and it will be fixed in the next build. > > << 2) the sql text is sometimes half visble when after scrolling, the cursor > is not visible sometimes. I guess this is what you mentioned earlier. When > clicking another tab and returning it's ok. >> > > Yep, it's a simple display issue with the syntax editor. > > << When using -and rewriting- your example -see below- : > > 3) I don't see a resulttable tab (which I expected). However I do see an > empty execution plan tab. >> > > Are you sure that you're executing the script in a script window and not a > statement window ? A statement window will have check boxes for an > execution plan, etc. and a script window won't. > > << 4) When executing the second time the error comes up that the temporary > table tmpJTOV already exists >> > > See here: > > http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=17&msg=781&page=1 > > I forgot that temp tables don't show up in the catalog information. > > -- > Tim Young > Elevate Software > www.elevatesoft.com > >
Wed, Nov 14 2007 12:39 PM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	Harry, << I'm sure. Also, in the left blue pane you see the word script. >> I tested it with the database that you posted in the Binaries group, and it works fine here with the 1.06 EDB Manager. I get an Error tab on the second and subsequent executions. However I missed your comment about the result tab yesterday - there won't be a result tab if you're using EXECUTE IMMEDIATE. To get a result tab, what you want is this: SCRIPT BEGIN DECLARE TempCursor CURSOR WITH RETURN FOR stmt; -- The WITH RETURN is the key BEGIN EXECUTE IMMEDIATE 'DROP TABLE tmpJTOV'; EXCEPTION END; EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE tmpJTOV AS SELECT extract(month from Datum) AS maand, SUM(totaal)AS bedr FROM contracten GROUP BY maand, bedr WITH DATA'; PREPARE stmt FROM 'SELECT * FROM tmpJTOV'; OPEN TempCursor USING 'TmpJTOV'; END; << Ok. Could you please show the script statement as it should be then, I can't figure it out. >> Just wrap the DROP TABLE in a BEGIN..EXCEPTION block like in the above example. It actually simplifies the script quite a bit. -- Tim Young Elevate Software www.elevatesoft.com
Wed, Nov 14 2007 1:41 PM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Tim 1. What's the performance overhead for the exception block Vs the old DBISAM style IF EXISTS? 2. Reading the manual it looks as though all temporary tables are created on disk. Is it possible to create in ram? Roy Lambert
Wed, Nov 14 2007 3:32 PM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	Roy, << 1. What's the performance overhead for the exception block Vs the old DBISAM style IF EXISTS? >> Pretty much the same as with a Delphi try..except block, which is what we use internally for the exception handling. << 2. Reading the manual it looks as though all temporary tables are created on disk. Is it possible to create in ram? >> Not at this time, no. However, it is something that I've had on the list of improvements to look at for some time now. -- Tim Young Elevate Software www.elevatesoft.com
Wed, Nov 14 2007 4:52 PM	Permanent Link
"Ralf Graap"	Hey Roy, > 2. Reading the manual it looks as though all temporary tables are created > on disk. Is it possible to create in ram? I second that. Ralf