Recordset as a memory block ?

anyone tried to utilize a recordset as a memory block ?

I’m trying to pass a SQL recordset across the internet (using a secure bridge we have built) and while I can do all sorts of things such as creating an XML version and unpacking at the other end etc, I’m trying to pass as a memory block for the best compression and also best speed at either end.

Anyone experimented with this and any advice ?

send the string you made the query with. it will be shorter anytime than the recordset.
specially if you have 100k records to send…
and redo the query at the other end.
and sending the string is trivial.

I’ve never seen it done but I’m sure someone has. Most things I’ve done have been either XML or JSON strings. I suspect that you won’t see enough compression via the memory block to be worth it since internally it’s just a list of bytes which is essentially a string. You might compress the string but then you’ll probably want to use EncodeBase64. I dunno, I tend to think it’s more hassle than it’s worth.

We do that with the database proxy in our MBS Network Kit for iOS.

You can encode all keys and values for all records as JSON or XML, compress and encrypt for transfer.

Thank you all.

Christian - I saw the MBS option but I’m using windows. I’d buy it now if you had it :slight_smile:

Bob - we have a JSON version working, but there is much overhead in recreating back as a recordset (as we are using it as a bridge for an existing application that was LAN based). We have to create a local DB and a local table based on the JSON items and then spin them in it… hell of an overhead.

Jean-Yves - we send the sql request as a string, but the results we were hoping to have ultimately as a recordset because we are using as a bridge for an existing application that has been built around SQL a while back. The overhead of putting back ini a recordset seems to be horrible overhead.

I think the database proxy is quite OS independent and may run on Windows, too.
It may be worth a try.

You could wrap the native client driver’s C API with declares. You can look at Xojo’s MySQLCommunityPlugin in the Extras > Database Plugin Resources folder how Xojo does it for MySQL. And instead of creating RecordSets you would create MemoryBlocks.

I would be very careful about sending SQL queries across the Internet no matter how secure you think your connection is. If your service ever got hacked it could mean that someone could send ANY SQL statement to your database which could be disastrous. Instead, I would have a predefined set of SQL queries at the server end which the client then invokes by sending command names (e.g.: GETCUSTOMERLIST).

As far as sending data back I would do one of the following:

  1. If the result column list of each command is fixed and known by the client I would simply concatenate all of the results with a delimiter, compress it and send it back to the client. This requires that the client and server formats are always kept in sync.

  2. If the result column list of each command could be variable you have a couple of choices:
    a) Serialise it using xml or JSON and compress it
    b) Build a header row which containing a comma separated list of the result columns followed by the results which are separated by a delimiter and then compress it

‘2a’ is possibly the easiest to implement but it will bloat the amount of the data being returned due to the formats being verbose.

We use ‘1’ to return thousands of rows from an application server to a RB client across the Internet and it works well. We also built in some intelligence so that the server did not return rows that the client already had.

Couldn’t you create a class that implements the DatabaseRecorsetInterface and provide the JSON/XML data to it as its data source rather than writing the data to a table?

I may be blowing smoke out my arse here as I’ve never done this, but it seems like a logical way to provide a “recordset” without a database.

As I mentioned awhile ago I thought using a memoryblock to Serialize a recordset for transfer to ca lient app Xojo would be smaller and faster than using JSON… though the data transfer would be binary rather than Base64 encoded, which is not an issue for what I am doing.

I started working on doing just that…and it look like I was both right and wrong.

I think I have the serialization part done.(won’t know for sure until the deserialization part is finished and I get the data back out)

It is all pure Xojo code with no plugins… I wanted to see if what i was doing was worthwhile, so I compared the Serialization time and string size of my memoryblock code vs JsonItem, JsonItem_MTC and Xojo.Data.GenerateJson ( Doing implicit conversions both ways between string and text).

As I have never used JSON, I essentially used the Luna Express RecordSetToJSON method for JSONItem And JASONItem_MTC, and based the Xojo.Data version on the language reference code…

