SQLite Forum

Type of the column
Login

Type of the column

(1) By oneeyeman on 2021-03-08 06:45:39 [link] [source]

Hi, ALL,

From the documentation:

[quote] The sqlite3_column_type() routine returns the datatype code for the initial data type of the result column. The returned value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. The return value of sqlite3_column_type() can be used to decide which of the first six interface should be used to extract the column value. The value returned by sqlite3_column_type() is only meaningful if no automatic type conversions have occurred for the value in question. After a type conversion, the result of calling sqlite3_column_type() is undefined, though harmless. Future versions of SQLite may change the behavior of sqlite3_column_type() following a type conversion. [/quote]

However, there are 2 types/functions there: sqlite3_column_int() and sqlite3_column_int64().

Do I understand correctly that the second one exist for 64-bit builds? If not - shouldn't there be SQLITE_INTEGER and SQLITE_INTEGER64?

Or is there a better way to distinguish? Or I should be using _int4() all the time?

Please advice.

Thank you.

(2) By Ryan Smith (cuz) on 2021-03-08 08:36:53 in reply to 1 [link] [source]

Do I understand correctly that the second one exist for 64-bit builds? If not - shouldn't there be SQLITE_INTEGER and SQLITE_INTEGER64?

Or is there a better way to distinguish? Or I should be using _int4() all the time?

I will assume _int4() is a typo and you meant _int64() there.

The answer is No, it has nothing to do with whether you are running a 64-bit or 32-bit application. It has strictly to do with the size of the target variable/memory you want the value of the column to be written to.

If you expect the values to always be smaller than 4-billion, then a 32-bit integer target will do just fine. (These happens to be smaller space-wise, more space-efficient to transfer in say an internet stream, and also used to be faster to do calculations with on older 32-bit systems, hence still prevailing through esp. legacy code.)

If however you expect values bigger than that, you need to provide a memory-box that is big enough to hold them, regardless of CPU/Application bitness.

16-bit, 32-bit and 64-bit are just the comfortable options we have that covers most use cases for nicely packaged byte-sizes and so are supplied freely in every compiler system and also why CPUs over the years chose to do their direct interactions at these sizes. Today they are all 64-bit and so far we haven't seen 128-bit being needed a lot, but maybe one day it will.

Some applications need values even bigger, like astronomy systems using perhaps 80-bit integer variables. These applications have to provide their own 80-bit variable spaces and perhaps their own code for doing calculation with them. The point here is that the memory/variable sizes holding the data is dependent on the use case of the data, NOT the bitness of the application, underlying CPU, or Operating system, etc.

You want to carry around a variable that is yay-big, you need to provide a box that is yay-big, and if that happens to be a 32-bit or 64-bit box, then SQLite can hand it off to you natively using the said functions, else you would perhaps need to read it as a string or Blob and convert it yourself.

Use what you need - but beware, if a value bigger than 4 billion is stored in the DB and you try read it with the _int32() function, the result might be surprising, so make sure you know the target use case and impose the limits as needed in your application.

Please advice.

You have been adviced. :)
Good luck!

(3) By oneeyeman on 2021-03-09 00:57:29 in reply to 2 [link] [source]

> I will assume _int4() is a typo and you meant _int64() there.

Yes sorry. My keyboard needs serious cleaning. ;-)

> The answer is No, it has nothing to do with whether you are running a 64-bit or 32-bit application. It has strictly to do with the size of the target variable/memory you want the value of the column to be written to.

OK.

> If you expect the values to always be smaller than 4-billion, then a 32-bit integer target will do just fine. (These happens to be smaller space-wise, more space-efficient to transfer in say an internet stream, and also used to be faster to do calculations with on older 32-bit systems, hence still prevailing through esp. legacy code.)

But then why not have SQLITE_INTEGER64 to indicate that the value is 64 bit integer?

> Some applications need values even bigger, like astronomy systems using perhaps 
> 80-bit integer variables. These applications have to provide their own 80-bit 
> variable spaces and perhaps their own code for doing calculation with them. The 
> point here is that the memory/variable sizes holding the data is dependent on 
> the use case of the data, NOT the bitness of the application, underlying CPU, or 
> Operating system, etc.

Does it still comes as SQLITE_INTEGER in this case?

Thank you.

(4) By Larry Brasfield (larrybr) on 2021-03-09 01:40:39 in reply to 3 [source]

I recommend Datatypes in SQLite Version 3 for your study. As you will find, among other facts, SQLite does store integers, tracking that they are integers. But it does not classify them by traditional, power-of-2 sizes, nor does it require you to do so. It uses the storage they need [a] as determined by how large the integer is, up to some value limit such as -2^63 to +2^63-1.

