The forum will be down for maintenance at some point between Friday, September 25, 2020 at 5pm PDT and Sunday, September 27, 2020 at 11:59 PDT. Downtime is unknown but will be minimized.

We use Batch Files to load data using dbisql.

The database in question has Option string_rtruncation = 'Off'.

Unknown to us, a 1 Character input was changed to 2 Characters, yet dbisql did not throw an error. It simply truncated the values.

This has been going on for 3 months ! How is this possible ?

I understand the string_rtruncation option by Default is now ON, but this one was OFF.

I thought SQL was more strict than this !

Comments appreciated e.g. any way to prevent silent truncation ?

greg

asked 15 Aug, 14:25

Gregory%20Durniak's gravatar image

Gregory Durniak
1397917
accept rate: 0%

edited 16 Aug, 12:27


Here's the real surprise: It is behaving as expected.

-- C:\\TEMP\\t.txt
1,'x'
2,'yz'

CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data VARCHAR ( 1 ) NOT NULL );
SET OPTION PUBLIC.string_rtruncation = 'Off';

LOAD TABLE t FROM 'C:\\TEMP\\t.txt';

SELECT * FROM t;

pkey,data
1,'x'
2,'y'

TRUNCATE TABLE t;
SET TEMPORARY OPTION string_rtruncation = 'On';
LOAD TABLE t FROM 'C:\\TEMP\\t.txt';

Could not execute statement.
Right truncation of string data
SQLCODE=-638, ODBC 3 State="22001"
Line 3, column 1
LOAD TABLE t FROM 'C:\\TEMP\\t.txt'

I've been working with SQL Anywhere since forever, and every single time the subject of "string_rtruncation" comes up I have to look it up in the Help:

When this option is set to Off, the exception is not raised and the character string is silently truncated.

Yes, the way it works is wrong, but it's been that way forever.

Way back in Version 5 through 9, even the default was wrong; you had to set it to On in order to catch string right truncation errors.

Here's a piece of legacy code from inside Foxhound which was first developed using Version 9:

SET OPTION PUBLIC.STRING_RTRUNCATION = 'ON'; -- raise error when non-blank characters are truncated on INSERT or UPDATE

Nowadays (Version 10 and later) the default is On, which is what you should leave it at

permanent link

answered 15 Aug, 16:12

Breck%20Carter's gravatar image

Breck Carter
30.8k496680992
accept rate: 20%

edited 15 Aug, 16:26

1

The situation clearly calls for a meme :)...

(15 Aug, 16:38) Breck Carter

Thanks for the prompt reply !

I just set the Database "Option" string_rtruncation to ON ( Permanent ), using Sybase Central

Then ran a Batch file to INSERT, e.g.

cd \

cd C:\Program Files\SQL Anywhere 17\Bin64\

dbisql -c "UID=dba;PWD=sql;Host=PROD01;ServerName=SM16;DBN=SM" input into "DBA"."t" from '\\192.168.1.5\Desktop\data.csv' format text escapes on escape character '\' delimited by ',' encoding 'Cp1252'

pause

It does NOT throw an error, unless I add: SET TEMPORARY OPTION string_rtruncation = 'On';

It's as if the Batch File Connection ignores the Database option. Is this possible ?

greg

permanent link

answered 15 Aug, 17:45

Gregory%20Durniak's gravatar image

Gregory Durniak
1397917
accept rate: 0%

edited 16 Aug, 01:46

IMO the SET OPTION statement is way more complex that it should be:

With most options, you can set their value at three levels of scope: public, user, and connection.
   SET [ EXISTING ] [ TEMPORARY ] OPTION [ userid.| PUBLIC.]option-name = [ option-value ]

In your case, it is possible someone set the value for the DBA user id.

SET OPTION PUBLIC.string_rtruncation    = 'On';
SET OPTION DBA.string_rtruncation       = 'Off';
SET TEMPORARY OPTION string_rtruncation = 'On';

SELECT * FROM SYSOPTIONS WHERE "option" = 'string_rtruncation';
SELECT CONNECTION_PROPERTY ( 'string_rtruncation' );

user_name,option,setting
'PUBLIC','string_rtruncation',On
'dba','string_rtruncation',Off

CONNECTION_PROPERTY('string_rtruncation')
'On'

Life is much easier when you DO NOT set permanent option values at the user id level. Stick with SET OPTION PUBLIC (everyone's defaults) and SET TEMPORARY OPTION (special exceptions).

(16 Aug, 09:03) Breck Carter

So, this is a bit complicated

I see I now have the PUBLIC Option ON, and DBA OFF ( as edited with Sybase Central )

Since this database was created in ASA 9, both were always OFF ( the Default at that time ), and apparently stayed OFF when upgraded to 17

We did not realize this allowed silent truncation

Data format changes are few, so we got away with it, until now.

We will probably leave it OFF, to avoid interruptions, and just be more careful with format changes

Thanks again !

greg

(16 Aug, 11:52) Gregory Durniak
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18

question asked: 15 Aug, 14:25

question was seen: 121 times

last updated: 16 Aug, 12:27