question - connector or query problem?

Reply

Member

96 Points

question - connector or query problem?

May 20, 2010 11:10 PM|lindows|LINK

hi all,

i'm trying to connect to multiple databases to create new stored procedures (updating production databases)

i have multiple database servers, each hosting a number of databases that needs to be updated, i have my logic to loop and connect each of those database servers and to the databases itself to execute the logic below, but i have an error when trying to execute the .sql file

the file works fine in navicat (query mode) or mysql command,

i use navicat to do a data transfer and export my stored procedures as a .sql file

below is what the sql file looks like

/*
Navicat MySQL Data Transfer


Source Server : localhost
Source Server Version : 50146
Source Host : localhost:3306
Source Database : f06bd4fe


Target Server Type : MYSQL
Target Server Version : 50146
File Encoding : 65001


Date: 2010-05-20 21:26:34
*/


SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Procedure structure for `prc_site_selector_site_listing`
-- ----------------------------
DROP PROCEDURE IF EXISTS `prc_site_selector_site_listing`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `prc_site_selector_site_listing`(FK_users_COL INT(10), Start_Limit_COL INT(10), End_Limit_COL INT(10))
BEGIN


SET @FK_users = FK_users_COL;
SET @Start_Limit = Start_Limit_COL;
SET @End_Limit = End_Limit_COL;


PREPARE STMT FROM
"SELECT Q1.row_count, Q2.PK_sites, Q2.site_display_name, Q2.site_description, Q2.PK_permissions_lookup, Q2.control_set FROM
(SELECT COUNT(*) row_count
FROM
tbl_sites
Inner Join tbl_permissions_lookup ON tbl_sites.PK_sites = tbl_permissions_lookup.FK_sites
WHERE
tbl_permissions_lookup.FK_users = ?) As Q1,
(SELECT
tbl_sites.PK_sites,
tbl_sites.site_display_name,
tbl_sites.site_description,
tbl_permissions_lookup.PK_permissions_lookup,
tbl_permissions_lookup.control_set
FROM
tbl_sites
Inner Join tbl_permissions_lookup ON tbl_sites.PK_sites = tbl_permissions_lookup.FK_sites
WHERE
tbl_permissions_lookup.FK_users = ?
ORDER BY tbl_sites.site_display_name ASC
LIMIT ?,?) AS Q2";


EXECUTE STMT USING @FK_users, @FK_users, @Start_Limit, @End_Limit;


END
;;
DELIMITER ;

and here is how i'm applying my changes

Dim RemoteConnectionString As String = remote_db_connection_string 
Dim connectionObject As New MySqlConnection(RemoteConnectionString) 
Dim commandText As String = sql_content_file 
Dim command As New MySqlCommand(commandText, connectionObject) 

If connectionObject.State = ConnectionState.Closed Then connectionObject.Open() 
command.ExecuteNonQuery() 
command.Parameters.Clear() 
connectionObject.Close() 
connectionObject.Dispose()

The sql executes fine in mysql console if i copy and paste into mysql console, it works fine if i copy and paste into navicat query mode, but .net crashes...

when i execute, i get error
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER ;;"