mysql/Sql Help

[slash]

Hi ,

Im new to MySql and i have no idea how to convert a sql server user
defined function to MySql. Can any one help me..

CREATE FUNCTION ChildCategories (@categoryId int, @taxId int,
@dummyTaxId int)
RETURNS @retTable TABLE (path varchar(2048),
parent_id int,
category_id int,
taxonomy_id int,
checkout_user_i d int)
AS
BEGIN
DECLARE ChildCatIds CURSOR
FOR
Select DISTINCT category_id
FROM E_KEW_Category
WHERE parent_id = @categoryId AND taxonomy_id = @taxId
OPEN childCatIds

DECLARE @childCatId int
FETCH NEXT FROM childCatIds INTO @childCatId
WHILE(@@FETCH_S TATUS = 0)
BEGIN
IF(@childCatId IS NOT NULL)

INSERT INTO @retTable(path, parent_id, category_id, taxonomy_id,
checkout_user_i d)
SELECT path, parent_id, category_id, taxonomy_id, checkout_user_i d
FROM E_KEW_Category
WHERE category_id = @childCatId AND taxonomy_id = @taxId
UNION
SELECT path,parent_id, category_id, taxonomy_id, checkout_user_i d
from ChildCategories ( @childCatId, @taxId, @dummyTaxId)
FETCH NEXT FROM childCatIds INTO @childCatId

END
CLOSE childCatIds
DEALLOCATE childCatIds


RETURN
END



Thanks in advance
slash

[Bill Karwin]

Re: mysql/Sql Help

slash wrote:[color=blue]
> Im new to MySql and i have no idea how to convert a sql server user
> defined function to MySql. Can any one help me..[/color]

Someone just asked a question on this same topic on another newsgroup
this week. Here's what I wrote in reply:

"Stored procedures and functions are a new feature in MySQL version 5.0."

http://dev.mysql.com/doc/mysql/en/Stored_Procedures.html

Are you using MySQL version 5.0? Keep in mind that MySQL 5.0 is only
available in alpha release at this time.

Also, the function language you are using seems to be proprietary to MS
SQL Server. MySQL says:
"MySQL follows the SQL:2003 syntax for stored procedures, which is also
used by IBM's DB2."

Other RDBMS vendors (e.g. Microsoft, Oracle, Sybase, etc.) that
implemented a stored procedure & function language prior to the
publication of the SQL:2003 standard, had to invent their own syntax,
and their syntax usually differs from the standard in either large or
small ways.

If you want to port your stored procedures and functions to MySQL 5.0,
you'll have to learn the MySQL syntax, and rewrite your functions on a
case-by-case basis. You might find cases where Microsoft has
implemented some feature that is simply not implemented in MySQL's
stored procedure language.

Bill K.

[slash]

Re: mysql/Sql Help

Thanks for the info bill.
but am using version MY SQL 4.0.15 and i have no idea how to convert
all those stored procs and functions of MS SQL to MY SQL 4.0.15 (am
afraid stored procs and functions are not avialable in this version)..
Give me a clue

Slash


Bill Karwin <bill@karwin.co m> wrote in message news:<cj8f93010 g5@enews3.newsg uy.com>...[color=blue]
> slash wrote:[color=green]
> > Im new to MySql and i have no idea how to convert a sql server user
> > defined function to MySql. Can any one help me..[/color]
>
> Someone just asked a question on this same topic on another newsgroup
> this week. Here's what I wrote in reply:
>
> "Stored procedures and functions are a new feature in MySQL version 5.0."
> http://dev.mysql.com/doc/mysql/en/St...rocedures.html
>
> Are you using MySQL version 5.0? Keep in mind that MySQL 5.0 is only
> available in alpha release at this time.
>
> Also, the function language you are using seems to be proprietary to MS
> SQL Server. MySQL says:
> "MySQL follows the SQL:2003 syntax for stored procedures, which is also
> used by IBM's DB2."
>
> Other RDBMS vendors (e.g. Microsoft, Oracle, Sybase, etc.) that
> implemented a stored procedure & function language prior to the
> publication of the SQL:2003 standard, had to invent their own syntax,
> and their syntax usually differs from the standard in either large or
> small ways.
>
> If you want to port your stored procedures and functions to MySQL 5.0,
> you'll have to learn the MySQL syntax, and rewrite your functions on a
> case-by-case basis. You might find cases where Microsoft has
> implemented some feature that is simply not implemented in MySQL's
> stored procedure language.
>
> Bill K.[/color]

[Bill Karwin]

Re: mysql/Sql Help

slash wrote:[color=blue]
> Thanks for the info bill.
> but am using version MY SQL 4.0.15 and i have no idea how to convert
> all those stored procs and functions of MS SQL to MY SQL 4.0.15 (am
> afraid stored procs and functions are not avialable in this version)..
> Give me a clue[/color]

Right. Stored procs and functions are not available in MySQL version
4.0. You cannot convert your procedures, because MySQL 4.0 does not
have that feature at all.

You have four options:

- Stay with Microsoft SQL Server. I don't recommended this; MS SQL
Server is reported to have data corruption bugs and security
vulnerabilities .

- Upgrade your MySQL software to 5.0, and convert the stored procedures.
You'll have to take your chances with the volatility of alpha
software. Also not recommended.

- Convert your database to another RDBMS that supports stored
procedures. For instance, PostgreSQL 7 has a stored procedure language,
and it is also a free, open-source RDBMS. There are also numerous
commercial RDBMS products with full-featured stored procedure languages.
My apologies to other readers for advocating another RDBMS on a MySQL
newsgroup!

- Write application code to do similar work as the stored procedures in
your old database.

Regards,
Bill K.