Re: [sqlite] field length retreval

[Simon Slavin]

On 18 Apr 2014, at 7:29pm, Donald Griggs  wrote:

> I tried a simple test with recent [Postgres] (9.3.4) version (using default 
> settings,
> if that matters) and verified that an insert with oversized string will
> fail to insert, unless the overage characters are spaces only, in which
> case it succeeds with truncation.

Made even more complicated because MySQL has a 'strict' mode where this will 
fail, but also another mode where it will issue a warning message, but a 
truncated copy of the string will be stored.

I had seen the truncating behaviour so many times I assumed it must be in the 
Standard.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Donald Griggs]

I know very little about Postgres, but I see this interesting page in their
documentation:

http://www.postgresql.org/docs/8.0/interactive/datatype-character.html

Excerpt:

*An attempt to store a longer string into a column of these types will
result in an error, unless the excess characters are all spaces, in which
case the string will be truncated to the maximum length. (This somewhat
bizarre exception is required by the SQL standard.) If the string to be
stored is shorter than the declared length, values of type character will
be space-padded; values of type character varying will simply store the
shorter string.*

*If one explicitly casts a value to character varying(n) or character(n),
then an over-length value will be truncated to n characters without raising
an error. (This too is required by the SQL standard.)*

*Note: Prior to PostgreSQL 7.2, strings that were too long were always
truncated without raising an error, in either explicit or implicit casting
contexts.*



I tried a simple test with recent (9.3.4) version (using default settings,
if that matters) and verified that an insert with oversized string will
fail to insert, unless the overage characters are spaces only, in which
case it succeeds with truncation.   I did NOT experiment with number
conversions, nor with a version prior to 7.2, nor did I attempt to review
the sql standards.

-
drop table if exists j2;
create table j2( a2 varchar(2), b3 varchar(3));
insert into j2 values ('a', 'b');
-- following will fail with error
insert into j2 values ('aaa', 'bbb');

ERROR:  value too long for type character varying(2)
** Error **

-- but following insert will succeed with truncated column b3
insert into j2 values ('aa', 'bbb');
select *, length(b3) from j2;

Output was:
"a";"b";1
"aa";"bbb";3

Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Luuk]

On 18-4-2014 18:56, Simon Slavin wrote:


On 18 Apr 2014, at 5:28pm, Dominique Devienne  wrote:


I'm not sure where you get that declaring a column as varchar()
implicitly truncate


While I can't find any reference one way or another in a SQL standard, all 
implementations I've seen that understand VARCHAR(n) truncate for any column 
defined with a number inside the brackets:



"When character expressions are converted to a character data type of a different 
size, values that are too long for the new data type are truncated."



"If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR 
column that exceeds the column's maximum length, the value is truncated to fit and a 
warning is generated."

('warnings' in MySQL are not an indication of failure and are routinely 
ignored.)

SQLite is unusual in that it ignores the number in the brackets.  I don't know 
what various implementations do for just VARCHAR or for VARCHAR() with no 
number inside the brackets.

Simon.


1> select @@version
2> go


--
--
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1  (Build 7601: 
Service Pack 1)



(1 rows affected)
1> create table test (a varchar(4));
2> go
1> insert into test values('abcd');
2> insert into test values('ABCDE');
3> go

(1 rows affected)
Msg 8152, Level 16, State 14, Server ACER\MSSQL, Line 2
String or binary data would be truncated.
The statement has been terminated.
1> select * from test;
2> go
a

abcd

(1 rows affected)
1>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Simon Slavin]

On 18 Apr 2014, at 5:28pm, Dominique Devienne  wrote:

> I'm not sure where you get that declaring a column as varchar()
> implicitly truncate

While I can't find any reference one way or another in a SQL standard, all 
implementations I've seen that understand VARCHAR(n) truncate for any column 
defined with a number inside the brackets:



"When character expressions are converted to a character data type of a 
different size, values that are too long for the new data type are truncated. "



"If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR 
column that exceeds the column's maximum length, the value is truncated to fit 
and a warning is generated."

('warnings' in MySQL are not an indication of failure and are routinely 
ignored.)

SQLite is unusual in that it ignores the number in the brackets.  I don't know 
what various implementations do for just VARCHAR or for VARCHAR() with no 
number inside the brackets.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Dominique Devienne]

On Fri, Apr 18, 2014 at 4:42 PM, Simon Slavin  wrote:
> On 18 Apr 2014, at 3:21pm, Dominique Devienne  wrote:
>> No need to resort to triggers. A simple check constraint will do
>
> A constraint can prevent you from putting too-long values in the field.  A 
> trigger can truncate the value to the correct length, which is what declaring 
> a column as VARCHAR() would do.

I'm not sure where you get that declaring a column as varchar()
implicitly truncate, but at least Oracle's VARCHAR2 behaves just like
the check constraint I demonstrated in SQLite earlier. --DD

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t (name varchar2(2));