These may be naive implementations of using JSON to serialize a record set, and someone knowledgeable might be able to create code to get smaller strings and/or faster performance … but it gave me a baseline to compare against.

The RecordSet I used for testing was based on an SQLite View. It had 37 fields and 20,000 records and records averaged (as binary) about 350 bytes - I did not look at individual JSON record sizes.

The data shown below is from running in the IDE… When compiled, the MemoryBlock code runs 2.5-3X faster. I did not test the other methods compiled

While this data only applies to this recordset as different record composition/field data sizes will obviously matter, I found it encouraging!

      Method             StringSize (MB)        Time Sec            GZipedSize (MB)
MemoryBlock              7.18                      1.85                  1.44
JSONItem                14.95                    124.67                  1.55
JSONItem_MTC            14.95                     12.42                  1.55
Xojo.Data               14.95                      1.67                  1.58

So uncompressed the memoryblock string is about half the size of the JSON string, but compressed there is not much difference in size

Speed wise the pure Xojo memoryblock code is much faster than either JSONItem method. The new framework is sightly faster… but my memoryblock code lets me add more features and I expect deserialization might faster than JSON as well.

Here is the JSONItem code I used

[code]Dim RecordsJSON As New JSONItem_MTC

Dim Startms as Double = Microseconds
// Loop over each record…
While Not Records.EOF

Dim RecordJSON As New JSONItem_MTC

// Loop over each column…
For i As Integer = 0 To Records.FieldCount-1

// Add a name / value pair to the JSON record.
RecordJSON.Value( Records.IdxField(i+1).Name ) = Records.IdxField(i+1).StringValue

Next

// Add the JSON record to the JSON records object.
RecordsJSON.Append(RecordJSON)

// Go to the next row.
Records.MoveNext

Wend
Dim StrVal As String = RecordsJSON.ToString
Dim Duration as Double = Microseconds - Startms
Dim Size As UInt32 =StrVal.LenB
Dim ZipedSz As UInt32 = GZip(StrVal).LenB
Dim ZDuration as Double = Microseconds - Startms
// Close the recordset.
If Close Then
Records.Close
End If

Dim C as New Clipboard
C.text = Str(Size) + Chr(9)+ Str(Duration) + Chr(9) + Str(ZipedSz)+ Chr(9) + Str(ZDuration)
C.Close[/code]

This is teh Xojo.Data.GenerateJSON codeL

[code]Dim dictArray() As Xojo.Core.Dictionary

Dim Startms as Double = Microseconds
// Loop over each record…
While Not Records.EOF

Dim d As New Xojo.Core.Dictionary

// Loop over each column…
For i As Integer = 0 To Records.FieldCount-1

// Add a name / value pair to the JSON record.
d.Value( Records.IdxField(i+1).Name ) = Records.IdxField(i+1).StringValue

Next

// Add the JSON record to the JSON records object.
dictArray.Append(d)

// Go to the next row.
Records.MoveNext

Wend
Dim StrVal As String = Xojo.Data.GenerateJSON(dictArray)
Dim Duration as Double = Microseconds - Startms
Dim Size As UInt32 =StrVal.LenB
Dim ZipedSz As UInt32 = GZip(StrVal).LenB
Dim ZDuration as Double = Microseconds - Startms
// Close the recordset.
If Close Then
Records.Close
End If

Dim C as New Clipboard
C.text = Str(Size) + Chr(9)+ Str(Duration) + Chr(9) + Str(ZipedSz)+ Chr(9) + Str(ZDuration)
C.Close[/code]

We have a function in MBS Xojo SQL Plugin:

BuildRecordSetMBS(fieldNames() as string, values() as string) as RecordSet

try it like this:

[code] dim names() as string = array(“Firstname”, “Lastname”)
dim values() as string

values.append “Stefan”
values.append “Miller”
values.append “Patrick”
values.append “Maier”

dim r as RecordSet = BuildRecordSetMBS(names, values)[/code]

So you may need to store your values as strings, join them, put in memory block, later reverse and split.