MySQL ODBC Connector - Stored Procedures and Functions

Author

Stored Procedures and Functions

Blue Wave Software

2005-09-26, 11:23 am

------ =_NextPart_000_0006_
01C5C2FE.BC285A10
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I don't have any experience with stored procedures and find the
Documentation in the MYSQL manual a bit sketchy or maybe I am just miss
reading it. Can any one point me to some documentation that will help =
with
fully understanding Stored Procedures?=20

=20

What I am trying to migrate out of my program code is a procedure to do =
the
following for update commands.

=20

1) Determine that the ID Field and the Last Updated Timestamp Field =
is
still the same as when the data was originally read.

2) If not the same then Raise an error back to the program so It can
determine the action.

3) If the same then lock row and perform update.

=20

Future development of this could extend to remove more out of code to =
handle
when the two don't match. The procedure there is=20

1) Compare Original Field Value to Current Value in Memory if the =
two
don't match then

2) IF the Original Field Value and the Current Value Stored in Table
Match then update Field IF not then raise error and prompt user for =
action.

=20

This may be more information than required, but some one out their might =
be
doing similar things that they can point me in the direction of some =
more
documentation or even better still a few Example scripts that I can pull
apart and learn from.

=20

=20

Regards,

Justin Elward

=20

Blue Wave Software Pty Limited

justin@bluewavesoftw
are.com.au

=20

Ph. +61 2 4320 6090

Fx. +61 2 4320 6092

=20

-------------------------------------------------------------------------=
---
-------------------

DISCLAIMER:=20

This message is proprietary to Blue Wave Software Pty Limited (BWS) and =
is
intended solely for the use of the individual or individuals to whom it =
is
addressed. It may contain privileged or confidential information and =
should
not be circulated with out informing BWS prior or used for any purpose =
other
than for what it is intended. If you have received this message in =
error,
please notify the originator immediately. If you are not the intended
recipient, you are notified that you are strictly prohibited from using,
copying, altering, or disclosing the contents of this message. BWS =
accepts
no responsibility (except where required under Australian law) for loss =
or
damage arising from the use or misuse of the information transmitted by =
this
email including damage from virus."

-------------------------------------------------------------------------=
---
-------------------

=20

------ =_NextPart_000_0006_
01C5C2FE.BC285A10--

Peter Brawley

2005-09-26, 1:23 pm

--=======AVGMAIL-433832D745C4=======
Content-Type: multipart/alternative; boundary=------------ 03050707040500060904
0809

-------------- 03050707040500060904
0809
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Justin,

>I don't have any experience with stored procedures and find the
>Documentation in the MYSQL manual a bit sketchy or maybe I am just
>miss reading it. Can any one point me to some documentation that will
>help with fully understanding Stored Procedures?

>What I am trying to migrate out of my program code is a procedure to
>do the following for update commands.

>1) Determine that the ID Field and the Last Updated Timestamp Field
>is still the same as when the data was originally read.

>2) If not the same then Raise an error back to the program so It can
>determine the action.

>3) If the same then lock row and perform update.

First, a quibble about the logic. Between the time a row is re-read for
changes and the time you write-lock it, it is possible for another process
to update the row. It would seem sounder to explicitly write-lock it up
front, make your changes, then unlock it. But secondly, LOCK is not
permitted in MySQL stored procs, so if you want to use MyISAM
and LOCK, you have to issue the LOCK outside the sproc, eg

SET GLOBAL log_bin_trust_routin
e_creators=TRUE;
DROP PROCEDURE IF EXISTS UpdTime;
LOCK TABLES test WRITE;
DELIMITER |
CREATE PROCEDURE UpdTime( IN readid INT, IN newtime TIMESTAMP )
BEGIN
UPDATE test SET time=newtime WHERE id=readid;
END;
|
DELIMITER ;
UNLOCK TABLES;

or more elegantly, convert the table to InnoDB and use a transaction to
accomplish
the same effect.

