RE: [nbusers] Creating Stored Procedures

  • From: "Wagoner, Mark" < >
  • To: < >
  • Subject: RE: [nbusers] Creating Stored Procedures
  • Date: Fri, 22 Feb 2008 12:45:05 -0500
  • Mailing-list: contact ; run by ezmlm

Thanks, David.  I was afraid that would be the case but thought I would make sure it wasn't something I was doing.  It wouldn't be so bad if IBM's Navigator was such a bloated piece of **** (IMHO).


From: HandyGeek [mailto: ]
Sent: Friday, February 22, 2008 12:40 PM
To:
Subject: Re: [nbusers] Creating Stored Procedures

Hi Mark,
I have discovered that the NetBeans SQL editor is for generic SQL code only.  Nothing vendor-specific.  I tried creating a trigger using Oracle PL/SQL - no go.  NetBeans blew up on an extra semicolon that PL/SQL absolutely required.  My opinion: better to use the vendor's database interface and execute your commands there.  It makes sense, as I do not see how NetBeans could possibly code for every vendor-specific language variation.  Hope this helps,  David

Wagoner, Mark wrote:
.local" type="cite">
Yes, this one is a sizeable chunk of code.  But even simple statements like this fail:
 
CREATE PROCEDURE test_proc
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
  IF USER = 'TESTUSER' THEN
    DELETE FROM user_pref WHERE userid = USER;
  END IF;
END
 
It works in IBM's Navigator but NB returns these:
 
Error code -104, SQL state 42601: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;.
Line 1, column 1
 
Error code -199, SQL state 42601: [SQL0199] Keyword IF not expected. Valid tokens: DECLARE.
Line 7, column 3
 
Error code -104, SQL state 42601: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: DECLARE.
Line 8, column 1
 
Execution finished after 0 s, 3 error(s) occurred.


From: Sergio Samayoa [ ">mailto: ]
Sent: Friday, February 22, 2008 11:26 AM
To: ">
Subject: RE: [nbusers] Creating Stored Procedures

109 parameters!

OMG!

I think that some of the grants suggestions is about database support.

Regards.


De: Wagoner, Mark [ ">mailto: ]
Enviado el: Viernes, 22 de Febrero de 2008 09:19 a.m.
Para: ">
Asunto: RE: [nbusers] Creating Stored Procedures

Well, it is 821 lines long so here is relevant portions of it. It errors out on the IF which is immediately after the first semicolon, that is why I think it is due to the compound statement.  This code works if I use IBM's tool (this is for a DB2/400 database) but I would prefer to do everything from NB.

CREATE PROCEDURE update_preferences (
  IN  i_item_id                     DECIMAL(15,0),
  IN  i_Alcohol_Volume              CHAR(1),
  IN  i_Alcohol_Weight              CHAR(1),

... (109 more parameters) ...


) LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA
SET OPTION COMMIT = *RS
BEGIN
  DECLARE rowcount  INTEGER;

  IF i_item_id IS NOT NULL THEN

    UPDATE form_pref SET
      Alcohol_Volume           = i_Alcohol_Volume,

... (692 more lines of code) ...

  END IF;
END


From: Sergio Samayoa [ ">mailto: ]
Sent: Friday, February 22, 2008 10:16 AM
To: ">
Subject: RE: [nbusers] Creating Stored Procedures

Show us the CREATE statement please…


De: Wagoner, Mark [ ">mailto: ]
Enviado el: Viernes, 22 de Febrero de 2008 08:57 a.m.
Para: ">
Asunto: [nbusers] Creating Stored Procedures

Is there any way to execute multi-line SQL statements in the database explorer?  If I try to create a stored procedure using the Execute Command option I get errors.  I'm sure it is because the statement is a compound statement.



Creating Stored Procedures

Wagoner, Mark 02/22/2008

RE: [nbusers] Creating Stored Procedures

Sergio Samayoa 02/22/2008

RE: [nbusers] Creating Stored Procedures

Wagoner, Mark 02/22/2008

RE: [nbusers] Creating Stored Procedures

Sergio Samayoa 02/22/2008

RE: [nbusers] Creating Stored Procedures

Wagoner, Mark 02/22/2008

Re: [nbusers] Creating Stored Procedures

HandyGeek 02/22/2008

RE: [nbusers] Creating Stored Procedures

Wagoner, Mark 02/22/2008

Re: [nbusers] Creating Stored Procedures

David Van Couvering 02/22/2008

RE: [nbusers] Creating Stored Procedures

Wagoner, Mark 02/22/2008

Re: [nbusers] Creating Stored Procedures

Rick Fincher 02/22/2008

Project Features

About this Project

www was started in November 2009, is owned by jpirek, and has 21 members.
By use of this website, you agree to the NetBeans Policies and Terms of Use (revision 20140418.2d69abc). © 2013, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo
 
 
Close
loading
Please Confirm
Close