[a. The "need" is by how they fit in 1, 2, 3, 4, 6, or 8 bytes of storage. ]

(5) By Ryan Smith (cuz) on 2021-03-09 12:49:10 in reply to 3 [link] [source]

But then why not have SQLITE_INTEGER64 to indicate that the value is 64 bit integer?

I feel like the entire idea I tried to explain must have not been understood, which points to a serious failing in explanatory power from me.

I will try one more time, hoping I do a better job this time, but first, it is a real good idea to heed Larry's advice and read up on what he suggested, which should also clarify some of what I am about to say.

TLDR: SQLite Storage is Typeless;

The data in the database isn't regarded (by the database itself) as having any significance. It doesn't know that a value is perhaps someone's Age, or the name of a Song, it just accepts what you put in there and gives it back when needed.

You might think "Yes - that's obvious, what a silly example", and you would be right, it is both silly and obvious but it also is exactly the same principle that underlies ALL storage in SQLite. SQLite not only doesn't know that a value is someone's age, it doesn't even know that the value is specifically an Integer, or Int64, or string or any of that, to the database engine, it is just a bunch of bytes. (I'll steal Keith's favorite term here and call it a "bag 'o bytes" from now on).

Try it yourself, make a Table with a column of type "NUMERIC", then INSERT into it a value but bind that with sqlite3_bind_int() (or int64 or FLoat if you like) then read it back out in a select query and use the sqlite3_column_text() - it all works because SQLite doesn't know and doesn't care what is in your bag 'o bytes when it stores it, though it may treat the bytes differently based on how you bind it.

How it works

It's like when you have some "stuff" you want to put into storage and you come to the storage facility and say, "Hey people, can you please store my stuff? I'll pay".
Being a nice capitalist storage facility, they might say "Yes sure" and then, "Ok, so for us to store it, you need to bring it in a nice storage container."
You might then say "But what kind of container?".
They may answer: "Well it's your choice really, but we do offer these 2feet x 1feet boxes, or if that is too small, we have the bigger 4ft x 8ft containers, or if you need bigger, we also have full size shipping containers.

You might then say "Oh good, my "stuff" is sort of small sized, so I will bring it to store in the 2x1ft container ok?"
The facility then says "Perfect, we will put it away."
Internally though, they cheat to save money and are not using the full containers, but they pack the stuff out of the container and pack it together as tightly as possible with whatever other stuff is there already from other people, simply marking clearly where your stuff begins and ends.

When you go back later and say "Please can I have my stuff?" They say "sure", because they know exactly where it is, but now they forgot what container it was brought in with, so they quickly make up a story and say "You know, as an added service we offer repacking stuff into whatever container you like, at no extra charge".
You, being impressed with this service, then says "Excellent, I will take it in the 4x8ft container."
They promptly stuff your stuff into that size container and hand it back to you, and everyone is happy.

Please note:

  • At no point in this story did the container size matter very much to either You or the storage facility, it was just whatever was convenient to transfer the stuff in, though the facility did offer a few specific options they have as a standard to transfer "stuff" in.
  • The facility never knew or cared what "stuff" you had, or even what "Type" of "stuff" it was.

Now back to SQLite:

When you hand off "stuff" to SQLite it doesn't care what it is, to the DB engine it is just a bag of stuff, or specifically because we are computing, a "bag 'o bytes". It may be handled differently in queries based on the column's "Affinity", but that has nothing to do with the specific stored value.

It offers some containers to transfer your bag 'o bytes into the engine with, namely the sqlite3_bind...() interfaces which can handle bytes as Integers, Int64's, Text, IEEE754 Floats, etc, and even a completely untyped container that can transfer bags 'o bytes directly (binary large objects, or BLOBs for short).

Similarly, upon retrieval, it will offer several ways to transfer the bytes back to you. The sqlite_column...() suite of functions will give your bytes back as Ints, int64s, Text, etc. whatever is needed by YOU, so you decide the method.

Now to refer back to your question:

But then why not have SQLITE_INTEGER64 to indicate that the value is 64 bit integer?

Hopefully now you can see that this question is moot. SQLite doesn't know anything about what "type" your data is, and cannot "indicate" it to you in any way, YOU have to know what type it is, and more importantly, what Type of container you need it to be delivered in. SQLite will deliver your bytes in whatever way you ask for it.

It's just bytes.

I hope it makes more sense this time. :)

(6) By oneeyeman on 2021-03-09 15:05:22 in reply to 5 [link] [source]

Hi, Ryan, I understand that SQLite is more like Perl/Python whereas MS SQL Server/PostgreSQL/mySQL/Sybase/Oracle are more like C/C++/Pascal.

