| Bug #42455 | Can't create a Trigger from VB program using MySQL ODBC | ||
|---|---|---|---|
| Submitted: | 29 Jan 2009 14:47 | Modified: | 29 May 2013 6:12 |
| Reporter: | Roberto Farioli | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 5.1.5 | OS: | Windows (XP) |
| Assigned to: | Lawrenty Novitsky | CPU Architecture: | Any |
| Tags: | ODBC, trigger | ||
[29 Jan 2009 16:40]
Tonci Grgin
Hi Roberto and thanks for your report. DELIMITER is valid command only in client, not in ODBC library thus this is *not* a bug. You are free to check what general query log entries DELIMITER produces. I'll give you a hint, none... Full Connect(Default) Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3 Successfully connected to DSN '5-1-05-local'. DELIMITER $ SQLExecDirect: In: hstmt = 0x0000000000649F10, szSqlStr = "", cbSqlStr = -3 Return: SQL_ERROR=-1 stmt: szSqlState = "42000", *pfNativeError = 1064, *pcbErrorMsg = 210, *ColumnNumber = -1, *RowNumber = -2 MessageText = "[MySQL][ODBC 5.1 Driver][mysqld-5.1.30-community-log]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 $' at line 1" Closing as !Bg.
[29 Jan 2009 16:55]
Roberto Farioli
Thanks, OK, but I have tested same DDL query without DELIMETER $$ call and parsing problem still arise
[29 Jan 2009 21:36]
Jess Balint
Robert, Don't include any dollar signs in your program. Please fix this and if the problem persists, show the exact error returned from MySQL.
[1 Mar 2009 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[29 May 2013 6:12]
Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.
Description: If I run this script from MySQL Query Browser, it is ok: ---------------------------------------------------------------------- DELIMITER $$ DROP TRIGGER IF EXISTS `test`.`trgProvaTrigger` $$ CREATE TRIGGER trgProvaTrigger BEFORE INSERT ON provatrigger FOR EACH ROW BEGIN DECLARE FETCHED_LOG_START datetime; DECLARE FETCHED_MASSIMO numeric(19,3); DECLARE FETCHED_MINIMO numeric(19,3); DECLARE FETCHED_MEDIA numeric(19,3); DECLARE FETCHED_NUMPUNTI numeric(19,3); DECLARE done INT DEFAULT 0; DECLARE cInsert CURSOR FOR SELECT LOG_START, MAX(DATO) AS MASSIMO, MIN(DATO) AS MINIMO, AVG(DATO) AS MEDIA, COUNT(DATO) AS NUMPUNTI FROM ( SELECT NEW.LOG_START AS LOG_START, NEW.D01 AS DATO UNION ALL SELECT NEW.LOG_START AS LOG_START, NEW.D02 AS DATO UNION ALL SELECT NEW.LOG_START AS LOG_START, NEW.D03 AS DATO) as T1 WHERE DATO <> -9999 GROUP BY NEW.LOG_START; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cInsert; REPEAT FETCH cInsert INTO FETCHED_LOG_START, FETCHED_MASSIMO, FETCHED_MINIMO, FETCHED_MEDIA, FETCHED_NUMPUNTI; IF NOT done THEN SET NEW.MINIMO = FETCHED_MINIMO; SET NEW.MASSIMO = FETCHED_MASSIMO; SET NEW.MEDIA = FETCHED_MEDIA; SET NEW.NUMPUNTI = FETCHED_NUMPUNTI; END IF; UNTIL done END REPEAT; CLOSE cInsert; END $$ DELIMITER ; ---------------------------------------------------------------------- If I run that query in a VB program using MySQL ODBC, I have an error in parsing the query How to repeat: Create a table: CREATE TABLE TestTrigger (LOG_START DateTime NOT NULL, D01 decimal(12,4) NOT NULL DEFAULT -9999, D02 decimal(12,4) NOT NULL DEFAULT -9999, D03 decimal(12,4) NOT NULL DEFAULT -9999, MINIMO decimal(12,4) NOT NULL DEFAULT -9999, MASSIMO decimal(12,4) NOT NULL DEFAULT -9999, MEDIA decimal(12,4) NOT NULL DEFAULT -9999, NUMPUNTI integer(4) NOT NULL DEFAULT 0 ) ENGINE=MyISAM; and put those lines in a VB 6.0 program using MySQL ODBC 5.1 ---------------------------------------------------------------------- Dim sSql As String sSql = "DELIMITER $$" & Chr(13) sSql = sSql & "DROP TRIGGER IF EXISTS trgTestTrigger $$" & Chr(13) sSql = sSql & "CREATE TRIGGER trgtestTrigger BEFORE INSERT ON TestTrigger" & Chr(13) sSql = sSql & "FOR EACH ROW" & Chr(13) sSql = sSql & "BEGIN" & Chr(13) sSql = sSql & "DECLARE FETCHED_LOG_START datetime;" & Chr(13) sSql = sSql & "DECLARE FETCHED_MASSIMO numeric(19,3);" & Chr(13) sSql = sSql & "DECLARE FETCHED_MINIMO numeric(19,3);" & Chr(13) sSql = sSql & "DECLARE FETCHED_MEDIA numeric(19,3);" & Chr(13) sSql = sSql & "DECLARE FETCHED_NUMPUNTI numeric(19,3);" & Chr(13) sSql = sSql & "DECLARE done INT DEFAULT 0;" & Chr(13) sSql = sSql & "DECLARE cInsert CURSOR FOR" & Chr(13) sSql = sSql & "SELECT LOG_START, MAX(DATO) AS MASSIMO, MIN(DATO) AS MINIMO, AVG(DATO) AS MEDIA, COUNT(DATO) AS NUMPUNTI FROM (" & Chr(13) sSql = sSql & "SELECT NEW.LOG_START AS LOG_START, NEW.D1 AS DATO " & Chr(13) sSql = sSql & "SELECT NEW.LOG_START AS LOG_START, NEW.D2 AS DATO " & Chr(13) sSql = sSql & "SELECT NEW.LOG_START AS LOG_START, NEW.D3 AS DATO " & Chr(13) sSql = sSql & ") as T1" & Chr(13) sSql = sSql & "WHERE DATO <> -9999" & Chr(13) sSql = sSql & "GROUP BY NEW.LOG_START;" & Chr(13) sSql = sSql & "DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;" & Chr(13) sSql = sSql & "OPEN cInsert;" & Chr(13) sSql = sSql & "REPEAT" & Chr(13) sSql = sSql & "FETCH cInsert INTO FETCHED_LOG_START, FETCHED_MASSIMO, FETCHED_MINIMO, FETCHED_MEDIA, FETCHED_NUMPUNTI;" & Chr(13) sSql = sSql & "IF NOT done THEN" & Chr(13) sSql = sSql & "SET NEW.MINIMO = FETCHED_MINIMO;" & Chr(13) sSql = sSql & "SET NEW.MASSIMO = FETCHED_MASSIMO;" & Chr(13) sSql = sSql & "SET NEW.MEDIA = FETCHED_MEDIA;" & Chr(13) sSql = sSql & "SET NEW.NUMPUNTI = FETCHED_NUMPUNTI;" & Chr(13) sSql = sSql & "END IF;" & Chr(13) sSql = sSql & "UNTIL done END REPEAT;" & Chr(13) sSql = sSql & "CLOSE cInsert;" & Chr(13) sSql = sSql & "END $$" & Chr(13) sSql = sSql & "DELIMITER ;" & Chr(13) .ExecuteSQL sSql ----------------------------------------------------------------------