PB
http://www.artfulsoftware.com

-----

Blue Wave Software wrote:

>I don't have any experience with stored procedures and find the
>Documentation in the MYSQL manual a bit sketchy or maybe I am just miss
>reading it. Can any one point me to some documentation that will help with
>fully understanding Stored Procedures?
>
>
>
>What I am trying to migrate out of my program code is a procedure to do the
>following for update commands.
>
>
>
>1) Determine that the ID Field and the Last Updated Timestamp Field is
>still the same as when the data was originally read.
>
>2) If not the same then Raise an error back to the program so It can
>determine the action.
>
>3) If the same then lock row and perform update.
>
>
>
>Future development of this could extend to remove more out of code to handle
>when the two don't match. The procedure there is
>
>1) Compare Original Field Value to Current Value in Memory if the two
>don't match then
>
>2) IF the Original Field Value and the Current Value Stored in Table
>Match then update Field IF not then raise error and prompt user for action.
>
>
>
>This may be more information than required, but some one out their might be
>doing similar things that they can point me in the direction of some more
>documentation or even better still a few Example scripts that I can pull
>apart and learn from.
>
>
>
>
>
>Regards,
>
> Justin Elward
>
>
>
>Blue Wave Software Pty Limited
>
> justin@bluewavesoftw
are.com.au
>
>
>
>Ph. +61 2 4320 6090
>
>Fx. +61 2 4320 6092
>
>
>
>----------------------------------------------------------------------------
>-------------------
>
>DISCLAIMER:
>
>This message is proprietary to Blue Wave Software Pty Limited (BWS) and is
>intended solely for the use of the individual or individuals to whom it is
>addressed. It may contain privileged or confidential information and should
>not be circulated with out informing BWS prior or used for any purpose other
>than for what it is intended. If you have received this message in error,
>please notify the originator immediately. If you are not the intended
>recipient, you are notified that you are strictly prohibited from using,
>copying, altering, or disclosing the contents of this message. BWS accepts
>no responsibility (except where required under Australian law) for loss or
>damage arising from the use or misuse of the information transmitted by this
>email including damage from virus."
>
>----------------------------------------------------------------------------
>-------------------
>
>
>
>
>
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005
>
>

-------------- 03050707040500060904
0809
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Justin, 
 
>I don't have any experience with stored procedures and find the 
>Documentation in the MYSQL manual a bit sketchy or maybe I am just 
>miss reading it. Can any one point me to some documentation that
will 
>help with fully understanding Stored Procedures? 
 
>What I am trying to migrate out of my program code is a procedure
to 
>do the following for update commands. 
 
>1)     Determine that the ID Field and the Last Updated Timestamp
Field 
>is still the same as when the data was originally read. 
 
>2)     If not the same then Raise an error back to the program so
It can 
>determine the action. 
 
>3)     If the same then lock row and perform update. 
 
First, a quibble about the logic. Between the time a row is re-read for 
changes and the time you write-lock it, it is possible for another
process 
to update the row. It would seem sounder to explicitly write-lock it up
 
front, make your changes, then unlock it. But secondly, LOCK is not 
permitted in MySQL stored procs, so if you want to use MyISAM 
and LOCK, you have to issue the LOCK outside the sproc, eg 
 
SET GLOBAL log_bin_trust_routin
e_creators=TRUE; 
DROP PROCEDURE IF EXISTS UpdTime; 
LOCK TABLES test WRITE; 
DELIMITER | 
CREATE PROCEDURE UpdTime( IN readid INT, IN newtime TIMESTAMP ) 
BEGIN 
  UPDATE test SET time=newtime WHERE id=readid; 
END; 
| 
DELIMITER ; 
UNLOCK TABLES; 
 
or more elegantly, convert the table to InnoDB and use a transaction to
accomplish 
the same effect. 
 
PB 
<a class="moz-txt-link-freetext" href="http://www.artfulsoftware.com">http://www.artfulsoftware.com</a> 
 
