How do I create Stored Procedures in MySQL for the OPC Data Logger?

We have found that Stored Procedures created in MySQL are stored as plain text.  Because of this we have found that Stored Procedures have to be formatted in a particular way. 

Below are 3 examples. The first one works, and the second two do not.

This works because each Column Name is on a separate line:

CREATE PROCEDURE `myDatabase`.`myPROCName`(
TagName varchar(10),
TagTime timestamp,
TagQuality varchar(10),
TagValue int
)
INSERT INTO myDatabase.myTable( ItemName, ItemValue, ItemQuality, ItemTimestamp)
VALUES ( TagName, TagValue, TagQuality, TagTime  );

This does not work because each Column is NOT on a separate line:

CREATE PROCEDURE `myDatabase`.`myPROCName` (TagName varchar(10), TagTime timestamp, TagQuality varchar(10), TagValue int)
INSERT INTO myDatabase.myTable( ItemName, ItemValue, ItemQuality, ItemTimestamp)
VALUES ( TagName, TagValue, TagQuality, TagTime  );

In the OPC Data Logger, you would only see the following:  VARCHAR(10).  This is incorrect!  We should see: TagName, TagTime, TagValue and TagDescription.

This does not work because the first character on each new line is a space.  Make sure there is no space on each new line:

CREATE PROCEDURE `myDatabase`.`mySPROCName`(
   TagName varchar(10),
   TagTime timestamp,
   TagQuality varchar(10),
   TagValue int
   )
INSERT INTO myDatabase.myTable( ItemName, ItemValue, ItemQuality, ItemTimestamp)
VALUES ( TagName, TagValue, TagQuality, TagTime  );

This would show four empty lines where the tags should be, this is because of the space.

We have also found that some MySQL Clients do not create the Stored Procedures in the correct format. To avoid this, we recommend using the MySQL Command Line Client to create your Stored Procedures.

The Stored Procedure should be saved a file with extension *.sql.  A sample file is attached below to test with. To run this:

1. Open the Command Line MySQL Client.
2. You will first need to log into your database with your password. 
3. Once you log in you should see the following.

   mysql>

4. The command to run your *.sql file is:

   source SQLStatement.sql

5. This will only work if SQLStatement.sql is stored in the same directory as the executable for the Command Line Client.  For MySQL 5.0 the default location is "C:\Program Files\MySQL\MySQL Server 5.0\bin".  You can also specify the fully qualified path of the SQLStatement.sql file.

• SQLStatement.sql