Eclipse Community Forums: DTP

Help

Home

Home » Eclipse Projects » DTP » Dumb question?

Show: Today's Messages :: Show Polls :: Message Navigator

Dumb question? [message #49497]

Sat, 30 August 2008 09:58

Andrew

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?

Report message to a moderator

Re: Dumb question? [message #49559 is a reply to message #49497]

Tue, 02 September 2008 14:33

Eclipse User

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?
>

Report message to a moderator

Re: Dumb question? [message #49619 is a reply to message #49559]

Tue, 02 September 2008 22:32

Andrew

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

Report message to a moderator

Re: Dumb question? [message #49647 is a reply to message #49619]

Wed, 03 September 2008 09:41

Eclipse User

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
>
>
>
>

Report message to a moderator

Re: Dumb question? [message #49677 is a reply to message #49647]

Thu, 04 September 2008 22:20

Andrew

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.

Report message to a moderator

Re: Dumb question? [message #49829 is a reply to message #49677]

Mon, 08 September 2008 09:43

Eclipse User

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.
>

Report message to a moderator

Re: Dumb question? [message #49859 is a reply to message #49829]

Mon, 08 September 2008 13:46

Andrew

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.

Report message to a moderator

Re: Dumb question? [message #592975 is a reply to message #49497]

Tue, 02 September 2008 14:33

Eclipse User

Report message to a moderator

Re: Dumb question? [message #593000 is a reply to message #49559]

Tue, 02 September 2008 22:32

Andrew

Messages: 8
Registered: July 2009

Junior Member

Report message to a moderator

Re: Dumb question? [message #593012 is a reply to message #49619]

Wed, 03 September 2008 09:41

Hui Cao

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
>
>
>
>

Report message to a moderator