----- Original Message -----
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>; "wangxu" <[EMAIL PROTECTED]>
Cc: "mysql" <mysql@lists.mysql.com>
Sent: Friday, January 20, 2006 2:20 AM
Subject: Re: question about "CONTAINS SQL"
At 8:29 -0500 1/19/06, Rhino wrote:
I am copying the rest of the list with this so that everyone may benefit
from the discussion.
If your routine modifies data, in other words if it does SQL Update,
Insert or Delete but your routine definition says only "CONTAINS SQL", I
would expect your routine to fail at runtime. I can't say this with
certaintly because I don't have one of the newer versions of MySQL that
supports these routines but I'm pretty sure that you will have a runtime
failure. After all, CONTAINS SQL implies only that you are creating
objects like tables within your routine; CONTAINS SQL does not permit the
execution of Insert, Update, or Delete. Therefore, I expect that you will
get a runtime error as soon as you do your first Insert, Update, or
Delete. If you want to avoid the error, use "MODIFIES SQL" instead of
"CONTAINS SQL".
Of course the best way to be sure is to try this for yourself. Try the
routine with CONTAINS SQL and see what happens at runtime. If it fails, as
I strongly expect, change CONTAINS SQL to MODIFIES SQL DATA and your error
will almost certainly go away.
No, these characteristics are merely advisory. The server doesn't
impose any restraints based on them.
Oh? Really?
I use DB2 a lot more than I use MySQL - and I'm not currently using a
version of MySQL that supports stored procedures and user-defined
functions - so I assumed that the MODIFIES/READS/CONTAINS clauses actually
_do_ something in MySQL. Since MySQL and DB2 both try to follow roughly the
same SQL standards that seemed like a reasonable assumption.
Obviously, I was wrong and bow to your superior knowledge of what MySQL
actually does with these clauses.
If the MODIFIES/READS/CONTAINS clauses are just checked for spelling and
otherwise ignored, i.e. if they are not enforced, then why does Wangxu's
procedure not work? Again, my version of MySQL doesn't support procedures so
I can't try it for myself.
Rhino
----- Original Message ----- From: "wangxu" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>
Sent: Thursday, January 19, 2006 2:54 AM
Subject: Re: question about "CONTAINS SQL"
If I create a routine with modification operation and not spectify
characteristic in CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL
DATA.
In the maunal,the default value is "CONTAINS SQL" if i haven't
spectify a values.
If it's meaning that the routine with ""CONTAINS SQL" include
modification operation?
Should many problem happen?
----- Original Message ----- From: "Rhino" <[EMAIL PROTECTED]>
To: "wangxu" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Thursday, January 19, 2006 12:21 AM
Subject:Re: question about "CONTAINS SQL"
If you are writing something that does INSERT, UPDATE, or DELETE, you
need
to use the MODIFIES SQL DATA option.
Rhino
----- Original Message ----- From: "wangxu" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Wednesday, January 18, 2006 3:05 AM
Subject: Re: question about "CONTAINS SQL"
But what is INSERT OR UPDATE need?
----- Original Message ----- >> From: "Rhino" <[EMAIL PROTECTED]>
To: "wangxu" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Tuesday, January 17, 2006 9:49 PM
Re: question about "CONTAINS SQL"
> ----- Original Message ----- >> > From: "wangxu"
<[EMAIL PROTECTED]>
> To: <mysql@lists.mysql.com>
> Sent: Tuesday, January 17, 2006 12:35 AM
> Subject: question about "CONTAINS SQL"
>
>
> >I notice there are one section in the manual:
> >
> > CONTAINS SQL indicates that the routine does not contain >> > >
statements
> > that
> > read or write data.
> >
> > And that the option is default.
> >
> > It's true?
> >
> > If i wouldn't do read or write in routine.What can i do yet?
> >
>
> Commands like GRANT or REVOKE or CREATE TABLE don't read or write
>> > data
> within tables but they involve SQL so commands like this need >> >
CONTAINS
> SQL,
> rather than the NO SQL, READS SQL DATA or MODIFIES SQL DATA
options.
>
> Rhino
>
>
>
>
> -- >> > No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.14.19/231 - Release Date:
> 16/01/2006
>
>
> -- >> > MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date:
18/01/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date:
18/01/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date:
18/01/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/234 - Release Date:
18/01/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date:
19/01/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.21/235 - Release Date: 19/01/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