Table created.

SQL> insert into t values ('a');

1 row created.

SQL> insert into t values ('aa');

1 row created.

SQL> insert into t values ('aaa');
insert into t values ('aaa')
  *
ERROR at line 1:
ORA-12899: value too large for column "xxx"."T"."NAME" (actual: 3, maximum: 2)


SQL>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Simon Slavin]

On 18 Apr 2014, at 3:21pm, Dominique Devienne  wrote:

> On Fri, Apr 18, 2014 at 12:53 AM, Simon Slavin  wrote:
>> There are ways to enforce field length limits entirely within SQLite but 
>> they're complicated so post again if you want me to explain TRIGGERs.
> 
> No need to resort to triggers. A simple check constraint will do

A constraint can prevent you from putting too-long values in the field.  A 
trigger can truncate the value to the correct length, which is what declaring a 
column as VARCHAR() would do.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Dominique Devienne]

On Fri, Apr 18, 2014 at 12:53 AM, Simon Slavin  wrote:
> On 17 Apr 2014, at 11:24pm, David Clark  wrote:
>> If I have a table of
>> field1 varchar(25)
>> field2 varchar(50)
>> field3 varchar(75)
>
> You don't.  SQLite does not support a datatype of varchar().  Fields you 
> declare like that will be implemented as TEXT fields and handled the same as 
> any other TEXT field, and the length of the contents ignored.
>
>> I know sqlite does not enforce limits, but in my program it would be useful 
>> if I could find the declared lengths of
>> 25, 50 and 75 in this case.  How might I do that in sqlite?
>
> Technically you could retrieve and parse the CREATE command used to make the 
> TABLE and figure them out.  But since they aren't going to be enforced 
> there's little point.
>
> There are ways to enforce field length limits entirely within SQLite but 
> they're complicated so post again if you want me to explain TRIGGERs.

No need to resort to triggers. A simple check constraint will do:

C:\Users\DDevienne>sqlite3
SQLite version 3.8.3.1 2014-02-11 14:52:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t (a varchar(2), b varchar(2) check (length(b) < 3));
sqlite> insert into t (a) values ('a'), ('aa'), ('aaa');
sqlite> select * from t;
a|
aa|
aaa|
sqlite> insert into t (b) values ('b'), ('bb'), ('bbb');
Error: CHECK constraint failed: t
sqlite> select * from t;
a|
aa|
aaa|
sqlite> insert into t (b) values ('b'), ('bb');
sqlite> select * from t;
a|
aa|
aaa|
|b
|bb
sqlite>

You still have to explicitly hard-code the max-length to check against though.

Perhaps you could create a function that takes 3 params, table-name,
column-name (or index), and column value, and use
sqlite3_column_decltype as Igor mentioned, to generate a simple select
column-name from table-name and introspect the varchar(x) declared
type to infer the max length (caching the result with aux-data?), but
that seems like too much work for little gain to me. --DD

PS: I didn't check whether length() returns the byte-length or the
char-length (unicode codepoint length), which matters if you go beyond
ASCII and care about Oracle-like VARCHAR2(64 CHAR) vs VARCHAR2(64).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Simon Slavin]

On 17 Apr 2014, at 11:24pm, David Clark  wrote:

> If I have a table of 
> field1 varchar(25)
> field2 varchar(50)
> field3 varchar(75)

You don't.  SQLite does not support a datatype of varchar().  Fields you 
declare like that will be implemented as TEXT fields and handled the same as 
any other TEXT field, and the length of the contents ignored.

> I know sqlite does not enforce limits, but in my program it would be useful 
> if I could find the declared lengths of
> 25, 50 and 75 in this case.  How might I do that in sqlite?

Technically you could retrieve and parse the CREATE command used to make the 
TABLE and figure them out.  But since they aren't going to be enforced there's 
little point.

There are ways to enforce field length limits entirely within SQLite but 
they're complicated so post again if you want me to explain TRIGGERs.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] field length retreval

[Igor Tandetnik]

On 4/17/2014 6:24 PM, David Clark wrote:

If I have a table of
field1 varchar(25)
field2 varchar(50)
field3 varchar(75)

I know sqlite does not enforce limits, but in my program it would be useful if 
I could find the declared lengths of
25, 50 and 75 in this case.  How might I do that in sqlite?


Prepare a SELECT statement selecting these columns (you don't need to 
actually run it), and call sqlite3_column_decltype[16] for each column. 
That gives you raw text from the original CREATE TABLE statement. You 
can use "select * from mytable;" to do that for all columns (complete 
with sqlite3_column_name[16]).

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] field length retreval

[David Clark]

If I have a table of 
field1 varchar(25)
field2 varchar(50)
field3 varchar(75)

I know sqlite does not enforce limits, but in my program it would be useful if 
I could find the declared lengths of
25, 50 and 75 in this case.  How might I do that in sqlite?

Thank you,
David Clark
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users