----- 
 
Blue Wave Software wrote:
<blockquote cite=" mid000501c5c2aa$ea7c
4a10$6700a8c0@jenote
" type="cite">
<pre wrap="">I don't have any experience with stored procedures and find the
Documentation in the MYSQL manual a bit sketchy or maybe I am just miss
reading it. Can any one point me to some documentation that will help with
fully understanding Stored Procedures?

What I am trying to migrate out of my program code is a procedure to do the
following for update commands.

1) Determine that the ID Field and the Last Updated Timestamp Field is
still the same as when the data was originally read.

2) If not the same then Raise an error back to the program so It can
determine the action.

3) If the same then lock row and perform update.

Future development of this could extend to remove more out of code to handle
when the two don't match. The procedure there is

1) Compare Original Field Value to Current Value in Memory if the two
don't match then

2) IF the Original Field Value and the Current Value Stored in Table
Match then update Field IF not then raise error and prompt user for action.

This may be more information than required, but some one out their might be
doing similar things that they can point me in the direction of some more
documentation or even better still a few Example scripts that I can pull
apart and learn from.

Regards,

Justin Elward

Blue Wave Software Pty Limited

<a class="moz-txt-link-abbreviated" href=" mailto:justin@bluewa
vesoftware.com.au"> justin@bluewavesoftw
are.com.au</a>

Ph. +61 2 4320 6090

Fx. +61 2 4320 6092

----------------------------------------------------------------------------
-------------------

DISCLAIMER:

This message is proprietary to Blue Wave Software Pty Limited (BWS) and is
intended solely for the use of the individual or individuals to whom it is
addressed. It may contain privileged or confidential information and should
not be circulated with out informing BWS prior or used for any purpose other
than for what it is intended. If you have received this message in error,
please notify the originator immediately. If you are not the intended
recipient, you are notified that you are strictly prohibited from using,
copying, altering, or disclosing the contents of this message. BWS accepts
no responsibility (except where required under Australian law) for loss or
damage arising from the use or misuse of the information transmitted by this
email including damage from virus."

----------------------------------------------------------------------------
-------------------

</pre>
<pre wrap="">
<hr size="4" width="90%">
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005
</pre>
</blockquote>
</body>
</html>

-------------- 03050707040500060904
0809--
--=======AVGMAIL-433832D745C4=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"

No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005

--=======AVGMAIL-433832D745C4=======
Content-Type: text/plain; charset=us-ascii

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
--=======AVGMAIL-433832D745C4=======--

Gleb Paharenko

2005-09-27, 7:23 am

Hello.

Have a look here:
http://dev.mysql.com/tech-resources...procedures.html

However, it isn't clear for me what originally read data means. Do you
store the time of the first access to the data in some table or
somewhere else? You might obtain a better answer from the list members
if your add more details about table structure and your application logic.

>I don't have any experience with stored procedures and find the
>Documentation in the MYSQL manual a bit sketchy or maybe I am just miss
>reading it. Can any one point me to some documentation that will help with
>fully understanding Stored Procedures?
>
>
>
>What I am trying to migrate out of my program code is a procedure to
do the
>following for update commands.
>
>
>
>1) Determine that the ID Field and the Last Updated Timestamp Field is
>still the same as when the data was originally read.
>
>2) If not the same then Raise an error back to the program so It can
>determine the action.
>
>3) If the same then lock row and perform update.
>
>
>
>Future development of this could extend to remove more out of code to
handle
>when the two don't match. The procedure there is
>
>1) Compare Original Field Value to Current Value in Memory if the two
>don't match then
>
>2) IF the Original Field Value and the Current Value Stored in Table
>Match then update Field IF not then raise error and prompt user for
action.
>
>
>
>This may be more information than required, but some one out their
might be
>doing similar things that they can point me in the direction of some more
>documentation or even better still a few Example scripts that I can
pull apart and learn from.

Blue Wave Software wrote:

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@ensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links