* Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

For users to report plugin bugs and request plugin enhancements; and for authors to test new/new versions of plugins, and to discuss plugin development (in the Programming Technicalities sub-forum). If you want advice on choosing or using a plugin, please ask in General Usage or an appropriate sub-forum.
Post Reply
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by Mark1834 »

This may be an impossible ask, as it needs somebody familiar with the inner workings of fhSQL, SQLite and the RM proprietary RMNOCASE treatment of UTF-8 strings, but I'll try anyway :).

I'm getting odd results with the following code, which is designed to extract Source Template Definitions from RM. They are stored in the SQLite table as a BLOB, which is just an unstructured array of bytes. If I view the BLOB in SQLite Expert, everything makes sense. I can see the definition displayed as XML in a very similar format to the FH definition files.

Code: Select all

require('fhSQL')

tblT = {}
FileName = 'D:\\OneDrive\\Documents\\FH Plugin Development\\junk.rmgc'
database = fhSQL.connectSQLite(FileName)

SQL = 'SELECT FieldDefs FROM SourceTemplateTable'

ResultSet = database:select(SQL)

for record in ResultSet:rows() do
    FieldDef = tostring(record.FieldDefs)
--  FieldDef = FieldDef:sub(1,100)
    print(FieldDef:len(), FieldDef)
    table.insert(tblT, FieldDef)
end

fhSaveTextFile('D:\\OneDrive\\Desktop\\junk.txt', table.concat(tblT, '\n'))

fhMessageBox(#tblT .. ' records inserted')
If I run this script, it tells me that 415 values have been stored in the table, which is correct. Comparing the first record extracted, both this script and SQLite Expert tell me that it is 1341 bytes. However, both the output file and the debug window show only the first record, and that is truncated at 260 bytes. There is nothing unusual at this point according to the SQLite Expert Hex viewer.
Capture.PNG
Capture.PNG (73.77 KiB) Viewed 3851 times
If I truncate the strings before storing them, as per the commented out code line, I get all the values reported as expected :?.

There seems to be something in the latter part of the string that is upsetting the plugin.

Any ideas?
Mark Draper
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by tatewise »

In Lua, strings are terminated by a byte with the value 00.
There is no such value evident in the Hex Editor screenshot, but exactly what byte values exist at about byte 260.
I often use the Lua function string.byte (s [, i [, j]]) to debug the contents of strings and print the byte values.

I assume the Plugin has File > Encoding > UTF-8
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by Mark1834 »

All the bytes after the truncation are reported as 00. If I convert the retrieved value to ANSI with fhConvertUTF8toANSI(), I get all the values, but they are all truncated.

I think that explains why the first value is shown as truncated, as fhSQL does not appear to be retrieving the BLOB correctly. I didn't expect that to suppress all further output to either file or debug print window, but it seems that it does. I tried to run the following simple script, and nothing appeared in the output window apart from a 'Plugin has completed' message.

Code: Select all

c = string.char(0)
print(c)
print('hello')
Mark Draper
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by tatewise »

If you step through those statements with the debugger it does display hello.
Not sure why it doesn't if you just use Go.

Returning to your posted script, is tostring(...) strictly necessary here? Usually, that is only needed for numbers.
FieldDef = tostring(record.FieldDefs)
Try it without tostring(...)

Presumably, you are using the following to examine the byte values:
print( FieldDef:byte(250,270) )
You are saying that all the bytes beyond about position 260 are 00.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by Mark1834 »

The loop code now reads

Code: Select all

for record in ResultSet:rows() do
    FieldDef = record.FieldDefs
    print(FieldDef:len())
    print( FieldDef:byte(250,300) )
    print(FieldDef)
    table.insert(tblT, FieldDef)
    FieldDef = ''
end
If I step through the code by keeping F11 depressed until the Message Box appears, all records are shown (truncated) in the debugger print window. They all show character 194 at position 269, but all other bytes after the truncation appear to be zero.
Capture.PNG
Capture.PNG (25.97 KiB) Viewed 3813 times
Mark Draper
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by tatewise »

I wonder if it is significant that the first 0 byte is always at position 256?
i.e. Is there a 256-byte buffer constraint in the library module somewhere?

It is fascinating that every row FieldDef starts with the UTF-8 BOM 
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by Mark1834 »

My gut feeling is that there are two separate issues here. Why does fhSQL not retrieve the data correctly, and why does FH/Lua react so oddly to all those zero bytes? They may be unrelated.
Mark Draper
User avatar
Jane
Site Admin
Posts: 8508
Joined: 01 Nov 2002 15:00
Family Historian: V7
Location: Somerset, England
Contact:

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by Jane »

fhSQL is a simple wrapper for Luacom and the Microsoft SQL driver. Remember pure lua does not handle UTF8 strings, so that may be the problem, another possible is that the blobs need special handling looking at the example here https://dev.mysql.com/doc/connector-net ... ading.html for a similar connector seems to suggest they might.

It might be worth using a local copy of the rows function from fhSQL so you can debug the original data coming from Luacom to see if it throws any light on the problem.

To get the print statement to dump stuff correctly you will need to replace the zero bytes in the string before outputting it see
https://www.lua.org/pil/21.2.2.html
Jane
My Family History : My Photography "Knowledge is knowing that a tomato is a fruit. Wisdom is not putting it in a fruit salad."
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by tatewise »

Mark, FH/Lua is quite happy managing byte 0 values in text strings.
They can be gsub replaced, matched, and sub string extracted, etc.
However, as soon as they are displayed/printed the first byte 0 terminates what gets displayed.

I have been doing as Jane suggested and included the entire fhSQL library script in a Plugin so I can debug it.
It happens that the RM sample.rmgc file has suitable data tables and I get the same issues as Mark.

It is the rows function, where it is retrieving field values, that exhibits the problem.
If I change the script to operate on other entries such as Bibliography instead of FieldDefs it all works fine, but are shorter.
But they are TEXT whereas FieldDefs is a BLOB entry, so I suspect that BLOB data is not retrieved correctly by the .Fields, .Item or .Value operations, which may be a problem with luacom.CreateObject("ADODB.Connection").
The length of the field is correct, but after the first 256 bytes the bytes get filled with byte 0 values.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
Jane
Site Admin
Posts: 8508
Joined: 01 Nov 2002 15:00
Family Historian: V7
Location: Somerset, England
Contact:

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by Jane »

Have you checked the MS ADODB documentation, it may be a different function is needed for blob fields. It does not state that you need to use something other than value, but it could be worth trying GetChunk in your version of rows for the blob fields. See https://www.w3schools.com/asp/ado_ref_field.asp
Jane
My Family History : My Photography "Knowledge is knowing that a tomato is a fruit. Wisdom is not putting it in a fruit salad."
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by Mark1834 »

We're obviously thinking along the same lines, as I've been doing exactly the same experiments :).
  • I can retrieve short BLOBs without any problems.
  • If I copy exactly the same bytes from a BLOB field to a TEXT field using SQLiteExpert, the entire contents are retrieved without any problems, and when written to file it shows as UTF-8 BOM in Notepad++ . There doesn't seem to be an intrinsic problem with handling UTF-8.
  • If I create a long BLOB using only ASCII characters, it is still truncated.
