SQLiteDatabase.TableColumns not returning the length

Databases
1

Hi All,

I have a basic SQLite DB and when I try to get the length of a column it returns 0 on all columns.
I am using the function mentioned above: SQLiteDatabase.TableColumns.
All the rest of the data comes through fine.
Using 2023R3.1, Windows 11, and SQLite 3.23.1.

Thanks.

3

Looks like a bug, Length is always Nil - just tested on macOS 14.1.1 with 2023r3.1.

You may want to open an Issue to report it…

Or does SQLite not support ColumnLength at all?

4

Length of what? The size of a column will vary from row to row, depending on the data that is stored there.

5

Documentation said:
Length: An integer that describes the length of the field (for some text fields), the precision of numeric data (for some numeric fields) or the default value for a field (for SQLite columns).

6

Hi Tim,

What @Emile_Schwarz said.
The method is TableColumns found here: SQLiteDatabase — Xojo documentation

I should have been clearer and stated the “metadata length of the column” or “the table defined length of the column”.

7

One of the tables, customer, has the following definition:

CREATE TABLE customer ( 
	cust_id              INTEGER NOT NULL  PRIMARY KEY  ,
	frst_nm              VARCHAR(75)     ,
	lst_nm               VARCHAR(75)     ,
	business_nm          VARCHAR(250)     
 )
;

(Note, this is just a test database, don’t take it too seriously.)

Despite the fact that SQLite data types are not strict I would still expect the metadata to return lengths of the character fields.
PRAGMA table_info(customer);
Does return the column lengths for the character fields.

0 cust_id INTEGER 1 1
1 frst_nm VARCHAR(75) 0 0
2 lst_nm VARCHAR(75) 0 0
3 business_nm VARCHAR(250) 0 0

So yeah, this is starting to feel like a bug to me.

8

You know, SQLite does not have such concept. Char(100), Char(1), Varchar(255) ends all as TEXT

Text holds from zero to thousands chars, it ignores the limits you impose in some DBMS, and that’s why probably it does not return such limits.

I guess it deserves just better documentation.

9

Yes. I love that I can have a file based database, but I am not a fan of the fact that it intentionally does not more strictly control the data types.
So based on what you are saying it is more of a documentation issue in Xojo and not a true bug?

10

Yes, because if it returned 10 as limit of a text column, your app should count on that and fail silently, like with you moving a 50 chars value into it expecting a truncation, but that will never happen.

They opted by 0, but it could be like 2GB the fixed limit returned.

1 Like
11

SQLite does not even record the varchar() numbers you give it. The notion of length for SQLite has little meaning, and most of that page does not apply to SQLite, whatever the page says.

Content                Occupies this numer of bytes
-------                ----------------------------

NULL                        0  
Integer                     0 to 8 depending on the value of the integer 
Real                        8 bytes (IEEE double)
Text                        0 to large, depending on text length
blob                        (same as Text)

All other types map to one of these, as can be discovered by reading the SQLite doc.

If you don’t like it, you could probably declare your tables as STRICT, you’d have to check the SQLITE doc for precise info. Me I don’t care; all this VARCHAR stuff seems like no more than a throwback to the 1960s anyway.

12

I get it. This is one of my nits with SQLite: if I give you DDL then do exactly what I tell you to do or fail.
I would rather SQLite not process DDL with sizes then. Throw an error and just make me put in the types and move on. I know why it does that: it is for some sort of half-hearted compliance with ANSI SQL so that you can have portable DDL.
Again, I understand the reason why and I am OK with it. I was just hoping that it would return the size since it does store the size as shown in the PRAGMA command in one of my previous posts.
Now that I know it is not a bug and I know what to expect I am fine with it.

13

It does store it in the PRAGMA, but AFAIK that is only parsed when the table is modified. And I dont see that the size (length) tells one very much, in the case of SQLite.

Not a bug, IMO, except as far as the doc is concerned.

14

Check the values in the debugger:
foo VARCHAR(75)

and others…

Or your recorded TABLEs (the ones stored in the sqlite file). So you will know what type really are your columns, etc.