 |
Derar
Novice
Joined: 09 Sep 2006
Posts: 44
|
 Posted: Tue Aug 24, 2010 9:41 pm
[3.22a] SQL Result Size Limitations?
|
I seem to be running into an issue where individual result fields returned from SQL Select queries are capped at 255 characters.
Basically, it's much more efficient when working to create large ordered lists to use GROUP_CONCAT to put them together within the query, rather than pull all the rows and then loop through with #WHILE & %additem.
The queries I'm running work fine with big result sets (2000+ chars) in SQLite Expert, so it looks like the limit is on CMud's side.
To Test:
| Code: |
<alias name="runtest" id="1">
<value>#SQLDB TestDB
#CALL %sql("TestDB", "CREATE TABLE TestTbl (TestCol BLOB)")
#LOOP 1,30 {
$sql = "INSERT INTO TestTbl (TestCol) VALUES ('"
$sql = %concat($sql, "This Is Entry #", %i, "')")
#CALL %sql("TestDB", $sql)
}
$db = %sql("TestDB", "SELECT GROUP_CONCAT(TestCol, '|') AS Test FROM TestTbl")
#ECHO $db.Item("Test")</value>
</alias>
|
Outputs:
| Code: |
This Is Entry #1|This Is Entry #2|This Is Entry #3|This Is Entry #4|This Is Entry #5|This Is Entry #6|This Is Entry #7|This Is Entry #8|This Is Entry #9|This Is Entry #10|This Is Entry #11|This Is Entry #12|This Is Entry #13|This Is Entry #14|This Is Entr
|
As you can see, didn't even make it half way.
So I'm not sure if there's something I'm missing that I can use to force CMud to take a larger result per field, but if I'm not, could we get that size cap increased please (fairly significantly)? :) |
|
|
|
|
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
Posted: Tue Aug 24, 2010 10:19 pm
|
I believe the proper field type in SQLite is "TEXT" and not "BLOB". Give that a try to see if it helps.
|
|
|
|
|
Derar
Novice
Joined: 09 Sep 2006
Posts: 44
|
Posted: Tue Aug 24, 2010 11:01 pm
|
Blob is a valid SQLite definition, but the point is moot since SQLite isn't strictly typed anyway. I only used BLOB in the example, as well; in what I'm really working on the datatypes I sometimes pull from are both Integer and Text.
I tried with Text anyway, and it didn't make a difference.
I was looking at some of the zQuery documentation, and checked the DataType on the field in question - it's defaulting to String, which seems to be your standard 255-char data. The note on DataTypes suggests what DataType you're getting is related to the driver, so...
I also tried upgdating to the newest sqlite3.dll version, which didn't help either.
I don't see any way really to forcibly alter the field of the incoming data, but maybe I'm just not looking hard enough. |
|
|
|
|
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
Posted: Wed Aug 25, 2010 4:40 pm
|
The problem is that you are not referencing a BLOB field directly. You are creating a bunch of records, and then creating an adhoc query with your SELECT GROUP_CONCAT command. The JDBC database drivers that CMUD is using for SQLite have no way to tell that the GROUP_CONCAT command returns a BLOB/TEXT field, so it just gets treated as a normal STRING field, which has a limit of 255 characters.
For example, if I change your test to this, then it works fine:
| Code: |
<alias name="runtest" id="1">
<value>#SQLDB TestDB
#CALL %sql("TestDB", "CREATE TABLE TestTbl (TestCol BLOB)")
$sql = "INSERT INTO TestTbl (TestCol) VALUES ('"
#LOOP 1,30 {
$sql = %concat($sql, "This Is Entry #", %i, "|")
}
$sql = %concat($sql, "')")
#CALL %sql("TestDB", $sql)
$db = %sql("TestDB", "SELECT TestCol FROM TestTbl")
#ECHO $db.Item("TestCol")</value>
</alias> |
Here I am just storing a really long string into a single BLOB field and then returning it.
I don't know of any way in SQLite to force the result field to be a BLOB instead of STRING, but maybe somebody else can help with that. CMUD is doing very little to interfere with this though...it's the database driver that determines the result type, not CMUD.
Also, there *is* a difference between using BLOB and TEXT/MEMO. Even though SQLite is a "typeless" database, SQL itself is not. So it doesn't really matter that SQLite stores everything typeless internally. You still need field types for external programs (like CMUD) to treat the data properly.
As a simple example, look at the SQLite Administrator program (http://sqliteadmin.orbmu2k.de). If you use BLOB, then the admin program treats the field as binary data and does not display any text. But if you change it to use TEXT instead of blob, then SQLite Admin will properly display the data in the column as a text value.
In general, BLOB is reserved for Binary data (like images) and TEXT is used for long string values. |
|
|
|
|
Derar
Novice
Joined: 09 Sep 2006
Posts: 44
|
Posted: Wed Aug 25, 2010 5:27 pm
|
Interesting, I always took Blob as big text, but I'll have to remember that.
I tried forcing the datatype on the concat by doing CAST(GROUP_CONCAT(TestCol, '|') AS TEXT) but that didn't do the trick either.
It's unfortunate, but at least after some more research into the zQuery object I was able to achieve the data collection I wanted using the Position property, and in other cases a GROUP BY clause, so it all works out for now.
Thanks for the pointers! |
|
|
|
|
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
Posted: Wed Aug 25, 2010 8:08 pm
|
You might try using CAST without the "AS TEXT" part. From the SQLite help:
| Quote: |
Casting a value into TEXT renders the value as if via sqlite3_snprintf() except that the resulting TEXT uses the encoding of the database connection.
Casting a value to a with no affinity causes the value to be converted into a BLOB. Casting to a BLOB consists of first casting the value to TEXT in the encoding of the database connection, then interpreting the resulting byte sequence as a BLOB instead of as TEXT. |
Looks like CAST as TEXT just changes the encoding, so it might not be triggering the ZeosLib/JDBC drivers to interpret the field as a full text field.
But we are definitely into the guts of exactly how the ZeosLib and JDBC drivers are working with SQLite here. Definitely well beyond my territory.
At least glad you found a workaround. |
|
|
|
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