The issue definitely appears to be retrieving BLOBs in excess of 256 bytes. I'll hold off any more playing while Mike digs into the detailed plumbing - you're a lot more experienced than I am with that level of detail...
Mark Draper
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by tatewise »

I have come to the same conclusion by experimenting with existing fields.
e.g.
SourceTable Comments are TEXT and examples up to 977 in length are retrieved OK.
Short BLOB fields are OK, but anything over 256 in length gets mangled.

It is not the fhSQL library itself but the underpinning luacom.CreateObject("ADODB.Connection") where the problem lies.
Unfortunately, luacom is a dll application extension so that is beyond debugging.
Not sure where to go next.

Do we know if ADODB supports BLOB data?

There are a few discussions regarding luacom or ADODB and BLOB found by a Google search but nothing helpful.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by Mark1834 »

If a direct import from RM manages to bring over long BLOBs ok, CP must have found a way around it. It might be worth raising a ticket to see if we get any guidance (or somebody who has their ear could ask a direct question...?).
Mark Draper
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by Mark1834 »

I’m working with a local copy of fhSQL so I can work out what it is actually doing. Most of it’s ok, but I don’t understand the first line of the rows() function.

What does return function() do? I can’t find an example anywhere in the usual online places.

Even if it doesn’t solve the current problem, there are some new concepts in there that are worth me becoming familiar with.
Mark Draper
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by tatewise »

I'm away from my PC this weekend but I think it is the iteration function that returns each row to your for loop.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
Jane
Site Admin
Posts: 8508
Joined: 01 Nov 2002 15:00
Family Historian: V7
Location: Somerset, England
Contact:

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by Jane »

I spent a few hours on this today, but the root problem is the SQLite ODBC driver used for ADO looks to limit fields to 255.

See
https://stackoverflow.com/questions/152 ... d-vbscript

FH, as opposed to lua uses the SQLite dll directly so does not have the same limitation.
Jane
My Family History : My Photography "Knowledge is knowing that a tomato is a fruit. Wisdom is not putting it in a fruit salad."
User avatar
Mark1834
Megastar
Posts: 2458
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by Mark1834 »

Thanks Jane, appreciated. You seem to be in contact with FH support, as they have closed the ticket I raised with the same comment, so it looks unfixable.

It will only affect a tiny number of users (plugin authors interested in the interface with RM and similar), but it's probably worth mentioning on the KB as and when we include reference to fhSQL.
Mark Draper
User avatar
tatewise
Megastar
Posts: 28341
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Issue with fhSQL not retrieving RM/SQLite BLOB values correctly

Post by tatewise »

As mentioned earlier fhSQL ADODB does support long TEXT fields. It is only BLOB fields that are truncated.

fhSQL is already in the KB with all the other library modules.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
Post Reply