This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

Bug 151300

Summary: Delimiter statements are not being saved
Product: db Reporter: Roman Mostyka <romanmostyka>
Component: SQL EditorAssignee: Jiri Skrivanek <jskrivanek>
Status: RESOLVED WONTFIX    
Severity: blocker    
Priority: P3    
Version: 6.x   
Hardware: All   
OS: All   
Issue Type: DEFECT Exception Reporter:
Attachments: Screenshot of strange looking statement in "SQL History" dialog.
create procedure after execution

Description Roman Mostyka 2008-10-24 16:35:21 UTC
1. Connect to some DB, open SQL editor and run following:
"delimiter \\
CREATE PROCEDURE p11 ()
BEGIN
  DECLARE x1 CHAR(5) DEFAULT 'outer';
  BEGIN
    DECLARE x1 CHAR(5) DEFAULT 'inner';
    SELECT x1;
  END;
  SELECT x1;
END; \\".
2. Open "SQL History" dialog and look at executed statements.

Result: The looks strange, I guess because od delimiter.
Comment 1 Roman Mostyka 2008-10-24 16:36:24 UTC
Created attachment 72615 [details]
Screenshot of strange looking statement in "SQL History" dialog.
Comment 2 John Baker 2008-10-27 17:38:06 UTC
which databaase was used ?
Comment 3 Roman Mostyka 2008-10-27 17:48:40 UTC
I tried with MySQL and Java DB.
But it seems to me that it isn't so important since the problem in "SQL History" dialog I guess.
Comment 4 John Baker 2008-10-31 08:28:54 UTC
This create procedure statement is valid for MySQL, not Java DB.

delimiter \\
CREATE PROCEDURE p11 ()
BEGIN
  DECLARE x1 CHAR(5) DEFAULT 'outer';
  BEGIN
    DECLARE x1 CHAR(5) DEFAULT 'inner';
    SELECT x1;
  END;
  SELECT x1;
END; \\

Executing against MySQL, SQL History shows the statement properly.

For Java DB, since there are syntax errors, the statement looks strange in SQL History.

SQL History should still list statements that failed to execute.

Either please close this issue or lower the priority.
Comment 5 Roman Mostyka 2008-10-31 13:14:51 UTC
I don't see a reason for lowering priority since statement isn't save correctly even if it executed successfully.
John, have you tried to insert executed statement in clean SQL editor and execute this statement again?
I get for posted statement following statement from "SQL History":
"CREATE PROCEDURE p11 ()
BEGIN
  DECLARE x1 CHAR(5) DEFAULT 'outer';
  BEGIN
    DECLARE x1 CHAR(5) DEFAULT 'inner';
    SELECT x1;
  END;
  SELECT x1;
END;;"
I don't see "delimiter \\" and I don't see "\\" in the end, but I see excessive semicolon.
Comment 6 David Vancouvering 2008-10-31 18:23:08 UTC
That does look like a bug; create procedure statements need to be saved correctly.  John, please see if you can
reproduce what Roman is seeing.
Comment 7 John Baker 2008-10-31 18:43:24 UTC
As I mentioned if the create procedure has the correct syntax and executes successfully, then History displays it
correctly.  

Once there's a syntax error, the create procedure looks strange in History.
Comment 8 John Baker 2008-10-31 19:07:15 UTC
I'm attaching a screenshot showing the create procedure with delimiter as it appears.

Looks ok to me and not the same as the original screenshot.
Comment 9 John Baker 2008-10-31 19:08:11 UTC
Created attachment 73017 [details]
create procedure after execution
Comment 10 John Baker 2008-10-31 19:17:46 UTC
SQL History doesn't modify the statement that had been executed.

I don't think "delimiter \\" is executed.

If it's expected for "delimiter \\ to be saved then this should not be filed against SQL History

However, from the comments, the character of this bug is changing.  First you say that the SQL looks strange, then you
say that "delimiter \\" is not appearing. 
Comment 11 Roman Mostyka 2008-10-31 19:30:35 UTC
For me not full executed statement looks strange. ;)
And I'm not sure about delimiter. On one hand it is not usual statement, but on the other hand this operator helps to
execute statement successfully.
Comment 12 David Vancouvering 2008-10-31 19:33:54 UTC
Looks good to me.  It seems odd that statements with syntax errors "look funny". Maybe we should only record history if
the execution succeeds - why would you want to save a statement that is broken?
Comment 13 John Baker 2008-10-31 19:38:49 UTC
So, now you say the only problem is "delimiter \\ .. \\" is not surrounding the create procedure statement in History ?

If you think "delimiter \\ .. \\" should be saved then assign the issue to another area.

Just to be clear I executed the following statement in the SQL Editor and the result is the screenshot I attached.

delimiter \\
CREATE PROCEDURE p11 ()
BEGIN
  DECLARE x1 CHAR(5) DEFAULT 'outer';
  BEGIN
    DECLARE x1 CHAR(5) DEFAULT 'inner';
    SELECT x1;
  END;
  SELECT x1;
END; \\
Comment 14 John Baker 2008-10-31 19:43:37 UTC
> It seems odd that statements with syntax errors "look funny". Maybe we should only record history if
the execution succeeds - why would you want to save a statement that is broken?

Other tools save statements that have syntax errors.  The statement could be corrected later

Comment 15 Roman Mostyka 2008-11-05 20:15:59 UTC
I agree with John that incorrect statements also should be saved (or at least we should have such choice), since they
can be inserted than and modified.
John, what do you mean "then assign the issue to another area"? This problem occurs in SQL History, I can't imagine what
area I should assign it to.
And here is one question left: is "delimiter" should be saved or not. If yes, then this issue should be fixed. And issue
#152451 also should be fixed. If no, then both these issues should be closed.
But I think that delimiter should be saved, since SQL History should save statements which were executed in IDE's SQL
editor rather than pure SQL, but without delimiter these statements won't work.
Comment 16 David Vancouvering 2008-11-05 21:01:17 UTC
The delimiter 'statement' is not executed, John is right, so given the current logic ("we only save executed
statements") you won't see it.  

However, not having it can cause confusion because you may try to re-execute a statement that assumes a certain
delimiter and it will fail.  So I think we should fix this.

It would be a small change to save the delimiter statement to history.  John, I know "officially" this change would
belong to the SQL editor, but if you could handle it, that would be great.  You made mods to db.core already to add
history when executing, this is just adding history in another place in the code.  Look at db.core,
org.netbeans.modules.db.sql.execute.SQLExecuteHelper.SQLSplitter.checkDelimiterStatement().

Thanks.

Comment 17 John Baker 2008-11-18 03:24:54 UTC
Is this important for 7.0 ?  

I changed TM to 7.0
Comment 18 Roman Mostyka 2008-11-18 13:11:19 UTC
John, I agree with you that this issue should be fixed at least in 7.0.
What do you think about having a fix for a next patch for 6.5?
Comment 19 Jiri Rechtacek 2009-02-10 15:25:50 UTC
Reassigned to new owner.
Comment 20 Jiri Rechtacek 2009-07-01 07:44:41 UTC
NB6.7 was released already - http://www.netbeans.org/community/releases/67/
Postpone fixing this issue on the next release.
Comment 21 Jiri Skrivanek 2009-09-14 16:25:31 UTC
Delimiter will not be saved in SQL history. It is just list of executed SQL commands.