Whenever I make an update to my database (changes in stored procs, changes in tables, new tables ,etc.), I create a new sql file which I run on client’s computer via TeamViewer.
I wanted my application to update its own database. So I decided to make it download sql files via ftp then updates itself using ExecuteNonQuery or something.
Unfortunately I’m stuck at Delimiters pushing errors. If I remove Delimiters, running stored proc update queries are impossible (or am I missing something?).
Here is a sample content of an sql file I usually run:
USE 'iceberg'; DROP procedure IF EXISTS 'get_unpaidandduetickets'; DELIMITER $$ USE 'iceberg'$$ CREATE DEFINER='root'@'localhost' PROCEDURE 'get_unpaidandduetickets'() BEGIN SELECT (SELECT IFNULL(COUNT(ticketid),0) FROM tickets WHERE balance > 0 AND status = 1) AS unpaidtickets ,(SELECT IFNULL(COUNT(ticketid),0) FROM tickets WHERE balance > 0 AND status = 1 AND DATE(duedate) = DATE(NOW())) AS dueticketstoday ,(SELECT IFNULL(COUNT(ticketid),0) FROM tickets WHERE balance > 0 AND status = 1 AND DATE(duedate) < DATE(NOW())) AS pastduetickets; END$$ DELIMITER ; CALL set_databaseversion('001020181119');
Is there any way I could pull this off? I’m open to other means.
Thanks in advance!
Advertisement
Answer
Assumed that you have connection string which contains the database name, it is unnecessary to append USE 'iceberg';
and DELIMITER $$
in the script because MySqlScript
instance can control the delimiter for you. Therefore, adjust your script contents as in example below:
DROP procedure IF EXISTS 'get_unpaidandduetickets'$$ CREATE DEFINER='root'@'localhost' PROCEDURE 'get_unpaidandduetickets'() BEGIN -- procedure contents here END$$ CALL set_databaseversion('001020181119')$$
Then, use ReadAllText()
to read entire file contents as string and pass it into Query
property inside MySqlScript
instance:
' connection string example ' see connectionstrings.com/mysql-connector-net-mysqlconnection/ for details Dim connectionString As String = "Server=localhost;Port=3306;Database=iceberg;Uid=UserID;Pwd=Password" Using con As New MySqlConnection(connectionString) Dim content As String = System.IO.File.ReadAllText("scriptfilename.sql") Dim sqlScript As New MySqlScript(con) sqlScript.Query = content ' query string sqlScript.Delimiter = "$$" ' delimiter setting sqlScript.Execute() ' other stuff End Using
Note: You can create custom extension method which removes unnecessary parts such like DELIMITER
and USE
keywords.
Reference: Using Delimiters with MySqlScript