Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Problem with double quotes #965

Closed
sogft opened this issue Mar 4, 2015 · 3 comments
Closed

Problem with double quotes #965

sogft opened this issue Mar 4, 2015 · 3 comments

Comments

@sogft
Copy link

@sogft sogft commented Mar 4, 2015

Hi,
we have a problem when we try to migrate the following sql :


-- procedure get_stats_bonus_cumul


DROP procedure IF EXISTS get_stats_bonus_cumul;
DELIMITER //
CREATE PROCEDURE get_stats_bonus_cumul (isdn BIGINT, bid VARCHAR(100), dt0 DATE, dt1 DATE)
BEGIN
DECLARE sql_cmd VARCHAR(10000);

SET sql_cmd = CONCAT(
'SELECT
s1.msisdn AS msisdn,
b.bonus_name AS bonus_name,
"',dt0,'" AS from_date,
"',dt1,'" AS to_date,
s1.qty_earned - COALESCE(s0.qty_earned,0) AS qty_earned,
s1.qty_used - COALESCE(s0.qty_used,0) AS qty_used,
s1.qty_expired - COALESCE(s0.qty_expired,0) AS qty_expired,
b.recommended_price,
b.optin,
b.unit
FROM stats_bonus s1
LEFT JOIN stats_bonus s0 ON (s0.msisdn = s1.msisdn AND s0.agg_date="',dt0,'")
LEFT JOIN bonuses b ON (s1.bonus_id = b.bonus_id)
WHERE s1.agg_date="',dt1,'"');

IF NOT (isdn = 0) THEN
SET sql_cmd = CONCAT(sql_cmd, ' AND s1.msisdn=',isdn);
END IF;

IF(bid <> '') THEN
SET sql_cmd = CONCAT(sql_cmd, ' AND b.bonus_id IN (',bid,')');
END IF;

SET @sqlstatement = sql_cmd;
PREPARE sqlquery FROM @sqlstatement;
EXECUTE sqlquery;
DEALLOCATE PREPARE sqlquery;

END
//

DELIMITER ;

The problem is related to the double quote followed to the single quote .

The issue resolved on #611 consider only the single quote followed to the double quote.

Please, can you fix this bug, we have a lot of this files to migrate .

Thanks.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Mar 4, 2015

Can you distill it down to the simplest possible statement that fails? (I am assuming you are using MySQL...)

Cheers
Axel

@sogft
Copy link
Author

@sogft sogft commented Mar 4, 2015

Hi Axel,
yes, can you try this statement , the same error :

CREATE PROCEDURE get_stats_bonus_cumul (isdn BIGINT, bid VARCHAR(100), dt0 DATE, dt1 DATE)
DELIMITER
//

BEGIN
DECLARE sql_cmd VARCHAR(10000);
SET sql_cmd = CONCAT(
'SELECT
"',dt0,'" AS from_date,
FROM stats_bonus s1
WHERE s1.agg_date="',dt1,'"');

SET @sqlstatement = sql_cmd;
PREPARE sqlquery FROM @sqlstatement;
EXECUTE sqlquery;
DEALLOCATE PREPARE sqlquery;
END

//

DELIMITER ;

I confirm you that we use Mysql.

Thanks,
Marco.

@axelfontaine axelfontaine changed the title Problem with double quotes - Command Line 3.1 Problem with double quotes Mar 5, 2015
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Mar 5, 2015
@axelfontaine axelfontaine added this to the Flyway 3.2 milestone Mar 5, 2015
@sogft
Copy link
Author

@sogft sogft commented Mar 5, 2015

Thanks Axel for a quick resolution !

jmahonin added a commit to jmahonin/flyway that referenced this issue Jul 15, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.