[sqlite] Size query
Is it possible to construct a query that will tell me the total size in bytes of the result set? Specifically, in a messaging protocol that returns data we need to fragment the reply and it would be nice to know how much data or how many packets will be required to send the response. Thanks, Scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size query
Couldn't you do something like: select length(FieldName) from TableName where Condition=True ? The result would be the size. Otherwise, the only thing I can think of is just doing a select to get the results you want, then just keep a running tally on what would need to be transmitted, then do your packet math. On Wed, Nov 3, 2010 at 6:24 PM, Scott A Mintz sami...@ra.rockwell.comwrote: Is it possible to construct a query that will tell me the total size in bytes of the result set? Specifically, in a messaging protocol that returns data we need to fragment the reply and it would be nice to know how much data or how many packets will be required to send the response. Thanks, Scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size query
If x is numeric length(x) returns the length of x expressed as a string. So the value of length(1 ) is 5, not 2. -Scott sqlite-users-boun...@sqlite.org wrote on 11/03/2010 06:35:52 PM: Couldn't you do something like: select length(FieldName) from TableName where Condition=True ? The result would be the size. Otherwise, the only thing I can think of is just doing a select to get the results you want, then just keep a running tally on what would need to be transmitted, then do your packet math. On Wed, Nov 3, 2010 at 6:24 PM, Scott A Mintz sami...@ra.rockwell.comwrote: Is it possible to construct a query that will tell me the total size in bytes of the result set? Specifically, in a messaging protocol that returns data we need to fragment the reply and it would be nice to know how much data or how many packets will be required to send the response. Thanks, Scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size query
On Wed, 03 Nov 2010 18:35:52 -0400, Stephen Chrzanowski pontia...@gmail.com wrote: Couldn't you do something like: select length(FieldName) from TableName where Condition=True ? The result would be the size. Otherwise, the only thing I can think of Caution: This will return the size in bytes of a BLOB field, but the size in *characters* of a TEXT field. Except for lobotomized 7-bit-only text, the two will almost never be the same in a UTF-8 database. If the database is encoded in UTF-16, there are exactly two bytes for every plane-0 character and exactly four bytes for every character in plane 1 and up. Casting to a BLOB will result in the text being simply reinterpreted as a BLOB, which is probably what the original poster wants. If applied to a numeric (INTEGER or FLOAT) value, length() will first cast input to TEXT, then return the size in characters; since the cast will only return values 127, the result will be in bytes for UTF-8 and half the byte-size for UTF-16. Casting to BLOB will not fix this, as the value is still first cast to TEXT. Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size query
On Wed, 03 Nov 2010 18:24:57 -0400, Scott A Mintz sami...@ra.rockwell.com wrote: Is it possible to construct a query that will tell me the total size in bytes of the result set? Specifically, in a messaging protocol that returns data we need to fragment the reply and it would be nice to know how much data or how many packets will be required to send the response. Per my other post, for TEXT values, you can CAST to BLOB and then use length(). Assuming the C API, I am guessing that it’s much more efficient play with sqlite3_column_bytes(), sqlite3_column_bytes16(), sqlite3_value_bytes(), and/or sqlite3_value_bytes16(). These will return byte-counts (with no zero terminator) for both TEXT and BLOB values. As far as I can tell, you will need to count the bytes in numeric values yourself. (I’ve looked into this before, and just glanced into it again.) SQLite uses its own variable-length integer representation internally, occupying between 1 and 64 bits per value; if this is for a network protocol, you probably want to count the bytes in protocol representation rather than SQLite’s representation. Counting the bytes (or lack thereof) in NULL values should probably also be done in your protocol’s representation. Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size query
On Wed, 03 Nov 2010 19:17:48 -0400, Samuel Adam a...@certifound.com wrote: SQLite uses its own variable-length integer representation internally, occupying between 1 and 64 bits per value; if this is for a Sorry to reply to my own post; I wish to be precise. By “internally”, I meant (and should have said) “in the database file format”. Which raises another question: If you want the byte-length of an SQLite integer, do you mean as stored in the database, or as processed in-memory, typically via sqlite3_int64 and sqlite3_uint64? Looking at the typedefs, these latter *could* compile to something bigger than 8 bytes on exotic platforms (although it would be difficult for them to be smaller). Samuel Adam a...@certifound.com 763 Montgomery Road Hillsborough, NJ 08844-1304 United States http://certifound.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size query
But wouldn't you be aware of what the data is you want to transmit anyways? Sure, it thinks as 1 as a length of 5, but, you'll know that you'll need to send 4 or 8 bytes. On Wed, Nov 3, 2010 at 6:50 PM, Scott A Mintz sami...@ra.rockwell.comwrote: If x is numeric length(x) returns the length of x expressed as a string. So the value of length(1 ) is 5, not 2. -Scott sqlite-users-boun...@sqlite.org wrote on 11/03/2010 06:35:52 PM: Couldn't you do something like: select length(FieldName) from TableName where Condition=True ? The result would be the size. Otherwise, the only thing I can think of is just doing a select to get the results you want, then just keep a running tally on what would need to be transmitted, then do your packet math. On Wed, Nov 3, 2010 at 6:24 PM, Scott A Mintz sami...@ra.rockwell.comwrote: Is it possible to construct a query that will tell me the total size in bytes of the result set? Specifically, in a messaging protocol that returns data we need to fragment the reply and it would be nice to know how much data or how many packets will be required to send the response. Thanks, Scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
