|
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 |
|
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 |
|
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 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 '20, 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 '20, 11:52)
Gregory Durniak
|