Thank you Rhino.
Your description is very detailed.
But my try was fail.No error has been throw out.
Follow is the process.
Please help me to analyse the result.
----------------------------------------------------------------------------------
[EMAIL PROTECTED] bin]# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.16-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| test |
| wangxu |
+--------------------+
6 rows in set (0.03 sec)
mysql> use wangxu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_wangxu |
+------------------+
| ht_detail |
+------------------+
1 row in set (0.00 sec)
mysql> select * from ht_detail;
+---------------+----------+------+
| hth | sbh | sbsl |
+---------------+----------+------+
| 79NK0001/0003 | 79NK0001 | 1 |
| 79NK0001/0003 | 79NK0002 | 1 |
| 79NK0001/0003 | 79NK0003 | 1 |
+---------------+----------+------+
3 rows in set (0.01 sec)
mysql> delimiter //
mysql> create procedure test()
-> deterministic
-> contains sql
-> begin
-> insert ht_detail values('sdf','ser',4);
-> end
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> show create procedure test \G
*************************** 1. row ***************************
Procedure: test
sql_mode:
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
Create Procedure: CREATE PROCEDURE "test"()
DETERMINISTIC
begin
insert ht_detail values('sdf','ser',4);
end
1 row in set (0.00 sec)
mysql> call test();
Query OK, 1 row affected (0.00 sec)
mysql> select * from ht_detail;
+---------------+----------+------+
| hth | sbh | sbsl |
+---------------+----------+------+
| 79NK0001/0003 | 79NK0001 | 1 |
| 79NK0001/0003 | 79NK0002 | 1 |
| 79NK0001/0003 | 79NK0003 | 1 |
| sdf | ser | 4 |
+---------------+----------+------+
4 rows in set (0.00 sec)
mysql>
----------------------------------------------------------------------------------
----- Original Message -----
From: "Rhino" <[EMAIL PROTECTED]>
To: "wangxu" <[EMAIL PROTECTED]>
Cc: "mysql" <mysql@lists.mysql.com>
Sent: Thursday, January 19, 2006 9:29 PM
Subject:Re: question about "CONTAINS SQL"
> 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.
>
> 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]
>
>
