Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » DTP » Dumb question?
Dumb question? [message #49497] Sat, 30 August 2008 09:58 Go to next message
Andrew is currently offline AndrewFriend
Messages: 8
Registered: July 2009
Junior Member
Apologies in advance if I have missed something obvious, but after a few
hours searching the newsgroups and various websites, I am at a loss.

I am trying to create stored procedures in MySQL, which requires that I
use terminators within the body of the procedure.

After scanning through the DTP code it looks like DTP will always split
the body of the procedure at each terminator. So something like:

CREATE PROCEDURE Client_InsertClient (
INOUT clntid INTEGER,
typecodeid INTEGER,
firstname VARCHAR(45),
lastname VARCHAR(45),
titlecodeid INTEGER,
gendercodeid INTEGER,
dob DATE,
salutation VARCHAR(45),
homephone VARCHAR(20),
busphone VARCHAR(20),
mobilephone VARCHAR(20),
otherphone VARCHAR(20),
countryid INTEGER)
BEGIN
DECLARE oldclntid INTEGER;

SET oldclntid = clntid;

INSERT lead_client (
clnt_type_code_id,
clnt_firstname,
clnt_lastname,
clnt_title_code_id,
clnt_gender_code_id,
clnt_date_of_birth,
clnt_salutation,
clnt_home_phone,
clnt_bus_phone,
clnt_mobile_phone,
clnt_other_phone,
clnt_country_id
)
VALUES (
typecodeid,
firstname,
lastname,
titlecodeid,
gendercodeid,
dob,
salutation,
homephone,
busphone,
mobilephone,
otherphone,
countryid
);

SET clntid = LAST_INSERT_ID();
END

Is never going to be executed successfully by DTP? Looking at the Results
view, it appears to split into individual statements which are therefore
incomplete and not executable.

Have I missed something?
Re: Dumb question? [message #49559 is a reply to message #49497] Tue, 02 September 2008 14:33 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: jeremy.peel.ingres.com

Hi Andrew,

This may be another Dumb Question.
It might lead to a dumb answer though ;)

Are you looking at this procedure in the general SQL editor?
What we do is to create a procedure with an empty body in the SQL editor
and then open that procedure for edit from DSE. This opens it with the
procedure editor. You may have more luck with that.

Jeremy

Andrew wrote:
> Apologies in advance if I have missed something obvious, but after a few
> hours searching the newsgroups and various websites, I am at a loss.
>
> I am trying to create stored procedures in MySQL, which requires that I
> use terminators within the body of the procedure.
>
> After scanning through the DTP code it looks like DTP will always split
> the body of the procedure at each terminator. So something like:
>
> CREATE PROCEDURE Client_InsertClient (
> INOUT clntid INTEGER,
> typecodeid INTEGER,
> firstname VARCHAR(45), lastname VARCHAR(45),
> titlecodeid INTEGER,
> gendercodeid INTEGER,
> dob DATE,
> salutation VARCHAR(45),
> homephone VARCHAR(20),
> busphone VARCHAR(20),
> mobilephone VARCHAR(20),
> otherphone VARCHAR(20),
> countryid INTEGER)
> BEGIN
> DECLARE oldclntid INTEGER;
>
> SET oldclntid = clntid;
>
> INSERT lead_client (
> clnt_type_code_id,
> clnt_firstname,
> clnt_lastname,
> clnt_title_code_id,
> clnt_gender_code_id,
> clnt_date_of_birth,
> clnt_salutation,
> clnt_home_phone,
> clnt_bus_phone,
> clnt_mobile_phone,
> clnt_other_phone,
> clnt_country_id
> )
> VALUES (
> typecodeid,
> firstname,
> lastname,
> titlecodeid,
> gendercodeid,
> dob,
> salutation,
> homephone,
> busphone,
> mobilephone,
> otherphone,
> countryid
> );
>
> SET clntid = LAST_INSERT_ID(); END
>
> Is never going to be executed successfully by DTP? Looking at the
> Results view, it appears to split into individual statements which are
> therefore incomplete and not executable.
>
> Have I missed something?
>
Re: Dumb question? [message #49619 is a reply to message #49559] Tue, 02 September 2008 22:32 Go to previous messageGo to next message
Andrew is currently offline AndrewFriend
Messages: 8
Registered: July 2009
Junior Member
Jeremy,

Thanks. You're correct, I am using the general SQL editor. I normally
work with a development environment where each stored proc is kept in a
separate version-controlled file. So I initially set out to follow the
same approach - open a .sql file, code the procedure, and execute to
compile into the database.

When working with MySQL, I don't have any option to create a new Stored
Proc. On the Stored Procedures node in the DSE, the context menu shows
Refresh and Properties, but no "New". Maybe I'm looking in the wrong
place? Or is this a limitation of DTP's support for MySQL?

Thanks,
Andrew
Re: Dumb question? [message #49647 is a reply to message #49619] Wed, 03 September 2008 09:41 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: hui.cao.sybase.ocm

Hi Andrew,
This is a good question. Currently the general SQL editor always tries to
split SQL statements by ";", unless you have a SQL parser for that specific
SQL dialect and there's no parsing errors. I think there're several
solutions to this problem:
1. Contribute a MySQL parser into the framework, which can correctly
recognize the "create procedure" syntax; This is the preferred approach.
2. Add a preference to control whether to use terminators automatically; or
add a toggle button on the SQL editor internal toolbar. The drawback of this
approach is the preference/toggle button is useless if you already have a
full-fledged SQL parser.

But for now you'll have to follow Jeremy's smart workaround: create the
empty procedure in general SQL editor first, then edit the body in stored
procedure (routine) editor.

Please feel free to create a bug for this problem. Thanks.

--
Best Regards!

Max (Hui) Cao
Sybase Shanghai RD

"Andrew " <aandsrick@yahoo.co.nz> wrote in message
news:bad6983aa2da2ee8bf72ae86b5757478$1@www.eclipse.org...
> Jeremy,
>
> Thanks. You're correct, I am using the general SQL editor. I normally
> work with a development environment where each stored proc is kept in a
> separate version-controlled file. So I initially set out to follow the
> same approach - open a .sql file, code the procedure, and execute to
> compile into the database.
>
> When working with MySQL, I don't have any option to create a new Stored
> Proc. On the Stored Procedures node in the DSE, the context menu shows
> Refresh and Properties, but no "New". Maybe I'm looking in the wrong
> place? Or is this a limitation of DTP's support for MySQL?
>
> Thanks,
> Andrew
>
>
>
>
Re: Dumb question? [message #49677 is a reply to message #49647] Thu, 04 September 2008 22:20 Go to previous messageGo to next message
Andrew is currently offline AndrewFriend
Messages: 8
Registered: July 2009
Junior Member
Thanks very much for the explanation. I tried creating the empty
procedure (successfully), however the new procedure does not show in the
DSE. I guess this means that the system is not loading all MySQL database
objects yet. I'll look further.
Re: Dumb question? [message #49829 is a reply to message #49677] Mon, 08 September 2008 09:43 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: jeremy.peel.ingres.com

Hi Andrew,
Do you get to see *any* database procedures in the DSE for MySQL?

If other procedures do exist but do not appear that would be an
indication that the catalog loader needs attention for MySQL.

If other procedures do appear (and exist!), did you try refreshing the
DSE once your CREATE PROCEDURE succeeded?

Please ignore if this is too basic but whenever you create a new
database object in DTP you have to refresh DSE to make it show up. For
example, try creating a new table e.g.:
CREATE TABLE airport (
ap_iatacode CHAR(3) PRIMARY KEY,
ap_place VARCHAR(30)NOT NULL,
ap_name VARCHAR(50) NOT NULL
);

And then refresh DSE. The table should then appear in the list.

Jeremy


Andrew wrote:
> Thanks very much for the explanation. I tried creating the empty
> procedure (successfully), however the new procedure does not show in the
> DSE. I guess this means that the system is not loading all MySQL
> database objects yet. I'll look further.
>
Re: Dumb question? [message #49859 is a reply to message #49829] Mon, 08 September 2008 13:46 Go to previous message
Andrew is currently offline AndrewFriend
Messages: 8
Registered: July 2009
Junior Member
Jeremy Peel wrote:

> Hi Andrew,
> Do you get to see *any* database procedures in the DSE for MySQL?

> If other procedures do exist but do not appear that would be an
> indication that the catalog loader needs attention for MySQL.

> If other procedures do appear (and exist!), did you try refreshing the
> DSE once your CREATE PROCEDURE succeeded?

> Please ignore if this is too basic but whenever you create a new
> database object in DTP you have to refresh DSE to make it show up. For
> example, try creating a new table e.g.:
> CREATE TABLE airport (
> ap_iatacode CHAR(3) PRIMARY KEY,
> ap_place VARCHAR(30)NOT NULL,
> ap_name VARCHAR(50) NOT NULL
> );

> And then refresh DSE. The table should then appear in the list.

> Jeremy

Jeremy, thanks.

Tables work fine - creating and refreshing.

However, while I can see the procedures (including the newly created
sproc) in the MySQL tools, nothing shows up in DSE.

Exiting Eclipse and restarting does not help.

I am running Eclipse 3.4.0, with DTP 1.6.0.200806091 on Mac OsX 10.5.4.
Re: Dumb question? [message #592975 is a reply to message #49497] Tue, 02 September 2008 14:33 Go to previous message
Eclipse UserFriend
Originally posted by: jeremy.peel.ingres.com

Hi Andrew,

This may be another Dumb Question.
It might lead to a dumb answer though ;)

Are you looking at this procedure in the general SQL editor?
What we do is to create a procedure with an empty body in the SQL editor
and then open that procedure for edit from DSE. This opens it with the
procedure editor. You may have more luck with that.

Jeremy

Andrew wrote:
> Apologies in advance if I have missed something obvious, but after a few
> hours searching the newsgroups and various websites, I am at a loss.
>
> I am trying to create stored procedures in MySQL, which requires that I
> use terminators within the body of the procedure.
>
> After scanning through the DTP code it looks like DTP will always split
> the body of the procedure at each terminator. So something like:
>
> CREATE PROCEDURE Client_InsertClient (
> INOUT clntid INTEGER,
> typecodeid INTEGER,
> firstname VARCHAR(45), lastname VARCHAR(45),
> titlecodeid INTEGER,
> gendercodeid INTEGER,
> dob DATE,
> salutation VARCHAR(45),
> homephone VARCHAR(20),
> busphone VARCHAR(20),
> mobilephone VARCHAR(20),
> otherphone VARCHAR(20),
> countryid INTEGER)
> BEGIN
> DECLARE oldclntid INTEGER;
>
> SET oldclntid = clntid;
>
> INSERT lead_client (
> clnt_type_code_id,
> clnt_firstname,
> clnt_lastname,
> clnt_title_code_id,
> clnt_gender_code_id,
> clnt_date_of_birth,
> clnt_salutation,
> clnt_home_phone,
> clnt_bus_phone,
> clnt_mobile_phone,
> clnt_other_phone,
> clnt_country_id
> )
> VALUES (
> typecodeid,
> firstname,
> lastname,
> titlecodeid,
> gendercodeid,
> dob,
> salutation,
> homephone,
> busphone,
> mobilephone,
> otherphone,
> countryid
> );
>
> SET clntid = LAST_INSERT_ID(); END
>
> Is never going to be executed successfully by DTP? Looking at the
> Results view, it appears to split into individual statements which are
> therefore incomplete and not executable.
>
> Have I missed something?
>
Re: Dumb question? [message #593000 is a reply to message #49559] Tue, 02 September 2008 22:32 Go to previous message
Andrew is currently offline AndrewFriend
Messages: 8
Registered: July 2009
Junior Member
Jeremy,

Thanks. You're correct, I am using the general SQL editor. I normally
work with a development environment where each stored proc is kept in a
separate version-controlled file. So I initially set out to follow the
same approach - open a .sql file, code the procedure, and execute to
compile into the database.

When working with MySQL, I don't have any option to create a new Stored
Proc. On the Stored Procedures node in the DSE, the context menu shows
Refresh and Properties, but no "New". Maybe I'm looking in the wrong
place? Or is this a limitation of DTP's support for MySQL?

Thanks,
Andrew
Re: Dumb question? [message #593012 is a reply to message #49619] Wed, 03 September 2008 09:41 Go to previous message
Hui Cao is currently offline Hui CaoFriend
Messages: 29
Registered: July 2009
Junior Member
Hi Andrew,
This is a good question. Currently the general SQL editor always tries to
split SQL statements by ";", unless you have a SQL parser for that specific
SQL dialect and there's no parsing errors. I think there're several
solutions to this problem:
1. Contribute a MySQL parser into the framework, which can correctly
recognize the "create procedure" syntax; This is the preferred approach.
2. Add a preference to control whether to use terminators automatically; or
add a toggle button on the SQL editor internal toolbar. The drawback of this
approach is the preference/toggle button is useless if you already have a
full-fledged SQL parser.

But for now you'll have to follow Jeremy's smart workaround: create the
empty procedure in general SQL editor first, then edit the body in stored
procedure (routine) editor.

Please feel free to create a bug for this problem. Thanks.

--
Best Regards!

Max (Hui) Cao
Sybase Shanghai RD

"Andrew " <aandsrick@yahoo.co.nz> wrote in message
news:bad6983aa2da2ee8bf72ae86b5757478$1@www.eclipse.org...
> Jeremy,
>
> Thanks. You're correct, I am using the general SQL editor. I normally
> work with a development environment where each stored proc is kept in a
> separate version-controlled file. So I initially set out to follow the
> same approach - open a .sql file, code the procedure, and execute to
> compile into the database.
>
> When working with MySQL, I don't have any option to create a new Stored
> Proc. On the Stored Procedures node in the DSE, the context menu shows
> Refresh and Properties, but no "New". Maybe I'm looking in the wrong
> place? Or is this a limitation of DTP's support for MySQL?
>
> Thanks,
> Andrew
>
>
>
>
Re: Dumb question? [message #593020 is a reply to message #49647] Thu, 04 September 2008 22:20 Go to previous message
Andrew is currently offline AndrewFriend
Messages: 8
Registered: July 2009
Junior Member
Thanks very much for the explanation. I tried creating the empty
procedure (successfully), however the new procedure does not show in the
DSE. I guess this means that the system is not loading all MySQL database
objects yet. I'll look further.
Re: Dumb question? [message #593064 is a reply to message #49677] Mon, 08 September 2008 09:43 Go to previous message
Eclipse UserFriend
Originally posted by: jeremy.peel.ingres.com

Hi Andrew,
Do you get to see *any* database procedures in the DSE for MySQL?

If other procedures do exist but do not appear that would be an
indication that the catalog loader needs attention for MySQL.

If other procedures do appear (and exist!), did you try refreshing the
DSE once your CREATE PROCEDURE succeeded?

Please ignore if this is too basic but whenever you create a new
database object in DTP you have to refresh DSE to make it show up. For
example, try creating a new table e.g.:
CREATE TABLE airport (
ap_iatacode CHAR(3) PRIMARY KEY,
ap_place VARCHAR(30)NOT NULL,
ap_name VARCHAR(50) NOT NULL
);

And then refresh DSE. The table should then appear in the list.

Jeremy


Andrew wrote:
> Thanks very much for the explanation. I tried creating the empty
> procedure (successfully), however the new procedure does not show in the
> DSE. I guess this means that the system is not loading all MySQL
> database objects yet. I'll look further.
>
Re: Dumb question? [message #593072 is a reply to message #49829] Mon, 08 September 2008 13:46 Go to previous message
Andrew is currently offline AndrewFriend
Messages: 8
Registered: July 2009
Junior Member
Jeremy Peel wrote:

> Hi Andrew,
> Do you get to see *any* database procedures in the DSE for MySQL?

> If other procedures do exist but do not appear that would be an
> indication that the catalog loader needs attention for MySQL.

> If other procedures do appear (and exist!), did you try refreshing the
> DSE once your CREATE PROCEDURE succeeded?

> Please ignore if this is too basic but whenever you create a new
> database object in DTP you have to refresh DSE to make it show up. For
> example, try creating a new table e.g.:
> CREATE TABLE airport (
> ap_iatacode CHAR(3) PRIMARY KEY,
> ap_place VARCHAR(30)NOT NULL,
> ap_name VARCHAR(50) NOT NULL
> );

> And then refresh DSE. The table should then appear in the list.

> Jeremy

Jeremy, thanks.

Tables work fine - creating and refreshing.

However, while I can see the procedures (including the newly created
sproc) in the MySQL tools, nothing shows up in DSE.

Exiting Eclipse and restarting does not help.

I am running Eclipse 3.4.0, with DTP 1.6.0.200806091 on Mac OsX 10.5.4.
Previous Topic:Listening to SQL Results
Next Topic:Problem with Data Source Explorer
Goto Forum:
  


Current Time: Fri Nov 28 10:15:27 GMT 2014

Powered by FUDForum. Page generated in 0.03005 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software