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 3 1918
slash wrote: 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..
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.
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 <bi**@karwin.co m> wrote in message news:<cj******* **@enews3.newsg uy.com>... slash wrote: 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..
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.
slash wrote: 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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ryan Schefke |
last post by:
------=_NextPart_000_0077_01C34C8B.2B90C960
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
..I just sent this out to the win32 distribution list but no one has
replied.can someone on this list please help? The issue should be
trivial for experienced MySQL users, I'm just a novice, thanks!
|
by: Girish Agarwal |
last post by:
--0-474210375-1058976151=:31789
Content-Type: text/plain; charset=us-ascii
Content-Id:
Content-Disposition: inline
Note: forwarded message attached.
__________________________________
|
by: Yun Guan |
last post by:
Hello mysql gurus,
I am trying to run perl on mysql database on Red Hat box. I want to install
DBI and DBD:mysql using CPAN:
perl -MCPAN -e shell
cpan>install DBI
The above succeeded, but
cpan>install DBD::mysql
|
by: Mark Adams |
last post by:
I am a relative newbie to MySQL. I had a Postfix+Courier+MySQL mail
server running for several months. It took me a week or so to get it up
and running in September. Now, I did a clean upgrade to Mandrake 9.2
and am reinstalling everything. This thing is kicking my ass and I
can't seem to get past it. I could really use some help if anybody has any.
Just for reference, this was my primary source for information on
installation and...
|
by: Kirk Soodhalter |
last post by:
Hi,
This started as a phpmyadmin problem, but has somehow morphed into a
mysql problem. I don't know how to fix it. I am posting the
conversation from a php newsgroup since it started there. Thanks for
any help you might be able to give.
-Kirk
Ok, in the midst of fixing this problem I have somehow managed to create
| |
by: Saqib Ali |
last post by:
I installed mySQL and have it running.... but I think I made a
mistake somewhere along the line...... I believe I did follow the
instructions that were provided with the distribution at:
http://dev.mysql.com/downloads/
The database is up. It seems that a test database
instance has been created (named "test"). I can see
it. However, the other database instance that should
have been created, named "mysql" doesn't show up when
I do "show...
|
by: Alex Hunsley |
last post by:
I am trying to install the DBD::mysql perl module. However, it claims I
need mysql.h:
cpan> install DBD::mysql
CPAN: Storable loaded ok
Going to read /home/alex/.cpan/Metadata
Database was generated on Mon, 29 Nov 2004 16:01:05 GMT
Running install for module DBD::mysql
Running make for R/RU/RUDY/DBD-mysql-2.9004.tar.gz
CPAN: Digest::MD5 loaded ok
|
by: MLH |
last post by:
I'm supposed to set a password for the MySQL root user. The output of
mysql_install_db instructed me to run the following commands...
/usr/bin/mysqladmin -u root -h appserver password mynwewpasswd
I did. It did not work. Here's the error:
/usr/bin/mysqladmin: connect to server at 'appserver' failed
error: 'Host 'appserver.crci.com' is not allowed to connect to this
MySQL server'
Another command I'm supposed to run also resulted in an...
|
by: Cheryl Langdon |
last post by:
Hello everyone,
This is my first attempt at getting help in this manner. Please
forgive me if this is an inappropriate request.
I suddenly find myself in urgent need of instruction on how to
communicate with a MySQL database table on a web server, from inside
of my company's Access-VBA application. I know VBA pretty well but
have never before needed to do this HTTP/XML/MySQL type functions.
|
by: PowerLifter1450 |
last post by:
I've been having a very rough time installinig mySQL on Linux. I have
been following the instructions form here:
http://www.hostlibrary.com/installing_apache_mysql_php_on_linux
Everytime I get to #./configure it goes through all of the preparing
tables and starting mysqlServer and daemon, but than immediaetly says
"mysql ended" -- I try to do #make right after anyway, but I get the
error "No targets specified and no makefile found" -- Any...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |