When setting bind variable value getting syntax error

Toad Data Point
1

Hello

I am using TDA v2.6 and in my automation script I am trying to setup three variables (aw, ss, wk) and I am able to get one working (wk) but the other two are failing and I get the below error (extract from log file):

– 16/06/2010 12:56:32: AR_Seasonal.log: Variable “aw” set to “#WK_SEAS.AW_SEAS#”
– 16/06/2010 12:56:32: AR_Seasonal.log: Variable value = Syntax error

I have narrowed the issue down the fact that the variable can not handle the / in the value it is getting from the result set. An example in this case is 2010A/W, when the result was 2010AW the variable could handle it.

Is there anyway around this as 90% of our season codes (that’s what the value above is!) have these in?

Thank you
Glenn

2

Hi Glenn,

This was a known issue in TDA 2.5 and is fixed in 2.6. To workaround the issue in 2.5 surround the value with quotes. I have attached two sample projects that run in C:\Temp. Just change to your Oracle connection. One is the workaround for 2.5 and the other is how it should work in 2.6.

I know you said you are using 2.6 but I find it ok in this version. If you are using 2.6 and the same code does not work for you, let me know and we can dig deeper into the issue.

Debbie
Seasoncode_2_5.zip (1.74 KB)

3

Hi Glenn,

This was a known issue in TDA 2.5 and is fixed in 2.6. To workaround the issue in 2.5 surround the value with quotes. I have attached two sample projects that run in C:\Temp. Just change to your Oracle connection. One is the workaround for 2.5 and the other is how it should work in 2.6.

I know you said you are using 2.6 but I find it ok in this version. If you are using 2.6 and the same code does not work for you, let me know and we can dig deeper into the issue.

Debbie
SeasonCode_2_6.zip (1.72 KB)

4

Hi Debbie

Thanks for responding so quickly, I had a look at your answer but my problem is that my season code is not hard coded but is the result of a query itself as it will vary week on week what the season code it is that I need to look for. The code I am using to get the data for my variables is:

SELECT GC_BYP_VARS2.TIME_ID,
GC_BYP_VARS2.WK_TYPE,
GC_BYP_VARS2.AW_SEAS,
GC_BYP_VARS2.SS_SEAS
FROM MERLIVE.GC_BYP_VARS2 GC_BYP_VARS2

So it is the result of column GC_BYP_VARS2.AW_SEAS that I am trying to return as my variable aw, so I am unable to put the quotes around it unless I am misunderstanding you?

Thanks
Glenn

5

I created a table and put the sample season code in it and it works the same in 2.6. To use the work around in 2.5 you can do this–>

select ‘"’ || SEASON_CODE || ‘"’ from BACKSLASH_TEST

Debbie

6

select ‘"’ || SEASON_CODE || ‘"’ as SEASON_CODE from BACKSLASH_TEST

Correction, you do need to give it an alias.

7

Hi Debbie

I am definitely running this is TDA v2.6 but it still gives me a syntax error when I ask it to use the actual column.

However using your concatenation idea for the column with an alias I was able to get it to successfully bring back the season code and apply it to the script, so I will stick with this.

Thank you for all your help

Glenn

8

Sounds like I still have an error in my 2.6 code and I would like to fix. Can you send me a Excel or csv file of all of the season codes (or whatever the column is that is causing the issue)? You shouldn’t have to quote these in 2.6.

Debbie

9

I have attached the spreadhseet of this weeks extract, below is the code used to query it in case this helps.

SELECT GC_BYP_VARS2.TIME_ID,
GC_BYP_VARS2.WK_TYPE,
GC_BYP_VARS2.AW_SEAS,
GC_BYP_VARS2.SS_SEAS,
‘"’ || AW_SEAS || ‘"’ AS AW_SEAS2,
‘"’ || SS_SEAS || ‘"’ AS SS_SEAS2
FROM MERLIVE.GC_BYP_VARS2 GC_BYP_VARS2

Thanks
Glenn
WK_Seasons.xls (11 KB)

10

I imported your values to sample table and use your SQL without quotes. When I do this I do get a syntax error when setting the variable but I also get the correct values being set. See screenshot. Do you bet this? Or empty values or the last values set? It is possible that I did fix the issue but did not prevent from writing the error to the log file. Which would make it confusing.

Can you remove your quotes from your SQL and see if you get the same results?

Debbie
Errorbutnoerror.png

11

Hi Debbie

That is strange yes you are quite right, it is actually setting the values correctly and the automation script runs fine.

There were two reasons why I missed this to begin with, the first is I was setting the log reference up incorrect, using the variable value string (i.e. #WK_SEAS.AW_SEAS#) instead of the variable name and so I did not realise it was actually being set.

The second problem was actually to do with my main script, I was using more than one named variable in my sql script that was using an ODBC connection. I had forgot to use the positional parameters and then set them using the find and replace. This is what was actually causing my script to fail, not the supposed incorrectly set variables.

Thank you for all your help
Glenn

12

Sounds good. It is still confusing that I write that there was an error. So I will fix that. (Cr74, 984)

We are working on the positional parameters with ODBC. CR71,285 is to support multiple ‘?’ parms in ODBC. And CR69,476 for supporting them in automation. The first one is scheduled for 2.7 but support in automation I have scheduled for 3.0. If this support is a big deal I can try to push to 2.7. What do you think?

Debbie

13

I do intend to use more and more of these variables now as it speeds up the jobs significantly. For instance the job I just attached in the other post (appending loop results into one sheet) has been cut from three and a half hours when using a view, into a 6 minute job with the weeks as variables instead!

As there is the find and replace workaround option for now then it is not the end of the world if it is not possible to get it supported by 2.7.

Thanks
Glenn

14

Okay. Sounds good. Positional param support for Automation will be in 3.0.