I was acrually hoping to write something like:

[code] if( ( res = sqlite3_prepare_v2( m_db, sqlite_pimpl->m_myconv.to_bytes( query.c_str() ).c_str(), -1, &m_stmt, NULL ) ) != 0 ) { GetErrorMessage( res, errorMessage ); errorMsg.push_back( errorMessage ); } else { while( true ) { res = sqlite3_step( m_stmt ); if( res == SQLITE_ROW ) { std::vector<DataEditFiield> row; int count = sqlite3_column_count( m_stmt ); for( int i = 0; i < count; ++i ) { res = sqlite3_column_type( m_stmt, i ); switch( res ) { case SQLITE_INTEGER: row.push_back( DataEditFiield( sqlite3_column_int64( m_stmt, i ) ) ); break; case SQLITE_FLOAT: row.push_back( DataEditFiield( sqlite3_column_double( m_stmt, i ) ) ); break; case SQLITE_TEXT: row.push_back( DataEditFiield( sqlite_pimpl->m_myconv.from_bytes( reinterpret_cast<const char *>( sqlite3_column_text( m_stmt, i ) ) ) ); break; } } } } } [/code]

in order to kind of emulate all those "big DBMSes" and use it to further processing.

Am I wrong? Will it work like this?

Thank you.

(7) By Scott Robison (casaderobison) on 2021-03-09 15:51:37 in reply to 3 [link] [source]

Other answers are excellent but maybe this will be helpful to you (or someone).

SQLITE_INTEGER doesn't tell you anything about the size of the integer. Only that it is integral. It could be stored in one of several ways, from 8- up to 64-bit (though not all variants are used internally; there is no such thing as a 10-bit integer within the SQLite storage model, for example).

If you truly don't know in advance what size of data you expect to get back from the database, you must assume it is the full 64-bit and use the int64 API.

If you want to know "in advance" what size of an integer you will need, you'll need something like the following pseudo-code:

#define EXTENDED_INT8    -1
#define EXTENDED_INT16   -2
#define EXTENDED_INT32   -3
#define EXTENDED_INT64   -4

#define EXTENDED_BETWEEN(v, l, h) ((l <= v) && (v <= h))

int extended_column_type(sqlite3_stmt* pStmt, int iCol)
{
  int type = sqlite3_column_type(pStmt, iCol);
  if (type == SQLITE_INTEGER)
  {
    sqlite3_int64 value = sqlite3_column_int64(pStmt, iCol);
    if (EXTENDED_BETWEEN(value, -128, 127))
      type = EXTENDED_INT8;
    else if (EXTENDED_BETWEEN(value, -32768, 32767))
      type = EXTENDED_INT16;
    else if (EXTENDED_BETWEEN(value, -2147483648, 2147483647))
      type = EXTENDED_INT32;
    else
      type = EXTENDED_INT64;
  }
  return type;
}

I've not tried to compile that so it might not be perfect, and there are other ways to do it which might be "better" based on some criteria, but I hope it is illustrative of how you can build your own type checker that provides finer granularity than what SQLite offers by default.

(8) By Ryan Smith (cuz) on 2021-03-09 16:05:57 in reply to 6 [link] [source]

Sure, that'll work, but that wasn't your question.

Also note that from, say a View, or any of a vast number of Query idiosyncrasies, a column that you would "expect" to be, say Integer, might have a value that is Text - but at least in that case SQLite will correctly return SQLITE_TEXT for that column type, but that says very little about what the original data/table/column type was, and more about what that value can be regarded as in its current form.

ALSO note - this may change from row to row. On one row column 3 might be an integer, on the next row it might be something else.

So given those givens, sure your proposed code will work (and I would even say is how it was intended to work) but it still makes the question about it being Int64 or Int32 moot. If it says INTEGER, and you do not know or do not control the range of values that go in there, best use a big enough box - i.e. Int64.

(9) By oneeyeman on 2021-03-09 16:24:09 in reply to 8 [link] [source]

Hi, Ryan, Thank you for confirming that it will work as intended, even though you don't say what the intention is. ;-)

However, my thought was to write smth like:

case SQLITE_INTEGER: sqlite_column_int(....); break; case SQLITE_INTEGER64: sqlite3_column_int64(...); break;

because as you wrote originally, sqlite3_column_int() appears more efficient:

[quote] (These happens to be smaller space-wise, more space-efficient to transfer in say an internet stream, and also used to be faster to do calculations with on older 32-bit systems, hence still prevailing through esp. legacy code. [/quote]

Hopefully now you see the reason for my original question - I just wanted to make sure I'm not missing anything.

TY for sticking with me and sorry for poor explanation in the OP.

BTW, is there a reason code is not preformatted on this forum? Either with usual [code[[/code] tags, "{{{ }}}" or simply SO way with backticks?

BTW2, is there a simple way to quote you in my reply?

(10) By Ryan Smith (cuz) on 2021-03-09 16:57:17 in reply to 9 [link] [source]

  1. SQLite will not inform you about the value ranges, only about the base type.

  2. Scott provided a great way for you to do exactly what you want (if efficiency of transfer is your real goal as understood from your last post).

  3. The forum uses "Markdown" formatting (if you select it as such above the edit box). Google "markdown" for some more info on that, but here are the basics:

To include a quote, simply precede it with the greater-than sign: > Again, it has to be the first character on a new-new-line, so if I type: > this is a quote, but on a new-new line, it looks like this:

this is a quote

Code can be formatted by (among others) enclosing in three back-ticks or tilde signs after a new-line which itself follows a new-line (let's call it a new-new-line), so if I start a piece of text on a new line after a new-new-line by ``` or ~~~ , any text that follows will be formatted as mono-space code text until the next such 3 characters are found. For example, the next section is code:

   SELECT 1, 2, 3
     FROM myTable
    WHERE id = 5
   ;

And now back to normal.

Happy learning!

(11) By Keith Medcalf (kmedcalf) on 2021-03-09 17:57:03 in reply to 9 [link] [source]

However, my thought was to write smth like:

case SQLITE_INTEGER: sqlite_column_int(....); break; case SQLITE_INTEGER64: sqlite3_column_int64(...); break;

because as you wrote originally, sqlite3_column_int() appears more efficient:

Actually, it is not. All "integers" in SQLite3 are 64-bit signed integers. If the reported datatype is INTEGER then requesting the data be returned in any format OTHER THAN a 64-bit integer requires a data conversion of some sort of another, even if that is only the CPU "truncating" the upper 32-bits.

(12) By oneeyeman on 2021-03-09 18:12:12 in reply to 11 [link] [source]

Hi, Keith,

So, even if I know that the value is and will be 1 or 0 (like boolean in C/C++), it will still better to use sqlite3_column_int64(), right? At least that's how I read your reply...

Keep in mind that 1 and 0 above are used as an example.

Thank you.

(13) By Keith Medcalf (kmedcalf) on 2021-03-09 18:53:27 in reply to 12 [link] [source]

I would say yes with the only caveat being if you are running on a physical machine that does not have a 64-bit ALU. If the inherent ALU architecture is only 32-bit, for example, then retrieving a 32-bit integer and dealing only with 32-bit integers will be more efficient. Similarly, if you are running on a 16-bit, 8-bit or 4-bit machine, then you probably want to truncate the value to the inherent ALU bit size as soon as possible.

There is, of course, a trade off in memory usage by using longer storage spaces than actually required -- but from the perspective of the CPU and the appurtenant pieces (RAM chips, controllers, etc) the "squishing" of the native hardware size into a smaller storage size is more costly that not doing that.

NB: ALU is the Arithmetic Logic Unit, which is the hardware sub-unit of the CPU that actually performs arithmetic and logic operations.

Note that this is hardware dependent. There was a time when operating a CPU in 32-bit was more efficient than 64-bit because, even though the CPU was capable of 64-bit operation, it was really a 32-bit CPU using double-wide operations.

Modern hardware is inherently 64-bit or wider for the most part.

(14) By oneeyeman on 2021-03-10 02:10:50 in reply to 13 [link] [source]

Hi guys, One more question, if I may to close this thread...

What is sqlite3_int64 typedef'ed from?

Thank you.

(15) By Scott Robison (casaderobison) on 2021-03-10 02:57:32 in reply to 14 [link] [source]

It's in the header file, but it looks like this:

#ifdef SQLITE_INT64_TYPE
  typedef SQLITE_INT64_TYPE sqlite_int64;
# ifdef SQLITE_UINT64_TYPE
    typedef SQLITE_UINT64_TYPE sqlite_uint64;
# else
    typedef unsigned SQLITE_INT64_TYPE sqlite_uint64;
# endif
#elif defined(_MSC_VER) || defined(__BORLANDC__)
  typedef __int64 sqlite_int64;
  typedef unsigned __int64 sqlite_uint64;
#else
  typedef long long int sqlite_int64;
  typedef unsigned long long int sqlite_uint64;
#endif
typedef sqlite_int64 sqlite3_int64;
typedef sqlite_uint64 sqlite3_uint64;

So the exact incantation to get 64 bit integers depends on the environment, and can be customized by defining SQLITE_INT64_TYPE and (optionally) SQLITE_UINT64_TYPE macros.