Blob / select statement

#1 · 2023-07-30, 10:21 am

if I wanted to create a temp table (not full select statement)

would this part would to create column in "blob" formatting ? (I am new to blob types)

'
<Root><Fields>
<Field><Name>AccessType</Name><Value>web</Value></Field>
<Field><Name>AccessDate</Name><Value> ' || E.UTCModDate ||' </Value></Field>
<Field><Name>memorial</Name><Value> ' || Prefix || ' ' || Given || ' ' || Surname || ' ' || Suffix || ' (' || BirthYear || '-' || DeathYear || ' ) ' || ' </Value></Field>
<Field><Name>FaG</Name><Value> ' || Details || ' </Value></Field>
<Field><Name>cmtrynm</Name><Value> ' || (substr(pt.Name, 1, instr(pt.Name,', ')-1)) || ' </Value></Field>
<Field><Name>cmtryloc</Name><Value> ' || (substr(pt.Name, instr(pt.Name,',')+2)) || ' </Value></Field>
<Field><Name>createdby</Name><Value></Value></Field>
<Field><Name>photoby</Name><Value></Value></Field>
</Fields></Root>
'
as Fields,

Thanks

Kevin

#2 · 2023-07-30, 9:23 pm

That expression outputs TEXT. You need to envelop your SELECT statement in the CAST() command:

CAST(textstring AS Blob)

The reverse of that for a Blob containing TEXT is:

CAST(Blob AS TEXT)

However, any string function or operator on a Blob outputs text, e.g.,:

TRIM(Blob)

I don't know why RM types a field as BLOB when it contains apparently just text. Maybe it has something to do with control codes or Unicode? I've not experienced any issue when cells in a Blob-type column are text.

kevync has reacted to this post.

#3 · 2023-07-30, 10:16 pm

Thanks Tom -- this seems to indicate it not really text as far as datatype (Even if that is all it contains).

https://www.geeksforgeeks.org/sqlite-data-types/

I guess I will have explore more on this - maybe someone else has some experience with Sqlite blobs

#4 · 2023-07-31, 10:46 pm

Quote from thejerrybryan on 2023-07-31, 10:46 pm
I'm equally mystified by RM's use of the BLOB datatype for data that is inherently TEXT. One thing that you do have to be aware of is that an SQLite schema's definition of a the datatype for a column is just a suggestion. You can have a BLOB column and store TEXT rows within it and vice versa. This is for all SQLite databases in the world, not just the RM database. SQLite is the only relational base in the world that has such cavalier typing rules. As a result, applications developed in SQLite are sometimes difficult to port to other relational databases.
At one point in RM7, I had a bunch of rows in the BLOB column CitationTable.Fields where I had done updates and the rows had become TEXT. RM7 stilled worked just fine, but I went in and did a CAST AS BLOB to make all the rows back into BLOB. And again RM7 couldn't seem to tell the difference.
When doing GROUP BY operations for such data, I have found that the GROUP BY for a BLOB groups by the entire BLOB field include a BLOB header and also a UTF-8 header. All TEXT fields in SQLite are UTF-8 and you can't get rid of that in a GROUP BY. But I have found it useful, for example to do GROUP BY CAST(CitationTable.Fields AS TEXT) rather than simply doing a GROUP BY CitationTable. Fields. As Tom already mentioned, most any string function will return a TEXT value for a BLOB field so you can do that instead of doing the explicit CAST.
I have also found that stray whitespace characters in some data elements such as CitationTable.Fields can wreak havoc on the logic of my queries. Such whitespace characters usually got into my database years ago when I would do a copy from a Web site and paste into a citation. Such a copy and paste can pick up stray tab characters, carriage return characters, linefeed characters, and leading or trailing space characters. Interior space characters are not a problem, but the other three white space characters can be a problem anywhere. Rather than making my queries deal with such stray characters, I have tried to remove them from my database.

I'm equally mystified by RM's use of the BLOB datatype for data that is inherently TEXT. One thing that you do have to be aware of is that an SQLite schema's definition of a the datatype for a column is just a suggestion. You can have a BLOB column and store TEXT rows within it and vice versa. This is for all SQLite databases in the world, not just the RM database. SQLite is the only relational base in the world that has such cavalier typing rules. As a result, applications developed in SQLite are sometimes difficult to port to other relational databases.

At one point in RM7, I had a bunch of rows in the BLOB column CitationTable.Fields where I had done updates and the rows had become TEXT. RM7 stilled worked just fine, but I went in and did a CAST AS BLOB to make all the rows back into BLOB. And again RM7 couldn't seem to tell the difference.

When doing GROUP BY operations for such data, I have found that the GROUP BY for a BLOB groups by the entire BLOB field include a BLOB header and also a UTF-8 header. All TEXT fields in SQLite are UTF-8 and you can't get rid of that in a GROUP BY. But I have found it useful, for example to do GROUP BY CAST(CitationTable.Fields AS TEXT) rather than simply doing a GROUP BY CitationTable. Fields. As Tom already mentioned, most any string function will return a TEXT value for a BLOB field so you can do that instead of doing the explicit CAST.

I have also found that stray whitespace characters in some data elements such as CitationTable.Fields can wreak havoc on the logic of my queries. Such whitespace characters usually got into my database years ago when I would do a copy from a Web site and paste into a citation. Such a copy and paste can pick up stray tab characters, carriage return characters, linefeed characters, and leading or trailing space characters. Interior space characters are not a problem, but the other three white space characters can be a problem anywhere. Rather than making my queries deal with such stray characters, I have tried to remove them from my database.

#5 · 2023-09-16, 2:37 pm

My guess as to why the XML fields are all blobs is that it has to do with the XML text used in RM 7 (and earlier ?).

RM used to save the XML with a Byte Order Mark- 2 bytes that indicate how the Unicode text was encoded. UTF-8, UTF-16 big endian, etc.
I think that SQLite text fields did not handle the BOM.

Since RM8, the XML is saved without the BOM (and the previously used XML processing line was also dropped-

<?xml version="1.0" encoding="UTF-8" ?>

#6 · 2023-09-16, 7:04 pm

Thanks Richard -- I have figured out how work with Blobs since the July Post. At least for most of my needs.