Bug?: auto rtrim of space in SQL - VisualCron

Please note that VisualCron support is not actively monitoring this community forum. Please use our contact page for contacting the VisualCron support directly.

Welcome Guest! To enable all features please Login or Register.

Jon Tofte-Hansen
Paid support Topic Starter

2017-09-27T11:44:15Z

If I run this sql in an Oracle SQL task:

select 'a ',' b',' ',chr(13)||chr(10) from dual

The output is this (with " as text qualifier):

"a"," b","","
"

The trailing space is removed in cell 1 (and probably 3) while the leading space in cell 2 remains. CrLf is not removed either (cell 4).

Sponsor

Forum information

Wanna join the discussion?! Login to your Forum forum account or Register a new forum account

Support
Official support

2017-09-27T13:13:23Z

We do not alter the result in any way. We just present it as it comes so it might be something with Oracle, ODBC or whatever type of Connection you use.

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

Jon Tofte-Hansen
Paid support Topic Starter

2017-09-28T10:44:24Z

Hi Henrik

The server version is 8.2.9.

The connection details:

Storage type: Database
Database type: Oracle
Data provider: Native
Connection type: Standard
Data Source: <a tnsnames entry>
Unicode: true
Convert data types: unticked

When I spool the output of SQL*Plus (also using the Oracle client) the spaces are not removed.

Should I connect otherwise?

EDIT: removed erronious comment on Data provider

Edited by user 2017-09-28T11:47:47Z | Reason: Not specified

Support
Official support

2017-09-28T11:28:13Z

Originally Posted by: Jon Tofte-Hansen

Hi Henrik

The server version is 8.2.9.

The connection details:

Storage type: Database
Database type: Oracle
Data provider: Native (uses the locally installed Oracle client)
Connection type: Standard
Data Source: <a tnsnames entry>
Unicode: true
Convert data types: unticked

When I spool the output of SQL*Plus (also using the Oracle client) the spaces are not removed.

Should I connect otherwise?

For testing, please use our Native client instead.

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

Jon Tofte-Hansen
Paid support Topic Starter

2017-09-28T11:46:46Z

I already use Native (and it uses the local tnsnames.ora).

Support
Official support

2017-09-28T11:48:19Z

Originally Posted by: Jon Tofte-Hansen

I already use Native (and it uses the local tnsnames.ora).

It is not the same as the one specified. Please create a new with the one I specified.

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

Jon Tofte-Hansen
Paid support Topic Starter

2017-09-28T12:18:04Z

Ok - still on VC 8.2.9:
1: I created a new SQL connection using the Conneciton Wizard (Use connection guide)
2: Storage type: Database, Database type: Oracle, Data provider Native
3: Connection type: Standard, Data Source: IP/SID (ie. without using tnsnames.ora)
4: Created a new job with a new SQL task (and a write to file task) with this SQL (deliberately swapping the space position):

select ' a','b ',' ' from dual

The output (also when writing to a file) is unfortunately still removing trailing spaces (in Output, Field separator is Comma and Text qualifier is "):

" a","b",""

The database version: 11.2.0.4.0 (same result against 9.2.0.8.0).

Edited by user 2017-09-28T12:29:10Z | Reason: Not specified

Support
Official support

2017-09-28T12:32:20Z

Unfortunately this seems to be something from Oracle. Not sure if you get the same from ODBC or OLEDB but it is clear that VC by itself does not alter the output.

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

Jon Tofte-Hansen
Paid support Topic Starter

2017-09-28T12:58:54Z

Hi Henrik

Thanks for the answer.

I admit that this particular case might not be a big problem, but I find it hard to believe that an Oracle produced driver would modify the output.

Support
Official support

2017-09-28T13:01:25Z

#10

Originally Posted by: Jon Tofte-Hansen

Hi Henrik

Thanks for the answer.

I admit that this particular case might not be a big problem, but I find it hard to believe that an Oracle produced driver would modify the output.

What you have a table with data date contains a blank space?

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

Jon Tofte-Hansen
Paid support Topic Starter

2017-09-28T13:16:02Z

#11

I discovered this because I moved a Java-program (that produced a list with a JDBC driver) to VisualCron. In the VC output some fields now were empty and not with one or more spaces.

The receiving program can handle this, so I don't have an acute problem, but I still think the output should be exact. Thank you for looking into this.

Support
Official support

2017-09-28T13:17:59Z

#12

Originally Posted by: Jon Tofte-Hansen

I discovered this because I moved a Java-program (that produced a list with a JDBC driver) to VisualCron. In the VC output some fields now were empty and not with one or more spaces.

The receiving program can handle this, so I don't have an acute problem, but I still think the output should be exact. Thank you for looking into this.

We are open to investigate further but would like to know the query above. If you have a column with content "a " for example (ending blank space) if that is trimmed or not. This way we do not just test incoming data but outgoing data only. Helps to find the culprit.

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

Jon Tofte-Hansen
Paid support Topic Starter

2017-09-28T13:34:21Z

#13

Issued:

CREATE TABLE testtab
AS
    SELECT ' a' c1, 'b ' c2, ' ' c3, '        ' c4 FROM DUAL;

Selecting in the db tool TOAD (using Oracle client) the spaces are in the output.

VC (or the Native driver) still removes the trailing spaces in the output :

" a","b","",""

Support
Official support

2017-09-28T13:49:12Z

#14

Interesting. But space is there in select if you insert "a a"?

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

Jon Tofte-Hansen
Paid support Topic Starter

2017-09-28T13:55:26Z

#15

Only trailing spaces are removed ie. spaces to the right. If I eg. put an "X" at the end of the 8 spaces in c4 above the ouput is 8 spaces and an X. So obviously "a a" will be correctly outputted - otherwise the SQL task would be useless.

You can not reproduce this?

Edited by user 2017-09-28T13:56:50Z | Reason: Spaces were trimmed!

Support
Official support

2017-09-28T14:05:21Z

#16

Originally Posted by: Jon Tofte-Hansen

Only trailing spaces are removed ie. spaces to the right. If I eg. put an "X" at the end of the 8 spaces in c4 above the ouput is 8 spaces and an X. So obviously "a a" will be correctly outputted - otherwise the SQL task would be useless.

You can not reproduce this?

I am just double checking everything.

Here is the result when using SQL Server:

"a ","23"
"a a","55"
" b","33"

So everything works there so it is not a general problem. We have to add some logging for Oracle to detect where it goes wrong. I do not have access to any Oracle server at the moment.

Edited by user 2017-09-28T14:06:05Z | Reason: Not specified

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

Jon Tofte-Hansen
Paid support Topic Starter

2017-09-28T14:21:44Z

#17

You can install Oracle Database 11g Express Edition to test the SQL task (on a real database 😄 ).

Support
Official support

2017-09-28T16:55:32Z

#18

Originally Posted by: Jon Tofte-Hansen

You can install Oracle Database 11g Express Edition to test the SQL task (on a real database 😄 ).

Unfortunately there was problems with installation and permissions. Very different from other databases. Strange thing is that we have a lot of users running Oracle. We will add some debugging for a new build for you.

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

Support
Official support

2017-09-28T16:59:15Z

#19

Can you verify that when you create Oracle->Native you then select "Direct mode" in the Connection string.

Henrik
Support
http://www.visualcron.com
Please like VisualCron on facebook!

Jon Tofte-Hansen
Paid support Topic Starter

2017-09-29T08:39:43Z

#20

As stated above I used Standard and not Standard 2 - direct mode (no Oracle Client) to connect.

I have created a new connection with the second type:
Provider=Oracle;data source=<db host IP>;user=<db user>;password=<pw>;direct=true;Port=1521;SID=;Service Name=<db service name>;

But the result is the same - both a select from DUAL and from a real table: trailing spaces are trimmed.

Edited by user 2017-09-29T09:14:30Z | Reason: Not specified