.Execute Fails with 3707 error trying to create/replace macro

[DaveRoth]

It looks like the execute code doesn't accurately identify the end of macro DDL and throws this error:

3707, "[42000] [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error, expected something like ')' between ';' and the end of the request. "

[escheie]

Here is an excerpt from the documentation:

A semi-colon is used as the default delimiter when specifying multiple SQL statements. Any occurrence of a semi-colon outside of a SQL string literal or comments is treated as a delimiter. When SQL scripts contain SQL stored procedures that contain semi-colons internal to the procedure, the delimiter should be change to something other than the default. To use a different character sequence as the delimiter, the delimiter parameter can be used. E.g.

session.execute(file="myqueries.sql", delimiter=";;")

Does this help resolve your problem?

[DaveRoth]

Yes, I did try changing to a delimiter of ');' thinking that might get it to see the end of macro, but got same error.

thanks

Dave

---

From: Eric Scheie notifications@github.com Sent: Thursday, December 7, 2017 11:01 To: Teradata/PyTd Cc: Dave Roth; Author Subject: Re: [Teradata/PyTd] .Execute Fails with 3707 error trying to create/replace macro (#88)

Here is an excerpt from the documentation:

A semi-colon is used as the default delimiter when specifying multiple SQL statements. Any occurrence of a semi-colon outside of a SQL string literal or comments is treated as a delimiter. When SQL scripts contain SQL stored procedures that contain semi-colons internal to the procedure, the delimiter should be change to something other than the default. To use a different character sequence as the delimiter, the delimiter parameter can be used. E.g.

session.execute(file="myqueries.sql", delimiter=";;")

Does this help resolve your problem?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FTeradata%2FPyTd%2Fissues%2F88%23issuecomment-350011793&data=02%7C01%7C%7C9bde45aa8585450b353e08d53d8bc5a0%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636482592876851073&sdata=ImLvfgGM8MyZjROctZSvF0tu7dzNk4dlhVz%2FUiMXv0o%3D&reserved=0, or mute the threadhttps://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAHzkTnxOObXR8NUGnLq8cbFq_1mouEu6ks5s-AvVgaJpZM4Q5h-A&data=02%7C01%7C%7C9bde45aa8585450b353e08d53d8bc5a0%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636482592876851073&sdata=RDO%2Fdxc3db6yer50KbfGesfAieXZtN68MnSrd2awvAg%3D&reserved=0.

[escheie]

Can you provide sample script to reproduce your error?

[DaveRoth]

Hey just went on vacation will submit in a week but it’s the basic format. Doesn’t matter if comment there or not

Replace macro xxx ( Unparalleled ) As ( Select xxx ;

);

Comment on xxx ‘stuff’;

Dave Roth

On Dec 7, 2017, at 12:08, Eric Scheie notifications@github.com<mailto:notifications@github.com> wrote:

Can you provide sample script to reproduce your error?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://nam03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FTeradata%2FPyTd%2Fissues%2F88%23issuecomment-350032364&data=02%7C01%7C%7Cc5ff4bf4e70c4fbd355708d53d952490%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636482633122285506&sdata=%2FyvLuQH9mfDuaoeyWHa1ytlfYUOj0%2B8Gh8oFTnG6PmM%3D&reserved=0, or mute the threadhttps://nam03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAHzkTg9wcEz02wUl1tI5h0FgaV-uaprzks5s-BuOgaJpZM4Q5h-A&data=02%7C01%7C%7Cc5ff4bf4e70c4fbd355708d53d952490%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636482633122285506&sdata=aUDcwoLIY9krDasB2HtcWZvpW05TxIcouXBRdrY5nYc%3D&reserved=0.

[DaveRoth]

ok, back :)

here is the full macro text that fails:

MODEL_STANDARDS_CLOB_USAGE.txt

and this is the error again

[escheie]

I was able to execute the script using the following statement:

session.execute(file="MODEL_STANDARDS_CLOB_USAGE.txt", delimiter=";;"):

I just had to add an extra ";" at the end of the macro. E.g. ");;"

[DaveRoth]

well darn, that does get me by the prior error but still throws an only DDL allowed error because it tried to run the replace macro and the comment in the same query. Did you remove the comment by chance?

[escheie]

No, I just added the extra semi-colon after the macro so PyTd knew where to split the two queries.

[DaveRoth]

So, I got a version to work by splitting out the macro and comment into a string array then executing both parts. A little clunky but workable. Maybe we need a special macro process ?

[DaveRoth]

Got it. Yeah that would work but I would never get them to accept non standard coding :) Thanks for all your help!

[DaveRoth]

Closing this as I was able to code a work around in my modules.