Issues with Using Link SQL File on Select to File Automation

Has there been any reported issues with the “Link SQL file” option when utilizing the select to file activity in automation?

I have queries that are erroring out saying “File_1 - [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token was not valid. Valid tokens: ( + - ? : DAY INF NAN RID ROW RRN CASE CAST CHAR DATE DAYS HASH.”

When I run the query (which is a .sql) file in editor it works just fine. Also, when I use the Export Wizard for the query it works absolutely fine. Also, I can browse for the .sql file itself and not choose the Link to SQL file and it will run. It seems that the link SQL file selection is what causes the issue.

Also, I’m running into an issue with the delimiter in the select to file. I have the query going to a .csv file and again when I pull it in the editor and manually export or when I pull it using the export wizard, it exports the delimited values just fine, but when I run the query in the select to file (without using the Link SQL file), it delimits wierd and puts ’ ’ around half of the records causing it to delimit out one extra column. When I export as excel it also does just fine. It seems like it’s just an issue with .csv.

I’d really love to use the select to file option for simple .csv or excel without ahving to use the export wizard so I dont have to build a lot of templates.

Just wanted to see if there were known issues or not. I’m attaching a copy of the .sql file. Thanks

Chad King
Central_Basic_Subs_Camp_Pkg.sql (3.59 KB)

By the way, I’m using TDP 3.4. I have another machine with 3.3 and tested this on 3.3 and everything works absolutely fine. The query runs ok and the file export delimites just fine using the select to file option.

Thanks

Chad

I’d like to try two things.

  1. Can you take out the blank lines temporarily to see if this makes a difference? I realize that you are using these to make the SQL more legible. But let’s see if it is tripping up the Select To File parsing. We can later put in --region tags that fold in the editor.

  2. Try downloading this versions of TDP 3.4. I don’t know if you use Base or Pro so download which ever applies to you.

community-downloads.quest.com/…/ToadDataPoint_Base_3.4.0.2061.zip

community-downloads.quest.com/…/ToadDataPoint_pro_3.4.0.2061.zip

Debbie, I appreciate you helping me look into this.

I did do your step one and took out all spacing and yes it does run successfully when all spacing is removed. It ran successfully in 3.4.0.2038 before I installed the newer version 3.4.0.2061. Unfortunately, the delimiter issue still remains.

I did install version 3.4.0.2061. I tried the automation script brand new and retried the query with the spacing and it still has the same issue and does not run. When removing the spacing it does run successfully. Again though, the delimiting is still messed up as well and adding wierd ’ ’ around certain things causing the file to add extra columns in records (Not all, but most).

One last thing to note is that I do have other .sql files that I use that also has spacing and has no issues. On the other hand they are all done through the export wizard, and again it seems when I use the export wizard, the SQL with spacing runs fine and the delimiting runs fine as well.

Is it possible there is an issue with the 3.4 version select to file activity?

Thanks again for your help.

Chad

I can’t reproduce the parsing issue. Since I can’t execute your SQL I also don’t get your syntax error. I see you are fully qualifying your SQL so that should not be the problem. But it would not hurt to set the default schema on your connection to CENLIVFILE. The editor is different in that it sets the session to a specific schema before running the SQL.

Also, have you changed ODBC drivers recently?

What is the connection that is set in the select to file activity? Is that exactly the same as when you execute in the editor?

Debbie,

I have not changed any ODBC drivers and all other queries are working fine on both versions, which doesn’t indicate to me any driver issues.

Anyways, I’ve done further testing on the issue and was able to resolve the delimiter to .csv problem. Apparently in the export wizard or through other programs, any fields with a comma in them are bypassed and still separated correctly in the delimited .csv file. Apparently with this select to file option, it has issues with any fields with the commas specifically for exports to .csv files. I noticed that the fields that were having issues indeed had a comma in them. For example the fields that said Dallas Tx were fine, while some that said Dallas, Tx would have this issue. At this point, dont know if there’s anything further that can be done to the software, but I was able to resolve this by using a REPLACE function to get rid of that Comma in the field. This works fine now using that.

In regards to the spacing issue causing the query to error out, I also noticed that it only has issues on multiple spaces in the query itself. For example if you say customer = 123, it’s fine, but if you said customer = 123 (with an extra space) this is where it has issues. To replicate this I took all the spacing out and switched solely to tabs to keep it still looking about the same. Therefore replacing all spacing with tabs. When I did this and got rid of any multiple spaces the query works just fine. Therefore, I have to think there’s an issue with 3.4 specifically with the select to file for any queries that have space used more than once on a query. I was wondering if you can try to replicate this issue on a text to file and either use more than one space or multiple spaces in a query and see if it does the same to you?

I appreciate the help with this and hope this gets us a bit further.

Chad

RE: CSV file with embedded comas: I thought that quoting all string values would allow for embedded quotes. Are they in your case?

Also, if you are exporting from our app, change to Delimited text type and choose another symbol for the delimitation. I like to use the bar for this --> | That symbol is not often embedded where comma’s are.

RE: SelectToFile spaceing issues. We ran into a simliar issue today with someone else. The file that was being linked was a unicode file and also had different carriage returns than we were expecting. Can you open your file in notepad and go to save it. What is the encoding at the bottom? The one you attached says ANSI but would like to check on your end.

Debbie,

Great point on the delimited types. I’ll keep that in mind and utilize that if the issue continues to remain present on other fields that may contain a comma. The replace function is also helpful and I at least know to utilize this when I run directly into this issue and need a quick fix.

In regards to the spacing issues of the SQL, the file encoding type of my .sql files are ANSI. Again i find it interesting it works fine on older versions, but suddenly stopped in the select to file method only for linked sql files with spaces being used. It disregards tabs, just more than one space seem to cause the issue.

Thanks again!!

We are looking into the spacing issue. For an update you can refer to QAT-1127, QAT-1122 and QAT-1891. I think these are all related and there is a deeper issue here. I need to give our developers some time to sort it out. Check back with me in a couple of weeks to see if we have something in the Beta to fix. Or look for these numbers in the release notes.

Chad, we made some changes in the code. Can you download the latest Beta and see if it is fixed?

www.toadworld.com/…/21426.aspx

It is beta build 3.5.0.2671

Debbie

Did these changes work for you? We are getting ready to release TDP 3.5. So the door for fixing is closing.

Debbie, I apologize for the delay on this. I didnt get a chance to download beta and due to the minimal impacting changes, we’ll probably wait for 3.6 for our next upgrade. I just wanted to check, even though I didnt get a chance to test, was spacing issue fix still implemented in 3.5 or since I wasn’t able to test, disregarded for the release?

Thanks

Chad

We made several changes. As mentioned we fixed QAT-1127 & 1122. QAT-1891 was not fixed as in this circumstance the file the user was linking to did not have a proper encoding byte mark.

So changes were made but I can’t guarentee your exact file is fixed. When you have time please open your file with notepad and save in the encoding of your choice. Then download and try TDP 3.5 which is now released.

Hey Debbie. I knew I’d find this thread we discussed a good while back. I’m actually using TDP 3.8 and am back to running into the spacing issue on the select to file automation. Again the query works fine with the spacing, but when running with automation, any additional spaces show on the verbose logging as little question marks surrounded by a diamond type character and of course the query fails when running from automation. I apologize I never got a chance to check this sooner as I’m now pushing more and more queries over to TDP. I do notice on your previous post that the issue was not fixed and it may have never been truly resolved. Just wanted to see if there was any plans to continue to address this issue? Thanks!! I’m attaching a sample SQL file just in case.
Camp_Pkg_Digital.sql (13.6 KB)

When I look at the issues that were entered for this there never were any changes. Igor said that the files had different encodings and that one did not have an ending byte. Did you do as Igor suggested which is to open in our editor and go to the Editor menu. Choose the encoding menu and set the encoding to ascii and then save. see if this makes a difference. If you open this file in Microsoft word it says the encoding is Turkish.

Debbie,

I did do this. I think it makes no difference whatsoever. I first get this message, then if I hit yes to use the encoding, it replaces the spacing with diamond looking characters as it was doing before. This still wont run in automation at all. I’m attaching the log as well. Thanks!

Ascii_Error.png
Ascii_Log.log (23.7 KB)

I think for me to really dig in and work on this I will need to reproduce the issue. I would need you to open a support ticket. I see that you are using iSeries also. I would like you to execute the SQL in a SQL Editor and then generate the Support Bundle. This should generate some DDL for me so I can use your query. I would also need your Automation script, log file and all dependent files. Describe the issue and tell them to send a subcase directly to me.

Martin, can you help with this one?

Hi Chad,

I was trying to reproduce this problem and here are my findings:

  1. I have opened your sql file in hex view so i could get the code of that whitespace character -- it is "U+00A0: No-Break Space" character

  2. Since i could not use your SQL code for execution I had to create my own simplified version: "select 1 from dual" using the Oracle provider ---- note there is the no-break-space character before the word "from"

  3. I have used my sql in automation export wizard using both from file and manually typing the code and when i hit Next i got error "Invalid character" so i could not to even run it...

  4. I have used my sql in automation Select to file using the manual type-in code textbox and browsed for file or using a link file and i got the same error - invalid character

  5. I believe that space characters are encoding-insensitive so i beleive the conversion must have happened in the original source already.
    I need to know:

  6. How was that sql code created? typed in Toad sql editor? Was it copied from other software (ctrl+c ctrl+v)?

  7. Did the code already have these characters in the original source?

  8. Can you identify the moment once the code gets converted using the no-break-space character?

  9. Please clarify exact steps how to reproduce the state where in Select to file activity it works but once using the link-file option it does not? Because at my side it doesn't work even if the option is disabled..

Martin, I appreciate the response.

  1. The SQL would have been originally created in an old software called Forest and Trees, then copied/ pasted into a notepad file and saved as .sql for use for this query in TDP.

  2. Yes the code would have already had the additional blank characters in the original source.

  3. When I open this .sql file up in the editor, I cannot identify from the code when the code gets converted to the no-break-space character.

  4. I’m not sure where I may have caused confusion, but the spacing issue doesn’t work for either the link to file or if it’s not linked and pasted directly in, or just disabled. I had mentioned before though that the query runs absolutely fine in the actual editor even with the additional spacing, just not the select to file activity.

Lastly, I really appreciate ya’lls help with this. I understand this a very minor issue. Since the submission of this, I’ve just gone in fixed the SQL used for these to ensure the extra spacing is removed. Therefore, although its probably something that could be addressed and feel free to do so, I understand its just as easy to go in and resolve the extra spacing from the .sql, which is cleaner anyways. Therefore, if ya’ll would like to pursue addressing the issue on a further release, I’m more than happy to continue gathering whatever you need. If not, I’m content with resolving the spacing issues in the .sql and moving forward.

I really just wanted to resubmit to provide Debbie and Team the heads up that the issue was not corrected on the newer releases.

Thanks again for your help with this!

Chad